31

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?

zx8754
  • 52,746
  • 12
  • 114
  • 209
Nipun Arora
  • 553
  • 1
  • 5
  • 9
  • I cannot test this in the computer I am using at the moment but have you tried using the argument `append = TRUE`? – leosz Dec 09 '15 at 07:59
  • 4
    "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 Dec 12 '15 at 16:38
  • 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 Sep 07 '18 at 18:28

2 Answers2

37

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.

R. Schifini
  • 9,085
  • 2
  • 26
  • 32
  • 1
    This corrupts my file. – Brash Equilibrium Apr 30 '18 at 21:54
  • @BrashEquilibrium just tried it with Excel 2013 and the latest version of `openxlsx` and it still works for me. – R. Schifini May 01 '18 at 03:37
  • That’s odd. I just ended up using writexl to construct a Woekbook anew. – Brash Equilibrium May 01 '18 at 15:27
  • 2
    This doesn't work if the workbook doesn't exist for the first sheet. – jzadra Sep 07 '18 at 18:27
  • @jzadra I tried changing the order of worksheets and this still writes the data on the intended sheet. – R. Schifini Sep 07 '18 at 19:47
  • @R.Schifini The issue with my use case is that I don't start with an existing excel file. I just want to save multiple tables to a new file in a loop. Using `write.xlsx()` and specifying a sheet name should only overwrite that sheet if it exists, not the entire workbook. – jzadra Sep 10 '18 at 16:38
  • 3
    `wb <- createWorkbook()` `addWorksheet(wb, sheetName = "test1")` `writeData(wb, sheet = "test1", x = data1)` `addWorksheet(wb, sheetName = "test2")` `writeData(wb, sheet = "test2", x = data2)` `saveWorkbook(wb, "test.xlsx")` – R. Schifini Sep 10 '18 at 19:52
  • @jzadra your problem is different than the question asked. If you try the comment above you will see that you need to create an empty workbook, then start adding worksheets, then the data, loop it as many times as you want, and finallly write the workbook to hdd. – R. Schifini Sep 10 '18 at 19:56
  • A much-improved answer is given in [link](https://stackoverflow.com/questions/27713310/r-easy-way-to-export-multiple-data-frame-to-multiple-excel-worksheets). Using example @R.Schifini used it would be: `DFToExport = list(test1 = data1, test2 = data2)` `write.xlsx(DFToExport, "test.xlsx")` – Malan Kriel Dec 01 '18 at 09:33
  • 1
    This corrupts my file too. Upon further investigation it seems that `openxlsx` will corrupt a workbook if if it contains Excel content it is not built to handle. The devs seems to be updating the package on an as-needed basis as people come to them with corrupted workbooks. – grapestory Aug 25 '21 at 14:16
2

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

library(xlsx2dfs)
# 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!
Martin Gal
  • 16,640
  • 5
  • 21
  • 39
A Neto
  • 233
  • 2
  • 10
  • When answering a question, it is best to write out the suggested code, using the example code/data provided in the original question. Welcome to SO! – Josh Mar 12 '20 at 13:22
  • @Josh I added some code using the code/data of original question. – Gwang-Jin Kim Jun 03 '20 at 10:20
  • 1
    @Gwang-JinKim, thanks. I made my comment because I was asked by the Review Queue to review Americo's answer (possibly his first?), but the details you added will be useful to others. – Josh Jun 04 '20 at 11:27