Springboot 使用easyexcel 实现Excel导出功能

不念不忘少年蓝@ 2022-11-21 01:11 258阅读 0赞

## *Springboot 使用easyexcel 实现Excel导出* ##

导入easyexcel依赖

<dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>easyexcel</artifactId>
                <version>2.1.3</version>
            </dependency>

封装类上面的 @ExcelProperty(value = “设备号”, index = 0)  
是excel的列名 和位置  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ0NjY0MzI5_size_16_color_FFFFFF_t_70_pic_center]代码

@ExcelProperty(value = "设备号", index = 0)

导出excel 控制层

/**历史任务  EXCEL导出功能
     * 参数   模糊查询  封装类
     * */
            @RequestMapping("/downloadEasyExcel")
            public void downloadEasyExcel(HttpServletResponse response,Task task) throws IOException {
                String fileName = URLEncoder.encode("历史任务列表", "UTF-8") + ConFig.newDate();
                response.setContentType("application/vnd.ms-excel");
                response.setCharacterEncoding("utf-8");
                response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
                // excel头策略
                WriteCellStyle headWriteCellStyle = new WriteCellStyle();
                WriteFont headWriteFont = new WriteFont();
                headWriteFont.setFontHeightInPoints((short) 11);
                headWriteFont.setBold(false);
                headWriteCellStyle.setWriteFont(headWriteFont);
                // excel内容策略
                WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
                WriteFont contentWriteFont = new WriteFont();
                contentWriteFont.setFontHeightInPoints((short)11);
                contentWriteCellStyle.setWriteFont(contentWriteFont);
                // 设置handler
                HorizontalCellStyleStrategy styleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
                /** 导出数据查询方法*/
                List<TaskExcel> tasks = taskService.selectHistoricalMissionEXCEL(task);
                EasyExcel.write(response.getOutputStream(), TaskExcel.class)
                        .sheet("下载excel服务")
                        .registerWriteHandler(styleStrategy)
                        .doWrite(tasks);
            }

在导出excel的时候经常会有这种需求 数据库里面存放的是数字 但是导出excel的时候不能显示数字  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ0NjY0MzI5_size_16_color_FFFFFF_t_70_pic_center 1]

在sql使用 CASE方法  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ0NjY0MzI5_size_16_color_FFFFFF_t_70_pic_center 2]  
代码

(CASE task_status WHEN '3' THEN '完成' end ) as task_status,

最后导出样式  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ0NjY0MzI5_size_16_color_FFFFFF_t_70_pic_center 3]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ0NjY0MzI5_size_16_color_FFFFFF_t_70_pic_center]: /images/20221120/c168b988c69f40358c1f579ad0704c5e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ0NjY0MzI5_size_16_color_FFFFFF_t_70_pic_center 1]: https://img-blog.csdnimg.cn/20201029114949701.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ0NjY0MzI5,size_16,color_FFFFFF,t_70#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ0NjY0MzI5_size_16_color_FFFFFF_t_70_pic_center 2]: https://img-blog.csdnimg.cn/20201029115108484.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ0NjY0MzI5,size_16,color_FFFFFF,t_70#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ0NjY0MzI5_size_16_color_FFFFFF_t_70_pic_center 3]: https://img-blog.csdnimg.cn/20201029115347741.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ0NjY0MzI5,size_16,color_FFFFFF,t_70#pic_center