Java Excel 数据处理：自动填充空白单元格并计算统计信息

下面是一个示例代码，用于实现上述功能：

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DateUtil;
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 ExcelReader {
    public static void main(String[] args) throws IOException, ParseException {
        // 读取输入Excel文件
        FileInputStream input = new FileInputStream(new File('input.xlsx'));
        Workbook workbook = WorkbookFactory.create(input);
        Sheet sheet = workbook.getSheetAt(0);

        // 获取日期列和数据列的索引
        int dateIndex = -1;
        int dataIndex = -1;
        Row firstRow = sheet.getRow(0);
        Iterator<Cell> cellIterator = firstRow.cellIterator();
        while (cellIterator.hasNext()) {
            Cell cell = cellIterator.next();
            if (cell.getStringCellValue().equals('日期')) {
                dateIndex = cell.getColumnIndex();
            }
            if (cell.getStringCellValue().equals('数据')) {
                dataIndex = cell.getColumnIndex();
            }
        }

        // 检测数据列中的空白，计算出平均值后填充空白
        ArrayList<Double> dataValues = new ArrayList<Double>();
        Iterator<Row> rowIterator = sheet.rowIterator();
        rowIterator.next(); // 跳过第一行的表头
        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            Cell dateCell = row.getCell(dateIndex);
            Cell dataCell = row.getCell(dataIndex);
            if (dataCell.getCellType() == CellType.BLANK) {
                // 计算上下单元格数据的平均值
                double sum = 0.0;
                int count = 0;
                if (row.getRowNum() > 1) {
                    Row prevRow = sheet.getRow(row.getRowNum() - 1);
                    Cell prevDataCell = prevRow.getCell(dataIndex);
                    if (prevDataCell.getCellType() == CellType.NUMERIC) {
                        sum += prevDataCell.getNumericCellValue();
                        count++;
                    }
                }
                if (row.getRowNum() < sheet.getLastRowNum()) {
                    Row nextRow = sheet.getRow(row.getRowNum() + 1);
                    Cell nextDataCell = nextRow.getCell(dataIndex);
                    if (nextDataCell.getCellType() == CellType.NUMERIC) {
                        sum += nextDataCell.getNumericCellValue();
                        count++;
                    }
                }
                double average = sum / count;

                // 填充空白单元格
                dataCell.setCellValue(average);
            } else if (dataCell.getCellType() == CellType.NUMERIC) {
                dataValues.add(dataCell.getNumericCellValue());
            }
        }

        // 计算数据列的平均值和标准差
        double sum = 0.0;
        for (Double value : dataValues) {
            sum += value;
        }
        double average = sum / dataValues.size();

        double variance = 0.0;
        for (Double value : dataValues) {
            variance += Math.pow(value - average, 2);
        }
        double stdDev = Math.sqrt(variance / dataValues.size());

        // 输出平均值和标准差
        System.out.println('平均值：' + average);
        System.out.println('标准差：' + stdDev);

        // 将处理后的数据写入输出Excel文件
        FileOutputStream output = new FileOutputStream(new File('output.xlsx'));
        workbook.write(output);
        output.close();
        input.close();
        workbook.close();
    }

    // 将Excel日期格式转换为Java Date类型
    private static Date getDateValue(Cell cell) throws ParseException {
        if (DateUtil.isCellDateFormatted(cell)) {
            SimpleDateFormat dateFormat = new SimpleDateFormat('yyyy-MM-dd');
            return dateFormat.parse(dateFormat.format(cell.getDateCellValue()));
        } else {
            return null;
        }
    }
}

需要注意的是，上述代码依赖于 Apache POI 库来读写 Excel 文件。在使用之前，需要先下载并添加该库到项目的 Classpath 中。