Java-TestNG：读取excel文件，通过TestNG和Apache POI断言并写回excel文件

Question

我正在使用Selenium Java和使用Apache POI的TestNG开发一个POC。我有一个excel表，其中A列包含url，B列包含是否使用相应的url读取/执行的状态（是或否）。我想在C列中保存执行状态pass/fail。

package Read_Write_Excel;
import org.testng.annotations.Test;
import libraries.read_excel_data;
public class read_write_excel 
{

    String path = "C:\\SeleniumUtilities\\excelData\\TestData.xlsx";
    @Test
    public void read_excel() throws Exception
    {
        read_excel_data excel = new read_excel_data(path);
        excel.read_data(0);
    }
}

package libraries;

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

import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class read_excel_data 
{

    XSSFWorkbook workbook;
    XSSFSheet sheet;
    String location;
    int SheetNo;
    write_excel_data write_excel;
    public read_excel_data(String excelPath)
        {
            location = excelPath;
            try {
                File src = new File(excelPath);
                FileInputStream fis = new FileInputStream(src);
                workbook = new XSSFWorkbook(fis);
                write_excel = new write_excel_data(location);

            } catch (Exception e) {
                System.out.println(e.getMessage());
            }
        }


    public void read_data(int sheetNumber) throws Exception
    {
        SheetNo = sheetNumber;
        sheet = workbook.getSheetAt(sheetNumber);
        int rows = sheet.getLastRowNum();
        System.out.println("Total number of Rows to read : "+(rows+1));
        for(int i=0;i<=rows;i++)
        {
            XSSFRow now_row = sheet.getRow(i);
            if(now_row.getCell(1).getStringCellValue().equalsIgnoreCase("yes"))
            {
                String data = now_row.getCell(0).getStringCellValue();
                System.out.println("Data from Row "+i+" is: "+data);
                String status = assertion.asserting(data);
                System.out.println("Status is : "+status);
                write_excel.write_data(SheetNo,i,status);
            }
        }

        workbook.close();
    }

}

package libraries;
public class assertion 
{

    public static String asserting(String url)
    {
        String result;
        if(url.contains("com"))
            {
                result = "Pass";
                System.out.println("Result is : "+result);
                return result;
            }
        else
            {
                result = "Fail";
                System.out.println("Result is : "+result);
                return result;
            }

    }
}

package libraries;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;

import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class write_excel_data 
{

    File des;
    XSSFSheet sheet;
    XSSFWorkbook wb;
    public write_excel_data(String location)
    {
        try {
            des = new File(location);
            FileInputStream fis = new FileInputStream(des);
            wb = new XSSFWorkbook(fis);


        } catch (Exception e) {
            System.out.println(e.getMessage());
        }

    }

    public void write_data(int sheetNo,int i, String status) throws Exception
    {
        sheet = wb.getSheetAt(sheetNo);
        int rows = sheet.getLastRowNum();
        System.out.println("Total number of Rows to write : "+(rows+1));
        sheet.getRow(i).createCell(2).setCellValue(status);
        FileOutputStream fos = new FileOutputStream(des);
        wb.write(fos);
        wb.close();
    }
}

我面临的问题是：

>

有时我会遇到NullPointerException

[TestNG] Running:
  C:\Users\DEDLY\AppData\Local\Temp\testng-eclipse--1619303069\testng-customsuite.xml

Total number of Rows to read : 3
Data from Row 0 is: a.com
Result is : Pass
Status is : Pass
Total number of Rows to write : 3
Data from Row 1 is: b.com
Result is : Pass
Status is : Pass
Total number of Rows to write : 3
[Utils] Attempting to create C:\Users\DEDLY\workspace\SeleniumJavaTestNG\test-output\Default suite\Default test.xml
[Utils]   Directory C:\Users\DEDLY\workspace\SeleniumJavaTestNG\test-output\Default suite exists: true
FAILED: read_excel
org.apache.poi.openxml4j.exceptions.OpenXML4JRuntimeException: Rule M2.4 exception : this error should NEVER happen, if so please send a mail to the developers team, thanks !
    at org.apache.poi.openxml4j.opc.internal.ContentTypeManager.getContentType(ContentTypeManager.java:342)
    at org.apache.poi.openxml4j.opc.internal.ContentTypeManager.removeContentType(ContentTypeManager.java:255)
    at org.apache.poi.openxml4j.opc.OPCPackage.removePart(OPCPackage.java:943)
    at org.apache.poi.openxml4j.opc.PackagePart.getOutputStream(PackagePart.java:522)
    at org.apache.poi.xssf.usermodel.XSSFWorkbook.commit(XSSFWorkbook.java:1546)
    at org.apache.poi.POIXMLDocumentPart.onSave(POIXMLDocumentPart.java:332)
    at org.apache.poi.POIXMLDocument.write(POIXMLDocument.java:195)
    at libraries.write_excel_data.write_data(write_excel_data.java:38)
    at libraries.read_excel_data.read_data(read_excel_data.java:51)
    at Read_Write_Excel.read_write_excel.read_excel(read_write_excel.java:15)
    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:100)
    at org.testng.internal.Invoker.invokeMethod(Invoker.java:646)
    at org.testng.internal.Invoker.invokeTestMethod(Invoker.java:811)
    at org.testng.internal.Invoker.invokeTestMethods(Invoker.java:1137)
    at org.testng.internal.TestMethodWorker.invokeTestMethods(TestMethodWorker.java:129)
    at org.testng.internal.TestMethodWorker.run(TestMethodWorker.java:112)
    at org.testng.TestRunner.privateRun(TestRunner.java:753)
    at org.testng.TestRunner.run(TestRunner.java:607)
    at org.testng.SuiteRunner.runTest(SuiteRunner.java:368)
    at org.testng.SuiteRunner.runSequentially(SuiteRunner.java:363)
    at org.testng.SuiteRunner.privateRun(SuiteRunner.java:321)
    at org.testng.SuiteRunner.run(SuiteRunner.java:270)
    at org.testng.SuiteRunnerWorker.runSuite(SuiteRunnerWorker.java:52)
    at org.testng.SuiteRunnerWorker.run(SuiteRunnerWorker.java:86)
    at org.testng.TestNG.runSuitesSequentially(TestNG.java:1284)
    at org.testng.TestNG.runSuitesLocally(TestNG.java:1209)
    at org.testng.TestNG.runSuites(TestNG.java:1124)
    at org.testng.TestNG.run(TestNG.java:1096)
    at org.testng.remote.AbstractRemoteTestNG.run(AbstractRemoteTestNG.java:132)
    at org.testng.remote.RemoteTestNG.initAndRun(RemoteTestNG.java:236)
    at org.testng.remote.RemoteTestNG.main(RemoteTestNG.java:81)


===============================================
    Default test
    Tests run: 1, Failures: 1, Skips: 0
===============================================


===============================================
Default suite
Total tests run: 1, Failures: 1, Skips: 0
===============================================

更新-2：

Answer 1

只需查看堆栈跟踪，它看起来可能与单元格类型有关（最多的调用是“RemoveContentType”和“getContentType”。我认为这意味着它试图将字符串放入int单元格或不存在的单元格中。

首先，尝试将文本放在C列的三个单元格中的每一个单元格中。如果这样做有效，您可以查看API以查看是否有方法为该单元格设置类型。

如果这不起作用，您可以尝试将最初的电子表格（来自Excel）保存为旧类型。有时Poi在最新的excel版本中不起作用。

null