Help with recalculating formulas

[Conor]

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

[Adrian Dragulescu]

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.

[Florent D.]

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,
>
> 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)****
>
> ** **
>
> Seehttp://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.****

[Adrian Dragulescu]

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

[fredinstl]

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

[Adrian Dragulescu]

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)

 

  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)

 

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

 

 

--
You received this message because you are subscribed to the Google Groups "R-package-xlsx" group.
To unsubscribe from this group and stop receiving emails from it, send an email to r-package-xls...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[a.kra...@gmail.com]

Hi

I am hoping you could help me to find a solution to my problem. I have a workbook with 3 spreadsheets, styles and formulas and I need to load them into R add values to specific cells in the spreadsheets and the save the workbook. The challenge is to save it with formulas so when I open the workbook it will recalculate itself.

Thanks