Java Excel 处理：填充空白单元格的平均值

import java.io.File; import java.io.FileInputStream; import java.io.FileOutputStream; import java.io.IOException; import java.util.ArrayList; import java.util.Iterator; import java.util.List;

import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellType; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.ss.usermodel.WorkbookFactory;

public class ExcelProcessor {

public static void main(String[] args) {
    try {
        // 读取Excel文件
        FileInputStream inputStream = new FileInputStream(new File('input.xlsx'));
        Workbook workbook = WorkbookFactory.create(inputStream);
        Sheet sheet = workbook.getSheetAt(0); // 默认读取Sheet1

        // 处理B列空白单元格
        processBlankCells(sheet);

        // 写入新的Excel文件
        FileOutputStream outputStream = new FileOutputStream(new File('output.xlsx'));
        workbook.write(outputStream);
        outputStream.close();

        System.out.println('处理完成！');
    } catch (IOException e) {
        e.printStackTrace();
    }
}

private static void processBlankCells(Sheet sheet) {
    int lastRowNum = sheet.getLastRowNum();
    List<CellRange> rangeList = new ArrayList<>(); // 保存每个空白单元格或相邻空白单元格和其上下不为空白单元格的数据计算出来的平均值
    for (int i = 1; i <= lastRowNum; i++) { // 跳过第一行标题
        Row row = sheet.getRow(i);
        if (row != null) {
            Cell dateCell = row.getCell(0);
            Cell dataCell = row.getCell(1);
            if (dateCell != null && dataCell != null) {
                if (dataCell.getCellType() == CellType.BLANK) {
                    // B列空白单元格，开始向上或向下寻找不为空白单元格的数据
                    int aboveIndex = i - 1;
                    int belowIndex = i + 1;
                    double sum = 0.0;
                    int count = 0;
                    while (aboveIndex >= 0 || belowIndex <= lastRowNum) {
                        if (aboveIndex >= 0) {
                            Row aboveRow = sheet.getRow(aboveIndex);
                            if (aboveRow != null) {
                                Cell aboveCell = aboveRow.getCell(1);
                                if (aboveCell != null && aboveCell.getCellType() != CellType.BLANK) {
                                    sum += aboveCell.getNumericCellValue();
                                    count++;
                                    break;
                                }
                            }
                            aboveIndex--;
                        }
                        if (belowIndex <= lastRowNum) {
                            Row belowRow = sheet.getRow(belowIndex);
                            if (belowRow != null) {
                                Cell belowCell = belowRow.getCell(1);
                                if (belowCell != null && belowCell.getCellType() != CellType.BLANK) {
                                    sum += belowCell.getNumericCellValue();
                                    count++;
                                    break;
                                }
                            }
                            belowIndex++;
                        }
                    }
                    if (count > 0) {
                        double average = sum / count;
                        CellRange range = new CellRange(dataCell, aboveIndex + 1, belowIndex - 1, average);
                        rangeList.add(range);
                    }
                }
            }
        }
    }
    // 填充B列空白单元格
    for (CellRange range : rangeList) {
        for (int i = range.startIndex; i <= range.endIndex; i++) {
            Row row = sheet.getRow(i);
            if (row == null) {
                row = sheet.createRow(i);
            }
            Cell cell = row.getCell(1);
            if (cell == null) {
                cell = row.createCell(1);
            }
            cell.setCellValue(range.average);
        }
    }
}

private static class CellRange {
    Cell cell;
    int startIndex;
    int endIndex;
    double average;

    public CellRange(Cell cell, int startIndex, int endIndex, double average) {
        this.cell = cell;
        this.startIndex = startIndex;
        this.endIndex = endIndex;
        this.average = average;
    }
}