Apache POI导出Excel时如何根据动态数据行自动调整平均值位置

问题背景 #

需要将PostgreSQL的SQL查询结果通过Apache POI写入预置的Excel模板，在数据区域底部对指定列计算平均值；由于每次查询返回的数据行数不固定，需要自动适配平均值行的写入位置。
相关参考：

• 操作逻辑示例：
• 最终效果示例：
  现有待优化的代码逻辑如下：

Resource resource = resourceLoader.getResource("classpath:/temps/" + querySelected.getTemplateName());

workbook = new XSSFWorkbook(resource.getInputStream());
XSSFSheet sheetTable1 = workbook.getSheet("Table1");

int rowCounter = 1;


for (tname:tname) {

    Row values = sheetTable1.createRow(rowCounter);
    rowCounter++;}


    cell = values.createCell(0, CellType.NUMERIC);
    cell.setCellValue(knz.gettablename().doubleValue());}

实现方法 #

核心逻辑不需要额外遍历Sheet查找最后一行：你代码里用来记录写入行号的rowCounter变量，在所有数据行写入完成后，刚好指向数据区域下方的第一个空行，直接在这个位置创建平均值行即可，完全适配动态行数。

第一步：修正现有代码的逻辑问题 #

你当前的代码把单元格赋值逻辑写在了循环外部，只会给最后一行数据赋值，需要把单元格赋值移动到循环内部。

第二步：写入平均值行 #

平均值推荐直接写入Excel原生AVERAGE公式，后续打开文件时如果手动调整数据，平均值会自动重算，不需要Java端额外计算。
修正后的可运行代码示例：

Resource resource = resourceLoader.getResource("classpath:/temps/" + querySelected.getTemplateName());
workbook = new XSSFWorkbook(resource.getInputStream());
XSSFSheet sheetTable1 = workbook.getSheet("Table1");
// 起始行号根据模板表头行数调整，当前值对应「第1行（索引0）是表头，从第2行（索引1）开始写数据」
int rowCounter = 1;

// 遍历SQL结果逐行写入数据
for (DataEntity knz : tnameList) {
    Row dataRow = sheetTable1.createRow(rowCounter);
    // 单元格赋值逻辑必须放在循环内，逐行给每个单元格赋值
    Cell firstCell = dataRow.createCell(0, CellType.NUMERIC);
    firstCell.setCellValue(knz.getTableName().doubleValue());
    // 其余业务列的赋值逻辑按需求补充在这里
    
    rowCounter++;
}

// 循环结束后，rowCounter就是平均值行的POI行索引，直接创建该行
Row avgRow = sheetTable1.createRow(rowCounter);
// 第一列写入平均值标识
avgRow.createCell(0).setCellValue("平均值");

// 给需要计算平均值的列设置公式，示例为计算第2列（POI列索引1，对应Excel列标识B）的平均值
// 注意Excel行号从1开始，数据起始行是第2行（对应POI索引1），最后一行数据的Excel行号刚好等于rowCounter
Cell avgCell = avgRow.createCell(1, CellType.FORMULA);
avgCell.setCellFormula(String.format("AVERAGE(B2:B%d)", rowCounter));
// 其余需要计算平均值的列，按相同逻辑替换列索引、Excel列标识即可

可选优化 #

如果不需要Excel原生公式，想直接在Java端计算好平均值写入，可以在遍历数据写入时，用变量累加对应列的数值总和、统计有效数据行数，循环结束后直接计算平均值写入avgRow的对应单元格即可，行定位逻辑完全不变。
如果模板中可能残留历史写入的旧数据，可以在写入新数据前先遍历删除旧数据行，避免数据混淆；如果是追加写入场景，直接把rowCounter初始值设为sheetTable1.getLastRowNum() + 1即可。 #

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