easyExcel自定义表头导出

深碍√TFBOYSˉ_ 2023-09-30 15:24 68阅读 0赞

当页面列表数据过多，而我们真实需要导出Excel表格的列没有那么多时，则需要支持，仅导出用户选定的列，老样子直接上图上代码。

如：页面全量数据如图，有9列  
![在这里插入图片描述][93602f1a92ef4b98871e4cedce0bd169.png]  
但实际上，业务有时只需要三列，每次下载后，还需要手动删除不需要的列，如图：  
![在这里插入图片描述][7c50e3c38fd44084b3b0d42d93a95b24.png]  
这是，我们就需要根据用户的指定列，导出指定列的数据，下面是代码实现

Cotroller 层

@Autowired
        private TestExportService testExportService;
    
        @GetMapping("/test/export")
        public void export(HttpServletResponse response , List<String> headList) {
        
            testExportService.exportTest(response,testDTO);
        }

Service 层

void exportTest(HttpServletResponse response, List<String> headList) throws Exception;

ServiceImpl

@Autowired
        private TestMapper testMapper;
        
        @Override
        public void exportTest(HttpServletResponse response, List<String> headList) throws Exception{
        
            // 查出需要导出的数据
            List<TestExportDTO> testExport= testMapper.testExportList();
            // 将查出的数据转化为导出需要的数据
            String toJSONString = JSON.toJSONString(testExport);
            List<TestExportVO> testExportVO= JSON.parseArray(toJSONString, TestExportVO.class);
    
    		// 设置导出配置
    		response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            DateTimeFormatter dtf = DateTimeFormatter.ofPattern("yyyyMMdd-HHmmss");
            String fileName = URLEncoder.encode("导出文件的名称" + dtf.format(LocalDateTime.now()), "UTF-8");
            response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
            // 标题样式
            WriteCellStyle headCellStyle = this.getHeadStyle();
            // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
            HorizontalCellStyleStrategy strategy = new HorizontalCellStyleStrategy(headCellStyle, new WriteCellStyle());
            response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
    
    		 EasyExcel.write(response.getOutputStream())
                        .sheet("导出文件的sheet名称")
                        //设置默认样式及写入头信息开始的行数
                        .useDefaultStyle(true).relativeHeadRowIndex(0)
                        // 表头、内容样式设置
                        .registerWriteHandler(strategy)
                        // 统一列宽,如需设置自动列宽则new LongestMatchColumnWidthStyleStrategy()
                        .registerWriteHandler(new SimpleColumnWidthStyleStrategy(20))
                        .registerWriteHandler(new SimpleRowHeightStyleStrategy((short) 30, (short) 15))
                        // 这里放入前端入参的动态表头
                        .head(this.head(headList))
                        // 当然这里数据也可以用 List<List<String>> 去传入
                        .doWrite(this.getData(testExportVO, headList));
        }
    
        /**
         * 表头样式配置（根据自己实际情况进行删减，调整）
         *
         * @return
         */
        public static WriteCellStyle getHeadStyle() {
        
            // 头的策略
            WriteCellStyle headWriteCellStyle = new WriteCellStyle();
            // 背景颜色
            headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
            headWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
            // 字体
            WriteFont headWriteFont = new WriteFont();
            headWriteFont.setFontName("宋体");//设置字体名字
            headWriteFont.setFontHeightInPoints((short) 14);//设置字体大小
            headWriteFont.setBold(true);//字体加粗
            headWriteCellStyle.setWriteFont(headWriteFont); //在样式用应用设置的字体;
            // 样式
            headWriteCellStyle.setBorderBottom(BorderStyle.THIN);//设置底边框;
            headWriteCellStyle.setBottomBorderColor((short) 0);//设置底边框颜色;
            headWriteCellStyle.setBorderLeft(BorderStyle.THIN);  //设置左边框;
            headWriteCellStyle.setLeftBorderColor((short) 0);//设置左边框颜色;
            headWriteCellStyle.setBorderRight(BorderStyle.THIN);//设置右边框;
            headWriteCellStyle.setRightBorderColor((short) 0);//设置右边框颜色;
            headWriteCellStyle.setBorderTop(BorderStyle.THIN);//设置顶边框;
            headWriteCellStyle.setTopBorderColor((short) 0); //设置顶边框颜色;
            headWriteCellStyle.setWrapped(true);  //设置自动换行;
            headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);//设置水平对齐的样式为居中对齐;
            headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);  //设置垂直对齐的样式为居中对齐;
            headWriteCellStyle.setShrinkToFit(true);//设置文本收缩至合适
    
            return headWriteCellStyle;
        }
        
        /**
         * 动态表头的处理
         *
         * @param headList
         * @return
         */
        private List<List<String>> head(List<String> headList) {
        
            List<List<String>> list = new ArrayList<>();
    
            List<ColumnConfig> config = this.createTestListColumnConfig();
            Map<String, String> headMap = config.stream().collect(Collectors.toMap(ColumnConfig::getValueName, ColumnConfig::getDisplayName));
            for (String head : headList) {
        
                List<String> headStr = new ArrayList<>();
                headStr.add(headMap.get(head));
                list.add(headStr);
            }
            return list;
        }
    
        /**
         * 配置导出列表的动态配置
         * 这里是全量，根据方法"动态表头的处理"，进行动态匹配
         *
         * @return list
         */
        private List<ColumnConfig> createTestListColumnConfig() {
        
            List<ColumnConfig> columnConfigList = new ArrayList<>();
            columnConfigList.add(new ColumnConfig(0, "订单号", "id"));
            columnConfigList.add(new ColumnConfig(1, "会员姓名", "name"));
            columnConfigList.add(new ColumnConfig(2, "下单时间", "createTime"));
            columnConfigList.add(new ColumnConfig(3, "订单状态", "status"));
            columnConfigList.add(new ColumnConfig(4, "订单标签", "label"));
            columnConfigList.add(new ColumnConfig(5, "所属企业", "companyName"));
            columnConfigList.add(new ColumnConfig(6, "所属项目", "projectName"));
            columnConfigList.add(new ColumnConfig(7, "项目类型", "projectType"));
            columnConfigList.add(new ColumnConfig(8, "项目经理", "projectManager"));
            return columnConfigList;
        }
    
        /**
         * 不创建对象写入
         *
         * @param originalDataList           查出需要导出的数据
         * @param headList                   需要的表头咧
         * @return
         */
        public List<List<Object>> getData(List<TestExportVO> originalDataList, List<String> headList) {
        
            ArrayList<List<Object>> result = Lists.newArrayList();
            Class<TestExportVO> clazz = TestExportVO.class;
    
            for (TestExportVOrecordData : originalDataList) {
        
                ArrayList<Object> data = new ArrayList<>();
                for (String fieldName : headList) {
        
                    Field field;
                    try {
        
                        field = clazz.getDeclaredField(fieldName);
                        field.setAccessible(true);
                        Object o = field.get(recordData);
                        data.add(o);
                    } catch (Exception e) {
        
                        e.printStackTrace();
                        throw new RuntimeException(e.getMessage());
                    }
                }
                result.add(data);
            }
    
            return result;
        }

ColumnConfig实体类

@Data
    public class ColumnConfig {
        
    
        /**
         * 排序
         */
        private int rank;
    
        /**
         * 展示的列名
         */
        private String displayName;
    
        /**
         * 列对应的数据字段名
         */
        private String valueName;
    }

需要导出的实体类

@Data
    @HeadRowHeight(30)
    @ColumnWidth(20)
    public class TestExportVO{
        
    
        @ExcelProperty("订单号")
        private String id;
    
        @ExcelProperty("会员姓名")
        private String name;
    
        @ExcelProperty("下单时间")
        private String createTime;
    
        @ExcelProperty("订单状态")
        private String status;
    
        @ExcelProperty("订单标签")
        private String label;
    
        @ExcelProperty("所属企业")
        private String companyName;
    
        @ExcelProperty("所属项目")
        private String projectName;
        
        @ExcelProperty("项目类型")
        private String projectType;
    
        @ExcelProperty("项目经理")
        private String projectManager;
    
    }

[93602f1a92ef4b98871e4cedce0bd169.png]: https://img-blog.csdnimg.cn/93602f1a92ef4b98871e4cedce0bd169.png
[7c50e3c38fd44084b3b0d42d93a95b24.png]: https://img-blog.csdnimg.cn/7c50e3c38fd44084b3b0d42d93a95b24.png