You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Dimitri Pissarenko <di...@gmx.net> on 2003/11/15 15:11:10 UTC
HSSF, Problem with a formula
Hello!
I'm writing an application, in which I create an Excel document with
several worksheets. These show different values and their sums. I want
to create an additional worksheet, where only sums of those values are
shown.
For this purpose, I created this new worksheet and tried to set a
formula in a cell, which "points" exactly to the sum row of the
original worksheet.
The formula is
'Profile B'!N79-'Profile B'!N132
ie, the difference between cell N79 in worksheet "Profile B" and cell
N132 in worksheet "Profile B".
If I enter this formula manually in an Excel document, it works.
But POI generates following error message
2003-11-15 14:59:34,642 ERROR [profile.bsh] -
java.lang.RuntimeException: Cannot Parse, sorry : Integer Expected
at
org.apache.poi.hssf.model.FormulaParser.Abort(FormulaParser.java:172)
at
org.apache.poi.hssf.model.FormulaParser.Expected(FormulaParser.java:179)
at
org.apache.poi.hssf.model.FormulaParser.GetNum(FormulaParser.java:276)
at
org.apache.poi.hssf.model.FormulaParser.Factor(FormulaParser.java:506)
at
org.apache.poi.hssf.model.FormulaParser.Term(FormulaParser.java:545)
at
org.apache.poi.hssf.model.FormulaParser.Expression(FormulaParser.java:596)
at
org.apache.poi.hssf.model.FormulaParser.parse(FormulaParser.java:636)
at
org.apache.poi.hssf.usermodel.HSSFCell.setCellFormula(HSSFCell.java:714)
...
Does someone know, how to solve this problem?
TIA
Dimitri Pissarenko
---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: poi-user-help@jakarta.apache.org
Re: HSSF, Problem with a formula
Posted by Dimitri Pissarenko <di...@gmx.net>.
Hello!
>> If I enter formula like "-N155"
>Yes, the parser doesnt handle unary operators at the moment.. but the workaround
>is simple, try something like "0-N155"
Thanks, this workaround helps!
>> I've already tried to use worksheet names without spaces,
>Did you remove the quotes around the worksheet name?
Yes, I tried it both with and without quotes.
Best regards
Dimitri Pissarenko
---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: poi-user-help@jakarta.apache.org
Re: xls generation problem
Posted by av...@itellix.com.
Cool! Glad to be of help.
Regards
-
Avik
Quoting Chris Cheshire <cc...@bigredwire.com>:
> Avik,
>
> Thanks for the sanity check. I was still using the old jar in the
> classpath. I have really changed to the 2.0RC1 this time, and yes it
> does work properly.
>
> Thanks
>
> Chris
>
> avik.sengupta@itellix.com wrote:
>
> >Well, thats strange .. RC1 is supposed to fix a major file corruption issue
> for
> >large files.. see bug 15375 in bugzilla... many people have tested the
> fix....
> >http://nagoya.apache.org/bugzilla/show_bug.cgi?id=15375
> >
> >
> >Are you sure you you had RC1 in the classpath?
> >
> >If RC1 still has that problem, its unfortunately back to reading BiffViewer
> >dumps. What you can do is try to isolate the problem thru trial and error,
> and
> >see which cell is causing the error. Thats the usual approach to this kind
> of an
> >issue.. it aint pretty!
> >
> >If you can isolate the problem enuf to send the list a testcase that
> replicates
> >the problem, we can have a look at it.
> >
> >Regards
> >-
> >Avik
> >
> >Quoting Chris Cheshire <cc...@bigredwire.com>:
> >
> >
> >
> >>I am using POI to build spreadsheets of data read from the database,
> >>which then get emailed out as internal reports once a week. Up until
> >>last week I was using a 1.9 dev release and everything worked fine, but
> >>I got an excel (2000) error box saying "Unable to read file". I tried
> >>opening the spreadsheet on open office on a unix machine and it opened
> >>fine except for an error saying it exceeded 32000 rows. I know the
> >>spreadsheet doesn't have enough data to exceed 64000 though. This week I
> >>updated to the 2.0RC1 build, and got exactly the same problem. Can
> >>anyone point me in the right direction to how to track this error down
> >>please?
> >>
> >>Thanks
> >>
> >>Chris
> >>
> >>
> >>
> >>---------------------------------------------------------------------
> >>To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
> >>For additional commands, e-mail: poi-user-help@jakarta.apache.org
> >>
> >>
> >>
> >>
> >
> >
> >
> >
> >
> >---------------------------------------------------------------------
> >To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
> >For additional commands, e-mail: poi-user-help@jakarta.apache.org
> >
> >
> >
> >
> >
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: poi-user-help@jakarta.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: poi-user-help@jakarta.apache.org
Re: xls generation problem
Posted by Chris Cheshire <cc...@bigredwire.com>.
Avik,
Thanks for the sanity check. I was still using the old jar in the
classpath. I have really changed to the 2.0RC1 this time, and yes it
does work properly.
Thanks
Chris
avik.sengupta@itellix.com wrote:
>Well, thats strange .. RC1 is supposed to fix a major file corruption issue for
>large files.. see bug 15375 in bugzilla... many people have tested the fix....
>http://nagoya.apache.org/bugzilla/show_bug.cgi?id=15375
>
>
>Are you sure you you had RC1 in the classpath?
>
>If RC1 still has that problem, its unfortunately back to reading BiffViewer
>dumps. What you can do is try to isolate the problem thru trial and error, and
>see which cell is causing the error. Thats the usual approach to this kind of an
>issue.. it aint pretty!
>
>If you can isolate the problem enuf to send the list a testcase that replicates
>the problem, we can have a look at it.
>
>Regards
>-
>Avik
>
>Quoting Chris Cheshire <cc...@bigredwire.com>:
>
>
>
>>I am using POI to build spreadsheets of data read from the database,
>>which then get emailed out as internal reports once a week. Up until
>>last week I was using a 1.9 dev release and everything worked fine, but
>>I got an excel (2000) error box saying "Unable to read file". I tried
>>opening the spreadsheet on open office on a unix machine and it opened
>>fine except for an error saying it exceeded 32000 rows. I know the
>>spreadsheet doesn't have enough data to exceed 64000 though. This week I
>>updated to the 2.0RC1 build, and got exactly the same problem. Can
>>anyone point me in the right direction to how to track this error down
>>please?
>>
>>Thanks
>>
>>Chris
>>
>>
>>
>>---------------------------------------------------------------------
>>To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
>>For additional commands, e-mail: poi-user-help@jakarta.apache.org
>>
>>
>>
>>
>
>
>
>
>
>---------------------------------------------------------------------
>To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
>For additional commands, e-mail: poi-user-help@jakarta.apache.org
>
>
>
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: poi-user-help@jakarta.apache.org
Re: xls generation problem
Posted by av...@itellix.com.
Well, thats strange .. RC1 is supposed to fix a major file corruption issue for
large files.. see bug 15375 in bugzilla... many people have tested the fix....
http://nagoya.apache.org/bugzilla/show_bug.cgi?id=15375
Are you sure you you had RC1 in the classpath?
If RC1 still has that problem, its unfortunately back to reading BiffViewer
dumps. What you can do is try to isolate the problem thru trial and error, and
see which cell is causing the error. Thats the usual approach to this kind of an
issue.. it aint pretty!
If you can isolate the problem enuf to send the list a testcase that replicates
the problem, we can have a look at it.
Regards
-
Avik
Quoting Chris Cheshire <cc...@bigredwire.com>:
> I am using POI to build spreadsheets of data read from the database,
> which then get emailed out as internal reports once a week. Up until
> last week I was using a 1.9 dev release and everything worked fine, but
> I got an excel (2000) error box saying "Unable to read file". I tried
> opening the spreadsheet on open office on a unix machine and it opened
> fine except for an error saying it exceeded 32000 rows. I know the
> spreadsheet doesn't have enough data to exceed 64000 though. This week I
> updated to the 2.0RC1 build, and got exactly the same problem. Can
> anyone point me in the right direction to how to track this error down
> please?
>
> Thanks
>
> Chris
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: poi-user-help@jakarta.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: poi-user-help@jakarta.apache.org
xls generation problem
Posted by Chris Cheshire <cc...@bigredwire.com>.
I am using POI to build spreadsheets of data read from the database,
which then get emailed out as internal reports once a week. Up until
last week I was using a 1.9 dev release and everything worked fine, but
I got an excel (2000) error box saying "Unable to read file". I tried
opening the spreadsheet on open office on a unix machine and it opened
fine except for an error saying it exceeded 32000 rows. I know the
spreadsheet doesn't have enough data to exceed 64000 though. This week I
updated to the 2.0RC1 build, and got exactly the same problem. Can
anyone point me in the right direction to how to track this error down
please?
Thanks
Chris
---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: poi-user-help@jakarta.apache.org
Re: HSSF, Problem with a formula
Posted by av...@itellix.com.
> If I enter formula like "-N155"
Yes, the parser doesnt handle unary operators at the moment.. but the workaround
is simple, try something like "0-N155"
> I've already tried to use worksheet names without spaces,
Did you remove the quotes around the worksheet name?
Regards
-
avik
Quoting Dimitri Pissarenko <di...@gmx.net>:
> >I think its because of the space in the sheet name.. I guess the formula
> parser
> >doesnt support it... try this with a sheet name without a space... look at
>
> I've already tried to use worksheet names without spaces, but it did
> not help.
>
> Then, I decided to put the rows with the sums into the same worksheet
> and in this way to avoid references to other worksheets. It helped,
> except in one case:
>
> If I enter formula like "-N155" (i. e. the value of cell N155
> multiplied by -1), I get following exception:
>
> 2003-11-16 21:26:38,718 DEBUG [processCalculation.bsh] -
> formula=N155*(-1)
> 2003-11-16 21:26:38,718 ERROR [profile.bsh] -
> java.util.EmptyStackException
> at java.util.Stack.peek(Unknown Source)
> at java.util.Stack.pop(Unknown Source)
> at
> org.apache.poi.hssf.model.FormulaParser.createTree(FormulaParser.java:813)
> at
> org.apache.poi.hssf.model.FormulaParser.getRPNPtg(FormulaParser.java:654)
> at
> org.apache.poi.hssf.model.FormulaParser.getRPNPtg(FormulaParser.java:650)
> at
> org.apache.poi.hssf.usermodel.HSSFCell.setCellFormula(HSSFCell.java:715)
>
> How can I display a negative value of a cell?
>
> TIA
>
> dap
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: poi-user-help@jakarta.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: poi-user-help@jakarta.apache.org
Re: HSSF, Problem with a formula
Posted by Dimitri Pissarenko <di...@gmx.net>.
>I think its because of the space in the sheet name.. I guess the formula parser
>doesnt support it... try this with a sheet name without a space... look at
I've already tried to use worksheet names without spaces, but it did
not help.
Then, I decided to put the rows with the sums into the same worksheet
and in this way to avoid references to other worksheets. It helped,
except in one case:
If I enter formula like "-N155" (i. e. the value of cell N155
multiplied by -1), I get following exception:
2003-11-16 21:26:38,718 DEBUG [processCalculation.bsh] -
formula=N155*(-1)
2003-11-16 21:26:38,718 ERROR [profile.bsh] -
java.util.EmptyStackException
at java.util.Stack.peek(Unknown Source)
at java.util.Stack.pop(Unknown Source)
at
org.apache.poi.hssf.model.FormulaParser.createTree(FormulaParser.java:813)
at
org.apache.poi.hssf.model.FormulaParser.getRPNPtg(FormulaParser.java:654)
at
org.apache.poi.hssf.model.FormulaParser.getRPNPtg(FormulaParser.java:650)
at
org.apache.poi.hssf.usermodel.HSSFCell.setCellFormula(HSSFCell.java:715)
How can I display a negative value of a cell?
TIA
dap
---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: poi-user-help@jakarta.apache.org
Re: HSSF, Problem with a formula
Posted by av...@itellix.com.
I think its because of the space in the sheet name.. I guess the formula parser
doesnt support it... try this with a sheet name without a space... look at
TestFormulas and TestFormulaParser in the source for examples.. i dont have a
development environment right now so cant test it myself.
Regards
-
Avik
Quoting Dimitri Pissarenko <di...@gmx.net>:
> Hello!
>
> I'm writing an application, in which I create an Excel document with
> several worksheets. These show different values and their sums. I want
> to create an additional worksheet, where only sums of those values are
> shown.
>
> For this purpose, I created this new worksheet and tried to set a
> formula in a cell, which "points" exactly to the sum row of the
> original worksheet.
>
> The formula is
>
> 'Profile B'!N79-'Profile B'!N132
>
> ie, the difference between cell N79 in worksheet "Profile B" and cell
> N132 in worksheet "Profile B".
>
> If I enter this formula manually in an Excel document, it works.
>
> But POI generates following error message
>
> 2003-11-15 14:59:34,642 ERROR [profile.bsh] -
> java.lang.RuntimeException: Cannot Parse, sorry : Integer Expected
> at
> org.apache.poi.hssf.model.FormulaParser.Abort(FormulaParser.java:172)
> at
> org.apache.poi.hssf.model.FormulaParser.Expected(FormulaParser.java:179)
> at
> org.apache.poi.hssf.model.FormulaParser.GetNum(FormulaParser.java:276)
> at
> org.apache.poi.hssf.model.FormulaParser.Factor(FormulaParser.java:506)
> at
> org.apache.poi.hssf.model.FormulaParser.Term(FormulaParser.java:545)
> at
> org.apache.poi.hssf.model.FormulaParser.Expression(FormulaParser.java:596)
> at
> org.apache.poi.hssf.model.FormulaParser.parse(FormulaParser.java:636)
> at
> org.apache.poi.hssf.usermodel.HSSFCell.setCellFormula(HSSFCell.java:714)
> ...
>
> Does someone know, how to solve this problem?
>
> TIA
>
> Dimitri Pissarenko
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: poi-user-help@jakarta.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: poi-user-help@jakarta.apache.org