6

Is there any way I can get to the formatted value that excel shows in a row, versus the raw value I am getting returned from the stream?

Or would this fall under the "formula evaluation" category, which this does not support?

Adam Davis
  • 71
  • 1
  • 2
  • Are you trying to get the result of a formula cell? – akokskis Aug 14 '13 at 17:01
  • 2
    No, I'm trying to get the formatted value of a cell. As in, the raw value is 57.9826734, but the cell is formatted to round to two decimals, so when I actually open the spreadsheet I see 57.99. I actually need POI to spit out 57.99 – Adam Davis Aug 14 '13 at 18:04

1 Answers1

16

If you have the Cell that you're trying to get the data out of, try the following

DataFormatter formatter = new DataFormatter();
String formattedCellValue = formatter.formatCellValue(myCell);

If that doesn't get exactly what you're looking for, there are a number of different methods in the DataFormatter class that do the trick. Check out the API.

akokskis
  • 1,486
  • 15
  • 32
  • Unfortunately with XSSF you lose all of the handy easy DOM classes like cell/row/etc. Check this link for a code example: http://stackoverflow.com/questions/9372630/reading-an-excel-sheet-using-pois-xssf-and-sax-event-api -- and it looks like this code snippet might actually have what I need. Hallelujah! Thanks akokskis :) – Adam Davis Aug 14 '13 at 20:06
  • 1
    ?? Your comment makes no sense to me... Using XSSF, you have access to all of those classes in org.apache.poi.ss.usermodel. If you have an `XSSFWorkbook` (which extends `Workbook`), you can get an `XSSFSheet` (which extends `Sheet`), from which you can get an `XSSFRow` (which extends `Row`) from which you can get an `XSSFCell` (which extends `Cell`). Once you have that `XSSFCell`, pass that along to the formatter and you'll get the value you're looking for. No need to get into the SAX parsing. – akokskis Aug 14 '13 at 20:25
  • 1
    if we have a formula, this gives the formula value. Is there a way to get the cached formula value, formatted? – gaurav5430 Jul 04 '16 at 10:03
  • 2
    `formatCellValue` accepts a second argument. You can get a FormulaEvaluator like this: `workbook.getCreationHelper().createFormulaEvaluator()` – neXus Dec 08 '17 at 09:41