根据Excel模板生成表格。很早之前写的代码，之前使用jxl后来升级到了poi，一直在用。后来发现阿里开源的easyexcel也有相同的功能，并且优化了xlsx的内存问题。如有相同需求推荐使用easyexcel。
- 支持表头、表尾数据填充
- 支持列表数据行遍历
- 支持多列表
- 支持单元格格式
- 支持合并单元格
-
xlsx大数据量操作慢
public class ExcelUtils {
private static final String REG = "\\{([a-zA-Z_]+)\\}";// 匹配"{exp}"
private static final String REG_LIST = "\\{\\.([a-zA-Z_]+)\\}";// 匹配"{.exp}"
private static final Pattern PATTERN = Pattern.compile(REG);
private static final Pattern PATTERN_LIST = Pattern.compile(REG_LIST);
private ExcelUtils() {
}
/**
* 根据模板生成Excel文件
*
* @param templateFile 模版文件
* @param context 表头或表尾数据集合
* @param dataList 列表
* @return
*/
public static byte[] writeExcel(File templateFile, Map<String, Object> context,
List<Map<String, Object>> dataList) {
try (Workbook workbook = WorkbookFactory.create(templateFile)) {
Sheet sheet = workbook.getSheetAt(0);// 获取配置文件sheet 页
int listStartRowNum = -1;
for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {
Row row = sheet.getRow(i);
if (row != null) {
for (int j = 0; j < row.getLastCellNum(); j++) {
Cell cell = row.getCell(j);
if (cell != null && cell.getCellType() == CellType.STRING) {
String cellValue = cell.getStringCellValue();
// 获取到列表数据所在行
if (listStartRowNum == -1 && cellValue.matches(REG_LIST)) {
listStartRowNum = i;
}
Object newValue = cellValue;
Matcher matcher = PATTERN.matcher(cellValue);
while (matcher.find()) {
String replaceExp = matcher.group();// 匹配到的表达式
String key = matcher.group(1);// 获取key
Object replaceValue = context.get(key);
if (replaceValue == null) {
replaceValue = "";
}
if (replaceExp.equals(cellValue)) {// 单元格是一个表达式
newValue = replaceValue;
} else {// 以字符串替换
newValue = ((String) newValue).replace(replaceExp, replaceValue.toString());
}
}
setCellValue(cell, newValue);
}
}
}
}
if (-1 != listStartRowNum) {// 如果不为 -1 说明有需要循环的列表表达式
Row listStartRow = sheet.getRow(listStartRowNum);
if (CollectionUtils.isEmpty(dataList)) {// 列表数据为空，清空列表表达式行
for (int i = 0; i < listStartRow.getLastCellNum(); i++) {
Cell cell = listStartRow.getCell(i);
if (cell != null) {
cell.setCellValue("");
}
}
} else {
int lastCellNum = listStartRow.getLastCellNum();
if (listStartRowNum + 1 <= sheet.getLastRowNum()) {
sheet.shiftRows(listStartRowNum + 1, sheet.getLastRowNum(), dataList.size(), true, false);// 列表数据行后面行下移，留出数据填充区域
}
for (int i = 0; i < dataList.size(); i++) {// 循环列表数据 生成行
Map<String, Object> map = dataList.get(i);// 一行数据
int newRowNum = listStartRowNum + i + 1;// 保留表达式行
Row newRow = sheet.createRow(newRowNum);// 创建新行
for (int j = 0; j < lastCellNum; j++) {// 循环遍历单元格
Cell cell = listStartRow.getCell(j);// 列表数据行
// 填充数据
if (cell != null) {
Cell newCell = newRow.createCell(j);
newCell.setCellStyle(cell.getCellStyle());// 设置单元格格式
if (cell.getCellType() == CellType.STRING
&& cell.getStringCellValue().matches(REG_LIST)) {// 单元格是一个表达式
String cellExp = cell.getStringCellValue();
Matcher matcher = PATTERN_LIST.matcher(cellExp);
matcher.find();
String key = matcher.group(1);// 获取key
Object newValue = map.get(key);
if (newValue == null) {
newValue = "";
}
setCellValue(newCell, newValue);
} else {// 不是表达式复制单元格数据
CellType cellType = cell.getCellType();
if (cellType == CellType.NUMERIC) {
newCell.setCellValue(cell.getNumericCellValue());
} else if (cellType == CellType.BOOLEAN) {
newCell.setCellValue(cell.getBooleanCellValue());
} else if (cellType == CellType.STRING) {
newCell.setCellValue(cell.getStringCellValue());
} else if (cellType == CellType.FORMULA) {
// 处理公式，待实现
} else {
newCell.setCellValue(cell.getStringCellValue());
}
}
}
}
}
sheet.removeRow(listStartRow);// 删除list表达式行
sheet.shiftRows(listStartRowNum + 1, sheet.getLastRowNum(), -1, true, false);// 数据区域上移一行，覆盖表达式行
// 合并单元格处理
for (int i = 0; i < lastCellNum; i++) {
CellRangeAddress mergedRangeAddress = getMergedRangeAddress(sheet, listStartRowNum, i);
if (mergedRangeAddress != null) {// 合并的单元格
i = mergedRangeAddress.getLastColumn();
for (int j = 1; j < dataList.size(); j++) {
int newRowNum = listStartRowNum + j;
sheet.addMergedRegionUnsafe(new CellRangeAddress(newRowNum, newRowNum,
mergedRangeAddress.getFirstColumn(), mergedRangeAddress.getLastColumn()));
}
}
}
}
}
// 公式生效
sheet.setForceFormulaRecalculation(true);
ByteArrayOutputStream out = new ByteArrayOutputStream();
workbook.write(out);
return out.toByteArray();
} catch (Exception e) {
throw new ExcelException("生成excel失败!", e);
}
}
private static void setCellValue(Cell cell, Object value) {
if (value instanceof Number) {// 如果是数字类型的设置为数值
cell.setCellValue(Double.parseDouble(value.toString()));
} else if (value instanceof Date) {// 如果为时间类型的设置为时间
cell.setCellValue((Date) value);
} else if (value instanceof String) {
cell.setCellValue((String) value);
} else if (value instanceof Boolean) {
cell.setCellValue((Boolean) value);
} else {
cell.setCellValue(value.toString());
}
}
/**
* 获取指定行/列的合并单元格区域
*
* @param sheet
* @param row
* @param column
* @return CellRangeAddress 不是合并单元格返回null
*/
private static CellRangeAddress getMergedRangeAddress(Sheet sheet, int row, int column) {
List<CellRangeAddress> mergedRegions = sheet.getMergedRegions();
for (CellRangeAddress cellAddresses : mergedRegions) {
if (row >= cellAddresses.getFirstRow() && row <= cellAddresses.getLastRow()
&& column >= cellAddresses.getFirstColumn() && column <= cellAddresses.getLastColumn()) {
return cellAddresses;
}
}
return null;
}
/**
* 多个列表支持，按顺序写入excel。 列表数据数量需等于列表表达式数量，不然多余的表达式不会被清空。多余的列表数据不会被写入
*
* @param templateFile
* @param context
* @param dataLists
* @return
*/
public static byte[] writeMultiList(File templateFile, Map<String, Object> context,
List<List<Map<String, Object>>> dataLists) {
try {
File temp = templateFile;
for (List<Map<String, Object>> dataList : dataLists) {
byte[] tempBytes = writeExcel(temp, context, dataList);
temp = File.createTempFile("multi_excel", ".excel");
FileUtils.writeByteArrayToFile(temp, tempBytes);
}
return FileUtils.readFileToByteArray(temp);
} catch (ExcelException e) {
throw e;
} catch (Exception e) {
throw new ExcelException("生成Excel失败！", e);
}
}
static class ExcelException extends RuntimeException {
/**
*
*/
private static final long serialVersionUID = -2772261598232964002L;
public ExcelException(String msg, Throwable e) {
super(msg, e);
}
public ExcelException(String msg) {
super(msg);
}
}
}
网友评论