bhuber <> changed:

           What    |Removed                     |Added
             Status|NEEDINFO                    |NEW

--- Comment #2 from bhuber <> ---
@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:


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:


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(

        CellStyle style = new CellStyleHack(
                cellValue.getFormatIndex(), cellValue.getFormatString());
        Cell cell = new CellHack(Double.parseDouble(cellValue.getValue()),
        java.text.Format format = dataFormatter.createFormat(cell);
        if (format instanceof DecimalFormat) {
            DecimalFormat decimalFormat = ((DecimalFormat) format);
            char dSep =
            char gSep =
            String cSymbol =
            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
            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;

You are receiving this mail because:
You are the assignee for the bug.
To unsubscribe, e-mail:
For additional commands, e-mail:

Reply via email to