easyexcel的动态表头和自定义转换器

https://www.yuque.com/easyexcel/doc/easyexcel

依赖

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

普通web示例

	/**
	 * 文件下载并且失败的时候返回json（默认失败了会返回一个有部分数据的Excel）
	 */
	@GetMapping("/v1/import")
	public void downloadFailedUsingJson(HttpServletResponse response,FeedbackQuery feedbackQuery) throws IOException {


		//查询数据
		List<Feedback> list = feedbackService.listFeedbacks(feedbackQuery);

		List<FeedbackImportVo> voList = list.stream().map(feedback -> {
			FeedbackImportVo src = new FeedbackImportVo();
			BeanUtils.copy(feedback,src);
			return src;
		}).collect(Collectors.toList());
		// 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman
		try {
			response.setContentType("application/vnd.ms-excel");
			response.setCharacterEncoding("utf-8");
			// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
			String fileName = URLEncoder.encode(String.format("用户反馈记录-%s", DateUtil.format(new Date(),"yyyy-MM-dd")), "UTF-8").replaceAll("\\+", "%20");
			response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
			// 这里需要设置不关闭流
			EasyExcel.write(response.getOutputStream(), FeedbackImportVo.class).autoCloseStream(Boolean.FALSE).sheet("用户反馈记录")
					.doWrite(voList);
		} catch (Exception e) {
			// 重置response
			response.reset();
			response.setContentType("application/json");
			response.setCharacterEncoding("utf-8");
			Map<String, String> map = new HashMap<String, String>();
			map.put("status", "failure");
			map.put("message", "下载文件失败" + e.getMessage());
			response.getWriter().println(JSON.toJSONString(map));
		}
	}

实体(用了自定义转换器)

/**
 * @Auther: wxy
 * @Date: 2020/11/11 16:50
 * @Description:
 */
@Data
public class FeedbackImportVo implements Serializable {


    private static final long serialVersionUID = 8635674721036495183L;


    /**
     * 创建时间-反馈时间
     */
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    @ExcelProperty(index = 0, value = "反馈时间", converter = LocalDateTimeConverter.class)
    private LocalDateTime createTs;

    /**
     * 处理时间
     */
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    @ExcelProperty(index = 1, value = "处理时间", converter = LocalDateTimeConverter.class)
    private LocalDateTime updateTs;

    /**
     * 类别
     */
    @ExcelProperty(index = 2, value = "类别")
    private String catalogName;

    /**
     * 反馈人uid
     */
    @ExcelProperty(index = 3, value = "反馈人id", converter = LongStringConverter.class)
    private Long uid;

    /**
     * 反馈人昵称
     */
    @ExcelProperty(index = 4, value = "反馈人昵称")
    private String nickName;

    /**
     * 反馈人是否是vip 0=否|1=是
     */
    @ExcelProperty(index = 5, value = "vip")
    private Integer isVip;

    /**
     * 反馈人联系方式:手机号
     */
    @ExcelProperty(index = 6, value = "反馈人联系方式")
    private String mobile;

    /**
     * 反馈内容
     */
    @ExcelProperty(index = 7, value = "反馈内容")
    private String content;

    /**
     * 客户端
     */
    @ExcelProperty(index = 8, value = "客户端")
    private String deviceType;

    /**
     * 版本号
     */
    @ExcelProperty(index = 9, value = "版本号")
    private String version;


    /**
     * 渠道号
     */
    @ExcelProperty(index = 10, value = "渠道号")
    private String channel;

    /**
     * 手机型号
     */
    @ExcelProperty(index = 11, value = "手机型号")
    private String phoneModel;


    /**
     * 上传图片,最多5张
     */
    @ExcelProperty(index = 12, value = "图片")
    private String images;

    /**
     * 反馈状态：0=未处理|1=处理中|2=已处理 |3=暂不处理
     */
    @ExcelProperty(index = 13, value = "状态", converter = StatusConverter.class)
    private Integer status;

}

自定义转换器

package cn.jmfen.sport.backend.converter;

import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.property.ExcelContentProperty;

import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;

/**
 * @Auther: wxy
 * @Date: 2020/11/11 17:45
 * @Description:
 */
public class LocalDateTimeConverter implements Converter<LocalDateTime> {
    /**
     * java类型
     *
     * @return
     */
    @Override
    public Class<LocalDateTime> supportJavaTypeKey() {
        return LocalDateTime.class;
    }

    /**
     * excel数据类型
     *
     * @return
     */
    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.STRING;
    }

    /**
     * 导入
     * @param cellData
     * @param excelContentProperty
     * @param globalConfiguration
     * @return
     * @throws Exception
     */
    @Override
    public LocalDateTime convertToJavaData(CellData cellData, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        return LocalDateTime.parse(cellData.getStringValue(), DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
    }

    /**
     * 导出
     * @param value
     * @param contentProperty
     * @param globalConfiguration
     * @return
     */
    @Override
    public CellData<String> convertToExcelData(LocalDateTime value, ExcelContentProperty contentProperty,
                                               GlobalConfiguration globalConfiguration) {
        return new CellData<>(value.format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")));
    }
}

package cn.jmfen.sport.backend.converter;

import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.property.ExcelContentProperty;

/**
 * @Auther: wxy
 * @Date: 2020/11/11 18:01
 * @Description:
 */
public class StatusConverter implements Converter<Integer> {
    @Override
    public Class<Integer> supportJavaTypeKey() {
        return Integer.class;
    }

    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.STRING;
    }

    @Override
    public Integer convertToJavaData(CellData cellData, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        return null;
    }

    @Override
    public CellData convertToExcelData(Integer integer, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        return new CellData(StatusEnum.getValue(integer));
    }

    enum StatusEnum {
        //反馈状态：0=未处理|1=处理中|2=已处理 |3=暂不处理
        UNTREATED(0, "未处理"),
        PROCESSING(1, "处理中"),
        PROCESSED(2, "已处理"),
        NOT_FOR_THE_TIME_BEING(3, "暂不处理");
        private Integer code;
        private String value;

        StatusEnum(Integer code, String value) {
            this.code = code;
            this.value = value;
        }

        public static String getValue(Integer code) {
            StatusEnum[] values = values();
            for (StatusEnum value : values) {
                if (value.code.equals(code)) {
                    return value.value;
                }
            }
            return "";
        }
    }

}

动态表头

    /**
     * 根据参数只导出指定列
     * <p>
     * 1. 创建excel对应的实体对象 参照{@link DemoData}
     * <p>
     * 2. 根据自己或者排除自己需要的列
     * <p>
     * 3. 直接写即可
     *
     * @since 2.1.1
     */
    @Test
    public void excludeOrIncludeWrite() {
        String fileName = TestFileUtil.getPath() + "excludeOrIncludeWrite" + System.currentTimeMillis() + ".xlsx";

        // 根据用户传入字段 假设我们要忽略 date
        Set<String> excludeColumnFiledNames = new HashSet<String>();
        excludeColumnFiledNames.add("date");
        // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
        EasyExcel.write(fileName, DemoData.class).excludeColumnFiledNames(excludeColumnFiledNames).sheet("模板")
            .doWrite(data());

        fileName = TestFileUtil.getPath() + "excludeOrIncludeWrite" + System.currentTimeMillis() + ".xlsx";
        // 根据用户传入字段 假设我们只要导出 date
        Set<String> includeColumnFiledNames = new HashSet<String>();
        includeColumnFiledNames.add("date");
        // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
        EasyExcel.write(fileName, DemoData.class).includeColumnFiledNames(includeColumnFiledNames).sheet("模板")
            .doWrite(data());
    }