CODEWITHSUNDEEP
rexcelxlconnect
Archimeow
Archimeow

Reputation: 240

Using XLConnect to write data to excel template gives empty cell error

I am using the R package XLConnect to write data frames to an existing excel worksheet in an existing workbook. The excel workbook has a worksheet for the raw data, which I populate using writeWorksheet() in R, and another worksheet for the formatted data which references the raw data worksheet and performs calculations. When I write my data to the raw data worksheet in R, however, the formatted data worksheet does not update and gives an error that "Formula refers to empty cell", even though those cells have data in them. I'm uncertain if this error is due to R and XLConnect or something in my workbook. When I simply copy and paste my data directly into the cells in my Raw Data Import worksheet I do not receive an error. Please see example below, and thank you for your help:

In R:

library(XLConnect)

# Creating first data frame
L3 <- LETTERS[1:3]
fac <- sample(L3, 10, replace = TRUE)
(d <- data.frame(x = 1, y = 1:10, fac = fac))
df.1<-data.frame(1, 1:10, sample(L3, 10, replace = TRUE))

# Creating second data frame
L4 <- LETTERS[4:7]
fac <- sample(L4, 10, replace = TRUE)
(d <- data.frame(x = 1, y = 1:10, fac = fac))
df.2<-data.frame(1, 1:10, sample(L4, 10, replace = TRUE))

# Reading in workbook
wb      <- loadWorkbook(xlname)
wbnames <- as.vector(getSheets(wb)) # where wbnames is of length two
[1] "Raw Data Import"  [2] "Formatted Data"

# Writing df.1 and df.2 to specific locations in Raw Data Import worksheet
writeWorksheet(wb,df.1,sheet=wbnames[1],startRow=3,header=F)
writeWorksheet(wb,df.2,sheet=wbnames[1],startRow=15,header=F)

# Saving workbook
saveWorkbook(wb)

Upvotes: 4

Views: 3043

Answers (2)

COrtega
COrtega

Reputation: 146

You can use the XLConnect function setForceFormulaRecalculation(). This forces Excel to recalculate formula values upon opening the worksheet. The second argument allows you to specify a sheet to recalculate. If it is set to "*", it will recalculate all of the formulas in the workbook.

wb      <- loadWorkbook(xlname)
wbnames <- as.vector(getSheets(wb))
writeWorksheet(wb,df.1,sheet=wbnames[1],startRow=3,header=F)
writeWorksheet(wb,df.2,sheet=wbnames[1],startRow=15,header=F)
setForceFormulaRecalculation(wb,"*",TRUE)
saveWorkbook(wb,'~/test.xls')

Upvotes: 4

IRTFM
IRTFM

Reputation: 263301

I had no success until I added a createSheet operation for each sheet. If you want to use existing sheet information, then look at getSheets and ?getSheetPos.

> wb      <- loadWorkbook('~/test.xls')
> wbnames <- as.vector(getSheets(wb)) 
> createSheet(wb, "test1"); createSheet(wb, 'test2')
> writeWorksheet(wb,df.1,sheet='test1',startRow=3,header=F)
> writeWorksheet(wb,df.2,sheet='test2',startRow=15,header=F)
> saveWorkbook(wb,'~/test.xls')

When I later ran your code I saw that both dataframes got written to Sheet1. If I interposed a saveWorkbook-operation I got the data on different sheets:

> writeWorksheet(wb,df.1,sheet='Sheet1',startRow=3,header=F); saveWorkbook(wb,'~/test.xls')
> writeWorksheet(wb,df.2,sheet='Sheet2',startRow=15,header=F)
> saveWorkbook(wb,'~/test.xls')

XLConnect is a quasi-commercial product and I don't think they encourage questions to SO, so you might want to contact Mirai Solutions, GMBH.

Upvotes: 0

Related Questions