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

Ivan Calandra c@|@ndr@ @end|ng |rom rgzm@de
Wed May 27 09:24:01 CEST 2020


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:
> On Wed, 27 May 2020, John writes:
>
>> 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!!
>>
> 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")
>
>
>



More information about the R-help mailing list