easyexce时区国际化处理

导出excel

String targetTimeZoneId = StringUtilsPlus.defaultIfBlank(TimeZoneIdContext.getTimeZoneId().get(),ZoneIdEnum.CTT.getZoneIdName());

EasyExcel.write(file, Excel.class)

        .registerConverter(new EasyExcelDateTimeZoneStringConverter(ZoneIdEnum.CTT.getZoneIdName(),targetTimeZoneId))
        .registerConverter(new EasyExcelDateDateConverter(ZoneIdEnum.CTT.getZoneIdName(),targetTimeZoneId))
        .registerConverter(new EasyExcelDateNumberConverter(ZoneIdEnum.CTT.getZoneIdName(),targetTimeZoneId))
        .registerConverter(new EasyExcelLocalDateTimeZoneStringConverter(ZoneIdEnum.CTT.getZoneIdName(),targetTimeZoneId))
        .registerConverter(new EasyExcelLocalDateTimeNumberConverter(ZoneIdEnum.CTT.getZoneIdName(),targetTimeZoneId))
        .registerConverter(new EasyExcelLocalDateTimeDateConverter(ZoneIdEnum.CTT.getZoneIdName(),targetTimeZoneId))
         
          .sheet("测试时区转化").doWrite(excelList);

导入excel

String sourceTimeZoneId = StringUtilsPlus.defaultIfBlank(TimeZoneIdContext.getTimeZoneId().get(),ZoneIdEnum.CTT.getZoneIdName());
//读取
 ExcelDataListener excelDataListener = new ExcelDataListener();

 EasyExcel.read(read,Excel.class,excelDataListener)
 
         .registerConverter(new EasyExcelDateNumberConverter(sourceTimeZoneId,ZoneIdEnum.CST.getZoneIdName()))
         .registerConverter(new EasyExcelDateTimeZoneStringConverter(sourceTimeZoneId,ZoneIdEnum.CTT.getZoneIdName()))
        .registerConverter(new EasyExcelLocalDateTimeZoneStringConverter(sourceTimeZoneId,ZoneIdEnum.CTT.getZoneIdName()))
         .registerConverter(new EasyExcelLocalDateTimeNumberConverter(sourceTimeZoneId,ZoneIdEnum.CTT.getZoneIdName()))
       
         .doReadAll();

Date 解析器

public class EasyExcelDateDateConverter extends DateDateConverter {
    /**
     * 来源时区
     */
    private String sourerTimeZoneId;
    /**
     * 目标时区
     */
    private String targetTimeZoneId;
    
    public EasyExcelDateDateConverter(String sourerTimeZoneId,String targetTimeZoneId) {
        super();
        this.sourerTimeZoneId = sourerTimeZoneId;
        this.targetTimeZoneId = targetTimeZoneId;
    }
    @Override
    public WriteCellData<?> convertToExcelData(
            Date value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        String format = null;
        if (contentProperty != null && contentProperty.getDateTimeFormatProperty() != null) {
            format = contentProperty.getDateTimeFormatProperty().getFormat();
        }else{
            format = DateUtils.defaultDateFormat;
        }
        value = TimeZoneUtil.convertTimeZoneDate(value, this.sourerTimeZoneId,this.targetTimeZoneId,format);
        WriteCellData<?> cellData = new WriteCellData(value);
       
        
        WorkBookUtil.fillDataFormat(cellData, format, DateUtils.defaultDateFormat);
        return cellData;
    }
}

public class EasyExcelDateNumberConverter extends DateNumberConverter {
    /**
     * 来源时区
     */
    private String sourerTimeZoneId;
    /**
     * 目标时区
     */
    private String targetTimeZoneId;
    
    public EasyExcelDateNumberConverter(String sourerTimeZoneId,String targetTimeZoneId) {
        super();
        this.sourerTimeZoneId = sourerTimeZoneId;
        this.targetTimeZoneId = targetTimeZoneId;
    }
    @Override
    public Date convertToJavaData(
            ReadCellData<?> cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) {
        Date date = contentProperty != null && contentProperty.getDateTimeFormatProperty() != null ? DateUtils.getJavaDate(cellData.getNumberValue().doubleValue(), contentProperty.getDateTimeFormatProperty().getUse1904windowing()) : DateUtils.getJavaDate(cellData.getNumberValue().doubleValue(), globalConfiguration.getUse1904windowing());
        String format =  contentProperty != null && contentProperty.getDateTimeFormatProperty() != null ?contentProperty.getDateTimeFormatProperty().getFormat():DateUtils.DATE_FORMAT_19;
        return TimeZoneUtil.convertTimeZoneDate(date, this.sourerTimeZoneId,this.targetTimeZoneId,format);
    }
    @Override
    public WriteCellData<?> convertToExcelData(Date value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) {
        String format =  contentProperty != null && contentProperty.getDateTimeFormatProperty() != null ?contentProperty.getDateTimeFormatProperty().getFormat():DateUtils.DATE_FORMAT_19;
        value = TimeZoneUtil.convertTimeZoneDate(value, this.sourerTimeZoneId,this.targetTimeZoneId,format);
        return contentProperty != null && contentProperty.getDateTimeFormatProperty() != null ? new WriteCellData(
                BigDecimal.valueOf(DateUtil.getExcelDate(value, contentProperty.getDateTimeFormatProperty().getUse1904windowing()))) : new WriteCellData(BigDecimal.valueOf(DateUtil.getExcelDate(value, globalConfiguration.getUse1904windowing())));
    }
}

public class EasyExcelDateTimeZoneStringConverter extends DateStringConverter {
    
    /**
     * 来源时区
     */
   private String sourerTimeZoneId;
    /**
     * 目标时区
     */
   private String targetTimeZoneId;
    
    public EasyExcelDateTimeZoneStringConverter(String sourerTimeZoneId,String targetTimeZoneId) {
        super();
        this.sourerTimeZoneId = sourerTimeZoneId;
        this.targetTimeZoneId = targetTimeZoneId;
    }
    
    /**
     * 时间值转化String
     * @param value
     * @param contentProperty
     * @param globalConfiguration
     * @return
     */
    @Override
    public WriteCellData<?> convertToExcelData(Date value, ExcelContentProperty contentProperty,
            GlobalConfiguration globalConfiguration) {
        String format =  contentProperty != null && contentProperty.getDateTimeFormatProperty() != null ?contentProperty.getDateTimeFormatProperty().getFormat():DateUtils.DATE_FORMAT_19;
        String dateValue =  DateUtils.format(value, format);
        dateValue = TimeZoneUtil.convertTimeZoneDateStr(dateValue, this.sourerTimeZoneId,this.targetTimeZoneId,format);
        return  new WriteCellData(dateValue);
    }
    
    /**
     * 将单元格数据转换为Java日期类型
     * 此方法重写自父类，用于具体处理日期类型的转换
     *
     * @param cellData 单元格数据，包含从Excel单元格中读取的数据信息
     * @param contentProperty Excel内容属性，用于处理与内容相关的属性
     * @param globalConfiguration 全局配置，包含整个Excel读取过程的全局设置
     * @return Date 转换后的Java日期对象
     * @throws ParseException 如果转换过程中出现解析异常
     */
    @Override
    public Date convertToJavaData(ReadCellData<?> cellData, ExcelContentProperty contentProperty,
            GlobalConfiguration globalConfiguration)  {
        String format =  contentProperty != null && contentProperty.getDateTimeFormatProperty() != null ?contentProperty.getDateTimeFormatProperty().getFormat():DateUtils.DATE_FORMAT_19;
        String dateValue =  cellData.getStringValue();
        Date date = TimeZoneUtil.convertTimeZoneDate(dateValue, this.sourerTimeZoneId,this.targetTimeZoneId,format);
        return date;
    }
}

LocalDateTime 解析器

public class EasyExcelLocalDateTimeDateConverter extends LocalDateTimeDateConverter {
    /**
     * 来源时区
     */
    private String sourerTimeZoneId;
    /**
     * 目标时区
     */
    private String targetTimeZoneId;
    
    public EasyExcelLocalDateTimeDateConverter(String sourerTimeZoneId,String targetTimeZoneId) {
        super();
        this.sourerTimeZoneId = sourerTimeZoneId;
        this.targetTimeZoneId = targetTimeZoneId;
    }
    @Override
    public WriteCellData<?> convertToExcelData(LocalDateTime value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        String format = null;
        if (contentProperty != null && contentProperty.getDateTimeFormatProperty() != null) {
            format = contentProperty.getDateTimeFormatProperty().getFormat();
        }else{
            format= DateUtils.defaultDateFormat;
        }
        value = TimeZoneUtil.convertTimeZoneLocalDateTime(value, this.sourerTimeZoneId,this.targetTimeZoneId);
        WriteCellData<?> cellData = new WriteCellData(value);
        WorkBookUtil.fillDataFormat(cellData, format, DateUtils.defaultDateFormat);
        return cellData;
    }
}

public class EasyExcelLocalDateTimeNumberConverter extends LocalDateTimeNumberConverter {
    /**
     * 来源时区
     */
    private String sourerTimeZoneId;
    /**
     * 目标时区
     */
    private String targetTimeZoneId;
    
    public EasyExcelLocalDateTimeNumberConverter(String sourerTimeZoneId,String targetTimeZoneId) {
        super();
        this.sourerTimeZoneId = sourerTimeZoneId;
        this.targetTimeZoneId = targetTimeZoneId;
    }
    @Override
    public LocalDateTime convertToJavaData(ReadCellData<?> cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) {
        LocalDateTime localDateTime = contentProperty != null && contentProperty.getDateTimeFormatProperty() != null ? DateUtils.getLocalDateTime(cellData.getNumberValue().doubleValue(), contentProperty.getDateTimeFormatProperty().getUse1904windowing()) : DateUtils.getLocalDateTime(cellData.getNumberValue().doubleValue(), globalConfiguration.getUse1904windowing());
        return TimeZoneUtil.convertTimeZoneLocalDateTime(localDateTime, sourerTimeZoneId,targetTimeZoneId);
    }
    @Override
    public WriteCellData<?> convertToExcelData(LocalDateTime value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) {
        value = TimeZoneUtil.convertTimeZoneLocalDateTime(value, sourerTimeZoneId,targetTimeZoneId);
        return contentProperty != null && contentProperty.getDateTimeFormatProperty() != null ? new WriteCellData(
                BigDecimal.valueOf(DateUtil.getExcelDate(value, contentProperty.getDateTimeFormatProperty().getUse1904windowing()))) : new WriteCellData(BigDecimal.valueOf(DateUtil.getExcelDate(value, globalConfiguration.getUse1904windowing())));
    }
}

public class EasyExcelLocalDateTimeZoneStringConverter extends LocalDateTimeStringConverter {
    /**
     * 来源时区
     */
    private String sourerTimeZoneId;
    /**
     * 目标时区
     */
    private String targetTimeZoneId;
    
    public EasyExcelLocalDateTimeZoneStringConverter(String sourerTimeZoneId,String targetTimeZoneId) {
        super();
        this.sourerTimeZoneId = sourerTimeZoneId;
        this.targetTimeZoneId = targetTimeZoneId;
    }
    
    /**
     * 将单元格数据转换为Java LocalDateTime对象
     * 此方法主要用于处理Excel单元格中的日期时间数据，将其转换为Java的LocalDateTime对象
     * 它考虑了时区的转换，以确保日期时间的准确性
     *
     * @param cellData 单元格数据，包含要转换的日期时间字符串
     * @param contentProperty Excel内容属性，未在此方法中使用，但可能在将来或在实现的接口中需要
     * @param globalConfiguration 全局配置，提供有关如何处理Excel数据的配置信息
     * @return LocalDateTime对象，表示转换后的日期和时间如果输入为null，则返回null
     * @throws Exception 如果转换过程中发生错误，则抛出异常
     */
    @Override
    public LocalDateTime convertToJavaData(ReadCellData<?> cellData, ExcelContentProperty contentProperty,
            GlobalConfiguration globalConfiguration)  {
        // 检查输入的单元格数据是否为null
        if(cellData==null){
            // 如果单元格数据为null，则返回null
            return  null;
        }
        // 使用时区工具类转换日期时间字符串考虑到源时区和目标时区，以获得正确的LocalDateTime对象
        LocalDateTime localDateTime  = TimeZoneUtil.convertTimeZoneLocalDateTime(cellData.getStringValue(), this.sourerTimeZoneId,this.targetTimeZoneId);
        // 返回转换后的LocalDateTime对象
        return localDateTime;
    }
    
    /**
     * 将LocalDateTime类型的值转换为Excel中的单元格数据
     * 此方法主要用于处理日期时间数据在Java对象与Excel表格之间的转换和格式化
     *
     * @param value 需要转换的LocalDateTime值如果为null，则返回包含null值的WriteCellData对象
     * @param contentProperty Java对象中的Excel内容属性，用于获取日期时间的格式信息
     * @param globalConfiguration 全局配置，尚未使用，但可能在未来扩展中用于控制转换行为
     * @return 返回一个WriteCellData对象，包含格式化后的日期时间字符串
     * @throws Exception 如果转换过程中发生错误，则抛出异常
     */
    @Override
    public WriteCellData<?> convertToExcelData(LocalDateTime value, ExcelContentProperty contentProperty,
            GlobalConfiguration globalConfiguration)  {
        // 如果输入值为null，直接返回包含null值的WriteCellData对象，无需进一步处理
        if(value==null){
            return  new WriteCellData(value);
        }
        // 根据contentProperty中的配置获取日期时间格式，如果没有配置或配置为空，则使用默认格式
        String format =  contentProperty != null && contentProperty.getDateTimeFormatProperty() != null ?contentProperty.getDateTimeFormatProperty().getFormat():DateUtils.DATE_FORMAT_19;
        // 转换时区，将源时区的LocalDateTime值转换为目标时区的LocalDateTime值
        value = TimeZoneUtil.convertTimeZoneLocalDateTime(value, this.sourerTimeZoneId,this.targetTimeZoneId);
        // 使用DateTimeFormatter根据指定的格式将转换后的LocalDateTime值格式化为字符串，并包装到WriteCellData对象中返回
        return  new WriteCellData(value.format(DateTimeFormatter.ofPattern(format)));
    }
}