https://issues.apache.org/bugzilla/show_bug.cgi?id=47411
Summary: xlsx files with date values don't display properly in
Mac Excel 2008 (due to different defaults, apparently)
Product: POI
Version: 3.5-dev
Platform: PC
OS/Version: Mac OS X 10.4
Status: NEW
Severity: normal
Priority: P2
Component: XSSF
AssignedTo: [email protected]
ReportedBy: [email protected]
Created an attachment (id=23853)
--> (https://issues.apache.org/bugzilla/attachment.cgi?id=23853)
Patch to XSSFWorkbook.java from poi 3.5-beta6
When I create a spreadsheet using XSSFWorkbook with date values in them, and
open the file on Mac Excel 2008, all of the date values are off by exactly 4
years. I have done some investigation, and I believe the issue is due to
differing defaults when the workbook.xml file has a missing <workbookPr> tag
(which seems to happen with all xlsx files created via XSSFWorkbook).
According to the ECMA-376 docs, on page 2292, it talks about "1900" vs "1904
backward compatibility" for dates in xlsx files, and I believe the "lack" of a
<workbookPr> tag showing the different defaults for Windows Excel 2007 and Mac
Excel 2008.
If I simply add the following code after workbook creation, the <workbookPr>
tag is added to the workbook.xml file, and the dates show properly on Mac Excel
2008, and Windows Excel 2007.
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorkbookPr;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
XSSFWorkbook workbook = new XSSFWorkbook();
if (workbook.getCTWorkbook().getWorkbookPr() == null) {
// this code causes a <workbookpr> element to be written to the workbook.xml
// file. If this mode isn't set, then Mac
// Excel 2008 defaults to date1904 mode whereas Windows Excel 2007
// defaults to date1900 mode.
CTWorkbookPr workbookPr = CTWorkbookPr.Factory.newInstance();
workbookPr.setDate1904(false);
workbook.getCTWorkbook().setWorkbookPr(workbookPr);
}
I will attach some screenshots and example code showing the differences between
display of dates on Excel 2007/Excel 2008. Also, I believe the patch to this
is simply adding extra initialization to onWorkbookCreate() in
XSSFWorkbook.java. I will also attach a patch showing this addition.
Thanks much!
--Leif
--
Configure bugmail: https://issues.apache.org/bugzilla/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
