Vue前端上传EXCEL文件，后端（springBoot+MyBatis+MySQL）解析EXCEL并批量插入/更新数据库

Vue前端

前端主要用了element-ui的upload组件。

      <el-upload
        class="filter-item"
        name="excelFile"
        :action="uploadUrl()"
        :on-error="uploadFalse"
        :on-success="uploadSuccess"
        :before-upload="beforeAvatarUpload"
        :limit="1"
         accept=".xlsx,.xls"
        :show-file-list="false"
        :file-list="fileList">
         <el-button  style="margin-left: 10px;" icon="el-icon-edit" type="primary">批量上架</el-button>
       </el-upload>

关于每个字段的意思：

uploadUrl() 是后台接口（接受上传的文件并做后端的逻辑处理） 注意：uploadUrl方法中，直接return的是你的后端URL接口，可以是相对路径，也可以是绝对路径。

upLoadData是上传文件时要上传的额外参数，也可以不写，将参数直接带在URL请求中
形似： url + “?businessName=” + this.businessName

uploadError是上传文件失败时的回掉函数
uploadSuccess是文件上传成功时的回掉函数

beforeAvatarUpload是在上传文件之前调用的函数，可以在这里进行文件类型的判断，对上传格式及大小作限制

**:auto-upload=“false”**是停止文件自动上传模式

**name=“excelFile”**这里是将导入的EXCEL文件命名，并传给后端，所以后端接口的入参也要是这个名字，下面会贴代码。

:file-list=“fileList” 显示已上传的文件列表

 uploadSuccess(response, file, fileList) {
      console.log(response)
      if (response.code==200) {
        this.$message({
          message: response.message,
          type: 'success'
        });
      } else {
        this.$message({
          message: response.message,
          type: 'error'
        });
      }
    },
    uploadFalse(response, file, fileList) {
      this.$message({
          message: '文件上传失败！',
          type: 'error'
        });
    },
    // 上传前对文件的大小的判断
    beforeAvatarUpload(file) {
      const extension = file.name.split(".")[1] === "xls";
      const extension2 = file.name.split(".")[1] === "xlsx";
      const isLt2M = file.size / 1024 / 1024 < 10;
         if (!extension && !extension2) {
           this.$message({
            message: '上传模板只能是 xls、xlsx格式!',
            type: 'error'
           });
          }
          if (!isLt2M) {
           console.log("上传模板大小不能超过 10MB!");
           this.$message({
            message: '上传模板大小不能超过 10MB!',
            type: 'error'
          });
         }
        return extension || extension2 || extension3 || (extension4 && isLt2M);
    },
    uploadUrl: function() {
      return (
        process.env.VUE_APP_BASE_API+'/good/ExcelInsertGoodinStore'+
        "?businessid=" +
        this.$store.getters.id
      );
    },

后端

controller层

  @RequestMapping(value = "/ExcelInsertGoodinStore", method = RequestMethod.POST)
    @ResponseBody
    public RetResult ExcelInsertGoodinStore(@RequestParam("excelFile") MultipartFile excelFile,
                                           @RequestParam(value = "businessid", required = true ) int businessid) {
        String name = excelFile.getOriginalFilename();
        try {
            if(goodService.excelToList(excelFile.getInputStream(),businessid)>0) {
                return new RetResult().setCode(RetCode.SUCCESS).setMessage("导入Excel批量添加商品成功！");
            }else return new RetResult().setCode(RetCode.FAIL).setMessage("导入Excel批量添加商品失败！");
        } catch (Exception e) {
            e.printStackTrace();
            return new RetResult().setCode(RetCode.FAIL).setMessage("文件内容出现错误，导致无法正确导入");
        }
    }

service层：如何解析Excel文件

 public int  excelToList(InputStream inputStream,int businessid) throws IOException, InvalidFormatException {
        List<GoodinStore> gisList=new ArrayList<GoodinStore>();
        Workbook workbook = null;
        workbook = WorkbookFactory.create(inputStream);
        inputStream.close();
        //工作表对象
        Sheet sheet = workbook.getSheetAt(0);
        //总行数
        int rowLength = sheet.getLastRowNum() + 1;
        //工作表的列
        Row row = sheet.getRow(0);
        //总列数
        int colLength = row.getLastCellNum();
        for (int i = 1; i < rowLength; i++) {
            row = sheet.getRow(i);
            row.getCell(0).setCellType(CellType.STRING);
            String cell0 = row.getCell(0).getStringCellValue().trim();
            GoodinStore gis=new GoodinStore();
            .....
            gisList.add(gis);
            }
        }
        return goodMapper.insertMultiGis(gisList);
    }

注意：
使用了getStringCellValue()方法来获取值，POI会判断单元格的类型，如果非字符串类型就会抛出异常。

Exception in thread “main” java.lang.IllegalStateException: Cannot get a STRING value from a NUMERIC cell

解决方法是在读取某单元格时，使用setCellType()方法先将该单元格的类型设置为STRING。

row.getCell(0).setCellType(CellType.STRING);

MyBatis：实现批量插入

利用唯一索引和ON DUPLICATE KEY UPDATE实现重复覆盖、不重复插入的操作

<!--批量添加上架商品-->
    <insert id="insertMultiGis" parameterType="java.util.List"  useGeneratedKeys="true" keyProperty="gis_id" keyColumn="gis_id">
        INSERT INTO goodinstore
        (goodid,businessid,retailprice,min_stock,stock)
        values
        <foreach collection="list" item="item" index="index" separator=",">
         (#{item.good.id},#{item.businessid},#{item.retailPrice},#{item.min_stock},#{item.stock})
        </foreach>
        ON DUPLICATE KEY UPDATE
        retailprice = values(retailprice),
        min_stock = values(min_stock),
        stock = values(stock)
    </insert>

注意：useGeneratedKeys=“true” keyProperty=“gis_id” keyColumn="gis_id"实现主键自增

在mysql中设置唯一索引Unique Index

UNIQUE KEY的用途：主要是用来防止数据插入的时候重复的。
1，创建表时 CREATE TABLE Persons ( Id_P int NOT NULL, LastName
varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255),
City varchar(255), UNIQUE (Id_P) ) 如果需要命名 UNIQUE 约束，以及为多个列定义 UNIQUE
约束，请使用下面的 SQL 语法： CREATE TABLE Persons ( Id_P int NOT NULL,
LastName varchar(255) NOT NULL, FirstName varchar(255), Address
varchar(255), City varchar(255), CONSTRAINT uc_PersonID UNIQUE
(Id_P,LastName) )

2，当表已被创建时，如需在 “Id_P” 列创建 UNIQUE 约束，请使用下列 SQL：
ALTER TABLE Persons ADD UNIQUE (Id_P)
如需命名 UNIQUE 约束，并定义多个列的 UNIQUE 约束，请使用下面的 SQL 语法：
ALTER TABLE Persons ADD CONSTRAINT uc_PersonID UNIQUE (Id_P,LastName)

3，撤销 UNIQUE 约束

如需撤销 UNIQUE 约束，请使用下面的 SQL： MySQL:

ALTER TABLE Persons DROP INDEX uc_PersonID

MySQL中的insert ignore into, replace into等的一些用法总结

1.insert ignore into

当插入数据时，如出现错误时，如重复数据，将不返回错误，只以警告形式返回。所以使用ignore请确保语句本身没有问题，否则也会被忽略掉。例如：
INSERT IGNORE INTO books (name) VALUES (‘MySQL Manual’) INSERT IGNORE INTO books (name) VALUES (‘MySQL Manual’),(‘NGINX Manual’),(‘REDIS Manual’)

2.on duplicate key update 当primary或者unique重复时，则执行update语句，如update后为无用语句，如id=id，则同1功能相同，但错误不会被忽略掉。例如，为了实现name重复的数据插入不报错：
INSERT INTO books (name) VALUES (‘MySQL Manual’) ON duplicate KEY UPDATE id = id

3.insert … select … where not exist 根据select的条件判断是否插入，可以不光通过primary 和unique来判断，也可通过其它条件。例如：
INSERT INTO books (name) SELECT ‘MySQL Manual’ FROM dual WHERE NOT EXISTS (SELECT id FROM books WHERE id = 1)

4.replace into 如果存在primary or unique相同的记录，则先删除掉。再插入新记录。注意若原记录存在(a1,b1,c1)，新记录为(a1,b2)，则replace后c字段为null
REPLACE INTO books SELECT 1, ‘MySQL Manual’ FROM books