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

import java.io.File; import java.io.FileInputStream; import java.io.FileOutputStream; 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.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 file = new FileInputStream(new File('input.xlsx'));
        Workbook workbook = WorkbookFactory.create(file);
        Sheet sheet = workbook.getSheetAt(0); // 默认读取 Sheet1
        
        // 处理 B 列空白单元格
        processBlankCells(sheet);
        
        // 写入新的 Excel 文件
        FileOutputStream out = new FileOutputStream(new File('output.xlsx'));
        workbook.write(out);
        out.close();
        
        System.out.println('Excel 文件处理完毕！');
    } catch (Exception e) {
        e.printStackTrace();
    }
}

// 处理 B 列空白单元格
private static void processBlankCells(Sheet sheet) {
    Iterator<Row> rowIterator = sheet.iterator();
    List<Integer> blankCellIndexes = new ArrayList<>(); // 记录空白单元格的索引
    int rowIndex = 0;
    while (rowIterator.hasNext()) {
        Row row = rowIterator.next();
        if (rowIndex == 0) { // 跳过第一行
            rowIndex++;
            continue;
        }
        Cell dateCell = row.getCell(0);
        Cell dataCell = row.getCell(1);
        if (dataCell.getCellType() == Cell.CELL_TYPE_BLANK) { // 记录空白单元格的索引
            blankCellIndexes.add(rowIndex);
        }
        rowIndex++;
    }
    // 处理空白单元格
    for (int blankCellIndex : blankCellIndexes) {
        processBlankCell(sheet, blankCellIndex);
    }
}

// 处理单个空白单元格
private static void processBlankCell(Sheet sheet, int blankCellIndex) {
    Row row = sheet.getRow(blankCellIndex);
    Cell dataCell = row.getCell(1);
    int blankCount = 1; // 空白单元格的数量
    double sum = 0;
    int rowIndex = blankCellIndex - 1; // 从上一个不为空的单元格开始查找
    while (rowIndex >= 0) { // 向上搜索
        Row prevRow = sheet.getRow(rowIndex);
        Cell prevDataCell = prevRow.getCell(1);
        if (prevDataCell.getCellType() != Cell.CELL_TYPE_BLANK) {
            sum += prevDataCell.getNumericCellValue();
            blankCount++;
        } else {
            break;
        }
        rowIndex--;
    }
    rowIndex = blankCellIndex + 1; // 从下一个不为空的单元格开始查找
    while (rowIndex <= sheet.getLastRowNum()) { // 向下搜索
        Row nextRow = sheet.getRow(rowIndex);
        Cell nextDataCell = nextRow.getCell(1);
        if (nextDataCell.getCellType() != Cell.CELL_TYPE_BLANK) {
            sum += nextDataCell.getNumericCellValue();
            blankCount++;
        } else {
            break;
        }
        rowIndex++;
    }
    double average = sum / blankCount; // 计算平均值
    dataCell.setCellValue(average); // 填充空白单元格
    // 填充相邻空白单元格
    rowIndex = blankCellIndex - 1;
    while (rowIndex >= 0) { // 向上搜索
        Row prevRow = sheet.getRow(rowIndex);
        Cell prevDataCell = prevRow.getCell(1);
        if (prevDataCell.getCellType() == Cell.CELL_TYPE_BLANK) {
            prevDataCell.setCellValue(average);
        } else {
            break;
        }
        rowIndex--;
    }
    rowIndex = blankCellIndex + 1;
    while (rowIndex <= sheet.getLastRowNum()) { // 向下搜索
        Row nextRow = sheet.getRow(rowIndex);
        Cell nextDataCell = nextRow.getCell(1);
        if (nextDataCell.getCellType() == Cell.CELL_TYPE_BLANK) {
            nextDataCell.setCellValue(average);
        } else {
            break;
        }
        rowIndex++;
    }
}

}