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.

Joeyjoffre

You might be stuck first reading from that worksheet, rbinding (or otherwise combining) old with new data, then go with the write. Related: community.rstudio.com/t/append-function-of-xlsx-in-openxlsx/… – Vair

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 (github.com/awalker89/openxlsx/issues/135). (In fact, awalker89 has been silent since Oct 2018.) – Vair

Answer 1

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)

Closefisted

after saveWorkbook, the column names disappeared from the file. I mean first row became empty, because the original file had column names. – Bellanca

Answer 2

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)

Diatropism

This works for xlsx, but not for openxlsx as explained above. Openxlsx has the advantage it doesn't require Java to be installed. – Traceytrachea

Answer 3

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

Urquhart

Answer 4

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

Loney