Excel 日期插入工具：自动在日期列插入中间值

package Data_Recovery;

import java.io.FileInputStream; import java.io.FileOutputStream; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Date; import java.util.List;

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

public class EM03 { public static void main(String[] args) { try { // 读取input-2.xlsx文件 FileInputStream file = new FileInputStream('input-2.xlsx'); Workbook workbook = new XSSFWorkbook(file); Sheet sheet = workbook.getSheet('P1');

        // 检查是否存在P2工作表，如果存在则删除
        if (workbook.getSheet('P2') != null) {
            workbook.removeSheetAt(workbook.getSheetIndex('P2'));
        }

        // 创建新的P2工作表
        Sheet newSheet = workbook.createSheet('P2');

        // 复制P1工作表的单元格格式到P2工作表
        copyCellStyle(workbook, sheet, newSheet);

        // 获取P1工作表第一列的时间日期数据
        List<Date> dates = getColumnValues(sheet, 0);

        // 进行日期插入操作
        List<Date> newDates = insertDates(dates);

        // 将插入后的日期写入P2工作表
        writeColumnValues(newSheet, 0, newDates);

        // 保存结果到input-2.xlsx文件
        FileOutputStream outFile = new FileOutputStream('input-2.xlsx');
        workbook.write(outFile);
        outFile.close();

        System.out.println('操作完成');
    } catch (Exception e) {
        e.printStackTrace();
    }
}

// 复制单元格格式
private static void copyCellStyle(Workbook workbook, Sheet sourceSheet, Sheet targetSheet) {
    for (int rowIndex = 0; rowIndex <= sourceSheet.getLastRowNum(); rowIndex++) {
        Row sourceRow = sourceSheet.getRow(rowIndex);
        Row targetRow = targetSheet.createRow(rowIndex);

        for (int columnIndex = 0; columnIndex < sourceRow.getLastCellNum(); columnIndex++) {
            Cell sourceCell = sourceRow.getCell(columnIndex);
            Cell targetCell = targetRow.createCell(columnIndex);

            if (sourceCell != null) {
                CellStyle style = sourceCell.getCellStyle();
                targetCell.setCellStyle(style);
            }
        }
    }
}

// 获取指定列的日期数据
private static List<Date> getColumnValues(Sheet sheet, int columnIndex) {
    List<Date> columnValues = new ArrayList<>();

    for (int rowIndex = 0; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
        Row row = sheet.getRow(rowIndex);

        if (row != null) {
            Cell cell = row.getCell(columnIndex);

            if (cell != null && cell.getCellType() == CellType.NUMERIC) {
                columnValues.add(cell.getDateCellValue());
            }
        }
    }

    return columnValues;
}

// 在日期数据中插入中间值，直至相邻日期间隔小于10天
private static List<Date> insertDates(List<Date> dates) {
    List<Date> newDates = new ArrayList<>(dates);

    for (int i = 0; i < newDates.size() - 1; i++) {
        Date currentDate = newDates.get(i);
        Date nextDate = newDates.get(i + 1);

        long interval = (nextDate.getTime() - currentDate.getTime()) / (24 * 60 * 60 * 1000);

        if (interval > 10) {
            while (interval > 10) {
                Date middleDate = new Date((currentDate.getTime() + nextDate.getTime()) / 2);
                if (!newDates.contains(middleDate)) {
                    newDates.add(i + 1, middleDate);
                }
                interval = (nextDate.getTime() - middleDate.getTime()) / (24 * 60 * 60 * 1000);
                nextDate = newDates.get(i + 2); // 更新相邻日期
            }
        }
    }

    return newDates;
}

// 将日期数据写入指定列
private static void writeColumnValues(Sheet sheet, int columnIndex, List<Date> dates) {
    SimpleDateFormat dateFormat = new SimpleDateFormat('yyyy-MM-dd hh:mm');

    for (int rowIndex = 0; rowIndex < dates.size(); rowIndex++) {
        Row row = sheet.getRow(rowIndex);

        if (row == null) {
            row = sheet.createRow(rowIndex);
        }

        Cell cell = row.createCell(columnIndex);
        cell.setCellValue(dateFormat.format(dates.get(rowIndex)));
    }
}

}