5

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!

ikempf
  • 153
  • 1
  • 10
  • What's the purpose of doing so, as long as you would be getting the same result at the end? Updating files in most programming languages works like that: "read->modify->write". – Taher A. Ghaleb Nov 19 '19 at 16:38
  • Hi! If I update the entire file all of the formatting and comments etc in the excel file are gone. Because I do not want that I am looking for a way to just update single cells, that way the rest of the document should (in my theory) remain untouched. Is this possible? – ikempf Nov 20 '19 at 07:57
  • 1
    I see your point. Thanks for the clarification. Please have a look at my answer where I use `openxlsx` to do the job. Hope you find it useful. – Taher A. Ghaleb Nov 20 '19 at 17:02

2 Answers2

5

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.

Taher A. Ghaleb
  • 5,120
  • 5
  • 31
  • 44
  • 1
    This will work only on `Windows`. I am running `Ubuntu` with `libreoffice` and it does not work. I just commented to inform others. – Guilherme Parreira Mar 22 '21 at 19:00
  • On a Mac running Catalina, this approach produces a broken XLSX file that Excel tries to recover and ends up deleting my content of interest. – mattador May 09 '21 at 21:18
0

The solution I used works as follows:

  1. Create an excel file with some data.
  2. Open the excel file in R Studio by using:
  File <- excel_file #path to excel file "Data_Analysis.xlsx" for example
  sheet <- "Info"
  wb <- openxlsx::loadWorkbook(file = File)

  #Read a section of a sheet called "Info" from the Excel File
  Data_Info <- openxlsx::read.xlsx(wb, sheet = sheet,
                                   colNames = FALSE, #Column Names are there
                                   rows = seq(1:4), #Copy out these rows
                                   cols = seq(1:5)) #Copy out these columns
  1. The user can now perform all the manipulations on the dataframe Data_Info which he wants.
  2. To save the changed dataframe (NEW_DATAFRAME_TO_BE_SAVED) back to the excel file, use this code:

  openxlsx::writeData(wb, sheet = sheet, x = NEW_DATAFRAME_TO_BE_SAVED,
            startCol = "A",
            startRow = 2,
            colNames = FALSE) # Do not print Header Line with Column Names
  openxlsx::saveWorkbook(wb2, File, overwrite = TRUE)

Be carful with this last step as it will overwrite the previous file. Also note that the last step only works if the excel file is not currently open, otherwise an error/warning will occur.

ikempf
  • 153
  • 1
  • 10