Quantcast

How to force Excel to recalculate on opening xlsx

classic Classic list List threaded Threaded
16 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

How to force Excel to recalculate on opening xlsx

toshihiko.saka
Hello,

I'm planning to develop an application which sets values of cells in
Excel 2007 file (xlsx) with POI 3.7.

After setting values of cells with POI, I am expecting that formulas
referring to the edited cells will be recalculated when I open it in
Excel 2007, but actually it doesn't work.

I'm doing as follows:
1) I created a new spreadsheet with Excel 2007, set "0" to cell A1 and "
=A1*2" to cell B1, and saved it as "temp.xlsx".
2) The POI program read temp.xlsx and changed cell A1 value to "2".
3) When I opened temp.xlsx in Excel 2007, I expected cell B1 to show "
4", but it was "0". Forcing a recalculation in Excel didn't fix it
either.

I know that in case of "xls" HSSFSheet.setForceFormulaRecalculation() is
available to force Excel to recalculate on opening xls file. But
XSSFSheet does not seem to have the same method.

How can I force Excel to recalculate on opening xlsx file?

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: How to force Excel to recalculate on opening xlsx

Nick Burch-11
On Thu, 4 Nov 2010, [hidden email] wrote:
> After setting values of cells with POI, I am expecting that formulas
> referring to the edited cells will be recalculated when I open it in
> Excel 2007, but actually it doesn't work.

You could just get POI to calculate the values for you:
  http://poi.apache.org/spreadsheet/eval.html

Nick

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: How to force Excel to recalculate on opening xlsx

toshihiko.saka
Hi Nick,

> You could just get POI to calculate the values for you:
>   http://poi.apache.org/spreadsheet/eval.html

I had already tried this, but unfortunately I came across a phenomenon
which gave me an impression that the POI formula evaluation might still
have some incorrect behaviors.

I reported this as next:
https://issues.apache.org/bugzilla/show_bug.cgi?id=50209

So I thought that getting Excel to caluculate formula is more reliable.

But, I see that there is no way like HSSFSheet.
setForceFormulaRecalculation() in case of XSSF.

Thanks for your help.

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: How to force Excel to recalculate on opening xlsx

Mark Beardsley
As you are targetting the more recent version of Exce; - Office 2007 on - you do have another option, a macro. It is possible to create a macro within a workbook and stipulate that it be run when the workbook is opened; that macro can force a recalculation of all forumals in the workbook using a single command - Calculate. Excel 2007 intorduced the xlsm file which can contain signed macros and avoid the problem of the user being asked if macros should be run or not.

So, you can create a workbook that includes such a macro and use this as the basis for the workbook you create using POI.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: How to force Excel to recalculate on opening xlsx

Nick Burch-11
In reply to this post by toshihiko.saka
On Fri, 5 Nov 2010, Toshihiko Saka wrote:
> I had already tried this, but unfortunately I came across a phenomenon
> which gave me an impression that the POI formula evaluation might still
> have some incorrect behaviors.
>
> I reported this as next:
> https://issues.apache.org/bugzilla/show_bug.cgi?id=50209

We do have some unit tests which I might have expected to pick up this
issue. If you have a look at TestFormulasFromSpreadsheet and
FormulaEvalTestData.xls you can see how we do most of our formula testing,
and there's also the TestSubtotal class.

If we're missing something from those unit tests that is tripping you up,
please do submit a patch to enhance the unit tests to cover your use case!

Secondly, fixing up the formula evaluator isn't nearly as scary as you
might fear. Yegor did a great talk yesterday on how the formula evaluator
works, how to extend it, how to write functions etc. It's only a quick 20
minute talk, but it's well worth a look.

Yegor - I can't spot the talk on your people.apache.org site, and it
won't be posted on the apachecon site for another day or so. Could you
upload it somewhere and share the link?

Cheers
Nick

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: How to force Excel to recalculate on opening xlsx

Steven Lira
I have the same issue and I am looking for a setForceFormulaRecalculation() in the newer xlsx format workbook.  The workbooks I am working with are fairly complex and have some functions like TRIMMEAN() which POI does not currently support.  Is there any possibility of setForceFormulaRecalculation() being added to XSSFSheet?

Thanks
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: How to force Excel to recalculate on opening xlsx

Nick Burch-11
On Mon, 24 Jan 2011, Steven Lira wrote:
> Is there any possibility of setForceFormulaRecalculation() being added
> to XSSFSheet?

I don't know if the .xlsx format supports it? If you can figure out what
XML element needs to be set, we can add the support to POI, but I've no
idea what that needs to be...

Nick

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: How to force Excel to recalculate on opening xlsx

dickschoeller
A quick look seems to indicate the fullCalcOnLoad attribute of x:sheetcalcpr, which is a child of the sheet.

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: How to force Excel to recalculate on opening xlsx

Nick Burch-11
On Mon, 18 Apr 2011, dickschoeller wrote:
> A quick look seems to indicate the fullCalcOnLoad attribute of
> x:sheetcalcpr, which is a child of the sheet.

Are you able to do a quick check on this? I'd suggest unzipping a .xlsx
file, adding the flag, then changing several cells that formulas depend
on. Open that, and see if excel updates the formulas for you
automatically?

If it does, we can add a method that lets you set this

Thanks
Nick

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: How to force Excel to recalculate on opening xlsx

dickschoeller
Adding the following to the worksheet worked.  But it did seem to be order dependent.  I had to put it in after <sheetData></sheetData>.

<sheetCalcPr fullCalcOnLoad="true"/>

Dick
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: How to force Excel to recalculate on opening xlsx

Nick Burch-11
On Wed, 20 Apr 2011, dickschoeller wrote:
> Adding the following to the worksheet worked.  But it did seem to be order
> dependent.  I had to put it in after <sheetData></sheetData>.
>
> <sheetCalcPr fullCalcOnLoad="true"/>

Thanks for the info. I've added support for getting/setting this in
r1095667, do you want to give that a whirl?

Nick

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: How to force Excel to recalculate on opening xlsx

dickschoeller
I presume that to do this I would need to pull the sources down with svn or git and then build.  It's not something I'm set up to do at work.  So, that may take a little bit.

Dick
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: How to force Excel to recalculate on opening xlsx

Mark Beardsley
Hello Dick,

No, you will not have to build the library yourself. Take a look here - http://encore.torchbox.com/poi-cvs-build/ - and yo will find nightly builds. Not too sure which one you will need but I guess if you grab the latest, that should suffice.

Yours

Mark B

PS. The list is quite long and you will need to download a few jars from it.

http://encore.torchbox.com/poi-cvs-build/poi-3.8-beta3-20110421.jar
http://encore.torchbox.com/poi-cvs-build/poi-dependencies-3.8-beta3-20110421.zip
http://encore.torchbox.com/poi-cvs-build/poi-ooxml-3.8-beta3-20110421.jar
http://encore.torchbox.com/poi-cvs-build/poi-ooxml-schemas-3.8-beta3-20110421.jar

are all you need, at least I think so.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: How to force Excel to recalculate on opening xlsx

dickschoeller
Hi Mark,

I took the build that you pointed to and hooked it up in my Eclipse environment.  I don't see any sign of the method setForceFormulaRecalculation on either Sheet or XSSFSheet.  So, I can't try it using that build.

So, how would you recommend I approach this?

BTW, I identified a whole area of formula evaluation that you can't get at without having this feature or an even more substantial bit of work elsewhere.  The templates that I am working with have data validation formulas.  Because there is no access to the data validation that is already on a sheet from POI, there is no way to iterate through those and force them to be evaluated before writing the file out.

Thanks,
Dick
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: How to force Excel to recalculate on opening xlsx

dickschoeller
Never mind!  

I see that it is in the source in the 20110422 build.  So, I'll grab that and try again.

Dick
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: How to force Excel to recalculate on opening xlsx

dickschoeller
Woo hoo!  It works like a charm.

Now all I have to do is to deal with my product owner about the 3.8 release schedule.

Thanks for the great work!

Dick
Loading...