EasyExcel動態(tài)表頭導出（支持多級表頭）

在很多業(yè)務場景中，都會應用到動態(tài)表頭的導出，也會涉及到多級表頭的導出，如下圖所示

通過EasyExcel，我們可以快速實現(xiàn)這一需求，具體代碼如下

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;

    /**
     * 字段個數(shù)
     */
    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);
    }
    /**
     * 為合并的單元格設置樣式（可根據(jù)需要自行調整）
     */
    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文章來源地址http://www.zghlxwxcb.cn/news/detail-712105.html

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;
/**
 * 動態(tài)導出工具
 *@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);

    /**
     * 根據(jù)模板導出數(shù)據(jù) 單個sheet
     *
     * @param response     返回對象
     * @param dataList     導出的數(shù)據(jù)集合
     * @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();
    }
    /**
     * 動態(tài)表頭生成excel
     * @param headers 要生成的表頭
     * @param dataList 數(shù)據(jù)
     * @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("生成動態(tài)EXL失敗，字段", e);
        }
        long endTime = System.currentTimeMillis();
        log.info("動態(tài)導出耗時:{}", 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          數(shù)據(jù)對象
     * @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 {
            //先根據(jù)反射獲取實體類的class對象
            Class<?> objClass = obj.getClass();
            //設置實體類屬性的集合
            Field[] fields = ReflectUtil.getFields(objClass);
            for (String prop : propList) {
                //循環(huán)實體類對象集合
                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("生成動態(tài)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;
    }
}

測試：

到了這里，關于EasyExcel動態(tài)表頭導出（支持多級表頭）的文章就介紹完了。如果您還想了解更多內容，請在右上角搜索TOY模板網以前的文章或繼續(xù)瀏覽下面的相關文章，希望大家以后多多支持TOY模板網！