EasyExcel使用方式（包含导出图片）

最新推荐文章于 2024-08-21 16:59:13 发布

-隐函数-

最新推荐文章于 2024-08-21 16:59:13 发布

阅读量876

点赞数

文章标签： java excel

本文链接：https://blog.csdn.net/weixin_56777219/article/details/134004314

版权

1、导EasyExcel依赖

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

2、创建导出excel的实体类

@Getter
@Setter
@EqualsAndHashCode
@HeadStyle(fillForegroundColor = 9)
@HeadFontStyle(fontHeightInPoints = 12, bold = BooleanEnum.TRUE)       //头字体   加粗
@HeadRowHeight(31)
@ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER, verticalAlignment = VerticalAlignmentEnum.CENTER,borderTop = BorderStyleEnum.THIN,borderBottom = BorderStyleEnum.THIN,borderLeft = BorderStyleEnum.THIN,borderRight = BorderStyleEnum.THIN) //水平居中  垂直居中  上 下 左 右（边框线）
@ContentFontStyle(fontHeightInPoints = 11)    //内容字体
@ContentRowHeight(123)

public class ExcelDto {
        @ExcelProperty(value = "序号", index = 0)  //意思为序号这一列为第0列（从0开始算）
        @ColumnWidth(14)
        private String number;
        @ExcelProperty(value = "姓名", index = 1)
        @ColumnWidth(18)
        private String name;
        @ExcelProperty(value = "时间", index = 2)
        @ColumnWidth(35)
        private Date time;
        @ColumnWidth(54)
        @ExcelProperty(value = "备注", index = 3)
        private String text;
        @ExcelProperty(value = "图片", index = 4)
        @ContentStyle(shrinkToFit = BooleanEnum.TRUE,borderTop = BorderStyleEnum.THIN,borderBottom = BorderStyleEnum.THIN,borderLeft = BorderStyleEnum.THIN,borderRight = BorderStyleEnum.THIN)  //上下左右（边框线）
        @ColumnWidth(43)
        private File image;  //file类型
}

3、代码进行逻辑赋值

controller

 /**
   * 导出excel
   */
    @GetMapping(value = "/exportExcel")
    public void exportExcel(HttpServletResponse response, HttpServletRequest request,T entity) {
        jsjtProblemLabelService.exportExcel(response, request,entity);
    }

service

    /**
     * 导出
     * @param response
     * @param request
     */
    public void exportExcel(HttpServletResponse response, HttpServletRequest request,T entity) {
        try {

            //创建导出excel的集合
            List<ExcelDto> list = ListUtils.newArrayList();
            
            //假设excelListBySQL 为数据库中查询数据的集合
            List<Excel> excelListBySQL = new ArrayList();
            
            //处理逻辑代码取出符合条件的值返回进excelListBySQL中 

            //循环赋值
            for (int i = 0; i < excelListBySQL.size(); i++) {

                //取出每一个从数据库中取出的对象
                Excel excel = excelListBySQL.get(i);

                //返回进导出excel的类中
                ExcelDto excelDto = new ExcelDto();

                //赋值序号
                excelDto.setNumber(String.valueOf(++number));
                //赋值姓名
                excelDto.setName(excel .getName());
                //赋值时间
                excelDto.setTime(excel .getTime());
                //赋值备注
                excelDto.setText(excel .getText());
                //赋值图片(数据库路径为相对路径)
                excelDto.setImage(imageByUrl(excel .getImageUrl())); 

                //将每一行对象添加进导出excel的集合中
                list.add(excelDto);
            }


            String fileName = URLEncoder.encode("EasyExcel导出.xlsx", "UTF-8");
            OutputStream fileOutputStream = null;
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
            response.setContentType("application/x-download");
            response.setCharacterEncoding("UTF-8");
            response.addHeader("Pargam", "no-cache");
            response.addHeader("Cache-Control", "no-cache");
            response.flushBuffer();
            fileOutputStream = response.getOutputStream();
            ExcelWriter excelWriter = EasyExcel.write(fileOutputStream, ExcelDto.class)
                    .build();
            WriteSheet sheet = EasyExcel.writerSheet(0, "sheet1").build();  //控制sheet页
            excelWriter.write(list, sheet);
            excelWriter.finish();
            fileOutputStream.flush();
            fileOutputStream.close();

        } catch (Exception e) {
            e.getMessage();
        }

    }

    /**
      *将string类型的url转换为文件
      */
    public File imageByUrl(String imageUrl) throws Exception{
        if (StringUtils.isBlank(imageUrl)){
            return null;
        }
        //指定读取的图片文件
        URI uri = new URI("file:///" + path+"/"+imageUrl);
        File file = new File(uri);
        //存在返回file否则返回null
        return file.exists() ?  file :  null;  
    }