js 导出Excel

最近从Silverlight这边转到javascript过来，现在要导出一个导出excel的功能。上级领导指示当页显示多少数据，就导出多少数据，没有必要从后台在去数据。以前也没有接触过这方面的，在网上一整狂查资料，最终决定采用excel2003xml+Flash插件实现改功能。

获取excel2003xml格式

在桌面新建一个excel文件，另存为2003xml格式，用Vs2012打开该文件，就能清晰的明白数据保存方式。

Javascript构造xml数据

了解数据结构后，我们已经知道了excel中的数据存储在节点Worksheet下的Table中，ExpandedColumnCount属性说明该Execl文件中有多少列，ExpandedRowCount说明有多少行数据。Row节点下ss:Index说明改行数据是从第几行开始。Cell节点属性ss:Index说明数据是在第几列，ss:MergeDown是从本单元格像下合并多少单元格，ss:MergeAcross从本单元格开始向左合并多少个单元格，数据格式如下：

表格中的数据个列都会有列头信息，我采用列头和数据分离，都采用一个二维数组，dataOpts.HeadInfo格式：

[[               { field: 'F_UserID', title: '公告ID', width: 100,  hidden: true, rowspan: 3 },               { field: 'F_RealName', title: '姓名', width: 100, rowspan: 3 },               { field: 'F_LoginName', title: '登录名', width: 100, rowspan: 3 },               { field: 'F_PassWord', title: '密码', width: 100, rowspan: 3 },               { title: '多表头', colspan: 5 }            ], [               { field: 'F_UserNick', title: '昵称', width: 100,rowspan:2},               { field: 'F_IdNumber', title: '身份证号', width: 100,rowspan:2 },               { title: '多表3', colspan: 3}            ], [               { field: 'F_Tel', title: '电话', width: 100},               { field: 'F_BirthDate', title: '生日', width: 100 },               { field: 'F_EMail', title: '邮箱', width: 100 },            ]

　　filed用于判断是否是合并列，其中rowspan对应MergeDown，clospan对应MergeAcross,构建表头代码如下：

  for (var i = 0; i < dataOpts.HeadInfo.length; i++) {            var rowindex = dataOpts.RowStart + i;            headerXml += '<Row ss:Index="' + rowindex + '" ss:AutoFitHeight="0">';            for (var cell = 0; cell < dataOpts.HeadInfo[i].length; cell++) {                var curcell = dataOpts.HeadInfo[i][cell];                if (curcell.hidden)                    continue;                var cellindex = dataOpts.ColumStart + cloumIndex;                var MergeDown = curcell.rowspan ? curcell.rowspan - 1 : 0;                var MergeAcross = curcell.colspan ? curcell.colspan - 1 : 0;                if (curcell.field) {                    cloumIndex = cloumIndex + 1;                    ExpandedColumnCount = ExpandedColumnCount + 1;                }                headerXml += '<Cell ss:StyleID="TableHeadStyle" ss:Index="' + cellindex +                    (MergeDown === 0 ? '' : '" ss:MergeDown="' + MergeDown) +                    (MergeAcross === 0 ? '' : '" ss:MergeAcross="' + MergeAcross) +                   '"><Data ss:Type="String">' + curcell.title + '</Data></Cell>';            }            headerXml += '</Row>';            ExpandedRowCount = ExpandedRowCount + 1;        }

　　同理，构建数据就显得简单些了，不会存在合并问题,数据行是从表头后开始绘制的，在Row节点中可不用设置ss:index的值，主要代码如下：

  //创建数据        for (var i = 0; i < dataOpts.RowInfo.length; i++) {            rowxml += '<Row ss:AutoFitHeight="0">';            for (var j = 0; j < dataOpts.RowInfo[i].length; j++) {                var value = dataOpts.RowInfo[i][j];                rowxml += '<Cell ss:StyleID="TableHeadStyle" ' +                    (j === 0 ? 'ss:Index="' + dataOpts.ColumStart + '"' : ' ') +                    ' ><Data ss:Type="String">' + value + '</Data></Cell>'            }            rowxml += '</Row> ';            ExpandedRowCount = ExpandedRowCount + 1;        }

　 改功能主要是针对easyui中datagrid开发，单生成在处理行数据时需要特别处理转换成二维数组，调用方式

onLoadSuccess: function (data) {                    setTimeout(function () {                        ZeroClipboard_TableTools.setMoviePath('@Url.Content("~/Scripts/media/copy_csv_xls_pdf.swf")');                        var flash = new ZeroClipboard_TableTools.Client("exportExcel");                        flash.setHandCursor(true);                        data = [];                        var dataarray = grid.datagrid("getRows");                        for (var i = 0; i < dataarray.length; i++) {                            data[i] = [];                            cols = grid.datagrid("getColumnFields");                            var mins = 0;                            for (var j = 0; j < cols.length; j++) {                                var colname = cols[j];                                var filed = grid.datagrid("getColumnOption", colname);                                if (filed.hidden) {                                    mins += 1;                                    continue                                }                                data[i][j - mins] = dataarray[i][colname];                            }                        }                        flash.setText(JSXmlExcel.BulidXml({ HeadInfo: grid.datagrid("options").columns, RowInfo: data, RowStart: 2, ColumStart: 2, SheetName: 'Test' }));                        flash.setAction('save');                        flash.setCharSet('UTF8');                        flash.setFileName("excel.xls");                    }, 100);                }            });

ZeroClipboard.js

  1 // Simple Set Clipboard System  2 // Author: Joseph Huckaby  3   4 var ZeroClipboard_TableTools = {  5   6     version: "1.0.4-TableTools2",  7     clients: {}, // registered upload clients on page, indexed by id  8     moviePath: '', // URL to movie  9     nextId: 1, // ID of next movie 10  11     $: function (thingy) { 12         // simple DOM lookup utility function 13         if (typeof (thingy) == 'string') thingy = document.getElementById(thingy); 14         if (!thingy.addClass) { 15             // extend element with a few useful methods 16             thingy.hide = function () { this.style.display = 'none'; }; 17             thingy.show = function () { this.style.display = ''; }; 18             thingy.addClass = function (name) { this.removeClass(name); this.className += ' ' + name; }; 19             thingy.removeClass = function (name) { 20                 this.className = this.className.replace(new RegExp("//s*" + name + "//s*"), " ").replace(/^/s+/, '').replace(//s+$/, ''); 21             }; 22             thingy.hasClass = function (name) { 23                 return !!this.className.match(new RegExp("//s*" + name + "//s*")); 24             } 25         } 26         return thingy; 27     }, 28  29     setMoviePath: function (path) { 30         // set path to ZeroClipboard.swf 31         this.moviePath = path; 32     }, 33  34     dispatch: function (id, eventName, args) { 35         // receive event from flash movie, send to client         36         var client = this.clients[id]; 37         if (client) { 38             client.receiveEvent(eventName, args); 39         } 40     }, 41  42     register: function (id, client) { 43         // register new client to receive events 44         this.clients[id] = client; 45     }, 46  47     getDOMObjectPosition: function (obj) { 48         // get absolute coordinates for dom element 49         debugger; 50         var info = { 51             left: 0, 52             top: 0, 53             width: obj.width ? obj.width : obj.offsetWidth, 54             height: obj.height ? obj.height : obj.offsetHeight 55         }; 56  57         if (obj.style.width != "") 58             info.width = obj.style.width.replace("px", ""); 59  60         if (obj.style.height != "") 61             info.height = obj.style.height.replace("px", ""); 62  63         while (obj) { 64             info.left += obj.offsetLeft; 65             info.to