Help with recalculating formulas | Conor | 3/1/12 1:05 PM | Hi xlxs group,
I'm trying to use this package for a data processing task and I would appreciate some help. I'm pretty much a beginner with R and programming, so there may be a very simple solution here, but I haven't been able to find it. Here is what I'm trying to do: I have a bunch of text files with raw data that needs to be processed and a big excel template that has lots of formulas, lookup tables etc to process the raw data and output it in a useable form. As I've got it set up, it works fine if I copy the raw data and paste it into the spreadsheet, then copy the processed data out to a new spreadsheet using excel. The problem is that I have hundreds of text files and I'd rather not do each one by hand. I've been trying to use the xlsx package in R to execute the following steps: 1) read in a text file 2) load the blank template spreadsheet 3) write the contents of the text file to the appropriate place in the spreadsheet 4) save the spreadsheet with a new name 5) copy the processed data from the appropriate places in the new spreadsheet and write it to a new output sheet I think I'm pretty close to being able to do what I want to, but the problem that I keep running into is that after I've written the text file into the spreadsheet, the formulas that refer to the cells where the raw data is stored do not seem to re-calculate to take account for the new values. Even if I open the spreadsheet in excel and hit 'recalculate' the values stay as they were before writing. I have to actually click on the contents of a cell and then hit enter (without needing to actually change the formula at all) in order to make it notice the new values. This obviously defeats the purpose of what I'm trying to do since I would still need to open each spreadsheet individually. Here is a simplified version of the code that I'm trying to use. The 'template' file here would just be a two sheet workbook where sheet1 has raw data and sheet 2 has some formula that manipulate the raw data in the corresponding cell on sheet 1. d<-seq(from=0,to=100,by=1) wb<-loadWorkbook(".../template.xlsx") sheets<-getSheets(wb) sheet<-sheets[[1]] addDataFrame(d,sheet,col.names=FALSE,row.names=FALSE,startRow=2,startColumn=2) saveWorkbook(wb,".../data1.xlsx") Thanks in advance for any help you can provide! Conor |

Re: Help with recalculating formulas | Adrian Dragulescu | 3/2/12 6:03 PM | Conor, You cannot do it with the current version of the package. It is implemented in the next version of the java POI library 3.8 (still in beta). But you can download it and drop the 3.8 jars into the xlsxjars package in the java/ folder on top of the 3.7 ones. Then before you save your spreadsheet do a wb$setForceFormulaRecalculation(TRUE)
See http://poi.apache.org/spreadsheet/eval.html for more details.
Adrian I’m waiting for the 3.8 version to be released before I add it to the R package. |

Re: Help with recalculating formulas | Florent D. | 4/12/12 4:14 AM | Not an optimal solution, but you can do this while waiting for
Adrian's new release: as your workbook opens, force Excel to recalc all cells via VBA code. You can follow these steps: 1) open your template with Excel 2) press ALT+F11 to enter VBA mode 3) add a module to your project with the following code: Sub Auto_Open() Application.CalculateFull End Sub 4) save your template with the *.xlsm extension (macro enabled) and exit 5) within R, when you save your final product in a file, also make sure to use the *.xlsm extension. Florent. On Mar 2, 10:03 pm, Adrian Dragulescu <adrian.dragule...@gmail.com> wrote: > Conor,> Then before you save your spreadsheet do a**** > > wb$setForceFormulaRecalculation(TRUE)**** > > ** ** > > Seehttp://poi.apache.org/spreadsheet/eval.html for more details. **** > > ** ** >> package.**** |

Re: Help with recalculating formulas | Adrian Dragulescu | 4/12/12 6:51 PM | Version 3.8 for Apache POI has been released a few days ago. I'll try
to find some time and incorporate it over the weekend (or next week) into xlsxjars. I'll push a new version on CRAN afterwards. Cheers, Adrian |

Re: Help with recalculating formulas | Fred Achard | 2/5/15 9:03 AM | Hello, I can't make this to work. my formula appears as text, and only F2 enters set it as calculation. I use R-3.1.1 and xlsx 0.5.7 (just reinstalled it). wb <- createWorkbook() sheet1 <- createSheet(wb, "Sheet1") rows <- createRow(sheet1, 1:10) # 10 rows cells <- createCell(rows, colIndex=1:8) # 8 columns for( r in 1:9) setCellValue( cells[[ r, 1]], r) setCellValue( cells[[ 10, 1]], '=sum(A1:A9)' ) wb$setForceFormulaRecalculation( TRUE) saveWorkbook( wb, file = 'x.xls') any help much appreciated. Thanks. Fred |

Re: Help with recalculating formulas | Adrian Dragulescu | 2/10/15 6:15 PM | Fred,
It is slightly different, see below. I will make some R function wrappers in the next version of the package to facilitate this. Also, you may find the documentation useful http://poi.apache.org/spreadsheet/eval.html Best regards, Adrian require(xlsx)
cell <- cells[[ 10, 1]] cell$setCellFormula( 'SUM(A1:A9)' ) evaluator <- wb$getCreationHelper()$createFormulaEvaluator() #evaluator$evaluateInCell( cells[[10,1]] ) # replaces the formula with the value evaluator$evaluateFormulaCell( cell ) # keeps formula in cell returns the cell type ... cell$getNumericCellValue() saveWorkbook( wb, file = 'out.xlsx' )
-- |