Cell Formula not refreshed

[mjw_85uk]

I use HSSF POI to update a spreadsheet with data taken from a database.  I have a totals row that takes the sum of a range of cells as seen in the image below:

  TOTALS ROW

When i open the file after it has been populated with the information using OpenOffice the totals row has the correct values in it.  However, when i open the file using MS Excel the value in the cell is #VALUE! and not the nurmeric value it should be.  I am using POI 3.1 and the code i use to recalculate the values is:

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);
                }
        }
}
               
Anyone got any suggestions to what is happen?

many thanks
mark

[Nick Burch]

On Mon, 14 Jul 2008, mjw_85uk wrote:
> When i open the file after it has been populated with the information
> using OpenOffice the totals row has the correct values in it.  However,
> when i open the file using MS Excel the value in the cell is #VALUE! and
> not the nurmeric value it should be.  I am using POI 3.1 and the code i
> use to recalculate the values is:

It might be worth trying a recent nightly build [1], there have been a few
formula evaluation fixes since 3.1 final

Nick

[1] http://encore.torchbox.com/poi-svn-build/

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@...
For additional commands, e-mail: user-help@...

[mjw_85uk]

hi nick,

thanks for the advice.  i updated the jar file to a build jar file from last night and it worked prefectly.

many thanks
mark

Nick Burch wrote:

On Mon, 14 Jul 2008, mjw_85uk wrote:
> When i open the file after it has been populated with the information
> using OpenOffice the totals row has the correct values in it.  However,
> when i open the file using MS Excel the value in the cell is #VALUE! and
> not the nurmeric value it should be.  I am using POI 3.1 and the code i
> use to recalculate the values is:

It might be worth trying a recent nightly build [1], there have been a few
formula evaluation fixes since 3.1 final

Nick

[1] http://encore.torchbox.com/poi-svn-build/

---------------------------------------------------------------------
To unsubscribe, e-mail: [e-mail]
For additional commands, e-mail: [e-mail]

[Thierry Morel]

Hi, I'm experiencing exactly the same pb on POI 3.1, Excel is showing #VALUE! and not OpenOffice ...
So I downloaded some more recent build, alpha, betas up to the very last 3.5.
With every recent builds I found, I have this execution error :
java.lang.ClassNotFoundException: org.apache.poi.hssf.model.FormulaParser$FormulaParseException

on a simple
newCell.setCellFormula(newFormula);
Console : XLS Row 131 Col : 28 oldFformula="AC129" newFformula="AC129+Janvier.AC131"

the worst is that this FormulaParser$FormulaParseException.class is present in the jar.
I don't understant anything and tried all solutions. The formula parsing seems good, cause
when I change the new formula, I have parsing errors. So the parsing is right, but an
exception is raised ??
Thanks in advance.
Thy


----- Original Message -----
From: "Nick Burch" <nick@...>
To: "POI Users List" <user@...>
Sent: Monday, July 14, 2008 3:16 PM
Subject: Re: Cell Formula not refreshed

> On Mon, 14 Jul 2008, mjw_85uk wrote:
> > When i open the file after it has been populated with the information
> > using OpenOffice the totals row has the correct values in it.  However,
> > when i open the file using MS Excel the value in the cell is #VALUE! and
> > not the nurmeric value it should be.  I am using POI 3.1 and the code i
> > use to recalculate the values is:
>
> It might be worth trying a recent nightly build [1], there have been a few
> formula evaluation fixes since 3.1 final
>
> Nick
>
> [1] http://encore.torchbox.com/poi-svn-build/
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@...
> For additional commands, e-mail: user-help@...
>
>

[Nick Burch]

On Sat, 19 Jul 2008, Thierry Morel wrote:
> So I downloaded some more recent build, alpha, betas up to the very last
> 3.5. With every recent builds I found, I have this execution error :
> java.lang.ClassNotFoundException: org.apache.poi.hssf.model.FormulaParser$FormulaParseException

Try re-compiling your application before using it with poi 3.5. A few
things have changed slightly, and while the java compiler can handle these
fine, the run time linker tends to be a bit more useless :(

Nick

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@...
For additional commands, e-mail: user-help@...

[Thierry Morel]

Thank you very much for your answer Nick.
In fact there was basically a parsing pb, my formula was
"$\'SheetName\'.F130".
The parser doesn't accept this grammar.
I replaced it by "SheetName!F130" that works fine.

Concerning compilation, I already rebuilt the whole project, but the pb
persists.
I was thinking about a Manifest.mf file lack, but your's is empty.
Notice that I include the POI jar in to my own jar by unzipping and jar-ing
again.
This always has worked fine up to now... but I didn't have parsing pbs ...
The worst is all classes are correctly linked, but the
FormulaParseException.

Thank you again for your answer, and congratulations for your hard work
on POI for many years now.
Thy

----- Original Message -----
From: "Nick Burch" <nick@...>
To: "POI Users List" <user@...>
Sent: Monday, July 21, 2008 10:40 AM
Subject: Re: FormulaParser$FormulaParseException not found


> On Sat, 19 Jul 2008, Thierry Morel wrote:
> > So I downloaded some more recent build, alpha, betas up to the very last
> > 3.5. With every recent builds I found, I have this execution error :
> > java.lang.ClassNotFoundException:
org.apache.poi.hssf.model.FormulaParser$FormulaParseException

>
> Try re-compiling your application before using it with poi 3.5. A few
> things have changed slightly, and while the java compiler can handle these
> fine, the run time linker tends to be a bit more useless :(
>
> Nick
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@...
> For additional commands, e-mail: user-help@...
>
>

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@...
For additional commands, e-mail: user-help@...

[Nick Burch]

On Mon, 21 Jul 2008, Thierry Morel wrote:
> I was thinking about a Manifest.mf file lack, but your's is empty.
> Notice that I include the POI jar in to my own jar by unzipping and
> jar-ing again. This always has worked fine up to now... but I didn't
> have parsing pbs ... The worst is all classes are correctly linked, but
> the FormulaParseException.

org.apache.poi.hssf.model.FormulaParser.FormulaParseException is present
in poi 3.5, and hasn't been changed. (I'd mis-remembered, and it's a
different bit you might need to re-compile for)

I'm tempted to say there's either something wrong with your packaging, or
you have an older version of poi kicking about on your classpath

See the first question on the faq - http://poi.apache.org/faq.html - it's
probably worth you trying that with the formula parser class, and the
exception, and checking it all comes from the right place

Nick

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@...
For additional commands, e-mail: user-help@...