NullpointerException when trying to read XLSX file

Question

I currently have this code to open an xlsx file using apache POI

File existingXlsx = new File("/app/app.xlsx");
System.out.println("File Exists: " + existingXlsx.exists());

Workbook workbook = WorkbookFactory.create(existingXlsx);

When I try to execute this, I get the following output

File Exists: true
java.lang.NullPointerException
    at org.apache.poi.xssf.usermodel.XSSFWorkbook.onDocumentRead(XSSFWorkbook.java:270)
    at org.apache.poi.POIXMLDocument.load(POIXMLDocument.java:159)
    at org.apache.poi.xssf.usermodel.XSSFWorkbook.<init>(XSSFWorkbook.java:186)
    at org.apache.poi.ss.usermodel.WorkbookFactory.create(WorkbookFactory.java:91)

The file I am trying to open can be opened in Excel and show the data correctly, what can I do to get POI to read the XLSX file?

Here is the file that breaks;

https://mega.co.nz/#!FJMWjQKI!CzihQgMVpxOQDTXzSnb3UFYSKbx4yFTb03-LI3iLmkE

Edit

I have also tried, this results in the same error;

Workbook workbook = new XSSFWorkbook(new FileInputStream(existingXlsx));

Edit

I found the line it is throwing the exception on;

WorkbookDocument doc = WorkbookDocument.Factory.parse(getPackagePart().getInputStream());
this.workbook = doc.getWorkbook();

Map<String, XSSFSheet> shIdMap = new HashMap<String, XSSFSheet>();
for(POIXMLDocumentPart p : getRelations())
{
    if(p instanceof SharedStringsTable) sharedStringSource = (SharedStringsTable)p;
    else if(p instanceof StylesTable) stylesSource = (StylesTable)p;
    else if(p instanceof ThemesTable) theme = (ThemesTable)p;
    else if(p instanceof CalculationChain) calcChain = (CalculationChain)p;
    else if(p instanceof MapInfo) mapInfo = (MapInfo)p;
    else if (p instanceof XSSFSheet) {
        shIdMap.put(p.getPackageRelationship().getId(), (XSSFSheet)p);
    }
}

stylesSource.setTheme(theme); <== BREAKS HERE

Edit

After some research POI seems to be unable to find the styles.xml and the workbook.xml, I find this strange because a simple reader like TextWrangler which shows the structure of the archive shows me the styles xml.

How do I fix this? Is there a default styles.xml and workbook.xml which I can insert into the archive?

Answer 1

Now I've dowloaded the latest packages:

• poi-src-3.9-20121203.zip (As source)
• xmlbeans-2.6.0.zip
  • jsr173_1.0_api.jar
  • resolver.jar
  • xbean.jar
  • xbean_xpath.jar
  • xmlbeans-qname.jar
  • xmlpublic.jar
• ooxml-schemas-1.1.jar
• dom4j-1.6.1.jar
• commons-codec-1.8.jar
• commons-logging-1.1.3.jar
• ant.jar (ant 1.7)

And your test2.xlsx were read without problems:

  public static void main(String arg []){
    try {
      //File existingXlsx = new File("/app/app.xlsx");
      File existingXlsx = new File("c:/Java/poi-3.9/test-data/__theproblem/test2.xlsx");
      System.out.println("File Exists: " + existingXlsx.exists());

      Workbook workbook = WorkbookFactory.create(existingXlsx);

    } catch (Exception e) {
      e.printStackTrace();
    }
  }

Are you sure you're using ooxml-schemas-1.1.jar as the POI documentation recommends?

EDIT

Hmm. It's work for me from jar too.

1. I have downloaded poi-bin-3.9-20121203.tar.gz from http://poi.apache.org/download.html

2. Made a new project in Eclipse, extracted all the jars from the zip:

   • lib/commons-codec-1.5.jar
   • lib/commons-logging-1.1.jar
   • lib/dom4j-1.6.1.jar
   • lib/junit-3.8.1.jar
   • lib/log4j-1.2.13.jar
   • lib/poi-3.9-20121203.jar
   • lib/poi-examples-3.9-20121203.jar
   • lib/poi-excelant-3.9-20121203.jar
   • lib/poi-ooxml-3.9-20121203.jar
   • lib/poi-ooxml-schemas-3.9-20121203.jar
   • lib/poi-scratchpad-3.9-20121203.jar
   • lib/stax-api-1.0.1.jar
   • lib/xmlbeans-2.3.0.jar
3. Add the test xlsx:
   • test-data/test2.xlsx
4. The test Java:
   • src/XlsxReadTest1.java

Source:

    import java.io.File;
    import org.apache.poi.ss.usermodel.Workbook;
    import org.apache.poi.ss.usermodel.WorkbookFactory;

    public class XlsxReadTest1 {
      public static void main(String arg []){
        try {

          File existingXlsx = new File("c:/Java/__Work/apache_POI/poi-3.9-bin/test-data/test2.xlsx");
          System.out.println("File Exists: " + existingXlsx.exists());

          Workbook workbook = WorkbookFactory.create(existingXlsx);

          System.out.println("A1: " + workbook.getSheetAt(0).getRow(0).getCell(0).getStringCellValue());

        } catch (Exception e) {
          e.printStackTrace();
        }
      }
    }

5. Run. (Tried with jdk1.7.0_07, jdk1.6.0_31)

6. Result:

   File Exists: true
   A1: Testing Edit
   

"Testing Edit" is the content of the first cell on the first sheet of your file.

I think, You may try this, from scratch.

(Maybe you are using other jars for your project, whom interfere with this jars in the class loader? Class loader is a cunning guy...)

Answer 2

I guess you just used the wrong poi package. Try to download the following or you check the newest version from the page.

The following I tested in my Eclipse development:

http://www.apache.org/dyn/closer.cgi/poi/release/bin/poi-bin-3.9-20121203.zip extract it, and include all the jars into your eclipse lib

I combine user1234's answer and my own approach, both are working on your test2.xlsx

            import java.io.File;
            import java.io.FileInputStream;
            import java.io.FileNotFoundException;
            import java.io.IOException;

            import org.apache.poi.openxml4j.exceptions.OpenXML4JException;
            import org.apache.poi.openxml4j.opc.OPCPackage;
            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;
            import org.apache.poi.xssf.extractor.XSSFExcelExtractor;
            import org.apache.poi.xssf.usermodel.XSSFWorkbook;
            import org.apache.xmlbeans.XmlException;


            public class Main {

                /**
                 * @param args
                 */
                public static void main(String[] args) {
                    //      File existingXlsx = new File("app.xlsx");

                    File file = new File("test2.xlsx");
                    FileInputStream fs;
                    try {
                        fs = new FileInputStream(file);

                        OPCPackage xlsx = OPCPackage.open(fs);
                        XSSFExcelExtractor xe = new XSSFExcelExtractor(xlsx);
                        System.out.println(xe.getText());


                    } catch (FileNotFoundException e1) {
                        e1.printStackTrace();
                    } catch (XmlException e) {
                        e.printStackTrace();
                    } catch (OpenXML4JException e) {
                        e.printStackTrace();
                    } catch (IOException e) {
                        e.printStackTrace();
                    }

                    /// -------------- Another approach

                    File existingXlsx = new File("test2.xlsx");
                    System.out.println("File Exists: " + existingXlsx.exists());

                    try {
                        Workbook workbook = new XSSFWorkbook(new FileInputStream(
                            existingXlsx));

                        Sheet worksheet = workbook.getSheet("Filter criteria");
                        Row row1 = worksheet.getRow(0);
                        Cell cellA1 = row1.getCell((short) 0);
                        String a1Val = cellA1.getStringCellValue();


                        System.out.println("A1: " + a1Val);

                    } catch (IOException e) {
                        e.printStackTrace();
                    }

                }

            }

finally I got the result:

Answer 3

If you want to read .xlsx, could you please try this code (uses apache poi 3.9) :

    File file = new File("/app/app.xlsx");
    FileInputStream fs = new FileInputStream(file);
    OPCPackage xlsx = OPCPackage.open(fs);
    XSSFExcelExtractor xe = new XSSFExcelExtractor(xlsx);
    System.out.println(xe.getText());

The above code should display the content of the file app.xlsx.