java批量导入Excel数据

1.后台导入代码

import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.result.ExcelImportResult;
import cn.afterturn.easypoi.excel.imports.ExcelImportService;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.multipart.MultipartFile;


  @ApiOperation(value = "以导入excel方式")
    @PostMapping(value = "/uuApplyUserInfo")
    public String importMonitor(@RequestParam MultipartFile file) throws Exception {

        if (file == null) {
            return ValueUtil.isError("导入失败，上传文件数据不能为空");
        }

        ImportParams params = new ImportParams();
        params.setNeedVerify(true);//是否开启校验
        params.setHeadRows(1); //头行忽略的行数

        final ExcelImportService excelImportService = new ExcelImportService();
        ExcelImportResult excelImportResult = excelImportService.importExcelByIs(file.getInputStream(), YzLicensedUnit.class, params, false);
        //校验成功数据
        List<YzLicensedUnit> list = excelImportResult.getList();

        final Field failCollection = ExcelImportService.class.getDeclaredField("failCollection");
        failCollection.setAccessible(true);
        //校验失败数据
        List<YzLicensedUnit> failList = (List) failCollection.get(excelImportService);

        if (list.size() == 0 && failList.size() == 0) {
            return ValueUtil.isError("导入失败，上传文件数据不能为空");
        }
        if (failList.size() > 0){
            return ValueUtil.isError("导入失败，上传文件数据与模板不一致");
        }
        //如果没有错误，可以存入数据库
        if (list.size() >= 0 &&  StringUtil.isNotEmpty(list)) {
            //批量插入sql语句
            licensedUnitService.saveBatch(list);
        }else{
            return ValueUtil.isError("导入失败，上传文件数据不能为空");
        }

        return ValueUtil.toJson("导入成功");
    }

2.实体类

import cn.afterturn.easypoi.excel.annotation.Excel;

@Data
@TableName("数据库表名")
public class YzLicensedUnit {

    
    //表格有的字段都要加Execl,并且name要跟表格字段一致
    @Excel(name = "持证面积/亩")
    @NotNull(message = "持证面积/亩不能为空")
    private BigDecimal acreage;

    @ApiModelProperty(value = "经度")
    private String longitude;

    @ApiModelProperty(value = "纬度")
    private String latitude;

    //replace 表格传来的值如果等于 是，则字段内容插到表中的是0，否就是1
    @Excel(name = "苗种生产许可证持证单位",replace ={"是_0","否_1"})
    @NotNull(message = "苗种生产许可证持证单位不能为空")
    private String permit;

   
    @Excel(name = "持证编号")
    @NotNull(message = "持证编号不能为空")
    private String number;

    @Excel(name = "持证单位")
    @NotNull(message = "持证单位不能为空")
    private String entName;

2.1设置表格下拉选项 

3.vue前端导入功能代码

      <el-upload
          :auto-upload="true"
          :multiple="false"
          :on-change="handleChange"
          :on-success="fileUploadSuccess"
          :on-error="fileUploadError"
          :file-list="fileList"
          :action="BASE_API"
          name="file"
          accept="application/vnd.ms-excel,application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
      >
        <el-button size="small" type="primary">批量导入</el-button>
      </el-upload>

export default {
  data() {
    return {
      fileList: [],
      //批量导入接口地址
      BASE_API: this.http_url + "/api/uuApplyUserInfo",
     
    };
  },
 methods: {
    handleChange() {
    },
    // 上传多于一个文件时
    fileUploadExceed() {
      this.$message.warning("只能选取一个文件");
    },
    //上传成功回调：通信成功
    fileUploadSuccess(row) {
      //业务失败
      if (row.code == '500') {
        this.$message.error(row.msg);
      } else {
        //业务成功
        this.$message.success(row.msg);
      }
      this.fileList = [];
      this.search();
    },
    //上传失败回调：通信失败
    fileUploadError(error) {
      error = JSON.parse(error.toString().substr(6));
      this.$message.error(error.msg);
    }
}