You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Patil Minal <Pa...@JohnDeere.com> on 2009/06/17 10:07:24 UTC
Formula are not evaluated in POI when the cell value change
Hi,
I am using POI to populate a data sheet in a template. Another sheet in the workbook has references to data in this data sheet through VLOOKUP formula. Now if I populate data sheet cells using poi then the formulas on the other sheet do not read those values. The formula need to be evaluated manually by pressing F2 and then the values show up. Please preovide me guidance on this. The formulas on the other sheet should reevaluate since the cells the formulae are referencing are changed.
- Minal.
RE: Formula are not evaluated in POI when the cell value change
Posted by MSB <ma...@tiscali.co.uk>.
I looked at the workbook level but never thought to check at the sheet level,
sorry about that.
Anyway, glad you found a way that works and I will remember that for the
future.
Yours
Mark B
Patil Minal wrote:
>
>
> Hi Mark,
>
> Controlling calculation is an option but I found an option within API
> which forces recalculation of a sheet when you open it next time. And it
> worked for me.
>
> sheetObject.setForceFormulaRecalculation(true)
>
> Thanks a lot.
>
> - Minal.
>
> -----Original Message-----
> From: MSB [mailto:markbrdsly@tiscali.co.uk]
> Sent: Wednesday, June 17, 2009 10:14 PM
> To: user@poi.apache.org
> Subject: Re: Formula are not evaluated in POI when the cell value change
>
>
> OK, a very quick look around but I have found some interesting
> information.
>
> Firstly, I do not think there is anything available within the API that
> allows you to force recalucation when the workbook is opened.
>
> Secondly, I found this bit of information that is very interesting IMO;
>
> "Controlling Calculation
>
> Excel has a range of options allowing you to control the way it
> calculates.
> You can change these options using the Tools -->Options-->Calculation tab.
>
> Calculation Settings Keep Changing
>
> Because a number of Excel's calculation settings work at the application
> level (they are the same for all open workbooks), and are set by the first
> workbook opened, they may appear to change randomly depending on the
> sequence in which workbooks are opened. FastExcel Version 2 allows you to
> solve many of these problems.
>
> Automatic Calculation.
>
> Automatic calculation mode means that Excel will automatically recalculate
> all open workbooks at each and every change, and whenever you open a
> workbook.
>
> Usually when you open a workbook in Automatic mode and Excel recalculates
> you will not see the recalculation because nothing will have changed since
> the workbook was saved.
>
> An exception is when you open a workbook in Excel 2000 that was saved
> using Excel 97, or you open using Excel2002/2003 a workbook saved in
> Excel2000:
> because the Excel calculation engines are different a Full calculation is
> done."
>
> So, there could be two reasons why you do not see that the formumlas have
> been caculated when you open the workbook. Firstly, the automatic
> caculation setting could have been changed when you or a user open a
> workbook or, as is more likely I believe, because as far as Excel is
> aware, the file has not changed. Of course, I could well be wrong.
>
> Yours
>
> Mark B
>
>
>
> Patil Minal wrote:
>>
>> Hi,
>>
>> I am using POI to populate a data sheet in a template. Another sheet
>> in the workbook has references to data in this data sheet through
>> VLOOKUP formula. Now if I populate data sheet cells using poi then the
>> formulas on the other sheet do not read those values. The formula need
>> to be evaluated manually by pressing F2 and then the values show up.
>> Please preovide me guidance on this. The formulas on the other sheet
>> should reevaluate since the cells the formulae are referencing are
>> changed.
>>
>>
>> - Minal.
>>
>>
>>
>
> --
> View this message in context:
> http://www.nabble.com/Formula-are-not-evaluated-in-POI-when-the-cell-value-change-tp24068885p24077559.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
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
>
--
View this message in context: http://www.nabble.com/Formula-are-not-evaluated-in-POI-when-the-cell-value-change-tp24068885p24087014.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 are not evaluated in POI when the cell value change
Posted by Patil Minal <Pa...@JohnDeere.com>.
Hi Mark,
Controlling calculation is an option but I found an option within API which forces recalculation of a sheet when you open it next time. And it worked for me.
sheetObject.setForceFormulaRecalculation(true)
Thanks a lot.
- Minal.
-----Original Message-----
From: MSB [mailto:markbrdsly@tiscali.co.uk]
Sent: Wednesday, June 17, 2009 10:14 PM
To: user@poi.apache.org
Subject: Re: Formula are not evaluated in POI when the cell value change
OK, a very quick look around but I have found some interesting information.
Firstly, I do not think there is anything available within the API that allows you to force recalucation when the workbook is opened.
Secondly, I found this bit of information that is very interesting IMO;
"Controlling Calculation
Excel has a range of options allowing you to control the way it calculates.
You can change these options using the Tools -->Options-->Calculation tab.
Calculation Settings Keep Changing
Because a number of Excel's calculation settings work at the application level (they are the same for all open workbooks), and are set by the first workbook opened, they may appear to change randomly depending on the sequence in which workbooks are opened. FastExcel Version 2 allows you to solve many of these problems.
Automatic Calculation.
Automatic calculation mode means that Excel will automatically recalculate all open workbooks at each and every change, and whenever you open a workbook.
Usually when you open a workbook in Automatic mode and Excel recalculates you will not see the recalculation because nothing will have changed since the workbook was saved.
An exception is when you open a workbook in Excel 2000 that was saved using Excel 97, or you open using Excel2002/2003 a workbook saved in Excel2000:
because the Excel calculation engines are different a Full calculation is done."
So, there could be two reasons why you do not see that the formumlas have been caculated when you open the workbook. Firstly, the automatic caculation setting could have been changed when you or a user open a workbook or, as is more likely I believe, because as far as Excel is aware, the file has not changed. Of course, I could well be wrong.
Yours
Mark B
Patil Minal wrote:
>
> Hi,
>
> I am using POI to populate a data sheet in a template. Another sheet
> in the workbook has references to data in this data sheet through
> VLOOKUP formula. Now if I populate data sheet cells using poi then the
> formulas on the other sheet do not read those values. The formula need
> to be evaluated manually by pressing F2 and then the values show up.
> Please preovide me guidance on this. The formulas on the other sheet
> should reevaluate since the cells the formulae are referencing are changed.
>
>
> - Minal.
>
>
>
--
View this message in context: http://www.nabble.com/Formula-are-not-evaluated-in-POI-when-the-cell-value-change-tp24068885p24077559.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
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Formula are not evaluated in POI when the cell value change
Posted by MSB <ma...@tiscali.co.uk>.
OK, a very quick look around but I have found some interesting information.
Firstly, I do not think there is anything available within the API that
allows you to force recalucation when the workbook is opened.
Secondly, I found this bit of information that is very interesting IMO;
"Controlling Calculation
Excel has a range of options allowing you to control the way it calculates.
You can change these options using the Tools -->Options-->Calculation tab.
Calculation Settings Keep Changing
Because a number of Excel's calculation settings work at the application
level (they are the same for all open workbooks), and are set by the first
workbook opened, they may appear to change randomly depending on the
sequence in which workbooks are opened. FastExcel Version 2 allows you to
solve many of these problems.
Automatic Calculation.
Automatic calculation mode means that Excel will automatically recalculate
all open workbooks at each and every change, and whenever you open a
workbook.
Usually when you open a workbook in Automatic mode and Excel recalculates
you will not see the recalculation because nothing will have changed since
the workbook was saved.
An exception is when you open a workbook in Excel 2000 that was saved using
Excel 97, or you open using Excel2002/2003 a workbook saved in Excel2000:
because the Excel calculation engines are different a Full calculation is
done."
So, there could be two reasons why you do not see that the formumlas have
been caculated when you open the workbook. Firstly, the automatic caculation
setting could have been changed when you or a user open a workbook or, as is
more likely I believe, because as far as Excel is aware, the file has not
changed. Of course, I could well be wrong.
Yours
Mark B
Patil Minal wrote:
>
> Hi,
>
> I am using POI to populate a data sheet in a template. Another sheet in
> the workbook has references to data in this data sheet through VLOOKUP
> formula. Now if I populate data sheet cells using poi then the formulas on
> the other sheet do not read those values. The formula need to be evaluated
> manually by pressing F2 and then the values show up. Please preovide me
> guidance on this. The formulas on the other sheet should reevaluate since
> the cells the formulae are referencing are changed.
>
>
> - Minal.
>
>
>
--
View this message in context: http://www.nabble.com/Formula-are-not-evaluated-in-POI-when-the-cell-value-change-tp24068885p24077559.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 are not evaluated in POI when the cell value change
Posted by MSB <ma...@tiscali.co.uk>.
I could very well be wrong because I have never had to do this sort of thing
myself, but I think that after populating the sheet you need to force any
and all formulae to evaluate themselves. You can find the code to do this on
the following page;
http://poi.apache.org/spreadsheet/eval.html
and I am guessing that you will need to look at the "Re-calculating all
formulas in a Workbook" section.
The reason I am saying this is that if you are using Excel and entering a
value into a cell that could affect a formula elsewhere on a sheet then the
application is aware of the change you have made and able to perform the
re-calculation. However, when it reads a file, it is not aware that any
changes have been made and will not automatically perform a re-calculation
of the formulas. Also, at the back of my mind is the thought that it is
possible to tell Excel to recalculate forumulae when it open a sheet. I
cannot remember though if this is an option you need to set from within
Excel itself. I will have a dig aorund and see what I can find.
Yours
Mark B
Patil Minal wrote:
>
> Hi,
>
> I am using POI to populate a data sheet in a template. Another sheet in
> the workbook has references to data in this data sheet through VLOOKUP
> formula. Now if I populate data sheet cells using poi then the formulas on
> the other sheet do not read those values. The formula need to be evaluated
> manually by pressing F2 and then the values show up. Please preovide me
> guidance on this. The formulas on the other sheet should reevaluate since
> the cells the formulae are referencing are changed.
>
>
> - Minal.
>
>
>
--
View this message in context: http://www.nabble.com/Formula-are-not-evaluated-in-POI-when-the-cell-value-change-tp24068885p24077312.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