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

r excel openxlsx
Nipun Arora picture Nipun Arora · Dec 9, 2015 · Viewed 25.9k times · Source

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?

Answer

R. Schifini picture R. Schifini · Dec 12, 2015

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.

Related questions

Read multiple xlsx files with multiple sheets into one R data frame

I have been reading up on how to read and combine multiple xlsx files into one R data frame and have come across some very good suggestions like, How to read multiple xlsx file in R using loop with specific …

Read More
R - Autofit Excel column width

How do I autofit the column width using openxlsx? One of my columns has a date variable (eg. 21-08-2017) and if copied using ctrl+c from Excel, and pasted normally elsewhere, it shows like #######(if column width is increased …

Read More
Export data from R to Excel

I am writing codes to export database from R into Excel, I have been trying others codes including: write.table(ALBERTA1, "D:/ALBERTA1.txt", sep="\t") write.csv(ALBERTA1,":\ALBERTA1.csv") your_filename_in_R = read.csv("ALBERTA1.csv") your_…

Read More