使用R中的xlsx软件包更新excel电子表格数据 [英] updating excel spreadsheet figures using the xlsx package in R

问题描述

我有一个关于R中的xlsx包的查询。我知道如何使用包中的大多数函数，直到现在还没有遇到任何问题。我将首先显示我的代码，然后会提出我的问题。

I have a query relating to the xlsx package in R. I know how to use the majority of the functions in the package and have not run into any problems until now. I will first display my code and then will ask my question.

#Code-Section1: 
library(xlsx) 
data1<-iris 
data1<-data1[,c(5,1:4)] 
wb <- createWorkbook() 
sheet1 <- createSheet(wb, sheetName="Sheet1") 
addDataFrame(data1, sheet1, startRow=1, startColumn=1,row.names=FALSE)  
saveWorkbook(wb, file="test.xlsx")  
rm(data1);rm(sheet1);rm(wb) 
#Code-Section1-end: 

这部分只是将虹膜数据集放在一个名为Sheet1的Sheet中，在excel电子表格test.xlsx中。现在在excel中，我决定通过添加一行添加更多内容到电子表格，所以excel电子表格的最后两行是：

This part simply takes the iris data set and put it into a Sheet called Sheet1 in a excel spreadsheet called test.xlsx. Now in excel I decide to add further contents to the spread sheet by adding a total row, so the final two rows of the excel spreadsheet are:

virginica   5.9     3.0     5.1     1.8
            876.5   458.6   563.7   179.9

另一个我做的事情是向电子表格添加4列，其中包含每个数字在行总数中的百分比。接下来，我想将test.xlsx加载到R工作簿中。我定义一个名为temp的新数据框，其中包含一些随机的正态值。我的目的是更新test.xlsx文件中的数字，以便行总数和百分比也随之改变。根据rnorm（150,5,1）值的输出，更新的电子表格的最后两行应该有所不同。我将数据框保存到名为testa.xlsx的新电子表格中。值被更新，但由于某些原因，即使其单元格的值仍然为= sum（b2：b151）或= b2 / b $ 152，行总数和百分比保持不变。

Another thing I do is add 4 more columns to the spreadsheet containing the percentage of each figure out of the row totals. Next, I want to load the test.xlsx into a R workbook. I define a new data frame called temp with some random normal values. My intention is to update the figures in the test.xlsx file so that the row totals and the percentages will subsequently change as well. The last two rows of the updated spreadsheet should be something different depending on the output of the rnorm(150,5,1) values. I save the data frame into a new spreadsheet called testa.xlsx. The values are updated but for some reason the row totals and the percentages remain unchanged even though the values of their cell for example is still "=sum(b2:b151)" or "=b2/b$152".

#Code-Section2: 
temp <- data.frame(Sepal.Length=rnorm(150,5,1), Sepal.Width=rnorm(150,5,1), Petal.Length=rnorm(150,5,1), Petal.Width=rnorm(150,5,1)) 
wb<-loadWorkbook("test.xlsx") 
f<-getSheets(wb)[[1]] 
addDataFrame(temp, sheet=f, startRow=2, startColumn=2,row.names=FALSE,col.names=FALSE) 
saveWorkbook(wb, file="testa.xlsx") 
#Code-Section2-end: 

因此，我的问题是如何加载工作簿，更新数字，使其中具有公式的单元格将被随后更新，然后保存到新的excel文件。我可以用另一种方式来做，但是它需要更多的步骤，我不介意做，但只是更新数字，所以其他细胞会改变将是如此方便。再次像每一个帖子一样，请原谅任何语法错误，如果您认为有必要，请随时更改任何此消息。

My question is thus, how do I load a workbook, update the figures such that the cells that have formulas in them will be subsequently updated and then saved to a new excel file. I can probably do it in another way but it would require more steps which I do not mind doing but simply updating figures so other cells would change would be so handy. Again as with every post I do, please forgive any grammar mistakes and feel free to change any of this message if you think it is necessary.

推荐答案

在保存工作簿之前（ wb ），添加以下内容：

Before saving your workbook (wb), add the following:

wb$setForceFormulaRecalculation(TRUE)

如果不起作用，还有一个VBA解决方案此处。

If it does not work, there is also a VBA solution here.

这篇关于使用R中的xlsx软件包更新excel电子表格数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！