Springboot 使用EasyExcel导出Excel文件

Excel导出系列目录：
引入依赖
创建导出模板类
- 创建图片转化器
逻辑处理
- controller
- service
导出效果
遗留问题

Excel导出系列目录：

【Springboot 使用EasyExcel导出Excel文件】
【Springboot 使用POI导出Excel文件】
【Springboot 导出Excel文件方式对比与注意事项】

本文使用EasyExcel导出xlsx后缀的Excel文件。

引入依赖

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

创建导出模板类

导出模板类实质就是带有很多EasyExcel注解的实体类，以下类中以属性的形式列出所有excel列且对单元格样式以及字体样式都有设置。

@Data
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
@ContentRowHeight(120)
@AllArgsConstructor
@NoArgsConstructor
public class ExportRecord implements Serializable {@ExcelProperty(value = "序号", order = 1)@ContentFontStyle(fontName = "仿宋", fontHeightInPoints = 12)@ContentStyle(borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN,horizontalAlignment = HorizontalAlignmentEnum.CENTER, verticalAlignment = VerticalAlignmentEnum.CENTER, dataFormat = 49)@ColumnWidth(12)private Integer number;@ExcelProperty(value = "时间", order = 2)@ContentFontStyle(fontName = "仿宋", fontHeightInPoints = 12)@ContentStyle(borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN,horizontalAlignment = HorizontalAlignmentEnum.CENTER, verticalAlignment = VerticalAlignmentEnum.CENTER, dataFormat = 49)@ColumnWidth(20)private String alarmTime;@ExcelProperty(value = "照片",order = 3,converter = ImageConverter.class)@ContentStyle(borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN,dataFormat = 49)@ColumnWidth(17)private byte[] faceImageUrl;@ExcelProperty(value = "姓名", order = 4)@ContentFontStyle(fontName = "仿宋", fontHeightInPoints = 12)@ContentStyle(borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN,verticalAlignment = VerticalAlignmentEnum.CENTER, dataFormat = 49)@ColumnWidth(20)private String staffName;@ExcelProperty(value = "手机号码", order = 5)@ContentFontStyle(fontName = "仿宋", fontHeightInPoints = 12)@ContentStyle(borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN,horizontalAlignment = HorizontalAlignmentEnum.CENTER, verticalAlignment = VerticalAlignmentEnum.CENTER, dataFormat = 49)@ColumnWidth(20)private String staffPhone;@ExcelProperty(value = "备注信息", order = 6)@ContentFontStyle(fontName = "仿宋", fontHeightInPoints = 12)@ContentStyle(borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN,verticalAlignment = VerticalAlignmentEnum.CENTER, dataFormat = 49)@ColumnWidth(20)private String staffRemark;
}

注意：

@ContentStyle注解参数一定要有dataFormat = 49，否则不生效。
@ContentRowHeight指定行高的注解是修饰类的，不是修饰属性字段的。
ImageConverter类是专门用于处理图片的，具体原理我不清楚，也没专门研究，有兴趣的可以自己研究研究。

创建图片转化器

@Slf4j
public class ImageConverter implements Converter<byte[]> {@Overridepublic Class<?> supportJavaTypeKey() {return byte[].class;}@Overridepublic WriteCellData<?> convertToExcelData(byte[] value,ExcelContentProperty contentProperty,GlobalConfiguration globalConfiguration)  {if(ObjectUtil.isNotEmpty(value)){try {return new WriteCellData<>(value);}catch (Exception e){log.info("图片获取异常",e);}}return new WriteCellData<>("");}
}

逻辑处理

controller

@RestController
@Slf4j
@RequestMapping({"/v1/test"})
public class TestController {@Resourceprivate TestService testService;@PostMapping("/export_record")public void exportRecord(HttpServletResponse response, @RequestBody GetRecordDto dto) {testService.exportRecord(response, dto);log.info("/export_record 导出记录完毕");}
}

service

@Override
public void exportRecord(HttpServletResponse response, GetRecordDto dto) {try {List<ExportRecord> result = new ArrayList<>();// 查询数据List<GetRecordVo> recordInfoList = ...;int index = 0;for (GetRecordVo item : recordInfoList) {// 将图片转换成byte[]类型数据并为ExportRecord赋值byte[] faceImage = ...;result.add(new ExportRecord().setNumber(++ index).setAlarmTime(item.getAlarmTime()).setFaceImageUrl(faceImage).setStaffName(item.getStaffName()).setStaffPhone(item.getStaffPhone()).setStaffRemark(item.getStaffRemark()));}// 设置文件名以及响应头，导出文件String fileName = UUID.randomUUID().toString().replace("-", "") + "_记录.xlsx" ;response.setContentType("application/OCTET-STREAM;charset=UTF-8");String file = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");response.setHeader("Content-disposition", "attachment;filename=" + file);EasyExcel.write(response.getOutputStream(), ExportRecord.class).sheet("sheet1").doWrite(result);}catch (MixException e){throw e;}catch (Exception e){log.error("导出记录失败：", e);}
}