Bug 63841 - Cell formatting used wrong decimal separator when a Java locale is set
Summary: Cell formatting used wrong decimal separator when a Java locale is set
Status: NEW
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: 4.0.1-FINAL
Hardware: PC All
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2019-10-13 14:28 UTC by Joan
Modified: 2019-11-16 17:00 UTC (History)
0 users



Attachments

Note You need to log in before you can comment on or make changes to this bug.
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;"α #.##")