使用easyexcel导入62个字段，十万加行数的excel

1️⃣ 准备工作

1. 版本对应

在easyexcel官网的常见问题栏中往下滑找到

2. 下载jar包

maven项目不想多说，在pom.xml文件下，dependcy标签下引入就可以；

在web_inf项目下需要手动引入jar包,在mvn中心仓库，下载对应jar包以及所依赖的其他jar包 

2️⃣ 编写代码

代码结构为很简单的control、dao、impl、entity

1.编写实体类，即你数据库表的对应字段

2.编写dao层

3.编写impl层

4.编写control层

5.xml

3️⃣ 前端html

4️⃣ 代码

1.entity

package com.tiancom.pas.phmxdr.entity;

import lombok.EqualsAndHashCode;
import lombok.Getter;
import lombok.Setter;

/**
 * @Author : YuanXin
 * @create 2023/8/17 10:42
 * @Description : Jxdx_Phdkmx实体类
 */
@Getter
@Setter
@EqualsAndHashCode
public class JxdxPhdkmx {
    private Integer TJRQ;
    private String JG;
    private String SSJG;
    private String EJJG;
    private String JGHZ;
    private String SSQY;
    private String SFYCS;
    private String JRJGBM;
    private String JJH;
    private String HTBH;
    private String KHBH;
    private String KHMC;
    private String KHLX;
    private String KHDL;
    private String SSHY;
    private String SYZXZ;
    private String ZJLX;
    private String ZJHM;
    private String LXDZ;
    private String LXDH;
    private Integer SXZE;
    private String SXEDQJ;
    private Integer CZJE;
    private Integer TCDYSYYE;
    private String FFRQ;
    private String YSDQR;
    private String SJDQR;
    private String ZQDQR;
    private String DKQX;
    private String KM;
    private String YWPZ;
    private Integer YLL;
    private Integer NLL;
    private Integer LXSR;
    private String WJFL;
    private String DKTXDL;
    private String DKTXXL;
    private String DKYT;
    private String BHHYFL;
    private String FSLX;
    private String ZYDBFSDL;
    private String ZYDBFSXL;
    private String ZHDB;
    private String DZYWZL;
    private String BZ;
    private String GHRBH;
    private String CSBZ;
    private String SFXYDK;
    private String SFYJKJPH_BHPJRZ;
    private String SFYJKJPH_HPURZ;
    private String SFRHKJPH;
    private String SFZXZ;
    private String SFLSXD;
    private String SFCYDK;
    private String SFKJXD;
    private String SFSNDK;
    private String SFNH;
    private String SFSCSD;
    private String QD;
    private String SFHTX;
    private String SFZTX;
    private String SFMY;

}

2.dao

package com.tiancom.pas.phmxdr.dao;

import com.tiancom.pas.common.framework.ibatis.IBaseDAO;
import com.tiancom.pas.pagewidget.service.exception.PasCloudException;
import com.tiancom.pas.phmxdr.entity.JxdxPhdkmx;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * @Author : YuanXin
 * @create 2023/8/11 15:50
 * @Description : easyExcel的dao层
 */
@Repository
public class PhmxdrDao {


    @Autowired
    private IBaseDAO ibaseDAO;

    public void save(List<JxdxPhdkmx> list) {

        try{
            String sqlKey = "insert_jxdx_phdkmx";
            ibaseDAO.batchInsert(sqlKey, (List)list);
        }catch(Exception e){
            e.printStackTrace();
            throw new PasCloudException("查询系统状态异常！");
        }

    }

}

3.impl层

package com.tiancom.pas.phmxdr.dao;


import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.fastjson.JSON;
import com.tiancom.pas.phmxdr.dao.PhmxdrDao;
import com.tiancom.pas.phmxdr.entity.JxdxPhdkmx;
import lombok.extern.slf4j.Slf4j;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

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

/**
 * @Author : YuanXin
 * @create 2023/8/11 15:34
 * @Description : easyExcel的实现类，用于监听excel，是读取excel的监听器
 */
@Slf4j
public class PhmxdrImpl extends AnalysisEventListener<JxdxPhdkmx> {


    private static final Logger LOGGER = LoggerFactory.getLogger(JxdxPhdkmx.class);

    private static final int BATCH_COUNT = 100;


    List<JxdxPhdkmx> list = new ArrayList<JxdxPhdkmx>();


    private PhmxdrDao phmxdrDao;

    public PhmxdrImpl(PhmxdrDao phmxdrDao) {
        this.phmxdrDao = phmxdrDao;
    }
    private void saveData() {
        LOGGER.info("{}条数据，开始存储数据库！", list.size());
        phmxdrDao.save(list);
        LOGGER.info("存储数据库成功！");

    }

    @Override
    public void invoke(JxdxPhdkmx jxdxPhdkmx, AnalysisContext analysisContext) {
        LOGGER.info("解析到一条数据:{}", JSON.toJSONString(jxdxPhdkmx));
        list.add(jxdxPhdkmx);
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (list.size() >= BATCH_COUNT) {
            saveData();
            // 存储完成清理 list
            list.clear();
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        saveData();
        LOGGER.info("所有数据解析完成！");
    }
}

4.control层

package com.tiancom.pas.phmxdr.controller;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.cache.MapCache;
import com.tiancom.pas.easyexcel.entity.easyexcelData;
import com.tiancom.pas.phmxdr.dao.PhmxdrDao;
import com.tiancom.pas.phmxdr.entity.JxdxPhdkmx;
import com.tiancom.pas.phmxdr.dao.PhmxdrImpl;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;
import org.springframework.web.multipart.commons.CommonsMultipartResolver;

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

/**
 * @Author : YuanXin
 * @create 2023/8/11 15:44
 * @Description : easyExcel的业务层
 */
@Controller
@RequestMapping("/phmxdr")
public class PhdrmxImportController {

    @Autowired
    private PhmxdrDao phmxdrDao;



    /**
     * 普惠导入明细-导入excel
     */
    @RequestMapping(value = "/readExcel", method = {RequestMethod.POST, RequestMethod.GET})
    @ResponseBody
    public String readExcel(MultipartFile file, HttpServletRequest request) throws IOException {

        CommonsMultipartResolver commonsMultipartResolver = new CommonsMultipartResolver(request.getSession().getServletContext());
        commonsMultipartResolver.setDefaultEncoding("utf-8");
        MultipartHttpServletRequest mulReq = (MultipartHttpServletRequest) request;
        Map<String, MultipartFile> map = mulReq.getFileMap();
        EasyExcel.read(map.get("files").getInputStream(), JxdxPhdkmx.class, new PhmxdrImpl(phmxdrDao)).readCache(new MapCache()).sheet().doRead();

        return "success";

    }



}

5.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-2.dtd" >
<sqlMap namespace="JXDX_PHDKMX">
    <!-- 导入数据至临时表 -->
    <insert id="insert_jxdx_phdkmx" parameterClass="com.tiancom.pas.phmxdr.entity.JxdxPhdkmx">
        insert into jxdx_phdkmx(TJRQ, JG, SSJG, EJJG, JGHZ, SSQY, SFYCS, JRJGBM, JJH, HTBH, KHBH, KHMC, KHLX, KHDL,
                                SSHY, SYZXZ, ZJLX, ZJHM, LXDZ, LXDH, SXZE, SXEDQJ, CZJE, TCDYSYYE, FFRQ, YSDQR, SJDQR,
                                ZQDQR, DKQX, KM, YWPZ, YLL, NLL, LXSR, WJFL, DKTXDL, DKTXXL, DKYT, BHHYFL, FSLX,
                                ZYDBFSDL, ZYDBFSXL, ZHDB, DZYWZL, BZ, GHRBH, CSBZ, SFXYDK, SFYJKJPH_BHPJRZ,
                                SFYJKJPH_HPURZ, SFRHKJPH, SFZXZ, SFLSXD, SFCYDK, SFKJXD, SFSNDK, SFNH, SFSCSD, QD,
                                SFHTX, SFZTX, SFMY)
        values (#TJRQ:INTEGER#,#JG:VARCHAR#, #SSJG:VARCHAR#, #EJJG:VARCHAR#, #JGHZ:VARCHAR#, #SSQY:VARCHAR#, #SFYCS:VARCHAR#, #JRJGBM:VARCHAR#, #JJH:VARCHAR#, #HTBH:VARCHAR#, #KHBH:VARCHAR#, #KHMC:VARCHAR#, #KHLX:VARCHAR#, #KHDL:VARCHAR#, #SSHY:VARCHAR#, #SYZXZ:VARCHAR#, #ZJLX:VARCHAR#, #ZJHM:VARCHAR#, #LXDZ:VARCHAR#, #LXDH:VARCHAR#, #SXZE:NUMBER#, #SXEDQJ:VARCHAR#, #CZJE:NUMBER#, #TCDYSYYE:NUMBER#, #FFRQ:VARCHAR#, #YSDQR:VARCHAR#, #SJDQR:VARCHAR#, #ZQDQR:VARCHAR#, #DKQX:VARCHAR#, #KM:VARCHAR#, #YWPZ:VARCHAR#, #YLL:NUMBER#, #NLL:NUMBER#, #LXSR:NUMBER#, #WJFL:VARCHAR#, #DKTXDL:VARCHAR#, #DKTXXL:VARCHAR#, #DKYT:VARCHAR#, #BHHYFL:VARCHAR#, #FSLX:VARCHAR#, #ZYDBFSDL:VARCHAR#, #ZYDBFSXL:VARCHAR#, #ZHDB:VARCHAR#, #DZYWZL:VARCHAR#, #BZ:VARCHAR#, #GHRBH:VARCHAR#, #CSBZ:VARCHAR#, #SFXYDK:VARCHAR#, #SFYJKJPH_BHPJRZ:VARCHAR#, #SFYJKJPH_HPURZ:VARCHAR#, #SFRHKJPH:VARCHAR#, #SFZXZ:VARCHAR#, #SFLSXD:VARCHAR#, #SFCYDK:VARCHAR#, #SFKJXD:VARCHAR#, #SFSNDK:VARCHAR#, #SFNH:VARCHAR#, #SFSCSD:VARCHAR#, #QD:VARCHAR#, #SFHTX:VARCHAR#, #SFZTX:VARCHAR#, #SFMY:VARCHAR#)
    </insert>


</sqlMap>

6 html

<!DOCTYPE html>
<html lang="en" xmlns:pastag="http://www.w3.org/2001/XMLSchema">
<head>
    <meta charset="UTF-8">
    <title>普惠导入明细</title>
    <link href="../lib/easyui/2.3/css/default/easyui.css" rel="stylesheet" type="text/css"/>
    <link href="../lib/easyui/2.3/css/icon.css" rel="stylesheet" type="text/css"/>
    <link rel="stylesheet" type="text/css" href="../static/layui/css/layui.css"/>
    <link rel="stylesheet" type="text/css" href="../pasplus/runing/css/public.css"/>
    <link rel="stylesheet" type="text/css" href="../lib/layui/common.css"/>
    <link id="themesUI" rel="Stylesheet"
          href="../smart/themes/smartone/blue/css/jquery-ui/jquery-ui-1.9.2.custom.min.css" type="text/css"/>
</head>
<body>
<div id="query_conn_panel">
    <form name="myForm" method="post" id="myForm">
        <table border="0" cellpadding="2" cellspacing="2" id="query_table">
            <tr>
                <td align="right">统计日期:</td>
                <td align="left">
                    <input type="text" name="nd" maxlength="8" value="20230821" style="width:150px;"
                           class="{required:true} Wdate"
                           onfocus="WdatePicker({skin:'default',dateFmt:'yyyyMMdd'})"/>
                </td>
                <td>
                    <input type="button" name="search"
                           class="layui-btn layui-btn-normal layui-icon operation cbutton cbutton_bg_70 layui-btn-common"
                           style="letter-spacing:4px" value="查询" onclick="doSubmit()">
                </td>
                <td>
                    <div style="position: relative;">
                        <input type="button" name="import" value=" 导 入"
                               class="layui-btn layui-btn-warm iconfont operation cbutton cbutton_bg_90 layui-btn-common layui-icon">
                        <input class="scwd" type="file" onchange="toImportPage()">
                    </div>

                </td>
                <td>
                    <input type="button" class="layui-btn layui-btn-warm imgButton iconfont layui-icon" name="export"
                           value="&#xe634; 导 出 模 板"
                           id="exportBtn" onclick="exportResults()"/>
                </td>
            </tr>
        </table>
    </form>
</div>
</body>
<script src="../smart/common/util.js"></script>
<script type="text/javascript">


    var datacolumns = [{
        "title": "统计日期",
        "field": "TJRQ",
        "width": 150,
        "align": "center",
        "hidden": false
    }, {
        "title": "导入条数",
        "field": "CO",
        "width": 150,
        "align": "left",
        "hidden": false,
    }];


    $(document).ready(function () {
        $(document.body).append("<table id='maintable'></table>");
        mtable = $("#maintable");
        mtable.attr("ltop", $(window).height() - mtable.offset().top);
        mtable.datagrid({
            rownumbers: true,
            pagination: true,
            singleSelect: true,
            collapsible: true,
            width: '100%',
            nowrap: true,
            height: $(window).height() - mtable.offset().top,
            onl oadSuccess: function (data) {
                setTimeout(function () {
                    tableAutoResize(); //需要比datagrid的计算更后面
                    var rows = data.rows;
                    for (var i = 0, l = rows.length; i < l; i++) { //设置表格左边的数字列与右边一样的高度
                        var item_arr = $('tr[datagrid-row-index=' + i + ']');
                        item_arr.eq(0).height(item_arr.eq(1).height())
                    }
                }, 0)

            },
            columns: [datacolumns]
        });
    })



    var mtable = "";
    function doSubmit() { //主表查询
        $('.icon-add-self').css('visibility', 'hidden');
        mtable.datagrid({
            url: '/phmxdrOther/findCount.html',
            queryParams: {
                db: db,
                nd: $('input[name=nd]').val()
            }
        });
    }


    function toImportPage() {
        var files = $('.scwd').prop('files');
        var data = new FormData();
        data.append('files', files[0]);
        data.append('filename', files[0].name);
        $('.scwd').val('');
        newAjax.ajax({
            url: '/phmxdr/readExcel.html',
            type: 'POST',
            data: data,
            cache: false,
            processData: false,
            contentType: false
        }).done(function (data) {
            console.log("data",data);
            if(data === "success"){
                custom_alert("导入成功");
            } else {
                custom_alert("导入数据有误,请检查");
            }
            doSubmit();
        });
    }

    function exportResults() {
        newAjax.get('/phmxdrOther/writeExcel.html', {
            nd: $('input[name=nd]').val(),
            contentType: 'application/vnd.ms-excel',
            responseType: "blob"
        }).done(function (res) {
            window.open("/phmxdrOther/writeExcel.html");
            // const link = document.createElement('a')
            // const blob = new Blob([res], { type: 'application/vnd.ms-excel' })
            // console.log("blob",blob);
            // link.style.display = 'none'
            // link.href = URL.createObjectURL(blob)
            // link.setAttribute('download','普惠导入明细.xlsx')
            // document.body.appendChild(link)
            // link.click()
            // document.body.removeChild(link)
        })
    }


</script>
</html>