openxlsx::write.xlsx overwriting existing worksheet instead append

Question

The openxlsx::write.xlsx function is overwriting spreadsheet instead of adding another tab.

I tried do follow some orientations of Stackoverflow, but without sucess.

dt.escrita <- format(Sys.time(), '%Y%m%d%H%M%S')

write.xlsx( tbl.messages
           ,file = paste('.\\2_Datasets\\messages_',dt.escrita,'.xlsx')
           ,sheetName = format(Sys.time(), '%d-%m-%y')
           ,append = FALSE)

write.xlsx( tbl.dic.dados
            ,file = paste('.\\2_Datasets\\messages_',dt.escrita,'.xlsx')
            ,sheetName = 'Dicionario_Dados'
            ,append = TRUE)

A spreadsheet with two tabs named: 30-07-19 and Dicionario_Dados.

You might be stuck first reading from that worksheet, `rbind`ing (or otherwise combining) old with new data, then go with the write. Related: https://community.rstudio.com/t/append-function-of-xlsx-in-openxlsx/9351/2 — r2evans, Jul 30 '19 at 19:05
I don't know if `openxlsx` is in active development (several relevant bugs are going un-discussed/un-resolved), but this was requested in 2015 (https://github.com/awalker89/openxlsx/issues/135). (In fact, awalker89 has been silent [since Oct 2018](https://github.com/awalker89).) — r2evans, Jul 30 '19 at 19:07

score 15 · Accepted Answer · edited Nov 21 '20 at 21:40

Not sure if I understand correctly, you want to create one xlsx file with two tabs/sheets? Then I usually first create the sheets and then write into each sheet seperatly (This is different from adding data to the same sheet by appending it).

library("openxlsx")
mtcars1 <- mtcars %>% filter(cyl == 4)
mtcars2 <- mtcars %>% filter(cyl == 6)

wb <- createWorkbook()
addWorksheet(wb, "mtcars1")
addWorksheet(wb, "mtcars2")

writeData(wb, "mtcars1", mtcars1, startRow = 1, startCol = 1)
writeData(wb, "mtcars2", mtcars2, startRow = 1, startCol = 1)

saveWorkbook(wb, file = "excel_test.xlsx", overwrite = TRUE)

Update: Just wondering why I never used the append argument in openxlsx (which is my standard package for read/writing excel). It seems like there is no such argument to neither of the three functions write.xlsx(), writeData(), writeDataTable(). At least it's not in the documentation.

The function does not seem to throw an error when called with unknown arguments, for example the call below has a non-existing somearg, but returns no error.

write.xlsx(mtcars2,
           file = "excel_test.xlsx",
           sheetName = "mtcars1",
           somearg = TRUE)

Update 2 To append data to an existing table you could read in the number of rows of the existing worksheet, add +1 and use this values as startRow:

wb2 <- loadWorkbook(file = "excel_test.xlsx")

writeData(wb2,
          "mtcars1",
          mtcars2,
          colNames = FALSE,
          startRow = nrow(readWorkbook("excel_test.xlsx"))+1)
#Fixed the call to nrow, instead of ncol.

saveWorkbook(wb2, file = "excel_test.xlsx", overwrite = TRUE)

after `saveWorkbook`, the column names disappeared from the file. I mean first row became empty, because the original file had column names. — Aliton Oliveira, Oct 20 '21 at 17:01

score 0 · Answer 2 · answered Aug 07 '19 at 12:17

0

Write different dataframes to different sheets.

library(xlsx)
write.xlsx(dataframe1, file="filename.xlsx", sheetName="sheet1")
write.xlsx(dataframe2, file="filename.xlsx", sheetName="sheet2", append=TRUE)

answered Aug 07 '19 at 12:17

ASH

20,759
19
87
200

4

This works for xlsx, but not for openxlsx as explained above. Openxlsx has the advantage it doesn't require Java to be installed. – Peurke Jul 27 '20 at 19:00

score 0 · Answer 3 · answered Apr 16 '21 at 16:13

You can simply use a named list of dataframes:

library(openxlsx)

df_lst <- setNames(list(tbl.messages, tbl.dic.dados), as.list(c(format(Sys.time(), '%d-%m-%y'), 'Dicionario_Dados')))

write.xlsx( df_lst, file = paste0('.\\2_Datasets\\messages_',dt.escrita,'.xlsx') )

score 0 · Answer 4 · answered Dec 28 '22 at 09:32

0

The write.xlsx function of the xlsx package does have an append argument

answered Dec 28 '22 at 09:32

emiecha

1

openxlsx::write.xlsx overwriting existing worksheet instead append

4 Answers4

Linked