• 日期: 2028-04-26
  • 标签: 常规

这个工具使用 EM 算法填充 Excel 表格中缺失的值，并自动插入缺失的时间数据，使数据更加完整。支持自定义输入文件路径，并自动将修复后的数据写入原文件。

代码示例：

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

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

public class EM02 {

    public static void main(String[] args) {
        // 定义输入文件和输出文件的路径
        String inputFile = 'input.xlsx';
        try (Workbook workbook = WorkbookFactory.create(new FileInputStream(inputFile))) { // 使用工作簿工厂创建 Excel 工作簿
            Sheet sheet = workbook.getSheet('P1'); // 获取 P1 工作表
            DecimalFormat df = new DecimalFormat('#.##'); // 创建 Decimal 格式化器，用于保留两位小数
            // 创建新的工作表
            String newSheetName = 'P1-1';
            int sheetIndex = workbook.getSheetIndex(newSheetName);
            Sheet newDataSheet;
            if (sheetIndex >= 0) {
                workbook.removeSheetAt(sheetIndex);
            }
            newDataSheet = workbook.createSheet(newSheetName);
            // 复制原工作表的数据到新的工作表
            for (int i = 0; i <= sheet.getLastRowNum(); i++) {
                Row oldRow = sheet.getRow(i);
                Row newRow = newDataSheet.createRow(i);
                if (oldRow != null) {
                    for (int j = 0; j < oldRow.getLastCellNum(); j++) {
                        Cell oldCell = oldRow.getCell(j);
                        Cell newCell = newRow.createCell(j);
                        if (oldCell != null) {
                            if(oldCell.getCellType() == CellType.STRING){
                                newCell.setCellValue(oldCell.getStringCellValue());
                            } else if(oldCell.getCellType() == CellType.NUMERIC){
                                newCell.setCellValue(oldCell.getNumericCellValue());
                            } else if(oldCell.getCellType() == CellType.BOOLEAN){
                                newCell.setCellValue(oldCell.getBooleanCellValue());
                            } else if(oldCell.getCellType() == CellType.FORMULA){
                                newCell.setCellValue(oldCell.getCellFormula());
                            } else if(oldCell.getCellType() == CellType.ERROR){
                                newCell.setCellValue(oldCell.getErrorCellValue());
                            }
                            // 复制原单元格的样式到新单元格
                            CellStyle oldCellStyle = oldCell.getCellStyle();
                            CellStyle newCellStyle = workbook.createCellStyle();
                            newCellStyle.cloneStyleFrom(oldCellStyle);
                            newCell.setCellStyle(newCellStyle);
                        }
                    }
                }
            }
            // 对每一行进行处理
            for (int i = 1; i <= newDataSheet.getLastRowNum(); i++) {
                Row row = newDataSheet.getRow(i); // 获取行对象
                if (row != null) {
                    Cell cell = row.getCell(1); // 获取第二列单元格
                    if (cell == null || cell.getCellType() == CellType.BLANK) { // 判断单元格是否为空或者空白
                        double avg = calculateEM(newDataSheet, i); // 计算EM算法填充的值
                        if (avg > 0) { // 如果填充的值大于 0
                            cell = row.createCell(1); // 创建新的单元格
                            cell.setCellValue(Double.parseDouble(df.format(avg))); // 将填充的值填入单元格
                            // 设置单元格的数据类型为数值类型
                            CellStyle cellStyle = workbook.createCellStyle();
                            DataFormat dataFormat = workbook.createDataFormat();
                            cellStyle.setDataFormat(dataFormat.getFormat('0.00'));
                            cell.setCellStyle(cellStyle);
                        }
                    }
                }
            }

            // 插入缺失的时间和数据
            insertMissingData(newDataSheet);

            // 将工作簿写入输入文件
            FileOutputStream outputStream = new FileOutputStream(inputFile);
            workbook.write(outputStream);
            outputStream.close();
            System.out.println('Data filling completed.'); // 输出信息
        } catch (Exception e) { // 捕获异常
            e.printStackTrace();
        }
    }

    // 计算EM算法填充的值
    private static double calculateEM(Sheet sheet, int rowIndex) {
        Row row;
        double missingValue = 0; // 缺失值
        row = sheet.getRow(rowIndex); // 获取当前行对象
        if (row != null) {
            Cell cell = row.getCell(1); // 获取指定列的单元格
            if (cell != null && cell.getCellType() == CellType.NUMERIC) {
                missingValue = cell.getNumericCellValue(); // 缺失值为单元格中的值
            } else if (cell != null && cell.getCellType() == CellType.STRING) {
                try {
                    missingValue = Double.parseDouble(cell.getStringCellValue()); // 转换为数字类型
                } catch (NumberFormatException e) {
                    missingValue = 0; // 转换失败则缺失值为 0
                }
            }
        }

        List<Double> data = new ArrayList<>(); // 存储数据
        for (int i = 0; i <= sheet.getLastRowNum(); i++) { // 对每一行进行处理
            row = sheet.getRow(i); // 获取行对象
            if (row != null) {
                Cell cell = row.getCell(1); // 获取指定列的单元格
                if (cell != null && cell.getCellType() == CellType.NUMERIC) {
                    data.add(cell.getNumericCellValue()); // 将数据添加到列表中
                } else if (cell != null && cell.getCellType() == CellType.STRING) {
                    try {
                        double value = Double.parseDouble(cell.getStringCellValue()); // 转换为数字类型
                        data.add(value); // 将数据添加到列表中
                    } catch (NumberFormatException e) {
                        // 转换失败，忽略该值
                    }
                }
            }
        }

        if (data.size() > 0 && missingValue >= 0) { // 如果存在数据且缺失值大于 0
            double sum = 0; // 总和
            int count = 0; // 非缺失值个数
            for (double value : data) { // 遍历数据
                if (value >= 0) { // 非缺失值
                    sum += value; // 累加非缺失值
                    count++; // 非缺失值个数加一
                }
            }
            if (count > 0) { // 如果存在非缺失值
                double average = sum / count; // 计算平均值
                double variance = 0; // 方差
                for (double value : data) { // 遍历数据
                    if (value >= 0) { // 非缺失值
                        variance += Math.pow(value - average, 2); // 累加方差
                    }
                }
                variance /= count; // 计算方差
                double stdDeviation = Math.sqrt(variance); // 标准差
                return generateRandomValue(average, stdDeviation); // 返回填充的值
            }
        }
        return 0; // 如果不存在数据或缺失值小于等于0，返回 0
    }

    // 生成符合正态分布的随机值
    private static double generateRandomValue(double average, double stdDeviation) {
        double value;
        do {
            value = average + stdDeviation * Math.random(); // 生成随机值
        } while (value < 0); // 保证生成的值大于等于 0
        return value;
    }

    private static void insertMissingData(Sheet sheet) {
        Workbook workbook = sheet.getWorkbook(); // 获取工作簿对象
        List<Date> insertDates = new ArrayList<>(); // 保存插入的日期
        Sheet newDataSheet = workbook.createSheet('temp'); // 创建新的Sheet对象，用于保存插入的数据

        // 复制原有的数据到新的Sheet中
        for (int i = 0; i <= sheet.getLastRowNum(); i++) {
            Row oldRow = sheet.getRow(i);
            Row newRow = newDataSheet.createRow(i);
            if (oldRow != null) {
                for (int j = 0; j < oldRow.getLastCellNum(); j++) {
                    Cell oldCell = oldRow.getCell(j);
                    Cell newCell = newRow.createCell(j);
                    if (oldCell != null) {
                        if(oldCell.getCellType() == CellType.STRING){
                            newCell.setCellValue(oldCell.getStringCellValue());
                        } else if(oldCell.getCellType() == CellType.NUMERIC){
                            newCell.setCellValue(oldCell.getNumericCellValue());
                        } else if(oldCell.getCellType() == CellType.BOOLEAN){
                            newCell.setCellValue(oldCell.getBooleanCellValue());
                        } else if(oldCell.getCellType() == CellType.FORMULA){
                            newCell.setCellValue(oldCell.getCellFormula());
                        } else if(oldCell.getCellType() == CellType.ERROR){
                            newCell.setCellValue(oldCell.getErrorCellValue());
                        }
                        // 复制原单元格的样式到新单元格
                        CellStyle oldCellStyle = oldCell.getCellStyle();
                        CellStyle newCellStyle = workbook.createCellStyle();
                        newCellStyle.cloneStyleFrom(oldCellStyle);
                        newCell.setCellStyle(newCellStyle);
                    }
                }
            }
        }

        // 对每一行进行处理
        for (int i = 1; i <= newDataSheet.getLastRowNum(); i++) {
            Row currentRow = newDataSheet.getRow(i);
            Row nextRow = newDataSheet.getRow(i + 1);
            if (currentRow != null && nextRow != null) {
                Cell currentDateCell = currentRow.getCell(0);
                Cell nextDateCell = nextRow.getCell(0);
                if (currentDateCell != null && nextDateCell != null && currentDateCell.getCellType() == CellType.NUMERIC && nextDateCell.getCellType() == CellType.NUMERIC) {
                    Date currentDate = currentDateCell.getDateCellValue();
                    Date nextDate = nextDateCell.getDateCellValue();
                    long diff = nextDate.getTime() - currentDate.getTime();
                    long diffDays = diff / (24 * 60 * 60 * 1000);
                    if (diffDays > 10) {
                        int insertCount = (int) (diffDays / 10);
                        for (int j = 1; j <= insertCount; j++) {
                            // 移动原有数据
                            newDataSheet.shiftRows(i + 1, newDataSheet.getLastRowNum(), 1, true, true);

                            // 插入新行
                            Row newRow = newDataSheet.createRow(i + 1);
                            Cell newDateCell = newRow.createCell(0);
                            Cell newDataCell = newRow.createCell(1);
                            Date insertDate = new Date(currentDate.getTime() + (long) j * 10 * 24 * 60 * 60 * 1000);
                            newDateCell.setCellValue(insertDate);
                            insertDates.add(insertDate); // 添加插入的日期到列表中

                            // 设置新单元格的格式与原单元格一致
                            CellStyle oldDateCellStyle = currentDateCell.getCellStyle();
                            CellStyle newDateCellStyle = workbook.createCellStyle();
                            newDateCellStyle.cloneStyleFrom(oldDateCellStyle);
                            newDateCell.setCellStyle(newDateCellStyle);

                            // 设置新数据单元格的格式与原单
                            CellStyle oldDataCellStyle = currentRow.getCell(1).getCellStyle();
                            CellStyle newDataCellStyle = workbook.createCellStyle();
                            newDataCellStyle.cloneStyleFrom(oldDataCellStyle);
                            newDataCell.setCellStyle(newDataCellStyle);

                            newDataCell.setCellValue(calculateEM(newDataSheet, i + j));

                            // 将原有数据填充到新行的相应位置上
                            Cell currentDataCell = currentRow.getCell(1);
                            CellStyle currentDataCellStyle = currentDataCell.getCellStyle();
                            Cell newDataCell2 = newRow.createCell(1);
                            newDataCell2.setCellStyle(currentDataCellStyle);
                            newDataCell2.setCellValue(currentDataCell.getNumericCellValue());
                        }
                        i += insertCount;
                    }
                }
            }
        }

        // 对插入的日期进行排序
        Collections.sort(insertDates);

        // 更新对应行的日期单元格的值
        for (int i = 0; i < insertDates.size(); i++) {
            Row row = newDataSheet.getRow(i + 1);
            Cell dateCell = row.getCell(0);
            dateCell.setCellValue(insertDates.get(i));
        }

        // 将新的Sheet复制回原有的Sheet中
        workbook.removeSheetAt(workbook.getSheetIndex(sheet));
        workbook.setSheetName(workbook.getSheetIndex(newDataSheet), sheet.getSheetName());
        workbook.setSheetOrder(sheet.getSheetName(), workbook.getSheetIndex(newDataSheet));
    }
}

使用说明：

  1. 将代码保存为 EM02.java 文件。
  2. 确保已安装 Apache POI 库。
  3. 将需要修复的 Excel 文件命名为 input.xlsx，并将文件放置在与 EM02.java 文件相同的目录下。
  4. 运行 EM02.java 文件。
  5. 程序完成后，input.xlsx 文件将被更新，包含填充后的缺失值和插入的缺失时间数据。

错误解决：

org.apache.xmlbeans.impl.values.XmlValueDisconnectedException 错误通常出现在将新的 Sheet 对象复制回原有 Sheet 对象时，尝试获取新 Sheet 对象的名称时。解决方法如下：

  1. 在复制新的 Sheet 对象回原有的 Sheet 对象之前，先将新的 Sheet 对象从工作簿中移除，可以使用 workbook.removeSheetAt() 方法。
  2. 在复制新的 Sheet 对象回原有的 Sheet 对象之前，先为新的 Sheet 对象设置一个新的名称，可以使用 workbook.setSheetName() 方法。
  3. 在复制新的 Sheet 对象回原有的 Sheet 对象之后，使用 workbook.setSheetOrder() 方法将新的 Sheet 对象放置在原有 Sheet 对象的位置。

通过以上步骤，可以避免出现 XmlValueDisconnectedException 错误，并成功插入缺失的数据。

Excel 数据修复工具：使用 EM 算法填充缺失值并插入缺失时间数据

原文地址: https://www.cveoy.top/t/topic/fQh8 著作权归作者所有。请勿转载和采集!

免费AI点我,无需注册和登录