Using POI throws java.lang.NullPointerException

Question

I am trying to read data from one .xlsx and put it in a different file. The error happens when the loop is running for the second time. There are no blank cells and all the 187 rows have data in them (checked and verified). There is only one column with data in it (the first column A), so i am not using the Null Checker. Here is the code that i have so far. I am not including the code for writing and closing the file as the error occurs during the second iteration of the loop.

FileInputStream fis=new FileInputStream("C:\\test\\Migration_input.xlsx");


    XSSFWorkbook wb = new XSSFWorkbook(fis);
    XSSFSheet sheet = wb.getSheetAt(0);
    int rowCount = sheet.getLastRowNum();
    System.out.println(rowCount);

    FileOutputStream fos=new FileOutputStream("C:\\test\\Migration.xlsx");

    for (int b=0; b<rowCount;b++)
    {

        System.out.println(b);

        //This part to read from existing file

        XSSFRow rowreader=sheet.getRow(b);
        XSSFCell cellreader = rowreader.getCell(0);
        **String cellinput = cellreader.getStringCellValue(); //Errors out on this line on second iteration - It is not a null value and has some string data in it.

        //This part to write in a different file

        XSSFRow rowwriter=sheet.createRow(b+1);
        XSSFCell cellwriter= rowwriter.createCell(2);
        System.out.println(cellinput);
        cellwriter.setCellType(cellwriter.CELL_TYPE_STRING);
        cellwriter.setCellValue(cellinput);
        System.out.println("done");     
    }

I changed the code to this but still seems to be having an error:

for (int b=0; b<rowCount;b++)
        {

        System.out.println(b);

        //This part to read from existing file
        XSSFRow rowreader=sheet.getRow(b);
        XSSFCell cellreader = rowreader.getCell(0);
        System.out.println(cellreader.getStringCellValue());
        **cellinput[b] = cellreader.getStringCellValue();   //Errors out    
        System.out.println("stored in array string all the values");
        }

And here is the updated output

0
Academic Cert Expected Date
FAILED: get_labels
java.lang.NullPointerException
at crm_migration.labels_on_given_object.get_labels(labels_on_given_object.java:99)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.testng.internal.MethodInvocationHelper.invokeMethod(MethodInvocationHelper.java:84)
at org.testng.internal.Invoker.invokeMethod(Invoker.java:714)

It reads the value from the .xlsx fine but when i try to store it into an string array, that is when it throws the error now. I have separated the loop so that it only reads now and stores the value in an array, **again there are no null values in the first column A with 187 rows in it. Please assist.

The error happens cause you read and write in the same file. `XSSFRow rowwriter=sheet.createRow(b+1);` your variable sheet here is the same as the one you read from unless there's missing code. If there's missing code, fix your question. — Jonathan Drapeau, Jul 01 '15 at 18:58
Btw, writing in the same excel file is a [known problem](http://stackoverflow.com/q/8253653/773623), unsure if that was fixed. — Jonathan Drapeau, Jul 01 '15 at 19:01

score 0 · Answer 1 · answered Jul 01 '15 at 20:23

0

The read logic you have implemented is wrong.First create a loop over all the rows and then loop through cell values in each row

//This part to read from existing file

   //Create a loop over all the rows of excel file to read it

    for (int b=0; b<=rowCount;b++)
            {

                Row row = sheet.getRow(b);

                //loop to print cell values in a row

                for (int j = 0; j < row.getLastCellNum(); j++) {

                    String cellinput=row.getCell(j).getStringCellValue();

                    System.out.println(cellinput);
    }
    }

Similarly implement the write logic

I have tested the above code it works fine.Kindly get back if you have any problems

answered Jul 01 '15 at 20:23

Vicky

2,999
2
21
36

Both Rows and Cells can be null, you need to check for that. See the [Apache POI docs](http://poi.apache.org/spreadsheet/quick-guide.html#Iterator) for more – Gagravarr Jul 02 '15 at 08:16
Hi @Gagravarr yes i know but in the question it is mentioned that sheet has no blank cells and all rows have data.So didn't use a null check. But yes it is always good to have a null check – Vicky Jul 02 '15 at 08:34
1

I was missing the following code `cellinput = new String[rowCount];` – T-rex - the 1st break dancer Jul 02 '15 at 19:42

Using POI throws java.lang.NullPointerException

1 Answers1