springboot中整合POI实现上传数据导入

1.添加pom依赖

<!--poi -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.17</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.17</version>
</dependency>

2.前台实现文件上传

html：

<form id="yjtzForm" class="form-inline" method="post" enctype="multipart/form-data">
      <div class="form-group">
          <label for="yjtz"> 移交台账文件:</label>
          <input type="file"  class="form-control" id="yjtz" name="yjtz">
      </div>
      <button id="yjtzBtn" class="btn btn-primary" onclick="FwdjcxImp.yjtzB()" >移交台账导入</button>
</form>

js：

var FwdjcxImp = {

};
FwdjcxImp.yjtzB=function() {
    //var form = $("#yjtzForm").serialize();
    var form = new FormData(document.getElementById("yjtzForm"));
    //console.log(form);
    //提交信息
    $.ajax({
        url: Feng.ctxPath + "/fwdjcx/yjtzImport",
        type: 'POST',
        dataType: "json",
        async: false,
        cache: false,
        data: form,
        processData: false,
        contentType: false,
        success: function (result) {
            //console.log(result)
            Feng.success(result);
        },
        error: function (err) {
            Feng.error("导入失败!");
        }
    });

}

3.后台接收文件，并读取exel内容，保存到数据库中

controller：

@PostMapping("/yjtzImport")
    @ResponseBody
    public String yjtzImport(@RequestParam("yjtz") MultipartFile file, HttpServletRequest request) throws Exception {

        //接收到的文件转成输入流
        InputStream inputStream = file.getInputStream();
        String result = fwdjcxzmService.yjtzImport(inputStream);

        return result;
    }

service：

@Override
    public String yjtzImport(InputStream inputStream) throws Exception {

        Integer addNum = 0;
        Integer updateNum = 0;

        //创建工作簿
        Workbook wb = WorkbookFactory.create(inputStream);
        //读取第一个sheet
        Sheet sheet = wb.getSheetAt(0);
        for (int r =2;r<sheet.getPhysicalNumberOfRows();r++){
            Row row = sheet.getRow(r);
            String qlr = row.getCell(2).getStringCellValue();
            if (StringUtils.isNotEmpty(qlr)){
                //将行中的数据转成对象
                Fwdjyjtz fwdjyjtz = new Fwdjyjtz(row);

                //1.根据 坐落＿权利人＿证号 进行查询，是否存在记录
                EntityWrapper<Fwdjyjtz> entityWrapper = new EntityWrapper<>();
                entityWrapper.eq("QLR",qlr);
                List<Fwdjyjtz> list = fwdjyjtzMapper.selectList(entityWrapper);
                //2.若存在更新，不存在新增
                if (CollectionKit.isNotEmpty(list)){
                    for (Fwdjyjtz f:list){
                        fwdjyjtz.setId(f.getId());
                        fwdjyjtzMapper.updateById(f);
                        updateNum ++;
                    }
                }else {
                    fwdjyjtzMapper.insert(fwdjyjtz);
                    addNum ++;
                }
            }
        }
        return "成功新增:"+addNum+"行,更新:"+updateNum;
    }

model：

    public Fwdjyjtz(Row row) {
        this.zh = row.getCell(1).getStringCellValue();
        this.qlr = row.getCell(2).getStringCellValue();
        this.sfzh = row.getCell(3).getStringCellValue();
        //登记时间
        String djsj = row.getCell(4).getStringCellValue();
        //this.djrq = StringUtils.isNotEmpty(djsj.trim())? DateTimeKit.parse(djsj.replace(".","-")):null;

        this.scdj = row.getCell(5).getStringCellValue();
        this.yt = row.getCell(6).getStringCellValue();
        this.cwbj = row.getCell(7).getStringCellValue();
        this.zyy = row.getCell(8).getStringCellValue();
        if (row.getCell(9).getCellTypeEnum().equals(CellType.NUMERIC)){
            this.ym = String.valueOf(row.getCell(9).getNumericCellValue());
        }else {
            this.ym = row.getCell(9).getStringCellValue();
        }
        this.bz = row.getCell(10).getStringCellValue();
    }

        实现的逻辑很简单，①从前台上传exel文件②接收文件③读取exel文件内容并保存。我们的项目持久层用的mybatis-plus，可以根据自己项目的持久层更改入库的代码。