vue、element、axios、api 实现下载 excel 文件

本篇文章记录如何结合：axios请求后台实现下载excel文件

 

 前端页面+脚本

<el-form-item>
    <el-button type="primary" icon="el-icon-search" v-on:click="getList">查询</el-button>
</el-form-item>
<el-form-item>
    <el-button type="primary" icon="el-icon-download" :loading="onDownLoading" v-on:click="DownLoad">下载</el-button>
</el-form-item>

vue实现：

var vm = new Vue({
    el: '#vm_table',
    data() {
        return {
            onDownLoading: false,   //下载中动画
        }
    },
    methods: {  

        //下载
        async DownLoad() {
            vm.onDownLoading = true;  //显示下载中动态图
            var param = vm.filter;    //参数

            //异步下载
            axios.request({
                method: 'post',
                baseURL: this.apiUrl,
                url: '/xxxx/xxxx',
                params: param,
                responseType: 'blob',   //接收类型
            }).then(function (res) {
                vm.onDownLoading = false;  //关闭下载中动态图
                if (res.status == 200) {
                    let fileName = res.headers['content-disposition'].split(';')[1].split('=')[1]; //excel文件名称
                    let blob = new Blob([res.data])
                    if (window.navigator.msSaveOrOpenBlob) {
                        navigator.msSaveBlob(blob, fileName);
                    } else {
                        //非IE下载
                        var link = document.createElement('a');
                        link.href = window.URL.createObjectURL(blob);
                        link.download = fileName;
                        link.click();
                        window.URL.revokeObjectURL(link.href);  //释放url对象
                    }
                } else {
                    vm.$message.error('下载失败,请刷新后重新尝试');
                }
            }).catch(function (res) {
                vm.onDownLoading = false;  //关闭下载中动态图
                vm.$message.error('请求异常,刷新后重新尝试');
            })
        },

    }
});

服务端实现：

//下载excel文件
[HttpPost]
public FileResult DownLoad()
{
    var loginname = Request["loginName"].AsStringWhiteSpace();
    var mobile = Request["Mobile"].AsStringWhiteSpace();
    try
    {
        //查询数据库，返回一个DataTable
        DataTable datatabla = RecordsBll.DownLoadRecords(loginname,mobile);
        if (datatabla != null)
        {
            //文件名
            var fileName = DateTime.Now.Ticks.ToString() + ".xlsx";
            if (datatabla.Rows.Count > 50000)
            {
                fileName = DateTime.Now.Ticks.ToString() + ".csv";
            }
            var getbuffer = ExcelHelper.ExportDataTableToExcel(datatabla, true);
            return File(getbuffer, "application/ms-excel", fileName);
        }
        return File(new byte[0], "application/ms-excel", DateTime.Now.Ticks.ToString() + ".xlsx");
    }
    catch (Exception)
    {
        return File(new byte[0], "application/ms-excel", DateTime.Now.Ticks.ToString() + ".xlsx");
    }
}

另外导出excel需要引用第三方插件，EPPlus.dll

/// <summary>
/// 5万条数据以内导出
/// </summary>
/// <param name="sourceTable">数据源</param>
/// <param name="header">显示标题</param>
/// <returns></returns>
public static byte[] ExportDataTableToExcel(DataTable sourceTable, bool header)
{
    if (sourceTable == null)
        return null;

    //超过5万条使用csv格式
    if (sourceTable.Rows.Count > 50000)
    {
        return ToCsv(sourceTable);
    }

    ExcelPackage excel = new ExcelPackage();
    ExcelWorksheet sheet = excel.Workbook.Worksheets.Add("sheet1");

    var ii = 0;

    if (header)
    {
        ii++;
        for (int i = 1; i <= sourceTable.Columns.Count; i++)
        {
            sheet.Cells[ii, i].Value = sourceTable.Columns[i - 1];
        }
    }
    foreach (DataRow row in sourceTable.Rows)
    {
        ii++;
        for (int i = 1; i <= sourceTable.Columns.Count; i++)
        {
            sheet.Cells[ii, i].Value = row[sourceTable.Columns[i - 1]].AsString();
        }
    }

    MemoryStream ms = new MemoryStream();
    excel.SaveAs(ms);
    var result = ms.ToArray();

    ms.Close();
    ms.Dispose();
    return result;
}

导出CSV文件

/// <summary>
/// 导出Csv格式文件
/// </summary>
/// <param name="dtData"></param>
/// <returns></returns>
public static byte[] ToCsv(DataTable dtData)
{
    System.Web.HttpContext context = System.Web.HttpContext.Current;
    StringBuilder sb = new StringBuilder();
    //列名
    int count = Convert.ToInt32(dtData.Columns.Count);
    for (int i = 0; i < count; i++)
    {
        sb.Append(dtData.Columns[i].ColumnName + ",");
    }
    sb.Append("\n");
    //行数据
    for (int i = 0; i < dtData.Rows.Count; i++)
    {
        for (int j = 0; j < count; j++)
        {
            string strName = dtData.Rows[i][j].AsString().Replace("\n", "").Replace(",", "_x002C");
            sb.Append(strName + "\t,");
        }
        sb.Append("\n");
    }
    StringWriter sw = new StringWriter(sb);
    byte[] fileContents = Encoding.Default.GetBytes(sw.ToString());
    sw.Close();
    sw.Dispose();
    return fileContents;
}

到此为止。