[Bug 61495] New: FormulaEvaluator does evaluate Number wrong. [=TEXT(nr;FORMAT)]

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

[Bug 61495] New: FormulaEvaluator does evaluate Number wrong. [=TEXT(nr;FORMAT)]

Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=61495

            Bug ID: 61495
           Summary: FormulaEvaluator does evaluate Number wrong.
                    [=TEXT(nr;FORMAT)]
           Product: POI
           Version: 3.16-FINAL
          Hardware: PC
            Status: NEW
          Severity: regression
          Priority: P2
         Component: SS Common
          Assignee: [hidden email]
          Reporter: [hidden email]
  Target Milestone: ---

Created attachment 35300
  --> https://bz.apache.org/bugzilla/attachment.cgi?id=35300&action=edit
Text Excel File

I am reading an excel file with lots of huge formulas.
The Problem occurs, with the folowing Formula:

Orginal: =WENN(TEST!A2="";"";VERKETTEN("D";" ";TEXT(TEST!A2;"00,00")))
Translated: =IF(TEST!A2="";"";CONCAT("D";" ";TEXT(TEST!A2;"00,00")))

The issue is that the excel file in my Office Excel does show up in a correct
manner since it's localization is GERMAN. However reading it in with, Apache
POI and using the FormulaEvaluator does mess up the results. However using the
format "0.00" does indeed result in a correct manner, so I do assume it's an
localization issue. I also used a org.apache.poi.ss.usermodel.DataFormatter
with the hope to tell it my Locale. However both methods do fail. I did
recreate a minimal test.xlsm and i tested this issue with the following apache
poi releases: LATEST 3.17 Beta - "poi-bin-3.17-beta1-20170701", LATEST 3.16 -
"poi-bin-3.16-20170419", 3.15 Beta2 - "poi-3.15-beta2".

I did create also a Test Class:

####TEST CLASS START####


import java.io.FileInputStream;
import java.util.Locale;
import java.util.stream.IntStream;

import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

public class POITester_3_17 {

        private static final String WORKBOOK_PATH =
"C:\\Users\\l.dag\\Desktop\\test.xlsm";
        private static final String SHEET_NAME = "TEST";

        public static void main(String[] args) throws Exception {
                //Read in Workbook as usual
                final FileInputStream fis;
                final Workbook wb = WorkbookFactory.create(fis = new
FileInputStream(POITester_3_17.WORKBOOK_PATH));
                fis.close();
                //Read end ...

                //Load Sheet + FormulaEvaluator
                final Sheet sheet = wb.getSheet(POITester_3_17.SHEET_NAME);


                final FormulaEvaluator fe =
wb.getCreationHelper().createFormulaEvaluator();
                final DataFormatter df = new
DataFormatter(Locale.getDefault());
                //Load end

                //Evaluate and Print out the cells to test [Hardcoded location,
for test case]
                IntStream.range(0,2).mapToObj(sheet::getRow).map(r ->
r.getCell(1)).forEach(c -> {                      
                        System.out.println(fe.evaluate(c));
                        System.out.println(df.formatCellValue(c, fe));
                });
        }

}

####TEST CLASS END####

Resulting Output using test.xlsm:

####OUTPUT START####
org.apache.poi.ss.usermodel.CellValue ["D 67,10"]
D 67,10
org.apache.poi.ss.usermodel.CellValue ["D 00.68"]
D 00.68
####OUTPUT END####

####WHAT MY EXCEL SHOWS START####
67,10   D 0.067
68,20   D 68,20
####WHAT MY EXCEL SHOWS END####

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

Reply | Threaded
Open this post in threaded view
|

[Bug 61495] FormulaEvaluator does evaluate Number wrong. [=TEXT(nr;FORMAT)]

Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=61495

L.Dag <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 CC|                            |[hidden email]
                 OS|                            |All

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

Reply | Threaded
Open this post in threaded view
|

[Bug 61495] FormulaEvaluator does evaluate Number wrong. [=TEXT(nr;FORMAT)]

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=61495

L.Dag <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 CC|                            |[hidden email]

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

Reply | Threaded
Open this post in threaded view
|

[Bug 61495] FormulaEvaluator does evaluate Number wrong. [=TEXT(nr;FORMAT)]

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=61495

Dominik Stadler <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
           Severity|regression                  |normal

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

Reply | Threaded
Open this post in threaded view
|

[Bug 61495] FormulaEvaluator does evaluate Number wrong. [=TEXT(nr;FORMAT)]

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=61495

--- Comment #1 from Axel Howind <[hidden email]> ---
From my experience, localized Numberformats in Excel, such as "0,00" will be
stored as "0.00" internally. All number formats seem to be stored according to
US locale conventions with a dot ('.') separating the fraftional part. The
comma (',') is used as a grouping character. In german locale, it is reversed.

Excel automatically translates dots and commas when used as a number format,
but I doubt it also does so when using a formula. I think that should be
checked first before deciding how POI should handle this.

A fun fact is that even the Excel help at least for german Excel 2010 is
completely useless here because it seems to have been translated without trying
out the examples given (it says that '=TEXT(A1,"$0.00") & " per hour"' will
result in '23,50 € pro Stunde', magically translating not only the number, but
also the text in quotes).

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