Summary: | DataFormatter should provide additional createFormat method for callers that do not work with Cell (SAX) | ||
---|---|---|---|
Product: | POI | Reporter: | bhuber <bernd.huber> |
Component: | XSSF | Assignee: | POI Developers List <dev> |
Status: | NEW --- | ||
Severity: | enhancement | ||
Priority: | P2 | ||
Version: | unspecified | ||
Target Milestone: | --- | ||
Hardware: | PC | ||
OS: | Linux |
Description
bhuber
2018-02-09 10:59:53 UTC
We have a private method that I think may be what you want: private Format createFormat(double cellValue, int formatIndex, String sFormat) If you enable access to that method via reflection + setAccessible, does that do what you need? Could you also clarify why you want the Format object rather than the formatted string? @Nick Burch Yes the private method "Format createFormat(double cellValue, int formatIndex, String sFormat)" does exactly do what i need. My use-case is the following: I need to import Excel-files that are huge, and therefore i need to use the "XSSF and SAX Event API" for memory-reasons (save HEAP-memory). Numbers are saved by Excel als Floating-point values like this: 62.474099999999993 We have a convention for our software that all BigDecimals that we save into our database need to have precision of max. 9 digits before the fraction and max. 7 digits after the fraction. As you see, the above number has 15 digits after the fraction which is too long. As i do not want to deal with floating-point problem at all i just want to parse the number as it is shown to the user in the Excel-GUI. To do this with apache-poi i can call the function DataFormatter.formatRawCellContents(..) which gives me the following value for the above example: 62,474 As you see the decimal separator is a comma (,). I need to replace the decimal separator of the excel-format with the decimal-separator of BigDecimal so i can use BigDecimal with this value. But because the decimal-separator of the excel-format is specific to the country i can not just replace it. I need to find out the decimal-separator of the format like follow, and replace it with a dot (.). String xlsxValue = formatter.formatRawCellContents( Double.parseDouble(cellValue.getValue()), cellValue.getFormatIndex(), cellValue.getFormatString()); CellStyle style = new CellStyleHack( cellValue.getFormatIndex(), cellValue.getFormatString()); Cell cell = new CellHack(Double.parseDouble(cellValue.getValue()), style); java.text.Format format = dataFormatter.createFormat(cell); if (format instanceof DecimalFormat) { DecimalFormat decimalFormat = ((DecimalFormat) format); char dSep = decimalFormat.getDecimalFormatSymbols().getDecimalSeparator(); char gSep = decimalFormat.getDecimalFormatSymbols().getGroupingSeparator(); String cSymbol = decimalFormat.getDecimalFormatSymbols().getCurrencySymbol(); String posPre = decimalFormat.getPositivePrefix(); String posSuf = decimalFormat.getPositiveSuffix(); String negPre = decimalFormat.getNegativePrefix(); String negSuf = decimalFormat.getNegativeSuffix(); // java always expects '.' as decimal seperator for BigDecimal and Double. xlsxValue = xlsxValue.replace("" + gSep, ""); xlsxValue = xlsxValue.replace(dSep, '.'); if (cSymbol != null) { // remove € xlsxValue = xlsxValue.replace(cSymbol, ""); } if (posPre != null) { // remove % xlsxValue = xlsxValue.replace(posPre, ""); } if (posSuf != null) { // remove % xlsxValue = xlsxValue.replace(posSuf, ""); } if (negPre != null) { // remove % xlsxValue = xlsxValue.replace(negPre, ""); } if (negSuf != null) { // remove % xlsxValue = xlsxValue.replace(negSuf, ""); } xlsxValue = xlsxValue.trim(); } return xlsxValue; please also see following Ticket of me on stackoverflow: https://stackoverflow.com/questions/48701673/parse-excel-decimal-format-with-apache-poi-to-java-bigdecimal/48706525#48706525 I solved it now with a different approach, so maybe this feature is not needed anymore.. |