🚀 Excel 模板生成工具：ExcelTemplateUtil
ExcelTemplateUtil 是一个基于 Apache POI 的 Java 工具类，用于从 Excel 模板生成动态 Excel 文件。它支持占位符替换（如 ${variable}）和循环处理（如 <jx:foreach>），适合批量生成报表或数据导出场景。以下是功能说明和代码解析！📜
📝 功能概述
- 占位符替换：将 Excel 单元格中的
${variable}替换为实际数据。 - 循环处理：通过
<jx:foreach>标签动态生成多行数据。 - 格式支持：自动识别日期、数字（包括百分比）、科学计数法等格式。
- 日志记录：使用 SLF4J 记录操作日志，便于调试。
使用场景
- 生成动态报表（如财务、销售数据）。
- 批量导出用户数据到 Excel。
- 基于模板生成复杂格式的 Excel 文件。
🔧 代码解析
核心类：ExcelTemplateUtil
- 依赖：Apache POI（处理 Excel）、SLF4J（日志）。
- 主要方法：
generateExcel：读取模板，处理数据，生成输出文件。processSheet：处理每个 Sheet，包含占位符替换和循环逻辑。replacePlaceholders：替换${variable}占位符。applyCellValue：根据单元格格式设置值（字符串、数字、日期）。
关键逻辑
- 占位符处理：
- 使用正则表达式
Pattern.compile("\\$\\{([^}]+)\\}")匹配${variable}。 - 支持嵌套字段（如
${user.name}）和循环索引（如${status.index}）。
- 使用正则表达式
- 循环处理：
- 解析
<jx:foreach items="${list}" var="item" varstatus="status">标签。 - 动态复制模板行，填充列表数据。
- 解析
- 单元格格式：
- 自动检测单元格格式（如日期、百分比）。
- 科学计数法和数字通过正则匹配处理。
示例代码
以下是完整代码（已精简注释以节省空间）：
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
public class ExcelTemplateUtil {
private static final Logger logger = LoggerFactory.getLogger(ExcelTemplateUtil.class);
private static final Pattern PLACEHOLDER_PATTERN = Pattern.compile("\\$\\{([^}]+)\\}");
private static final Pattern FOREACH_PATTERN = Pattern.compile("<jx:foreach\\s+([^>]*)>", Pattern.CASE_INSENSITIVE);
private static final Pattern ATTRIBUTE_PATTERN = Pattern.compile("\\s*(\\w+)=\"([^\"]*)\"", Pattern.CASE_INSENSITIVE);
private static final Pattern INDEX_EXPR_PATTERN = Pattern.compile("^([\\w]+)\\.index(?:([+\\-*/])(\\d+))?$", Pattern.CASE_INSENSITIVE);
public static void generateExcel(String templatePath, String outputPath, Map<String, Object> data) throws IOException {
try (FileInputStream fis = new FileInputStream(templatePath);
Workbook workbook = new XSSFWorkbook(fis);
FileOutputStream fos = new FileOutputStream(outputPath)) {
for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
processSheet(workbook.getSheetAt(i), data);
}
workbook.write(fos);
}
}
private static void processSheet(Sheet sheet, Map<String, Object> data) {
int rowIndex = 0;
while (rowIndex <= sheet.getLastRowNum()) {
Row row = sheet.getRow(rowIndex);
if (row == null) {
rowIndex++;
continue;
}
String foreachStart = findForeachStart(row);
if (foreachStart != null) {
Matcher matcher = FOREACH_PATTERN.matcher(foreachStart);
if (matcher.find()) {
Map<String, String> attrMap = parseAttributes(matcher.group(1));
String itemsName = attrMap.get("items").replace("${", "").replace("}", "");
String varName = attrMap.get("var");
String statusName = attrMap.get("varstatus");
Object listData = data.get(itemsName);
if (listData instanceof List) {
List<?> list = (List<?>) listData;
int endRowIndex = findForeachEnd(sheet, rowIndex);
if (endRowIndex == -1 || endRowIndex <= rowIndex) {
rowIndex++;
continue;
}
int rowsToCopy = endRowIndex - rowIndex - 1;
if (list.isEmpty()) {
sheet.shiftRows(endRowIndex + 1, sheet.getLastRowNum(), -(endRowIndex - rowIndex + 1));
rowIndex++;
continue;
}
List<TemplateRow> templateRows = new ArrayList<>();
for (int i = 0; i < rowsToCopy; i++) {
Row sourceRow = sheet.getRow(rowIndex + 1 + i);
templateRows.add(new TemplateRow(sourceRow != null ? sourceRow : sheet.createRow(rowIndex + 1 + i)));
}
sheet.shiftRows(rowIndex, sheet.getLastRowNum(), -(endRowIndex - rowIndex + 1));
int insertRowIndex = rowIndex;
int currentIndex = 0;
for (Object item : list) {
for (int i = 0; i < rowsToCopy; i++) {
Row newRow = sheet.createRow(insertRowIndex + i);
copyRowFromTemplate(newRow, templateRows.get(i), item, varName, statusName, currentIndex);
}
insertRowIndex += rowsToCopy;
currentIndex++;
}
rowIndex = insertRowIndex;
} else {
rowIndex++;
}
} else {
rowIndex++;
}
} else {
for (Cell cell : row) {
if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
String cellValue = cell.getStringCellValue();
if (cellValue != null && cellValue.contains("${")) {
String replacedValue = replacePlaceholders(cellValue, data);
applyCellValue(cell, replacedValue, cell.getCellStyle());
}
}
}
rowIndex++;
}
}
}
private static Map<String, String> parseAttributes(String attributes) {
Map<String, String> attrMap = new HashMap<>();
Matcher matcher = ATTRIBUTE_PATTERN.matcher(attributes);
while (matcher.find()) {
attrMap.put(matcher.group(1).toLowerCase(), matcher.group(2));
}
return attrMap;
}
private static String findForeachStart(Row row) {
for (Cell cell : row) {
if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
String cellValue = cell.getStringCellValue();
if (cellValue != null && cellValue.toLowerCase().contains("<jx:foreach")) {
return cellValue;
}
}
}
return null;
}
private static int findForeachEnd(Sheet sheet, int startRowIndex) {
for (int i = startRowIndex + 1; i <= sheet.getLastRowNum(); i++) {
Row row = sheet.getRow(i);
if (row != null) {
for (Cell cell : row) {
if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
String cellValue = cell.getStringCellValue();
if (cellValue != null && cellValue.toLowerCase().contains("</jx:foreach>")) {
return i;
}
}
}
}
}
return -1;
}
private static class TemplateRow {
private final List<String> values = new ArrayList<>();
private final List<CellStyle> styles = new ArrayList<>();
TemplateRow(Row row) {
int lastCellNum = row.getLastCellNum();
for (int col = 0; col < lastCellNum; col++) {
Cell cell = row.getCell(col, Row.RETURN_BLANK_AS_NULL);
values.add(cell != null && cell.getCellType() == Cell.CELL_TYPE_STRING ? cell.getStringCellValue() : "");
styles.add(cell != null ? cell.getCellStyle() : null);
}
}
String getValue(int col) {
return col < values.size() ? values.get(col) : "";
}
CellStyle getStyle(int col) {
return col < styles.size() ? styles.get(col) : null;
}
int getCellCount() {
return values.size();
}
}
private static void copyRowFromTemplate(Row targetRow, TemplateRow templateRow, Object item, String varName, String statusName, int index) {
for (int col = 0; col < templateRow.getCellCount(); col++) {
Cell targetCell = targetRow.createCell(col);
String cellValue = templateRow.getValue(col);
CellStyle style = templateRow.getStyle(col);
if (style != null) {
targetCell.setCellStyle(style);
}
String replacedValue = (cellValue != null && cellValue.contains("${"))
? replacePlaceholdersWithVar(cellValue, item, varName, statusName, index)
: cellValue;
applyCellValue(targetCell, replacedValue, style);
}
}
private static void applyCellValue(Cell cell, String value, CellStyle style) {
if (value == null || style == null) {
cell.setCellValue(value);
return;
}
short dataFormat = style.getDataFormat();
String formatString = style.getDataFormatString();
if (dataFormat == 0 && (formatString == null || formatString.equals("General"))) {
if (isDecimal(value) || isScientific(value)) {
cell.setCellValue(new BigDecimal(value).doubleValue());
} else {
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellValue(value);
}
return;
}
if (isDateFormat(dataFormat, formatString)) {
try {
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
cell.setCellValue(new Date(value));
} catch (Exception e) {
cell.setCellValue(value);
}
} else if (isNumericFormat(dataFormat, formatString)) {
try {
double numericValue = Double.parseDouble(value);
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
cell.setCellValue(formatString != null && formatString.contains("%") ? numericValue / 100 : numericValue);
} catch (NumberFormatException e) {
cell.setCellValue(value);
}
} else {
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellValue(value);
}
}
private static boolean isDateFormat(short dataFormat, String formatString) {
return dataFormat == 14 || dataFormat == 22 || (formatString != null && formatString.contains("yy"));
}
private static boolean isNumericFormat(short dataFormat, String formatString) {
return dataFormat == 0 || dataFormat == 2 || dataFormat == 10 || dataFormat == 177
|| (formatString != null && (formatString.contains("0") || formatString.contains("%")));
}
private static String replacePlaceholdersWithVar(String text, Object item, String varName, String statusName, int index) {
Matcher matcher = PLACEHOLDER_PATTERN.matcher(text);
StringBuffer result = new StringBuffer();
while (matcher.find()) {
String placeholder = matcher.group(1);
if (placeholder.startsWith(varName + ".")) {
String field = placeholder.substring(varName.length() + 1);
String replacement = getValueFromData(field, item);
matcher.appendReplacement(result, Matcher.quoteReplacement(replacement != null ? replacement : ""));
} else if (statusName != null && INDEX_EXPR_PATTERN.matcher(placeholder).matches()) {
Matcher exprMatcher = INDEX_EXPR_PATTERN.matcher(placeholder);
if (exprMatcher.matches() && exprMatcher.group(1).equals(statusName)) {
String operator = exprMatcher.group(2);
String operand = exprMatcher.group(3);
String replacement = operator == null ? String.valueOf(index)
: computeIndex(index, operator, operand);
matcher.appendReplacement(result, Matcher.quoteReplacement(replacement));
} else {
matcher.appendReplacement(result, Matcher.quoteReplacement(matcher.group(0)));
}
} else {
matcher.appendReplacement(result, Matcher.quoteReplacement(matcher.group(0)));
}
}
matcher.appendTail(result);
return result.toString();
}
private static String computeIndex(int index, String operator, String operand) {
try {
int operandValue = Integer.parseInt(operand);
switch (operator) {
case "+": return String.valueOf(index + operandValue);
case "-": return String.valueOf(index - operandValue);
case "*": return String.valueOf(index * operandValue);
case "/": return operandValue != 0 ? String.valueOf(index / operandValue) : "0";
default: logger.error("Unsupported operator: " + operator); return String.valueOf(index);
}
} catch (NumberFormatException e) {
logger.error("Invalid operand in expression: " + operand);
return String.valueOf(index);
}
}
private static String replacePlaceholders(String text, Map<String, Object> data) {
Matcher matcher = PLACEHOLDER_PATTERN.matcher(text);
StringBuffer result = new StringBuffer();
while (matcher.find()) {
String replacement = getValueFromData(matcher.group(1), data);
matcher.appendReplacement(result, Matcher.quoteReplacement(replacement != null ? replacement : ""));
}
matcher.appendTail(result);
return result.toString();
}
private static String getValueFromData(String placeholder, Object data) {
String[] parts = placeholder.split("\\.");
Object current = data;
for (String part : parts) {
if (current instanceof Map) {
current = ((Map<?, ?>) current).get(part);
} else if (current != null) {
current = getFieldValue(current, part);
} else {
return null;
}
}
return current != null ? current.toString() : null;
}
private static Object getFieldValue(Object obj, String fieldName) {
try {
Field field = obj.getClass().getDeclaredField(fieldName);
field.setAccessible(true);
return field.get(obj);
} catch (NoSuchFieldException | IllegalAccessException e) {
logger.error("Failed to get field value: " + fieldName, e);
return null;
}
}
public static boolean isDecimal(String str) {
if (str == null) return false;
String regex = "^-?\\d+(\\.\\d+)?$";
if (str.matches(regex)) {
String[] split = str.split("\\.");
if (split.length > 0 && (split[0].startsWith("00") || split[0].length() >= 15)) {
return false;
}
return true;
}
return false;
}
public static boolean isScientific(String str) {
if (str == null) return false;
String regex = "^-?\\d*\\.\\d+[eE][+-]?\\d+$";
return str.matches(regex);
}
public static void main(String[] args) throws IOException {
Map<String, Object> data = new HashMap<>();
data.put("user", new User("张三", 25, new Date()));
List<User> userList = new ArrayList<>();
for (int i = 0; i < 20000; i++) {
userList.add(new User("李四", null, new Date()));
userList.add(new User("王五", null, new Date()));
}
data.put("users", userList);
data.put("date", "2025-03-11");
String templatePath = "D:/template.xlsx";
String outputPath = "D:/output.xlsx";
long startTime = System.currentTimeMillis();
generateExcel(templatePath, outputPath, data);
System.out.println("Excel 文件生成成功！耗时：" + (System.currentTimeMillis() - startTime) / 1000 + "秒");
}
static class User {
private String name;
private Integer age;
private Date makedate;
private String name1 = "00211111111111111111111111";
private String name2 = "3.1415926";
private String name3 = "XXXXXXXXXXXXXXXXXXXXXX";
// ... 其他字段省略 ...
public User(String name, Integer age, Date makedate) {
this.name = name;
this.age = age;
this.makedate = makedate;
}
}
}
🌟 使用方法
1. 准备 Excel 模板
- 创建
template.xlsx，包含占位符（如${user.name}）和循环标签：<jx:foreach items="${users}" var="user" varstatus="status"> ${status.index} | ${user.name} | ${user.age} | ${user.makedate} </jx:foreach> - 保存至
D:/template.xlsx。
2. 运行代码
- 配置
dataMap，包含单对象（user）和列表（users）。 - 调用
generateExcel("D:/template.xlsx", "D:/output.xlsx", data)。 - 输出文件将生成在
D:/output.xlsx。
示例输出
- 生成的 Excel 包含 40000 行用户数据（
users列表），每个用户填充name、age、makedate等字段。 - 耗时记录：约数秒（视机器性能）。
📌 注意事项
- 依赖配置：
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>5.2.3</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>5.2.3</version> </dependency> <dependency> <groupId>org.slf4j</groupId> <artifactId>slf4j-api</artifactId> <version>2.0.7</version> </dependency> - 模板规范：确保
<jx:foreach>和</jx:foreach>配对，避免解析失败。 - 文件路径：Windows 使用
D:/，Linux 使用/path/to/，注意斜杠方向。 - 数据校验：确保
dataMap 包含模板中所有占位符对应的键。
评论区