上传数据、下载模板文件解决方案（前端：antd；后端：.Net Core WebAPI）

 

一、Excel 模板下载

　　通过静态文件下载。

将模板文件放在根目录的 public 文件夹下备用。

下载事件方法如下：（通过临时生成一个 a 标签，触发后再移除）

    downLoadExcelModel = () => {
      var a = document.createElement("a");
      a.href = "./ModelName.xlsx";
      a.download = "模板文件名.xlsx";
      a.style.display = "none";
      document.body.appendChild(a);
      a.click();
      a.remove();
    };

二、上传 Excel 表格

 通过 Upload 控件上传目标文件，然后调用后台接口进行数据处理：（部分简单的变量处理省略）

 1 //先引入 axios
 2     import axios from 'axios';//npm install axios
 3     import { UploadOutlined } from '@ant-design/icons';
 4 
 5 //控件上传事件
 6     uploadDictList=({file, fileList})=>{
 7       console.log("file.status",file.status);
 8       if (file.status === "done") {
 9         const formData = new FormData()
10         formData.append('file', fileList[fileList.length - 1].originFileObj);
11         console.log("formData:",formData);
12         axios({
13             method: 'post',
14             url: '/api/List?paraname='+this.state.typecode,//配置访问接口
15             data: formData,
16             headers: { "Content-Type": "multipart/form-data"}
17         }).then(({data}) => {
18           console.log("baxk-data:",data);
19           if(data.code==200)
20             message.success(`上传成功！(成功/总数:${data.desc})`)
21           else
22             message.error("上传失败，请稍后重试！");
23         }).catch((err) =>{
24             console.log(err);
25             message.error("上传失败，请稍后重试！");
26         })
27       }
28       else if (file.status === "error") {
29         message.error(`上传失败，请稍后重试！${file.name}`);
30       }
31     }
32 
33 //控件
34     <Upload 
35       fileList={fileList}
36       showUploadList={false}
37       onChange={this.uploadDictList}
38     >
39       <Button disabled={uploadable} type="primary" icon={<UploadOutlined />} style={{margin:"20px 20px 20px 0"}}>上传数据项</Button>
40     </Upload>

三、.Net Core 3.0 WebAPI 文件接收与解析

主要就是 IFormCollection 来接收传入文件。

 1     using Dapper;
 2     using Microsoft.AspNetCore.Cors;
 3     using Microsoft.AspNetCore.Http;
 4     using Microsoft.AspNetCore.Mvc;
 5     using NPOI.HSSF.UserModel;
 6     using NPOI.SS.UserModel;
 7     using NPOI.XSSF.UserModel;
 8     using System;
 9     using System.Collections.Generic;
10     using System.Data;
11     using System.IO;
12     using System.Linq;
  1     [HttpPost]
  2     public BackDataModel UploadddList(string ddtype, IFormCollection file)
  3     {
  4         var fileobj = file.Files[0];
  5         string filename = Path.GetFileName(fileobj.FileName);
  6         var filesize = fileobj.Length;//获取上传文件的大小单位为字节byte
  7         string fileType = System.IO.Path.GetExtension(filename);//获取上传文件的扩展名
  8         long maxsize = 4000 * 1024;//定义上传文件的最大空间大小为4M
  9         if (filesize >= maxsize)
 10             return new BackDataModel() { Code = 201, Desc = $"导入失败，表格文件必须小于（4M）。" };
 11         var filestream = fileobj.OpenReadStream();
 12         DataTable dt = new DataTable();
 13         ISheet sheet = null;
 14         IWorkbook workbook = null;
 15         if (fileType == ".xlsx")//2007以上版本excel
 16             workbook = new XSSFWorkbook(filestream);
 17         else if (fileType == ".xls")//2007以下版本excel
 18             workbook = new HSSFWorkbook(filestream);
 19         else
 20             throw new Exception("传入的不是Excel文件！");
 21         sheet = workbook.GetSheetAt(0);//取第一个 sheet
 22         var idddModellist_insert = new List<IdddModel>();
 23         int countall = 0;
 24         if (sheet != null)
 25         {
 26             IRow firstRow = sheet.GetRow(0);//首行值设置为表头
 27             int cellCount = firstRow.LastCellNum;
 28             if (cellCount != 6 || firstRow.Cells[0].StringCellValue != "代码" || firstRow.Cells[1].StringCellValue != "名称" || firstRow.Cells[2].StringCellValue != "备注1"
 29                 || firstRow.Cells[3].StringCellValue != "备注2" || firstRow.Cells[4].StringCellValue != "备注3" || firstRow.Cells[5].StringCellValue != "备注4")
 30             {
 31                 return new BackDataModel() { Code = 201, Desc = $"导入失败，请按照‘模板’填值后重试！" };
 32             }
 33             for (int i = firstRow.FirstCellNum; i < cellCount; i++)
 34             {
 35                 ICell cell = firstRow.GetCell(i);
 36                 if (cell != null)
 37                 {
 38                     string cellValue = cell.StringCellValue.Trim();
 39                     if (!string.IsNullOrEmpty(cellValue))
 40                     {
 41                         DataColumn dataColumn = new DataColumn(cellValue);
 42                         dt.Columns.Add(dataColumn);
 43                     }
 44                 }
 45             }
 46             DynamicParameters dynamicParameters = new DynamicParameters();
 47             dynamicParameters.Add("@typename", ddtype);
 48             string sql_getddinfo = "select * from aa where g>0";
 49             var ddinfolist = db.factory.Query<IdddModel>(sql_getddinfo, dynamicParameters).ToList();
 50             int ddnumber = 1;
 51             for (int j = sheet.FirstRowNum + 1; j <= sheet.LastRowNum; j++)//遍历行
 52             {
 53                 var idddModel = new IdddModel();
 54                 IRow row = sheet.GetRow(j);
 55                 if (row == null || row.Cells.Count != 6 ||
 56                     ((row.GetCell(0) == null || row.GetCell(0).StringCellValue.Length == 0) && (row.GetCell(1) == null || row.GetCell(1).StringCellValue.Length == 0)))//值不为空
 57                 {
 58                     continue;
 59                 }
 60                 countall++;
 61                 if (row.GetCell(0) != null && row.GetCell(1).CellType == CellType.String)
 62                 {
 63                     idddModel.ddcode = row.GetCell(0).ToString();
 64                 }
 65                 if (row.GetCell(1) != null && row.GetCell(1).CellType == CellType.String)
 66                 {
 67                     idddModel.ddname = row.GetCell(1).ToString();
 68                 }
 69                 if (row.GetCell(2) != null)
 70                 {
 71                     idddModel.Remark1 = row.GetCell(2).ToString();
 72                 }
 73                 if (row.GetCell(3) != null)
 74                 {
 75                     idddModel.Remark2 = row.GetCell(3).ToString();
 76                 }
 77                 if (row.GetCell(4) != null)
 78                 {
 79                     idddModel.Remark3 = row.GetCell(4).ToString();
 80                 }
 81                 if (row.GetCell(5) != null)
 82                 {
 83                     idddModel.Remark4 = row.GetCell(5).ToString();
 84                 }
 85                 if (ddinfolist.Where(dd => dd.ddcode == idddModel.ddcode && dd.ddname == idddModel.ddname)
 86                 {
 87                     idddModel.ddtypecode = ddinfolist?[0].ddtypecode;
 88                     idddModel.ddtypename = ddinfolist?[0].ddtypename;
 89                     idddModel.ID = Guid.NewGuid().ToString();
 90                     idddModel.Snumber = ddinfolist.Count + ddnumber;
 91                     idddModel.Insert_time = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
 92                     idddModel.Update_time = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
 93                     idddModel.ff = 1;
 94                     idddModellist_insert.Add(idddModel);
 95                     ddnumber++;
 96                 }
 97             }
 98             string sql_insertdd = $"insert into ";
 99             int backnum = db.factory.Execute(sql_insertdd, idddModellist_insert);
100             if (backnum != 1 && idddModellist_insert.Count > 0)
101                 return new BackDataModel() { Code = 201, Desc = $"数据保存不成功，请刷新列表确认！" };
102         }
103         else
104         {
105             return new BackDataModel() { Code = 201, Desc = $"导入失败，未取到表格中数据！" };
106         }
107         return new BackDataModel() { Code = 200, Desc = $"{idddModellist_insert.Count}/{countall}" };
108     }

以上代码已验证可用，若有疑问，请留言讨论。

posted @ 2022-03-10 18:38  橙子家  阅读(649)  评论(4)  编辑  收藏  举报