上传数据、下载模板文件解决方案（前端：antd；后端：.Net Core WebAPI）
上传数据、下载模板文件解决方案（前端：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 }
以上代码已验证可用，若有疑问，请留言讨论。
漫思
