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

最新推荐文章于 2024-09-29 02:39:06 发布

Keson Z

最新推荐文章于 2024-09-29 02:39:06 发布

阅读量8.6k

点赞数 3

分类专栏： # POI操作Excel表格导入导出文章标签： EasyPOI excel

本文链接：https://blog.csdn.net/weixin_44009447/article/details/116530324

版权

POI操作Excel表格导入导出专栏收录该内容

3 篇文章 1 订阅

订阅专栏

一。前言

官方Api文档地址：

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用法,这里就不累言了：
在这里插入图片描述

@ExcelCollection

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

@ExcelEntity

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

二。使用

导入依赖：

<!--easypoi 导入导出 -->
<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;
}

在这里插入图片描述
excel显示效果：

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

在这里插入图片描述
SkylightCashStatisticsCommonExcelVo：

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 ApplicationExcelVo
 * @Description TODO 天窗兑现统计 子集公共vo
 * @Author Zhongks
 * @Date 2021/5/8  10:11
 * @Version 1.0
 **/
@Data
@Accessors(chain = true)
@AllArgsConstructor
@ExcelTarget(value = "SkylightCashStatisticsExcelVo")
public class SkylightCashStatisticsCommonExcelVo {

    @Excel(name = "供电类", width = 20)
    private String powerSupply;

    @Excel(name = "非供电类", width = 20)
    private String nonPowerSupply;
}

在这里插入图片描述
excel显示效果：

3.使用@ExcelCollection显示效果：（由于实现原理一致，本文章不做示例）

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实现换行说明：
在这里插入图片描述

控制器：

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