A brief analysis of how EasyExcel exports automatic echo Chinese

introduction

In actual business, we often need to display the code values in the database (such as 1, 2, 3) as Chinese when exporting Excel (such as "In Progress", "Completed", "Canceled"), and when importing Excel, the Chinese filled in by the user needs to be automatically converted to the corresponding code values. This article will introduce how to implement this function through a global converter (GlobalCodeConverter) and custom annotations, combining the database dynamic query table to improve code reusability and maintainability.

1. Requirement scenarios

Suppose there is an order table, the field trans_status stores the status code (1: in progress, 2: completed, 3: cancelled), and the following needs must be met.

When exporting Excel: convert trans_status=1 to "In Progress" display
When importing Excel: User input "In Progress", automatically converts to 1 and stores it to the database
Support multicode tables: fields such as gender, priority, etc. also need to be processed similarly

2. Implementation ideas

Custom annotation: mark the fields that need to be converted and specify the code table type

Global converter: intercept the fields of annotation marks, and dynamically query the database to obtain the code value mapping relationship

Cache optimization: Reduce frequent database queries and improve performance

3. Code implementation

1. Custom annotations @ExcelSelected

Used to mark fields that need to be converted and specify the code table type

@Target()
@Retention()
public @interface ExcelSelected {
    /**
      * Code table type (such as trans_status)
      */
    String codeField();
}

2. Code table entity and database query

Design code table structure (such as sys_dict)

CREATE TABLE sys_dict (
    type VARCHAR(50) COMMENT 'Code table type (such as trans_status)',
    code VARCHAR(20) COMMENT 'Code value',
    name VARCHAR(50) COMMENT 'display name'
);

3. Global Converter GlobalCodeConverter

Implement the Converter interface to handle the conversion logic during import and export

public class GlobalCodeConverter implements Converter&lt;String&gt; {

    private static final Map&lt;String, Map&lt;String, String&gt;&gt; CACHE = new ConcurrentHashMap&lt;&gt;();

    @Override
    public Class&lt;String&gt; supportJavaTypeKey() {
        return ;
    }

    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return ;
    }

    // Conversion when reading Excel (Chinese->code value)    @Override
    public String convertToJavaData(ReadConverterContext&lt;?&gt; context) {
        String cellValue = ().getStringValue();
        Field field = ().getField();
        ExcelSelected annotation = ();

        if (annotation != null) {
            Map&lt;String, String&gt; codeMap = getCodeMap(());
            return ().stream()
                    .filter(entry -&gt; ().equals(cellValue))
                    .map(::getKey)
                    .findFirst()
                    .orElse(null);
        }
        return cellValue;
    }

    // Conversion when writing to Excel (code value -> Chinese)    @Override
    public WriteCellData&lt;String&gt; convertToExcelData(WriteConverterContext&lt;String&gt; context) {
        String value = ();
        Field field = ().getField();
        ExcelSelected annotation = ();

        if (annotation != null) {
            Map&lt;String, String&gt; codeMap = getCodeMap(());
            return new WriteCellData&lt;&gt;((value));
        }
        return new WriteCellData&lt;&gt;(value);
    }

    // Get code table data    private Map&lt;String, String&gt; getCodeMap(String codeType) {
        // Query with cached code table        return (codeType, k -&gt; {
            // Code for actually querying the database (example)            Map&lt;String, String&gt; map = new HashMap&lt;&gt;();
            ("1", "in progress");
            ("2", "Completed");
            ("3", "Canceled");
            return map;
        });
    }
}

4. Entity class usage annotations

Add @ExcelSelected on the field you want to convert

public class OrderVO {
    @ExcelProperty("Order Status")
    @ExcelSelected(codeField = "trans_status")
    private String transStatus;
}

5. Register a global converter

// Export(fileName, )
         .registerConverter(new GlobalCodeConverter())
         .sheet().doWrite(orders);
// Import(fileName, , new PageReadListener&lt;&gt;(list -&gt; {}))
         .registerConverter(new GlobalCodeConverter())
         .sheet().doRead();

4. Effect verification

Export Excel: trans_status=1 shows as "In Progress"

Import Excel: User input "In Progress" and automatically convert to 1 to save to the database

5. Summary

Through custom annotations and global converters, dynamic conversion of code values and Chinese is realized, and the code is simple and easy to expand. Here you can combine the previous articlesSummary of three implementation methods of EasyExcel custom pull-down annotations, further improve user experience.

This is the article about how EasyExcel exports and automatically echoes Chinese. This is all about this article. For more relevant EasyExcel exports and automatically echoes Chinese content, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!