2

cell A1 has the formula "=A2" and is formatted to show 1 decimal point

cell A2 has the value 4.23

cell A1 shows 4.2 (formatted display value)

cell A2 shows 4.23 (formatted display value)

My XSSF eventmodel sheetContentHandler class implements

public void cell(String cellReference, String formattedValue, XSSFComment comment)

but formattedValue returns 4.2 for cell A1, and i want to retrieve the unformattedValue 4.23

How can i do this ? (I can't change to usermodel, i have huge files)

thedrs
  • 1,412
  • 12
  • 29
  • 1
    Short answer - write your own SAX handler for processing the sheet XML. It's not too hard to do, but it's certainly more work than just using `XSSFSheetXMLHandler` (which handles formatting internally) – Gagravarr Feb 24 '16 at 10:10

2 Answers2

2

Ok There is no way to do this using the sheetContentHandler (though you could get the actual formula if you want there, but that is not what i am looking for)

The only solution will be to use a SheetHandler class which extends DefaultHandler.

Example code on apache-poi site: Example

thedrs
  • 1,412
  • 12
  • 29
1

If the only problem is numerical value formatting, you can pass in a custom DataFormatter to the XSSFSheetXMLHandler and continue to user the SheetContentsHandler interface.

Just override the main formatRawCellContents(...) method to not format the value and return the raw value. e.g.

@Override
public String formatRawCellContents(double value, int formatIndex, String formatString, boolean use1904Windowing) {
    return Double.toString(value);
}

This makes it much easier for dates as well as you don't have to muck around reversing date formats, just use DateUtil.getJavaDate() for the value.

Jack
  • 111
  • 2
  • I've also liked the formatting option by using the toPlainString() method of BigDecimal, as described here: https://stackoverflow.com/questions/46431561/apache-poi-dataformatter-returns-scientific-notation – Jeff S. Jun 11 '21 at 18:24