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.

Answer 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.

Answer 2

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();

Answer 3

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