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: dev@poi.apache.org Reporter: l....@outlook.de 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: dev-unsubscr...@poi.apache.org For additional commands, e-mail: dev-h...@poi.apache.org