EasyExcel动态表头导出(支持多级表头)

EasyExcel动态表头导出(支持多级表头)

在很多业务场景中,都会应用到动态表头的导出,也会涉及到多级表头的导出,如下图所示

通过EasyExcel,我们可以快速实现这一需求,具体代码如下

DynamicHeader

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

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: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;
    }
}

测试:

热门相关:慕先生的小骄傲   楚氏赘婿   重生成偏执霍少的小仙女   楚氏赘婿   魔神狂后