Execel文档批量替换标签实现方案

问题背景

需求：俺现网班级作为维度，批量导出每个班级学员的数据，excel的个数在1k左右，每一张表的人数在90左右。导出总耗时在10小时左右。

代码编写完成并导出现网数据后，发现导出的标题错了。

解决方案

1.通过修改代码，重新导出。（耗时在10h）

2.通过java 代码实现excel标签替换。（耗时在10分钟）

代码实现

依赖

    implementation "org.apache.poi:poi:5.2.3"
    implementation "org.apache.poi:poi-ooxml:5.2.3"

代码 

其中当文件中只有旧的标签且其他数据不存在时，会直接报错，需要我们手动处理即可。

template为我们新模板的样式文件，sourseDir为旧excel的文件夹。outputDir为新文件的生成位置。

根据样式的实际行数修改readTemplateData中的循环行数。removeRows方法中设置旧excel中标签的起止行数。（索引从0开始）

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import java.io.*;
import java.util.ArrayList;
import java.util.List;
public class ExecelUtios {
    public static void main(String[] args) {
        String templatePath = "C:\\Users\\fjhb\\Desktop\\test111\\template.xlsx";
        String sourceDir = "C:\\Users\\fjhb\\Desktop\\教育学院\\4考勤\\2023";
        String outputDir = "C:\\Users\\fjhb\\Desktop\\教育学院\\4考勤最总\\2023\\";

        try {
            // 读取模板数据（内容和样式）
            TemplateData templateData = readTemplateData(templatePath);
            List<String> errorFileName = new ArrayList<>();
            File dir = new File(sourceDir);
            File[] files = dir.listFiles((d, name) ->
                    name.toLowerCase().endsWith(".xls") || name.toLowerCase().endsWith(".xlsx"));

            if (files == null || files.length == 0) {
                System.out.println("目录中没有Excel文件");
                return;
            }

            System.out.println("开始处理 " + files.length + " 个文件...");

            for (File file : files) {
                try {
                    processFile(file, templateData, outputDir);
                    System.out.println("✓ 已处理: " + file.getName());
                } catch (Exception e) {
                    System.err.println("✗ 处理失败: " + file.getName() + " - " + e.getMessage());
                    errorFileName.add(file.getName());
                }
            }

            System.out.println("处理完成! 成功处理 " + files.length + " 个文件");
            if(!errorFileName.isEmpty()) {
                System.out.println("执行错误，需要手动处理的文件为下（因为文件没有内容只有标题）:");
                for (String s : errorFileName) {
                    System.err.println(s);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    // 读取模板数据（包含样式、内容和合并单元格）
    private static TemplateData readTemplateData(String templatePath) throws IOException {
        try (InputStream is = new FileInputStream(templatePath);
             Workbook templateWorkbook = WorkbookFactory.create(is)) {

            Sheet sheet = templateWorkbook.getSheetAt(0);
            List<RowData> rows = new ArrayList<>();
            List<CellRangeAddress> mergedRegions = new ArrayList<>();

            // 读取前三行
            for (int i = 0; i < 3 && i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (row != null) {
                    rows.add(new RowData(row, templateWorkbook));
                }
            }

            // 读取前三行的合并单元格区域
            for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
                CellRangeAddress mergedRegion = sheet.getMergedRegion(i);
                if (mergedRegion.getLastRow() < 3) { // 只处理前三行的合并
                    mergedRegions.add(mergedRegion);
                }
            }

            return new TemplateData(rows, mergedRegions, templateWorkbook);
        }
    }

    // 处理单个文件
    private static void processFile(File file, TemplateData templateData, String outputDir)
            throws IOException {

        try (InputStream is = new FileInputStream(file);
             Workbook workbook = WorkbookFactory.create(is)) {

            Sheet sheet = workbook.getSheetAt(0);

            // 1. 删除原有的合并区域（前三行）
            removeMergedRegionsInRange(sheet, 0, 2);

            // 2. 删除原有的前两行
            removeRows(sheet, 0, 1);

            // 3. 插入模板行（带样式）
            insertTemplateRows(sheet, templateData, workbook);

            // 4. 确保输出目录存在
            File outDir = new File(outputDir);
            if (!outDir.exists()) outDir.mkdirs();

            // 5. 保存文件
            String outputPath = outputDir + File.separator + file.getName();
            try (OutputStream os = new FileOutputStream(outputPath)) {
                workbook.write(os);
            }
        }
    }

    // 删除指定行范围内的合并区域
    private static void removeMergedRegionsInRange(Sheet sheet, int startRow, int endRow) {
        for (int i = sheet.getNumMergedRegions() - 1; i >= 0; i--) {
            CellRangeAddress mergedRegion = sheet.getMergedRegion(i);
            if (mergedRegion.getFirstRow() >= startRow && mergedRegion.getLastRow() <= endRow) {
                sheet.removeMergedRegion(i);
            }
        }
    }

    // 删除指定行范围
    private static void removeRows(Sheet sheet, int startRow, int endRow) {
        // 删除行内容
        for (int i = startRow; i <= endRow; i++) {
            Row row = sheet.getRow(i);
            if (row != null) {
                sheet.removeRow(row);
            }
        }

        // 移动行
        if (endRow < sheet.getLastRowNum()) {
            sheet.shiftRows(endRow + 1, sheet.getLastRowNum(), -(endRow - startRow + 1));
        }
    }

    // 插入模板行（带样式）
    private static void insertTemplateRows(Sheet sheet, TemplateData templateData, Workbook targetWorkbook) {
        if (templateData.rows.isEmpty()) return;

        // 移动现有行
        sheet.shiftRows(0, sheet.getLastRowNum(), templateData.rows.size(), true, true);

        // 创建新行并应用模板
        for (int i = 0; i < templateData.rows.size(); i++) {
            Row newRow = sheet.createRow(i);
            templateData.rows.get(i).applyTo(newRow, targetWorkbook, templateData.sourceWorkbook);
        }

        // 添加合并区域
        for (CellRangeAddress mergedRegion : templateData.mergedRegions) {
            sheet.addMergedRegion(mergedRegion);
        }
    }

    // 模板数据容器
    static class TemplateData {
        final List<RowData> rows;
        final List<CellRangeAddress> mergedRegions;
        final Workbook sourceWorkbook;

        public TemplateData(List<RowData> rows, List<CellRangeAddress> mergedRegions, Workbook sourceWorkbook) {
            this.rows = rows;
            this.mergedRegions = mergedRegions;
            this.sourceWorkbook = sourceWorkbook;
        }
    }

    // 行数据容器
    static class RowData {
        private final List<CellData> cells = new ArrayList<>();

        public RowData(Row sourceRow, Workbook sourceWorkbook) {
            if (sourceRow != null) {
                for (Cell cell : sourceRow) {
                    cells.add(new CellData(cell, sourceWorkbook));
                }
            }
        }

        public void applyTo(Row targetRow, Workbook targetWorkbook, Workbook sourceWorkbook) {
            for (CellData cellData : cells) {
                cellData.applyTo(targetRow, targetWorkbook, sourceWorkbook);
            }
        }
    }

    // 单元格数据容器（包含样式）
    static class CellData {
        private final int columnIndex;
        private final CellStyle sourceStyle;
        private final Object value;
        private final CellType cellType;
        private final Workbook sourceWorkbook;

        public CellData(Cell sourceCell, Workbook sourceWorkbook) {
            this.columnIndex = sourceCell.getColumnIndex();
            this.sourceStyle = sourceCell.getCellStyle();
            this.sourceWorkbook = sourceWorkbook;
            this.cellType = sourceCell.getCellType();

            switch (cellType) {
                case STRING:
                    value = sourceCell.getStringCellValue();
                    break;
                case NUMERIC:
                    value = sourceCell.getNumericCellValue();
                    break;
                case BOOLEAN:
                    value = sourceCell.getBooleanCellValue();
                    break;
                case FORMULA:
                    value = sourceCell.getCellFormula();
                    break;
                case BLANK:
                    value = "";
                    break;
                default:
                    value = null;
            }
        }

        public void applyTo(Row targetRow, Workbook targetWorkbook, Workbook sourceWorkbook) {
            Cell newCell = targetRow.createCell(columnIndex);

            // 复制单元格值
            setCellValue(newCell, value, cellType);

            // 复制单元格样式（深度复制）
            if (sourceStyle != null) {
                CellStyle newStyle = targetWorkbook.createCellStyle();
                copyCellStyleDeep(newStyle, sourceStyle, targetWorkbook, sourceWorkbook);
                newCell.setCellStyle(newStyle);
            }
        }

        private void setCellValue(Cell cell, Object value, CellType cellType) {
            if (value == null) return;

            switch (cellType) {
                case STRING:
                    cell.setCellValue((String) value);
                    break;
                case NUMERIC:
                    cell.setCellValue((Double) value);
                    break;
                case BOOLEAN:
                    cell.setCellValue((Boolean) value);
                    break;
                case FORMULA:
                    cell.setCellFormula((String) value);
                    break;
                case BLANK:
                    cell.setBlank();
                    break;
                default:
            }
        }

        // 深度复制单元格样式（支持.xls和.xlsx）
        private void copyCellStyleDeep(CellStyle newStyle, CellStyle sourceStyle,
                                       Workbook targetWorkbook, Workbook sourceWorkbook) {

            // 复制基本样式属性
            newStyle.setAlignment(sourceStyle.getAlignment());
            newStyle.setVerticalAlignment(sourceStyle.getVerticalAlignment());
            newStyle.setBorderTop(sourceStyle.getBorderTop());
            newStyle.setBorderBottom(sourceStyle.getBorderBottom());
            newStyle.setBorderLeft(sourceStyle.getBorderLeft());
            newStyle.setBorderRight(sourceStyle.getBorderRight());
            newStyle.setTopBorderColor(sourceStyle.getTopBorderColor());
            newStyle.setBottomBorderColor(sourceStyle.getBottomBorderColor());
            newStyle.setLeftBorderColor(sourceStyle.getLeftBorderColor());
            newStyle.setRightBorderColor(sourceStyle.getRightBorderColor());
            newStyle.setFillPattern(sourceStyle.getFillPattern());

            // 复制背景色
            if (sourceStyle.getFillBackgroundColor() > 0) {
                newStyle.setFillBackgroundColor(sourceStyle.getFillBackgroundColor());
            }

            // 复制前景色
            if (sourceStyle.getFillForegroundColor() > 0) {
                newStyle.setFillForegroundColor(sourceStyle.getFillForegroundColor());
            }

            // 复制其他属性
            newStyle.setDataFormat(sourceStyle.getDataFormat());
            newStyle.setWrapText(sourceStyle.getWrapText());
            newStyle.setIndention(sourceStyle.getIndention());
            newStyle.setRotation(sourceStyle.getRotation());
            newStyle.setHidden(sourceStyle.getHidden());
            newStyle.setLocked(sourceStyle.getLocked());
            newStyle.setShrinkToFit(sourceStyle.getShrinkToFit());

            // 复制字体
            Font sourceFont = sourceWorkbook.getFontAt(sourceStyle.getFontIndex());
            Font newFont = targetWorkbook.createFont();
            copyFontDeep(newFont, sourceFont, targetWorkbook, sourceWorkbook);
            newStyle.setFont(newFont);
        }

        // 深度复制字体样式
        private void copyFontDeep(Font newFont, Font sourceFont,
                                  Workbook targetWorkbook, Workbook sourceWorkbook) {

            newFont.setBold(sourceFont.getBold());
            newFont.setColor(sourceFont.getColor());
            newFont.setFontHeight(sourceFont.getFontHeight());
            newFont.setFontHeightInPoints(sourceFont.getFontHeightInPoints());
            newFont.setFontName(sourceFont.getFontName());
            newFont.setItalic(sourceFont.getItalic());
            newFont.setStrikeout(sourceFont.getStrikeout());
            newFont.setTypeOffset(sourceFont.getTypeOffset());
            newFont.setUnderline(sourceFont.getUnderline());
            newFont.setCharSet(sourceFont.getCharSet());
        }
    }

}

批量执行即可。

执行效果为下：