使用 Apache POI 填充 Excel 文件中的缺失数据

package org.example;

import org.apache.poi.ss.usermodel.; import org.apache.poi.xssf.usermodel.;

import java.io.FileInputStream; import java.io.FileOutputStream; import java.io.IOException; import java.util.Calendar; import java.util.Date; import java.util.Map; import java.util.TreeMap;

public class test05 {

public static void main(String[] args) {
    String inputFilePath = "C:\\Users\\Administrator\\Desktop\\input.xlsx";
    String outputFilePath = "output.xlsx";
    String sheetName = "Sheet1";

    try {
        // 读取输入Excel文件
        FileInputStream fis = new FileInputStream(inputFilePath);
        Workbook wb = new XSSFWorkbook(fis);
        Sheet sheet = wb.getSheet(sheetName);

        // 读取数据
        Map<Date, Double> dataMap = new TreeMap<>();
        for (Row row : sheet) {
            if (row.getRowNum() == 0) {
                continue; // 跳过表头
            }
            Cell dateCell = row.getCell(0);
            Cell dataCell = row.getCell(1);
            // 解决空指针异常：判断单元格是否为null
            if (dataCell == null) {
                continue;
            }
            Date date = dateCell.getDateCellValue();
            double data = dataCell.getNumericCellValue();
            dataMap.put(date, data);
        }

        // 计算平均值
        double sum = 0.0;
        int count = 0;
        for (Double data : dataMap.values()) {
            sum += data;
            count++;
        }
        double avg = sum / count;

        // 填充缺失数据
        Date startDate = dataMap.keySet().iterator().next();
        Date endDate = new Date(); // 当前日期
        Calendar calendar = Calendar.getInstance();
        calendar.setTime(startDate);
        while (calendar.getTime().before(endDate)) {
            Date date = calendar.getTime();
            if (!dataMap.containsKey(date)) {
                dataMap.put(date, avg);
            }
            calendar.add(Calendar.DATE, 1); // 加一天
        }

        // 写入输出Excel文件
        FileOutputStream fos = new FileOutputStream(outputFilePath);
        XSSFWorkbook wbOut = new XSSFWorkbook();
        Sheet sheetOut = wbOut.createSheet(sheetName);
        int rowNum = 0;
        for (Map.Entry<Date, Double> entry : dataMap.entrySet()) {
            Date date = entry.getKey();
            double data = entry.getValue();
            Row row = sheetOut.createRow(rowNum++);
            Cell dateCell = row.createCell(0);
            Cell dataCell = row.createCell(1);
            dateCell.setCellValue(date);
            dataCell.setCellValue(data);
        }
        wbOut.write(fos);
        fos.close();

        System.out.println("数据已写入输出Excel文件：" + outputFilePath);
    } catch (IOException e) {
        e.printStackTrace();
    }
}

}