You are viewing a plain text version of this content. The canonical link for it is here.

Posted to user@poi.apache.org by Aniket Banerjee <co...@gmail.com> on 2011/01/28 11:14:23 UTC

Formula Calculation Option with POI.

Hi,
Is it possible change the Calculation Option of an excel using POI. Like in
my case I want to make the calculation mode to manual instead of automatic
using some POI API. Is this possible?

Thanks,
Aniket

Re: Formula Calculation Option with POI.

Posted by Aniket Banerjee <co...@gmail.com>.

Thanks Mark.
Actually both the method evaluateForlulaCell() and evaluateInCell()
calculates the same way. But only the thing that is different is the way
they store the result. Now what I can guess is as Formula Option Automatic
calculation is ON so excel tries to do the calculation and gives the error.
But suppose the formula is not set from POI and it already exists in excel
then it evaluates properly.

On Mon, Jan 31, 2011 at 1:43 PM, Mark Beardsley <ma...@tiscali.co.uk>wrote:

>
> I am not trying to evade answering your questions here but I have one to
> ask
> myself.
>
> It seems as though your evaluating the formulae with POI prioir to opening
> the workbook using Excel might be causing these issues. Do you need to
> evaluate the formulae with POI for any reason? If not, what would happen
> when the workbook was opened with Excel? Maybe this will manage to
> circumvent the problem you are facing.
>
> Now, as to your other questions;
>
> So my question is why excel gives #VALUE! error for above functions. Is
> there some compatibility issue.
>
> I honestly do not know the answer to this question. This error is quite
> common and occurs when the workng type of value - for example a text value
> rather than a number - or the incorect operand is used. Look carefuly at
> the
> formulae and the cells they are referencing just to make sure that you are
> not trying to include the string value '30' rather than the integer value
> 30
> into a SUM for example. It may simply be that Excel is far stricter about
> this than POI is and will refuse to make any assumptions.
>
> How can I solve this problem OR how should I approach to solve this problem
> where I need to keep both formula and result without #VALUE!.
>
> Well, I guess that there are two answers to this question really. If you
> can
> solve the #VALUE issue above then the problem may well - hopefully -
> resolve
> itself. If not, then I would suggest you will need to identify what the two
> calcaultion methods are doing differently and try to 'normalise' them, i.e.
> try to make sure that both operate in the appropriate manner. Aside from
> that, I do not know enough about the Excel file structure to offer any
> advice - I do know that Excel stores the result of the last calculation run
> fro formulae away to the file somewehere. But, I do not know eher this is
> and if it is possible to get at it with the API. The thought going through
> my mind is if you could update the cached value to hold the result of the
> formulas evaluation then the forumla could remain the the cell but I do not
> know if this is possible or even if it might work.
>
> Sorry to not be of more help.
>
> Yours
>
> Mark B
> --
> View this message in context:
> http://apache-poi.1045710.n5.nabble.com/Formula-Calculation-Option-with-POI-tp3361199p3364162.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: Formula Calculation Option with POI.

Posted by Mark Beardsley <ma...@tiscali.co.uk>.

I am not trying to evade answering your questions here but I have one to ask
myself.

It seems as though your evaluating the formulae with POI prioir to opening
the workbook using Excel might be causing these issues. Do you need to
evaluate the formulae with POI for any reason? If not, what would happen
when the workbook was opened with Excel? Maybe this will manage to
circumvent the problem you are facing.

Now, as to your other questions;

So my question is why excel gives #VALUE! error for above functions. Is
there some compatibility issue.

I honestly do not know the answer to this question. This error is quite
common and occurs when the workng type of value - for example a text value
rather than a number - or the incorect operand is used. Look carefuly at the
formulae and the cells they are referencing just to make sure that you are
not trying to include the string value '30' rather than the integer value 30
into a SUM for example. It may simply be that Excel is far stricter about
this than POI is and will refuse to make any assumptions.

How can I solve this problem OR how should I approach to solve this problem
where I need to keep both formula and result without #VALUE!.

Well, I guess that there are two answers to this question really. If you can
solve the #VALUE issue above then the problem may well - hopefully - resolve
itself. If not, then I would suggest you will need to identify what the two
calcaultion methods are doing differently and try to 'normalise' them, i.e.
try to make sure that both operate in the appropriate manner. Aside from
that, I do not know enough about the Excel file structure to offer any
advice - I do know that Excel stores the result of the last calculation run
fro formulae away to the file somewehere. But, I do not know eher this is
and if it is possible to get at it with the API. The thought going through
my mind is if you could update the cached value to hold the result of the
formulas evaluation then the forumla could remain the the cell but I do not
know if this is possible or even if it might work.

Sorry to not be of more help.

Yours

Mark B
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Formula-Calculation-Option-with-POI-tp3361199p3364162.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: Formula Calculation Option with POI.

Posted by Aniket Banerjee <co...@gmail.com>.

Thanks Mark for the detailed explanation.
Just one more query although I have asked this before too in this forum and
also debugged the code to look into the case but not sure why this is
happening.

So if my excel option is automatic although if I do evaluateFormulaCell()
but as the formula remains in the cell excel does the calculation again by
itself.
Now for some formula it works fine. But for some functions in excel like
VALUE,TRIM,ADDRESS this gives me #VALUE! error. So I need to use
evaluateInCell() but unfortunately our client is not happy that the formula
gets removed from the cell :(.
So my question is why excel gives #VALUE! error for above functions. Is
there some compatibility issue.
How can I solve this problem OR how should I approach to solve this problem
where I need to keep both formula and result without #VALUE!.

Thanks,
Aniket

On Fri, Jan 28, 2011 at 8:42 PM, Mark Beardsley <ma...@tiscali.co.uk>wrote:

>
> Do not waste your time. I have just had a good look and this setting
> relates
> to the application and not to any file. Therefore, there will be no setting
> within the .xls or .xlsx file that has any effect upon whether the
> calculation mode is automaic or not. As of now, the only way I know to set
> this programmatically is with a macro. You would need to define a macro
> within the workbook that ran whenever the user opened the workbook and
> which
> adjusted the settings as required. There are quite a few problems with this
> approach;
>
> 1. The change in settings would persist - that means that the next workbook
> the user opened would also be affected by the changes you made.
> 2. Whilst macros can be signed with Office 2007 files (.xlsm), this feature
> is not supported with the older binary file format. Thus, when the user
> opens a workbok they will be faced with a message telling them it contains
> macros, that these could perform harmful actions and offering them the
> option of disabling the macro.
>
> So, to sum up, I guess the answer is that it can be done, not with POI or a
> patched version of the api and that you need to think carefully before
> doing
> this - how happy will your users me if your workbook makes changes to their
> copy of Excel?
>
> Yours
>
> Mark B
> --
> View this message in context:
> http://apache-poi.1045710.n5.nabble.com/Formula-Calculation-Option-with-POI-tp3361199p3361591.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: Formula Calculation Option with POI.

Posted by Mark Beardsley <ma...@tiscali.co.uk>.

Do not waste your time. I have just had a good look and this setting relates
to the application and not to any file. Therefore, there will be no setting
within the .xls or .xlsx file that has any effect upon whether the
calculation mode is automaic or not. As of now, the only way I know to set
this programmatically is with a macro. You would need to define a macro
within the workbook that ran whenever the user opened the workbook and which
adjusted the settings as required. There are quite a few problems with this
approach;

1. The change in settings would persist - that means that the next workbook
the user opened would also be affected by the changes you made.
2. Whilst macros can be signed with Office 2007 files (.xlsm), this feature
is not supported with the older binary file format. Thus, when the user
opens a workbok they will be faced with a message telling them it contains
macros, that these could perform harmful actions and offering them the
option of disabling the macro.

So, to sum up, I guess the answer is that it can be done, not with POI or a
patched version of the api and that you need to think carefully before doing
this - how happy will your users me if your workbook makes changes to their
copy of Excel?

Yours

Mark B
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Formula-Calculation-Option-with-POI-tp3361199p3361591.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: Formula Calculation Option with POI.

Posted by Mark Beardsley <ma...@tiscali.co.uk>.

1. The change in settings would persists - that means that the next workbook
the user opened would also be affected by the changes you made.
2. Whilst macros can be signed with Office 2007 one (.xlsm), this feature is
not supported with the older binary file format. Thus, when the user opens a
workbok they will be faced with a message telling them it contains macros,
that these could perform harmful actions and offering them the option of
disabling the macro.

Yours

Mark B
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Formula-Calculation-Option-with-POI-tp3361199p3361586.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: Formula Calculation Option with POI.

Posted by Mark Beardsley <ma...@tiscali.co.uk>.

Well, the xls format is binary and much harder to dig around in. If you
wanted to work on that, it is best to start of by looking at the
specification for the file format which Microsoft have published and asking
lots of questions at the dev list. Also, you would have to be certain that
the feature existed in the version of the format that POI targets. I think
it was the version current when Excel 97 or maybe even 95 was released and
it has remained that way to ensure maximum backward compatibility with older
versions of the application.

Yours

Mark B
-- 
View this message in context: http://apache-poi.1045710.n5.nabble.com/Formula-Calculation-Option-with-POI-tp3361199p3361367.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: Formula Calculation Option with POI.

Posted by Aniket Banerjee <co...@gmail.com>.

Thanks Mark. I will try and do that. I have also contributed two methods
(CLEAN and ADDRESS)
 to POI 3.8 and try keep doing that :).
But will it be possible for .xls format too?
On Fri, Jan 28, 2011 at 5:06 PM, Mark Beardsley <ma...@tiscali.co.uk>wrote:

>
> No, not as far as I am aware. The trick to get around this os very simple
> however; use Excel to reate a template file and set the calculation options
> on that as you want them. Next, use POI to open the template and populate
> then save it away. The calculation options should still be set as they were
> when you read the template file as POI passes through unchanged anything it
> does not yet parse.
>
> Now, can I ask which version of the file format you are targetting? If the
> SpreadsheetML version (.xlsx) would you consider adding this functionality
> into the api? As you no doubt know, the .xlsx files are simply zipped xml
> so
> it should be quite straightforward to identify where the options are stored
> within this file. Once you know this, identifying the necessary openxml
> classes and then coding supporting XSSF ones should be possible. If you
> want
> to have a go at this, then post specific questions onto the dev list.
> Contributions from users are always welcomed as the whole project is run on
> a voluntary basis - people like Yegor, Nick, David and Josh (to mention
> just
> a few) have contributed a lot of their spare time to pushing the api
> forward
> and are always welcoming and supportive of assistance.
>
> Yours
>
> Mark B
> --
> View this message in context:
> http://apache-poi.1045710.n5.nabble.com/Formula-Calculation-Option-with-POI-tp3361199p3361289.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: Formula Calculation Option with POI.

Posted by Mark Beardsley <ma...@tiscali.co.uk>.

No, not as far as I am aware. The trick to get around this os very simple
however; use Excel to reate a template file and set the calculation options
on that as you want them. Next, use POI to open the template and populate
then save it away. The calculation options should still be set as they were
when you read the template file as POI passes through unchanged anything it
does not yet parse.

Now, can I ask which version of the file format you are targetting? If the
SpreadsheetML version (.xlsx) would you consider adding this functionality
into the api? As you no doubt know, the .xlsx files are simply zipped xml so
it should be quite straightforward to identify where the options are stored
within this file. Once you know this, identifying the necessary openxml
classes and then coding supporting XSSF ones should be possible. If you want
to have a go at this, then post specific questions onto the dev list.
Contributions from users are always welcomed as the whole project is run on
a voluntary basis - people like Yegor, Nick, David and Josh (to mention just
a few) have contributed a lot of their spare time to pushing the api forward
and are always welcoming and supportive of assistance.

Yours

Mark B
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Formula-Calculation-Option-with-POI-tp3361199p3361289.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