I have an Excel workbook of which I want to edit/fill some particular cells using R, without changing any of the formatting.
So far I've tried XLConnect package and it seems it could do what I'm looking for, I just didn't find a way to do it.
My straightforward approach to the problem:
wb <- loadWorkbook("file1.xls")
data1 <- readWorksheet(wb, "Sheet1", header=TRUE)
## adding a value to a particular cell:
data1[11,12] <- 3.2
## rewriting old data:
writeWorksheet(wb, data1, "Sheet1")
saveWorkbook(wb, "new_file1.xls")
However, this way the new workbook loses all of the previous formatting (merged cells, formulas, etc).
Is there a way to change values in some of the cells without losing any of the formatting of the remaining sheet?
Here is an example using R to automate Excel.
library(RDCOMClient)
xlApp <- COMCreate("Excel.Application")
wb <- xlApp[["Workbooks"]]$Open("file.1.xls")
sheet <- wb$Worksheets("Sheet1")
# change the value of a single cell
cell <- sheet$Cells(11,12)
cell[["Value"]] <- 3.1
# change the value of a range
range <- sheet$Range("A1:F1")
range[["Value"]] <- paste("Col",1:6,sep="-")
wb$Save() # save the workbook
wb$SaveAS("new.file.xls") # save as a new workbook
xlApp$Quit() # close Excel