poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Skye Shaw <ss...@lucas.cis.temple.edu>
Subject HSSFCell With Formula Errors
Date Mon, 06 Oct 2008 22:59:27 GMT

Hi,

 	This problem arises from POI-3.2-alpha1. I'm using the alpha since 
I had previously ran into this bug: 
https://issues.apache.org/bugzilla/show_bug.cgi?id=45798

 	I want to extract the values of my spreadsheet's cells as strings.
This worked fine:

 	if(cell.getCellType()==HSSFCell.CELL_TYPE_NUMERIC) {
 	    short i = cell.getCellStyle().getDataFormat();
 	    String format = cellFormat.getFormat(i);
 	    if(HSSFDateUtil.isADateFormat((int)i,format)) {
 		//Some date stuff
  	    }
 	}
 	else if(cell.getCellType()==HSSFCell.CELL_TYPE_FORMULA) {
 	    val = cell.getRichStringCellValue().toString()
 	}
 	else {
 	    //etc....
 	}


 	Until... testing. I threw a bunch of spreadshets at it and
received:

 	Exception in thread "main" java.lang.IllegalStateException:
 	Cannot get a error value from a text formula cell
         at org.apache.poi.hssf.usermodel.HSSFCell.typeMismatch(HSSFCell.java:625)
         at org.apache.poi.hssf.usermodel.HSSFCell.checkFormulaCachedValueType(HSSFCell.java:630)
         at org.apache.poi.hssf.usermodel.HSSFCell.getRichStringCellValue(HSSFCell.java:709)

 	Ok, so I change the CELL_TYPE_FORMULA branch to:

 	else if(cell.getCellType()==HSSFCell.CELL_TYPE_FORMULA
 		&& cell.getCellType()!=HSSFCell.CELL_TYPE_ERROR)

 	This results in the same exception.

 	Looking at the source I see that the cell type is assigned 
once at initialization (i.e. instances of FormulaRecordAggregate are 
deemed CELL_TYPE_FORMULA), though when one attempts to get that cell's 
value as a Rich String, the cell's FormulaRecord.getCachedResultType is 
called, revealing that the cell is of CELL_TYPE_ERROR.

 	This seems wrong, though further examination of the code shows 
that an effort is made to keep a clear distinction between formula and 
error (non-formula error) cells. Plus, I see that there's a 
getCachedFormulaResultType() method.

 	I then try:

 	else if(cell.getCellType()==HSSFCell.CELL_TYPE_FORMULA &&
 		cell.getCachedFormulaResultType()!=HSSFCell.CELL_TYPE_ERROR

 	But, once I hit a formula that returns a numeric value, I receive:

 	Exception in thread "main" java.lang.IllegalStateException: Cannot get a text
value from a numeric formula cell at org.apache.poi.hssf.usermodel.HSSFCell.typeMismatch(HSSFCell.java:625)
at org.apache.poi.hssf.usermodel.HSSFCell.checkFormulaCachedValueType(HSSFCell.java:630)
at org.apache.poi.hssf.usermodel.HSSFCell.getRichStringCellValue(HSSFCell.java:709)

 	How is one supposed to get a string value from a formula cell, 
numeric or otherwise?

-Skye


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


Mime
View raw message