Easy way to export multiple data frame to multiple Excel worksheets

0 votes

I am surprised to find that there is no easy way to export multiple data.frame to multiple worksheets of an Excel file? I tried xlsx package, seems it can only write to one sheet (override old sheet); I also tried WriteXLS package, but it gives me error all the time...

My code structure is like this: by design, for each iteration, the output dataframe (tempTable) and the sheetName (sn) got updated and exported into one tab.

for (i in 2 : ncol(code)){ 
        ...
        tempTable <- ...
        sn <- ...
        WriteXLS("tempTable", ExcelFileName = "C:/R_code/../file.xlsx",
              SheetNames = sn);
}

I can export to several cvs files, but there has to be an easy way to do that in Excel, right?

Mar 24, 2022 in Database by Edureka
• 13,630 points
919 views

1 answer to this question.

0 votes

To export numerous data frames from R to multiple Excel worksheets, use the following basic syntax:

library(openxlsx)

dataset_names <- list('Sheet1' = df1, 'Sheet2' = df2, 'Sheet3' = df3)
write.xlsx(dataset_names, file = 'mydata.xlsx')

Exporting Multiple Data Frames to Multiple Excel Sheets is an example.
Let's pretend we have the following three R data frames:

#define data frames
df1 = data.frame(playerID=c(1, 2, 3, 4),
                 team=c('A', 'B', 'B', 'C'))

df2 = data.frame(playerID=c(1, 2, 3, 4),
                 rebounds=c(7, 8, 8, 14))

df3 = data.frame(playerID=c(1, 2, 3, 4),
                 points=c(19, 22, 25, 29))

To export all three of these data frames to distinct sheets within the same Excel file, use the following syntax:

library(openxlsx)

#define sheet names for each data frame
dataset_names <- list('Sheet1' = df1, 'Sheet2' = df2, 'Sheet3' = df3)

#export each data frame to separate sheets in same Excel file
openxlsx::write.xlsx(dataset_names, file = 'mydata.xlsx') 
answered Mar 25, 2022 by gaurav
• 22,970 points

Related Questions In Database

0 votes
0 answers

Export SQL query data to Excel

My query returns a huge amount of ...READ MORE

Aug 18, 2022 in Database by Kithuzzz
• 33,680 points
167 views
+2 votes
1 answer

How to export data from MySql to a CSV file?

If you are using MySql workbench then ...READ MORE

answered Jan 4, 2019 in Database by Priyaj
• 58,100 points
723 views
0 votes
1 answer

What is the best way to fetch data from table?

Hey Shraddha, I understand your doubts about fetching ...READ MORE

answered May 25, 2019 in Database by sampriti
• 1,120 points
1,377 views
0 votes
1 answer

Importing notepad data to excel (difficult split)

Excel: How to Use It On the Data ...READ MORE

answered Mar 14, 2022 in Database by gaurav
• 22,970 points
134 views
0 votes
0 answers

Laravel - Datatables export excel from filtered data

Hello i would like to export data ...READ MORE

Mar 28, 2022 in Database by Edureka
• 13,630 points
2,697 views
0 votes
1 answer

How to transform multiple tables in one excel sheet to one table with Power BI?

If my prediction is correct- You have a ...READ MORE

answered Apr 4, 2022 in Database by Edureka
• 13,630 points
1,470 views
0 votes
1 answer

Excel - how to calculate sum of multiple rows into different columns

The AutoSum button or formula can be ...READ MORE

answered Apr 4, 2022 in Database by Edureka
• 13,630 points
5,871 views
0 votes
0 answers

Is there a way to crack the password on an Excel VBA Project?

I've been asked to update some Excel ...READ MORE

Apr 7, 2022 in Database by Edureka
• 13,630 points
182 views
0 votes
1 answer

How to Export Tally Data programmatically to CSV or Excel format

Open data (Ledger/P&L or Balance Sheet) that ...READ MORE

answered Mar 15, 2022 in Database by gaurav
• 22,970 points
1,146 views
0 votes
1 answer

How to Export Tally Data programmatically to CSV or Excel format

Tally Data in Excel or PDF: How ...READ MORE

answered Mar 30, 2022 in Database by gaurav
• 22,970 points
454 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP