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

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

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

miao
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]]

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

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

Enrico Schumann-2
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

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

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

Ivan Calandra-5
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")
>
>
>

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

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

Jeff Newmiller
In reply to this post by miao
give the function a list of data frames.

On May 26, 2020 11:21:58 PM PDT, John <[hidden email]> 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]]
>
>______________________________________________
>[hidden email] 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.

______________________________________________
[hidden email] 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.