You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Eric Peters <Er...@Peters.org> on 2013/07/17 23:31:31 UTC
Getting Errors & Formulas from HSSF FormulaRecord (event model)
Based upon
http://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/hssf/eventusermodel/examples/XLS2CSVmra.java
I have a modified case FormulaRecord.sid:
case FormulaRecord.sid:
FormulaRecord frec = (FormulaRecord) record;
thisRow = frec.getRow();
thisColumn = frec.getColumn();
if(Double.isNaN( frec.getValue() )) {
// Formula result is a string
// This is stored in the next record
outputNextStringRecord = true;
nextRow = frec.getRow();
nextColumn = frec.getColumn();
} else {
thisStr = formatListener.formatNumberDateCell(frec);
}
I was expecting to be able to get the result of a formula (One example I
have a CONCATENATE() statement, but I end up getting a value like "0.00" or
in another column I expect an error message like: #DIV/0! but instead get
"0"Thoughts on how I can get this to match up with the XSSF?
Thanks,
Eric
- XLSStreamReader - Values *** FAILED ***
Vector(Vector(text_value1, formatted_value1, TRUE, 8/7/12, 5:01:01 AM,
8/7/12 5:01, 1, 1.10, 0.00, 0, sharedstrings_value), Vector(,
formatted_value2, FALSE, 8/8/12, 5:02:02 AM, 8/8/12 5:02, 2, 2.20, 0.00, 0,
sharedstrings_value), Vector(text_value3, formatted_value3, TRUE, 8/9/12,
5:03:03 AM, 8/9/12 5:03, 3, 3.30, 0.00, 0), Vector(text_value4,
formatted_value4, FALSE, 8/10/12, 5:04:04 AM, 8/10/12 5:04, 4, 4.40, 0.00,
0, sharedstrings_value), Vector(text_value5, formatted_value5, TRUE, ,
5:05:05 AM, 8/11/12 5:05, 5, 5.50, 0.00, 0, sharedstrings_value),
Vector(text_value6, formatted_value6, FALSE, 8/12/12, 5:06:06 AM, 8/12/12
5:06, 6, 6.60, 0.00, 0, sharedstrings_value)) did not equal
Vector(Vector(text_value1, formatted_value1, TRUE, 8/7/12, 5:01:01 AM,
8/7/12 5:01, 1, 1.10, 11.1whee, #DIV/0!, sharedstrings_value), Vector(,
formatted_value2, FALSE, 8/8/12, 5:02:02 AM, 8/8/12 5:02, 2, 2.20,
22.2whee, #DIV/0!, sharedstrings_value), Vector(text_value3,
formatted_value3, TRUE, 8/9/12, 5:03:03 AM, 8/9/12 5:03, 3, 3.30, 33.3whee,
#DIV/0!, ), Vector(text_value4, formatted_value4, FALSE, 8/10/12, 5:04:04
AM, 8/10/12 5:04, 4, 4.40, 44.4whee, #DIV/0!, sharedstrings_value),
Vector(text_value5, formatted_value5, TRUE, , 5:05:05 AM, 8/11/12 5:05, 5,
5.50, 55.5whee, #DIV/0!, sharedstrings_value), Vector(text_value6,
formatted_value6, FALSE, 8/12/12, 5:06:06 AM, 8/12/12 5:06, 6, 6.60,
66.6whee, #DIV/0!, sharedstrings_value)) (TestXLSStreamReader.scala:21)
Re: Getting Errors & Formulas from HSSF FormulaRecord (event
model)
Posted by Nick Burch <ap...@gagravarr.org>.
On Mon, 22 Jul 2013, Eric Peters wrote:
> That said I'll see if I can at least update that XLS2CSVmra.java example
> - do you have any idea what the mra is suppose to mean?
MRA = Missing Record Aware
There was an earlier XLS -> CSV converter that wasn't, which had the
unfortunate effect of not outputting the correct number of commas...
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Getting Errors & Formulas from HSSF FormulaRecord (event model)
Posted by Eric Peters <Er...@Peters.org>.
I hesitate to even attempt to patch one of the examples, in that it's work
for me right now (& my unit tests) but as you pointed out, I'm a newbie and
don't have the background on why/how things were done (Why was it
doing if(Double.isNaN(
frec.getValue() )) {????)
I also can't guarantee my test coverage isn't complete for even the example
code I posted below.
Not to mention, I've been programming on Scala so much recently, I don't
even know if I could write java code anymore :)
That said I'll see if I can at least update that XLS2CSVmra.java example -
do you have any idea what the mra is suppose to mean?
-Eric
On Mon, Jul 22, 2013 at 2:12 PM, Nick Burch <ap...@gagravarr.org> wrote:
> Hi EriC
>
> If you think there are gaps in the examples, or places where the javadocs
> aren't ideal, do please open issues in our bugzilla instance, and ideally
> upload suggested fixes / patches :)
>
> (We need suggested fixes from new people, as they come at it with fresh
> eyes, and can point out the bits that older timers just skip over as they
> know how it works already!)
>
> Nick
>
>
> On Thu, 18 Jul 2013, Eric Peters wrote:
>
> Thanks Nick!
>>
>> Those two references helped a ton. I've solved the problem, and posting
>> some of my steps here just for Google Fodder for future people.
>>
>> https://svn.apache.org/repos/**asf/tika/trunk/tika-parsers/**
>> src/main/java/org/apache/tika/**parser/microsoft/**ExcelExtractor.javahad<https://svn.apache.org/repos/asf/tika/trunk/tika-parsers/src/main/java/org/apache/tika/parser/microsoft/ExcelExtractor.javahad>
>> the first secret I was missing (
>> formula.hasCachedResultString(**)):
>>
>> This worked to print the value of a good formula:
>> if(frec.hasCachedResultString(**)) {
>> // Formula result is a string
>> // This is stored in the next record
>> outputNextStringRecord = true
>> nextRow = frec.getRow()
>> nextColumn = frec.getColumn()
>> } else {
>> thisStr = formatListener.**formatNumberDateCell(frec)
>> }
>>
>> Where this didn't (maybe that original example I referenced could get
>> updated?):
>>
>> if(Double.isNaN( frec.getValue() )) {
>> // Formula result is a string
>> // This is stored in the next record
>> outputNextStringRecord = true
>> nextRow = frec.getRow()
>> nextColumn = frec.getColumn()
>> } else {
>> thisStr = formatListener.**formatNumberDateCell(frec)
>> }
>>
>> The only missing piece now is displaying the "#DIV/0" type of error
>> messages.
>>
>> For my particular Error Formula, frec.hasCachedResultString() returns
>> false
>>
>> frec.getCachedErrorValue() returns: 7 (
>> http://poi.apache.org/apidocs/**org/apache/poi/hssf/record/**
>> FormulaRecord.html#**getCachedErrorValue()is<http://poi.apache.org/apidocs/org/apache/poi/hssf/record/FormulaRecord.html#getCachedErrorValue()is>
>> a very bad javadoc) - not sure if its related to
>> http://poi.apache.org/apidocs/**org/apache/poi/ss/usermodel/**
>> FormulaError.html<http://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/FormulaError.html>
>>
>> frec.getParsedExpression() is an array of three:
>> expression: org.apache.poi.ss.formula.ptg.**RefPtg [G2]
>> expression: org.apache.poi.ss.formula.ptg.**IntPtg [0]
>> expression: class org.apache.poi.ss.formula.ptg.**DividePtg
>>
>>
>> I tried printing frec.getCachedErrorValue() for each Formula entry to see
>> what a "normal' value might look like, only to find out I triggered an
>> exception on my first "good formula":
>>
>> 09:29:44.705 [main] ERROR fm.util.Resource - Caught exception using
>> resource
>> java.lang.**IllegalStateException: Not an error cached value - <string>
>> at
>> org.apache.poi.hssf.record.**FormulaRecord$**SpecialCachedValue.**
>> getErrorValue(FormulaRecord.**java:163)
>> ~[poi-3.9.jar:3.9]
>> at
>> org.apache.poi.hssf.record.**FormulaRecord.**getCachedErrorValue(**
>> FormulaRecord.java:252)
>> ~[poi-3.9.jar:3.9]
>> at
>> fm.util.XLSStreamProcessor.**processRecord(XLSStreamReader.**scala:153)
>> ~[classes/:na]
>> at
>> org.apache.poi.hssf.**eventusermodel.**MissingRecordAwareHSSFListener**
>> .processRecord(**MissingRecordAwareHSSFListener**.java:189)
>> ~[poi-3.9.jar:3.9]
>> at
>> org.apache.poi.hssf.**eventusermodel.**FormatTrackingHSSFListener.**
>> processRecord(**FormatTrackingHSSFListener.**java:85)
>> ~[poi-3.9.jar:3.9]
>> at
>> org.apache.poi.hssf.**eventusermodel.HSSFRequest.**
>> processRecord(HSSFRequest.**java:112)
>> ~[poi-3.9.jar:3.9]
>>
>> Based upon the methods available to:
>> http://poi.apache.org/apidocs/**org/apache/poi/hssf/record/**
>> FormulaRecord.htmlI<http://poi.apache.org/apidocs/org/apache/poi/hssf/record/FormulaRecord.htmlI>
>> wasn't quite sure howto check for what the formula type was, but based
>> upon:
>> http://grepcode.com/file_/**repo1.maven.org/maven2/org.**
>> apache.poi/poi/3.9/org/apache/**poi/hssf/record/FormulaRecord.**
>> java/?v=source<http://grepcode.com/file_/repo1.maven.org/maven2/org.apache.poi/poi/3.9/org/apache/poi/hssf/record/FormulaRecord.java/?v=source>
>> I was able to see how FormulaRecord was dealing with the various
>> fields
>> under the covers. The result is I've so far pieced this together, which
>> seems to do the trick:
>>
>> if(frec.hasCachedResultString(**)) {
>> // Formula result is a string
>> // This is stored in the next record
>> outputNextStringRecord = true
>> nextRow = frec.getRow()
>> nextColumn = frec.getColumn()
>> } else {
>> thisStr = frec.getCachedResultType match {
>> case Cell.CELL_TYPE_STRING =>
>> formatListener.**formatNumberDateCell(frec)
>> case Cell.CELL_TYPE_BOOLEAN => frec.getCachedBooleanValue()
>> match {
>> case false => "FALSE"
>> case true => "TRUE"
>> }
>> case Cell.CELL_TYPE_ERROR =>
>> ErrorEval.getText(frec.**getCachedErrorValue())
>> case _ => logger.warn("Unknown Formula Result Type");
>> "<unknown>"
>> }
>> }
>>
>> Now my test file looks exactly like my XSSF:
>>
>> Vector(
>> Vector(text_value1, formatted_value1, TRUE, 8/7/12, 5:01:01 AM, 8/7/12
>> 5:01, 1, 1.10, 11.1whee, #DIV/0!, sharedstrings_value),
>> Vector(, formatted_value2, FALSE, 8/8/12, 5:02:02 AM, 8/8/12 5:02, 2,
>> 2.20, 22.2whee, #DIV/0!, sharedstrings_value),
>> Vector(text_value3, formatted_value3, TRUE, 8/9/12, 5:03:03 AM, 8/9/12
>> 5:03, 3, 3.30, 33.3whee, #DIV/0!, ),
>> Vector(text_value4, formatted_value4, FALSE, 8/10/12, 5:04:04 AM, 8/10/12
>> 5:04, 4, 4.40, 44.4whee, #DIV/0!, sharedstrings_value),
>> Vector(text_value5, formatted_value5, TRUE, , 5:05:05 AM, 8/11/12 5:05,
>> 5, 5.50, 55.5whee, #DIV/0!, sharedstrings_value),
>> Vector(text_value6, formatted_value6, FALSE, 8/12/12, 5:06:06 AM, 8/12/12
>> 5:06, 6, 6.60, 66.6whee, #DIV/0!, sharedstrings_value)
>> )
>>
>> Thanks again Nick!
>>
>> Eric
>>
>>
>> On Thu, Jul 18, 2013 at 3:08 AM, Nick Burch <ap...@gagravarr.org> wrote:
>>
>> On Wed, 17 Jul 2013, Eric Peters wrote:
>>>
>>> Based upon
>>>> http://svn.apache.org/repos/****asf/poi/trunk/src/examples/**<http://svn.apache.org/repos/**asf/poi/trunk/src/examples/**>
>>>> src/org/apache/poi/hssf/****eventusermodel/examples/****
>>>> XLS2CSVmra.java<http://svn.**apache.org/repos/asf/poi/**
>>>> trunk/src/examples/src/org/**apache/poi/hssf/**eventusermodel/examples/
>>>> **XLS2CSVmra.java<http://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/hssf/eventusermodel/examples/XLS2CSVmra.java>
>>>> >
>>>>
>>>>
>>> Two other good event based examples to view are:
>>> https://svn.apache.org/repos/****asf/poi/trunk/src/java/org/**<https://svn.apache.org/repos/**asf/poi/trunk/src/java/org/**>
>>> apache/poi/hssf/extractor/****EventBasedExcelExtractor.java<**
>>> https://svn.apache.org/repos/**asf/poi/trunk/src/java/org/**
>>> apache/poi/hssf/extractor/**EventBasedExcelExtractor.java<https://svn.apache.org/repos/asf/poi/trunk/src/java/org/apache/poi/hssf/extractor/EventBasedExcelExtractor.java>
>>> >
>>> https://svn.apache.org/repos/****asf/tika/trunk/tika-parsers/****<https://svn.apache.org/repos/**asf/tika/trunk/tika-parsers/**>
>>> src/main/java/org/apache/tika/****parser/microsoft/****
>>> ExcelExtractor.java<https://**svn.apache.org/repos/asf/tika/**
>>> trunk/tika-parsers/src/main/**java/org/apache/tika/parser/**
>>> microsoft/ExcelExtractor.java<https://svn.apache.org/repos/asf/tika/trunk/tika-parsers/src/main/java/org/apache/tika/parser/microsoft/ExcelExtractor.java>
>>> >
>>>
>>>
>>>
>>> I was expecting to be able to get the result of a formula (One example I
>>>
>>>> have a CONCATENATE() statement, but I end up getting a value like
>>>> "0.00" or
>>>> in another column I expect an error message like: #DIV/0! but instead
>>>> get
>>>> "0"Thoughts on how I can get this to match up with the XSSF?
>>>>
>>>>
>>> Can you try reading that cell via the usermodel, to check if a cached
>>> value was actually saved to the file?
>>>
>>> Nick
>>>
>>> ------------------------------****----------------------------**
>>> --**---------
>>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.****org<
>>> user-unsubscribe@poi.**apache.org <us...@poi.apache.org>>
>>>
>>> For additional commands, e-mail: user-help@poi.apache.org
>>>
>>>
>>>
>>
> ------------------------------**------------------------------**---------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.**org<us...@poi.apache.org>
> For additional commands, e-mail: user-help@poi.apache.org
>
>
Re: Getting Errors & Formulas from HSSF FormulaRecord (event
model)
Posted by Nick Burch <ap...@gagravarr.org>.
Hi EriC
If you think there are gaps in the examples, or places where the javadocs
aren't ideal, do please open issues in our bugzilla instance, and ideally
upload suggested fixes / patches :)
(We need suggested fixes from new people, as they come at it with fresh
eyes, and can point out the bits that older timers just skip over as they
know how it works already!)
Nick
On Thu, 18 Jul 2013, Eric Peters wrote:
> Thanks Nick!
>
> Those two references helped a ton. I've solved the problem, and posting
> some of my steps here just for Google Fodder for future people.
>
> https://svn.apache.org/repos/asf/tika/trunk/tika-parsers/src/main/java/org/apache/tika/parser/microsoft/ExcelExtractor.javahad
> the first secret I was missing (
> formula.hasCachedResultString()):
>
> This worked to print the value of a good formula:
> if(frec.hasCachedResultString()) {
> // Formula result is a string
> // This is stored in the next record
> outputNextStringRecord = true
> nextRow = frec.getRow()
> nextColumn = frec.getColumn()
> } else {
> thisStr = formatListener.formatNumberDateCell(frec)
> }
>
> Where this didn't (maybe that original example I referenced could get
> updated?):
>
> if(Double.isNaN( frec.getValue() )) {
> // Formula result is a string
> // This is stored in the next record
> outputNextStringRecord = true
> nextRow = frec.getRow()
> nextColumn = frec.getColumn()
> } else {
> thisStr = formatListener.formatNumberDateCell(frec)
> }
>
> The only missing piece now is displaying the "#DIV/0" type of error
> messages.
>
> For my particular Error Formula, frec.hasCachedResultString() returns false
>
> frec.getCachedErrorValue() returns: 7 (
> http://poi.apache.org/apidocs/org/apache/poi/hssf/record/FormulaRecord.html#getCachedErrorValue()is
> a very bad javadoc) - not sure if its related to
> http://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/FormulaError.html
>
> frec.getParsedExpression() is an array of three:
> expression: org.apache.poi.ss.formula.ptg.RefPtg [G2]
> expression: org.apache.poi.ss.formula.ptg.IntPtg [0]
> expression: class org.apache.poi.ss.formula.ptg.DividePtg
>
>
> I tried printing frec.getCachedErrorValue() for each Formula entry to see
> what a "normal' value might look like, only to find out I triggered an
> exception on my first "good formula":
>
> 09:29:44.705 [main] ERROR fm.util.Resource - Caught exception using resource
> java.lang.IllegalStateException: Not an error cached value - <string>
> at
> org.apache.poi.hssf.record.FormulaRecord$SpecialCachedValue.getErrorValue(FormulaRecord.java:163)
> ~[poi-3.9.jar:3.9]
> at
> org.apache.poi.hssf.record.FormulaRecord.getCachedErrorValue(FormulaRecord.java:252)
> ~[poi-3.9.jar:3.9]
> at
> fm.util.XLSStreamProcessor.processRecord(XLSStreamReader.scala:153)
> ~[classes/:na]
> at
> org.apache.poi.hssf.eventusermodel.MissingRecordAwareHSSFListener.processRecord(MissingRecordAwareHSSFListener.java:189)
> ~[poi-3.9.jar:3.9]
> at
> org.apache.poi.hssf.eventusermodel.FormatTrackingHSSFListener.processRecord(FormatTrackingHSSFListener.java:85)
> ~[poi-3.9.jar:3.9]
> at
> org.apache.poi.hssf.eventusermodel.HSSFRequest.processRecord(HSSFRequest.java:112)
> ~[poi-3.9.jar:3.9]
>
> Based upon the methods available to:
> http://poi.apache.org/apidocs/org/apache/poi/hssf/record/FormulaRecord.htmlI
> wasn't quite sure howto check for what the formula type was, but based
> upon:
> http://grepcode.com/file_/repo1.maven.org/maven2/org.apache.poi/poi/3.9/org/apache/poi/hssf/record/FormulaRecord.java/?v=source
> I was able to see how FormulaRecord was dealing with the various
> fields
> under the covers. The result is I've so far pieced this together, which
> seems to do the trick:
>
> if(frec.hasCachedResultString()) {
> // Formula result is a string
> // This is stored in the next record
> outputNextStringRecord = true
> nextRow = frec.getRow()
> nextColumn = frec.getColumn()
> } else {
> thisStr = frec.getCachedResultType match {
> case Cell.CELL_TYPE_STRING =>
> formatListener.formatNumberDateCell(frec)
> case Cell.CELL_TYPE_BOOLEAN => frec.getCachedBooleanValue()
> match {
> case false => "FALSE"
> case true => "TRUE"
> }
> case Cell.CELL_TYPE_ERROR =>
> ErrorEval.getText(frec.getCachedErrorValue())
> case _ => logger.warn("Unknown Formula Result Type");
> "<unknown>"
> }
> }
>
> Now my test file looks exactly like my XSSF:
>
> Vector(
> Vector(text_value1, formatted_value1, TRUE, 8/7/12, 5:01:01 AM, 8/7/12
> 5:01, 1, 1.10, 11.1whee, #DIV/0!, sharedstrings_value),
> Vector(, formatted_value2, FALSE, 8/8/12, 5:02:02 AM, 8/8/12 5:02, 2,
> 2.20, 22.2whee, #DIV/0!, sharedstrings_value),
> Vector(text_value3, formatted_value3, TRUE, 8/9/12, 5:03:03 AM, 8/9/12
> 5:03, 3, 3.30, 33.3whee, #DIV/0!, ),
> Vector(text_value4, formatted_value4, FALSE, 8/10/12, 5:04:04 AM, 8/10/12
> 5:04, 4, 4.40, 44.4whee, #DIV/0!, sharedstrings_value),
> Vector(text_value5, formatted_value5, TRUE, , 5:05:05 AM, 8/11/12 5:05,
> 5, 5.50, 55.5whee, #DIV/0!, sharedstrings_value),
> Vector(text_value6, formatted_value6, FALSE, 8/12/12, 5:06:06 AM, 8/12/12
> 5:06, 6, 6.60, 66.6whee, #DIV/0!, sharedstrings_value)
> )
>
> Thanks again Nick!
>
> Eric
>
>
> On Thu, Jul 18, 2013 at 3:08 AM, Nick Burch <ap...@gagravarr.org> wrote:
>
>> On Wed, 17 Jul 2013, Eric Peters wrote:
>>
>>> Based upon
>>> http://svn.apache.org/repos/**asf/poi/trunk/src/examples/**
>>> src/org/apache/poi/hssf/**eventusermodel/examples/**XLS2CSVmra.java<http://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/hssf/eventusermodel/examples/XLS2CSVmra.java>
>>>
>>
>> Two other good event based examples to view are:
>> https://svn.apache.org/repos/**asf/poi/trunk/src/java/org/**
>> apache/poi/hssf/extractor/**EventBasedExcelExtractor.java<https://svn.apache.org/repos/asf/poi/trunk/src/java/org/apache/poi/hssf/extractor/EventBasedExcelExtractor.java>
>> https://svn.apache.org/repos/**asf/tika/trunk/tika-parsers/**
>> src/main/java/org/apache/tika/**parser/microsoft/**ExcelExtractor.java<https://svn.apache.org/repos/asf/tika/trunk/tika-parsers/src/main/java/org/apache/tika/parser/microsoft/ExcelExtractor.java>
>>
>>
>> I was expecting to be able to get the result of a formula (One example I
>>> have a CONCATENATE() statement, but I end up getting a value like "0.00" or
>>> in another column I expect an error message like: #DIV/0! but instead get
>>> "0"Thoughts on how I can get this to match up with the XSSF?
>>>
>>
>> Can you try reading that cell via the usermodel, to check if a cached
>> value was actually saved to the file?
>>
>> Nick
>>
>> ------------------------------**------------------------------**---------
>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.**org<us...@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: Getting Errors & Formulas from HSSF FormulaRecord (event model)
Posted by Eric Peters <Er...@Peters.org>.
Thanks Nick!
Those two references helped a ton. I've solved the problem, and posting
some of my steps here just for Google Fodder for future people.
https://svn.apache.org/repos/asf/tika/trunk/tika-parsers/src/main/java/org/apache/tika/parser/microsoft/ExcelExtractor.javahad
the first secret I was missing (
formula.hasCachedResultString()):
This worked to print the value of a good formula:
if(frec.hasCachedResultString()) {
// Formula result is a string
// This is stored in the next record
outputNextStringRecord = true
nextRow = frec.getRow()
nextColumn = frec.getColumn()
} else {
thisStr = formatListener.formatNumberDateCell(frec)
}
Where this didn't (maybe that original example I referenced could get
updated?):
if(Double.isNaN( frec.getValue() )) {
// Formula result is a string
// This is stored in the next record
outputNextStringRecord = true
nextRow = frec.getRow()
nextColumn = frec.getColumn()
} else {
thisStr = formatListener.formatNumberDateCell(frec)
}
The only missing piece now is displaying the "#DIV/0" type of error
messages.
For my particular Error Formula, frec.hasCachedResultString() returns false
frec.getCachedErrorValue() returns: 7 (
http://poi.apache.org/apidocs/org/apache/poi/hssf/record/FormulaRecord.html#getCachedErrorValue()is
a very bad javadoc) - not sure if its related to
http://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/FormulaError.html
frec.getParsedExpression() is an array of three:
expression: org.apache.poi.ss.formula.ptg.RefPtg [G2]
expression: org.apache.poi.ss.formula.ptg.IntPtg [0]
expression: class org.apache.poi.ss.formula.ptg.DividePtg
I tried printing frec.getCachedErrorValue() for each Formula entry to see
what a "normal' value might look like, only to find out I triggered an
exception on my first "good formula":
09:29:44.705 [main] ERROR fm.util.Resource - Caught exception using resource
java.lang.IllegalStateException: Not an error cached value - <string>
at
org.apache.poi.hssf.record.FormulaRecord$SpecialCachedValue.getErrorValue(FormulaRecord.java:163)
~[poi-3.9.jar:3.9]
at
org.apache.poi.hssf.record.FormulaRecord.getCachedErrorValue(FormulaRecord.java:252)
~[poi-3.9.jar:3.9]
at
fm.util.XLSStreamProcessor.processRecord(XLSStreamReader.scala:153)
~[classes/:na]
at
org.apache.poi.hssf.eventusermodel.MissingRecordAwareHSSFListener.processRecord(MissingRecordAwareHSSFListener.java:189)
~[poi-3.9.jar:3.9]
at
org.apache.poi.hssf.eventusermodel.FormatTrackingHSSFListener.processRecord(FormatTrackingHSSFListener.java:85)
~[poi-3.9.jar:3.9]
at
org.apache.poi.hssf.eventusermodel.HSSFRequest.processRecord(HSSFRequest.java:112)
~[poi-3.9.jar:3.9]
Based upon the methods available to:
http://poi.apache.org/apidocs/org/apache/poi/hssf/record/FormulaRecord.htmlI
wasn't quite sure howto check for what the formula type was, but based
upon:
http://grepcode.com/file_/repo1.maven.org/maven2/org.apache.poi/poi/3.9/org/apache/poi/hssf/record/FormulaRecord.java/?v=source
I was able to see how FormulaRecord was dealing with the various
fields
under the covers. The result is I've so far pieced this together, which
seems to do the trick:
if(frec.hasCachedResultString()) {
// Formula result is a string
// This is stored in the next record
outputNextStringRecord = true
nextRow = frec.getRow()
nextColumn = frec.getColumn()
} else {
thisStr = frec.getCachedResultType match {
case Cell.CELL_TYPE_STRING =>
formatListener.formatNumberDateCell(frec)
case Cell.CELL_TYPE_BOOLEAN => frec.getCachedBooleanValue()
match {
case false => "FALSE"
case true => "TRUE"
}
case Cell.CELL_TYPE_ERROR =>
ErrorEval.getText(frec.getCachedErrorValue())
case _ => logger.warn("Unknown Formula Result Type");
"<unknown>"
}
}
Now my test file looks exactly like my XSSF:
Vector(
Vector(text_value1, formatted_value1, TRUE, 8/7/12, 5:01:01 AM, 8/7/12
5:01, 1, 1.10, 11.1whee, #DIV/0!, sharedstrings_value),
Vector(, formatted_value2, FALSE, 8/8/12, 5:02:02 AM, 8/8/12 5:02, 2,
2.20, 22.2whee, #DIV/0!, sharedstrings_value),
Vector(text_value3, formatted_value3, TRUE, 8/9/12, 5:03:03 AM, 8/9/12
5:03, 3, 3.30, 33.3whee, #DIV/0!, ),
Vector(text_value4, formatted_value4, FALSE, 8/10/12, 5:04:04 AM, 8/10/12
5:04, 4, 4.40, 44.4whee, #DIV/0!, sharedstrings_value),
Vector(text_value5, formatted_value5, TRUE, , 5:05:05 AM, 8/11/12 5:05,
5, 5.50, 55.5whee, #DIV/0!, sharedstrings_value),
Vector(text_value6, formatted_value6, FALSE, 8/12/12, 5:06:06 AM, 8/12/12
5:06, 6, 6.60, 66.6whee, #DIV/0!, sharedstrings_value)
)
Thanks again Nick!
Eric
On Thu, Jul 18, 2013 at 3:08 AM, Nick Burch <ap...@gagravarr.org> wrote:
> On Wed, 17 Jul 2013, Eric Peters wrote:
>
>> Based upon
>> http://svn.apache.org/repos/**asf/poi/trunk/src/examples/**
>> src/org/apache/poi/hssf/**eventusermodel/examples/**XLS2CSVmra.java<http://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/hssf/eventusermodel/examples/XLS2CSVmra.java>
>>
>
> Two other good event based examples to view are:
> https://svn.apache.org/repos/**asf/poi/trunk/src/java/org/**
> apache/poi/hssf/extractor/**EventBasedExcelExtractor.java<https://svn.apache.org/repos/asf/poi/trunk/src/java/org/apache/poi/hssf/extractor/EventBasedExcelExtractor.java>
> https://svn.apache.org/repos/**asf/tika/trunk/tika-parsers/**
> src/main/java/org/apache/tika/**parser/microsoft/**ExcelExtractor.java<https://svn.apache.org/repos/asf/tika/trunk/tika-parsers/src/main/java/org/apache/tika/parser/microsoft/ExcelExtractor.java>
>
>
> I was expecting to be able to get the result of a formula (One example I
>> have a CONCATENATE() statement, but I end up getting a value like "0.00" or
>> in another column I expect an error message like: #DIV/0! but instead get
>> "0"Thoughts on how I can get this to match up with the XSSF?
>>
>
> Can you try reading that cell via the usermodel, to check if a cached
> value was actually saved to the file?
>
> Nick
>
> ------------------------------**------------------------------**---------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.**org<us...@poi.apache.org>
> For additional commands, e-mail: user-help@poi.apache.org
>
>
Re: Getting Errors & Formulas from HSSF FormulaRecord (event
model)
Posted by Nick Burch <ap...@gagravarr.org>.
On Wed, 17 Jul 2013, Eric Peters wrote:
> Based upon
> http://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/hssf/eventusermodel/examples/XLS2CSVmra.java
Two other good event based examples to view are:
https://svn.apache.org/repos/asf/poi/trunk/src/java/org/apache/poi/hssf/extractor/EventBasedExcelExtractor.java
https://svn.apache.org/repos/asf/tika/trunk/tika-parsers/src/main/java/org/apache/tika/parser/microsoft/ExcelExtractor.java
> I was expecting to be able to get the result of a formula (One example I
> have a CONCATENATE() statement, but I end up getting a value like "0.00"
> or in another column I expect an error message like: #DIV/0! but instead
> get "0"Thoughts on how I can get this to match up with the XSSF?
Can you try reading that cell via the usermodel, to check if a cached
value was actually saved to the file?
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org