Exception(org.apache.poi.openxml4j.exceptions.InvalidFormatException: Date not well formated, ....) when reading xlsx file using Apache POI?

Question

I'm using the following jar files:

poi-3.14-20160307.jar
poi-ooxml-3.14-20160307.jar
poi-ooxml-schemas-3.14-20160307.jar
xmlbeans-2.6.0.jar

Code:

package firstExcel;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.io.InputStreamReader;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class Test {

     public static void main( String[] args ) throws IOException {

        FileInputStream fis = new FileInputStream ( new File ("excel1.xlsx"));

        XSSFWorkbook wb = new XSSFWorkbook(fis);

        XSSFSheet sheet = wb.getSheetAt(0);

        FormulaEvaluator formulaEvaluator = wb.getCreationHelper().createFormulaEvaluator();

        for (Row row: sheet) {

            for (Cell cell: row){

                switch (formulaEvaluator.evaluateInCell(cell).getCellType()){

                case Cell.CELL_TYPE_NUMERIC:

                    System.out.print(cell.getNumericCellValue() + " t\t");
                    break;

                case Cell.CELL_TYPE_STRING:

                    System.out.print(cell.getStringCellValue() + " t\t" );
                    break;          
                }               
            }
        }
   }
}

Error message:

Exception in thread "main" java.lang.IllegalArgumentException: Date for    created could not be parsed: 2016-04-05T07:13:50+03:00
at     org.apache.poi.openxml4j.opc.internal.PackagePropertiesPart.setCreatedProperty(PackagePropertiesPart.java:393)
at org.apache.poi.openxml4j.opc.internal.unmarshallers.PackagePropertiesUnmarshaller.unmarshall(PackagePropertiesUnmarshaller.java:124)
at org.apache.poi.openxml4j.opc.OPCPackage.getParts(OPCPackage.java:726)
at org.apache.poi.openxml4j.opc.OPCPackage.open(OPCPackage.java:280)
at org.apache.poi.util.PackageHelper.open(PackageHelper.java:37)
at org.apache.poi.xssf.usermodel.XSSFWorkbook.<init>(XSSFWorkbook.java:274)
at firstExcel.Test.main(Test.java:45)
Caused by: org.apache.poi.openxml4j.exceptions.InvalidFormatException: Date 2016-04-05T07:13:50+03:00Z not well formated, expected format yyyy-MM-dd'T'HH:mm:ss'Z' or yyyy-MM-dd'T'HH:mm:ss.SS'Z'
at org.apache.poi.openxml4j.opc.internal.PackagePropertiesPart.setDateValue(PackagePropertiesPart.java:575)
at org.apache.poi.openxml4j.opc.internal.PackagePropertiesPart.setCreatedProperty(PackagePropertiesPart.java:391)
... 6 more

The Excel file is autogenerated by a web provider and can not be adapted. It is working fine in Excel on several different systems. All cells are formated as "general". None is set to date and time for instance, as the error states date not well formated. It should just be read as a string. Theres a lot of hebrew text in the file if that might cause the problem?

Does anyone have an idea to solve that issue? Thanks for your help!

Answer 1

Please open your excel1.xlsx with a ZIP utility. Have a look at /docProps/core.xml in that ZIP archive. You will find something like:

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<cp:coreProperties xmlns:cp="http://schemas.openxmlformats.org/package/2006/metadata/core-properties" xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:dcterms="http://purl.org/dc/terms/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<dcterms:created xsi:type="dcterms:W3CDTF">2016-04-05T07:13:50+03:00</dcterms:created>
...
</cp:coreProperties>

The problem ist the 2016-04-05T07:13:50+03:00. Excel will accept this as Zone GMT+03:00 but apache poi will not accept this. Apache poi will only accept 2016-04-05T07:13:50Z

Maybe it is not <dcterms:created but <dcterms:modified or another date there. The problem is the same.

Since this exception is thrown while creating the workbook, you have not really much possibilities. You could ask the web provider for not using such a date there. Or you could change that date within this XML file using manual methods. Or you could create a bug report to apache poi.

Why this is a bug?

http://dublincore.org/documents/dcmi-terms/ -> http://dublincore.org/documents/dcmi-terms/#terms-created -> http://dublincore.org/documents/2012/06/14/dcmi-terms/?v=elements#date -> http://www.w3.org/TR/NOTE-datetime:

The formats are as follows. Exactly the components shown here must be present, with exactly this punctuation. Note that the "T" appears literally in the string, to indicate the beginning of the time element, as specified in ISO 8601.

   Year:
      YYYY (eg 1997)
   Year and month:
      YYYY-MM (eg 1997-07)
   Complete date:
      YYYY-MM-DD (eg 1997-07-16)
   Complete date plus hours and minutes:
      YYYY-MM-DDThh:mmTZD (eg 1997-07-16T19:20+01:00)
   Complete date plus hours, minutes and seconds:
      YYYY-MM-DDThh:mm:ssTZD (eg 1997-07-16T19:20:30+01:00)
   Complete date plus hours, minutes, seconds and a decimal fraction of a
second
      YYYY-MM-DDThh:mm:ss.sTZD (eg 1997-07-16T19:20:30.45+01:00)

where:

 YYYY = four-digit year
 MM   = two-digit month (01=January, etc.)
 DD   = two-digit day of month (01 through 31)
 hh   = two digits of hour (00 through 23) (am/pm NOT allowed)
 mm   = two digits of minute (00 through 59)
 ss   = two digits of second (00 through 59)
 s    = one or more digits representing a decimal fraction of a second
 TZD  = time zone designator (Z or +hh:mm or -hh:mm)