EasyExcel实现springboot项目的Excel导入导出

1 导入依赖

<!--EasyExcel相关依赖-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.0.5</version>
        </dependency>
        <dependency>
            <groupId>org.apiguardian</groupId>
            <artifactId>apiguardian-api</artifactId>
            <version>1.1.2</version>
        </dependency>

2 使用说明

EasyExcel 和 EasyPoi 的使用非常类似，都是通过注解来控制导入导出。接下来我们以用户信息导入导出为例，分别实现下简单的单表导出。

3 简单导出

• 首先创建一个会员对象EasyExcelUser，封装用户信息，这里使用了EasyExcel的注解；

package com.pa.pojo.easyExcel;


import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentStyle;
import com.alibaba.excel.annotation.write.style.HeadStyle;
import com.alibaba.excel.enums.poi.HorizontalAlignmentEnum;
import com.pa.utils.easyExcelUser.GenderConverter;
import com.pa.utils.easyExcelUser.StatusConverter;
import lombok.Data;
import lombok.EqualsAndHashCode;

import java.util.Date;

/**
 * @author:
 * @Date: 2022-06-09 21:44
 * @Description:
 * @Modified By:
 * @Version:
 */
@Data
@EqualsAndHashCode(callSuper = false)
@HeadStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER)//表头样式 居中
@ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER)//内容样式 居中
public class EasyExcelUser {

    @ExcelProperty("序号")
    @ColumnWidth(10)
    private Long id;

    @ExcelProperty("用户名")
    @ColumnWidth(20)
    private String userName;

    @ExcelProperty("职务")
    @ColumnWidth(20)
    private String position;

    @ExcelProperty(value = "性别", converter = GenderConverter.class)
    //@ExcelProperty(value = "性别")
    @ColumnWidth(10)
    private Integer gender;

    @ExcelProperty("政治面貌")
    @ColumnWidth(20)
    private String politicsStatus;

    @ExcelProperty("手机号")
    @ColumnWidth(20)
    private String phone;

    @ExcelProperty("入职日期")
    @ColumnWidth(20)
    @DateTimeFormat("yyyy-MM-dd")
    private Date workAge;

    @ExcelProperty(value ="账号状态", converter = StatusConverter.class)
    @ColumnWidth(20)
    private int status;

    @ExcelProperty("创建时间")
    @ColumnWidth(20)
    @DateTimeFormat("yyyy-MM-dd HH:MM:SS")
    private Date createTime;


    @ExcelProperty("部门名")
    @ColumnWidth(20)
    private String departmentName;

    @ExcelProperty("角色名")
    @ColumnWidth(20)
    private String roleName;

}

上面代码使用到了EasyExcel的核心注解，我们分别来了解下：

@ExcelProperty：核心注解，value属性可用来设置表头名称，converter属性可以用来设置类型转换器；
@ColumnWidth：用于设置表格列的宽度；
@DateTimeFormat：用于设置日期转换格式。
在EasyExcel中，如果你想实现枚举类型到字符串的转换（比如gender属性中，0->男，1->女），需要自定义转换器，下面为自定义的GenderConverter代码实现；

package com.pa.utils.easyExcelUser;
import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.enums.CellDataTypeEnum;

import cn.hutool.core.util.StrUtil;
import com.alibaba.excel.converters.ReadConverterContext;
import com.alibaba.excel.converters.WriteConverterContext;
import com.alibaba.excel.metadata.data.WriteCellData;

/**
 * excel性别转换器
 *
 */
public class GenderConverter implements Converter<Integer> {

    @Override
    public Class<?> supportJavaTypeKey() {
        //对象属性类型
        return Integer.class;
    }

    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        //CellData属性类型
        return CellDataTypeEnum.STRING;
    }

    @Override
    public Integer convertToJavaData(ReadConverterContext<?> context) throws Exception {
        //CellData转对象属性
        String cellStr = context.getReadCellData().getStringValue();
        if (StrUtil.isEmpty(cellStr)) return null;
        if ("男".equals(cellStr)) {
            return 0;
        } else if ("女".equals(cellStr)) {
            return 1;
        } else {
            return null;
        }
    }

    @Override
    public WriteCellData<?> convertToExcelData(WriteConverterContext<Integer> context) throws Exception {
        //对象属性转CellData
        Integer cellValue = context.getValue();
        if (cellValue == null) {
            return new WriteCellData<>("");
        }
        if (cellValue == 0) {
            return new WriteCellData<>("男");
        } else if (cellValue == 1) {
            return new WriteCellData<>("女");
        } else {
            return new WriteCellData<>("");
        }
    }

}

StatusConverter转换器实现

package com.pa.utils.easyExcelUser;

import cn.hutool.core.util.StrUtil;
import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.converters.ReadConverterContext;
import com.alibaba.excel.converters.WriteConverterContext;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.data.WriteCellData;


/**
 * @Description : 自定义转换器 状态
 * @Author :
 * @Date :
 * <p>
 * 在EasyExcel中，如果你想实现枚举类型到字符串的转换（Status属性中，1->正常，0->停用），需要自定义转换器
 **/

public class StatusConverter implements Converter<Integer> {

    @Override
    public Class<?> supportJavaTypeKey() {
        //对象属性类型 0 1
        return Integer.class;
    }

    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        //CellData属性类型 "停用"  "启用"
        return CellDataTypeEnum.STRING;
    }

    @Override
    public Integer convertToJavaData(ReadConverterContext<?> context) throws Exception {
        //CellData转对象属性
        String cellStr = context.getReadCellData().getStringValue();
        if (StrUtil.isEmpty(cellStr)) return null;
        if ("停用".equals(cellStr)) {
            return 0;
        } else if ("启用".equals(cellStr)) {
            return 1;
        } else {
            return null;
        }
    }

    @Override
    public WriteCellData<?> convertToExcelData(WriteConverterContext<Integer> context) throws Exception {
        //对象属性转CellData
        Integer cellValue = context.getValue();
        if (cellValue == null) {
            return new WriteCellData<>("");
        }
        if (cellValue == 0) {
            return new WriteCellData<>("停用");
        } else if (cellValue == 1) {
            return new WriteCellData<>("启用");
        } else {
            return new WriteCellData<>("");
        }
    }
}

接下来我们在Controller中添加一个接口，用于导出以及导入用户列表到Excel，还需给响应头设置下载excel的属性，具体代码如下；

package com.pa.controller.easyExcelUser;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.pa.pojo.Department;
import com.pa.pojo.Role;
import com.pa.pojo.User;
import com.pa.pojo.easyExcel.EasyExcelUser;
import com.pa.service.department.DepartmentService;
import com.pa.service.role.RoleService;
import com.pa.service.user.UserService;
import com.pa.utils.MD5Utils;
import com.pa.utils.R;
import com.pa.utils.easyExcelUser.CommonResult;
import lombok.SneakyThrows;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

/**
 * @author: Xiao Guanglin
 * @Date: 2022-06-09 21:57
 * @Description:
 * @Modified By:
 * @Version:
 */
@Controller
//@Api(tags = "EasyExcelController", description = "EasyExcel导入导出测试")
@RequestMapping("/easyExcel")
public class EasyExcelController {

    Logger logger = LoggerFactory.getLogger(getClass());

    @Autowired
    UserService userService;
    @Autowired
    RoleService roleService;
    @Autowired
    DepartmentService departmentService;

    /**
     * @描述:   导出用户表到本地（Excel）
     * @author XiaoGuangLin
     * @date 2022/6/15 19:47
     * @param response

     */
    @RequestMapping(value = "/exportUserList", method = RequestMethod.GET)
    public void exportMemberList(HttpServletResponse response) {
        List<EasyExcelUser> easyExcelUsers = userService.getEasyExcelUsers();
        int id = 1;
        for (EasyExcelUser easyExcelUser : easyExcelUsers) {
            easyExcelUser.setId((long) id++);
        }
        for (EasyExcelUser easyExcelUser : easyExcelUsers) {
        }
        try {
            setExcelRespProp(response, "用户信息表");

            EasyExcel.write(response.getOutputStream())
                    .head(EasyExcelUser.class)
                    .excelType(ExcelTypeEnum.XLSX)
                    .sheet("用户列表")
                    .doWrite(easyExcelUsers);
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * @描述:   导入用户表到系统（Excel）
     * @author XiaoGuangLin
     * @date 2022/6/15 19:47
     * @param
     */
    @SneakyThrows
    @RequestMapping(value = "/importUserList", method = RequestMethod.POST)
    @ResponseBody
    public CommonResult  importUserList(@RequestPart("txt_file") MultipartFile file){
        List<EasyExcelUser> EasyExcelUserList = EasyExcel.read(file.getInputStream())
                .head(EasyExcelUser.class)
                .sheet()
                .doReadSync();
        List<Role> roleList = roleService.getRoles();
        List<Department> departmentList = departmentService.getDepartments();
        String password = "123456";
        String passwordEncryption= MD5Utils.mdtEncode(password);
        for (EasyExcelUser easyExcelUser : EasyExcelUserList) {
            if(easyExcelUser.getUserName()==null)break;
            if(userService.getUserByUserName(easyExcelUser.getUserName())==null){
                User user = new User();
                user.setUserName(easyExcelUser.getUserName());
                user.setPassword(password);
                user.setPosition(easyExcelUser.getPosition());
                user.setGender(easyExcelUser.getGender());
                user.setPhone(easyExcelUser.getPhone());
                Integer roleId = null;
                for (Role role : roleList) {
                    if(role.getRoleName().equals(easyExcelUser.getRoleName())){
                        roleId = role.getRoleId();

                        break;
                    }
                }
                System.out.println("roleId=="+roleId);
                user.setRoleId(roleId);
                Integer departId = null;
                for (Department department : departmentList) {

                }
                for (Department department : departmentList) {
                    if(department.getDepartmentName().equals(easyExcelUser.getDepartmentName())){
                        departId = department.getDepartmentId();
                        break;
                    }
                }
                user.setDepartId(departId);
                user.setWorkAge(easyExcelUser.getWorkAge());
                user.setStatus(easyExcelUser.getStatus());
                user.setCreateTime(new Date());
                user.setPoliticsStatus(easyExcelUser.getPoliticsStatus());
                userService.addUser(user);
            }

        }
        System.out.println("EasyExcelUserList="+EasyExcelUserList);
        return CommonResult.success(EasyExcelUserList);
        //return "redirect:/user/getUsers";
    }

    /**
     * 设置excel下载响应头属性
     */
    private void setExcelRespProp(HttpServletResponse response, String rawFileName) throws UnsupportedEncodingException {
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        String fileName = URLEncoder.encode(rawFileName, "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
    }
}

4 导入

首先，导入需要一个模态框上传excel表格，这里使用的是bootstrap的模态框组件：

<!--文件上传 模态框-->
 <form>
     <div class="modal fade" id="fileInput-modal" tabindex="-1" role="dialog" aria-labelledby="myModalLabel">
         <div class="modal-dialog modal-lg" role="document">
             <div class="modal-content">
                 <div class="modal-header">
                     <button type="button" class="close" data-dismiss="modal" aria-label="Close"><span aria-hidden="true">&times;</span></button>
                     <h4 class="modal-title" id="myModalLabel">请选择Excel文件</h4>
                 </div>
                 <div class="modal-body">
                     <a th:href="@{/ExcelTemplate/用户信息表导入模板.xlsx}"  class="form-control" style="border:none;">下载导入模板</a>
                     <input type="file" name="txt_file" id="txt_file" multiple class="file-loading" />
                 </div>
             </div>
         </div>
     </div>
 </form>

如图：

当然，需要配合前端代码：

<!--导入按钮-->
<a class="btn btn-success m-r-5" type="button" data-toggle="modal" data-target="#fileInput-modal"> 
<i class="mdi mdi-block-helper"></i> 导入</a>

/*  js代码  */
/**
 * 用户列表 文件上传
 */
$(function () {
    //0.初始化fileinput
    var oFileInput = new FileInput();
    oFileInput.Init("txt_file", "/easyExcel/importUserList");
});

//初始化fileinput
var FileInput = function () {
    var oFile = new Object();

    //初始化fileinput控件（第一次初始化）
    oFile.Init = function(ctrlName, uploadUrl) {
        var control = $('#' + ctrlName);

        //初始化上传控件的样式
        control.fileinput({
            language: 'zh', //设置语言
            uploadUrl: uploadUrl, //上传的地址
            allowedFileExtensions: ['xlsx','xls'],//接收的文件后缀
            showUpload: true, //是否显示上传按钮
            showCaption: false,//是否显示标题
            browseClass: "btn btn-primary", //按钮样式
            //dropZoneEnabled: false,//是否显示拖拽区域
            //minImageWidth: 50, //图片的最小宽度
            //minImageHeight: 50,//图片的最小高度
            //maxImageWidth: 1000,//图片的最大宽度
            //maxImageHeight: 1000,//图片的最大高度
            //maxFileSize: 0,//单位为kb，如果为0表示不限制文件大小
            //minFileCount: 0,
            maxFileCount: 10, //表示允许同时上传的最大文件个数
            enctype: 'multipart/form-data',
            validateInitialCount:true,
            previewFileIcon: "<i class='glyphicon glyphicon-king'></i>",
            msgFilesTooMany: "选择上传的文件数量({n}) 超过允许的最大数值{m}！",
        });

        //导入文件上传完成之后的事件
        $("#txt_file").on("fileuploaded", function (event, data, previewId, index) {
            $("#fileInput-modal").modal("hide");
            var data = data.response.lstOrderImport;
            if (data == undefined) {
                toastr.error('文件格式类型不正确');
                return;
            }
            //1.初始化表格
            var oTable = new TableInit();
            oTable.Init(data);
            $("#div_startimport").show();
        });
    }
    return oFile;
};