自动化测试读取Excel数据失败，报NullPointerException求助

问题场景 #

尝试通过读取Excel数据实现自动登录功能，运行时触发空指针异常，核心代码及错误信息如下：

原代码 #

package com.framework;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.time.Duration;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.hssf.usermodel.*;
import org.openqa.selenium.By;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.firefox.FirefoxDriver;

public class DataDriveFramework {
    public void readExcel(String filePath, String fileName, String sheetName) throws IOException {
        File file = new File(filePath+"\\"+fileName);
        FileInputStream fis = new FileInputStream(file);
        Workbook loginWorkbook=null;
        
        String fileExtension=fileName.substring(fileName.indexOf("."));
        if(fileExtension.equals(".xlsx"))
        {
            loginWorkbook=new XSSFWorkbook(fis);
        }
        else if(fileExtension.equals("xls"))
        {
            loginWorkbook=new HSSFWorkbook(fis);
    }
        Sheet loginSheet=loginWorkbook.getSheet(sheetName);
        
        int rowCount=loginSheet.getLastRowNum()-loginSheet.getFirstRowNum();
        for(int i=1;1<rowCount+1;i++)
        {
            Row row=loginSheet.getRow(i);
            String username=row.getCell(0).getStringCellValue();
            String password=row.getCell(0).getStringCellValue();
            test(username,password);
        }
}
    public void test(String username, String password)
    {
        WebDriver driver=new FirefoxDriver();
        driver.manage().timeouts().implicitlyWait(Duration.ofSeconds(10));
        
        String baseURL="https://accounts.google.com";
        driver.get(baseURL);
        
        driver.findElement(By.id("identifierId")).sendKeys(username);
        driver.findElement(By.xpath("/html/body/div[1]/div[1]/div[2]/div/c-wiz/div/div[2]/div/div[2]/div/div[1]/div/div/button/span")).click();
                
        driver.findElement(By.xpath("//*[@id=\"password\"]/div[1]/div/div[1]/input")).sendKeys(password);
        driver.findElement(By.xpath("//*[@id=\"passwordNext\"]/div/button/span")).click();
        driver.quit();
    }
    public static void main(String[] args)throws IOException {
        DataDriveFramework readFile=new DataDriveFramework();
        String filePath="C:\\Users\\Shefali\\eclipse-workspace\\DataDriveFramework\\TestExcelSheet\\";
        readFile.readExcel(filePath, "DataDriven.xls", "Sheet1");
    }
    
}

错误信息 #

Exception in thread "main" java.lang.NullPointerException: Cannot invoke "org.apache.poi.ss.usermodel.Workbook.getSheet(String)" because "loginWorkbook" is "null"
    at com.framework.DataDriveFramework.readExcel(DataDriveFramework.java:31)
    at com.framework.DataDriveFrame.main(DataDriveFrame.java:60)

问题分析与修复方案 #

1. 文件扩展名匹配错误（空指针直接原因） #

原代码中判断.xls文件时，条件写为fileExtension.equals("xls")，但通过fileName.substring(fileName.indexOf("."))获取的扩展名是带点的.xls，导致匹配失败，loginWorkbook未被初始化，触发空指针。

修复：将判断条件改为fileExtension.equals(".xls")

2. 循环条件语法错误 #

原代码for循环条件写为1<rowCount+1，把变量i写成了数字1，会导致循环逻辑错误（要么死循环要么不执行）。

修复：改为i < rowCount + 1

3. 密码读取列索引错误 #

原代码中密码读取的是第0列（和用户名同一列），应该读取第1列数据。

修复：将String password=row.getCell(0).getStringCellValue();改为String password=row.getCell(1).getStringCellValue();

4. 资源未关闭（潜在问题） #

FileInputStream和Workbook使用后未关闭，会造成资源泄漏，建议使用try-with-resources自动管理资源。

5. 其他优化点 #

• 避免每次登录都新建WebDriver实例，可考虑复用或提取为类成员变量
• 绝对XPath稳定性差，建议改用相对定位或元素ID

修正后完整代码 #

package com.framework;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.time.Duration;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.openqa.selenium.By;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.firefox.FirefoxDriver;

public class DataDriveFramework {
    public void readExcel(String filePath, String fileName, String sheetName) throws IOException {
        File file = new File(filePath + "\\" + fileName);
        // 使用try-with-resources自动关闭输入流和Workbook
        try (FileInputStream fis = new FileInputStream(file);
             Workbook loginWorkbook = getWorkbook(fis, fileName)) {

            Sheet loginSheet = loginWorkbook.getSheet(sheetName);
            if (loginSheet == null) {
                throw new IllegalArgumentException("指定的Sheet不存在: " + sheetName);
            }

            int rowCount = loginSheet.getLastRowNum() - loginSheet.getFirstRowNum();
            for (int i = 1; i < rowCount + 1; i++) {
                Row row = loginSheet.getRow(i);
                if (row == null) continue; // 跳过空行

                String username = row.getCell(0).getStringCellValue();
                String password = row.getCell(1).getStringCellValue(); // 修正列索引
                test(username, password);
            }
        }
    }

    // 提取Workbook创建逻辑，提高可读性
    private Workbook getWorkbook(FileInputStream fis, String fileName) throws IOException {
        String fileExtension = fileName.substring(fileName.indexOf("."));
        if (fileExtension.equals(".xlsx")) {
            return new XSSFWorkbook(fis);
        } else if (fileExtension.equals(".xls")) {
            return new HSSFWorkbook(fis);
        } else {
            throw new IllegalArgumentException("不支持的文件格式: " + fileExtension);
        }
    }

    public void test(String username, String password) {
        WebDriver driver = new FirefoxDriver();
        driver.manage().timeouts().implicitlyWait(Duration.ofSeconds(10));

        try {
            String baseURL = "https://accounts.google.com";
            driver.get(baseURL);

            driver.findElement(By.id("identifierId")).sendKeys(username);
            // 改用相对XPath或更稳定的定位方式
            driver.findElement(By.xpath("//button[@jsname='LgbsSe']")).click();

            driver.findElement(By.xpath("//input[@name='Passwd']")).sendKeys(password);
            driver.findElement(By.xpath("//button[@jsname='LgbsSe']")).click();
        } finally {
            driver.quit(); // 确保无论是否异常都关闭浏览器
        }
    }

    public static void main(String[] args) throws IOException {
        DataDriveFramework readFile = new DataDriveFramework();
        String filePath = "C:\\Users\\Shefali\\eclipse-workspace\\DataDriveFramework\\TestExcelSheet\\";
        readFile.readExcel(filePath, "DataDriven.xls", "Sheet1");
    }
}

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