阿里EasyExcel解析excel并导入数据库（相较于poi可防止内存溢出）

前言

使用poi进行excel文件的解析时，容易出现内存溢出的问题，前面用阿里的EasyExcel实现了excel的导出，亦可使用EasyExcel对excel文件进行解析。

---

一、EasyExcel解析excel的工具类

多种解析类型的供选择，具体代码如下：

package com.gettop.bi.core.util;

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 org.springframework.web.multipart.MultipartFile;

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


public class ExcelUtil {
    /**
     * 读取 Excel(多个 sheet)
     *
     * @param excel    文件
     * @param rowModel 实体类映射，继承 BaseRowModel 类
     * @return Excel 数据 list
     */
    public static List<Object> readExcel(MultipartFile excel, BaseRowModel rowModel) throws Exception {
        ExcelListener excelListener = new ExcelListener();
        ExcelReader reader = getReader(excel, excelListener);
        if (reader == null) {
            return null;
        }
        for (Sheet sheet : reader.getSheets()) {
            if (rowModel != null) {
                sheet.setClazz(rowModel.getClass());
            }
            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) throws Exception {
        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) throws Exception {
        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 ：一个 sheet，带表头
     *
     * @param response  HttpServletResponse
     * @param list      数据 list，每个元素为一个 BaseRowModel
     * @param fileName  导出的文件名
     * @param sheetName 导入文件的 sheet 名
     * @param object    映射实体类，Excel 模型
     */
    public static void writeExcel(HttpServletResponse response, List<? extends BaseRowModel> list,
                                  String fileName, String sheetName, BaseRowModel object) {
        ExcelWriter writer = new ExcelWriter(getOutputStream(fileName, response), ExcelTypeEnum.XLSX);
        Sheet sheet = new Sheet(1, 0, object.getClass());
        sheet.setSheetName(sheetName);
        writer.write(list, sheet);
        writer.finish();
    }

    /**
     * 导出文件时为Writer生成OutputStream
     */
    private static OutputStream getOutputStream(String fileName, HttpServletResponse response) {
        //创建本地文件
        String filePath = fileName + ".xlsx";
        File dbfFile = new File(filePath);
        try {
            if (!dbfFile.exists() || dbfFile.isDirectory()) {
                dbfFile.createNewFile();
            }
            fileName = new String(filePath.getBytes(), "ISO-8859-1");
            response.addHeader("Content-Disposition", "filename=" + fileName);
            return response.getOutputStream();
        } catch (IOException e) {
            e.printStackTrace();
            return null;
        }
    }


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

}

二、实体类添加注解，使实体类与excel对应

使用EasyExcel里的@ExcelProperty注解，具体代码如下：

package com.gettop.bi.web.model;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.BaseRowModel;
import lombok.Data;
import lombok.ToString;

import java.math.BigDecimal;

@Data
@ToString
public class BiFinanceFeeDetail extends BaseRowModel {

    private String id;

    @ExcelProperty(index = 0)
    private String versionCode;

    @ExcelProperty(index = 1)
    private String propertyCode;

    @ExcelProperty(index = 2)
    private String sbuCode;

    @ExcelProperty(index = 3)
    private String projectCode;

    @ExcelProperty(index = 4)
    private String departmentBigSort;

    @ExcelProperty(index = 5)
    private String departmentMotivateSort;

    @ExcelProperty(index = 6)
    private String departmentSort;

    @ExcelProperty(index = 7)
    private String budgetEntity;

    @ExcelProperty(index = 8)
    private String subjectLevel1;

    @ExcelProperty(index = 9)
    private String subjectLevel2;

    @ExcelProperty(index = 10)
    private String feeSplitSort;

    @ExcelProperty(index = 11)
    private String feeSort;

    @ExcelProperty(index = 12)
    private String yearMonth;

    @ExcelProperty(index = 13)
    private BigDecimal feeMoney;

    @ExcelProperty(index = 14)
    private String remark;

    @ExcelProperty(index = 15)
    private String quotationProjectName;

    @ExcelProperty(index = 16)
    private String buInnerExternal;

    @ExcelProperty(index = 17)
    private String ghWorkshop;

    @ExcelProperty(index = 18)
    private BigDecimal zfApportionRate;

    @ExcelProperty(index = 19)
    private String yfShowDepartment;

    @ExcelProperty(index = 20)
    private String iyear;
}

三、controller调用，解析excel

具体代码如下：

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.util.*;


@Controller
@Transactional
@RequestMapping(value = "/excel")
public class BiExcelController extends Thread{

    @Resource
    private BiExcelService biExcelService;

    private static final String FILE_PATH="D:\\caijing_file";
    /**
     * 导入
     *
     * @param
     * @return
     */


    @PostMapping("/importExcelBefore")
    @ResponseBody
    public Map<String, Object> importExcelBefore(@RequestParam(value = "file") List<MultipartFile> files, HttpServletRequest request) throws Exception {

        Map<String, Object> result = new HashMap<>();

        if (files != null && files.size() > 0) {
            MultipartFile file = files.get(0);
            String name = file.getOriginalFilename();

            File targetDir = new File(FILE_PATH);
            if (!targetDir.exists()) targetDir.mkdirs();
            File targetFile = new File(FILE_PATH + File.separator + name);
            IOUtils.write(IOUtils.toByteArray(file.getInputStream()), new FileOutputStream(targetFile));
            String filePath = targetFile.toString();
            if(file.getOriginalFilename().equals("xx.xlsx")){
                List list=  ExcelUtil.readExcel(file,new BiFinanceFeeDetail());
                result = biExcelService.importExcelBefore(list, name);
            }else if(file.getOriginalFilename().equals("xx.xlsx")){
                List list=  ExcelUtil.readExcel(file, new BiFinanceCustPay());
                result = biExcelService.importExcelBefore(list, name);
            }else if(file.getOriginalFilename().equals("xx.xlsx")){
                List list=  ExcelUtil.readExcel(file, new BiFinanceFeePersonDetail());
                result = biExcelService.importExcelBefore(list, name);
            }else{
                result.put("fail",1);
            }
            result.put("filePath",targetFile.getAbsolutePath());
        }
        return result;
    }

四、server层代码

代码如下：

public interface BiExcelService {
    Map<String, Object> importExcelBefore(List<Object> list, String name);
}

采用批量插入的方法导入数据库，mapper里用foearch ，serverImpl代码如下：

@Service
public class BiExcelServiceImpl implements BiExcelService {

    @Resource
    private BiFinanceFeeDetailMapper biFinanceFeeDetailMapper;
    @Resource
    private BiFinanceCustPayMapper biFinanceCustPayMapper;
    @Resource
    private BiFinanceFeePersonDetailMapper biFinanceFeePersonDetailMapper;
        @Override
    public Map<String, Object> importExcelBefore(List<Object> list, String name) {
        Map<String, Object> messageMap = new HashMap<>();
                    int nums = 1000;
                    int times = (int)Math.ceil((float)list.size() / nums);// 插入次数
                    /** 获取当前系统时间*/
                    long startTime =  System.currentTimeMillis();
                    try {
                        for (int i = 0; i < times; i++){
                            if(i == times - 1){
                                biFinanceFeeDetailMapper.batchSave(list.subList(i * nums, list.size()));
                                messageMap.put("success",1);
                            }else {
                                biFinanceFeeDetailMapper.batchSave(list.subList(i * nums, (i+1) * nums));
                                messageMap.put("success",1);
                            }
                        }
                        long endTime =  System.currentTimeMillis();
                        long usedTime = (endTime-startTime)/1000;
                        System.out.println(usedTime);
                 }
           }

五、mapper层代码

代码如下：

public interface BiFinanceFeeDetailMapper {
   
    int batchSave(@Param("lists") List<Object> list);

}

mapperImpl代码：

  <insert id="batchSave" parameterType="com.gettop.bi.web.model.BiFinanceFeeDetail" useGeneratedKeys="false">
    insert into BI_FINANCE_FEE_DETAIL (ID,VERSION_CODE, PROPERTY_CODE,
                                       SBU_CODE, PROJECT_CODE, DEPARTMENT_BIG_SORT,
                                       DEPARTMENT_MOTIVATE_SORT, DEPARTMENT_SORT,
                                       BUDGET_ENTITY, SUBJECT_LEVEL1, SUBJECT_LEVEL2,
                                       FEE_SPLIT_SORT, FEE_SORT, YEAR_MONTH,
                                       FEE_MONEY, REMARK, QUOTATION_PROJECT_NAME,
                                       BU_INNER_EXTERNAL, GH_WORKSHOP, ZF_APPORTION_RATE,
                                       YF_SHOW_DEPARTMENT, IYEAR)
           <foreach collection="lists" item="list" index="index" separator="union all">
            SELECT
             sys_guid(),
            #{list.versionCode,jdbcType=VARCHAR}, #{list.propertyCode,jdbcType=VARCHAR},
            #{list.sbuCode,jdbcType=VARCHAR}, #{list.projectCode,jdbcType=VARCHAR}, #{list.departmentBigSort,jdbcType=VARCHAR},
            #{list.departmentMotivateSort,jdbcType=VARCHAR}, #{list.departmentSort,jdbcType=VARCHAR},
            #{list.budgetEntity,jdbcType=VARCHAR}, #{list.subjectLevel1,jdbcType=VARCHAR}, #{list.subjectLevel2,jdbcType=VARCHAR},
            #{list.feeSplitSort,jdbcType=VARCHAR}, #{list.feeSort,jdbcType=VARCHAR}, #{list.yearMonth,jdbcType=VARCHAR},
            #{list.feeMoney,jdbcType=DECIMAL}, #{list.remark,jdbcType=VARCHAR}, #{list.quotationProjectName,jdbcType=VARCHAR},
            #{list.buInnerExternal,jdbcType=VARCHAR}, #{list.ghWorkshop,jdbcType=VARCHAR}, #{list.zfApportionRate,jdbcType=DECIMAL},
            #{list.yfShowDepartment,jdbcType=VARCHAR}, #{list.iyear,jdbcType=VARCHAR}
            FROM dual
           </foreach>
  </insert>

---

总结

使用阿里的EasyExcel插件可以非常简单高效的实现excel的导入和导出。