如何使用excel批量导入数据到数据库？看这一篇就够了！超详细

使用excel批量导入数据是比较常用的功能了。正好最近在做这一块，今天功能上线了，把它记录下来，方便后面再做类似的功能，也方便那些正在做这块功能的。
废话不多说

第一步：导入excel的pom依赖

org.apache.poi poi 3.14 org.apache.poi poi-ooxml 3.14

第二步，编写controller层代码
@PostMapping("/deliverWhiteExcel")
public void deliverWhiteExcel(@RequestParam(“multipartFile”) MultipartFile multipartFile, @RequestParam(“commodityId”) String commodityId) throws IOException {
shySaleService.deliverWhiteAndInsert(multipartFile,commodityId);

}

第三步：编写service层代码

/**
* 解析上传的白名单excel，处理后批量插入表
*/
@Transactional(rollbackFor = Exception.class)
@Override
//参数中commodityId是我这里业务需要的参数，如果不要，//可以不写，但是MultipartFile类型的参数必须要有
public void deliverWhiteAndInsert(MultipartFile multipartFile, String commodityId) throws IOException {
//获取文件名
String fileName=multipartFile.getOriginalFilename();
//获取文件流，读取excel文件
FileInputStream fileInputStream = (FileInputStream)multipartFile.getInputStream();
//创建workbook
Workbook workbook = null;
//获取文件后缀名
String[] fileNameArray=fileName.split("\.");
String extentionName=fileNameArray[fileNameArray.length-1];
if (extentionName.equalsIgnoreCase(“xls”)) {
// 创建2003版excel
workbook = new HSSFWorkbook(fileInputStream);
} else if (extentionName.equalsIgnoreCase(“XLSX”)) {
// 创建2007版excel
workbook = new XSSFWorkbook(fileInputStream);
} else {
throw new RuntimeException(“文件格式不正确”);
}
//得到sheet
Sheet sheet=workbook.getSheetAt(0);
//得到第一行
Row row0=sheet.getRow(0);
Cell cell0=row0.getCell(0);
int cellType=cell0.getCellType(); //如果是字符串类型，则cellType=1
if (cellType !=1){
throw new RuntimeException(“文件导入失败，请根据模板文件导入”);
}
//得到表头标题
String title=cell0.getStringCellValue();
//如果不存在表头或者表头标题不是手机号码，则提示文件导入失败，请根据模板文件导入
if (titlenull || !(title.equals(“手机号码”))){
throw new RuntimeException(“文件导入失败，请根据模板文件导入”);
}
//计算表格总共有多少行
int rowLength=sheet.getPhysicalNumberOfRows();
List phones=new ArrayList<>();
List whiteImportResultList=new ArrayList<>();
List lists=new ArrayList<>();
//WhiteImportResult whiteImportResult=new WhiteImportResult();
//因为第一行写的是各个列的中文标题，例如电话号码,所以从i=1开始循环
for (int i=1;i<rowLength;i++){
//得到行
Row row=sheet.getRow(i);
//如果中间是某一行是空行，则跳过该行
if (rownull){
continue;
}
//得到第i行第一单元格
Cell cell=row.getCell(0);
//如果单元格的值是数值类型的，cellType=0
if (cell.getCellType()==0) {
//获取单元格中的电话号码，因为是电话号码，所以要像这样获得,才不会是以科学计数法给出电话号码
DecimalFormat format = new DecimalFormat("#");
Number value = cell.getNumericCellValue();
String phoneNo = format.format(value);
phones.add(phoneNo);

          }else{
              //如果单元格的值不是数值类型,则跳过该条数据不解析
              continue;
          }
      }
    //解析出来的电话号码的个数
    int Length=phones.size();
      if (Length>0){
          //去掉重复手机号码
          phones = new ArrayList<>(new HashSet<>(phones));
      }
      int phonesLength=phones.size();;
    if (phonesLength!=0){
        for (int i=0;i<phonesLength;i++){
            WhiteImportResult white=new WhiteImportResult();
            if (phones.get(i).length()<11 ){

                white.setMark("电话号码小于11位");
                white.setState(0);
                white.setPhoneNo(phones.get(i));
                whiteImportResultList.add(white);
            }
            if (phones.get(i).length()>11){
                //长度大于11位，取前12位
                white.setPhoneNo(phones.get(i).substring(0,11)+phones.get(i).charAt(11));
                white.setState(0);
                white.setMark("电话号码长度多于11位");
                whiteImportResultList.add(white);
            }if (phones.get(i).length()==11 && phones.get(i).startsWith("0")){
                white.setMark("电话号码已0开头");
                white.setState(0);
                white.setPhoneNo(phones.get(i));
                whiteImportResultList.add(white);
            }
            //合规的手机号码
            if (phones.get(i).length()==11 && ! phones.get(i).startsWith("0")){
                //利用这个手机号码查看是否在用户信息表中是否存在
                UserInfo userInfo=userInfosMapper.selectByPhoneNo(phones.get(i));
                if (userInfo==null){
                    white.setPhoneNo(phones.get(i));
                    white.setState(0);
                    white.setMark("用户信息不存在");
                    whiteImportResultList.add(white);

                }else{
                    //查看在白名单表中该电话号码是否存在
                    List<TabCommodityWhiteList> whiteLists=tabCommodityWhiteListMapper.selectWhiteByOnePhone(phones.get(i));
                    if (!CollectionUtils.isEmpty(whiteLists)){
                        white.setState(0);
                        white.setUserName(userInfo.getUserName()==null?"":userInfo.getUserName());
                        white.setPhoneNo(userInfo.getPhoneNo()==null?"":userInfo.getPhoneNo());
                        white.setUserLevel(userInfo.getUserLevel()==null? 10:userInfo.getUserLevel());
                        white.setMark("该用户在白名单中已经存在");
                        whiteImportResultList.add(white);
                    }else {
                        white.setState(1);
                        white.setPhoneNo(phones.get(i));
                        white.setUserLevel(userInfo.getUserLevel() == null ? 10 : userInfo.getUserLevel());
                        white.setUserName(userInfo.getUserName() == null ? "" : userInfo.getUserName());
                        whiteImportResultList.add(white);
                        TabCommodityWhiteList commodityWhite = new TabCommodityWhiteList();
                        commodityWhite.setLogicDelete(0);
                        commodityWhite.setCommodityId(commodityId);
                        commodityWhite.setPhoneNo(userInfo.getPhoneNo());
                        commodityWhite.setUserId(userInfo.getUserId().longValue());
                        commodityWhite.setUserLevel(userInfo.getUserLevel() == null ? 10 : userInfo.getUserLevel());
                        commodityWhite.setUserName(userInfo.getUserName() == null ? "" : userInfo.getUserName());
                        lists.add(commodityWhite);
                    }
                }
            }
        }
    }else{
        throw new RuntimeException("excel中的电话号码没有一个数值型");
    }
    //将白名单导入结果批量插入白名单导入结果表
    //因为业务需要，公司要求将解析到的excel数据处理一下，插入一个//结果表中,记录操作成功失败等信息
    if (!CollectionUtils.isEmpty(whiteImportResultList)) {
        int counts = whiteImportResultMapper.batchInsertWhite(whiteImportResultList);

            System.out.println("将白名单导入结果信息插入结果表成功：" + counts);

    }
    //将信息导入白名单表(落入业务数据库)
    if (!CollectionUtils.isEmpty(lists)) {
        int count = tabCommodityWhiteListMapper.batchInsertWhiteList(lists);
        System.out.println("将信息导入白名单表条数：" + count);
    }

}

第四步：编写到层代码，将数据插入数据库

Integer batchInsertWhiteList(@Param(“list”) List list);

编写mapper.xml文件

<insert id="batchInsertWhiteList" parameterType="com.hauto.springboomerchant.model.TabCommodityWhiteList">
    insert into tab_commodity_whitelist( id,commodity_id,user_id,user_name,phone_no,user_level,logic_delete,create_time,update_time,create_by,update_by)
    values
    <foreach collection="list" item="item" index="index" separator=",">
        (#{item.id},#{item.commodityId},#{item.userId},#{item.userName},#{item.phoneNo},#{item.userLevel},#{item.logicDelete},#{item.createTime},#{item.updateTime},#{item.createBy},#{item.updateBy})
    </foreach>
</insert>