[Bug 62084] Issue with date format

[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