如何用Java+Apache POI按Lifecycle列值拆分Excel工作簿

前置依赖 #

如果使用Maven管理项目，需引入如下依赖，版本可根据项目实际情况调整，4.x和5.x版本均兼容下述代码：

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.1.2</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.1.2</version>
</dependency>

实现步骤 #

• 读取源Excel文件，定位到工作表A，查找Lifecycle列的索引位置
• 创建两个新工作簿，分别对应输出文件A_X.xlsx和A_Y.xlsx，先同步复制表头行和列宽
• 遍历源表所有数据行，读取Lifecycle列的值做判断，将行写入对应目标工作簿
• 输出两个新文件，关闭所有IO流和工作簿实例，避免资源泄露

完整实现代码 #

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;

public class ExcelSplitter {
    public static void main(String[] args) throws IOException {
        // 按需修改以下路径和参数
        String sourceFilePath = "你的本地路径/A.xlsx";
        String outputXPath = "A_X.xlsx";
        String outputYPath = "A_Y.xlsx";
        String filterColumnName = "Lifecycle";
        String filterValue = "X";

        // 读取源文件
        Workbook sourceWb = new XSSFWorkbook(new FileInputStream(sourceFilePath));
        Sheet sourceSheet = sourceWb.getSheet("A");
        if (sourceSheet == null) {
            sourceWb.close();
            throw new IllegalArgumentException("源文件中不存在名为A的工作表");
        }

        // 定位Lifecycle列索引
        Row headerRow = sourceSheet.getRow(0);
        int filterColIndex = -1;
        for (Cell cell : headerRow) {
            if (filterColumnName.equals(cell.getStringCellValue().trim())) {
                filterColIndex = cell.getColumnIndex();
                break;
            }
        }
        if (filterColIndex == -1) {
            sourceWb.close();
            throw new IllegalArgumentException("源表中未找到Lifecycle列");
        }

        // 初始化两个输出工作簿
        Workbook wbX = new XSSFWorkbook();
        Sheet sheetX = wbX.createSheet("A");
        Workbook wbY = new XSSFWorkbook();
        Sheet sheetY = wbY.createSheet("A");

        // 复制表头到两个新表
        copyRow(headerRow, sheetX.createRow(0), wbX);
        copyRow(headerRow, sheetY.createRow(0), wbY);

        // 遍历处理数据行
        int xRowNum = 1, yRowNum = 1;
        for (int i = 1; i <= sourceSheet.getLastRowNum(); i++) {
            Row currentRow = sourceSheet.getRow(i);
            if (currentRow == null) continue;

            // 读取Lifecycle列值
            Cell filterCell = currentRow.getCell(filterColIndex);
            String cellValue = "";
            if (filterCell != null) {
                filterCell.setCellType(CellType.STRING);
                cellValue = filterCell.getStringCellValue().trim();
            }

            // 写入对应工作簿
            if (filterValue.equals(cellValue)) {
                copyRow(currentRow, sheetX.createRow(xRowNum++), wbX);
            } else {
                copyRow(currentRow, sheetY.createRow(yRowNum++), wbY);
            }
        }

        // 输出文件并关闭资源
        try (FileOutputStream outX = new FileOutputStream(outputXPath);
             FileOutputStream outY = new FileOutputStream(outputYPath)) {
            wbX.write(outX);
            wbY.write(outY);
        } finally {
            wbX.close();
            wbY.close();
            sourceWb.close();
        }
    }

    /**
     * 复制行数据、样式和列宽
     */
    private static void copyRow(Row sourceRow, Row targetRow, Workbook targetWb) {
        int cellCount = sourceRow.getLastCellNum();
        // 复制单元格内容和样式
        for (int i = 0; i < cellCount; i++) {
            Cell sourceCell = sourceRow.getCell(i);
            Cell targetCell = targetRow.createCell(i);
            if (sourceCell == null) {
                targetCell.setCellValue("");
                continue;
            }
            // 复制值
            switch (sourceCell.getCellType()) {
                case STRING: targetCell.setCellValue(sourceCell.getStringCellValue()); break;
                case NUMERIC: targetCell.setCellValue(sourceCell.getNumericCellValue()); break;
                case BOOLEAN: targetCell.setCellValue(sourceCell.getBooleanCellValue()); break;
                case FORMULA: targetCell.setCellFormula(sourceCell.getCellFormula()); break;
                default: targetCell.setCellValue("");
            }
            // 复制样式
            CellStyle newStyle = targetWb.createCellStyle();
            newStyle.cloneStyleFrom(sourceCell.getCellStyle());
            targetCell.setCellStyle(newStyle);
        }
        // 复制列宽
        for (int i = 0; i < cellCount; i++) {
            targetRow.getSheet().setColumnWidth(i, sourceRow.getSheet().getColumnWidth(i));
        }
    }
}

注意事项 #

• 如果你的源文件是xls格式（Excel 2003及更早版本），将代码中的XSSFWorkbook替换为HSSFWorkbook即可
• 如果Lifecycle列是公式计算结果或者枚举类型，可根据实际场景调整单元格值的读取逻辑
• 700条数据量极小，无需优化内存占用，上述代码可直接正常运行

内容的提问来源于stack exchange，提问作者Sonali Sarjana