Skip to content

Output multiple sheets to Excel files with openxlsx::write.xlsx

4 messages · John, Enrico Schumann, Ivan Calandra +1 more

John
# 
Hi,

   This is my code a few years ago. I was able to output multiple sheet to
an excel file. Nevertheless, the "append" argument appears to be obsolete.
Now I see only one sheet, the latest added sheet, in the output. Is there
any other way to do it with openxlsx::write.xlsx or other
functions/packages?


openxlsx::write.xlsx(df1, file=fl_out, sheetName="a",
                 col.names=TRUE, row.names=FALSE, append=TRUE, showNA=FALSE)

openxlsx::write.xlsx(df2, file=fl_out, sheetName="b",
                     col.names=TRUE, row.names=FALSE,
append=TRUE, showNA=FALSE)

Thanks!!
Enrico Schumann
# 
On Wed, 27 May 2020, John writes:
I think you need to create a workbook first, then add
the sheets, and finally write the workbook to a file.
Something like this:

    df <- data.frame(a = 1:3,
                     b = 4:6)
    
    library("openxlsx")
    wb <- createWorkbook()
    
    sheet <- "sheet1"
    addWorksheet(wb, sheet)
    writeData(wb, sheet = sheet, x = df)
    
    sheet <- "sheet2"
    addWorksheet(wb, sheet)
    writeData(wb, sheet = sheet, x = df + 1)
    
    saveWorkbook(wb, file = "~/Desktop/two_sheets.xlsx")
Ivan Calandra
# 
Hi,

Rather than creating a workbook as suggested by Enrico, you can simply
supply a list to write.xlsx(); each element will be saved in a separate
sheet:
write.xlsx(list(a = df1, b = df2), file = fl_out)

That is not really appending, but that might work for you.

HTH,
Ivan

--
Dr. Ivan Calandra
TraCEr, laboratory for Traceology and Controlled Experiments
MONREPOS Archaeological Research Centre and
Museum for Human Behavioural Evolution
Schloss Monrepos
56567 Neuwied, Germany
+49 (0) 2631 9772-243
https://www.researchgate.net/profile/Ivan_Calandra
On 27/05/2020 9:15, Enrico Schumann wrote:
Jeff Newmiller
# 
give the function a list of data frames.
On May 26, 2020 11:21:58 PM PDT, John <miaojpm at gmail.com> wrote: