EasyExcel自定义多级表头导出

主要是封装好表头的list和数据的list。注意数据列的属性顺序和表头一致

package com.tgpms.web.common.controller;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.tgpms.web.excelTable.entity.ExcelTableColumn;
import com.tgpms.web.excelTable.entity.ExcelTableTemplate;
import com.tgpms.web.excelTable.service.IExcelTableColumnService;
import com.tgpms.web.excelTable.service.IExcelTableTemplateService;
import com.tgpms.web.system.entity.SmDatasource;
import com.tgpms.web.system.service.SmDatasourceService;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.*;
import java.util.stream.Collectors;

/**
 * @author jiangli
 * @since 2021/2/20 11:12
 * 通用Excel导出接口
 */
@Api(value = "ExcelController", tags = "通用Excel导出接口")
@RestController
@RequestMapping(value = "/ev/excel")
public class ExcelController {
    @Autowired
    private IExcelTableTemplateService templateService;
    @Autowired
    private IExcelTableColumnService columnService;
    @Autowired
    private SmDatasourceService smDatasourceService;

    @ApiOperation(value = "通过业务编码code导出数据", httpMethod = "GET")
    @GetMapping("/export")
    public void export(@RequestParam String code, @RequestParam String condition, HttpServletResponse response) throws IOException {
        ExcelTableTemplate one = templateService.getOne(new LambdaQueryWrapper<ExcelTableTemplate>().eq(ExcelTableTemplate::getCode, code));
        if (one == null) {
            return;
        }
        String ttId = one.getTtId();
        String dataSourceCode = one.getDataSourceCode();
        // 查询表格列
        List<ExcelTableColumn> excelTableColumns = columnService.list(new LambdaQueryWrapper<ExcelTableColumn>().eq(ExcelTableColumn::getTtId, ttId).orderByAsc(ExcelTableColumn::getOrderNo));
        Map<String, ExcelTableColumn> mapColumn = excelTableColumns.stream().collect(Collectors.toMap(ExcelTableColumn::getTcId, e -> e));
        List<ExcelTableColumn> leftList = excelTableColumns.stream().filter(e -> e.getLeaf().equals("1")).collect(Collectors.toList());
        List<List<String>> headList = new ArrayList<>();
        for (ExcelTableColumn excelTableColumn : leftList) {
            List<String> list = new ArrayList<>();
            headList(excelTableColumn,list,mapColumn);
            Collections.reverse(list);
            headList.add(list);
        }
        // 查询数据列表
        SmDatasource byDsCode = smDatasourceService.findByDsCode(dataSourceCode);
        if (null == byDsCode) {
            return;
        }
        String selectClause = byDsCode.getSelectClause().replace("@condition", condition);
        Map<String, String> params = new HashMap<>();
        params.put("sql", selectClause);
        List<Map<String, String>> list = smDatasourceService.executionSql(params);

        List<List<String>> data = new ArrayList<>();
        for (Map<String, String> map : list) {
            List<String> child = new ArrayList<>();
            for (ExcelTableColumn excelTableColumn : leftList) {
                String columnCode = excelTableColumn.getColumnCode();
                child.add(map.get(columnCode));
            }
            data.add(child);
        }

        // 头的策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // 背景设置为白色
        headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE1.getIndex());
        HorizontalCellStyleStrategy horizontalCellStyleStrategy =
                new HorizontalCellStyleStrategy(headWriteCellStyle,new ArrayList<>());

//        String path = "C:\\Users\\admin\\Desktop\\test.xlsx";
//        EasyExcel.write(path).head(headList).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).sheet("sheet1").doWrite(data);
        // 设置 ContentType
        response.setContentType("application/vnd.ms-excel");
        // 设置字符集
        response.setCharacterEncoding("utf-8");
        // 防止中文乱码
        String fileName = URLEncoder.encode(one.getDescription(), "UTF-8");
        // 设置 header
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
        // 写入 自定义表头
        EasyExcel.write(response.getOutputStream()).head(headList).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).sheet("sheet1").doWrite(data);
        // 写入 使用实体类的注解
        // EasyExcel.write(response.getOutputStream(), User.class).sheet("知识追寻者").doWrite(DataUtils.getUserData());
    }

    private void headList(ExcelTableColumn column, List<String> list, Map<String, ExcelTableColumn> mapColumn) {
        list.add(column.getLabel());
        String parentId = column.getParentId();
        if ("0".equals(parentId)) {
            return;
        }
        ExcelTableColumn tableColumn = mapColumn.get(parentId);
        headList(tableColumn,list,mapColumn);
    }

}

@Data
public class User {
​
    // id
    @ExcelProperty(index = 0,value = "编号")
    private Long id;
    // 名称
    @ExcelProperty(index = 1,value = "名称")
    private String name;
    // 创建时间
    @ExcelProperty(index = 2,value = "创建时间")
    private String createTime;
    // 描述
    @ExcelProperty(index = 3,value = "描述")
    private String description;
​
}

前端代码

 outExcel() {
      this.$message.success("正在下载，请稍候");
      const data = {code: this.code, condition: condition};
      this.axios.post('/ev/excel/export', qs.stringify(data), {responseType: 'blob'}).then(data => {
        if (data.status === 200) {
          const blob = new Blob([data.data]);
          const url = window.URL.createObjectURL(blob);
          const link = document.createElement("a");
          link.style.display = "none";
          link.href = url;
          link.setAttribute("download", this.description + '.xlsx');
          document.body.appendChild(link);
          link.click();
        } else {
          this.$message.error("网络错误：" + data.status);
        }
      })
    },