You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@poi.apache.org by bu...@apache.org on 2019/10/13 14:28:35 UTC
[Bug 63841] New: cell gives a wrong result
https://bz.apache.org/bugzilla/show_bug.cgi?id=63841
Bug ID: 63841
Summary: cell gives a wrong result
Product: POI
Version: 4.0.1-FINAL
Hardware: PC
Status: NEW
Severity: normal
Priority: P2
Component: POI Overall
Assignee: dev@poi.apache.org
Reporter: pinksterkoe@devnullmail.com
Target Milestone: ---
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
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 63841] Cell formatting used wrong decimal separator when a Java
locale is set
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=63841
Dominik Stadler <do...@gmx.at> changed:
What |Removed |Added
----------------------------------------------------------------------------
Status|REOPENED |NEW
Summary|cell gives a wrong result |Cell formatting used wrong
| |decimal separator when a
| |Java locale is set
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 63841] cell gives a wrong result
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=63841
--- Comment #3 from Dominik Stadler <do...@gmx.at> ---
Can you provide a small self-sufficient code-sample which reproduces this for
you?
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 63841] Cell formatting used wrong decimal separator when a Java
locale is set
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=63841
Dominik Stadler <do...@gmx.at> changed:
What |Removed |Added
----------------------------------------------------------------------------
Component|POI Overall |XSSF
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 63841] cell gives a wrong result
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=63841
Dominik Stadler <do...@gmx.at> changed:
What |Removed |Added
----------------------------------------------------------------------------
OS| |All
Status|NEW |RESOLVED
Resolution|--- |INVALID
--- Comment #1 from Dominik Stadler <do...@gmx.at> ---
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.
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 63841] cell gives a wrong result
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=63841
--- Comment #4 from Joan <ba...@gmail.com> ---
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;"α #.##")
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 63841] cell gives a wrong result
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=63841
Joan <ba...@gmail.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
Resolution|INVALID |---
Status|RESOLVED |REOPENED
--- Comment #2 from Joan <ba...@gmail.com> ---
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
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org