2

I have a 26 mb excel workbook to which I am trying to add a 42 kb worksheet. Using the openxlsx package, I have the following code:

wb_object <- loadWorkbook(to_name2)
addWorksheet(wb_object, "New Data")
writeData(wb_object, sheet = "New Data", m_data)
saveWorkbook(wb_object, to_name2, overwrite = TRUE)

What I have noticed is that this code takes about 2 minutes to execute. I believe R is reading in the entire 26 mb file and then appending the 42 kb worksheet. Is there any way to append the 42 kb worksheet to the 26 mb workbook without having to read in the 26 mb file? Would save 2 minutes per run.

Rahul Nallappa
  • 181
  • 2
  • 9
  • 1
    Just for clarity, the work you're doing is all in excel so you can't just do it in R? If you want to preserve everything in the workbook, so far as I know, you have to read in the whole workbook to add a page and rewrite. – Badger Mar 08 '18 at 20:40
  • 1
    Thanks for the quick response, Badger. The 26 mb file that I created in excel has significant conditional formatting and would be a difficult task to try to create the entire workbook itself in R. I do want to preserve everything else in the workbook, so if what you're saying is true, then I guess I have to read in the entire workbook. I suspected this, but thanks so much for confirming. – Rahul Nallappa Mar 08 '18 at 20:46
  • 1
    I'm no expert in R - Excel interactions, once I went to R I severed ties with Excel, so it's not unlikely that I'm wrong. This has just been my experience! 2 minutes is pretty uncanny though for such a tiny file. – Badger Mar 08 '18 at 20:49
  • Gotcha. Yeah, now that you mention it, 26 mb isn't that large a file to require 2 minutes to read. Maybe it's a limitation on the openxlsx end. – Rahul Nallappa Mar 08 '18 at 21:01

1 Answers1

2

I generally use openxlsx, but I'm not sure if openxlsx has a way to add a worksheet to an Excel file without first loading the Excel workbook into R. However, with the xlsx package, you can add a new worksheet without loading the Excel file. For example, if your file is "test.xlsx", then you could do:

library(xlsx)

write.xlsx(new_data, "test.xlsx", sheetName="New_Sheet", append=TRUE)

If I need to save anything in an Excel file, I generally try to do everything in R and then write whatever needs to go into the Excel file at the end. However, if you need to add to an existing Excel file, the above code provides an option to do that.

eipi10
  • 91,525
  • 24
  • 209
  • 285
  • This looks like the perfect solution, thanks so much! Will give it a try and let you know if it works. – Rahul Nallappa Mar 08 '18 at 21:10
  • I'm getting the following error: Error in .jcall("RJavaTools", "Ljava/lang/Object;", "invokeMethod", cl, : java.lang.OutOfMemoryError: Java heap space Seems odd that I'm getting this error, considering that I'm not loading the excel and thus shouldn't be using much RAM. – Rahul Nallappa Mar 08 '18 at 22:23
  • See [**this answer**](https://stackoverflow.com/a/44143679/496488) for how to increase your Java heap space. – eipi10 Mar 08 '18 at 22:35
  • Thanks for the quick answer. I had found that link independently, and I increased RAM allotment to 8gb and the program successfully executed. However, it took 4 minutes to execute, even slower than last time. – Rahul Nallappa Mar 08 '18 at 22:35
  • Strange. I'm not sure what's causing that. – eipi10 Mar 08 '18 at 22:36
  • No worries -- it does seem strange. Weirdly, while it's being executed, the file goes to 0 kb. When it's finally executed, it goes back to 22.7 mb, which is smaller than the original file size (26 mb). I wonder if it's rewriting the entire file. I did include append = true, so it shouldn't be... – Rahul Nallappa Mar 08 '18 at 22:43
  • last question, apologies. Are you sure that xlsx bypasses reading the file in the case that append = true? Page 38 of the official documentation here, https://cran.r-project.org/web/packages/xlsx/xlsx.pdf, says that append = true leads the file to be read from the disk. – Rahul Nallappa Mar 08 '18 at 22:47
  • I don't know if it bypasses reading the file. I didn't time it, but the time to complete the operation didn't seem extraordinarily long. – eipi10 Mar 08 '18 at 22:50