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());}}}批量执行即可。
执行效果为下：
