Bug 62088 - DataFormatter should provide additional createFormat method for callers that do not work with Cell (SAX)
Summary: DataFormatter should provide additional createFormat method for callers that ...
Status: NEW
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: unspecified
Hardware: PC Linux
: P2 enhancement (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2018-02-09 10:59 UTC by bhuber
Modified: 2018-02-09 13:06 UTC (History)
0 users



Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description bhuber 2018-02-09 10:59:53 UTC
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".
Comment 1 Nick Burch 2018-02-09 11:11:02 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?
Comment 2 bhuber 2018-02-09 12:23:50 UTC
@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;
Comment 3 bhuber 2018-02-09 13:06:41 UTC
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..