java批量導入Excel數(shù)據(jù)超詳細實例

更新時間：2023年08月23日 10:58:05 作者：java-jcm

這篇文章主要給大家介紹了關于java批量導入Excel數(shù)據(jù)的相關資料,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("導入失敗，上傳文件數(shù)據(jù)不能為空");
        }
        ImportParams params = new ImportParams();
        params.setNeedVerify(true);//是否開啟校驗
        params.setHeadRows(1); //頭行忽略的行數(shù)
        final ExcelImportService excelImportService = new ExcelImportService();
        ExcelImportResult excelImportResult = excelImportService.importExcelByIs(file.getInputStream(), YzLicensedUnit.class, params, false);
        //校驗成功數(shù)據(jù)
        List<YzLicensedUnit> list = excelImportResult.getList();
        final Field failCollection = ExcelImportService.class.getDeclaredField("failCollection");
        failCollection.setAccessible(true);
        //校驗失敗數(shù)據(jù)
        List<YzLicensedUnit> failList = (List) failCollection.get(excelImportService);
        if (list.size() == 0 && failList.size() == 0) {
            return ValueUtil.isError("導入失敗，上傳文件數(shù)據(jù)不能為空");
        }
        if (failList.size() > 0){
            return ValueUtil.isError("導入失敗，上傳文件數(shù)據(jù)與模板不一致");
        }
        //如果沒有錯誤，可以存入數(shù)據(jù)庫
        if (list.size() >= 0 &&  StringUtil.isNotEmpty(list)) {
            //批量插入sql語句
            licensedUnitService.saveBatch(list);
        }else{
            return ValueUtil.isError("導入失敗，上傳文件數(shù)據(jù)不能為空");
        }
        return ValueUtil.toJson("導入成功");
    }

2.實體類

import cn.afterturn.easypoi.excel.annotation.Excel;
@Data
@TableName("數(shù)據(jù)庫表名")
public class YzLicensedUnit {
    //表格有的字段都要加Execl,并且name要跟表格字段一致
    @Excel(name = "持證面積/畝")
    @NotNull(message = "持證面積/畝不能為空")
    private BigDecimal acreage;
    @ApiModelProperty(value = "經(jīng)度")
    private String longitude;
    @ApiModelProperty(value = "緯度")
    private String latitude;
    //replace 表格傳來的值如果等于 是，則字段內容插到表中的是0，否就是1
    @Excel(name = "苗種生產(chǎn)許可證持證單位",replace ={"是_0","否_1"})
    @NotNull(message = "苗種生產(chǎn)許可證持證單位不能為空")
    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) {
      //業(yè)務失敗
      if (row.code == '500') {
        this.$message.error(row.msg);
      } else {
        //業(yè)務成功
        this.$message.success(row.msg);
      }
      this.fileList = [];
      this.search();
    },
    //上傳失敗回調：通信失敗
    fileUploadError(error) {
      error = JSON.parse(error.toString().substr(6));
      this.$message.error(error.msg);
    }
}