EXCEL解析导入，多线程批量插入大量数据

EXCEL解析导入，批量插入大量数据

excel导入基本分为3个步骤：

 1. 上传文件
 2. 解析文件
 3. 逻辑内容

首先查看是哪一步需要优化，一般需要在第3步，插入数据库的逻辑优化。
如果数据量比较大的话，单条sql执行很慢，几条数据几次和数据库交互。影响执行效率。解决办法：

1. 批量插入
2. 多线程执行

直接贴代码，项目实例：

 public Result importExcel1() {
        AmroContext context = AmroContextUtil.getAmroContext();
        InputStream instream = null;
        AmroTransactionManager manager = AmroTransactionManager.buildAmroTransactionManager();
        Workbook wb = null;

        MultipartFile mf = context.getMultipartFile("file");
        String sourceId = context.getRequestParamMap().get("sourceId");

        LoginAccount loginAccount = context.getLoginAccount();
        String accountID = loginAccount.getAccountId();
        String userName = loginAccount.getUserName();
        String fileName = mf.getOriginalFilename();
        String msg = "";
        String pkid = "";
        List<EmModCnlList> emModCnlLists = new ArrayList<>();
        Result result = null;


        try {
            instream = mf.getInputStream();
            wb = ExcelUtils.getopenStyle(instream, fileName);
            Sheet sheet = wb.getSheetAt(0);
            /*  int rowNum = sheet.getLastRowNum();*/
            //表头行
            Row row = sheet.getRow(0);
            if (row == null) {
                msg = "第一行为空，请使用模板文件导入！";
                CommonSystemException.throwCustomException(com.bireturn.amro.tdms.constants.ErrorMsgConstant.ERR_DEL_BY_SELF, new Object[]{msg});
            }
            int colNum = row.getPhysicalNumberOfCells();

            if (colNum < 9) {
                msg = "小于9列，请使用模板文件导入！";
                CommonSystemException.throwCustomException(com.bireturn.amro.tdms.constants.ErrorMsgConstant.ERR_DEL_BY_SELF, new Object[]{msg});
            }
            if (colNum != 9) {
                msg = "导入表格列数有误！";
                CommonSystemException.throwCustomException(com.bireturn.amro.tdms.constants.ErrorMsgConstant.ERR_DEL_BY_SELF, new Object[]{msg});
            }
            EmModCnlList emModCnlList = null;
            int rows = sheet.getPhysicalNumberOfRows();
            emModCnlListMapper.delDataByAcPKid(sourceId);

            long bTime1 = System.currentTimeMillis();
            for (int i = 0; i < rows; i++) {

                //过滤表头行
                if (i == 0) {
                    continue;
                }
                /*   if()*/
                /*   pkid = emModCnlListMapper.getPkid();*/
                // 获取当前行的数据
                Row row2 = sheet.getRow(i);
                if (row2.getCell(0) == null && row2.getCell(1) == null && row2.getCell(2) == null && row2.getCell(3) == null
                        && row2.getCell(4) == null && row2.getCell(5) == null && row2.getCell(6) == null && row2.getCell(7) == null && row2.getCell(8) == null
                ) {
                    break;
                }
                emModCnlList = new EmModCnlList();

                int index = 0;
                String value = "";
                for (Cell cell : row2) {
                    if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                        value = StringUtils.isNotBlank(String.valueOf(cell.getNumericCellValue())) ? String.valueOf(cell.getNumericCellValue()) : "";
                    } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
                        value = StringUtils.isNotBlank(cell.getStringCellValue()) ? cell.getStringCellValue() : "";
                    }else{
                        value = StringUtils.isNotBlank(cell.getStringCellValue()) ? cell.getStringCellValue() : "";
                    }
                    switch (index) {
                        case 0:
                            emModCnlList.setMod(value);
                            index++;
                            continue;
                        case 1:
                            emModCnlList.setIsAntiModOf(value);
                            index++;
                            continue;
                        case 2:
                            emModCnlList.setHasForAntiMod(value);
                            index++;
                            continue;
                        case 3:
                            emModCnlList.setTitle(value);
                            index++;
                            continue;
                        case 4:
                            emModCnlList.setMp(value);
                            index++;
                            continue;
                        case 5:
                            emModCnlList.setMscn(value);
                            index++;
                            continue;
                        case 6:
                            emModCnlList.setSb(value);
                            index++;
                            continue;
                        case 7:
                            emModCnlList.setRfc(value);
                            index++;
                            continue;
                        case 8:
                            emModCnlList.setAta(value);
                            index++;
                            break;
                    }
                    break;
                }
                emModCnlList.setAcPkid(sourceId);
                emModCnlList.setImpDate(new Date());
                emModCnlList.setImpMan(userName);
                emModCnlList.setImpManId(accountID);
                emModCnlLists.add(emModCnlList);

            }
            int threadSize = 500;
            int dataSize = emModCnlLists.size();
            // 线程数
            int threadNum = dataSize / threadSize + 1;
            // 定义标记,过滤threadNum为整数
            boolean special = dataSize % threadSize == 0;

            // 创建一个线程池
            ExecutorService exec = Executors.newFixedThreadPool(threadNum);
            // 定义一个任务集合
            List<Callable<Integer>> tasks = new ArrayList<Callable<Integer>>();
            Callable<Integer> task = null;
            List<EmModCnlList> cutList = null;
            System.out.println("MOD清单导入——线程开始执行");
            // 确定每条线程的数据
            for (int i = 0; i < threadNum; i++) {
                if (i == threadNum - 1) {
                    if (special) {
                        break;
                    }
                    cutList = emModCnlLists.subList(threadSize * i, dataSize);
                } else {
                    cutList = emModCnlLists.subList(threadSize * i, threadSize * (i + 1));
                }
                // System.out.println("第" + (i + 1) + "组：" + cutList.toString());
                final List<EmModCnlList> listStr = cutList;
                task = new Callable<Integer>() {

                    @Override
                    public Integer call() throws Exception {
                       /* System.out.println(Thread.currentThread().getName() + "线程：" + listStr);*/
                        emModCnlListMapper.insertObject(listStr);
                        return 1;
                    }
                };
                // 这里提交的任务容器列表和返回的Future列表存在顺序对应的关系
                tasks.add(task);
            }

            List<Future<Integer>> results = exec.invokeAll(tasks);
            //捕捉异常或者....  这句话很重要
            for (Future<Integer> res : results) {
                System.out.println(res.get());
            }

            exec.shutdown();
            System.out.println("线程任务执行结束");
            System.err.println("MOD清单导入——执行任务消耗了 ：" + (System.currentTimeMillis() - bTime1) + "毫秒");
            manager.setIsOk(true);
        } catch (Exception e) {
            e.printStackTrace();
            if (!msg.equals("")) {
                CommonSystemException.throwCustomException(com.bireturn.amro.tdms.constants.ErrorMsgConstant.ERR_DEL_BY_SELF, new Object[]{msg});
            } else {
                CommonSystemException.throwCustomException(com.bireturn.amro.tdms.constants.ErrorMsgConstant.ERR_DEL_BY_SELF, new Object[]{e.toString()});
            }

        } finally {
            manager.transactionCommitOrRollback();
            if (wb != null) {
                try {
                    wb.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            if (instream != null) {
                try {
                    instream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        result = Result.buildSuccess();
        return result;
    }

xml文件：数据库oracle

 <insert id="insertObject"
            parameterType="java.util.List">
       insert  into EM_MOD_CNL_LIST(
        PKID,
        AC_PKID,
        MOD,
        IS_ANTI_MOD_OF,
        HAS_FOR_ANTI_MOD,
        TITLE,
        MP,
        MSCN,
        SB,
        RFC,
        ATA,
        IMP_MAN_ID,
        IMP_MAN,
        IMP_DATE
        )
        SELECT EM_MOD_CNL_LIST_SEQ.nextval,s.* FROM
        (
        <foreach collection="list" item="item" index="index" separator="UNION ALL">select
            #{item.acPkid},
            #{item.mod},
            #{item.isAntiModOf},
            #{item.hasForAntiMod},
            #{item.title},
            #{item.mp},
            #{item.mscn},
            #{item.sb},
            #{item.rfc},
            #{item.ata},
            #{item.impManId},
            #{item.impMan},
            #{item.impDate}
            from dual
        </foreach>
        ) s
    </insert>

我的excel只有一个sheet页所以没有优化。如果多个的话可以看着修改一下，把公共的提取出来。如果数据量很多的话，开很多线程影响内存，可以任务不统一提交，每一个线程执行任务完毕以后就释放，这里只用到简单的，为了省事。
亲测可用！亲测可用！亲测可用！