Java Apache POI Excel 处理: 填充空白单元格并输出

import java.io.File; import java.io.FileOutputStream; import java.io.IOException; import java.util.Date;

import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellType; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.ss.usermodel.WorkbookFactory; import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class test07 {

public static void main(String[] args) throws IOException {
    // 创建输入Workbook对象
    Workbook inputWorkbook = WorkbookFactory.create(new File('input.xlsx'));

    // 获取第一个Sheet
    Sheet inputSheet = inputWorkbook.getSheetAt(0);

    // 获取日期列和数据列的列号
    int dateColumn = -1;
    int dataColumn = -1;
    Row firstRow = inputSheet.getRow(0);
    for (Cell cell : firstRow) {
        String value = cell.getStringCellValue();
        if (value.equals('日期')) {
            dateColumn = cell.getColumnIndex();
        } else if (value.equals('数据')) {
            dataColumn = cell.getColumnIndex();
        }
    }

    // 判断日期列和数据列的列号是否已被正确赋值
    if (dateColumn == -1 || dataColumn == -1) {
        System.out.println('输入Excel格式不正确！');
        return;
    }

    // 遍历每一行，检测数据列中的空白并计算平均值填充空白单元格
    for (Row inputRow : inputSheet) {
        Cell dataCell = inputRow.getCell(dataColumn);
        if (dataCell != null && dataCell.getCellType() == CellType.BLANK) {
            // 获取上下单元格的数据
            Cell upperCell = inputRow.getCell(dataColumn - 1);
            Cell lowerCell = inputRow.getCell(dataColumn + 1);
            if (upperCell != null && lowerCell != null) {
                double upperValue = upperCell.getNumericCellValue();
                double lowerValue = lowerCell.getNumericCellValue();

                // 计算平均值
                double averageValue = (upperValue + lowerValue) / 2;

                // 填充空白单元格
                dataCell.setCellValue(averageValue);
            }
        }
    }

    // 创建输出Workbook对象
    Workbook outputWorkbook = new XSSFWorkbook();

    // 创建输出Sheet对象
    Sheet outputSheet = outputWorkbook.createSheet('output');

    // 遍历输入Sheet中的每一行数据，并将处理后的数据写入输出Sheet中
    for (Row inputRow : inputSheet) {
        if (inputRow.getCell(dateColumn) != null && inputRow.getCell(dataColumn) != null) {
            Row outputRow = outputSheet.createRow(inputRow.getRowNum());
            for (Cell inputCell : inputRow) {
                Cell outputCell = outputRow.createCell(inputCell.getColumnIndex());
                if (inputCell.getCellType() == CellType.STRING) {
                    String value = inputCell.getStringCellValue();
                    outputCell.setCellValue(value);
                } else if (inputCell.getCellType() == CellType.NUMERIC) {
                    double value = inputCell.getNumericCellValue();
                    outputCell.setCellValue(value);
                } else if (inputCell.getCellType() == CellType.BOOLEAN) {
                    boolean value = inputCell.getBooleanCellValue();
                    outputCell.setCellValue(value);
                } else if (inputCell.getCellType() == CellType.BLANK) {
                    outputCell.setBlank();
                } else {
                    // 其他类型处理
                }
            }
        }
    }

    // 将处理后的数据写入输出Excel文件中
    FileOutputStream outputStream = new FileOutputStream('output.xlsx');
    outputWorkbook.write(outputStream);
    outputStream.close();

    // 关闭输入Workbook对象
    inputWorkbook.close();
}

}