I use the "XSSF and SAX Event API". Because of that i do not have a "Cell" object. I can hapilly use the DataFormatter.formatRawCellContents(double value, int formatIndex, String formatString) method, because i have all parameters needed to call it. But i can not use the DataFormatter.createFormat(Cell cell) method because it expects a Cell-object as parameter. Could you please provide an additional createFormat-Method that also accepts "double value, int formatIndex, String formatString" as parameters instead? This would add helpful functionality for all of us using the "XSSF and SAX Event API".
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..