EasyExcel動態表頭導出（支持多級表頭）
EasyExcel動態表頭導出（支持多級表頭）
在很多業務場景中，都會應用到動態表頭的導出，也會涉及到多級表頭的導出，如下圖所示
通過EasyExcel，我們可以快速實現這一需求，具體代碼如下
maven依賴
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.6</version>
</dependency>
DynamicHeader.java
import java.util.List;
/**
*@Author: <a href="mailto:fxsen@foxmail.com">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.java
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.java
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.java
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:fxsen@foxmail.com">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;
}
}
測試：
浙公網安備 33010602011771號