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 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 rows = new ArrayList(); List 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() = 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 rows; final List mergedRegions; final Workbook sourceWorkbook; public TemplateData(List rows, List mergedRegions, Workbook sourceWorkbook) { this.rows = rows; this.mergedRegions = mergedRegions; this.sourceWorkbook = sourceWorkbook; } } // 行数据容器 static class RowData { private final List 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()); } }}

批量执行即可。

执行效果为下：