I use POI library to create xlsx files using the class XSSFWorkbook.
If i open the created file with double-click using "Windows Explorer",
Excel 2007 runs ok i can see the numbers correctly. But opening the same
file from Excel 2007, using Menu -> Open, every number with 8 or more
digits is truncated to the first digit.
If I open the file using freeOffice or Excel 2016, everything is OK.
If i create a xls file with POI using the class HSSFWorkbook i do not
find this error, but sometimes i need more than 256 columns in a file so
i need create xlsx files.
It is an important problem because i do not know what version of Excel
the users use.
I have create the following program example to check the error creating
two simple files:
package problem.poi;
import java.io.FileOutputStream;
import java.io.IOException;
import java.nio.file.Path;
import java.nio.file.Paths;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
public class Main {
private static final String NAME="result.xls";
public static void main(String[] args) {
new Main().test();
}
private void test() {
Workbook wb = new
org.apache.poi.hssf.usermodel.HSSFWorkbook();
doTest(wb);
wb =new org.apache.poi.xssf.usermodel.XSSFWorkbook();
doTest(wb);
System.out.println("Done.");
}
private void doTest(Workbook wb) {
String n1="1.443279783863E7";
n1="14432797.83863";
Double number=new Double(n1);
Sheet sheet = wb.createSheet("sheet1");
Row row=sheet.createRow(0);
Cell cell=row.createCell(0);
cell.setCellValue(number);
System.out.println(String.format("%,3.2f",
number.doubleValue()));
writeWorkbook(wb);
}
private void writeWorkbook(Workbook wb) {
String fileName;
if("XSSFWorkbook".equals(wb.getClass().getSimpleName())){
fileName = NAME+"x";
}else{
fileName = NAME;
}
Path path=Paths.get(".",fileName);
FileOutputStream fileOut=null;
try {
fileOut = new FileOutputStream(path.toFile());
wb.write(fileOut);
} catch (Exception e) {
e.printStackTrace();
}finally{
if(fileOut!=null){
try {
fileOut.close();
} catch (IOException e) {
}
}
}
}
}
This is my classpath:
commons-codec-1.10.jar
commons-collections4-4.1.jar
commons-logging-1.2.jar
log4j-1.2.17.jar
poi-3.15.jar
poi-ooxml-3.15.jar
poi-ooxml-schemas-3.15.jar
and my system configuration:
Windows 10
Language ES_es
Decimal separator ','
Thousands separator '.'
Results:
- result.xls (HSSFWorkbook) It does not matter the way you use
to open the file. It always runs ok. You can see the cell value =
14432797,84
- result.xlsx (XSSFWorkbook) The file is ok opening it using
freeOffice or excel 2016. You can see the cell value = 14432797,84
- result.xlsx (XSSFWorkbook) PROBLEM: Using Excel 2007 from
Windows Explorer (double-click) it runs ok, but if you open from Excel
2007 using Menu>Open, the cell value 14432797,84 is transformed to a
simple 1.
Do you know how to solve this problem? Any idea?
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscr...@poi.apache.org
For additional commands, e-mail: user-h...@poi.apache.org
