How can you change single entries in an excel file with R and not the entire data sheet?

Question

I have an excel file with some data in it. I want to use R to run some statistics on said sheet and then save some values into different columns in the original Excel Sheet. Is there any way to do this without always "overwriting" the whole excel file?

My_data <- read_xlsx("MeasurementData.xlsx", sheet = "Data_Overview")
data$Column1[1] = "result"
write.xlsx(My_data, file="MeasurementData.xlsx", sheetname="Data_Overview" )

So what I am attempting to do with this code is opening my xlsx file, changing one entry of it to "result" and then rewriting the whole slightly changed dataframe into the xlsx file. However what I want is to not rewrite the entire file but only overwrite/replace the changed entries.

Is there any way to do this? Thanks!

Answer 1

I would suggest that you use the openxlsx package instead, where you can update specific cells in a specific sheet without corrupting the other sheets in the file.

Here is an example:

install.packages("openxlsx")
library(openxlsx)
wb <- loadWorkbook("https://github.com/awalker89/openxlsx/files/744103/template.xlsx")

writeData(wb, sheet = "Iris Data", x = head(iris, 20))
saveWorkbook(wb, "populated_template.xlsx")
openXL("populated_template.xlsx")

As you will see, formatting is untouched and there is another sheet in the file that contains a plot for the populated data is also intact.

You can set x to a single value (as in your example) and set the position as you like (using startCol and startRow.

Hope you find it useful.