一.前言項目中使用到比較多的關于Excel的前端上傳與下載，整理出來，以便后續使用或分析他人。
1.前端vue:模板下載與導入Excel
導入Excel封裝了子組件，點擊導入按鈕可調用子組件，打開文件上傳的對話框，上傳成功后返回結果
<el-col style="padding: 10px 0 20px;"> <el-button class="pull-right" icon="el-icon-upload" type="primary" size="mini" @click="importFile()" >批量導入</el-button> <el-button class="pull-right right-10" icon="el-icon-download" type="primary" size="mini" @click="downloadFile('檔案模板')" >模板下載</el-button> <el-button size="mini" type="primary" icon="el-icon-plus" class="pull-right" @click="addRow" >新增</el-button> <div class="pull-right"> <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 + "http://檔案模版.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
public static DataTable ReadStreamToDataTable(Stream fileStream, string sheetName = null, bool isFirstRowColumn = true) { //定義要返回的datatable對象 DataTable data = new DataTable(); //excel工作表 ISheet sheet = null; //數據開始行(排除標題行) int startRow = 0; try { //根據文件流創建excel數據結構,NPOI的工廠類WorkbookFactory會自動識別excel版本，創建出不同的excel數據結構 IWorkbook workbook = WorkbookFactory.Create(fileStream); //如果有指定工作表名稱 if (!string.IsNullOrEmpty(sheetName)) { sheet = workbook.GetSheet(sheetName); //如果沒有找到指定的sheetName對應的sheet，則嘗試獲取第一個sheet if (sheet == null) { sheet = workbook.GetSheetAt(0); } } else { //如果沒有指定的sheetName，則嘗試獲取第一個sheet sheet = workbook.GetSheetAt(0); } if (sheet != null) { IRow firstRow = sheet.GetRow(0); //一行最后一個cell的編號 即總的列數 int cellCount = firstRow.LastCellNum; //如果第一行是標題列名 if (isFirstRowColumn) { for (int i = firstRow.FirstCellNum; i < cellCount; ++i) { ICell cell = firstRow.GetCell(i); if (cell != null) { string cellValue = cell.StringCellValue; if (cellValue != null) { DataColumn column = new DataColumn(cellValue); data.Columns.Add(column); } } } startRow = sheet.FirstRowNum + 1; } else { startRow = sheet.FirstRowNum; } //最后一列的標號 int rowCount = sheet.LastRowNum; for (int i = startRow; i <= rowCount; ++i) { IRow row = sheet.GetRow(i); if (row == null || row.FirstCellNum < 0) continue; //沒有數據的行默認是null DataRow dataRow = data.NewRow(); for (int j = row.FirstCellNum; j < cellCount; ++j) { //同理，沒有數據的單元格都默認是null ICell cell = row.GetCell(j); if (cell != null) { if (cell.CellType == CellType.Numeric) { //判斷是否日期類型 if (DateUtil.IsCellDateFormatted(cell)) { dataRow[j] = row.GetCell(j).DateCellValue; } else { dataRow[j] = row.GetCell(j).ToString().Trim(); } } else { dataRow[j] = row.GetCell(j).ToString().Trim(); } } } data.Rows.Add(dataRow); } } return data; } catch (Exception ex) { throw ex; } }文件下載比較簡單 public async Task<FileStreamResult> DownloadFiles() { string dataDir = _hosting.WebRootPath;//獲得當前服務器程序的運行目錄 dataDir = Path.Combine(dataDir, "ExcelTemplate"); var path = dataDir + "http://檔案模版.xlsx"; var memoryStream = new MemoryStream(); using (var stream = new FileStream(path, FileMode.Open)) { await stream.CopyToAsync(memoryStream); } memoryStream.Seek(0, SeekOrigin.Begin); return new FileStreamResult(memoryStream, "application/octet-stream");//文件流方式，指定文件流對應的ContenType。 }文件下載比較簡單
public async Task<FileStreamResult> DownloadFiles() { string dataDir = _hosting.WebRootPath;//獲得當前服務器程序的運行目錄 dataDir = Path.Combine(dataDir, "ExcelTemplate"); var path = dataDir + "http://檔案模版.xlsx"; var memoryStream = new MemoryStream(); using (var stream = new FileStream(path, FileMode.Open)) { await stream.CopyToAsync(memoryStream); } memoryStream.Seek(0, SeekOrigin.Begin); return new FileStreamResult(memoryStream, "application/octet-stream");//文件流方式，指定文件流對應的ContenType。 }文件上傳結果通知類
public class UploadResult { public int RepeatCount { get; set; } public int SuccessCount { get; set; } public int FileRepeatCount { get; set; } public int ErrorCount { get; set; } public List<UploadErrorDto> uploadErrors { get; set; } } public class UploadErrorDto { public string RowIndex { get; set; } public string ErrorCol { get; set; } public string ErrorData { get; set; } } 通過以上處理后，我們就可以在前端實現文件的上傳了，若上傳失敗則會返回失敗結果
以上就是整個前后端關于文件上傳與下載的實現，想通過日常記錄這種方式，來幫助自己更好的掌握基礎，穩固自己的技能
總結
以上所述是小編給大家介紹的vue實現Excel文件的上傳與下載功能的兩種方式，希望對大家有所幫助，如果大家有任何疑問歡迎給我留言，小編會及時回復大家的！
新聞熱點
疑難解答
