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 2018/02/07 12:42:52 UTC
[Bug 62084] New: Issue with date format
https://bz.apache.org/bugzilla/show_bug.cgi?id=62084
Bug ID: 62084
Summary: Issue with date format
Product: POI
Version: 3.17-FINAL
Hardware: PC
Status: NEW
Severity: critical
Priority: P2
Component: POI Overall
Assignee: dev@poi.apache.org
Reporter: Kushal.j1987@gmail.com
Target Milestone: ---
Created attachment 35715
--> https://bz.apache.org/bugzilla/attachment.cgi?id=35715&action=edit
Screenshot of excel and debug and exception
Hi Team,
We have observed an issue while converting and excel file to csv. This issue is
only for a specific cell value in a file. I am attaching the screen shot of
Excel file format from Microsoft Excel. Also attaching the screen shot of Debug
Mode of that particular cell.
Even just applying the function to check if its date format is failing. Below
is the code applied :
org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)
Attached PDF contains the images of the Excel Format from Microsoft Excel,
Debug reports of a cell, exception occurred when above code is executed on that
cell value.
Regards,
Kushal J
--
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 62084] Issue with date format
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=62084
Nick Burch <ap...@gagravarr.org> changed:
What |Removed |Added
----------------------------------------------------------------------------
Status|NEW |NEEDINFO
OS| |All
--- Comment #1 from Nick Burch <ap...@gagravarr.org> ---
What is the format string applied to that cell?
--
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 62084] Issue with date format
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=62084
Dominik Stadler <do...@gmx.at> changed:
What |Removed |Added
----------------------------------------------------------------------------
Resolution|--- |LATER
Status|NEEDINFO |RESOLVED
--- Comment #4 from Dominik Stadler <do...@gmx.at> ---
We cannot reproduce the problem with only screenshots, no sample file was
provided, so we cannot do much here unless we get more information. Thus
closing this as LATER for now, please reopen with more information if this is
still a problem 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 62084] Issue with date format
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=62084
Andreas Beeker <ki...@apache.org> changed:
What |Removed |Added
----------------------------------------------------------------------------
Component|POI Overall |SS Common
Hardware|PC |All
--
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 62084] Issue with date format
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=62084
--- Comment #2 from Kushal <Ku...@gmail.com> ---
Created attachment 35716
--> https://bz.apache.org/bugzilla/attachment.cgi?id=35716&action=edit
Cell format string
As requested attaching the image of cell format
--
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 62084] Issue with date format
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=62084
Kushal <Ku...@gmail.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
Status|NEEDINFO |NEW
--
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 62084] Issue with date format
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=62084
Sven <po...@table2web.de> changed:
What |Removed |Added
----------------------------------------------------------------------------
Resolution|LATER |---
Status|RESOLVED |REOPENED
--- Comment #5 from Sven <po...@table2web.de> ---
Hello,
I've noticed the same behavior with POI 4.0.0-final.
In my attached xlsx-file there is only a single cell with the formula
„=date(2018;1;1)“. This cell must be formatted with a format that contains at
least one dot (e.g. „T. MMM. JJJJ“).
Whether this xlsx-file is created with Excel 365 or LibreOffice 6.1.2.1 does
not change anything. It's the same behavior in both situations.
In org.apache.poi.xssf.model.StylesTable.readFrom(InputStream) there is a call
„styleSheet.getNumFmts()“. This returns a ctfmts with
ctfmts._textsource._user.toString() as follows:
<xml-fragment count="16"
xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac"
xmlns:x16r2="http://schemas.microsoft.com/office/spreadsheetml/2015/02/main"
xmlns:xr="http://schemas.microsoft.com/office/spreadsheetml/2014/revision"
xmlns:main="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<main:numFmt numFmtId="165" formatCode="yyyy\-mm\-dd;@"/>
<main:numFmt numFmtId="166" formatCode="d/m;@"/>
<main:numFmt numFmtId="167" formatCode="d/m/yy;@"/>
<main:numFmt numFmtId="168" formatCode="dd/mm/yy;@"/>
<main:numFmt numFmtId="169" formatCode="[$-407]d/\ mmm/;@"/>
<main:numFmt numFmtId="170" formatCode="[$-407]d/\ mmm/\ yy;@"/>
<main:numFmt numFmtId="171" formatCode="[$-407]d/\ mmm\ yy;@"/>
<main:numFmt numFmtId="172" formatCode="[$-407]mmm/\ yy;@"/>
<main:numFmt numFmtId="173" formatCode="[$-407]mmmm\ yy;@"/>
<main:numFmt numFmtId="174" formatCode="[$-407]d/\ mmmm\ yyyy;@"/>
<main:numFmt numFmtId="175" formatCode="[$-409]d/m/yy\ h:mm\ AM/PM;@"/>
<main:numFmt numFmtId="176" formatCode="d/m/yy\ h:mm;@"/>
<main:numFmt numFmtId="177" formatCode="[$-407]mmmmm;@"/>
<main:numFmt numFmtId="178" formatCode="[$-407]mmmmm\ yy;@"/>
<main:numFmt numFmtId="179" formatCode="d/m/yyyy;@"/>
<main:numFmt numFmtId="180" formatCode="[$-407]d/\ mmm/\ yyyy;@"/>
</xml-fragment>
It seems to me as if the format strings contain „/\“ where dots should be.
Code used to read the xlsx-file:
import java.io.File;
import java.util.Locale;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
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;
public class ReadXlsxTest {
public static void main(String[] args) throws Exception {
// read workbook
File xlsxFile = new File("date.xlsx");
Workbook workbook = WorkbookFactory.create(xlsxFile);
FormulaEvaluator formulaEvaluator =
workbook.getCreationHelper().createFormulaEvaluator();
formulaEvaluator.clearAllCachedResultValues();
Sheet sheet = workbook.getSheetAt(0);
// print input cell
for (Row row : sheet) {
for (Cell cell : row) {
System.out.println(cell.getAddress());
String formattedValue = new
DataFormatter(Locale.ENGLISH).formatCellValue(cell,
formulaEvaluator);
System.out.println(formattedValue);
}
}
}
}
Result in Excel and in LibreOffice: „1. Jan. 2018“.
Result in POI 4.0.0-final: „1/ Jan/ 2018“.
This behavior is the same with other locales, e.g. German.
So I'm reopening this issue as Dominik suggested.
--
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 62084] Issue with date format
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=62084
--- Comment #6 from Sven <po...@table2web.de> ---
Created attachment 36207
--> https://bz.apache.org/bugzilla/attachment.cgi?id=36207&action=edit
Contains a formatted date cell to reproduce the behavior
--
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 62084] Issue with date format
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=62084
Dominik Stadler <do...@gmx.at> changed:
What |Removed |Added
----------------------------------------------------------------------------
Status|NEW |NEEDINFO
--- Comment #3 from Dominik Stadler <do...@gmx.at> ---
Please provide a sample file which allows to reproduce the problem.
--
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