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.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
            Iterator<Row> rowIterator = sheet.iterator();

            // 读取B列数据中的空白单元格
            List<Integer> blankIndexes = new ArrayList<Integer>();
            while (rowIterator.hasNext()) {
                Row row = rowIterator.next();
                Cell cell = row.getCell(1); // B列数据
                if (cell.getCellTypeEnum() == CellType.BLANK) {
                    blankIndexes.add(row.getRowNum());
                }
            }

            // 计算B列数据空白单元格上下不为空白单元格的数据的平均值
            for (int index : blankIndexes) {
                Row row = sheet.getRow(index);
                Cell cell = row.getCell(1); // B列数据
                double sum = 0;
                int count = 0;
                int i = index - 1;
                while (i >= 0) { // 向上寻找不为空白单元格的数据
                    Row prevRow = sheet.getRow(i);
                    Cell prevCell = prevRow.getCell(1);
                    if (prevCell.getCellTypeEnum() != CellType.BLANK) {
                        sum += prevCell.getNumericCellValue();
                        count++;
                    } else {
                        break;
                    }
                    i--;
                }
                int j = index + 1;
                while (j <= sheet.getLastRowNum()) { // 向下寻找不为空白单元格的数据
                    Row nextRow = sheet.getRow(j);
                    Cell nextCell = nextRow.getCell(1);
                    if (nextCell.getCellTypeEnum() != CellType.BLANK) {
                        sum += nextCell.getNumericCellValue();
                        count++;
                    } else {
                        break;
                    }
                    j++;
                }
                if (count > 0) {
                    double average = sum / count;
                    cell.setCellValue(average);
                }
            }

            // 填充B列空白单元格
            for (int i = 0; i < blankIndexes.size(); i++) {
                int index = blankIndexes.get(i);
                Row row = sheet.getRow(index);
                Cell cell = row.getCell(1); // B列数据
                if (cell.getCellTypeEnum() == CellType.BLANK) {
                    double sum = 0;
                    int count = 0;
                    int j = index - 1;
                    while (j >= 0) { // 向上寻找不为空白单元格的数据
                        Row prevRow = sheet.getRow(j);
                        Cell prevCell = prevRow.getCell(1);
                        if (prevCell.getCellTypeEnum() != CellType.BLANK) {
                            sum += prevCell.getNumericCellValue();
                            count++;
                        } else {
                            break;
                        }
                        j--;
                    }
                    int k = index + 1;
                    while (k <= sheet.getLastRowNum()) { // 向下寻找不为空白单元格的数据
                        Row nextRow = sheet.getRow(k);
                        Cell nextCell = nextRow.getCell(1);
                        if (nextCell.getCellTypeEnum() != CellType.BLANK) {
                            sum += nextCell.getNumericCellValue();
                            count++;
                        } else {
                            break;
                        }
                        k++;
                    }
                    if (count > 0) {
                        double average = sum / count;
                        cell.setCellValue(average);
                        // 填充相邻空白单元格
                        if (i < blankIndexes.size() - 1 && index + 1 == blankIndexes.get(i + 1)) {
                            Row nextRow = sheet.getRow(index + 1);
                            Cell nextCell = nextRow.getCell(1);
                            nextCell.setCellValue(average);
                            i++;
                        }
                    }
                }
            }

            // 写入Excel文件
            FileOutputStream outputStream = new FileOutputStream(new File('output.xlsx'));
            workbook.write(outputStream);
            workbook.close();
            inputStream.close();
            outputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

}