Springboot 使用EasyExcel导出Excel文件

Excel导出系列目录：

【Springboot 使用EasyExcel导出Excel文件】
【Springboot 使用EasyExcel导出含图片并设置样式的Excel文件】
【Springboot 使用POI导出Excel文件】
【Springboot 导出Excel文件方式对比与注意事项】

★★★★尤其注意：

WPS对xlsx后缀文件兼容性不好，如果样式要求高，需要兼容各种excel软件，那就导出xls后缀的文件。具体见【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;
}

注意：

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

创建图片转化器

@Slf4j
public class ImageConverter implements Converter<byte[]> {
    @Override
    public Class<?> supportJavaTypeKey() {
        return byte[].class;
    }
    @Override
    public 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 {
    @Resource
    private 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);
	}
}

导出效果

遗留问题

实现导出之后，发现图片是铺满整个单元格的，每张图片会覆盖住单元格的上边框，所以，我想在放有图片的单元格中加入内边距，但是这个我查了一天始终没有实现，网上的方案有很多但是我用上一直没有生效，最终我放弃了。

经过不懈的寻找，最终找到了EasyExcel导出图片设置边距的示例，具体见下一篇文章：
【Springboot 使用EasyExcel导出含图片并设置样式的Excel文件】