EasyExcel动态表头导出（支持多级表头）
在很多业务场景中，都会应用到动态表头的导出，也会涉及到多级表头的导出，如下图所示
通过EasyExcel，我们可以快速实现这一需求，具体代码如下
DynamicHeader
import java.util.List;
/**
*@Author: <a href="mailto:[email protected]">Fxsen</a>
*@CreateTime: 2023年09月22日 09:16
*/
public class DynamicHeader {
/**
* 要导出的字段名称英文
*/
private String fieldName;
/**
* 要导出的表头名称中文
*/
private String headName;
/**
* 如果是多级表都，插入下级
*/
private List<DynamicHeader> children;
public String getFieldName() {
return fieldName;
}
public void setFieldName(String fieldName) {
this.fieldName = fieldName;
}
public String getHeadName() {
return headName;
}
public void setHeadName(String headName) {
this.headName = headName;
}
public List<DynamicHeader> getChildren() {
return children;
}
public void setChildren(List<DynamicHeader> children) {
this.children = children;
}
}
CustomTitleWriteHandler
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
public class CustomTitleWriteHandler implements SheetWriteHandler {
/**
* 标题
*/
private final String fileName;
/**
* 字段个数
*/
private final Integer count;
public CustomTitleWriteHandler(Integer count,String fileName) {
this.fileName = fileName;
this.count = count;
}
@Override
public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
}
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
// 获取clazz所有的属性
Workbook workbook = writeWorkbookHolder.getWorkbook();
Sheet sheet = workbook.getSheetAt(0);
Row row1 = sheet.createRow(0);
row1.setHeight((short) 800);
Cell cell = row1.createCell(0);
//设置标题
cell.setCellValue(fileName);
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
cellStyle.setBorderLeft(BorderStyle.THIN);//左边框
cellStyle.setBorderTop(BorderStyle.THIN);//上边框
cellStyle.setBorderRight(BorderStyle.THIN);//右边框
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
cellStyle.setAlignment(HorizontalAlignment.CENTER);
Font font = workbook.createFont();
font.setBold(true);
font.setFontHeight((short) 400);
font.setFontName("宋体");
cellStyle.setFont(font);
cell.setCellStyle(cellStyle);
CellRangeAddress region = new CellRangeAddress(0, 0, 0, count - 1);
setRegionStyle(sheet,region,cellStyle);
sheet.addMergedRegion(region);
}
/**
* 为合并的单元格设置样式（可根据需要自行调整）
*/
public static void setRegionStyle(Sheet sheet, CellRangeAddress region, CellStyle cs) {
for (int i = region.getFirstRow(); i <= region.getLastRow(); i++) {
Row row = sheet.getRow(i);
if (null == row) row = sheet.createRow(i);
for (int j = region.getFirstColumn(); j <= region.getLastColumn(); j++) {
Cell cell = row.getCell(j);
if (null == cell) cell = row.createCell(j);
cell.setCellStyle(cs);
}
}
}
}
CellStyle
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;
import java.util.List;
public class CellStyle extends AbstractColumnWidthStyleStrategy {
@Override
protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> cellDataList, Cell cell, Head head,
Integer relativeRowIndex, Boolean isHead) {
// 简单设置
Sheet sheet = writeSheetHolder.getSheet();
sheet.setColumnWidth(cell.getColumnIndex(), 5000);
}
}
DynamicExcelUtils
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.stream.Collectors;
import cn.hutool.core.util.ReflectUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.fill.FillConfig;
import com.wisesoft.core.util.DateUtil;
import org.apache.commons.collections4.CollectionUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
/**
* 动态导出工具
*@Author: <a href="mailto:[email protected]">Fxsen</a>
*@CreateTime: 2023年09月22日 09:13
*/
public class DynamicExcelUtils {
private static final Logger log = LoggerFactory.getLogger(DynamicExcelUtils.class);
/**
* 根据模板导出数据 单个sheet
*
* @param response 返回对象
* @param dataList 导出的数据集合
* @param object 填充对象
* @param fileName 文件名称
* @param templateName 模板名称
* @throws Exception
*/
public void exportTemplateExcel(HttpServletResponse response, List<?> dataList, Object object,
String fileName, String templateName) throws Exception {
InputStream inputStream = this.getClass().getResourceAsStream(templateName);
FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
ExcelWriter excelWriter = EasyExcelFactory.write(getOutputStream(fileName, response)).withTemplate(inputStream).build();
WriteSheet writeSheet0 = EasyExcelFactory.writerSheet(0).build();
excelWriter.fill(object, fillConfig, writeSheet0);
excelWriter.fill(dataList, fillConfig, writeSheet0);
excelWriter.finish();
}
/**
* 构建输出流
*
* @param fileName 文件名称
* @param response 输出流
* @return
* @throws Exception
*/
private OutputStream getOutputStream(String fileName, HttpServletResponse response) throws Exception {
fileName = URLEncoder.encode(fileName, StandardCharsets.UTF_8.name()).replaceAll("\\+", "%20");
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding(StandardCharsets.UTF_8.name());
response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");
return response.getOutputStream();
}
/**
* 动态表头生成excel
* @param headers 要生成的表头
* @param dataList 数据
* @param response
* @param fileName 文件名称
* @param titleName title名称
* @param <T>
*/
public static <T> void dynamicExportExcel(List<DynamicHeader> headers, List<T> dataList, HttpServletResponse response, String fileName, String titleName) {
long startTime = System.currentTimeMillis();
List<List<T>> allList = new ArrayList<>();
if (CollectionUtils.isNotEmpty(dataList)){
for (T detail : dataList) {
allList.addAll(dataList(headers, detail));
}
}
List<List<String>> headerList = headers(headers);
try (ServletOutputStream outputStream = response.getOutputStream()) {
String name = URLEncoder.encode(fileName, StandardCharsets.UTF_8.name()).replaceAll("\\+", "%20");
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + name + ".xlsx");//设置响应头
EasyExcel.write(outputStream).head(headerList)
//表格标题占位
.relativeHeadRowIndex(1)
//文件样式
.registerWriteHandler(new CustomTitleWriteHandler(headerList.size(),titleName))
.registerWriteHandler(new CellStyle())
.sheet(fileName).doWrite(allList);
} catch (IOException e) {
e.printStackTrace();
log.error("生成动态EXL失败，字段", e);
}
long endTime = System.currentTimeMillis();
log.info("动态导出耗时:{}", endTime - startTime);
}
//excel表头
public static List<List<String>> headers(List<DynamicHeader> excelHeaders) {
List<List<String>> headers = new ArrayList<>();
for (DynamicHeader header : excelHeaders) {
List<DynamicHeader> children = header.getChildren();
if (CollectionUtils.isNotEmpty(children)){
for (DynamicHeader child : children) {
List<String> head = new ArrayList<>();
head.add(header.getHeadName());
head.add(child.getHeadName());
headers.add(head);
}
}else {
List<String> head = new ArrayList<>();
head.add(header.getHeadName());
headers.add(head);
}
}
return headers;
}
/**
* 要导出的字段
*
* @param exportFields 表头集合
* @param obj 数据对象
* @return 集合
*/
public static <T> List<List<T>> dataList(List<DynamicHeader> exportFields, T obj) {
List<List<T>> list = new ArrayList<>();
List<T> data = new ArrayList<>();
List<String> propList = new ArrayList<>();
for (DynamicHeader exportField : exportFields) {
List<DynamicHeader> children = exportField.getChildren();
if (CollectionUtils.isNotEmpty(children)){
propList.addAll(children.stream().map(DynamicHeader::getFieldName).collect(Collectors.toList()));
}else {
propList.add(exportField.getFieldName());
}
}
if (obj instanceof Map){
Map map = (Map) obj;
for (String prop : propList) {
map.forEach((k,v)->{
if (prop.equals(k)){
if (Objects.isNull(v)){
v = "";
}else if (v instanceof Date){
v = DateUtil.format((Date)v,DateUtil.DATETIME_YMD_DASH);
}
data.add((T)v);
}
});
}
}else {
//先根据反射获取实体类的class对象
Class<?> objClass = obj.getClass();
//设置实体类属性的集合
Field[] fields = ReflectUtil.getFields(objClass);
for (String prop : propList) {
//循环实体类对象集合
for (Field field : fields) {
field.setAccessible(true);
//判断实体类属性跟特定字段集合名是否一样
if (field.getName().equals(prop)) {
T object = null;
try {
object = (T) field.get(obj);
} catch (IllegalAccessException e) {
e.printStackTrace();
log.error("生成动态EXL失败，字段", e);
}
//获取属性对应的值
if(null == object){
object = (T) "";
}else{
if(object instanceof LocalDate){
object = (T)((LocalDate)object).format(DateTimeFormatter.ofPattern(DateUtil.DEFAULT_DATETIME_FORMAT));
}
if(object instanceof LocalDateTime){
object = (T) ((LocalDateTime)object).format(DateTimeFormatter.ofPattern(DateUtil.DEFAULT_DATETIME_FORMAT));
}
}
data.add(object);
}
}
}
}
list.add(data);
return list;
}
}
测试：
