You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by "Karr, David" <da...@wamu.net> on 2007/08/07 02:07:17 UTC
Still struggling with "Coding Error: Expected ExpPtg to be converted from Shared to Non-Shared Formula"
A while ago I started to see cases where I would get the following
exception:
Coding Error: Expected ExpPtg to be converted from Shared to Non-Shared
Formula
I saw this when I tried to read cells from a "wide" spreadsheet. I
don't see it when I only read from low-numbered columns, but I found a
threshold where it's ok on one column and fails on the next column (for
instance, if I only read up to column BR, it's fine, but it fails on BS
(ironic)).
The error happens in this block in
"org.apache.poi.hssf.model.FormulaParser.toFormulaString(Workbook book,
Ptg[] ptgs)":
// Excel allows to have AttrPtg at position 0 (such as
Blanks) which
// do not have any operands. Skip them.
stack.push(ptgs[0].toFormulaString(book));
When this doesn't fail, when I look at the ptgs list, I see a bunch of
Ptg subclass instances, like RefVPtg (the 0th one), FuncPtg, AttrPtg,
and others.
When it fails, the only entry in the list is ExpPtg. When this is here,
it's not surprising that I get this exception, as this is what it does:
public String toFormulaString(Workbook book)
{
throw new RecordFormatException("Coding Error: Expected ExpPtg
to be converted from Shared to Non-Shared Formula");
}
I did file a bug report for this quite a while ago, and I wrote again
about it in this list 3 months ago, but I've never heard anything from
the bug report or the note I wrote 3 months ago. I would update the bug
report with a little more info that I've determined from looking at the
source (only what you see here), but I'm currently getting a 504 gateway
error trying to get to the bug db.
I tried searching through the source tree for references to the ExpPtg
class, since the error message makes me think something might be trying
to convert ExpPtg instances to something else.
The only thing I got from that search is the following:
* In org.apache.poi.hssf.dev.FormulaViewer.listFormula(FormulaRecord
record):
if (token instanceof ExpPtg) return;
* In org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator, in the static
block:
OPERATION_EVALS_MAP.put(EqualPtg.class, EqualEval.class);
//OPERATION_EVALS_MAP.put(ExpPtg.class, ExpEval.class); // TODO:
check
// this
OPERATION_EVALS_MAP.put(FuncPtg.class, FuncVarEval.class); //
TODO:
In the past, when we've seen this, we've been able to segment the
spreadsheet into multiple worksheets, and process each one
indidividually. This indicates that there's some issue with how Excel
stores its formula data for "wide" spreadsheets. After a certain point,
it must store them differently.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Still struggling with "Coding Error: Expected ExpPtg to be
converted from Shared to Non-Shared Formula"
Posted by Bruce Altner <br...@nasa.gov>.
I am out of the office until Monday, Aust 13. I will reply to your message
upon my return.
Thank you,
Bruce Altner
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
RE: Still struggling with "Coding Error: Expected ExpPtg to be converted from Shared to Non-Shared Formula"
Posted by "Karr, David" <da...@wamu.net>.
A possible workaround for this occurred to me, but I'm not sure exactly
what's involved in doing what I'm intending, and I really don't know
whether it would work.
What if I constructed a new spreadsheet in memory, consisting of the
original worksheet, copying columns A-E, and then the block of 50
columns that I want to work with? Then I would run the original process
on the constructed spreadsheet. I'm not sure what's involved with
copying a column range from one worksheet to another, and I also don't
know if I'm likely to hit the same bug with trying to copy the "far out"
columns that I get when simply trying to read them.
Any ideas?
> -----Original Message-----
> From: Karr, David
> Sent: Monday, August 06, 2007 5:07 PM
> To: POI Users List
> Subject: Still struggling with "Coding Error: Expected ExpPtg
> to be converted from Shared to Non-Shared Formula"
>
> A while ago I started to see cases where I would get the following
> exception:
>
> Coding Error: Expected ExpPtg to be converted from Shared to
> Non-Shared Formula
>
> I saw this when I tried to read cells from a "wide"
> spreadsheet. I don't see it when I only read from
> low-numbered columns, but I found a threshold where it's ok
> on one column and fails on the next column (for instance, if
> I only read up to column BR, it's fine, but it fails on BS (ironic)).
>
> The error happens in this block in
> "org.apache.poi.hssf.model.FormulaParser.toFormulaString(Workb
> ook book, Ptg[] ptgs)":
>
> // Excel allows to have AttrPtg at position 0 (such as
> Blanks) which
> // do not have any operands. Skip them.
> stack.push(ptgs[0].toFormulaString(book));
>
> When this doesn't fail, when I look at the ptgs list, I see a
> bunch of Ptg subclass instances, like RefVPtg (the 0th one),
> FuncPtg, AttrPtg, and others.
>
> When it fails, the only entry in the list is ExpPtg. When
> this is here, it's not surprising that I get this exception,
> as this is what it does:
>
> public String toFormulaString(Workbook book)
> {
> throw new RecordFormatException("Coding Error:
> Expected ExpPtg to be converted from Shared to Non-Shared Formula");
> }
>
> I did file a bug report for this quite a while ago, and I
> wrote again about it in this list 3 months ago, but I've
> never heard anything from the bug report or the note I wrote
> 3 months ago. I would update the bug report with a little
> more info that I've determined from looking at the source
> (only what you see here), but I'm currently getting a 504
> gateway error trying to get to the bug db.
>
> I tried searching through the source tree for references to
> the ExpPtg class, since the error message makes me think
> something might be trying to convert ExpPtg instances to
> something else.
>
> The only thing I got from that search is the following:
>
> * In org.apache.poi.hssf.dev.FormulaViewer.listFormula(FormulaRecord
> record):
>
> if (token instanceof ExpPtg) return;
>
> * In org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator, in the static
> block:
>
> OPERATION_EVALS_MAP.put(EqualPtg.class, EqualEval.class);
> //OPERATION_EVALS_MAP.put(ExpPtg.class,
> ExpEval.class); // TODO:
> check
> // this
> OPERATION_EVALS_MAP.put(FuncPtg.class, FuncVarEval.class); //
> TODO:
>
> In the past, when we've seen this, we've been able to segment
> the spreadsheet into multiple worksheets, and process each
> one indidividually. This indicates that there's some issue
> with how Excel stores its formula data for "wide"
> spreadsheets. After a certain point, it must store them differently.
>
> ---------------------------------------------------------------------
> 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: Still struggling with "Coding Error: Expected ExpPtg to be
converted from Shared to Non-Shared Formula"
Posted by Bruce Altner <br...@nasa.gov>.
I am out of the office until Monday, Aust 13. I will reply to your message
upon my return.
Thank you,
Bruce Altner
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
RE: Still struggling with "Coding Error: Expected ExpPtg to be converted from Shared to Non-Shared Formula"
Posted by "Karr, David" <da...@wamu.net>.
>From what I can see, this would require significant restudy and
refactoring. At this point, I'm just going to use my awkward
workaround. I don't have time to redesign and reimplement this using
the event model.
> -----Original Message-----
> From: Steve Widmar [mailto:SWIDMAR1@irf.com]
> Sent: Monday, August 06, 2007 7:37 PM
> To: POI Users List
> Subject: RE: Still struggling with "Coding Error: Expected
> ExpPtg to be converted from Shared to Non-Shared Formula"
>
> > I'm guessing the difference between the usermodel and the eventmodel
> is similar to DOM and SAX for XML?
>
> Similar, I agree. I've only played with eventmodel.
>
> I dug out some old code based on an online example, it
> probably looks a lot like
> http://poi.apache.org/hssf/how-to.html#event_api
>
> With that, I was able to see all of the 256 column cells and
> their values in all of the columns (A-IV) in the one row that
> I put formulas.
>
> So it looks like the eventmodel (or eventusermodel) has a
> chance of being helpful for wide sheets; at least it doesn't
> stop at some arbitrary column.
>
> You may have some refactoring ahead if you go this route, hehe.
>
> Cheers,
> Widmar
>
> ---------------------------------------------------------------------
> 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: Still struggling with "Coding Error: Expected ExpPtg to be
converted from Shared to Non-Shared Formula"
Posted by Nick Burch <ni...@torchbox.com>.
On Tue, 7 Aug 2007, Karr, David wrote:
> In my usermodel app, I have a "getCellValue(HSSFRow row, HSSFCell cell)"
> method. It checks the cell type, and handles each cell type. If it's a
> formula, it uses the formula evaluator to get the cell value.
You'd need to code quite a bit of that up yourself. For starters, you'd
need to track the cells, and store them somewhere so you can reference
them
One example of event usermodel code that tracks what cells it has seen is
the XLS 2 CSV code (that's also missing record aware):
http://svn.apache.org/repos/asf/poi/trunk/src/scratchpad/examples/src/org/apache/poi/hssf/eventusermodel/examples/XLS2CSVmra.java
That might prove a helpful starting point for you.
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Still struggling with "Coding Error: Expected ExpPtg to be
converted from Shared to Non-Shared Formula"
Posted by Bruce Altner <br...@nasa.gov>.
I am out of the office until Monday, Aust 13. I will reply to your message
upon my return.
Thank you,
Bruce Altner
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
RE: Still struggling with "Coding Error: Expected ExpPtg to be converted from Shared to Non-Shared Formula"
Posted by "Karr, David" <da...@wamu.net>.
Ok, I'm at least going to experiment with this. I've taken that event
listener skeleton and I've started to add in all the other events that
are hit in processing my spreadsheet, just for more information.
What I have to figure out eventually, is how I can get to the point
where I can do the same thing as my "usermodel" application, where given
a specific row and cell, what the value is in the cell. The cell could
be a formula that refers to another cell, so that all has to work.
In my usermodel app, I have a "getCellValue(HSSFRow row, HSSFCell cell)"
method. It checks the cell type, and handles each cell type. If it's a
formula, it uses the formula evaluator to get the cell value.
> -----Original Message-----
> From: Steve Widmar [mailto:SWIDMAR1@irf.com]
> Sent: Monday, August 06, 2007 7:37 PM
> To: POI Users List
> Subject: RE: Still struggling with "Coding Error: Expected
> ExpPtg to be converted from Shared to Non-Shared Formula"
>
> > I'm guessing the difference between the usermodel and the eventmodel
> is similar to DOM and SAX for XML?
>
> Similar, I agree. I've only played with eventmodel.
>
> I dug out some old code based on an online example, it
> probably looks a lot like
> http://poi.apache.org/hssf/how-to.html#event_api
>
> With that, I was able to see all of the 256 column cells and
> their values in all of the columns (A-IV) in the one row that
> I put formulas.
>
> So it looks like the eventmodel (or eventusermodel) has a
> chance of being helpful for wide sheets; at least it doesn't
> stop at some arbitrary column.
>
> You may have some refactoring ahead if you go this route, hehe.
>
> Cheers,
> Widmar
>
> ---------------------------------------------------------------------
> 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: Still struggling with "Coding Error: Expected ExpPtg to be converted from Shared to Non-Shared Formula"
Posted by Steve Widmar <SW...@irf.com>.
> I'm guessing the difference between the usermodel and the eventmodel
is similar to DOM and SAX for XML?
Similar, I agree. I've only played with eventmodel.
I dug out some old code based on an online example, it probably looks a
lot like
http://poi.apache.org/hssf/how-to.html#event_api
With that, I was able to see all of the 256 column cells and their
values in all of the columns (A-IV) in the one row that I put formulas.
So it looks like the eventmodel (or eventusermodel) has a chance of
being helpful for wide sheets; at least it doesn't stop at some
arbitrary column.
You may have some refactoring ahead if you go this route, hehe.
Cheers,
Widmar
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
RE: Still struggling with "Coding Error: Expected ExpPtg to be converted from Shared to Non-Shared Formula"
Posted by "Karr, David" <da...@wamu.net>.
> -----Original Message-----
> From: Steve Widmar [mailto:SWIDMAR1@irf.com]
> Sent: Monday, August 06, 2007 6:12 PM
> To: POI Users List
> Subject: RE: Still struggling with "Coding Error: Expected
> ExpPtg to be converted from Shared to Non-Shared Formula"
>
> David:
>
> I had typed in a bunch of suggestions, but they're gone now
> because I created a simple sheet that reproduced your error
> using the usermodel
> approach. I only got to BO (67th cell) before the same
> exception that
> you noted was raised.
>
> Here's my remaining suggestions & comments:
> 1) As a workaround, is there reason not to make sheets no
> more than 50 columns or so?
That's obviously the only practical workaround at this point. My
original spreadsheet is about 250 columns. We'll have to divide it into
several worksheets and probably execute several slightly different
command lines to process it.
> 2) Any difference if you try eventmodel, or usermodel
> (whichever one you're not using)
I'm using the usermodel, I guess. I never noticed that there was an
alternative. I'm guessing the difference between the usermodel and the
eventmodel is similar to DOM and SAX for XML?
> 3) If this is really a limitation of the current POI, I'm
> surprised the secret's kept this long.
>
> Sorry I'm not much help here.
>
> Cheers,
> Widmar
>
> Ps - I'm using [3.0.1 final]
>
> -----Original Message-----
> From: Karr, David [mailto:david.karr@wamu.net]
> Sent: Monday, August 06, 2007 6:07 PM
> To: POI Users List
> Subject: Still struggling with "Coding Error: Expected ExpPtg
> to be converted from Shared to Non-Shared Formula"
>
> A while ago I started to see cases where I would get the following
> exception:
>
> Coding Error: Expected ExpPtg to be converted from Shared to
> Non-Shared Formula
>
> I saw this when I tried to read cells from a "wide"
> spreadsheet. I don't see it when I only read from
> low-numbered columns, but I found a threshold where it's ok
> on one column and fails on the next column (for instance, if
> I only read up to column BR, it's fine, but it fails on BS (ironic)).
>
> The error happens in this block in
> "org.apache.poi.hssf.model.FormulaParser.toFormulaString(Workb
> ook book, Ptg[] ptgs)":
>
> // Excel allows to have AttrPtg at position 0 (such as
> Blanks) which
> // do not have any operands. Skip them.
> stack.push(ptgs[0].toFormulaString(book));
>
> When this doesn't fail, when I look at the ptgs list, I see a
> bunch of Ptg subclass instances, like RefVPtg (the 0th one),
> FuncPtg, AttrPtg, and others.
>
> When it fails, the only entry in the list is ExpPtg. When
> this is here, it's not surprising that I get this exception,
> as this is what it does:
>
> public String toFormulaString(Workbook book)
> {
> throw new RecordFormatException("Coding Error:
> Expected ExpPtg to be converted from Shared to Non-Shared Formula");
> }
>
> I did file a bug report for this quite a while ago, and I
> wrote again about it in this list 3 months ago, but I've
> never heard anything from the bug report or the note I wrote
> 3 months ago. I would update the bug report with a little
> more info that I've determined from looking at the source
> (only what you see here), but I'm currently getting a 504
> gateway error trying to get to the bug db.
>
> I tried searching through the source tree for references to
> the ExpPtg class, since the error message makes me think
> something might be trying to convert ExpPtg instances to
> something else.
>
> The only thing I got from that search is the following:
>
> * In org.apache.poi.hssf.dev.FormulaViewer.listFormula(FormulaRecord
> record):
>
> if (token instanceof ExpPtg) return;
>
> * In org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator, in the static
> block:
>
> OPERATION_EVALS_MAP.put(EqualPtg.class, EqualEval.class);
> //OPERATION_EVALS_MAP.put(ExpPtg.class,
> ExpEval.class); // TODO:
> check
> // this
> OPERATION_EVALS_MAP.put(FuncPtg.class, FuncVarEval.class); //
> TODO:
>
> In the past, when we've seen this, we've been able to segment
> the spreadsheet into multiple worksheets, and process each
> one indidividually. This indicates that there's some issue
> with how Excel stores its formula data for "wide"
> spreadsheets. After a certain point, it must store them differently.
>
> ---------------------------------------------------------------------
> 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
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
RE: Still struggling with "Coding Error: Expected ExpPtg to be converted from Shared to Non-Shared Formula"
Posted by Steve Widmar <SW...@irf.com>.
David:
I had typed in a bunch of suggestions, but they're gone now because I
created a simple sheet that reproduced your error using the usermodel
approach. I only got to BO (67th cell) before the same exception that
you noted was raised.
Here's my remaining suggestions & comments:
1) As a workaround, is there reason not to make sheets no more than 50
columns or so?
2) Any difference if you try eventmodel, or usermodel (whichever one
you're not using)
3) If this is really a limitation of the current POI, I'm surprised the
secret's kept this long.
Sorry I'm not much help here.
Cheers,
Widmar
Ps - I'm using [3.0.1 final]
-----Original Message-----
From: Karr, David [mailto:david.karr@wamu.net]
Sent: Monday, August 06, 2007 6:07 PM
To: POI Users List
Subject: Still struggling with "Coding Error: Expected ExpPtg to be
converted from Shared to Non-Shared Formula"
A while ago I started to see cases where I would get the following
exception:
Coding Error: Expected ExpPtg to be converted from Shared to Non-Shared
Formula
I saw this when I tried to read cells from a "wide" spreadsheet. I
don't see it when I only read from low-numbered columns, but I found a
threshold where it's ok on one column and fails on the next column (for
instance, if I only read up to column BR, it's fine, but it fails on BS
(ironic)).
The error happens in this block in
"org.apache.poi.hssf.model.FormulaParser.toFormulaString(Workbook book,
Ptg[] ptgs)":
// Excel allows to have AttrPtg at position 0 (such as
Blanks) which
// do not have any operands. Skip them.
stack.push(ptgs[0].toFormulaString(book));
When this doesn't fail, when I look at the ptgs list, I see a bunch of
Ptg subclass instances, like RefVPtg (the 0th one), FuncPtg, AttrPtg,
and others.
When it fails, the only entry in the list is ExpPtg. When this is here,
it's not surprising that I get this exception, as this is what it does:
public String toFormulaString(Workbook book)
{
throw new RecordFormatException("Coding Error: Expected ExpPtg
to be converted from Shared to Non-Shared Formula");
}
I did file a bug report for this quite a while ago, and I wrote again
about it in this list 3 months ago, but I've never heard anything from
the bug report or the note I wrote 3 months ago. I would update the bug
report with a little more info that I've determined from looking at the
source (only what you see here), but I'm currently getting a 504 gateway
error trying to get to the bug db.
I tried searching through the source tree for references to the ExpPtg
class, since the error message makes me think something might be trying
to convert ExpPtg instances to something else.
The only thing I got from that search is the following:
* In org.apache.poi.hssf.dev.FormulaViewer.listFormula(FormulaRecord
record):
if (token instanceof ExpPtg) return;
* In org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator, in the static
block:
OPERATION_EVALS_MAP.put(EqualPtg.class, EqualEval.class);
//OPERATION_EVALS_MAP.put(ExpPtg.class, ExpEval.class); // TODO:
check
// this
OPERATION_EVALS_MAP.put(FuncPtg.class, FuncVarEval.class); //
TODO:
In the past, when we've seen this, we've been able to segment the
spreadsheet into multiple worksheets, and process each one
indidividually. This indicates that there's some issue with how Excel
stores its formula data for "wide" spreadsheets. After a certain point,
it must store them differently.
---------------------------------------------------------------------
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