4

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?

runr
  • 1,142
  • 1
  • 9
  • 25
  • This is not making a lot of sense. If you don't want to replace a cell's contents (such as its formula) then ... don't do it. If are trying to put things into one part of a merged cell then it would seem desirable to break apart the merging. – IRTFM Nov 14 '14 at 18:41
  • If you're running Windows, (and you have Excel installed, obviously), you can automate Excel (and anything else...) from R using the `RDCOMClient` package. – jlhoward Nov 14 '14 at 18:48
  • Please read the help pages for the package and note that the `?writeWorksheet` page does link to `?writeNamedRegion`. (Wouldn't you have expected a function named 'writeWorksheet' to replace the entire worksheet?) – IRTFM Nov 14 '14 at 18:54
  • @BondedDust I *do* want to replace a cell's content, but I want to keep all of the previous formatting of the remaining sheet - the same as it was before. Like, if I had a template with blank cells that I wished to fill. By using `writeWorksheet` I write all of the information as a data.frame, so the all of the formats (merged cells, text sizes, col/row sizes, etc) are gone. – runr Nov 14 '14 at 19:11
  • Exactly. If you don't want to replace an entire worksheet then don't use a function named `writeWorksheet`. – IRTFM Nov 14 '14 at 19:33
  • @BondedDust is there a way to replace a value of only one cell using *XLConnect* (or any other for that matter) package? – runr Nov 14 '14 at 19:36
  • 1
    I think there is, but your question has no example file and my inclination to build a test case is low. – IRTFM Nov 14 '14 at 19:51
  • See XLConnect's setStyleAction. The style actions NONE or DATA_FORMAT_ONLY should do what you are looking for. – Martin Studer Nov 16 '14 at 21:41

3 Answers3

9

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
jlhoward
  • 58,004
  • 7
  • 97
  • 140
  • 3
    Just a note: the RDCOMClient package only works with Windows. This was in the comments, but I wanted to make it clear so Unix people don't waste their time. – CephBirk May 27 '16 at 22:09
  • what if there's a pop up that asks if i want to update links? is there anyway to click through this in R? – Rafael Jun 22 '17 at 20:54
6

If you dont need to use formula, you have 2 possible solutions.

You can use the {xlsx} package :

library(xlsx)
xlsx::write.xlsx(x = head(iris),file = "source3.xlsx",sheetName = "A")
hop3 <- xlsx::loadWorkbook(file = "source3.xlsx")
sheets <- getSheets(hop3)
rows  <- getRows(sheets$A,rowIndex = 2)   # get all the rows
cc <- getCells(rows,colIndex = 3) 
xlsx::setCellValue(cc[[1]],value = "54321")
hop3$setForceFormulaRecalculation(TRUE)
xlsx::saveWorkbook(hop3,file = "output3.xlsx")

You also can use {XLconnect}

library(XLConnect)    
XLConnect::writeWorksheetToFile(file = "source2.xlsx",data = head(iris),sheet="A")
hop2 <- XLConnect::loadWorkbook(file = "source2.xlsx")
createName(hop2, name = "plop", formula = "A!C2")
writeNamedRegion(hop2, 12345, name = "plop", header = FALSE)

Regards

Vincent Guyader
  • 2,927
  • 1
  • 26
  • 43
4

Using the XLC$STYLE_ACTION.NONE style action should add your data without changing any formatting:

data1 <- readWorksheetFromFile("file1.xls", "Sheet1")

## adding a value to a particular cell:
data1[11,12] <- 3.2 

## rewriting old data:
writeWorksheetToFile("file1.xls", data1, "Sheet1", styleAction = XLC$STYLE_ACTION.NONE)

Thanks to Martin for the suggestion to look into this in the comments.

CephBirk
  • 6,422
  • 5
  • 56
  • 74