使用EasyPOI导出Excel表格（含多sheet导出以及一对多导出）

矫情吗；* 2023-01-19 07:18 18阅读 0赞

## 一。前言 ##

### 官方Api文档地址： ###

[http://doc.wupaas.com/docs/easypoi/easypoi-1c0u4mo8p4ro8][http_doc.wupaas.com_docs_easypoi_easypoi-1c0u4mo8p4ro8]

### 常用注解介绍 ###

注解介绍：  
easypoi起因就是Excel的导入导出,最初的模板是实体和Excel的对应,model–row,filed–col 这样利用注解我们可以和容易做到excel到导入导出，经过一段时间发展,现在注解有5个类分别是：

*  `@Excel` 作用到filed上面,是对Excel一列的一个描述
 *  `@ExcelCollection` 表示一个集合,主要针对一对多的导出,比如一个老师对应多个科目,科目就可以用集合表示
 *  `@ExcelEntity` 表示一个继续深入导出的实体,但他没有太多的实际意义,只是告诉系统这个对象里面同样有导出的字段
 *  `@ExcelIgnore` 和名字一样表示这个字段被忽略跳过这个导导出
 *  `@ExcelTarget` 这个是作用于最外层的对象,描述这个对象的id,以便支持一个对象可以针对不同导出做出不同处理

### @Excel ###

这个是必须使用的注解,如果需求简单只使用这一个注解也是可以的,涵盖了常用的Excel需求,需要大家熟悉这个功能,主要分为基础,图片处理,时间处理,合并处理几块,name\_id是上面讲的id用法,这里就不累言了：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDAwOTQ0Nw_size_16_color_FFFFFF_t_70]

### @ExcelCollection ###

一对多的集合注解,用以标记集合是否被数据以及集合的整体排序：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDAwOTQ0Nw_size_16_color_FFFFFF_t_70 1]

### @ExcelEntity ###

标记是不是导出excel 标记为实体类,一遍是一个内部属性类,标记是否继续穿透,可以自定义内部id：  
![在这里插入图片描述][20210508155244821.png]

## 二。使用 ##

### 导入依赖： ###

<dependency>
        <groupId>cn.afterturn</groupId>
        <artifactId>easypoi-base</artifactId>
        <version>4.2.0</version>
    </dependency>
    <dependency>
        <groupId>cn.afterturn</groupId>
        <artifactId>easypoi-web</artifactId>
        <version>4.2.0</version>
    </dependency>
    <dependency>
        <groupId>cn.afterturn</groupId>
        <artifactId>easypoi-annotation</artifactId>
        <version>4.2.0</version>
    </dependency>

### 构建对应实体关系： ###

1.DayShiftExcelVo：

package com.cdtye.itps.jjxt.model.vo;
    
    import cn.afterturn.easypoi.excel.annotation.Excel;
    import cn.afterturn.easypoi.excel.annotation.ExcelTarget;
    import lombok.AllArgsConstructor;
    import lombok.Data;
    import lombok.experimental.Accessors;
    
    /** * @ClassName BureauDayShiftExcelVo * @Description TODO 日交班导出Excel实体Vo * @Author Zhongks * @Date 2021/5/7 11:34 * @Version 1.0 **/
    @Data
    @Accessors(chain = true)
    @AllArgsConstructor
    @ExcelTarget(value = "DayShiftExcelVo")
    public class DayShiftExcelVo { 
    
        @Excel(name = "日期", width = 20,height = 60)
        private String inputDate;
    
        @Excel(name = "基本情况", width = 20,height = 60)
        private String basicInformation;
    
        @Excel(name = "问题描述", width = 20,height = 60)
        private String faultDescription;
    
        @Excel(name = "上报情况", width = 20,height = 60)
        private String reportType;
    
        @Excel(name = "站段分析", width = 20,height = 60)
        private String segmentAnalysis;
    
        @Excel(name = "供电处审核", width = 20,height = 60)
        private String review;
    
        @Excel(name = "流程", width = 20,height = 60, replace = { "电调暂存草稿单_0","电调已提交状态_1","电调已分析状态_2","供电部审核通过_3","供电部审核不通过_4"})
        private String process;
    
        @Excel(name = "重点追踪", width = 20,height = 60, replace = { "非重点追踪_0","重点追踪_1"})
        private String track;
    }

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDAwOTQ0Nw_size_16_color_FFFFFF_t_70 2]  
excel显示效果：  
![在这里插入图片描述][20210508161800115.png]

2.SkylightCashStatisticsExcelVo：

package com.cdtye.itps.jjxt.model.vo;
    
    import cn.afterturn.easypoi.excel.annotation.Excel;
    import cn.afterturn.easypoi.excel.annotation.ExcelEntity;
    import cn.afterturn.easypoi.excel.annotation.ExcelTarget;
    import lombok.AllArgsConstructor;
    import lombok.Data;
    import lombok.experimental.Accessors;
    
    /** * @ClassName SkylightCashStatisticsExcelVo * @Description TODO 天窗兑现统计导出Excel实体Vo * @Author Zhongks * @Date 2021/5/8 10:05 * @Version 1.0 **/
    @Data
    @Accessors(chain = true)
    @AllArgsConstructor
    @ExcelTarget(value = "SkylightCashStatisticsExcelVo")
    public class SkylightCashStatisticsExcelVo { 
    
        @Excel(name = "段别", width = 20,needMerge = true)
        private String section;
    
        @Excel(name = "线路等级", width = 20,needMerge = true)
        private String lineLevel;
    
        @Excel(name = "线别", width = 20,needMerge = true)
        private String lineName;
    
        @ExcelEntity(name = "申请",show = true)
        private SkylightCashStatisticsCommonExcelVo applicationExcelVo;
    
        @Excel(name = "取消", width = 20,needMerge = true)
        private String cancel;
    
        @Excel(name = "作业车数量", width = 20,needMerge = true)
        private String numberOfOperationVehicles;
    
        @Excel(name = "检修列数量", width = 20,needMerge = true)
        private String numberOfMaintenanceTrains;
    
        @ExcelEntity(name = "申请时间",show = true)
        private SkylightCashStatisticsCommonExcelVo applicationTimeExcelVo;
    
        @ExcelEntity(name = "给点时间",show = true)
        private SkylightCashStatisticsCommonExcelVo giveTimeExcelVo;
    
        @ExcelEntity(name = "作业时间",show = true)
        private SkylightCashStatisticsCommonExcelVo workTimeExcelVo;
    
        @Excel(name = "取消", width = 20,needMerge = true)
        private String cancelReason;
    }

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDAwOTQ0Nw_size_16_color_FFFFFF_t_70 3]  
SkylightCashStatisticsCommonExcelVo：

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDAwOTQ0Nw_size_16_color_FFFFFF_t_70 4]  
excel显示效果：  
![在这里插入图片描述][2021050816192613.png]  
3.使用@ExcelCollection显示效果：（由于实现原理一致，本文章不做示例）  
![在这里插入图片描述][20210508162059943.png]

### Service层 ###

/** * @Author Zhongks * @Description //TODO excel导出 * @Date 12:25 2021/5/7 * @Param [list, response] * @return void **/
        public void export(BureauDayShiftVo bureauDayShiftVo,HttpServletResponse response) { 
            try { 
                // 设置下载的Excel名称，以当前时间为文件后缀，
                String dateTime = DateUtil.formatDateString(new Date(), DateUtil.DATE_FORMAT);
                String fileName = "供电安全质量日交班表"+dateTime+".xlsx";
                // 设置响应输出的头类型
                response.setHeader("content-Type", "application/vnd.ms-excel");
                response.setHeader("Content-Disposition", "attachment;filename="+fileName);
    
                // excel信息部分
                //供电处重点信息追踪表信息
                bureauDayShiftVo.setTrackFlag(1);
                Map<String, Object> trackSafeQualityMap =this.getTrackSafeQualityMap(bureauDayShiftVo);
                //日安全质量信息表信息
                bureauDayShiftVo.setTrackFlag(0);
                Map<String, Object> safeQualityParamsMap =this.getTrackSafeQualityMap(bureauDayShiftVo);
                //天窗兑现统计表
                Map<String, Object> skylightCashStatisticsMap = this.getSkylightCashStatisticsMap();
    
                //添加表
                List<Map<String, Object>> sheetsList = new ArrayList<>();
                sheetsList.add(trackSafeQualityMap);//sheet1
                sheetsList.add(safeQualityParamsMap);//sheet2
                sheetsList.add(skylightCashStatisticsMap);//sheet3
    
                //创建excel文件的方法
                Workbook workbook = ExcelExportUtil.exportExcel(sheetsList, ExcelType.HSSF);
                //通过response输出流直接输入给客户端
                ServletOutputStream outputStream = response.getOutputStream();
                workbook.write(outputStream);
                outputStream.flush();
                outputStream.close();
            } catch (Exception e) { 
                e.printStackTrace();
            }
        }
    
    
    	/** * @Author Zhongks * @Description //TODO 返回重点追踪以及非重点追踪excel信息 * @Date 9:31 2021/5/8 * @Param [bureauDayShiftVo] * @return java.util.List<java.util.Map<java.lang.String,java.lang.Object>> **/
        public Map<String, Object> getTrackSafeQualityMap(BureauDayShiftVo bureauDayShiftVo){ 
            List<DayShiftExcelVo> exportList = new LinkedList<>();
            List<Map<String, Object>> allTrackSafeQualityList = bureauDayShiftMapper.getAllTrackSafeQualityList(bureauDayShiftVo);
            //封装数据
            allTrackSafeQualityList.forEach(map -> { 
                String basicInformation="单位："+map.get("unitdeptname")+"\n"+
                        "线别："+map.get("lineName")+"\n"+
                        "所亭："+map.get("bdsSubstationName")+"\n"+
                        "开关号："+map.get("switchNo")+"\n"+
                        "故障地点："+DateUtil.formatDateString(map.get("stopDate"), DateUtil.DATE_FORMAT)+"\n"+
                        "发生时间："+map.get("unitdeptname")+"\n"+
                        "停时（分钟）："+map.get("unitdeptname")+"\n"+
                        "天气："+map.get("unitdeptname")+"\n"+
                        "专业分类："+map.get("unitdeptname")+"\n";
                String segmentAnalysis="单位："+map.get("unitdeptname")+"\n"+
                        "单位：详见分析报告"+"\n";
                String isTrack="";
                if(bureauDayShiftVo.getTrackFlag()==0){ 
                    isTrack="否";
                }else{ 
                    isTrack="是";
                }
                String review="科室："+map.get("trackunitdeptname")+"\n"+
                        "问题类别："+map.get("faultCategoryConfigName")+"\n"+
                        "定责考核："+map.get("dutyType")+"\n"+
                        "审核结果："+map.get("switchNo")+"\n"+
                        "重点追踪："+isTrack+"\n";
                DayShiftExcelVo dayShiftExcelVo=new DayShiftExcelVo(
                        DateUtil.formatDateString(map.get("inputDate"), DateUtil.DATE_FORMAT),
                        basicInformation,
                        (String)map.get("faultDescription"),
                        (String)map.get("reporttype"),
                        segmentAnalysis,
                        review,
                        map.get("safeQualityState").toString(),
                        String.valueOf(bureauDayShiftVo.getTrackFlag()));
                exportList.add(dayShiftExcelVo);
            });
    
            ExportParams exportParams = new ExportParams();
            //设置边框样式
            exportParams.setStyle(ExcelStyleType.BORDER.getClazz());
            // 设置sheet的名称
            if(bureauDayShiftVo.getTrackFlag()==0){ 
                exportParams.setSheetName("日安全质量信息");
            }else{ 
                exportParams.setSheetName("供电处重点追踪信息");
            }
    
            Map<String, Object> map = new HashMap<>();
            // title的参数为ExportParams类型，目前仅仅在ExportParams中设置了sheetName
            map.put("title", exportParams);
            // 模版导出对应得实体类型，即包含了List的对象
            map.put("entity", DayShiftExcelVo.class);
            // sheet中要填充得数据
            map.put("data", exportList);
            return map;
        }
    
    
        /** * @Author Zhongks * @Description //TODO 返回天窗兑现统计excel信息 * @Date 10:59 2021/5/8 * @Param [] * @return java.util.Map<java.lang.String,java.lang.Object> **/
        public Map<String, Object> getSkylightCashStatisticsMap(){ 
            List<SkylightCashStatisticsExcelVo> exportList = new LinkedList<>();
    
            //ToDo 得到天窗兑现统计列表数据并进行封装
            //示例数据
            SkylightCashStatisticsCommonExcelVo applicationExcelVo=new SkylightCashStatisticsCommonExcelVo("申请供电类","申请非供电类");
            SkylightCashStatisticsCommonExcelVo applicationTimeExcelVo=new SkylightCashStatisticsCommonExcelVo("申请时间供电类","申请时间非供电类");
            SkylightCashStatisticsCommonExcelVo getTimeExcelVo=new SkylightCashStatisticsCommonExcelVo("给点时间供电类","给点时间非供电类");
            SkylightCashStatisticsCommonExcelVo workTimeExcelVo=new SkylightCashStatisticsCommonExcelVo("作业时间供电类","作业时间非供电类");
            SkylightCashStatisticsExcelVo skylightCashStatisticsExcelVo=new SkylightCashStatisticsExcelVo("怀化供电段","高铁","沪昆高速线",
                    applicationExcelVo,"取消","10","10",applicationTimeExcelVo,getTimeExcelVo,workTimeExcelVo,"天窗取消原因");
            exportList.add(skylightCashStatisticsExcelVo);
            exportList.add(skylightCashStatisticsExcelVo);
            exportList.add(skylightCashStatisticsExcelVo);
    
            //供电处重点追踪信息表
            ExportParams exportParams = new ExportParams();
            //设置边框样式
            exportParams.setStyle(ExcelStyleType.BORDER.getClazz());
            // 设置sheet的名称
            exportParams.setSheetName("天窗兑现统计");
    
            Map<String, Object> map = new HashMap<>();
            // title的参数为ExportParams类型，目前仅仅在ExportParams中设置了sheetName
            map.put("title", exportParams);
            // 模版导出对应得实体类型，即包含了List的对象
            map.put("entity", SkylightCashStatisticsExcelVo.class);
            // sheet中要填充得数据
            map.put("data", exportList);
            return map;
        }

bureauDayShiftMapper.getAllTrackSafeQualityList(bureauDayShiftVo) 数据返回示例：

{ 
        "data": [
            { 
                "dutyType": null,
                "unitdeptname": "长沙供电段",
                "faultPlace": "故障地点",
                "reporttype": null,
                "faultmajorname": "接触网",
                "safeQualityState": 0,
                "faultCategoryConfigName": null,
                "lineName": "南广铁路",
                "stopMinute": null,
                "weatherInfo": null,
                "inputDate": 1620460879000,
                "archiveFiles": [],
                "trackunitdeptname": "安全科",
                "bdsSubstationName": null,
                "faultDescription": "故障概况\n",
                "id": "1390912864588001281",
                "stopDate": 1619798400000,
                "switchNo": null
            }
        ],
        "message": "操作成功",
        "status": 200
    }

excel实现换行说明：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDAwOTQ0Nw_size_16_color_FFFFFF_t_70 5]

### 控制器： ###

@ApiOperation("导出供电安全质量日交班表")
        @GetMapping("/export")
        public AjaxJson export(BureauDayShiftVo query, HttpServletResponse response) { 
            bureauDayShiftService.export(query,response);
            return AjaxJson.success();
        }

## 三。Excel导出效果 ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDAwOTQ0Nw_size_16_color_FFFFFF_t_70 6]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDAwOTQ0Nw_size_16_color_FFFFFF_t_70 7]

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDAwOTQ0Nw_size_16_color_FFFFFF_t_70 8]

[http_doc.wupaas.com_docs_easypoi_easypoi-1c0u4mo8p4ro8]: http://doc.wupaas.com/docs/easypoi/easypoi-1c0u4mo8p4ro8
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDAwOTQ0Nw_size_16_color_FFFFFF_t_70]: /images/20221021/731146bd4d4a4ffdb06c5bf1e43c603e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDAwOTQ0Nw_size_16_color_FFFFFF_t_70 1]: /images/20221021/42232ce6afa34556b454c7090f9641a6.png
[20210508155244821.png]: /images/20221021/9e6343b507404721978fdd5ea9ab84d1.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDAwOTQ0Nw_size_16_color_FFFFFF_t_70 2]: /images/20221021/796936a44aa047fcb461bc0ee95920f3.png
[20210508161800115.png]: /images/20221021/896337e74dd64f6c9d83b53178ecda51.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDAwOTQ0Nw_size_16_color_FFFFFF_t_70 3]: /images/20221021/84ae2afe7b804745938ddba3bd4d9a9f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDAwOTQ0Nw_size_16_color_FFFFFF_t_70 4]: /images/20221021/f1acd7ebd45443c0bf08651794256ff1.png
[2021050816192613.png]: /images/20221021/c1840e12afb5403e948619dc1dadd5aa.png
[20210508162059943.png]: /images/20221021/12c08afd19eb4bd986fe3ba282ffa339.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDAwOTQ0Nw_size_16_color_FFFFFF_t_70 5]: /images/20221021/629b1632a7e34826bf2f3ba95911e8b0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDAwOTQ0Nw_size_16_color_FFFFFF_t_70 6]: /images/20221021/21205f4c0d7e4ad5b26775f8d173d0fa.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDAwOTQ0Nw_size_16_color_FFFFFF_t_70 7]: /images/20221021/e86f6c4984b145feb28ef9034d596b41.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDAwOTQ0Nw_size_16_color_FFFFFF_t_70 8]: /images/20221021/fa45e2e6d73d4429ac09dd57b08c79c4.png