使用XLConnect将数据写入excel模板会产生空单元格错误

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:

我正在使用R包XLConnect将数据帧写入现有工作簿中的现有excel工作表中。excel工作簿有一个原始数据的工作表，我使用R中的writeWorksheet()填充该工作表，还有一个格式化数据的工作表，它引用原始数据工作表并执行计算。然而，当我将数据写到R中的原始数据工作表时，格式化的数据工作表不会更新，并给出一个错误，即“公式指的是空单元格”，即使这些单元格中有数据。我不确定这个错误是由于R和XLConnect还是在我的工作簿中。当我简单地将数据复制并粘贴到原始数据导入工作表中的单元格中时，我不会收到错误。请看下面的例子，感谢您的帮助:

In R:

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)

2 个解决方案

#1

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.

您可以使用XLConnect函数setforceschemarecalculation()。这迫使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')

#2

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.

在我为每张表单添加一个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')

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:

当我稍后运行您的代码时，我看到这两个dataframes都被写入了Sheet1。如果我插入一个保存工作簿操作，我会得到不同表格上的数据:

> 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.

XLConnect是一个准商用产品，我不认为它鼓励人们提出问题，所以您可能想要联系Mirai Solutions GMBH, GMBH是一家高水平的企业。