Java Apache POI Excel 数据处理：计算缺失日期的平均值

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

import java.io.FileInputStream; import java.io.FileOutputStream; import java.text.SimpleDateFormat; import java.util.*;

public class test04 {

public static void main(String[] args) {
    try {
        // 从输入Excel文件中读取数据
        FileInputStream inputWorkbook = new FileInputStream('C:\Users\Administrator\Desktop\input.xlsx');
        Workbook workbook = WorkbookFactory.create(inputWorkbook);
        Sheet sheet = workbook.getSheetAt(0);
        int totalRows = sheet.getLastRowNum() + 1;
        ArrayList<Data> dataList = new ArrayList<>();
        for (int i = 1; i < totalRows; i++) {
            Row row = sheet.getRow(i);
            Data data = new Data();
            data.date = getDateFromCell(row.getCell(0));
            data.value = getValueFromCell(row.getCell(1));
            dataList.add(data);
        }
        inputWorkbook.close();

        // 按日期对数据排序
        Collections.sort(dataList);

        // 计算缺失日期的平均值
        Map<Date, Double> missingDataMap = new HashMap<>();
        for (int i = 0; i < dataList.size() - 1; i++) {
            Data currentData = dataList.get(i);
            Data nextData = dataList.get(i + 1);
            long diffInMillis = nextData.date.getTime() - currentData.date.getTime();
            long diffInDays = diffInMillis / (24 * 60 * 60 * 1000);
            if (diffInDays > 1) {
                double diffInValue = nextData.value - currentData.value;
                double dailyValue = diffInValue / diffInDays;
                for (int j = 1; j < diffInDays; j++) {
                    Date missingDate = new Date(currentData.date.getTime() + j * (24 * 60 * 60 * 1000));
                    missingDataMap.put(missingDate, currentData.value + j * dailyValue);
                }
            }
        }
        double totalMissingValue = 0.0;
        int totalMissingCount = missingDataMap.size();
        for (Double value : missingDataMap.values()) {
            totalMissingValue += value;
        }
        double averageMissingValue = totalMissingValue / totalMissingCount;

        // 将数据写入输出Excel文件
        Workbook outputWorkbook = WorkbookFactory.create(true);
        Sheet outputSheet = outputWorkbook.createSheet();
        CellStyle dateStyle = outputWorkbook.createCellStyle();
        dateStyle.setDataFormat(outputWorkbook.getCreationHelper().createDataFormat().getFormat('yyyy-mm-dd'));
        int rowIndex = 0;
        Row headerRow = outputSheet.createRow(rowIndex++);
        headerRow.createCell(0).setCellValue('Date');
        headerRow.createCell(1).setCellValue('Value');
        for (Data data : dataList) {
            Row row = outputSheet.createRow(rowIndex++);
            Cell dateCell = row.createCell(0);
            dateCell.setCellValue(data.date);
            dateCell.setCellStyle(dateStyle);
            row.createCell(1).setCellValue(data.value);
        }
        for (Map.Entry<Date, Double> entry : missingDataMap.entrySet()) {
            Row row = outputSheet.createRow(rowIndex++);
            Cell dateCell = row.createCell(0);
            dateCell.setCellValue(entry.getKey());
            dateCell.setCellStyle(dateStyle);
            row.createCell(1).setCellValue(entry.getValue());
        }
        Row averageRow = outputSheet.createRow(rowIndex);
        averageRow.createCell(0).setCellValue('Average missing value:');
        averageRow.createCell(1).setCellValue(averageMissingValue);
        FileOutputStream outputWorkbookFile = new FileOutputStream('output.xlsx');
        outputWorkbook.write(outputWorkbookFile);
        outputWorkbookFile.close();
        outputWorkbook.close();
        System.out.println('Data processing completed successfully.');
    } catch (Exception e) {
        e.printStackTrace();
    }
}

public static Date getDateFromCell(Cell cell) {
    if (cell.getCellType() == CellType.NUMERIC && DateUtil.isCellDateFormatted(cell)) {
        return cell.getDateCellValue();
    } else {
        DataFormatter formatter = new DataFormatter();
        try {
            return new SimpleDateFormat('yyyy-MM-dd').parse(formatter.formatCellValue(cell));
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }
}

public static double getValueFromCell(Cell cell) {
    if (cell.getCellType() == CellType.NUMERIC ) {
        return cell.getNumericCellValue();
    } else {
        DataFormatter formatter = new DataFormatter();
        try {
            return Double.parseDouble(formatter.formatCellValue(cell));
        } catch (Exception e) {
            e.printStackTrace();
            return 0.0;
        }
    }
}

static class Data implements Comparable<Data> {
    Date date;
    double value;

    @Override
    public int compareTo(Data o) {
        return this.date.compareTo(o.date);
    }
}

}