R help - May 2020 - Output multiple sheets to Excel files with openxlsx::write.xlsx

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!!

	[[alternative HTML version deleted]]

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")



-- 
Enrico Schumann
Lucerne, Switzerland
http://enricoschumann.net

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")
>
>
>

give the function a list of data frames.

On May 26, 2020 11:21:58 PM PDT, John <miaojpm at gmail.com>
wrote:
>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!!
>
>	[[alternative HTML version deleted]]
>
>______________________________________________
>R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see
>https://stat.ethz.ch/mailman/listinfo/r-help
>PLEASE do read the posting guide
>http://www.R-project.org/posting-guide.html
>and provide commented, minimal, self-contained, reproducible code.
-- 
Sent from my phone. Please excuse my brevity.