Excel导入数据

1.前端上传文件，

参考ElEmentUI 组件

            <el-upload
              class="upload-excel"
              accept=".xls, .xlsx"
              ref="uploadExcel"
              :multiple="false"
              :auto-upload="true"
              :limit="1"
              action=""
              :http-request="submitUpload"
              :file-list="fileList"
              :show-file-list="false"
            >
              <el-button
                size="small"
                type="primary" 
                class="el-icon-upload"
                plain
              >导入</el-button>
            </el-upload>

      // 上传文件excel
      submitUpload(file) {
        const formData = new FormData();
        formData.append('file', file.file);
        $axios.post("/api/import", formData,{headers: {'content-type':'multipart/form-data;'}})
        .then(res => {
          this.fileList = [];
          if (res.data) {
            this.$message.success('导入成功');
            this.searchData();
          }
        });
      },

2.后端接收文件,先读取Excel文件，然后处理数据，最后批量插入数据库，

[HttpPost("import")]
public async Task<bool> ImportAsync()
{
    if (Request.Form.Files.Count == 0)
        throw new BusinessException(message: "请选择文件");
    if (Request.Form.Files.Count > 1)
        throw new BusinessException(message: "只能上传单个文件");
    var file = Request.Form.Files[0];
    return await _manager.ImportAsync(file);
}

//读取文件，单独放扩展类ImportExtensions

public static async Task<List<T>> ImportData<T>(IFormFile file, Func<ISheet, Task<List<T>>> ReadData)
{

    var extension = Path.GetExtension(file.FileName);

    IWorkbook workbook = null;
    if (extension.Equals(".xlsx"))
    {
        workbook = new XSSFWorkbook(file.OpenReadStream());
    }
    else
    {
        workbook = new HSSFWorkbook(file.OpenReadStream());
    }

    ISheet worksheet = workbook.GetSheetAt(0);

    // read data
    var list = await ReadData(worksheet);

    return list;
}

//manager里面代码

  public async Task<bool> ImportAsync(IFormFile file)
  {
      //读取文件
      var list = await ImportExtensions.ImportData(file, ReadDataFromSheet);

      //去除已存在的
      var accounts = list.Select(x => x.account);
      var repectAccounts = (await _repository.GetListAsync(x => accounts .Contains(x.account)))
          .Select(x => x.account);

      var distinctList = list.Where(x => !repectAccounts .Contains(x.account));

      //插入数据库
      await _repository.InsertManyAsync(distinctList);

      return true;
  }

 private async Task<List<Account>> ReadDataFromSheet(ISheet sheet)
 {
     var list = new List<Account>();
     var i = 1;
     while (sheet != null)
     {
         var row = sheet.GetRow(i);
         if (row.Cells.Count == 0)
         {
             break;
         }

         Account entity = new Account()
         {
             account= row.GetCell(0).StringCellValue.Trim(),
             age= row.GetCell(1).NumericCellValue,
         };

         list.Add(entity);
         i++;
     }

     return list;
 }