You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by J Keller <jk...@oberonassociates.com> on 2008/03/18 15:06:34 UTC
HSSF formula cells not calculating
Using poi-3.0-rc4, I'm reading an Excel spreadsheet (which works fine); changing some values (which works fine), and saving it out as a new version (which also works fine).
But, when I open the spreadsheet, all of my formula cells are not calculated (nor can I tell the spreadsheet to recalc using F9 for example). The are formula type cells according to HSSF.
The formulas are there, if I click on them I can see them when I open the spreadsheet in Excel. If I then click onto the slot at the top where you can edit the formula itself (and then leave focus) the correct value is calculated and displayed. But I can't force the whole spreadsheet to recalculate; I have to "fix" it one cell at a time.
Any idea what I could be doing wrong? Thank you,
-J
RE: HSSF formula cells not calculating
Posted by J Keller <jk...@oberonassociates.com>.
Done, thanks.
-----Original Message-----
From: David Fisher [mailto:dfisher@jmlafferty.com]
You should make a bugzilla report - https://issues.apache.org/
bugzilla/buglist.cgi?product=POI - and attach your sample spreadsheet
and the java code...
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: HSSF formula cells not calculating
Posted by David Fisher <df...@jmlafferty.com>.
J -
See http://poi.apache.org/who.html
Nick is the chair of the project management committee (PMC) for POI.
You should make a bugzilla report - https://issues.apache.org/
bugzilla/buglist.cgi?product=POI - and attach your sample spreadsheet
and the java code to prove the error - that way any of the currently
active developers can take a look. There are active developers not on
the page referenced. There is one who is working on the formula
parsing code and is responsible for much of the current improvement
in that area.
Regards,
Dave Fisher
On Mar 19, 2008, at 11:49 AM, J Keller wrote:
> Nick,
>
> I can now reproduce the behavior with a simple (4x6 cells)
> spreadsheet, and simple code to change and recalc it.
>
> This is not a spreadsheet created with POI, it's a greatly
> simplified version of one created in Excel. If I simplify it any
> more, I can't reproduce the problem.
>
> The problem is not affecting me (using setCellFormula worked around
> it), but if you'd like a copy of the simple code and spreadsheet,
> I'd be happy to send it to you. Are you one of the developers of POI?
>
> -J
>
>
> ---------------------------------------------------------------------
> 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: HSSF formula cells not calculating
Posted by J Keller <jk...@oberonassociates.com>.
Nick,
I can now reproduce the behavior with a simple (4x6 cells) spreadsheet, and simple code to change and recalc it.
This is not a spreadsheet created with POI, it's a greatly simplified version of one created in Excel. If I simplify it any more, I can't reproduce the problem.
The problem is not affecting me (using setCellFormula worked around it), but if you'd like a copy of the simple code and spreadsheet, I'd be happy to send it to you. Are you one of the developers of POI?
-J
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
RE: HSSF formula cells not calculating
Posted by J Keller <jk...@oberonassociates.com>.
Ok, I can duplicate the behavior with very simple code.
---------------
public static void main (String[] args)
{
try
{
File ssFile = new File ("simple.xls");
FileInputStream ssIn = new FileInputStream (ssFile);
HSSFWorkbook wb = new HSSFWorkbook (ssIn);
HSSFSheet sheet = wb.getSheetAt (0);
HSSFRow row = sheet.getRow (3);
HSSFCell cell = row.getCell ((short) 5);
cell.setCellValue (25);
// recalc
HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(sheet, wb);
for (Iterator rit = sheet.rowIterator(); rit.hasNext();)
{
HSSFRow r = (HSSFRow)rit.next();
evaluator.setCurrentRow(r);
for (Iterator cit = r.cellIterator(); cit.hasNext();)
{
HSSFCell c = (HSSFCell)cit.next();
if (c.getCellType() == HSSFCell.CELL_TYPE_FORMULA)
evaluator.evaluateFormulaCell (c);
}
}
FileOutputStream ssOut = new FileOutputStream ("changed.xls");
wb.write (ssOut);
ssOut.close();
}
catch (Exception x)
{
System.err.println (x);
}
}
---------------
However, I cannot duplicate the problem with a simple spreadsheet.
My spreadsheet is fairly complex (calculated cells that are used by other calculated cells, etc). Unfortunately, I can't publish it (it's full of proprietary information). I'll continue to try and isolate the problem.
-J
-----Original Message-----
From: Nick Burch [mailto:nick@torchbox.com]
Sent: Wednesday, March 19, 2008 11:06 AM
To: POI Users List
Subject: RE: HSSF formula cells not calculating
On Wed, 19 Mar 2008, J Keller wrote:
> According to the docs, that is the method I want. But it didn't work
> correctly for me. When I used the "recalc spreadsheet" code from the
> docs page, it did put calculated values into the cells, but they would
> not update when I changed any of the data in the other cells referenced
> by the formula.
Hmm, not sure if what you're seeing is a bug or not. Are you doing:
* update all cell values
* recalculate all formulas
* save
Or are you trying to do it in a different order? If you're doing it in
that order, and things aren't being correctly re-calculated, please do
open a new bug and upload a little unit test that shows off the issue.
> Once I added the call to setCellFormula, it worked fine. This looks
> like a bug.
That shouldn't be needed, assuming you're doing everything in the right
order
Nick
---------------------------------------------------------------------
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: HSSF formula cells not calculating
Posted by J Keller <jk...@oberonassociates.com>.
Here's what I did:
In POI:
1) open the spreadsheet
2) change some (non-formula) data cell values
3) re-calc everything
4) save the spreadsheet (to a new file)
In Excel:
1) open the spreadsheet (the calculated values are correct)
2) manually change a data cell
The calculated cells that should change, don't.
If I change my code to set the formula after the re-calc, it works as expected.
It will take me a little while to reproduce it using a simple spreadsheet, but I'll try to do so.
-J
-----Original Message-----
From: Nick Burch [mailto:nick@torchbox.com]
Sent: Wednesday, March 19, 2008 11:18 AM
To: POI Users List
Subject: RE: HSSF formula cells not calculating
On Wed, 19 Mar 2008, J Keller wrote:
> Although the values were correctly calculated, the formula cells would
> not recalculate when I changed the normal data cells. Adding the
> setCellFormula fixed it.
Where did you change the normal data cells though? In excel, or in poi?
If you change them in poi, you then need to do another re-calculation
afterwards
Nick
---------------------------------------------------------------------
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: HSSF formula cells not calculating
Posted by Nick Burch <ni...@torchbox.com>.
On Wed, 19 Mar 2008, J Keller wrote:
> Although the values were correctly calculated, the formula cells would
> not recalculate when I changed the normal data cells. Adding the
> setCellFormula fixed it.
Where did you change the normal data cells though? In excel, or in poi?
If you change them in poi, you then need to do another re-calculation
afterwards
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
RE: HSSF formula cells not calculating
Posted by J Keller <jk...@oberonassociates.com>.
I used the exact code from the "Re-calculating all formulas in a Workbook" section of the page linked: http://poi.apache.org/hssf/eval.html
I think I'm doing it in the right order. The only change I made was to the paths of the document.
------------------
FileInputStream fis = new FileInputStream("/somepath/test.xls");
HSSFWorkbook wb = new HSSFWorkbook(fis);
for(int sheetNum = 0; sheetNum < wb.getNumberOfSheets(); sheetNum++) {
HSSFSheet sheet = wb.getSheetAt(sheetNum);
HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(sheet, wb);
for(Iterator rit = sheet.rowIterator(); rit.hasNext();) {
HSSFRow r = (HSSFRow)rit.next();
evaluator.setCurrentRow(r);
for(Iterator cit = r.cellIterator(); cit.hasNext();) {
HSSFCell c = (HSSFCell)cit.next();
if(c.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {
evaluator.evaluateFormulaCell(c);
}
}
}
}
wb.write(new FileOutputStream("/somepath/changed.xls"));
------------------
Although the values were correctly calculated, the formula cells would not recalculate when I changed the normal data cells. Adding the setCellFormula fixed it.
I'll try to produce a simple example.
-J
-----Original Message-----
From: Nick Burch [mailto:nick@torchbox.com]
Sent: Wednesday, March 19, 2008 11:06 AM
To: POI Users List
Subject: RE: HSSF formula cells not calculating
On Wed, 19 Mar 2008, J Keller wrote:
> According to the docs, that is the method I want. But it didn't work
> correctly for me. When I used the "recalc spreadsheet" code from the
> docs page, it did put calculated values into the cells, but they would
> not update when I changed any of the data in the other cells referenced
> by the formula.
Hmm, not sure if what you're seeing is a bug or not. Are you doing:
* update all cell values
* recalculate all formulas
* save
Or are you trying to do it in a different order? If you're doing it in
that order, and things aren't being correctly re-calculated, please do
open a new bug and upload a little unit test that shows off the issue.
> Once I added the call to setCellFormula, it worked fine. This looks
> like a bug.
That shouldn't be needed, assuming you're doing everything in the right
order
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
RE: HSSF formula cells not calculating
Posted by Nick Burch <ni...@torchbox.com>.
On Wed, 19 Mar 2008, J Keller wrote:
> According to the docs, that is the method I want. But it didn't work
> correctly for me. When I used the "recalc spreadsheet" code from the
> docs page, it did put calculated values into the cells, but they would
> not update when I changed any of the data in the other cells referenced
> by the formula.
Hmm, not sure if what you're seeing is a bug or not. Are you doing:
* update all cell values
* recalculate all formulas
* save
Or are you trying to do it in a different order? If you're doing it in
that order, and things aren't being correctly re-calculated, please do
open a new bug and upload a little unit test that shows off the issue.
> Once I added the call to setCellFormula, it worked fine. This looks
> like a bug.
That shouldn't be needed, assuming you're doing everything in the right
order
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
RE: HSSF formula cells not calculating
Posted by J Keller <jk...@oberonassociates.com>.
According to the docs, that is the method I want. But it didn't work correctly for me. When I used the "recalc spreadsheet" code from the docs page, it did put calculated values into the cells, but they would not update when I changed any of the data in the other cells referenced by the formula.
Once I added the call to setCellFormula, it worked fine. This looks like a bug.
for (Iterator rit = sheet.rowIterator(); rit.hasNext();)
{
HSSFRow row = (HSSFRow) rit.next();
evaluator.setCurrentRow (row);
for (Iterator cit = row.cellIterator(); cit.hasNext();)
{
HSSFCell cell = (HSSFCell) cit.next();
if (cell != null && cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA)
{
String formula = cell.getCellFormula();
if (formula != null)
{
evaluator.evaluateFormulaCell (cell);
cell.setCellFormula (formula); // ADD THIS OR IT WON'T RECALC
}
}
}
}
-----Original Message-----
From: Nick Burch [mailto:nick@torchbox.com]
Sent: Wednesday, March 19, 2008 6:33 AM
To: POI Users List
Subject: RE: HSSF formula cells not calculating
On Tue, 18 Mar 2008, J Keller wrote:
> My original code seemed to replace the formula with the result of the
> formula. I added a call to setCellFormula after the call to
> evalueInCell, and it keeps both (the value and the formula):
See http://poi.apache.org/hssf/eval.html
You probably want evaluateFormulaCell, rather than evaluateInCell
Nick
---------------------------------------------------------------------
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: HSSF formula cells not calculating
Posted by Nick Burch <ni...@torchbox.com>.
On Tue, 18 Mar 2008, J Keller wrote:
> My original code seemed to replace the formula with the result of the
> formula. I added a call to setCellFormula after the call to
> evalueInCell, and it keeps both (the value and the formula):
See http://poi.apache.org/hssf/eval.html
You probably want evaluateFormulaCell, rather than evaluateInCell
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
RE: HSSF formula cells not calculating
Posted by J Keller <jk...@oberonassociates.com>.
My original code seemed to replace the formula with the result of the formula. I added a call to setCellFormula after the call to evalueInCell, and it keeps both (the value and the formula):
if (formula != null)
{
evaluator.evaluateInCell (cell);
cell.setCellFormula (formula);
}
-----Original Message-----
From: J Keller [mailto:jkeller@oberonassociates.com]
Sent: Tuesday, March 18, 2008 11:03 AM
To: POI Users List
Subject: RE: HSSF formula cells not calculating
Thanks. It's working now. I think my problem was that I wasn't calling the Evaluator's setCurrentRow method:
HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator (sheet, wb);
int rows = sheet.getLastRowNum();
for (int r = 0; r < rows; r++)
{
HSSFRow row = sheet.getRow (r);
evaluator.setCurrentRow (row);
short first = row.getFirstCellNum();
short last = row.getLastCellNum();
for (short c = first; c < last; c++)
{
HSSFCell cell = row.getCell (c);
if (cell != null &&
cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA)
{
String formula = cell.getCellFormula();
if (formula != null)
evaluator.evaluateInCell (cell);
}
}
}
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
RE: HSSF formula cells not calculating
Posted by J Keller <jk...@oberonassociates.com>.
Thanks. It's working now. I think my problem was that I wasn't calling the Evaluator's setCurrentRow method:
HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator (sheet, wb);
int rows = sheet.getLastRowNum();
for (int r = 0; r < rows; r++)
{
HSSFRow row = sheet.getRow (r);
evaluator.setCurrentRow (row);
short first = row.getFirstCellNum();
short last = row.getLastCellNum();
for (short c = first; c < last; c++)
{
HSSFCell cell = row.getCell (c);
if (cell != null &&
cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA)
{
String formula = cell.getCellFormula();
if (formula != null)
evaluator.evaluateInCell (cell);
}
}
}
-----Original Message-----
From: Nick Burch [mailto:nick@torchbox.com]
Sent: Tuesday, March 18, 2008 10:58 AM
To: POI Users List
Subject: Re: HSSF formula cells not calculating
On Tue, 18 Mar 2008, J Keller wrote:
> Using poi-3.0-rc4, I'm reading an Excel spreadsheet (which works fine);
> changing some values (which works fine), and saving it out as a new
> version (which also works fine).
>
> But, when I open the spreadsheet, all of my formula cells are not
> calculated (nor can I tell the spreadsheet to recalc using F9 for
> example).
That's expected. You either need to force excel to re-calculate on load,
or do the recalculation yourself:
http://poi.apache.org/hssf/eval.html
By default, excel will just used the cached formula values
Nick
---------------------------------------------------------------------
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: HSSF formula cells not calculating
Posted by Nick Burch <ni...@torchbox.com>.
On Tue, 18 Mar 2008, J Keller wrote:
> Using poi-3.0-rc4, I'm reading an Excel spreadsheet (which works fine);
> changing some values (which works fine), and saving it out as a new
> version (which also works fine).
>
> But, when I open the spreadsheet, all of my formula cells are not
> calculated (nor can I tell the spreadsheet to recalc using F9 for
> example).
That's expected. You either need to force excel to re-calculate on load,
or do the recalculation yourself:
http://poi.apache.org/hssf/eval.html
By default, excel will just used the cached formula values
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org