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

Question

I am using the XLConnect R package to write data frames to an existing excel worksheet in an existing workbook. The Excel workbook has a worksheet for raw data, which I fill out using writeWorksheet()R and another worksheet for formatted data, which refers to a worksheet for raw data and performs the calculations. However, when I write my data to the raw data sheet in R, the formatted data sheet is not updated and gives an error that “The formula refers to an empty cell”, although these cells have data in them. I am not sure if this error is related to R and XLConnect or something in my book. When I simply copy and paste my data directly into cells on my Raw Data Import data sheet, I do not receive an error message. Please see the example below and thanks 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)

Answer 1

XLConnect setForceFormulaRecalculation(). Excel . . "*", .

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')

Answer 2

, createSheet . , getSheets ?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')

, , Sheet1. saveWorkbook, :

> 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 - - , , SO, Mirai Solutions, GMBH.