Writing in the front

Exporting Excel is a frequently used function in the system. There are many solutions to implement, you can package Apache Poi yourself, or you can directly use the class libraries that others have already packaged. If the requirements are simple, it is possible to implement them yourself, and all bugs and features will be manageable. Using a third-party library is mainly convenient to avoid repeating the wheel, but the downside is that if bugs or features are found, it will be severely limited to iteration of the library version.

Exported data often contains time. If the time zone is not specified, the system uses the time zone of the server to format the time. As a result, the exported time may not be the expected time. Specifying the time zone is therefore an important feature. Since EasyExcel does not provide the ability to specify a time zone, you need to do it yourself.

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.1.2</version>
</dependency>
Copy the code

Implementation scheme

EasyExcel provides a great convenience for users by providing extendable features, especially allowing custom converters and listeners. This will be solved using the functionality of a custom converter. Because The Times in a table are generally in the same time zone, global time zones should be implemented and dynamic configuration should be supported rather than hard-coding a time zone into the code. In addition, a method is provided to set the time zone of the class’s Date type property.

The final result is as follows:

// io.gitlab.donespeak.tutorial.excel.easyexcel.timezone.DateTimeZoneConverterTest.TheDate
@Getter
@Setter
@ToString
@EqualsAndHashCode
@NoArgsConstructor
public static class TheDate {
    @DateTimeFormat("yyyy-MM-dd hh:mm:ss:SSS")
    @ExcelProperty(index = 0)
    private Date date;

    @DateTimeFormat("yyyy-MM-dd hh:mm:ss:SSS")
    @DateTimeZone("Asia/Tokyo")
    @ExcelProperty(index = 1)
    private Date jpDate;
}
Copy the code

The registerConverter method is recommended here to directly replace the default type converter in ExcelWriterBuilder and ExcelReaderBuilder. This can also be configured by specifying ExcelProperty.Converter, but it is a bit more cumbersome.

// Use US/Central to write the time in Excel
EasyExcel.write(file, TheDate.class)
    .registerConverter(new DateTimeZoneStringConverter(TIME_ZONE_ID_US_CENTRAL))
    .sheet("theDate").doWrite(listOriginal);

// Use US/Central to read the time in Excel
List<TheDate> listUsCentralWriteUsCentralRead = EasyExcel.read(file)
    .registerConverter(new DateTimeZoneStringConverter(TIME_ZONE_ID_US_CENTRAL))
    .head(TheDate.class).sheet().doReadSync();
Copy the code

Define the @dateTimeZone annotation

@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
@Inherited
public @interface DateTimeZone {

    /**
     * Specific value reference {@link TimeZone#getAvailableIDs()}
     */
    String value(a) default "";
}
Copy the code

This annotation specifies the time zone for a Date attribute.

Implement time zone converter: Date <-> String

import com.alibaba.excel.converters.date.DateStringConverter;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.property.ExcelContentProperty;

import java.text.ParseException;
import java.util.Date;

public class DateTimeZoneStringConverter extends DateStringConverter {

    private final String globalTimeZoneId;

    public DateTimeZoneStringConverter(a) {
        super(a); globalTimeZoneId =null;
    }

    public DateTimeZoneStringConverter(String timeZoneId) {
        super(a); globalTimeZoneId = timeZoneId; }@Override
    public Date convertToJavaData(CellData cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws ParseException {

        String timeZoneId = getTimeZoneId(contentProperty);
        String timeFormat = getTimeFormat(contentProperty);

        // System.out.println(String.format("%s: %s: %s", cellData.getStringValue(), timeFormat, timeZoneId));
        Date date = DateUtils.parseDate(cellData.getStringValue(), timeFormat , timeZoneId);
        return date;
    }

    @Override
    public CellData convertToExcelData(Date value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) {

        String timeZoneId = getTimeZoneId(contentProperty);
        String timeFormat = getTimeFormat(contentProperty);

        // System.out.println(String.format("%s: %s: %s", value, timeFormat, timeZoneId));
        String excelValue = DateUtils.format(value, timeFormat, timeZoneId);
        return new CellData(excelValue);
    }

    private String getTimeZoneId(ExcelContentProperty contentProperty) {
        if (contentProperty == null) {
            return null;
        }
        return DateTimeZoneUtil.getTimeZone(contentProperty.getField(), globalTimeZoneId);
    }

    private String getTimeFormat(ExcelContentProperty contentProperty) {
        if (contentProperty == null || contentProperty.getDateTimeFormatProperty() == null) {
            return null;
        }
        returncontentProperty.getDateTimeFormatProperty().getFormat(); }}Copy the code

Com. Alibaba. Excel. Converters. Date. Is DateStringConverter EasyExcel define converter used in the date of export as strings. In addition to convert the Date to the Number of converter com. Alibaba. Excel. Converters. Date. DateNumberConverter.

For convenience, DateTimeZoneStringConverter inherited DateStringConverter directly, and cover to convert the two methods of convertToJavaData () and convertToExcelData (). It looks like a lot of changes, but there are really no major changes, just a method to get time zones and TimeZone in SimpleDateFormat.

DateUtils here is rewritten DateUtils, the com EasyExcel. Alibaba. Excel. Util. DateUtils implementation does not support the TimeZone.

import com.alibaba.excel.util.StringUtils;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.TimeZone;

public class DateUtils {

    public static final String DATE_FORMAT_10 = "yyyy-MM-dd";
    public static final String DATE_FORMAT_14 = "yyyyMMddHHmmss";
    public static final String DATE_FORMAT_17 = "yyyyMMdd HH:mm:ss";
    public static final String DATE_FORMAT_19 = "yyyy-MM-dd HH:mm:ss";
    public static final String DATE_FORMAT_19_FORWARD_SLASH = "yyyy/MM/dd HH:mm:ss";
    private static final String MINUS = "-";

    private DateUtils(a) {
        throw new AssertionError("DateUtils can't be instantiated.");
    }

    /** * convert string to date */
    public static Date parseDate(String dateString, String dateFormat, String timeZone) throws ParseException {
        if (StringUtils.isEmpty(dateFormat)) {
            dateFormat = switchDateFormat(dateString);
        }
        SimpleDateFormat sdf = new SimpleDateFormat(dateFormat);
        if(! StringUtils.isEmpty(timeZone)) { sdf.setTimeZone(TimeZone.getTimeZone(timeZone)); }return sdf.parse(dateString);
    }

    /** * convert string to date */
    public static Date parseDate(String dateString) throws ParseException {
        return parseDate(dateString, switchDateFormat(dateString), null);
    }

    /** * switch date format */
    private static String switchDateFormat(String dateString) {
        int length = dateString.length();
        switch (length) {
            case 19:
                if (dateString.contains(MINUS)) {
                    return DATE_FORMAT_19;
                } else {
                    return DATE_FORMAT_19_FORWARD_SLASH;
                }
            case 17:
                return DATE_FORMAT_17;
            case 14:
                return DATE_FORMAT_14;
            case 10:
                return DATE_FORMAT_10;
            default:
                throw new IllegalArgumentException("can not find date format for："+ dateString); }}/** * Format date * 
 * yyyy-MM-dd HH:mm:ss */

    public static String format(Date date, String timeZone) {
        return format(date, null, timeZone);
    }

    /** * Format date ** When dateFormat is empty, the default value is YYYY-MM-DD HH: MM :ss */
    public static String format(Date date, String dateFormat, String timeZone) {
        if (date == null) {
            return "";
        }
        if (StringUtils.isEmpty(dateFormat)) {
            dateFormat = DATE_FORMAT_19;
        }
        SimpleDateFormat sdf = new SimpleDateFormat(dateFormat);
        if(! StringUtils.isEmpty(timeZone)) { sdf.setTimeZone(TimeZone.getTimeZone(timeZone)); }returnsdf.format(date); }}Copy the code

Encapsulates the methods obtained by TimeZone separately.

import com.alibaba.excel.util.StringUtils;
import java.lang.reflect.Field;

public class DateTimeZoneUtil {

    public static String getTimeZone(Field field, String defaultTimeZoneId) {
        DateTimeZone dateTimeZone = field.getAnnotation(DateTimeZone.class);
        if (dateTimeZone == null) {
            // If the Field does not have a DateTimeZone annotation, use global
            return defaultTimeZoneId;
        }
        String timeZoneId = dateTimeZone.value();
        if (StringUtils.isEmpty(timeZoneId)) {
            // If the DateTimeZone annotation of Field is empty, global is used
            return defaultTimeZoneId;
        }
        returntimeZoneId; }}Copy the code

Implement time zone converter: Date <-> Number

import com.alibaba.excel.converters.date.DateNumberConverter;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.property.ExcelContentProperty;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.DateUtil;

import java.math.BigDecimal;
import java.util.Calendar;
import java.util.Date;
import java.util.TimeZone;

@Slf4j
public class DateTimeZoneNumberConverter extends DateNumberConverter {

    private final String globalTimeZoneId;

    public DateTimeZoneNumberConverter(a) {
        this(null);
    }

    public DateTimeZoneNumberConverter(String timeZoneId) {
        super(a);this.globalTimeZoneId = timeZoneId;
    }

    @Override
    public Date convertToJavaData(CellData cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) {

        TimeZone timeZone = getTimeZone(contentProperty);
        boolean use1904windowing = getUse1904windowing(contentProperty, globalConfiguration);

        return DateUtil.getJavaDate(cellData.getNumberValue().doubleValue(), use1904windowing, timeZone);
    }

    @Override
    public CellData convertToExcelData(Date value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) {

        TimeZone timeZone = getTimeZone(contentProperty);
        Calendar calendar = getCalendar(value, timeZone);

        boolean use1904windowing = getUse1904windowing(contentProperty, globalConfiguration);

        CellData cellData = new CellData(BigDecimal.valueOf(DateUtil.getExcelDate(calendar, use1904windowing)));

        return cellData;
    }

    private TimeZone getTimeZone(ExcelContentProperty contentProperty) {
        if(contentProperty == null) {
            return null;
        }
        String timeZoneId = DateTimeZoneUtil.getTimeZone(contentProperty.getField(), globalTimeZoneId);
        return TimeZone.getTimeZone(timeZoneId);
    }

    private Calendar getCalendar(Date date, TimeZone timeZone) {
        Calendar calStart = Calendar.getInstance();
        calStart.setTime(date);
        if(timeZone ! =null) {
            calStart.setTimeZone(timeZone);
        }

        return calStart;
    }

    private boolean getUse1904windowing(ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) {
        if (contentProperty == null || contentProperty.getDateTimeFormatProperty() == null) {
            return contentProperty.getDateTimeFormatProperty().getUse1904windowing();
        } else {
            returnglobalConfiguration.getUse1904windowing(); }}}Copy the code

Similar DateTimeZoneStringConverter DateTimeZoneNumberConverter inherited DateNumberConverter, provides a Date and transformation between the Numbe of converter.

test

The following unit test, to @ DateTimeZone, DateTimeZoneStringConverter and DateTimeZoneNumberConverter are tested. This is also a complete use case.

package io.gitlab.donespeak.tutorial.excel.easyexcel.timezone;import com.alibaba.excel.EasyExcel;import com.alibaba.excel.annotation.ExcelProperty;import com.alibaba.excel.annotation.format.DateTimeFormat;import lombok.EqualsAndHashCode;import lombok.Getter;import lombok.NoArgsConstructor;import lombok.Setter;import lombok.ToString;import org.junit.Rule;import org.junit.Test;import org.junit.rules.TemporaryFolder;import java.io.File;import java.util.ArrayList;import java.util.Date;import java.util.List;import java.util.TimeZone;import java.util.function.Function;import java.util.stream.Collectors;import static org.junit.Assert.assertEquals;/ * * *@author DoneSpeak * @date2019/11/21 22:01 * /public class DateTimeZoneConverterTest {    @Getter    @Setter    @ToString    @EqualsAndHashCode    @NoArgsConstructor    public static class TheDate {        @DateTimeFormat("yyyy-MM-dd hh:mm:ss:SSS")        @ExcelProperty(index = 0)        private Date date;        @DateTimeFormat("yyyy-MM-dd hh:mm:ss:SSS")        @DateTimeZone("Asia/Tokyo")        @ExcelProperty(index = 1)        private Date jpDate;    }    @Rule    public TemporaryFolder temporaryFolder = new TemporaryFolder();    /** * https://www.zeitverschiebung.net/cn/all-time-zones.html */    private static final String TIME_ZONE_ID_US_CENTRAL = "US/Central";    private static final String TIME_ZONE_ID_ETC_UTC = "Etc/UTC";    private static final String TIME_ZONE_ID_JP = "Asia/Tokyo"; // UTC+9 public File getTestDirectory() { // return new File(""); Return temporaryFolder.getroot (); // Use a local path to facilitate the generation of files. } @Test public void testDateTimeZoneStringConverter() { File file = new File(getTestDirectory(), "easyexcel-test-dateTimeZoneStringConverter.xlsx"); if(file.exists()) { file.delete(); } List
      
        listOriginal = data(); // Use US/Central to write time in Excel EasyExcel. TheDate.class) .registerConverter(new DateTimeZoneStringConverter(TIME_ZONE_ID_US_CENTRAL)) .sheet("theDate").doWrite(listOriginal); / / US/Central time to read the Excel List < TheDate > listUsCentralWriteUsCentralRead = EasyExcel. Read (file). RegisterConverter (new  DateTimeZoneStringConverter(TIME_ZONE_ID_US_CENTRAL)) .head(TheDate.class).sheet().doReadSync(); assertListEquals(listOriginal, listUsCentralWriteUsCentralRead); / / use UTC time zone to read the Excel time List < TheDate > listUsCentralWriteEtcUtcRead = EasyExcel. Read (file). RegisterConverter (new DateTimeZoneStringConverter(TIME_ZONE_ID_ETC_UTC)) .head(TheDate.class).sheet().doReadSync(); System.out.println(listUsCentralWriteEtcUtcRead); assertTimeSpan(collectDate(listOriginal, d -> d.getDate()), collectDate(listUsCentralWriteEtcUtcRead, d -> d.getDate()), TIME_ZONE_ID_US_CENTRAL, TIME_ZONE_ID_ETC_UTC); assertTimeSpan(collectDate(listOriginal, d -> d.getJpDate()), collectDate(listUsCentralWriteEtcUtcRead, d -> d.getJpDate()), TIME_ZONE_ID_JP, TIME_ZONE_ID_JP); } @Test public void testDateTimeZoneNumberConverter() { File file = new File(getTestDirectory(), "easyexcel-test-dateTimeZoneNumberConverter.xlsx"); if(file.exists()) { file.delete(); } List
       
         listOriginal = data(); // Use US/Central to write time in Excel EasyExcel. TheDate.class) .registerConverter(new DateTimeZoneNumberConverter(TIME_ZONE_ID_US_CENTRAL)) .sheet("theDate").doWrite(listOriginal); / / US/Central time to read the Excel List < TheDate > listUsCentralWriteUsCentralRead = EasyExcel. Read (file). RegisterConverter (new  DateTimeZoneNumberConverter(TIME_ZONE_ID_US_CENTRAL)) .head(TheDate.class).sheet().doReadSync(); assertListEquals(listOriginal, listUsCentralWriteUsCentralRead); / / use UTC time zone to read the Excel time List < TheDate > listUsCentralWriteEtcUtcRead = EasyExcel. Read (file). RegisterConverter (new DateTimeZoneNumberConverter(TIME_ZONE_ID_ETC_UTC)) .head(TheDate.class).sheet().doReadSync(); assertTimeSpan(collectDate(listOriginal, d -> d.getDate()), collectDate(listUsCentralWriteEtcUtcRead, d -> d.getDate()), TIME_ZONE_ID_US_CENTRAL, TIME_ZONE_ID_ETC_UTC); assertTimeSpan(collectDate(listOriginal, d -> d.getJpDate()), collectDate(listUsCentralWriteEtcUtcRead, d -> d.getJpDate()), TIME_ZONE_ID_JP, TIME_ZONE_ID_JP); } private List
        
          data() { Date now = getTime(); List
         
           datas = new ArrayList<>(); TheDate thd = new TheDate(); thd.setDate(now); thd.setJpDate(now); datas.add(thd); return datas; } private Date getTime() {return new Date();} private Date getTime() {return new Date(); } private long getTimeSpan(Date from, Date to) { return from.getTime() - to.getTime(); } private long getTimeZoneTimeSpan(String timeZoneIdfrom, String timeZoneIdTo) { return TimeZone.getTimeZone(timeZoneIdfrom).getRawOffset() - TimeZone.getTimeZone(timeZoneIdTo).getRawOffset(); } private void assertListEquals(List
          
            listOriginal, List
           
             listUsCentral) { assertEquals(listOriginal.size(), listUsCentral.size()); for(int i = 0; i < listOriginal.size(); i ++) { TheDate original = listOriginal.get(i); TheDate usCentral = listUsCentral.get(i); assertEquals(original, usCentral); } } private void assertTimeSpan(List
            
              dateOriginal, List
             
               dateOperated, String timeZoneWrite, String timeZoneRead) { long timeZoneSpanFromUsCentralToEtcUtc = getTimeZoneTimeSpan(timeZoneWrite, timeZoneRead); for(int i = 0; i < dateOriginal.size(); I ++) {// Long span = getTimeSpan(dateoper.get (I), dateoriginal.get (I)); assertEquals(timeZoneSpanFromUsCentralToEtcUtc, span); } } private List
              
                collectDate( final List
               
                 list, Function
                
                  function) { return list.stream().map(function).collect(Collectors.toList()); }}
                ,>
               
              
             
            
           
          
         
        
       
      
Copy the code

Expansion – Talk about EasyExcel converters

Writing process

EasyExcel.write(file, TheDate.class)    .registerConverter(new DateTimeZoneStringConverter(TIME_ZONE_ID_US_CENTRAL))    .sheet("theDate").doWrite(listOriginal);
Copy the code
  • EasyExcel.write(file, TheDate.class): Creates oneExcelWriterBuilder, so far only set the file output path and header format.
  • registerConverter(new DateTimeZoneStringConverter(TIME_ZONE_ID_US_CENTRAL)): in order toExcelWriterBuilder.writeWorkbookAdd a custom converter.
  • sheet("theDate"): createExcelWriterSheetBuilderAnd the configurationExcelWriterThat is, information about the converter.
  • .doWrite(listOriginal): ExcelWriterGenerate the list into an Excel file.

The configuration of the converter takes place in sheet(“theDate”). According to:

ExcelWriterSheetBuilder.sheet() -> ExcelWriterSheetBuilder.build()    -> new ExcelWriter(writeWorkbook) -> new ExcelBuilderImpl(writeWorkbook)    -> new WriteContextImpl(writeWorkbook) -> WirteContextImpl.initCurrentSheetHolder(writeSheet)    -> new WriteSheetHolder(writeSheet, writeWorkbookHolder) -> new AbstractWriteHolder()
Copy the code

Here you can find the code to configure Converter:

/ / configure default Converterif (parentAbstractWriteHolder = = null) { setConverterMap(DefaultConverterLoader.loadDefaultWriteConverter()); } else { setConverterMap(new HashMap
      
       (parentAbstractWriteHolder.getConverterMap())); } / / configure custom Conveterif (writeBasicParameter getCustomConverterList ()! = null && ! writeBasicParameter.getCustomConverterList().isEmpty()) { for (Converter converter : writeBasicParameter.getCustomConverterList()) { getConverterMap().put(ConverterKeyBuild.buildKey(converter.supportJavaTypeKey()), converter); }}
      ,>
Copy the code

Com. Alibaba. Excel. Converters under package EasyExcel provide the default of the Converter. In the configuration by default in the process of the Converter, DefaultConverterLoader loadDefaultWriteConverter () will be the default Converter load. Returns a to the converter. SupportJavaTypeKey () constitute the key, the converter as the value of the Map, will have the following list after completion of loading (mapping relations basic types will be converted to encapsulation type) :

BigDecimal.class:   BigDecimalNumberConverterBoolean.class:      BooleanBooleanConverterByte.class:         ByteNumberConverterDate.class:         DateStringConverterDouble.class:       DoubleNumberConverterFloat.class:        FloatNumberConverterInteger.class:      IntegerNumberConverterLong.class:         LongNumberConverterShort.class:        ShortNumberConverterString.class:       StringStringConverterFile.class:         FileImageConverterInpurtStream.class: InputStreamImageConverterbyte[].class:       ByteArrayImageConverterByte[].class:       BoxingByteArrayImageConverterURL.class:          UrlImageConverter
Copy the code

If a custom Converter is available, an automatically defined Conveter is used and the original default Converter is replaced according to the supportJavaTypeKey.

At the time of writing, from AbstractExcelWriteExecutor, depending on the type of data to get the right converter converts JavaObject into the right CellData.

Read process

List<TheDate> listUsCentralWriteUsCentralRead = EasyExcel.read(file)    .registerConverter(new DateTimeZoneStringConverter(TIME_ZONE_ID_US_CENTRAL))    .head(TheDate.class).sheet().doReadSync();
Copy the code
  • EasyExcel.read(file): createExcelReaderBuilderObject that configures the input file location, default header, and default listener.
  • registerConverter(new DateTimeZoneStringConverter(TIME_ZONE_ID_US_CENTRAL)): in order toExcelReaderBuilder.readWorkbookAdd a custom converter.
  • head(TheDate.class): Sets the table header.
  • sheet(): createExcelReaderSheetBuilderAnd the configurationExcelReaderThat is, information about the converter.
  • doReadSync(): Synchronously reads data from a file to a list of objects.

Similar to the write process, the configuration of the converter takes place in sheet() and the process is basically the same. According to:

ExcelReaderSheetBuilder.sheet() -> ExcelReaderSheetBuilder.build()    -> new ExcelReader(readWorkbook) -> new ExcelAnalyserImpl(readWorkbook)    -> new AnalysisContextImpl(readWorkbook) -> new ReadWorkbookHolder(readWorkbook)    -> new AbstractReadHolder()
Copy the code

Here you can find the code to configure Converter:

if (parentAbstractReadHolder == null) {
    setConverterMap(DefaultConverterLoader.loadDefaultReadConverter());
} else {
    setConverterMap(new HashMap<String, Converter>(parentAbstractReadHolder.getConverterMap()));
}
if(readBasicParameter.getCustomConverterList() ! =null
    && !readBasicParameter.getCustomConverterList().isEmpty()) {
    for(Converter converter : readBasicParameter.getCustomConverterList()) { getConverterMap().put( ConverterKeyBuild.buildKey(converter.supportJavaTypeKey(), converter.supportExcelTypeKey()), converter); }}Copy the code

And the writing process, reading process through DefaultConverterLoader. LoadDefaultReadConverter () load mapping relation, After loading can be got by the converter. SupportJavaTypeKey () and the converter. The supportExcelTypeKey () constitute the key, in the converter, for the value of the map, have the mapping list as follows:

BigDecimal.class <- CellDataTypeEnum.BOOLEAN:   BigDecimalBooleanConverter
BigDecimal.class <- CellDataTypeEnum.NUMBER:    BigDecimalNumberConverter
BigDecimal.class <- CellDataTypeEnum.STRING:    BigDecimalStringConverter

Boolean.class <- CellDataTypeEnum.BOOLEAN:      BooleanBooleanConverter
Boolean.class <- CellDataTypeEnum.NUMBER:       BooleanNumberConverter
Boolean.class <- CellDataTypeEnum.STRING:       BooleanStringConverter

Byte.class <- CellDataTypeEnum.BOOLEAN:     ByteBooleanConverter
Byte.class <- CellDataTypeEnum.NUMBER:      ByteNumberConverter
Byte.class <- CellDataTypeEnum.STRING:      ByteStringConverter

Date.class <- CellDataTypeEnum.NUMBER:      DateNumberConverter
Date.class <- CellDataTypeEnum.STRING:      DateStringConverter

Double.class <- CellDataTypeEnum.BOOLEAN:   DoubleBooleanConverter
Double.class <- CellDataTypeEnum.NUMBER:    DoubleNumberConverter
Double.class <- CellDataTypeEnum.STRING:    DoubleStringConverter

Float.class <- CellDataTypeEnum.BOOLEAN:    FloatBooleanConverter
Float.class <- CellDataTypeEnum.NUMBER:     FloatNumberConverter
Float.class <- CellDataTypeEnum.STRING:     FloatStringConverter

Integer.class <- CellDataTypeEnum.BOOLEAN:  IntegerBooleanConverter
Integer.class <- CellDataTypeEnum.NUMBER:   IntegerNumberConverter
Integer.class <- CellDataTypeEnum.STRING:   IntegerStringConverter

Long.class <- CellDataTypeEnum.BOOLEAN:     LongBooleanConverter
Long.class <- CellDataTypeEnum.NUMBER:      LongNumberConverter
Long.class <- CellDataTypeEnum.STRING:      LongStringConverter

Long.class <- CellDataTypeEnum.BOOLEAN:     LongBooleanConverter
Long.class <- CellDataTypeEnum.NUMBER:      LongNumberConverter
Long.class <- CellDataTypeEnum.STRING:      LongStringConverter

Short.class <- CellDataTypeEnum.BOOLEAN:    ShortBooleanConverter
Short.class <- CellDataTypeEnum.NUMBER:     ShortNumberConverter
Short.class <- CellDataTypeEnum.STRING:     ShortStringConverter

String.class <- CellDataTypeEnum.BOOLEAN:   StringBooleanConverter
String.class <- CellDataTypeEnum.NUMBER:    StringNumberConverter
String.class <- CellDataTypeEnum.STRING:    StringStringConverter

String.class <- CellDataTypeEnum.ERROR:     StringErrorConverter
Copy the code

Unlike writing, reading has more combinations. Excel files can have multiple field types and corresponding javaObject properties. This mapping allows you to determine the converter used to convert the input data type to the target data type.

If a custom Converter is available, an automatically defined Conveter is used and the original default Converter is replaced according to supportJavaTypeKey and supportExcelTypeKey.

The use of type conversions depends on the use of subclasses of ReadListener.

References and others

  • EasyExcel @alibaba-easyexcel.github.io
  • easyExcel @mvnrepository.com
  • Apache POI @poi.apache.org

Add time zone parameter #841 to DateTimeFormat

Front-end Excel generation technology can be understood:

  • Use SheetJS for pure front-end parsing and Excel @github.com/kaola-fed generation
  • SheetJS/sheetjs @github.com/SheetJS