Java使用DataProvider读取Excel时遭遇NullPointerException问题排查

在使用Java读取Excel文件用于Selenium测试时，始终触发NullPointerException，修改多种文件路径后问题仍未解决。

测试代码（TestProviderII.java） #

public class TestProviderII {

public static void main(String[] args) {
    String url = ".\\src\\test\\resources\\dataProvider.xlsx";
    testData(url, "SheetOne");
}

public static void testData(String url, String sheet) {
    ExcelUtils excelUtils = new ExcelUtils(url, sheet);
    int rowCount = excelUtils.getRowCount();
    int colCount = excelUtils.getColCount();
    for (int i = 1; i < rowCount; i++) {
        for (int j = 0; j < colCount; j++) {
            String cellData = excelUtils.getCellDataString(i, j);
            System.out.println("Data : " + cellData);
        }
    }
  }
}

Excel工具类（ExcelUtils.java） #

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

public class ExcelUtils {

static XSSFWorkbook workbook;
static XSSFSheet sheet;

public ExcelUtils(String workBook, String sheetName) {
    try {
        workbook = new XSSFWorkbook(workBook);
        sheet = workbook.getSheet(sheetName);
    } catch (Exception e) {
        e.printStackTrace();
    }
}

public static int getRowCount() {
    int rowCount = 0;
    try {
        rowCount = sheet.getPhysicalNumberOfRows();
        System.out.println("No of Row : " + rowCount);
    } catch (Exception e) {
        System.out.println(e.getMessage());
        System.out.println(e.getCause());
        e.printStackTrace();
    }
    return rowCount;
}

public static int getColCount() {
    int colCount = 0;
    try {
        colCount = sheet.getRow(0).getPhysicalNumberOfCells();
        System.out.println("No of Column" + colCount);
    } catch (Exception e) {
        System.out.println(e.getMessage());
        System.out.println(e.getCause());
        e.printStackTrace();
    }
    return colCount;
}

public static String getCellDataString(int rowNum, int colNum) {
    String cellData = "";
    try {
        cellData = sheet.getRow(rowNum).getCell(colNum).getStringCellValue();
        System.out.println("Data Cell : " + cellData);
    } catch (Exception e) {
        System.out.println(e.getMessage());
        System.out.println(e.getCause());
        e.printStackTrace();
    }
    return cellData;
}

public static Double getCellDataNumber(int rowNum, int colNum) {
    double cellData = 0;
    try {
        cellData = sheet.getRow(rowNum).getCell(colNum).getNumericCellValue();
        System.out.println(cellData);
    } catch (Exception e) {
        System.out.println(e.getMessage());
        System.out.println(e.getCause());
        e.printStackTrace();
    }
    return cellData;
  }
}

报错信息 #

null
null
null
null
java.lang.NullPointerException
at test.ExcelUtils.getRowCount(ExcelUtils.java:23)

at test.TestProviderII.testData(TestProviderII.java:12)
at test.TestProviderII.main(TestProviderII.java:7)
java.lang.NullPointerException
at test.ExcelUtils.getColCount(ExcelUtils.java:36)
at test.TestProviderII.testData(TestProviderII.java:13)
at test.TestProviderII.main(TestProviderII.java:7)

Process finished with exit code 0

尝试过的文件路径 #

String url = ".\\src\\test\\resources\\dataProvider.xlsx";
String url = "src\\test\\resources\\dataProvider.xlsx";
String url = "C:\\Selenuim\\demoApplication\\src\\test\\resources\\dataProvider.xlsx"

核心问题1：静态成员变量的设计错误 #

ExcelUtils类中workbook和sheet被定义为静态变量，所有工具方法也都是静态方法。静态变量属于类而非实例，一旦构造方法中初始化失败（比如文件找不到），静态变量会保持null，后续调用getRowCount()等方法时，访问sheet.getPhysicalNumberOfRows()就会触发空指针。这种设计完全违背了面向对象的实例化逻辑，还会导致多实例调用时互相覆盖数据。

核心问题2：异常处理隐藏了真实错误 #

构造方法仅捕获异常并打印栈轨迹，没有向上抛出或明确提示错误原因，导致文件加载失败时无法快速定位问题：比如路径错误、文件不存在、文件格式不匹配（XSSFWorkbook仅支持.xlsx格式，.xls需用HSSFWorkbook）。

修复方案 #

1. 重构ExcelUtils类，移除静态修饰

将成员变量和工具方法改为实例级，确保每个ExcelUtils实例拥有独立的工作簿和工作表，同时优化异常处理：

import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.IOException;

public class ExcelUtils {

private XSSFWorkbook workbook;
private XSSFSheet sheet;

// 构造方法抛出异常，让调用方处理错误
public ExcelUtils(String workBookPath, String sheetName) throws Exception {
    workbook = new XSSFWorkbook(workBookPath);
    sheet = workbook.getSheet(sheetName);
    if (sheet == null) {
        throw new IllegalArgumentException("工作表 [" + sheetName + "] 不存在");
    }
}

public int getRowCount() {
    return sheet.getPhysicalNumberOfRows();
}

public int getColCount() {
    XSSFRow headerRow = sheet.getRow(0);
    if (headerRow == null) {
        throw new IllegalStateException("Excel文件无表头行");
    }
    return headerRow.getPhysicalNumberOfCells();
}

public String getCellDataString(int rowNum, int colNum) {
    XSSFRow row = sheet.getRow(rowNum);
    if (row == null) return "";
    
    XSSFRow.CellIterator cellIterator = row.cellIterator();
    while (cellIterator.hasNext()) {
        cellIterator.next().setCellType(CellType.STRING);
    }
    
    return row.getCell(colNum) != null ? row.getCell(colNum).getStringCellValue() : "";
}

public Double getCellDataNumber(int rowNum, int colNum) {
    XSSFRow row = sheet.getRow(rowNum);
    if (row == null) return 0.0;
    
    XSSFRow.CellIterator cellIterator = row.cellIterator();
    while (cellIterator.hasNext()) {
        cellIterator.next().setCellType(CellType.NUMERIC);
    }
    
    return row.getCell(colNum) != null ? row.getCell(colNum).getNumericCellValue() : 0.0;
}

// 添加资源关闭方法，避免内存泄漏
public void close() throws IOException {
    if (workbook != null) workbook.close();
}
}

2. 调整调用方代码，优化异常处理与资源管理

使用try-with-resources自动关闭工作簿，同时捕获异常明确错误信息：

public class TestProviderII {

public static void main(String[] args) {
    // 推荐用类加载器获取资源，避免路径问题
    String url = TestProviderII.class.getClassLoader().getResource("dataProvider.xlsx").getPath();
    
    try (ExcelUtils excelUtils = new ExcelUtils(url, "SheetOne")) {
        int rowCount = excelUtils.getRowCount();
        int colCount = excelUtils.getColCount();
        System.out.println("总行数：" + rowCount + "，总列数：" + colCount);
        
        for (int i = 1; i < rowCount; i++) {
            for (int j = 0; j < colCount; j++) {
                String cellData = excelUtils.getCellDataString(i, j);
                System.out.println("Data : " + cellData);
            }
        }
    } catch (Exception e) {
        System.err.println("读取Excel失败：" + e.getMessage());
        e.printStackTrace();
    }
}
}

3. 验证文件路径与格式

• 确认文件存在于指定路径，Maven项目中src/test/resources下的文件会被复制到target/test-classes，用类加载器获取资源可避免相对路径错误
• 若文件是.xls格式，需改用HSSFWorkbook，或用WorkbookFactory自动识别格式：

  workbook = WorkbookFactory.create(new File(workBookPath));
  

内容的提问来源于stack exchange，提问作者BMW83