EasyExcel自定义多级表头导出

浅浅的花香味﹌ 2022-10-31 12:09 6阅读 0赞

主要是封装好表头的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);
        }
    
    }

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5OTQwMjA1_size_16_color_FFFFFF_t_70][]

@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);
            }
          })
        },

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5OTQwMjA1_size_16_color_FFFFFF_t_70]: /images/20221024/c056d248f6f5437798935b63cbd91bf5.png