一.前言项目中使用到比较多的关于Excel的前端上传与下载，整理出来，以便后续使用或分析他人。
1.前端vue:模板下载与导入Excel
导入Excel封装了子组件，点击导入按钮可调用子组件，打开文件上传的对话框，上传成功后返回结果
<el-col> <el-button icon="el-icon-upload" type="primary" size="mini" @click="importFile()" >批量导入</el-button> <el-button icon="el-icon-download" type="primary" size="mini" @click="downloadFile('档案模板')" >模板下载</el-button> <el-button size="mini" type="primary" icon="el-icon-plus" @click="addRow" >新增</el-button> <div> <el-input placeholder="请输入编码,名称" prefix-icon="el-icon-search" v-model="FinQueryParams.archiveFilter" size="mini" ></el-input> </div> </el-col> <!-- 批量导入Dialog开始 --> <uploadTemp :apiURL="fileUploadUrl" ref="refFileUpload" :Refresh="Refresh" :OtherParams="{brandId: QueryParams.BrandID}" ></uploadTemp> <!-- 批量导入Dialog结束 --> importFile() { this.$refs.refFileUpload.open(); }
向后台提交文件的方法
submitFile() { const _this = this; if (!_this.files.name) { _this.$message.warning("请选择要上传的文件！"); return false; } let fileFormData = new FormData(); //filename是键，file是值，就是要传的文件 fileFormData.append("file", _this.files, _this.files.name); if(_this.OtherParams){ const keys=Object.keys(_this.OtherParams); keys.forEach(e=>{ fileFormData.append(e, _this.OtherParams[e]); }) } let requestConfig = { headers: { "Content-Type": "multipart/form-data" } }; AjaxHelper.post(_this.apiURL, fileFormData, requestConfig) .then(res => { console.log(res); if (res.success) { const result = res.result; if (result.errorCount == 0 && result.successCount > 0) { _this.$message({ message: `导入成功,成功${result.successCount}条`, type: "success" }); _this.closeFileUpload(); _this.Refresh(); } else if (result.errorCount > 0 && result.successCount >= 0) { _this.Refresh(); _this.tableData = result.uploadErrors; _this.successCount = result.successCount; _this.innerVisible = true; } else if (result.errorCount == 0 && result.successCount == 0) { _this.$message({ message: `上传文件中数据为空`, type: "error" }); } } }) .catch(function(error) { console.log(error); }); },
这是上传文件的调用方法。
2.模板下载
关于模板下载，之前没有考虑到IE10浏览器的兼容问题，导致在IE10下文件没法下载，后来百度后找到了解决办法。
downloadFile(name) { let requestConfig = { headers: { "Content-Type": "application/json;application/octet-stream" } }; AjaxHelper.post(this.downLoadUrl, requestConfig, { responseType: "blob" }).then(res => { // 处理返回的文件流 const content = res.data; const blob = new Blob([content]); var date = new Date().getFullYear() + "" + (new Date().getMonth() + 1) + "" + new Date().getDate(); const fileName = date + name + ".xlsx"; if ("download" in document.createElement("a")) { // 非IE下载 const elink = document.createElement("a"); elink.download = fileName; elink.style.display = "none"; elink.href = URL.createObjectURL(blob); document.body.appendChild(elink); elink.click(); URL.revokeObjectURL(elink.href); // 释放URL 对象 document.body.removeChild(elink); } else { // IE10+下载 navigator.msSaveBlob(blob, fileName); } }); },
前端的处理就结束了。
3.后端对于文件上传和下载的处理
文件上传
public UploadResult UploadFiles(IFormFile file, Guid brandId) { try { UploadResult uploadResult = new UploadResult(); if (file == null) { throw new UserFriendlyException(501, "上传的文件为空，请重新上传"); } string filename = Path.GetFileName(file.FileName); string fileEx = Path.GetExtension(filename);//获取上传文件的扩展名 string NoFileName = Path.GetFileNameWithoutExtension(filename);//获取无扩展名的文件名 string FileType = ".xls,.xlsx";//定义上传文件的类型字符串 if (!FileType.Contains(fileEx)) { throw new UserFriendlyException(501, "无效的文件类型，只支持.xls和.xlsx文件"); } //源数据 MemoryStream msSource = new MemoryStream(); file.CopyTo(msSource); msSource.Seek(0, SeekOrigin.Begin); DataTable sourceExcel = ReadStreamToDataTable(msSource, "", true); //模板数据 string dataDir = _hosting.WebRootPath;//获得当前服务器程序的运行目录 dataDir = Path.Combine(dataDir, "ExcelTemplate"); var path = dataDir + "//档案模版.xlsx"; MemoryStream msModel = new MemoryStream(); FileStream stream = new FileStream(path, FileMode.Open); stream.CopyTo(msModel); msModel.Seek(0, SeekOrigin.Begin); DataTable templateExcel = ReadStreamToDataTable(stream, "", true); //验证是否同模板相同 string columnName = templateExcel.Columns[0].ColumnName; if (columnName != sourceExcel.Columns[0].ColumnName) { throw new UserFriendlyException(501, "上传的模板文件不正确"); } int sucessCount = 0; int errorCount = 0; // 处理后台逻辑 执行 插入操作 uploadResult.SuccessCount = sucessCount; uploadResult.ErrorCount = errorCount; uploadResult.uploadErrors = errorList; return uploadResult; } catch (Exception ex) { throw new UserFriendlyException(501, "上传的模板文件不正确"); } }
将文件流转化为Datable