How to force Excel to recalculate on opening xlsx

[t***@justsystems.com]

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?

[Nick Burch]

Post by t***@justsystems.com
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

[Toshihiko Saka]

Hi Nick,

Post by Nick Burch
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.

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

[Nick Burch]

Post by Toshihiko Saka
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.
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

[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

[Nick Burch]

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

[dickschoeller]

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



--
View this message in context: http://apache-poi.1045710.n5.nabble.com/How-to-force-Excel-to-recalculate-on-opening-xlsx-tp3249595p4311197.html
Sent from the POI - User mailing list archive at Nabble.com.

[Nick Burch]

Post by dickschoeller
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

[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


--
View this message in context: http://apache-poi.1045710.n5.nabble.com/How-to-force-Excel-to-recalculate-on-opening-xlsx-tp3249595p4329966.html
Sent from the POI - User mailing list archive at Nabble.com.

[Nick Burch]

Post by 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"/>

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

Nick

[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


--
View this message in context: http://apache-poi.1045710.n5.nabble.com/How-to-force-Excel-to-recalculate-on-opening-xlsx-tp3249595p4330833.html
Sent from the POI - User mailing list archive at Nabble.com.

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

--
View this message in context: http://apache-poi.1045710.n5.nabble.com/How-to-force-Excel-to-recalculate-on-opening-xlsx-tp3249595p4331015.html
Sent from the POI - User mailing list archive at Nabble.com.

[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


--
View this message in context: http://apache-poi.1045710.n5.nabble.com/How-to-force-Excel-to-recalculate-on-opening-xlsx-tp3249595p4333743.html
Sent from the POI - User mailing list archive at Nabble.com.

[dickschoeller]

Never mind!

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

Dick


--
View this message in context: http://apache-poi.1045710.n5.nabble.com/How-to-force-Excel-to-recalculate-on-opening-xlsx-tp3249595p4333751.html
Sent from the POI - User mailing list archive at Nabble.com.

[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

--
View this message in context: http://apache-poi.1045710.n5.nabble.com/How-to-force-Excel-to-recalculate-on-opening-xlsx-tp3249595p4333856.html
Sent from the POI - User mailing list archive at Nabble.com.