R: Append a worksheet to an excel workbook without reading the entire workbook

Question

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.

Frustum

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. – Linus

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. – Frustum

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. – Linus

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. – Frustum

Answer 1

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.

Butterwort

This looks like the perfect solution, thanks so much! Will give it a try and let you know if it works. – Frustum

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. – Frustum

See this answer for how to increase your Java heap space. – Butterwort

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. – Frustum

Strange. I'm not sure what's causing that. – Butterwort

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... – Frustum

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, cran.r-project.org/web/packages/xlsx/xlsx.pdf, says that append = true leads the file to be read from the disk. – Frustum

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. – Butterwort