How to Export Multiple Data.Frame to Multiple Excel Worksheets

Easy way to export multiple data.frame to multiple Excel worksheets

You can write to multiple sheets with the xlsx package. You just need to use a different sheetName for each data frame and you need to add append=TRUE:

library(xlsx)
write.xlsx(dataframe1, file="filename.xlsx", sheetName="sheet1", row.names=FALSE)
write.xlsx(dataframe2, file="filename.xlsx", sheetName="sheet2", append=TRUE, row.names=FALSE)

Another option, one that gives you more control over formatting and where the data frame is placed, is to do everything within R/xlsx code and then save the workbook at the end. For example:

wb = createWorkbook()

sheet = createSheet(wb, "Sheet 1")

addDataFrame(dataframe1, sheet=sheet, startColumn=1, row.names=FALSE)
addDataFrame(dataframe2, sheet=sheet, startColumn=10, row.names=FALSE)

sheet = createSheet(wb, "Sheet 2")

addDataFrame(dataframe3, sheet=sheet, startColumn=1, row.names=FALSE)

saveWorkbook(wb, "My_File.xlsx")

In case you might find it useful, here are some interesting helper functions that make it easier to add formatting, metadata, and other features to spreadsheets using xlsx:
http://www.sthda.com/english/wiki/r2excel-read-write-and-format-easily-excel-files-using-r-software

Export multiple data frames to multiple Excel worksheets

Try the following :

sheets <- list(sheet1 = Top10, sheet2 = Top_Over_60) 
writexl::write_xlsx(sheets, "result.xlsx") 

Writing R dataframes to excel files with multiple worksheets

You can write each DF to the same xlsx.file and specify different names for the worksheets. You have to use append = TRUE for the second worksheet.

library(xlsx)
write.xlsx(mtcars[1:10,], file="filename.xlsx", sheetName="sheet1", row.names=FALSE)
write.xlsx(mtcars[11:20,], file="filename.xlsx", sheetName="sheet2", append=TRUE, row.names=FALSE)

This solution comes from here

How to Save or export multiple dataframes in excel in different tabs?

A simple method would be to hold your items in a collection and use the pd.ExcelWriter Class

Lets use a dictionary.

#1 Create a dictionary with your tab name and dataframe.

dfs  = {'df1' : df1, 'df2' : df2...} 

#2 create an excel writer object.

writer = pd.ExcelWriter('excel_file_name.xlsx')

#3 Loop over your dictionary write and save your excel file.

for name,dataframe in dfs.items():
    dataframe.to_excel(writer,name,index=False)

writer.save()

---

adding a path.

from pathlib import Path

trg_path = Path('your_target_path')

writer = pd.ExcelWriter(trg_path.joinpath('excel_file.xlsx'))

Save list of DataFrames to multisheet Excel spreadsheet

You should be using pandas own ExcelWriter class:

from pandas import ExcelWriter
# from pandas.io.parsers import ExcelWriter

Then the save_xls function works as expected:

def save_xls(list_dfs, xls_path):
    with ExcelWriter(xls_path) as writer:
        for n, df in enumerate(list_dfs):
            df.to_excel(writer,'sheet%s' % n)

How to export a DataFrame to multiple sheets of Excel File

Given your dataframe data:

• Iterates through each unique ID and slices the dataframe for those values, and then saves to the file.

import pandas as pd

with pd.ExcelWriter('data.xlsx') as writer:
    for i, value in enumerate(data.ID.unique(), start=1):
        data[data.ID == value].to_excel(writer, index=False, sheet_name=f'Sheet{i}')

• I think you would be better off to name each sheet the ID value instead of Sheet1, etc. e.g. sheet_name=f'{value}'
  
  
  • This will allow you to discern which sheet each ID is on.
  • pandas.DataFrame.to_excel

import pandas as pd

with pd.ExcelWriter('data.xlsx') as writer:
    for value in data.ID.unique():
        data[data.ID == value].to_excel(writer, index=False, sheet_name=f'{value}')

Export multiple dataframes to one Excel Sheet with r

Sounds like a bad idea to intersperse data frame names with data in the same worksheet, but that's up to you.

Anyway, use openxlsx. Here's a way to do it:

dfname1 <- data.frame(col1 = c("x", "x"), col2 = c("x", "x"), stringsAsFactors = FALSE)
dfname2 <- data.frame(col1 = c("x"), col3 = c("x"), stringsAsFactors = FALSE)

df_list <- list(dfname1=dfname1,
                dfname2=dfname2)

library(openxlsx)

wb <- createWorkbook()
addWorksheet(wb, "Foo")

curr_row <- 1
for(i in seq_along(df_list)) {
  writeData(wb, "Foo", names(df_list)[i], startCol = 1, startRow = curr_row)
  writeData(wb, "Foo", df_list[[i]], startCol = 1, startRow = curr_row+1)
  curr_row <- curr_row + nrow(df_list[[i]]) + 2
}

saveWorkbook(wb, "bar.xlsx")

This gives you (literally) what you asked for.

Download multiple dataframes in multiple sheets of the same excel file in a shiny app

try changing your server code to this. Also, remember to open the app in your browser and not just the rstudio viewer (assuming your are using rstudio). Hope this helps!

server <- shinyServer(function(input, output) {

    output$dl <- downloadHandler(

        filename = function() {
            paste0("df_dmodel", "_Table", ".xlsx")
        },
        content = function(file){
            tbl<-iris
            tbl2<-mtcars
            sheets <- mget(ls(pattern = "tbl")) # getting all objects in your environment with tbl in the name
            names(sheets) <- paste0("sheet", seq_len(length(sheets))) # changing the names in your list
            writexl::write_xlsx(sheets, path = file) # saving the file
        }
    ) 
})

---

Related Topics