Bug 42464 - Parsing spreadsheet results in "Expected ExpPtg to be converted from Shared to Non-Shared Formula"
Summary: Parsing spreadsheet results in "Expected ExpPtg to be converted from Shared t...
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 3.0-dev
Hardware: Other other
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2007-05-20 14:00 UTC by David Karr
Modified: 2008-01-08 15:02 UTC (History)
0 users

This is a "sanitized" version of the spreadsheet that causes the problem (183.50 KB, application/vnd.ms-excel)
2007-05-20 15:19 UTC, David Karr
Ant-based test case that demonstrates both a "good" and "bad" spreadsheet (78.73 KB, application/octet-stream)
2007-12-12 13:13 UTC, David Karr

Note You need to log in before you can comment on or make changes to this bug.
Description David Karr 2007-05-20 14:00:35 UTC
I'm using v3.0-rc4.  I have a app that uses POI that I've been using to parse
spreadsheets and generate data from.  I just got a new version of the
spreadsheet with some innocuous changes, and it now fails with the exception
that follows this.  I would provide this spreadsheet in the bug, but it has
customer data in it.  I would try to narrow down the test case, but I don't even
know what this exception means, so I'm not sure what to remove from the test case.

org.apache.poi.hssf.record.RecordFormatException: Coding Error: Expected ExpPtg
to be converted from Shared to Non-Shared Formula
	at org.apache.poi.hssf.record.formula.ExpPtg.toFormulaString(ExpPtg.java:78)
	at org.apache.poi.hssf.model.FormulaParser.toFormulaString(FormulaParser.java:886)
	at org.apache.poi.hssf.model.FormulaParser.toFormulaString(FormulaParser.java:868)
	at org.apache.poi.hssf.usermodel.HSSFCell.getCellFormula(HSSFCell.java:630)
Comment 1 David Karr 2007-05-20 15:19:01 UTC
Created attachment 20222 [details]
This is a "sanitized" version of the spreadsheet that causes the problem
Comment 2 Nick Burch 2007-12-12 03:57:20 UTC
Are you able to do another version of this spreadsheet that's almost the same,
but doesn't trigger the bug?

Ideally, we want two very similar versions of the spreadsheet, one which
triggers the bug, and one that doesn't. That way, it'll be much easier for us to
spot what area the change lies in, to narrow down what's wrong.
Comment 3 David Karr 2007-12-12 06:38:32 UTC
It simply has to do with the number of columns.  If I remove columns one by one,
it will get to a state where the bug doesn't occur.  It's been a while since
I've looked at this, but I'm pretty sure it's only when accessing those "wide"
columns that it happens.  Accessing the initial columns doesn't trigger the bug.
 If that's not enough info, I can try setting up this test case again and
generate a sheet with the maximum number of columns that doesn't trigger the bug.
Comment 4 Nick Burch 2007-12-12 06:44:55 UTC
If you could knock out some columns until you have a sheet with n columns that
works, and n+1 columns that doesn't, then that'd be great.

(We want to get the two sheets as similar as possible, but have one work
completely, and the other not. Only having a sheet where some columns work and
some don't leaves a few more unknown variables than we'd like)
Comment 5 David Karr 2007-12-12 13:13:45 UTC
Created attachment 21265 [details]
Ant-based test case that demonstrates both a "good" and "bad" spreadsheet

This test case does not include the poi jar files.  They have to be copied into
the "lib" directory, and then modify the "build.properties" file.  This
information is echoed in the README.txt file in the zip file.
Comment 6 Nick Burch 2008-01-08 15:02:51 UTC
Finally fixed in trunk

Eventually tracked it down to the silliest of things - using a signed 8 bit
value instead of an unsigned 8 bit value, so very long columns weren't being
matched to their shared formulas, as the shared formula ran to column -127...

With the signing issue fixed, we can parse the formula on both spreadsheets