Unable to write in the Excel file using Java and Apache POI

Question

Here is my code read is working correctly, but when cell value set and updating changes its giving error. Please Help.

I want to read each row and want to add Status at the and of each status

Here is my Excel sheet:

First Name  Last name
vinay       kumar
Vijay       Sharma
Rahul       Jain
Navin       Jain

Here is my Code

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

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.testng.annotations.Test;


public class test extends config
{
    public static String cellDataString = null;
    public static boolean cellDataBoolean = false;
    public static double cellDataInteger = 0;
    public static String cellvalue = null;
    public static int row, col;

    public static void test()
    {

        try 
        {
            String fileNameWithPath = "E:\\FrameworkDocumentation.xlsx";


            // Specify the path of file
            File src=new File(fileNameWithPath);
            //  File src=new File(fileName);

            // load file
            FileInputStream fileInputStream = new FileInputStream(src);

            // Load workbook
            XSSFWorkbook workBook = new XSSFWorkbook(fileInputStream);

            // Load sheet- Here we are loading first sheetonly
            XSSFSheet sheet1= workBook.getSheetAt(0);


            Cell cell = null;


            for( row=0; row<=sheet1.getPhysicalNumberOfRows(); row++)
            {
                    Row currentRow = sheet1.getRow(row);

                    for(col=0; col<currentRow.getLastCellNum(); col++)
                    {
                            Cell currentCell = currentRow.getCell(col, Row.RETURN_NULL_AND_BLANK);
                        /*  if(currentCell==null)
                            {
                                System.out.print("\t null");
                                continue;
                            }
                        */                                          
                            switch(currentCell.getCellType())
                            {
                                case Cell.CELL_TYPE_STRING:
                                    cellvalue = currentCell.getStringCellValue();
                                    break;
                                case Cell.CELL_TYPE_NUMERIC:
                                    cellvalue = Double.toString(currentCell.getNumericCellValue());
                                    break;
                                case Cell.CELL_TYPE_BOOLEAN:
                                    cellvalue = String.valueOf(currentCell.getBooleanCellValue());
                                    break;
                                case Cell.CELL_TYPE_BLANK:
                                    cellvalue = null;
                                    break;
                            } 

                            System.out.print("\t"+cellvalue);

                            if(col==2)
                            {
                                keyword = cellvalue;
                                System.out.print("("+keyword+")");
                            }
                            else if(col==3)
                            {
                                locator_type = cellvalue;
                                System.out.print("("+locator_type+")");
                            }
                            else if(col==4)
                            {
                                locator = cellvalue;
                                System.out.print("("+locator+")");
                            }
                            else if (col==5)
                            {
                                data = cellvalue;
                                System.out.print("("+data+")");
                            }


                    }




                    //Update the value of cell
                    XSSFRow sheetrow = sheet1.getRow(row);
            //      System.out.println(sheetrow);
                    if(sheetrow == null)
                    {
                        sheetrow = sheet1.createRow(row);
                    }
                    //Update the value of cell
                    cell = sheetrow.getCell(col);
                    if(cell == null)
                    {
                        cell = sheetrow.createCell(col);
                    }
                    cell.setCellValue("Pass");

                    System.out.println("\n current cell value : "+ cell.getStringCellValue());



            }   

            fileInputStream.close();

            FileOutputStream outFile =new FileOutputStream(new File(fileNameWithPath));
            workBook.write(outFile);
            outFile.close();
            System.out.println("Write Complete");
        } 

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

Error code

FAILED: test

    java.lang.NullPointerException
        at test.test.test(test.java:55)
        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:85)
        at org.testng.internal.Invoker.invokeMethod(Invoker.java:639)
        at org.testng.internal.Invoker.invokeTestMethod(Invoker.java:816)
        at org.testng.internal.Invoker.invokeTestMethods(Invoker.java:1124)
        at org.testng.internal.TestMethodWorker.invokeTestMethods(TestMethodWorker.java:124)
        at org.testng.internal.TestMethodWorker.run(TestMethodWorker.java:108)
        at org.testng.TestRunner.privateRun(TestRunner.java:773)
        at org.testng.TestRunner.run(TestRunner.java:623)
        at org.testng.SuiteRunner.runTest(SuiteRunner.java:359)
        at org.testng.SuiteRunner.runSequentially(SuiteRunner.java:354)
        at org.testng.SuiteRunner.privateRun(SuiteRunner.java:312)
        at org.testng.SuiteRunner.run(SuiteRunner.java:261)
        at org.testng.SuiteRunnerWorker.runSuite(SuiteRunnerWorker.java:52)
        at org.testng.SuiteRunnerWorker.run(SuiteRunnerWorker.java:86)
        at org.testng.TestNG.runSuitesSequentially(TestNG.java:1185)
        at org.testng.TestNG.runSuitesLocally(TestNG.java:1110)
        at org.testng.TestNG.run(TestNG.java:1018)
        at org.testng.remote.RemoteTestNG.run(RemoteTestNG.java:112)
        at org.testng.remote.RemoteTestNG.initAndRun(RemoteTestNG.java:205)
        at org.testng.remote.RemoteTestNG.main(RemoteTestNG.java:176)

Here is Same code that is running fine

public class updateExcel 
{  

    public static void main(String[] args)
    {

        try {
            FileInputStream file = new FileInputStream("E:\\TechartifactExcel.xlsx");

            XSSFWorkbook workbook = new XSSFWorkbook(file);
            XSSFSheet sheet = workbook.getSheetAt(0);



            Cell cell = null;

            for (int i=0;i<4;i++)
            {
                for(int j=2; j<4;j++)
                {


                    //Update the value of cell
                    XSSFRow sheetrow = sheet.getRow(i);
                    if(sheetrow == null)
                    {
                        sheetrow = sheet.createRow(i);
                    }
                    //Update the value of cell
                    cell = sheetrow.getCell(j);
                    if(cell == null)
                    {
                        cell = sheetrow.createCell(j);
                    }
                    cell.setCellValue("Pass");

                }
            }

            file.close();

            FileOutputStream outFile =new FileOutputStream(new File("E:\\TechartifactExcel.xlsx"));
            workbook.write(outFile);
            outFile.close();

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

Answer 1

Simply replace sheet1.getPhysicalNumberOfRows() with sheet1.getLastRowNum() or change the test of your first for loop for a strict comparison.

for( row=0; row <= sheet1.getLastRowNum(); row++)

or

for( row=0; row < sheet1.getPhysicalNumberOfRows(); row++)