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
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.
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
Can you provide a small self-sufficient code-sample which reproduces this for you?
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;"α #.##")