java - 无法使用 Java 和 Apache POI 写入 Excel 文件

Question

这是我的代码读取工作正常，但是当单元格值设置和更新更改时，会出现错误。请帮忙。

我想读取每一行并想在每个状态的末尾添加状态

这是我的 Excel 工作表:

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

这是我的代码

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

错误代码

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)

这是运行良好的相同代码

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

只需将 sheet1.getPhysicalNumberOfRows() 替换为 sheet1.getLastRowNum() 或更改第一个 for 循环的测试即可进行严格比较。

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

或

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