EasyExcel 集成国际化实现导入导出

1、配置文件准备

1.1 pom.xml 导入EasyExcel 依赖包

     

        <!-- https://mvnrepository.com/artifact/org.apache.commons/commons-lang3 -->
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-lang3</artifactId>
            <version>3.9</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>1.1.1</version>
        </dependency>

1.2 配置i18n 国际化文件

  在resources 目录下，点击右键-->new --> Resource Bundle 创建国际化文件，如下图所示：

  

 

1.3  在yml 文件中配置国际化文件地址messages:  和 需要导出的excel文件头的国际化key

二、编写excel 配置文件

2.1 构造导出对象

import com.alibaba.excel.metadata.BaseRowModel;
import lombok.Data;
import org.apache.commons.lang3.ObjectUtils;

import javax.servlet.http.HttpServletResponse;
import java.util.List;



public class EasyExcelParams {

    /**
     * excel文件名（不带拓展名)
     */
    private String excelNameWithoutExt;
    /**
     * sheet名称
     */
    private String sheetName ="sheet1";
    /**
     * 是否需要表头
     */
    private boolean needHead = true;
    /**
     * 数据
     */
    private List<? extends BaseRowModel> data;

    /**
     * 数据模型类型
     */
    private Class<? extends BaseRowModel> dataModelClazz;

    /**
     * 响应
     */
    private HttpServletResponse response;


    public EasyExcelParams(String excelNameWithoutExt, String sheetName, List<? extends BaseRowModel> data, Class<? extends BaseRowModel> dataModelClazz, HttpServletResponse response ){
        this.excelNameWithoutExt = excelNameWithoutExt;
        this.sheetName = sheetName;
        this.data = data;
        this.dataModelClazz = dataModelClazz;
        this.response = response;
    }

    public EasyExcelParams(String excelNameWithoutExt, List<? extends BaseRowModel> data, Class<? extends BaseRowModel> dataModelClazz, HttpServletResponse response ){
        this.excelNameWithoutExt = excelNameWithoutExt;
        this.data = data;
        this.dataModelClazz = dataModelClazz;
        this.response = response;
    }

    /**
     * 检查不允许为空的属性
     */
    public boolean isValid() {
        return ObjectUtils.allNotNull(excelNameWithoutExt, data, dataModelClazz, response);
    }

    public String getExcelNameWithoutExt() {
        return excelNameWithoutExt;
    }

    public void setExcelNameWithoutExt(String excelNameWithoutExt) {
        this.excelNameWithoutExt = excelNameWithoutExt;
    }

    public String getSheetName() {
        return sheetName;
    }

    public void setSheetName(String sheetName) {
        this.sheetName = sheetName;
    }

    public boolean isNeedHead() {
        return needHead;
    }

    public void setNeedHead(boolean needHead) {
        this.needHead = needHead;
    }

    public List<? extends BaseRowModel> getData() {
        return data;
    }

    public void setData(List<? extends BaseRowModel> data) {
        this.data = data;
    }

    public Class<? extends BaseRowModel> getDataModelClazz() {
        return dataModelClazz;
    }

    public void setDataModelClazz(Class<? extends BaseRowModel> dataModelClazz) {
        this.dataModelClazz = dataModelClazz;
    }

    public HttpServletResponse getResponse() {
        return response;
    }

    public void setResponse(HttpServletResponse response) {
        this.response = response;
    }


}

2.2 配置获取excel 头的国际化key

import lombok.Data;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Configuration;

import java.util.List;


@Data
@Configuration
@ConfigurationProperties("excel")
public class ExcelHeaderName {

    private List<String> userInfoHeader;

    private List<String> exportFailure;

 
}

 

2.3 编写EasyExcelUtil 导出工具类

import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.ExcelWriter;

import com.alibaba.excel.metadata.BaseRowModel;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.google.common.collect.Lists;
import com.***.fsd.ums.common.context.SpringBeanContext;
import com.***.fsd.ums.core.model.dto.EasyExcelParams;
import lombok.extern.slf4j.Slf4j;

import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.Validate;
import org.springframework.context.MessageSource;
import org.springframework.web.multipart.MultipartFile;


import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.time.LocalDateTime;
import java.util.Collections;
import java.util.List;
import java.util.Locale;
import java.util.ResourceBundle;


/**
 * EasyExcel工具类
 *
 * @author liuwangyanghdu@163.com
 * @date 2018/11/19
 */
@Slf4j
public class EasyExcelUtil {


    /**
     * 读取 Excel(多个 sheet)
     *
     * @param excel    文件
     * @param clazz 实体类映射，继承 BaseRowModel 类
     * @return Excel 数据 list
     */
    public static <T>List<T> readExcel(MultipartFile excel, Class<? extends BaseRowModel> clazz) {
        ExcelListener<T> excelListener = new ExcelListener();
        ExcelReader reader = getReader(excel, excelListener);
        if (reader == null) {
            return null;
        }
        for (Sheet sheet : reader.getSheets()) {

            sheet.setClazz(clazz);

            reader.read(sheet);
        }
        return excelListener.getDatas();
    }

    /**
     * 读取某个 sheet 的 Excel
     *
     * @param excel    文件
     * @param rowModel 实体类映射，继承 BaseRowModel 类
     * @param sheetNo  sheet 的序号 从1开始
     * @return Excel 数据 list
     */
    public static List<Object> readExcel(MultipartFile excel, BaseRowModel rowModel, int sheetNo) {
        return readExcel(excel, rowModel, sheetNo, 1);
    }

    /**
     * 读取某个 sheet 的 Excel
     *
     * @param excel       文件
     * @param rowModel    实体类映射，继承 BaseRowModel 类
     * @param sheetNo     sheet 的序号 从1开始
     * @param headLineNum 表头行数，默认为1
     * @return Excel 数据 list
     */
    public static List<Object> readExcel(MultipartFile excel, BaseRowModel rowModel, int sheetNo,
                                         int headLineNum) {
        ExcelListener excelListener = new ExcelListener();
        ExcelReader reader = getReader(excel, excelListener);
        if (reader == null) {
            return null;
        }
        reader.read(new Sheet(sheetNo, headLineNum, rowModel.getClass()));
        return excelListener.getDatas();
    }




    /**
     * 下载EXCEL文件2007版本
     *
     * @throws IOException IO异常
     */
    public static void exportExcel2007Format(EasyExcelParams excelParams) throws IOException {
        exportExcel(excelParams, ExcelTypeEnum.XLSX);
    }

    /**
     * 下载EXCEL文件2003版本
     *
     * @throws IOException IO异常
     */
    public static void exportExcel2003Format(EasyExcelParams excelParams) throws IOException {
        exportExcel(excelParams, ExcelTypeEnum.XLS);
    }

    /**
     * 根据参数和版本枚举导出excel文件
     *
     * @param excelParams 参数实体
     * @param typeEnum    excel类型枚举
     * @throws IOException
     */
    private static void exportExcel(EasyExcelParams excelParams, ExcelTypeEnum typeEnum) throws IOException {
        Validate.isTrue(excelParams.isValid(), "easyExcel params is not valid");

        HttpServletResponse response = excelParams.getResponse();
        ServletOutputStream out = response.getOutputStream();
        ExcelWriter writer = new ExcelWriter(out, typeEnum, excelParams.isNeedHead());
        prepareResponds(response, excelParams.getExcelNameWithoutExt(), typeEnum);
        Sheet sheet1 = new Sheet(1, 0, excelParams.getDataModelClazz());
        if (StringUtils.isNotBlank(excelParams.getSheetName())) {
            sheet1.setSheetName(excelParams.getSheetName());
        }
        writer.write(excelParams.getData(), sheet1);
        writer.finish();
        out.flush();
    }

    /**
     * 自定义excel每列的名称，支持国际化
     * @param excelParams excel 内容构造对象
     * @param heads     国际化excel的每列名称的key
     * @param language  国际化标签
     * @throws IOException
     */
    public static void exportExcelWithCustomHeader(EasyExcelParams excelParams, List<String> heads, String language) throws IOException {
        Validate.isTrue(excelParams.isValid(), "easyExcel params is not valid");
        List<String> currentHeads = getMulLanguageHeaders(heads, language);
        List<List<String>> headerList = Lists.newArrayList();
        if(currentHeads != null){
            currentHeads.forEach(h -> headerList.add(Collections.singletonList(h)));
        }
        HttpServletResponse response = excelParams.getResponse();
        ServletOutputStream out = response.getOutputStream();
        ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLS, excelParams.isNeedHead());
        prepareResponds(response, excelParams.getExcelNameWithoutExt(), ExcelTypeEnum.XLS);
        Sheet sheet1 = new Sheet(1, 0, excelParams.getDataModelClazz(), excelParams.getSheetName(), headerList);
        writer.write(excelParams.getData(), sheet1);
        writer.finish();
        out.flush();
    }


    /**
     *  获取多语言文件里面的Excel 头信息
     * @param headerKeys  多语言的Excel文件每列的key
     * @param language   多语言的key
     * @return
     */
    private static List<String> getMulLanguageHeaders(List<String> headerKeys, String language){
        Locale locale =  new Locale(language);
        MessageSource messageSource = SpringBeanContext.getBean(MessageSource.class);
     //   ResourceBundle rb = ResourceBundle.getBundle("i18n/messages", locale);
        List<String> resList = Lists.newArrayList();
        headerKeys.forEach(header ->{
            String str = messageSource.getMessage(header, null, locale);
            resList.add(str);
        });
        return resList;
    }

    /**
     * 将文件输出到浏览器（导出文件）
     *
     * @param response 响应
     * @param fileName 文件名（不含拓展名）
     * @param typeEnum excel类型
     */
    private static void prepareResponds(HttpServletResponse response, String fileName, ExcelTypeEnum typeEnum) throws UnsupportedEncodingException {
        String fileName2Export = URLEncoder.encode(fileName, "utf-8") + "_" + DateTimeUtil.formatDateTime(LocalDateTime.now(), "yyyy-MM-dd_HH-mm-ss");
        response.setContentType("application/form-data;charset=UTF-8");
        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName2Export + typeEnum.getValue());
    }



    /**
     * 返回 ExcelReader
     *
     * @param excel         需要解析的 Excel 文件
     * @param excelListener new ExcelListener()
     */
    private static ExcelReader getReader(MultipartFile excel,
                                         ExcelListener excelListener)  {
        String filename = excel.getOriginalFilename();
        if (filename == null || (!filename.toLowerCase().endsWith(".xls") && !filename.toLowerCase().endsWith(".xlsx"))) {
            try {
                throw new Exception("文件格式错误！");
            } catch (Exception e) {
                log.error("EasyExcelUtil.getReader: 文件格式错误！");
                e.printStackTrace();
            }
        }
        InputStream inputStream;
        try {
            inputStream = new BufferedInputStream(excel.getInputStream());
            return new ExcelReader(inputStream, null, excelListener, false);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }






}

springBeanContext.java

import org.springframework.beans.BeansException;
import org.springframework.context.ApplicationContext;
import org.springframework.context.ApplicationContextAware;
import org.springframework.stereotype.Component;

@Component
public class SpringBeanContext implements ApplicationContextAware {
	
	
	private static ApplicationContext springContext;
	
	@Override
	public void setApplicationContext(ApplicationContext springContext) throws BeansException {
		this.springContext = springContext;
	}
	
	public static ApplicationContext getSpringContext() {
        return springContext;
    }

    /**
     * 根据bean名称获取bean
     * @param name
     * @return
     */
    public static Object getBean(String name){
        return getSpringContext().getBean(name);
    }

    /**
     * 根据bean class获取bean
     * @param clazz
     * @return
     */
    public static <T> T getBean(Class<T> clazz){
        return getSpringContext().getBean(clazz);
    }

    /**
     * 根据bean名称和bean class获取bean
     * @param name
     * @param clazz
     * @return
     */
    public static <T> T getBean(String name, Class<T> clazz){
        return getSpringContext().getBean(name, clazz);
    }

}

三、使用示例

导出对象：

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.BaseRowModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

import java.util.Date;

/**
 * @Author: zhenglei12
 * @Data: Created in 2019/7/29 15:09
 * @Description:
 */
@Data
public class UserExprotVO extends BaseRowModel {

    @ApiModelProperty(value="序列号Id", hidden=false, notes="Id", dataType="Integer")
    @ExcelProperty(value = "id", index = 0)
    private Integer id;

    @ApiModelProperty(value="用户名", hidden=false, notes="用户名", dataType="String")
    @ExcelProperty(value = "姓名", index = 1)
    private String username;

    @ApiModelProperty(value="用户密码", hidden=false, notes="用户密码", dataType="String")
    @ExcelProperty(value = "用户密码", index = 2)
    private String password;

    @ApiModelProperty(value="邮箱地址", hidden=false, notes="邮箱地址", dataType="String")
    @ExcelProperty(value = "邮箱地址", index = 3)
    private String email;

    @ApiModelProperty(value="电话号码", hidden=false, notes="电话号码", dataType="String")
    @ExcelProperty(value = "电话号码", index = 4)
    private String phone;

    @ApiModelProperty(value="创建时间", hidden=false, notes="创建时间", dataType="String")
    @ExcelProperty(value = "创建时间", index = 5)
    private String  createTime;

    @ApiModelProperty(value="更新时间", hidden=false, notes="更新时间", dataType="String")
    @ExcelProperty(value = "更新时间", index = 6)
    private String updateTime;

}

测试：

    @PostMapping(value = "/exportUsers")
    @ApiOperation(value = "导出用户信息",notes = "导出用户信息")
    public DataResult<UserVO> exportUsers(@Valid @RequestBody UserExportQuery query, HttpServletRequest request, HttpServletResponse response){
        DataResult<UserVO> dataResult = new DataResult<>();

        List<UserExprotVO> userVOList= userService.exportUsers(query);
        EasyExcelParams easyExcelParams = new EasyExcelParams("userInfo",  userVOList, UserExprotVO.class, response);
        try {
           // EasyExcelUtil.exportExcel2007Format(easyExcelParams);

            String language = "zh_CN";
      //      String language = "en_US";
            List<String> headerList = excelHeaderName.getUserInfoHeader();
            EasyExcelUtil.exportExcelWithCustomHeader(easyExcelParams, headerList,language);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return dataResult;
    }