How to Modify an Existing a Sheet in an Excel Workbook Using Openxlsx Package in R

How do I modify an existing a sheet in an Excel Workbook using Openxlsx package in R?

Try this:

wb <- loadWorkbook("Revenue.xlsx")
writeData(wb, sheet = "Data", Rev_4, colNames = F)
saveWorkbook(wb,"Revenue.xlsx",overwrite = T)

You need to load the complete workbook, then modify its data and then save it to disk. With writeData you can also specify the starting row and column. And you could also modify other sections before saving to disk.

How do I modify an existing sheet in Excel using openxlsx (with same lay-out)?

it seems to be related to the use of the two packages together, even if you save before adding the new sheet the headers are missing.

But you can use write.xls from openxlsx package and it works fine, just change this line:

writexl::write_xlsx(list(iris = iris, mtcars = mtcars), "example_modify_exceltab.xlsx")

to:

openxlsx::write.xlsx(list(iris = iris, mtcars = mtcars), "example_modify_exceltab.xlsx")

and your script will run as intended

openxlsx::write.xlsx overwriting existing worksheet instead append

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)

---

Related Topics