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         //查询数据库，返回一个DataTable10         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

到此为止。