vue、element、axios、api 實作下載 excel 檔案

本篇文章記錄如何結合：axios請求后臺實作下載excel檔案

 前端頁面+腳本

1 <el-form-item>
2     <el-button type="primary" icon="el-icon-search" v-on:click="getList">查詢</el-button>
3 </el-form-item>
4 <el-form-item>
5     <el-button type="primary" icon="el-icon-download" :loading="onDownLoading" v-on:click="DownLoad">下載</el-button>
6 </el-form-item>

vue實作：

 1 var vm = new Vue({
 2     el: '#vm_table',
 3     data() {
 4         return {
 5             onDownLoading: false,   //下載中影片
 6         }
 7     },
 8     methods: {  
 9 
10         //下載
11         async DownLoad() {
12             vm.onDownLoading = true;  //顯示下載中動態圖
13             var param = vm.filter;    //引數
14 
15             //異步下載
16             axios.request({
17                 method: 'post',
18                 baseURL: this.apiUrl,
19                 url: '/xxxx/xxxx',
20                 params: param,
21                 responseType: 'blob',   //接收型別
22             }).then(function (res) {
23                 vm.onDownLoading = false;  //關閉下載中動態圖
24                 if (res.status == 200) {
25                     let fileName = res.headers['content-disposition'].split(';')[1].split('=')[1]; //excel檔案名稱
26                     let blob = new Blob([res.data])
27                     if (window.navigator.msSaveOrOpenBlob) {
28                         navigator.msSaveBlob(blob, fileName);
29                     } else {
30                         //非IE下載
31                         var link = document.createElement('a');
32                         link.href =https://www.cnblogs.com/peterzhang123/p/ window.URL.createObjectURL(blob);
33                         link.download = fileName;
34                         link.click();
35                         window.URL.revokeObjectURL(link.href);  //釋放url物件
36                     }
37                 } else {
38                     vm.$message.error('下載失敗,請重繪后重新嘗試');
39                 }
40             }).catch(function (res) {
41                 vm.onDownLoading = false;  //關閉下載中動態圖
42                 vm.$message.error('請求例外,重繪后重新嘗試');
43             })
44         },
45 
46     }
47 });

服務端實作：

 1 //下載excel檔案
 2 [HttpPost]
 3 public FileResult DownLoad()
 4 {
 5     var loginname = Request["loginName"].AsStringWhiteSpace();
 6     var mobile = Request["Mobile"].AsStringWhiteSpace();
 7     try
 8     {
 9         //查詢資料庫，回傳一個DataTable
10         DataTable datatabla = RecordsBll.DownLoadRecords(loginname,mobile);
11         if (datatabla != null)
12         {
13             //檔案名
14             var fileName = DateTime.Now.Ticks.ToString() + ".xlsx";
15             if (datatabla.Rows.Count > 50000)
16             {
17                 fileName = DateTime.Now.Ticks.ToString() + ".csv";
18             }
19             var getbuffer = ExcelHelper.ExportDataTableToExcel(datatabla, true);
20             return File(getbuffer, "application/ms-excel", fileName);
21         }
22         return File(new byte[0], "application/ms-excel", DateTime.Now.Ticks.ToString() + ".xlsx");
23     }
24     catch (Exception)
25     {
26         return File(new byte[0], "application/ms-excel", DateTime.Now.Ticks.ToString() + ".xlsx");
27     }
28 }

另外匯出excel需要參考第三方插件，EPPlus.dll

 1 /// <summary>
 2 /// 5萬條資料以內匯出
 3 /// </summary>
 4 /// <param name="sourceTable">資料源</param>
 5 /// <param name="header">顯示標題</param>
 6 /// <returns></returns>
 7 public static byte[] ExportDataTableToExcel(DataTable sourceTable, bool header)
 8 {
 9     if (sourceTable == null)
10         return null;
11 
12     //超過5萬條使用csv格式
13     if (sourceTable.Rows.Count > 50000)
14     {
15         return ToCsv(sourceTable);
16     }
17 
18     ExcelPackage excel = new ExcelPackage();
19     ExcelWorksheet sheet = excel.Workbook.Worksheets.Add("sheet1");
20 
21     var ii = 0;
22 
23     if (header)
24     {
25         ii++;
26         for (int i = 1; i <= sourceTable.Columns.Count; i++)
27         {
28             sheet.Cells[ii, i].Value = https://www.cnblogs.com/peterzhang123/p/sourceTable.Columns[i - 1];
29         }
30     }
31     foreach (DataRow row in sourceTable.Rows)
32     {
33         ii++;
34         for (int i = 1; i <= sourceTable.Columns.Count; i++)
35         {
36             sheet.Cells[ii, i].Value = https://www.cnblogs.com/peterzhang123/p/row[sourceTable.Columns[i - 1]].AsString();
37         }
38     }
39 
40     MemoryStream ms = new MemoryStream();
41     excel.SaveAs(ms);
42     var result = ms.ToArray();
43 
44     ms.Close();
45     ms.Dispose();
46     return result;
47 }

匯出CSV檔案

 1 /// <summary>
 2 /// 匯出Csv格式檔案
 3 /// </summary>
 4 /// <param name="dtData"></param>
 5 /// <returns></returns>
 6 public static byte[] ToCsv(DataTable dtData)
 7 {
 8     System.Web.HttpContext context = System.Web.HttpContext.Current;
 9     StringBuilder sb = new StringBuilder();
10     //列名
11     int count = Convert.ToInt32(dtData.Columns.Count);
12     for (int i = 0; i < count; i++)
13     {
14         sb.Append(dtData.Columns[i].ColumnName + ",");
15     }
16     sb.Append("\n");
17     //行資料
18     for (int i = 0; i < dtData.Rows.Count; i++)
19     {
20         for (int j = 0; j < count; j++)
21         {
22             string strName = dtData.Rows[i][j].AsString().Replace("\n", "").Replace(",", "_x002C");
23             sb.Append(strName + "\t,");
24         }
25         sb.Append("\n");
26     }
27     StringWriter sw = new StringWriter(sb);
28     byte[] fileContents = Encoding.Default.GetBytes(sw.ToString());
29     sw.Close();
30     sw.Dispose();
31     return fileContents;
32 }

到此為止，

標籤：ASP.NET

上一篇：.NET異步和多執行緒系列（三）- Task和Parallel

下一篇：Asp.Net WebAPI的簡介及創建