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 = 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 });
View Code

服务端实现:

 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 }
View Code

另外导出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 = 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 = 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 }
View Code

导出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 }
View Code

到此为止。

posted @ 2020-08-28 16:53  每天努力进步一点点  阅读(92)  评论(0编辑  收藏