如何将生成的Ticket No写入读取数据的Excel对应行最后一列

我需要把Excel每行数据读取为HashMap，传入自动化脚本执行后生成Ticket No，再将该编号写回原Excel对应行的最后一列。

初始Excel状态 #

期望Excel状态 #

我已有读取Excel的工具类，但无法将Ticket No写回原Excel的对应行，以下是相关代码片段：

数据提供器 #

@DataProvider(name = "Test1")
public static Object[][] getDetails() {
    ExcelReaderUtil excelReaderUtil = new ExcelReaderUtil("InputData1.xlsx");
    return excelReaderUtil.getFilteredDataAsHashMapFromSheet("Test1", "SheetName1");
}

Excel读取工具类 #

public ExcelReaderUtil(String excelfilePath) {
    try {
        this.file = new File(excelfilePath);
        if (!this.file.exists()) {
            this.file = new File(Constants.WORKING_DIRECTORY + "/" + excelfilePath);
            if (!this.file.exists()) {
                this.file = new File(Constants.WORKING_DIRECTORY + "/src/test/resources/data/" + excelfilePath);
            }
        }

        FileInputStream stream = new FileInputStream(this.file);
        this.work_book = new XSSFWorkbook(stream);
    } catch (Exception var3) {
        LoggerUtil.log(var3.getMessage());
    }
}

public Object[][] getFilteredDataAsHashMapFromSheet(String sheetName, String testCaseName) {
    XSSFSheet sheet = this.work_book.getSheet(sheetName);
    Object[][] dataArray = this.getDataAsHashMapFromSheet(sheet);
    return this.getFilteredDataAsHashMapFromSheet(dataArray, testCaseName);
}

写入尝试代码（存在问题） #

public static void main(String[] args) throws IOException, Exception  {
    XSSFWorkbook wb=new XSSFWorkbook();
    XSSFSheet spreadsheet=wb.getSheetAt(0);
    FileOutputStream fos=null;
    
        Row row=spreadsheet.createRow(i);
            Cell value=row.createCell(j);
            value.setCellValue(ticketno);
            fos=new FileOutputStream("InputData1.xlsx");
            wb.write(fos);
        
    wb.close();
    fos.close();
}

解决方案 #

核心问题在于当前写入代码新建了空的XSSFWorkbook，而非复用读取时已加载的work_book实例，同时缺少每行数据对应的行号信息（HashMap仅存储列名和值，未记录行位置）。

步骤1：修改读取逻辑，保存行号信息 #

在读取Excel时，同时返回行号和对应的数据HashMap，用自定义类包装这两个信息：

// 自定义类，存储行号与对应数据
public class ExcelRowData {
    private int rowNum;
    private Map<String, String> dataMap;

    public ExcelRowData(int rowNum, Map<String, String> dataMap) {
        this.rowNum = rowNum;
        this.dataMap = dataMap;
    }

    public int getRowNum() { return rowNum; }
    public Map<String, String> getDataMap() { return dataMap; }
}

// 修改ExcelReaderUtil中的读取方法，返回包含行号的对象数组
public Object[][] getDataAsHashMapFromSheet(XSSFSheet sheet) {
    List<ExcelRowData> rowDataList = new ArrayList<>();
    XSSFRow headerRow = sheet.getRow(0);
    int lastRowNum = sheet.getLastRowNum();

    for (int i = 1; i <= lastRowNum; i++) { // 跳过表头行
        XSSFRow row = sheet.getRow(i);
        if (row == null) continue;

        Map<String, String> dataMap = new HashMap<>();
        for (int j = 0; j < headerRow.getLastCellNum(); j++) {
            XSSFCell cell = row.getCell(j);
            String cellValue = cell == null ? "" : cell.toString();
            dataMap.put(headerRow.getCell(j).toString(), cellValue);
        }
        rowDataList.add(new ExcelRowData(i, dataMap));
    }

    // 转换为DataProvider需要的Object[][]格式
    Object[][] result = new Object[rowDataList.size()][1];
    for (int i = 0; i < rowDataList.size(); i++) {
        result[i][0] = rowDataList.get(i);
    }
    return result;
}

步骤2：在测试类中复用读取实例，写入Ticket No #

public class TicketGenerationTest {
    private static ExcelReaderUtil excelReader;

    @BeforeClass
    public static void initReader() {
        excelReader = new ExcelReaderUtil("InputData1.xlsx");
    }

    @Test(dataProvider = "Test1")
    public void generateTicket(ExcelRowData rowData) {
        // 获取数据并执行自动化脚本生成Ticket No
        Map<String, String> data = rowData.getDataMap();
        String ticketNo = yourAutomationLogic(data); // 替换为你的生成逻辑

        // 定位对应行和Ticket No列，写入数据
        XSSFSheet sheet = excelReader.work_book.getSheet("Test1");
        XSSFRow row = sheet.getRow(rowData.getRowNum());
        
        // 查找Ticket No列的索引
        XSSFRow header = sheet.getRow(0);
        int ticketColIndex = -1;
        for (int j = 0; j < header.getLastCellNum(); j++) {
            if ("Ticket No".equals(header.getCell(j).toString())) {
                ticketColIndex = j;
                break;
            }
        }

        if (ticketColIndex != -1) {
            XSSFCell ticketCell = row.getCell(ticketColIndex);
            if (ticketCell == null) {
                ticketCell = row.createCell(ticketColIndex);
            }
            ticketCell.setCellValue(ticketNo);
        }
    }

    @AfterClass
    public static void writeBackExcel() throws IOException {
        // 所有测试完成后，将修改写入原文件
        FileOutputStream fos = new FileOutputStream(excelReader.file);
        excelReader.work_book.write(fos);
        fos.close();
        excelReader.work_book.close();
    }

    // 示例自动化逻辑，替换为你的实际代码
    private String yourAutomationLogic(Map<String, String> data) {
        // 这里生成Ticket No
        return switch (data.get("Testcases")) {
            case "TC1" -> "M89373";
            case "TC2" -> "H15243";
            case "TC3" -> "H89734";
            case "TC4" -> "KT98273";
            default -> "";
        };
    }
}

步骤3：修正原写入代码的错误 #

原写入代码的问题：

• 新建空XSSFWorkbook会覆盖原文件内容，应复用读取时的实例
• 空Workbook调用getSheetAt(0)会报错，因为不存在工作表
• 未定义i和j变量，无法定位行和列

通过复用读取实例和记录行号，即可精准写入对应位置。

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