使用easyexcel导入62个字段，十万加行数的excel
使用easyexcel导入62个字段，十万加行数的excel
1️⃣ 准备工作
1. 版本对应
在easyexcel官网的常见问题栏中往下滑找到
2. 下载jar包
maven项目不想多说，在pom.xml文件下，dependcy标签下引入就可以；
在web_inf项目下需要手动引入jar包,在mvn中心仓库，下载对应jar包以及所依赖的其他jar包
编写代码
代码结构为很简单的control、dao、impl、entity
1.编写实体类，即你数据库表的对应字段
2.编写dao层
3.编写impl层
4.编写control层
5.xml
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=" 导 出 模 板" 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, onLoadSuccess: 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>
