Hi, you should look at the HSSFFormulaEvaluator class.
Pierre On Tue, Oct 7, 2008 at 12:59 AM, Skye Shaw <[EMAIL PROTECTED]>wrote: > > 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: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > > -- Cordialement, Pierre Lavignotte Ingénieur Conception & Développement http://pierre.lavignotte.googlepages.com
