Null pointer exception while using Apache POI

Question

I am trying to write a excel file using Apache POI package. Here is the code snippet:

String basePath = "/home/aman/Desktop";
String fileName = "result.xls";
File file = new File(basePath, fileName);   //File not null. checked.
OPCPackage pkg = OPCPackage.openOrCreate(file);  //pkg not null. checked.
Workbook wb = new XSSFWorkbook(pkg);   //GenerateReport.java:63

I get the following error:

Exception in thread "main" java.lang.NullPointerException
at org.apache.poi.POIXMLDocumentPart.read(POIXMLDocumentPart.java:382)
at org.apache.poi.POIXMLDocument.load(POIXMLDocument.java:155)
at org.apache.poi.xssf.usermodel.XSSFWorkbook.<init>(XSSFWorkbook.java:186)
at amazon.category.database.GenerateReport.generateExcel(GenerateReport.java:63)
at amazon.category.database.MerchantAdoptionStats.processAdoptionStats(MerchantAdoptionStats.java:197)
at amazon.category.database.MerchantAdoptionStats.main(MerchantAdoptionStats.java:386)

Any help appreciated.

@Reimeus `File.exists()` is `false` and I know that the file does not exists. — Aman Deep Gautam, Jan 09 '14 at 15:45
I think you are doing it wrong. Why don't you just create a new Workbook from scratch (not trying to read it from an empty file; what you are doing is used to parse an existing spreadsheet), do whatever you need with it and then `.write()` it to an OutputStream? — Jorge_B, Jan 09 '14 at 15:54
@Jorge_B Potentially the file can already exists. In that case I want to modify it. In this case it happens to be created anew. Any suggestions — Aman Deep Gautam, Jan 09 '14 at 16:04

score 1 · Answer 1 · answered Jan 09 '14 at 16:30

I found this example at the tutorial site: here You could try this approach.

    Workbook wb = new HSSFWorkbook();
    //Workbook wb = new XSSFWorkbook();
    CreationHelper createHelper = wb.getCreationHelper();
    Sheet sheet = wb.createSheet("new sheet");

    // Create a row and put some cells in it. Rows are 0 based.
    Row row = sheet.createRow((short)0);
    // Create a cell and put a value in it.
    Cell cell = row.createCell(0);
    cell.setCellValue(1);

    // Or do it on one line.
    row.createCell(1).setCellValue(1.2);
    row.createCell(2).setCellValue(createHelper.createRichTextString("This is a string"));
    row.createCell(3).setCellValue(true);

    // Write the output to a file
    FileOutputStream fileOut = new FileOutputStream("workbook.xls");
    wb.write(fileOut);
    fileOut.close();

I read the same tutorial. In file stream section is what I am doing. Here: http://poi.apache.org/spreadsheet/quick-guide.html#FileInputStream — Aman Deep Gautam, Jan 10 '14 at 08:16
Also have a look at this answer: http://stackoverflow.com/questions/11154678/xssfworkbook-takes-a-lot-of-time-to-load — Aman Deep Gautam, Jan 10 '14 at 15:02

score 1 · Answer 2 · answered Apr 03 '15 at 20:05

1

I hit my head against this one for a while myself. The trick is whether or not the file exists prior.

//if file is .xls
Workbook workbook;
if(file.exists) {
  NPOIFSFileSystem fs = new NPOIFSFileSystem(file);
  workbook = new HSSFWorkbook(fs.getRoot(), false);
}
else {
  workbook = new HSSFWorkbook();
}

//if file is .xlsx
Workbook workbook;
if(file.exists) {
  OPCPackage pkg = OPCPackage.open(file);
  workbook = new XSSFWorkbook(pkg);
}
else {
  workbook = new XSSFWorkbook();
}

The trick appears to be (and this doesn't look like it is documented well), that you create the workbooks off of the file system or package objects only if the file exists prior. If you want a new file, then don't use the file system or package objects to create your workbooks.

answered Apr 03 '15 at 20:05

Chris Aldrich

1,904
1
22
37

I think the [HSSFWorkbook constructor java docs](https://poi.apache.org/apidocs/org/apache/poi/hssf/usermodel/HSSFWorkbook.html) are fairly clear on `new HSSFWorkbook()` being for create, and `new HSSFWorkbook(NPOIFSFileSystem)` (+friends) being for reading an existing file? Or is there something unclear there? – Gagravarr Apr 04 '15 at 19:24
It was unclear. To be honest, I searched all over Apache POI's site for the Javadoc link and couldn't find it. And it shouldn't just be in the Javadoc. It should be in the "How To" section as well. Hence my almost two days of frustration until I figured this out. – Chris Aldrich Apr 06 '15 at 12:54
Near the top of the quick guide is [How to create a new workbook](http://poi.apache.org/spreadsheet/quick-guide.html#NewWorkbook) which shows your very case, does that not cover you in the docs? – Gagravarr Apr 06 '15 at 23:09

score 0 · Answer 3 · answered Jan 09 '14 at 15:31

0

If everything is what it looks like, you should not be able to build an XSSFWorkbook on an .xls file, since it is a class to model .xlsx files. You should use WorkbookFactory.create() instead of that, which is a factory method that will return the appropiate Workbook implementation for each case

answered Jan 09 '14 at 15:31

Jorge_B

9,712
2
17
22

I tried with `.xlsx`. Same result. Could you please see comments on question to get actual problem – Aman Deep Gautam Jan 09 '14 at 15:47

Null pointer exception while using Apache POI

3 Answers3

Linked