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

23
votes

I am using "openxlsx" package to read and write excel files. I have a fixed file with a sheet called "Data" which is used by formulas in other sheets. I want to update this Data sheet without touching the other. I am trying the following code:

write.xlsx(x = Rev_4, file = "Revenue.xlsx", sheetName="Data")

But this erases the excel file and creates a new one with just the new data in the "Data" sheet while all else gets deleted. Any Advice?

2
rexcelopenxlsx
I cannot test this in the computer I am using at the moment but have you tried using the argument append = TRUE? – leosz
"append=T" option seems to be absent with the "openxlsx" package. I know it is present in the java based "xlsx" package, but the package is slow and quickly runs out of memory with heavier files. I have a more tedious way around it, but it will be great if i could have an "openxlsx" based solution. – Nipun Arora
openxlsx is a pretty poorly implemented package. YOu have to do all sorts of extra things to add a worksheet to an existing workbook if you want the same code to work for the first worksheet you are creating. – jzadra

2 Answers

29
votes

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.

1
votes

I've found this package. It depends on openxlsx and helps to insert many sheets on a xlsx file. Maybe it makes easier:

Package documentation

# However, be careful, the function xlsx2dfs assumes
# that all sheets contain simple tables. If that is not the case,
# use the accepted answer!
dfs <- xlsx2dfs("Revenue.xlsx") # all sheets of file as list of dfs
dfs["Data"] <- Rev_4   # replace df of sheet "Data" by updated df Rev_4
dfs2xlsx(dfs, "Revenue.xlsx") # this overwrites the existing file! cave!