poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Davis, Zach" <Zach_Da...@polk.com>
Subject RE: Formulas
Date Wed, 14 Jan 2009 21:59:10 GMT
Using 3.2Final I've implemented HSSFFormulaEvaluator and for some
formulas I'm receiving this traceback.

Exception in thread "main" java.lang.RuntimeException: Error Value can
only be 0,7,15,23,29,36 or 42. It cannot be -30
	at
org.apache.poi.hssf.record.BoolErrRecord.setValue(BoolErrRecord.java:101
)
	at
org.apache.poi.hssf.usermodel.HSSFCell.setCellErrorValue(HSSFCell.java:7
50)
	at
org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.setCellValue(HSSFForm
ulaEvaluator.java:222)
	at
org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateInCell(HSSFFo
rmulaEvaluator.java:194)
	at
com.prtd.update.workbookloader.controller.WorkbookReader.getCellValueAsS
tring(WorkbookReader.java:172)
	at
com.prtd.update.workbookloader.controller.WorkbookReader.parseData(Workb
ookReader.java:291)
	at
com.prtd.update.workbookloader.controller.HistoryLoader.processWorkbook(
HistoryLoader.java:244)
	at
com.prtd.update.workbookloader.controller.HistoryLoader.processFiles(His
toryLoader.java:185)
	at
com.prtd.update.workbookloader.controller.HistoryLoader.main(HistoryLoad
er.java:165)


getCellValueAsString(HSSFCell) {
	//big switch for cell type.
	case HSSFCell.CELL_TYPE_FORMULA:
	    HSSFFormulaEvaluator evaluator = new
HSSFFormulaEvaluator(wb);
	    //call the function again with the cell converted.
	    return getCellValueAsString(evaluator.evaluateInCell(cell));
}

other formulas work so I assume that means it cant parse the formula?
Why cant this be something other than a runtime exception?
FormulaParseException for instance? Any way to detect this before
attempting to parse the formulas? like boolean canParseFormulaCell().

The code was very helpful though. Thanks for the heads up.


Cheers,
Zach Davis
Associate IT Programmer Analyst
R.L. Polk Southfield Office


-----Original Message-----
From: Nick Burch [mailto:nick@torchbox.com] 
Sent: Monday, January 12, 2009 4:33 PM
To: POI Users List
Subject: Re: Formulas

On Mon, 12 Jan 2009, Davis, Zach wrote:
> Is there any basic class to feed a HSSFCell containing a formula that 
> would return the displayed value of the formula like Excel would?

I'd suggest a read of http://poi.apache.org/spreadsheet/eval.html , as I

think it covers all of what you're after

Nick

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
*****************************************************************
This message has originated from R. L. Polk & Co.,
26955 Northwestern Highway, Southfield, MI 48033.
R. L. Polk & Co. sends various types of email
communications.  If this email message concerns the
potential licensing of a Polk product or service, and
you do not wish to receive further emails regarding Polk
products, forward this email to Do_Not_Send@polk.com
with the word "remove" in the subject line.

The email and any files transmitted with it are confidential
and intended solely for the individual or entity to whom they
are addressed.

If you have received this email in error, please delete this
message and notify the Polk System Administrator at
postmaster@polk.com.
*****************************************************************

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


Mime
View raw message