使用Java POI读取Excel仅返回表头和首行的问题排查求助

问题描述 #

使用Java POI读取Excel文件时遇到两个异常：

• getAllRows方法仅返回表头和首行数据，无法读取所有行
• getASingleRow方法仅返回表头内容，而非指定索引的数据行

代码实现 #

1. Cucumber场景步骤 #

When user enters data from the supplied excel file at the location
        |Excel        |     Location                               |  Sheet   |
        | ftbank.xlsx |    src/test/resources/testdata/ftbank.xlsx |  data    |

2. IDataReader接口 #

public interface IDataReader {
    /**
     * 读取Excel所有行数据
     * @return 包含所有行的Map列表，键为表头，值为单元格内容
     */
    public List<Map<String, String>> getAllRows();

    /**
     * 读取Excel指定索引的单行数据
     * @return 单行数据的Map，键为表头，值为单元格内容
     */
    public Map<String , String> getASingleRow();
}

3. ExcelConfiguration配置类 #

package testing.tables.dataTables;

import java.util.Objects;

public class ExcelConfiguration {
    private final String fileName;
    private final String fileLocation;
    private final String sheetName;
    private int index = -1;

    public String getFileName() {
        return fileName;
    }

    public String getFileLocation() {
        return fileLocation;
    }

    public String getSheetName() {
        return sheetName;
    }

    public int getIndex() {
        return index;
    }

    private ExcelConfiguration(String fileName, String fileLocation, String sheetName, int index) {
        this.fileName = fileName;
        this.fileLocation = fileLocation;
        this.sheetName = sheetName;
        this.index = index;
    }

    public static class ExcelConfigurationBuilder {
        private String fileName;
        private String fileLocation;
        private String sheetName;
        private int index = -1;

        public ExcelConfigurationBuilder setFileName(String fileName) {
            this.fileName = fileName;
            return this;
        }

        public ExcelConfigurationBuilder setFileLocation(String fileLocation) {
            this.fileLocation = fileLocation;
            return this;
        }

        public ExcelConfigurationBuilder setSheetName(String sheetName) {
            this.sheetName = sheetName;
            return this;
        }

        public ExcelConfigurationBuilder setIndex(int index) {
            this.index = index;
            return this;
        }

        public ExcelConfiguration build(){
            Objects.requireNonNull(fileName);
            Objects.requireNonNull(fileLocation);
            Objects.requireNonNull(sheetName);
            return new ExcelConfiguration(fileName, fileLocation, sheetName, index);
        }
    }
}

4. ExcelDataReader实现类（核心读取逻辑） #

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import lombok.SneakyThrows;
import java.io.File;
import java.io.IOException;
import java.util.*;
import java.util.function.BiConsumer;

public class ExcelDataReader implements IDataReader {
    private final ExcelConfiguration config;
    private Logger logger = LoggerFactory.getLogger(ExcelDataReader.class);

    public ExcelDataReader(ExcelConfiguration config) {
        this.config = config;
    }

    @SneakyThrows
    private XSSFWorkbook getWorkBook() throws IOException {
        return new XSSFWorkbook(new File(config.getFileLocation()));
    }

    private XSSFSheet getSheet(XSSFWorkbook workBook) {
        return workBook.getSheet(config.getSheetName());
    }

    private List<String> getHeaders(XSSFSheet sheet) {
        List<String> headers = new ArrayList<>();
        XSSFRow row = sheet.getRow(0);
        row.forEach((cell) -> headers.add(cell.getStringCellValue()));
        return Collections.unmodifiableList(headers);
    }

    @Override
    public List<Map<String, String>> getAllRows() {
        List<Map<String, String>> data;
        try (XSSFWorkbook workBook = getWorkBook()) {
            XSSFSheet sheet = getSheet(workBook);
            data = getData(sheet);
        } catch (Exception e) {
            logger.error(e, () -> String.format("无法读取Excel文件：%s，路径：%s", config.getFileName(), config.getFileLocation()));
            return Collections.emptyList();
        }
        return Collections.unmodifiableList(data);
    }

    private List<Map<String, String>> getData(XSSFSheet sheet) {
        List<Map<String, String>> data = new ArrayList<>();
        List<String> headers = getHeaders(sheet);

        for (int i = 1; i <= sheet.getLastRowNum(); i++) {
            XSSFRow row = sheet.getRow(i);
            if (row == null) continue; // 跳过空行
            Map<String, String> rowMap = new HashMap<>();
            forEachWithCounter(row, (index, cell) -> {
                rowMap.put(headers.get(index), cell.getStringCellValue());
            });
            data.add(rowMap);
        }
        return Collections.unmodifiableList(data);
    }

    private Map<String, String> getData(XSSFSheet sheet, int rowIndex) {
        List<String> headers = getHeaders(sheet);
        Map<String, String> rowMap = new HashMap<>();

        XSSFRow row = sheet.getRow(rowIndex);
        if (row == null) return rowMap; // 处理空行
        forEachWithCounter(row, (index, cell) -> {
            rowMap.put(headers.get(index), cell.getStringCellValue());
        });

        return Collections.unmodifiableMap(rowMap);
    }

    @Override
    public Map<String, String> getASingleRow() {
        Map<String, String> data;
        try (XSSFWorkbook workbook = getWorkBook()) {
            XSSFSheet sheet = getSheet(workbook);
            data = getData(sheet, config.getIndex());
        } catch (Exception e) {
            logger.error(e, () -> String.format("无法读取Excel文件：%s，路径：%s", config.getFileName(), config.getFileLocation()));
            return Collections.emptyMap();
        }
        return Collections.unmodifiableMap(data);
    }

    private void forEachWithCounter(Iterable<Cell> source, BiConsumer<Integer, Cell> biConsumer) {
        int i = 0;
        for (Cell cell : source) {
            biConsumer.accept(i, cell);
            i++;
        }
    }
}

5. StepDefinitions测试步骤实现 #

import io.cucumber.java.DataTableType;
import io.cucumber.java.en.When;
import java.util.List;
import java.util.Map;

public class ExcelReaderStepDefinitions {

    @When("user enters data from the supplied excel file at the location")
    public void user_enters_data_from_the_supplied_excel_file_at_the_location(IDataReader dataTable) {
        System.out.println("所有行数据：" + dataTable.getAllRows());
        System.out.println("=====单行数据=====");
        System.out.println(dataTable.getASingleRow());
        System.out.println("=================");

        List<Map<String, String>> data = dataTable.getAllRows();
        System.out.println("=================");
        // System.out.println(data.get(1).get("months"));
        System.out.println("=================");
    }

    @DataTableType
    public IDataReader exceltoDataTable(Map<String, String> entry) {
        ExcelConfiguration config = new ExcelConfiguration.ExcelConfigurationBuilder()
                .setFileName(entry.get("Excel"))
                .setFileLocation(entry.get("Location"))
                .setSheetName(entry.get("Sheet"))
                .setIndex(Integer.valueOf(entry.getOrDefault("Index", "1"))) // 修改默认索引为1，对应第一行数据
                .build();

        return new ExcelDataReader(config);
    }
}

测试输出 #

[{Index=abc, months=girl, deposit=mom, ltv=sis, propertyValue=dad, years=boy, loan=bro}]
=====SINGLE====
{Index=Index, months=months, deposit=deposit, ltv=ltv, propertyValue=propertyValue, years=years, loan=loan}
=========
=========
=========

期望结果 #

• getAllRows返回Excel中所有非表头行的数据
• getASingleRow返回指定索引（默认第一行数据）的内容

测试Excel数据说明：包含表头行（Index、months、deposit、ltv、propertyValue、years、loan），以及多行数据行，例如首行数据为abc、girl、mom、sis、dad、boy、bro。

问题排查与修复方案 #

1. getASingleRow返回表头的问题原因及修复 #

• 原因：原@DataTableType方法中默认索引设置为"0"，对应Excel的表头行（索引0），因此读取到表头内容。
• 修复：将默认索引修改为"1"（对应第一行数据），或者在Cucumber场景中添加Index列指定目标行索引，例如：

When user enters data from the supplied excel file at the location
        |Excel        |     Location                               |  Sheet   | Index |
        | ftbank.xlsx |    src/test/resources/testdata/ftbank.xlsx |  data    | 1     |

2. getAllRows仅返回一行数据的问题排查方向 #

• 检查Excel文件是否确实包含多行非空数据行
• 在getData方法中添加空行判断，避免因空行导致循环中断（已在修复后的代码中添加if (row == null) continue;）
• 验证sheet.getLastRowNum()返回值是否正确，确认是否读取到了所有行的索引

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