EasyExcel 实现国际化导入导出

新项目涉及国际化。不想每个语言都建一个pojo文件。就将字段信息放到了数据库中。因为数据体量小，都放在了一个表中，可以根据需要改成一个语言一个表，或者是一个类多个语言一个表。
思路：
下载模版： 查询导入模版的字段，使用EasyExcel实现文件导出
导入： 查询导入模版的字段，利用EasyExcel 回调监听，获取到excel的数据。使用反射将表头转换成字段名，构造对像并给对象赋值，获得对象数据。之后就可以根据实际需求对数据进行新增或修改。
导出：查询导出模版字段，查询导出数据，使用EasyExcel实现文件导出。比下载模版多一个查询导出数据。

新建数据库表

entity_header 表字段

CREATE TABLE `entity_header` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `entity` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '对象',
  `lang` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '语言标识',
  `field` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '字段名称',
  `translate` varchar(100) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '翻译后的字段名称',
  `sort` int(11) DEFAULT NULL COMMENT '显示顺序',
  `remark` varchar(200) DEFAULT NULL COMMENT '备注',
  `creator` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '创建者',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updater` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '更新者',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=33 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='对象表头字段翻译';

后端实现

新建导入导出VO

import com.alibaba.excel.annotation.ExcelProperty;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.experimental.Accessors;

/**
 * 包装容器 Excel 导入 VO
 */
@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
@Accessors(chain = false) // 设置 chain = false，避免导入有问题
public class PackingContainerImportExcelVO {

    private Long oemId;

    @ExcelProperty("代工厂")
    private String oemName;

    @ExcelProperty("包装容器号")
    private String code;

    @ExcelProperty("rfid")
    private String rfid;

    @ExcelProperty("重量下限")
    private String weightMin;

    @ExcelProperty("重量上限")
    private String weightMax;

    @ExcelProperty("皮重")
    private String tareWeight;

    @ExcelProperty("容器型号")
    private String packagingContainerType;

    @ExcelProperty("容器容积")
    private String packagingVolume;

}

import io.swagger.v3.oas.annotations.media.Schema;
import lombok.Builder;
import lombok.Data;

import java.util.List;
import java.util.Map;

@Schema(description = "包装容器导入 Response VO")
@Data
@Builder
public class PackingContainerImportRespVO {

    @Schema(description = "创建成功的包装容器数组", requiredMode = Schema.RequiredMode.REQUIRED)
    private List<String> createCodes;

    @Schema(description = "更新成功的包装容器数组", requiredMode = Schema.RequiredMode.REQUIRED)
    private List<String> updateCodes;

    @Schema(description = "导入失败的包装容器集合，key 为容器编号，value 为失败原因", requiredMode = Schema.RequiredMode.REQUIRED)
    private Map<String, String> failureCodes;

}

Controller文件增加方法

	@GetMapping("/get-import-template")
    @Operation(summary = "获得导入包装容器模板")
    public void importTemplate(HttpServletResponse response,
                               @RequestAttribute(value = "lang", required = false) String lang ) throws IOException {

        // 输出
        List<EntityHeaderDO> entityHeaderList = entityHeaderService.getEntityHeaderList(PackingContainerImportExcelVO.class.getSimpleName(), lang);
        if(CollectionUtil.isEmpty(entityHeaderList)){
            throw exception(ENTITY_FIELDS_TEMPLATE_NOT_EXISTS);
        }
        List<String> headerList = entityHeaderList.stream()
                .map(EntityHeaderDO::getTranslate).collect(Collectors.toList());
        ExcelUtils.writeTemplate(response,headerList,"container_data_template.xls");
    }

    @PostMapping("/import")
    @Operation(summary = "导入包装容器")
    @Parameters({
            @Parameter(name = "file", description = "Excel 文件", required = true),
            @Parameter(name = "updateSupport", description = "是否支持更新，默认为 false", example = "true")
    })
    @PreAuthorize("@ss.hasPermission('packing-container-info:import')")
    @Transactional(rollbackFor = Exception.class)
    public CommonResult<PackingContainerImportRespVO> importExcel(@RequestParam("file") MultipartFile file,
               @RequestAttribute(value = "lang", required = false) String lang ,
               @RequestParam(value = "updateSupport", required = false, defaultValue = "false") Boolean updateSupport) throws Exception {
        List<EntityHeaderDO> headerList = entityHeaderService.getEntityHeaderList(PackingContainerImportExcelVO.class.getSimpleName(), lang);
        if(CollectionUtil.isEmpty(headerList)){
            throw exception(ENTITY_FIELDS_TEMPLATE_NOT_EXISTS);
        }
        Map<String, String> headMap = headerList.stream().collect(Collectors.toMap(EntityHeaderDO::getTranslate, EntityHeaderDO::getField, (v1, v2) -> v1));
        List<PackingContainerImportExcelVO> list = excelImportService.importExcel(file, PackingContainerImportExcelVO.class, headMap);
        return success(packingContainerInfoService.importContainerList(list,updateSupport));
    }

	@GetMapping("/export-excel")
    @Operation(summary = "导出包装容器 Excel")
    @PreAuthorize("@ss.hasPermission('packing-container-info:export')")
    @ApiAccessLog(operateType = EXPORT)
    public void exportInspectionStandardDetailExcel(@Valid PackingContainerPageReqVO pageReqVO,
              @RequestAttribute(value = "lang", required = false) String lang,
              HttpServletResponse response) throws IOException {
        pageReqVO.setPageSize(PageParam.PAGE_SIZE_NONE);
        List<PackingContainerDO> list = packingContainerInfoService.getPackingContainerPage(pageReqVO).getList();
        // 输出
        List<EntityHeaderDO> entityHeaderList = entityHeaderService.getEntityHeaderList(InspectionStandardDetailExcelVO.class.getSimpleName(), lang);
        if(CollectionUtil.isEmpty(entityHeaderList)){
            throw exception(ENTITY_FIELDS_TEMPLATE_NOT_EXISTS);
        }
        List<String> headerList = entityHeaderList.stream()
                .map(EntityHeaderDO::getTranslate).collect(Collectors.toList());
        // 导出 Excel
        if(CollectionUtil.isNotEmpty(list) && LanguageEnum.ENGLISH.getLang().equals(lang)){
            list.forEach(this::setLangAttrEn);
        }
        ExcelUtils.writeExport(response, "包装容器.xls", "data", headerList,
                        BeanUtils.toBean(list, InspectionStandardDetailExcelVO.class));
    }

ExcelUtils 工具类

import cn.hutool.core.util.StrUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.converters.longconverter.LongStringConverter;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import com.mo.framework.common.util.http.HttpUtils;
import com.mo.framework.excel.core.handler.SelectSheetWriteHandler;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;

/**
 * Excel 工具类
 *
 */
public class ExcelUtils {

    /**
     * 将列表以 Excel 响应给前端
     *
     * @param response  响应
     * @param filename  文件名
     * @param sheetName Excel sheet 名
     * @param head      Excel head 头
     * @param data      数据列表哦
     * @param <T>       泛型，保证 head 和 data 类型的一致性
     * @throws IOException 写入失败的情况
     */
    public static <T> void write(HttpServletResponse response, String filename, String sheetName,
                                 Class<T> head, List<T> data) throws IOException {
        // 输出 Excel
        EasyExcel.write(response.getOutputStream(), head)
                .autoCloseStream(false) // 不要自动关闭，交给 Servlet 自己处理
                .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()) // 基于 column 长度，自动适配。最大 255 宽度
                .registerWriteHandler(new SelectSheetWriteHandler(head)) // 基于固定 sheet 实现下拉框
                .registerConverter(new LongStringConverter()) // 避免 Long 类型丢失精度
                .sheet(sheetName).doWrite(data);
        // 设置 header 和 contentType。写在最后的原因是，避免报错时，响应 contentType 已经被修改了
        response.addHeader("Content-Disposition", "attachment;filename=" + HttpUtils.encodeUtf8(filename));
        response.setContentType("application/vnd.ms-excel;charset=UTF-8");
    }

    public static <T> List<T> read(MultipartFile file, Class<T> head) throws IOException {
        return EasyExcel.read(file.getInputStream(), head, null)
                .autoCloseStream(false)  // 不要自动关闭，交给 Servlet 自己处理
                .doReadAllSync();
    }

    public static void writeTemplate(HttpServletResponse response,List<String> headerList,String fileName) throws IOException{
        // 将映射信息转换为表头列表
        List<List<String>> head = new ArrayList<>();
        for(String field : headerList){
            List<String> headerRow = new ArrayList<>();
            headerRow.add(field);
            head.add(headerRow);
        }
        // 设置响应头
        response.setContentType("application/vnd.ms-excel;charset=UTF-8");
        response.setCharacterEncoding("utf-8");
        fileName = StrUtil.isEmpty(fileName)?"import_template.xlsx":fileName;
        fileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName);

        // 使用 EasyExcel 写入表头并生成模板文件
        EasyExcel.write(response.getOutputStream())
                .head(head)
                .sheet("data")
                .doWrite(new ArrayList<>());
    }

    public static <T> void writeExport(HttpServletResponse response, String filename, String sheetName,
                                       List<String> headerList, List<T> data) throws IOException {
        // 将映射信息转换为表头列表
        List<List<String>> head = new ArrayList<>();
        for(String field : headerList){
            List<String> headerRow = new ArrayList<>();
            headerRow.add(field);
            head.add(headerRow);
        }
        // 设置 header 和 contentType。写在最后的原因是，避免报错时，响应 contentType 已经被修改了
        response.addHeader("Content-Disposition", "attachment;filename=" + HttpUtils.encodeUtf8(filename));
        response.setContentType("application/vnd.ms-excel;charset=UTF-8");
        // 输出 Excel
        EasyExcel.write(response.getOutputStream())
                .head(head)
                .autoCloseStream(false) // 不要自动关闭，交给 Servlet 自己处理
                .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()) // 基于 column 长度，自动适配。最大 255 宽度
                .registerConverter(new LongStringConverter()) // 避免 Long 类型丢失精度
                .sheet(sheetName).doWrite(data);
    }

}

导入功能利用反射将excel数据转成对象数据

使用表头信息与翻译信息匹配，获取字段名称

import com.alibaba.excel.EasyExcel;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

@Service
public class ExcelImportService {

    public <T> List<T> importExcel(MultipartFile file, Class<T> beanClass, Map<String,String> headerFieldMap) throws Exception{
        ExcelDataListener listener = new ExcelDataListener();

        // 使用 EasyExcel 读取 Excel 数据
        EasyExcel.read(file.getInputStream()).registerReadListener(listener).sheet().doRead();

        List<T> result = new ArrayList<>();

        for (Map<String,String> row : listener.getDataList()) {
            T bean = beanClass.getDeclaredConstructor().newInstance();
            for (Map.Entry<String, String> entry : row.entrySet()) {
                String header = entry.getKey();
                String fieldName = headerFieldMap.get(header); // 表头 → 字段名
                if (fieldName != null) {
                    Field field = beanClass.getDeclaredField(fieldName);
                    field.setAccessible(true);
                    field.set(bean, entry.getValue());
                }
            }
            result.add(bean);
        }

        return result;
    }

    public <T> List<T> convertToBeanList(List<Map<String, Object>> dataList, Class<T> beanClass) throws Exception {
        List<T> beans = new ArrayList<>();
        for (Map<String, Object> data : dataList) {
            T bean = beanClass.getDeclaredConstructor().newInstance();
            for (Map.Entry<String, Object> entry : data.entrySet()) {
                String fieldName = entry.getKey();
                Object value = entry.getValue();
                try {
                    Field field = beanClass.getDeclaredField(fieldName);
                    field.setAccessible(true);
                    if (field.getType() == Integer.class || field.getType() == int.class) {
                        field.set(bean, value == null ? 0 : Integer.parseInt((String) value));
                    } else if (field.getType() == Double.class || field.getType() == double.class) {
                        field.set(bean, value == null ? 0.0 : Double.parseDouble((String) value));
                    } else {
                        field.set(bean, value);
                    }
                } catch (NoSuchFieldException | IllegalAccessException ignored) {
                }
            }
            beans.add(bean);
        }
        return beans;
    }
}

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import lombok.Getter;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
 * excel 回调监听
 */
public class ExcelDataListener extends AnalysisEventListener<Map<Integer, String>> {
    private final Map<Integer, String> headers = new HashMap<>();
    @Getter
    private final List<Map<String, String>> dataList = new ArrayList<>();

    @Override
    public void invoke(Map<Integer, String> data, AnalysisContext context) {
        // 构建 header -> value 映射
        Map<String, String> row = new HashMap<>();
        for (Map.Entry<Integer, String> entry : data.entrySet()) {
            String header = headers.get(entry.getKey());
            if (header != null) {
                row.put(header, entry.getValue());
            }
        }
        dataList.add(row);

    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // 所有数据解析完成
    }

    // excel头部数据
    @Override
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
        // 存到全局的map中
        headers.putAll(headMap);
    }
}