Excel导入数据

1.前端上传文件，

参考ElEmentUI 组件

 1             <el-upload
 2               class="upload-excel"
 3               accept=".xls, .xlsx"
 4               ref="uploadExcel"
 5               :multiple="false"
 6               :auto-upload="true"
 7               :limit="1"
 8               action=""
 9               :http-request="submitUpload"
10               :file-list="fileList"
11               :show-file-list="false"
12             >
13               <el-button
14                 size="small"
15                 type="primary" 
16                 class="el-icon-upload"
17                 plain
18               >导入</el-button>
19             </el-upload>
20 
21       // 上传文件excel
22       submitUpload(file) {
23         const formData = new FormData();
24         formData.append('file', file.file);
25         $axios.post("/api/import", formData,{headers: {'content-type':'multipart/form-data;'}})
26         .then(res => {
27           this.fileList = [];
28           if (res.data) {
29             this.$message.success('导入成功');
30             this.searchData();
31           }
32         });
33       },

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

 1 [HttpPost("import")]
 2 public async Task<bool> ImportAsync()
 3 {
 4     if (Request.Form.Files.Count == 0)
 5         throw new BusinessException(message: "请选择文件");
 6     if (Request.Form.Files.Count > 1)
 7         throw new BusinessException(message: "只能上传单个文件");
 8     var file = Request.Form.Files[0];
 9     return await _manager.ImportAsync(file);
10 }
11 
12 //读取文件，单独放扩展类ImportExtensions
13 
14 public static async Task<List<T>> ImportData<T>(IFormFile file, Func<ISheet, Task<List<T>>> ReadData)
15 {
16 
17     var extension = Path.GetExtension(file.FileName);
18 
19     IWorkbook workbook = null;
20     if (extension.Equals(".xlsx"))
21     {
22         workbook = new XSSFWorkbook(file.OpenReadStream());
23     }
24     else
25     {
26         workbook = new HSSFWorkbook(file.OpenReadStream());
27     }
28 
29     ISheet worksheet = workbook.GetSheetAt(0);
30 
31     // read data
32     var list = await ReadData(worksheet);
33 
34     return list;
35 }
36 
37 //manager里面代码
38 
39   public async Task<bool> ImportAsync(IFormFile file)
40   {
41       //读取文件
42       var list = await ImportExtensions.ImportData(file, ReadDataFromSheet);
43 
44       //去除已存在的
45       var accounts = list.Select(x => x.account);
46       var repectAccounts = (await _repository.GetListAsync(x => accounts .Contains(x.account)))
47           .Select(x => x.account);
48 
49       var distinctList = list.Where(x => !repectAccounts .Contains(x.account));
50 
51       //插入数据库
52       await _repository.InsertManyAsync(distinctList);
53 
54       return true;
55   }
56 
57  private async Task<List<Account>> ReadDataFromSheet(ISheet sheet)
58  {
59      var list = new List<Account>();
60      var i = 1;
61      while (sheet != null)
62      {
63          var row = sheet.GetRow(i);
64          if (row.Cells.Count == 0)
65          {
66              break;
67          }
68 
69          Account entity = new Account()
70          {
71              account= row.GetCell(0).StringCellValue.Trim(),
72              age= row.GetCell(1).NumericCellValue,
73          };
74 
75          list.Add(entity);
76          i++;
77      }
78 
79      return list;
80  }