You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Steven Lira <li...@comcast.net> on 2011/01/24 16:59:18 UTC
Re: How to force Excel to recalculate on opening xlsx
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
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/How-to-force-Excel-to-recalculate-on-opening-xlsx-tp3249595p3354876.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: How to force Excel to recalculate on opening xlsx
Posted by dickschoeller <sc...@comcast.net>.
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.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: How to force Excel to recalculate on opening xlsx
Posted by dickschoeller <sc...@comcast.net>.
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.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: How to force Excel to recalculate on opening xlsx
Posted by dickschoeller <sc...@comcast.net>.
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.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: How to force Excel to recalculate on opening xlsx
Posted by Mark Beardsley <ma...@tiscali.co.uk>.
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.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: How to force Excel to recalculate on opening xlsx
Posted by dickschoeller <sc...@comcast.net>.
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.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: How to force Excel to recalculate on opening xlsx
Posted by Nick Burch <ni...@alfresco.com>.
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: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: How to force Excel to recalculate on opening xlsx
Posted by dickschoeller <sc...@comcast.net>.
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.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: How to force Excel to recalculate on opening xlsx
Posted by Nick Burch <ni...@alfresco.com>.
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: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: How to force Excel to recalculate on opening xlsx
Posted by dickschoeller <sc...@comcast.net>.
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.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: How to force Excel to recalculate on opening xlsx
Posted by Nick Burch <ni...@alfresco.com>.
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: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org