Appending a sheet to an Excel file using Apache POI - POIXMLException caused by NullPointerException

Question

Here is my code:

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.xssf.usermodel.*;

public class testingsyncclass {
    public static void main(String[] args) throws IOException, InvalidFormatException{
        writeParametersTableToFile("C:/ENTERPRISE_TESTING", "XLSX_TEST", "SHEETNAME");
    }

    public static void writeParametersTableToFile(String fileLocation, String fileName, String tableLocator) throws InvalidFormatException{
        String[] columnHeaders = {"What", "is", "a", "computer", "?"};
        ArrayList<Object[]> xlsxFileData = new ArrayList<Object[]>();
        for(int i = 0; i < 10; i++){
            Object[] o = {"CoffeeCup", "AnglerJS", "Emerald", "C♭", "C--"};
            xlsxFileData.add(o);
        }
        try{
            if(fileName.contains(".xlsx")){
                fileName = fileName.replaceAll(".xlsx", "");
            }
            File file = new File(fileLocation);
            if(file.mkdirs() || file.exists()){
                File excelFile = new File(fileLocation  + "/" + fileName + ".xlsx");
                XSSFWorkbook workbook;
                XSSFSheet sheet;
                if(excelFile.exists()){
                    workbook = new XSSFWorkbook(excelFile);
                } else {
                    workbook = new XSSFWorkbook();
                }
                if(workbook.getSheet(tableLocator)==null){
                    sheet = workbook.createSheet(tableLocator);
                } else {
                    sheet = workbook.createSheet("NEW_TEST_SHEET");
                }
                XSSFRow firstRow = sheet.createRow(0);

                //write headers
                for(int i = 0; i < columnHeaders.length; i++){
                    XSSFCell cell = firstRow.createCell(i);
                    cell.setCellValue(columnHeaders[i]);
                }
                //write data
                for(int i = 0; i < xlsxFileData.size(); i++){
                    XSSFRow row = sheet.createRow(i+1);
                    for(int j = 0; j < xlsxFileData.get(i).length; j++){
                        XSSFCell cell = row.createCell(j);
                        String value = (String) xlsxFileData.get(i)[j];
                        cell.setCellValue(value);
                    }
                }
                FileOutputStream fileOut = new FileOutputStream(fileLocation  + "/" + fileName + ".xlsx");

                //write this workbook to an Outputstream.
                System.out.println(fileOut + " (fileout)");
                System.out.println(workbook + " (workbook)");
                workbook.write(fileOut);
                fileOut.flush();
                fileOut.close();
                workbook.close();

            } else {
                System.err.println(fileLocation + " was an invalid directory.");
            }
        } catch (IOException e){
            System.err.println("Something went wrong!");
            e.printStackTrace();
        }
    }
}

Creating the file works fine- the file is created as expected. However, if the file exists I want this method to create a sheet and add it to the workbook. With the code I currently have, I get the following exception:

Exception in thread "main" org.apache.poi.POIXMLException: java.lang.NullPointerException
at org.apache.poi.POIXMLDocument.getProperties(POIXMLDocument.java:168)
at org.apache.poi.POIXMLDocument.write(POIXMLDocument.java:246)
at testframework.testingsyncclass.writeParametersTableToFile(testingsyncclass.java:67)
at testframework.testingsyncclass.main(testingsyncclass.java:17)
Caused by: java.lang.NullPointerException

The line throwing this exception is workbook.write(fileOut) yet both workbook and fileOut are not null. The output file also becomes corrupted on the second run.

What am I doing wrong?

Possible duplicate of [What is a NullPointerException, and how do I fix it?](https://stackoverflow.com/questions/218384/what-is-a-nullpointerexception-and-how-do-i-fix-it) — talex, Jul 12 '17 at 15:42
I did. You getting NullPointerException. Did you tried to fix it? — talex, Jul 12 '17 at 15:44
Yes; which is why I created a question on StackOverflow with a runnable example in an attempt to get some guidance. I don't get an exception when I create the file- only when I try to append a sheet to it on the second run. When stepping through with the debugger, none of my variables are null. — DCON, Jul 12 '17 at 15:51
See: https://stackoverflow.com/questions/34954363/modifying-existing-xlsx-spreadsheet-using-apache-poi-leads-to-unreadable-content/34961183#34961183. If you need reading and writing a `XSSFWorkbook` to the same file, then create the `XSSFWorkbook` from an `FileInputStream` rather than from the `File`. In your case: `workbook = new XSSFWorkbook(new FileInputStream(excelFile));` — Axel Richter, Jul 12 '17 at 16:03
That worked; I was literally just about to answer my own question. Do you know why it has to be a FileInputStream when the API seems to suggest you can pass in a file/file location as a parameter? — DCON, Jul 12 '17 at 16:04
Of course, once I read your answer on the other page, it explains it perfectly. Thanks @AxelRichter ! — DCON, Jul 12 '17 at 16:07

score 6 · Accepted Answer · answered Jul 12 '17 at 16:11

This error was caused because I didn't pass a FileInputStream as a parameter when creating the workbook. Although the API suggests that you can pass a File/Path as a parameter, the file is then essentially opened in ReadOnly mode- so when writing to it again an exception is thrown.

This is fixed by passing in a FileInputStream instead. My code then becomes:

FileInputStream fis = new FileInputStream(excelFile);
workbook = new XSSFWorkbook(fis);

score 0 · Answer 2 · answered Mar 26 '18 at 09:24

0

Workbook wb = WorkbookFactory.create(new FileInputStream("data/df.xlsx"));

answered Mar 26 '18 at 09:24

sureshshaw

41
4

Appending a sheet to an Excel file using Apache POI - POIXMLException caused by NullPointerException

2 Answers2