记录一下自己用springboot整合阿里巴巴开源的EasyExcel实现导入导出功能

2023-10-04 12:16:28

项目地址：https://github.com/changxueyi/springbootcrud

1.准备

项目的整体架构和图中的类似

2. 导入依赖

当然，基本的mysql-connector-java、mybaits、springboot的也需要导入，最重要的就是上面的

3、导出excel

dept实体类
导出 Excel 时，若需要表头，那么相应的实体类需要继承 BaseRowModel，并加入 @ExcelProperty(value = “id”, index = 0) 注解。其中 value 代表在导出 Excel 时，该字段对应的表头名称；index 代表该字段对应的表头位置（从0开始）。如下图：

1. 重要的代码

实体类对象

2. 控制层 DeptController

2.service业务层

3. 实现层

package com.cxy.service.Impl;

import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.cxy.common.poi.ExcelListener;
import com.cxy.dao.DeptMapper;
import com.cxy.domin.Dept;
import com.cxy.service.DeptService;
import org.apache.commons.lang3.RandomUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.BufferedInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
import java.util.Random;

/**
 * @ClassName DeptServiceImpl
 * @Description TODO
 * @Author changxueyi
 * @Date 2020/11/12 17:43
 */
@Service
public class DeptServiceImpl implements DeptService {
    @Autowired
    private DeptMapper deptMapper;

    @Override
    public void excelExport(HttpServletResponse response) throws IOException {
        List<Dept> list = deptMapper.queryAllDept();
        String fileName = "用户名单";
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-disposition", "attachment;filename=" + new String( fileName.getBytes("gb2312"), "ISO8859-1" ) + ".xls");
        ServletOutputStream out = response.getOutputStream();
        ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLS,true);
        Sheet sheet = new Sheet(1,0,Dept.class);
        //设置自适应宽度
        sheet.setAutoWidth(Boolean.TRUE);
        sheet.setSheetName("用户名单");
        writer.write(list,sheet);
        writer.finish();
        out.flush();
        response.getOutputStream().close();
        out.close();
    }

    @Override
    public void excelImport(MultipartFile file) throws IOException {
        if(!file.getOriginalFilename().equals("用户名单.xls") && !file.getOriginalFilename().equals("用户名单.xlsx") ){
            return;
        }
        InputStream inputStream = new BufferedInputStream(file.getInputStream());
        //实例化实现了AnalysisEventListener接口的类
        ExcelListener excelListener = new ExcelListener(deptMapper);
        ExcelReader reader = new ExcelReader(inputStream,null,excelListener);
        //读取信息
        reader.read(new Sheet(1,1,Dept.class));
    }
}

　4.Mapper层

5.Mapper实现层

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.cxy.dao.DeptMapper">
    <select id="queryAllDept" resultType="com.cxy.domin.Dept">
        select * from dept
    </select>

    <insert id="addDept" parameterType="com.cxy.domin.Dept">
        insert into dept values (#{deptNo},#{deptName},#{localtion})
    </insert>
</mapper>

　6.ExcelListener 工具类

package com.cxy.common.poi;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.cxy.dao.DeptMapper;
import com.cxy.domin.Dept;
import org.springframework.stereotype.Controller;

import java.util.ArrayList;
import java.util.List;

/**
 * @ClassName ExcelListener
 * @Description TODO
 * @Author changxueyi
 * @Date 2020/11/12 17:57
 */
@Controller
public class ExcelListener extends AnalysisEventListener<Dept> {
    private List<Dept> datas = new ArrayList<>();
    private static final int BATCH_COUNT = 3000;
    private DeptMapper deptMapper;

    public ExcelListener(DeptMapper deptMapper){
        this.deptMapper = deptMapper;
    }

    @Override
    public void invoke(Dept dept, AnalysisContext analysisContext) {
        //数据存储到datas，供批量处理，或后续自己业务逻辑处理。
        datas.add(dept);
        //达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if(datas.size() >= BATCH_COUNT){
            saveData();
            // 存储完成清理datas
            datas.clear();
        }
    }

    private void saveData() {
        for(Dept dept : datas){
            deptMapper.addDept(dept);
        }
    }

    public List<Dept> getDatas() {
        return datas;
    }

    public void setDatas(List<Dept> datas) {
        this.datas = datas;
    }

    /**
     * 所有数据解析完成了 都会来调用
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        saveData();//确保所有数据都能入库
    }

   /* @Override
    public void invoke(Dept dept, AnalysisContext analysisContext) {

    }*/
}