Bug 63841

Summary: Cell formatting used wrong decimal separator when a Java locale is set
Product: POI Reporter: Joan <baconbunker>
Component: XSSFAssignee: POI Developers List <dev>
Status: NEW ---    
Severity: normal    
Priority: P2    
Version: 4.0.1-FINAL   
Target Milestone: ---   
Hardware: PC   
OS: All   

Description Joan 2019-10-13 14:28:35 UTC
First of all, sorry for my bad English.

With POI i edit a excel file. And the following formula gives a wrong result.
=TEXT(24,09456;"α #,##")
It gives as result "α 24"
When i change the formula to
=TEXT(24,09456;"α #.##")
then it gives the correct result "α 24,09"

But in Europe the decimal sign is ',' and not '.'

Please fix this. Thanks Joan from the Netherlands
Comment 1 Dominik Stadler 2019-10-13 15:34:36 UTC
This is "by design". Microsoft Excel uses the US-format for specifying the formatting to avoid problems with writing in one format and reading in another. Whenever the format is applied, it is adjusted according to the current locale in either Excel itself or Apache POI if this is used for rendering the format.

So in the file-format, you specify "#.##" and if you configure the "locale" of your JavaVM, it should produce the expected European format if put "#.##" in the file as format.

See https://poi.apache.org/apidocs/dev/org/apache/poi/ss/format/CellFormat.html and https://poi.apache.org/components/spreadsheet/quick-guide.html#CellContents for some usage information.
Comment 2 Joan 2019-10-13 16:14:43 UTC
The excel file is also edited by normal users. If i use '=TEXT(24,09456;"α #.##")' then excel will give a wrong cell result.

the locale of my JavaVM is 
-Duser.country=NL -Duser.language=nl
Comment 3 Dominik Stadler 2019-10-13 17:59:14 UTC
Can you provide a small self-sufficient code-sample which reproduces this for you?
Comment 4 Joan 2019-10-13 18:46:35 UTC
of course i can do that
JavaVM is set to nl-NL

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;

import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class Test {
	public Test(String excelFile, String outputFile) {
		try {
			Workbook wb = WorkbookFactory.create(new FileInputStream(excelFile));
			for(Sheet sheet : wb) {
				for(Row row : sheet) {
					row.getCell(0).setCellValue((double)750/365);
				}
			}
			if (wb instanceof XSSFWorkbook) {
				XSSFFormulaEvaluator.evaluateAllFormulaCells(wb);
			}else if (wb instanceof HSSFWorkbook) {
				HSSFFormulaEvaluator.evaluateAllFormulaCells(wb);
			}
			FileOutputStream out = new FileOutputStream(new File(outputFile));
	        wb.write(out);
	        out.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public static void main(String[] args) {
		Test t = new Test("testfile.xlsx","output.xlsx");
	}
}


And "testFile.xlsx" is made as follows
cell a1=  1
cell a2=  1
cell b1=  =TEKST(A1;"α #,##")
cell b2=  =TEKST(A1;"α #.##")