使用EasyExcel导出Excel指定单元格格式

简介：在日常导出excel时导出日期格式一般都是将日期转换成yyyy-MM-dd格式的字符串，很少有情况需要关注单元格格式，但是在特殊需求下，我们仍然需要注意单元格格式。

1.正常导出EXCEL

public class FourListExcelDto {

    @DateTimeFormat("yyyy/M/dd")
    @ExcelProperty({"时间"})
    private Date exportDate;
    
}

在此种情况下导出的excel，虽然单元格中显示的数据是时间类型但是单元格本身还是常规类型的

2.导出日期单元格格式

在EasyExcel官方文档上查询无果后，又去官方的钉钉群中询问也是杳无音讯，随后开始查看EasyExcel和poi的源码，发现在EasyExcel中的ContentStyle注解上有一个dataFormat参数

/**
 * Custom content styles
 *
 * @author Jiaju Zhuang
 */
@Target({ElementType.FIELD, ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
@Inherited
public @interface ContentStyle {
    /**
     * Set the data format (must be a valid format). Built in formats are defined at {@link BuiltinFormats}.
     */
    short dataFormat() default -1;
	//...

该参数需要传入一个short值，根据源码上的注释查看BuiltinFormats类中发现，该short值其实是自定义数组的下标

public final class BuiltinFormats {
    public static final int FIRST_USER_DEFINED_FORMAT_INDEX = 164;
    private static final String[] _formats = new String[]{"General", "0", "0.00", "#,##0", "#,##0.00", "\"$\"#,##0_);(\"$\"#,##0)", "\"$\"#,##0_);[Red](\"$\"#,##0)", "\"$\"#,##0.00_);(\"$\"#,##0.00)", "\"$\"#,##0.00_);[Red](\"$\"#,##0.00)", "0%", "0.00%", "0.00E+00", "# ?/?", "# ??/??", "m/d/yy", "d-mmm-yy", "d-mmm", "mmm-yy", "h:mm AM/PM", "h:mm:ss AM/PM", "h:mm", "h:mm:ss", "m/d/yy h:mm", "reserved-0x17", "reserved-0x18", "reserved-0x19", "reserved-0x1A", "reserved-0x1B", "reserved-0x1C", "reserved-0x1D", "reserved-0x1E", "reserved-0x1F", "reserved-0x20", "reserved-0x21", "reserved-0x22", "reserved-0x23", "reserved-0x24", "#,##0_);(#,##0)", "#,##0_);[Red](#,##0)", "#,##0.00_);(#,##0.00)", "#,##0.00_);[Red](#,##0.00)", "_(* #,##0_);_(* (#,##0);_(* \"-\"_);_(@_)", "_(\"$\"* #,##0_);_(\"$\"* (#,##0);_(\"$\"* \"-\"_);_(@_)", "_(* #,##0.00_);_(* (#,##0.00);_(* \"-\"??_);_(@_)", "_(\"$\"* #,##0.00_);_(\"$\"* (#,##0.00);_(\"$\"* \"-\"??_);_(@_)", "mm:ss", "[h]:mm:ss", "mm:ss.0", "##0.0E+0", "@"};

如果需要指定类型，则传入指定类型的下标。
随后修改实体类上的注解

public class FourListExcelDto {

	@ContentStyle(dataFormat = 14)
    @DateTimeFormat("yyyy/M/dd")
    @ExcelProperty({"时间"})
    private Date exportDate;
    
}

再导出EXCEL后查看该列数据，为正确的日期格式