Excel 数据填充：使用 EM 算法和日期插入处理缺失值

package Data_Recovery;/n/nimport org.apache.poi.ss.usermodel.*;/n/nimport java.io.FileInputStream;/nimport java.io.FileOutputStream;/nimport java.text.DecimalFormat;/nimport java.text.ParseException;/nimport java.text.SimpleDateFormat;/nimport java.util.ArrayList;/nimport java.util.Date;/nimport java.util.List;/n/npublic class EM03 {/n/n public static void main(String[] args) {/n // 定义输入文件和输出文件的路径/n String inputFile = /'input-2.xlsx/';/n try (Workbook workbook = WorkbookFactory.create(new FileInputStream(inputFile))) { // 使用工作簿工厂创建 Excel 工作簿/n Sheet sheet = workbook.getSheet(/'P1/'); // 获取 P1 工作表/n DecimalFormat df = new DecimalFormat(/'#.##/'); // 创建 Decimal 格式化器，用于保留两位小数/n // 创建新的工作表/n String newSheetName = /'P1-1/';/n int sheetIndex = workbook.getSheetIndex(newSheetName);/n Sheet newDataSheet;/n if (sheetIndex >= 0) {/n workbook.removeSheetAt(sheetIndex);/n }/n newDataSheet = workbook.createSheet(newSheetName);/n // 复制原工作表的数据到新的工作表/n for (int i = 0; i <= sheet.getLastRowNum(); i++) {/n Row oldRow = sheet.getRow(i);/n Row newRow = newDataSheet.createRow(i);/n if (oldRow != null) {/n for (int j = 0; j < oldRow.getLastCellNum(); j++) {/n Cell oldCell = oldRow.getCell(j);/n Cell newCell = newRow.createCell(j);/n if (oldCell != null) {/n if(oldCell.getCellType() == CellType.STRING){/n newCell.setCellValue(oldCell.getStringCellValue());/n } else if(oldCell.getCellType() == CellType.NUMERIC){/n newCell.setCellValue(oldCell.getNumericCellValue());/n } else if(oldCell.getCellType() == CellType.BOOLEAN){/n newCell.setCellValue(oldCell.getBooleanCellValue());/n } else if(oldCell.getCellType() == CellType.FORMULA){/n newCell.setCellValue(oldCell.getCellFormula());/n } else if(oldCell.getCellType() == CellType.ERROR){/n newCell.setCellValue(oldCell.getErrorCellValue());/n }/n // 复制原单元格的样式到新单元格/n CellStyle oldCellStyle = oldCell.getCellStyle();/n CellStyle newCellStyle = workbook.createCellStyle();/n newCellStyle.cloneStyleFrom(oldCellStyle);/n newCell.setCellStyle(newCellStyle);/n }/n }/n }/n }/n // 插入缺失的时间/n insertMissingDates(newDataSheet);/n // 对每一行进行处理/n for (int i = 1; i <= newDataSheet.getLastRowNum(); i++) {/n Row row = newDataSheet.getRow(i); // 获取行对象/n if (row != null) {/n Cell cell = row.getCell(1); // 获取第二列单元格/n if (cell == null || cell.getCellType() == CellType.BLANK) { // 判断单元格是否为空或者空白/n double avg = calculateEM(newDataSheet, i, 1); // 计算EM算法填充的值/n if (avg > 0) { // 如果填充的值大于 0/n cell = row.createCell(1); // 创建新的单元格/n cell.setCellValue(Double.parseDouble(df.format(avg))); // 将填充的值填入单元格/n // 设置单元格的数据类型为数值类型/n CellStyle cellStyle = workbook.createCellStyle();/n DataFormat dataFormat = workbook.createDataFormat();/n cellStyle.setDataFormat(dataFormat.getFormat(/'0.00/'));/n cell.setCellStyle(cellStyle);/n }/n }/n }/n }/n // 将工作簿写入输入文件/n FileOutputStream outputStream = new FileOutputStream(inputFile);/n workbook.write(outputStream);/n outputStream.close();/n System.out.println(/'Data filling completed./'); // 输出信息/n } catch (Exception e) { // 捕获异常/n e.printStackTrace();/n }/n }/n/n // 计算EM算法填充的值/n private static double calculateEM(Sheet sheet, int rowIndex, int columnIndex) {/n Row row;/n double missingValue = 0; // 缺失值/n row = sheet.getRow(rowIndex); // 获取当前行对象/n if (row != null) {/n Cell cell = row.getCell(columnIndex); // 获取指定列的单元格/n if (cell != null && cell.getCellType() == CellType.NUMERIC) {/n missingValue = cell.getNumericCellValue(); // 缺失值为单元格中的值/n } else if (cell != null && cell.getCellType() == CellType.STRING) {/n try {/n missingValue = Double.parseDouble(cell.getStringCellValue()); // 转换为数字类型/n } catch (NumberFormatException e) {/n missingValue = 0; // 转换失败则缺失值为 0/n }/n }/n }/n/n List data = new ArrayList<>(); // 存储数据/n for (int i = 0; i <= sheet.getLastRowNum(); i++) { // 对每一行进行处理/n row = sheet.getRow(i); // 获取行对象/n if (row != null) {/n Cell cell = row.getCell(columnIndex); // 获取指定列的单元格/n if (cell != null && cell.getCellType() == CellType.NUMERIC) {/n data.add(cell.getNumericCellValue()); // 将数据添加到列表中/n } else if (cell != null && cell.getCellType() == CellType.STRING) {/n try {/n double value = Double.parseDouble(cell.getStringCellValue()); // 转换为数字类型/n data.add(value); // 将数据添加到列表中/n } catch (NumberFormatException e) {/n // 转换失败，忽略该值/n }/n }/n }/n }/n/n if (data.size() > 0 && missingValue >= 0) { // 如果存在数据且缺失值大于 0/n double sum = 0; // 总和/n int count = 0; // 非缺失值个数/n for (double value : data) { // 遍历数据/n if (value >= 0) { // 非缺失值/n sum += value; // 累加非缺失值/n count++; // 非缺失值个数加一/n }/n }/n if (count > 0) { // 如果存在非缺失值/n double average = sum / count; // 计算平均值/n double variance = 0; // 方差/n for (double value : data) { // 遍历数据/n if (value >= 0) { // 非缺失值/n variance += Math.pow(value - average, 2); // 累加方差/n }/n }/n variance /= count; // 计算方差/n double stdDeviation = Math.sqrt(variance); // 标准差/n double filledValue = generateRandomValue(average, stdDeviation); // 生成符合正态分布的随机值/n return filledValue; // 返回填充的值/n }/n }/n return 0; // 如果不存在数据或缺失值小于等于0，返回 0/n }/n/n // 生成符合正态分布的随机值/n private static double generateRandomValue(double average, double stdDeviation) {/n double value;/n do {/n value = average + stdDeviation * Math.random(); // 生成随机值/n } while (value < 0); // 保证生成的值大于等于 0/n return value;/n }/n/n // 解析日期/n private static Date parseDate(String dateString) {/n SimpleDateFormat dateFormat = new SimpleDateFormat(/'yyyy/MM/dd HH:mm/'); // 创建日期格式化器/n try {/n return dateFormat.parse(dateString); // 解析日期/n } catch (ParseException e) { // 捕获异常/n e.printStackTrace();/n return null; // 返回 null/n }/n }/n /n // 插入缺失的时间/n private static void insertMissingDates(Sheet sheet) {/n for (int i = 1; i < sheet.getLastRowNum(); i++) {/n Row currentRow = sheet.getRow(i);/n Row nextRow = sheet.getRow(i + 1);/n if (currentRow != null && nextRow != null) {/n Cell currentCell = currentRow.getCell(0);/n Cell nextCell = nextRow.getCell(0);/n if (currentCell != null && nextCell != null) {/n Date currentDate = parseDate(currentCell.getStringCellValue());/n Date nextDate = parseDate(nextCell.getStringCellValue());/n long diff = nextDate.getTime() - currentDate.getTime();/n if (diff > 10 * 24 * 60 * 60 * 1000) {/n int insertCount = (int) (diff / (10 * 24 * 60 * 60 * 1000));/n for (int j = 1; j <= insertCount; j++) {/n Row newRow = sheet.createRow(i + j);/n Cell newCell = newRow.createCell(0);/n newCell.setCellValue(formatDate(new Date(currentDate.getTime() + j * 10 * 24 * 60 * 60 * 1000)));/n }/n }/n }/n }/n }/n }/n /n // 格式化日期/n private static String formatDate(Date date) {/n SimpleDateFormat dateFormat = new SimpleDateFormat(/'yyyy/MM/dd HH:mm/');/n return dateFormat.format(date);/n }/