java批量导入Excel数据

绝地灬酷狼 2024-03-17 09:28 124阅读 0赞

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设置表格下拉选项

![7e23943b210d4bdaa21ea0383c0ebce2.png][]

![ba4a5c74fe5c4e4d8515d2c69940115e.png][]

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);
        }
    }

[7e23943b210d4bdaa21ea0383c0ebce2.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/17/62b2dd158914440bb69813b028e915b5.png
[ba4a5c74fe5c4e4d8515d2c69940115e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/17/25b3a8a7b6f44bedb01f94bcb7eb86c4.png