Bug 61495 - FormulaEvaluator does evaluate Number wrong. [=TEXT(nr;FORMAT)]
Summary: FormulaEvaluator does evaluate Number wrong. [=TEXT(nr;FORMAT)]
Status: NEW
Alias: None
Product: POI
Classification: Unclassified
Component: SS Common (show other bugs)
Version: 3.17-FINAL
Hardware: PC All
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2017-09-06 11:33 UTC by L.Dag
Modified: 2018-02-09 14:04 UTC (History)
2 users (show)



Attachments
Text Excel File (19.87 KB, application/vnd.ms-excel.sheet.macroEnabled.12)
2017-09-06 11:33 UTC, L.Dag
Details

Note You need to log in before you can comment on or make changes to this bug.
Description L.Dag 2017-09-06 11:33:53 UTC
Created attachment 35300 [details]
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####
Comment 1 Axel Howind 2017-10-23 12:04:00 UTC
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).
Comment 2 L.Dag 2018-02-09 14:04:31 UTC
Hello again,
I did HOTFIX this problem for my usecase, however i think that this still should be adressed since it is a bug after all.
The Hotfix is made by before reading in anything from the Workbook, i do parse every Formula Cell and replace the "0,00" to a "0.00" so the FormulaEvaluator can operate as intended.

#####ACTUAL_CODE######

/**
 * @author l.dag
 * 
 * searches Each Sheet, Row and Cell for Formulas with "0,00"(=German standard) and replaces with "0.00"(=US standard)
 *
 */
public static void repairFormulas(final Workbook wb) {
  wb.sheetIterator().forEachRemaining(sheet -> IntStream.range(sheet.getFirstRowNum(), sheet.getLastRowNum()).filter(i -> sheet.getRow(i)!=null).mapToObj(sheet::getRow)
  .flatMap(r -> IntStream.range(r.getFirstCellNum(), r.getLastCellNum()).filter(i -> r.getCell(i)!=null).mapToObj(r::getCell))
  .filter(c -> Cell.CELL_TYPE_FORMULA == c.getCellType() && c.getCellFormula().contains("\"0,00\"")).forEach(c -> c.setCellFormula(c.getCellFormula().replace("\"0,00\"", "\"0.00\""))));
}