web端导出excel数据供用户下载的方法。

感悟随笔 专栏收录该内容
4 篇文章 0 订阅

有两种方法来实现这个功能。一种呢,是javascript直接将table导出到excel供用户下载

 tableToExcel: (function () {
            var uri = 'data:application/vnd.ms-excel;base64,'
            , template = '<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40"><head><meta charset="utf-8" /><!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet><x:Name>{worksheet}</x:Name><x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet></x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]--></head><body><table>{table}</table></body></html>'
            , base64 = function (s) { return window.btoa(unescape(encodeURIComponent(s))) }//
            , format = function (s, c) { return s.replace(/{(\w+)}/g, function (m, p) { return c[p]; }) }
            return function (table, name, filename) {
                if (!table.nodeType) table = document.getElementById(table)
                var ctx = { worksheet: name || 'Worksheet', table: table.innerHTML }

                document.getElementById("dlink").href = uri + base64(format(template, ctx));
                // document.getElementById("dlink").href = uri + ctx;
                document.getElementById("dlink").download = filename;
                document.getElementById("dlink").click();
            }
        })(),

不过上面这种方法不支持IE浏览器,标签<a>中哦那个的download属性是html5中的新属性,很遗憾不支持IE。需要对IE进行特别的操作。

 TableToExcelForIE: function (tableID) {
            this.tableBorder = -1; //边框类型,-1没有边框 可取1/2/3/4
            this.backGround = 0; //背景颜色:白色 可取调色板中的颜色编号 1/2/3/4....
            this.fontColor = 1; //字体颜色:黑色
            this.fontSize = 10; //字体大小
            this.fontStyle = "Segoe UI"; //字体类型
            this.rowHeight = 20; //行高
            this.columnWidth = -1; //列宽
            this.lineWrap = true; //是否自动换行
            this.textAlign = -4108; //内容对齐方式 默认为居中
            this.autoFit = true; //是否自适应宽度
            this.tableID = tableID;

        },
 _f.TableToExcelForIE.prototype.setTextAlign = function (textAlign) {
        this.textAlign = textAlign;
    };
    _f.TableToExcelForIE.prototype.isAutoFit = function (autoFit) {
        if (autoFit == true || autoFit == false) this.autoFit = autoFit;
    }
    _f.TableToExcelForIE.prototype.getExcelFile = function () {
        //文件转换主函数
        var jXls, myWorkbook, myWorksheet, myHTMLTableCell, myExcelCell, myExcelCell2;
        var myCellColSpan, myCellRowSpan;
        try {
            jXls = new ActiveXObject('Excel.Application');
        } catch (e) {
            alert("无法启动Excel!\n\n如果您确信您的电脑中已经安装了Excel," + "那么请调整IE的安全级别。\n\n具体操作:\n\n" + "工具 → Internet选项 → 安全 → 自定义级别 → 对没有标记为安全的ActiveX进行初始化和脚本运行 → 启用\n\n或者使用其他浏览器进行表格导出(推荐谷歌浏览器)");
            return false;
        }
        jXls.Visible = true;
        myWorkbook = jXls.Workbooks.Add();
        jXls.DisplayAlerts = false;
        jXls.DisplayAlerts = true;
        myWorksheet = myWorkbook.ActiveSheet;
        var readRow = 0, readCol = 0; var totalRow = 0, totalCol = 0;
        var tabNum = 0;
        //设置行高、列宽
        if (this.columnWidth != -1)
            myWorksheet.Columns.ColumnWidth = this.columnWidth;
        else
            myWorksheet.Columns.ColumnWidth = 7;
        if (this.rowHeight != -1)
            myWorksheet.Rows.RowHeight = this.rowHeight;
        //搜索需要转换的Table对象,获取对应行、列数
        var obj = document.all.tags("table");
        for (var x = 0; x < obj.length; x++) {
            if (obj[x].id == this.tableID) {
                tabNum = x;
                totalRow = obj[x].rows.length;
                for (i = 0; i < obj[x].rows[0].cells.length; i++) {
                    myHTMLTableCell = obj[x].rows(0).cells(i);
                    myCellColSpan = myHTMLTableCell.colSpan;
                    totalCol = totalCol + myCellColSpan;
                }
            }
        }
        //开始构件模拟表格
        var excelTable = new Array();
        for (var i = 0; i <= totalRow; i++) {
            excelTable[i] = new Array();
            for (var t = 0; t <= totalCol; t++) {
                excelTable[i][t] = false;
            }
        }
        //开始转换表格 
        for (var z = 0; z < obj[tabNum].rows.length; z++) {
            readRow = z + 1;
            readCol = 0;
            for (var c = 0; c < obj[tabNum].rows(z).cells.length; c++) {
                myHTMLTableCell = obj[tabNum].rows(z).cells(c);
                myCellColSpan = myHTMLTableCell.colSpan;
                myCellRowSpan = myHTMLTableCell.rowSpan;
                for (var y = 1; y <= totalCol; y++) {
                    if (excelTable[readRow][y] == false) {
                        readCol = y;
                        break;
                    }
                }
                if (myCellColSpan * myCellRowSpan > 1) {
                    myExcelCell = myWorksheet.Cells(readRow, readCol);
                    myExcelCell2 = myWorksheet.Cells(readRow + myCellRowSpan - 1, readCol + myCellColSpan - 1);
                    myWorksheet.Range(myExcelCell, myExcelCell2).Merge();
                    myExcelCell.HorizontalAlignment = this.textAlign;
                    try {
                        myExcelCell.Font.Size = this.fontSize;
                        myExcelCell.Font.Name = this.fontStyle;
                        myExcelCell.wrapText = this.lineWrap;
                        myExcelCell.Interior.ColorIndex = this.backGround;
                        myExcelCell.Font.ColorIndex = this.fontColor;
                    }
                    catch (e) {
                        alert("Please check whether Excel product key is valid.");
                        return false;
                    }
                    if (this.tableBorder != -1) {
                        myWorksheet.Range(myExcelCell, myExcelCell2).Borders(1).Weight = this.tableBorder;
                        myWorksheet.Range(myExcelCell, myExcelCell2).Borders(2).Weight = this.tableBorder;
                        myWorksheet.Range(myExcelCell, myExcelCell2).Borders(3).Weight = this.tableBorder;
                        myWorksheet.Range(myExcelCell, myExcelCell2).Borders(4).Weight = this.tableBorder;
                    }
                    myExcelCell.Value = myHTMLTableCell.innerText;
                    for (var row = readRow; row <= myCellRowSpan + readRow - 1; row++) {
                        for (var col = readCol; col <= myCellColSpan + readCol - 1; col++) {
                            excelTable[row][col] = true;
                        }
                    }
                    readCol = readCol + myCellColSpan;
                } else {
                    myExcelCell = myWorksheet.Cells(readRow, readCol);
                    myExcelCell.Value = myHTMLTableCell.innerText;
                    myExcelCell.HorizontalAlignment = this.textAlign;
                    myExcelCell.Font.Size = this.fontSize;
                    myExcelCell.Font.Name = this.fontStyle;
                    myExcelCell.wrapText = this.lineWrap;
                    myExcelCell.Interior.ColorIndex = this.backGround;
                    myExcelCell.Font.ColorIndex = this.fontColor;
                    excelTable[readRow][readCol] = true;
                    readCol = readCol + 1;
                }
            }
        }
        if (this.autoFit == true)
            myWorksheet.Columns.AutoFit;
        jXls.UserControl = true;
        jXls = null;
        myWorkbook = null;
        myWorksheet = null;
    };
IE的话因为需要调用activexobject,所以需要进行如下的浏览器安全操作。
调整IE的安全级别。\n\n具体操作:\n\n" + "工具 → Internet选项 → 安全 → 自定义级别 → 对没有标记为安全的ActiveX进行初始化和脚本运行 → 启用

那么问题来了,如果想通过发送AJAX请求让后台生成excel供用户下载呢?

我先来捋一捋这个过程:

1.前台客户端发送ajax请求,调用导出excel的方法。

2.后台接收到请求,开始进行导出操作。生成的excel文件存在server端

3.直接传excel文件到前台我反正是不会。那么,就是调用httpcontext.response这个东西将文件流传到前台楼。

4.读取server暂存的excel文件将其读取为文件流的形式,然后把server端的excel文件删了,毕竟是暂时用一下。关于access is denied 的问题,请加模拟。

5.客户端接收到文件流之后因为指定类型了,而且最为关键的是指定了你传过来的东西是一个附件,所以马上按照指定的contentype进行下载。具体表现就是弹出一个下载确认框或者直接就开始下载了。

以下方法适用于asp.net进行下载。

 HttpContext.Current.Response.Clear();
            HttpContext.Current.Response.Charset = "GB2312";
            HttpContext.Current.Response.ContentEncoding = Encoding.UTF8;//注意编码
            HttpContext.Current.Response.AppendHeader("Content-Disposition",
                "attachment;filename=" + HttpUtility.UrlEncode(FileName, Encoding.UTF8).ToString());
            HttpContext.Current.Response.ContentType = <span style="font-family: 'Courier New'; line-height: 19.2000007629395px; white-space: pre-wrap; color: rgb(128, 0, 0);">"</span><span style="font-family: 'Courier New'; line-height: 19.2000007629395px; white-space: pre-wrap; color: rgb(128, 0, 0);">application/ms-excel"</span>;//image/JPEG;text/HTML;image/GIF;vnd.ms-excel/msword 

	   HttpContext.Current.Response.Write("内容");
            HttpContext.Current.Response.End();


小弟才疏学浅,自己用的是一个纯HTML页面,没有asp.net那种接收表单的.aspx界面,我在后台生成的excel传到前台时只有一堆乱码并不能生成excel文件,调试许久遂作罢,我最后用的是第一种,在javascript里直接把页面的一个table转化为excel供用户的报表下载。格式什么的丑了一点,但是打完收工!


上述代码都是我直接从我的代码里拉出来的,可能有些变量是我自定义的,仅供参考,不保证能直接调用!


  • 1
    点赞
  • 0
    评论
  • 0
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

©️2021 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值