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

 // Simple Set Clipboard System
 // Author: Joseph Huckaby

 var ZeroClipboard_TableTools = {

     version: "1.0.4-TableTools2",
     clients: {}, // registered upload clients on page, indexed by id
     moviePath: '', // URL to movie
     nextId: 1, // ID of next movie

     $: function (thingy) {
         // simple DOM lookup utility function
         if (typeof (thingy) == 'string') thingy = document.getElementById(thingy);
         if (!thingy.addClass) {
             // extend element with a few useful methods
             thingy.hide = function () { this.style.display = 'none'; };
             thingy.show = function () { this.style.display = ''; };
             thingy.addClass = function (name) { this.removeClass(name); this.className += ' ' + name; };
             thingy.removeClass = function (name) {
                 this.className = this.className.replace(new RegExp("\\s*" + name + "\\s*"), " ").replace(/^\s+/, '').replace(/\s+$/, '');
             };
             thingy.hasClass = function (name) {
                 return !!this.className.match(new RegExp("\\s*" + name + "\\s*"));
             }
         }
         return thingy;
     },

     setMoviePath: function (path) {
         // set path to ZeroClipboard.swf
         this.moviePath = path;
     },

     dispatch: function (id, eventName, args) {
         // receive event from flash movie, send to client
         var client = this.clients[id];
         if (client) {
             client.receiveEvent(eventName, args);
         }
     },

     register: function (id, client) {
         // register new client to receive events
         this.clients[id] = client;
     },

     getDOMObjectPosition: function (obj) {
         // get absolute coordinates for dom element
         debugger;
         var info = {
             left: 0,
             top: 0,
             width: obj.width ? obj.width : obj.offsetWidth,
             height: obj.height ? obj.height : obj.offsetHeight
         };

         if (obj.style.width != "")
             info.width = obj.style.width.replace("px", "");

         if (obj.style.height != "")
             info.height = obj.style.height.replace("px", "");

         while (obj) {
             info.left += obj.offsetLeft;
             info.top += obj.offsetTop;
             obj = obj.offsetParent;
         }

         return info;
     },

     Client: function (elem) {
         // constructor for new simple upload client
         this.handlers = {};

         // unique ID
         this.id = ZeroClipboard_TableTools.nextId++;
         this.movieId = 'ZeroClipboard_TableToolsMovie_' + this.id;

         // register client with singleton to receive flash events
         ZeroClipboard_TableTools.register(this.id, this);

         // create movie
         if (elem) this.glue(elem);
     }
 };

 ZeroClipboard_TableTools.Client.prototype = {

     id: 0, // unique ID for us
     ready: false, // whether movie is ready to receive events or not
     movie: null, // reference to movie object
     clipText: '', // text to copy to clipboard
     fileName: '', // default file save name
     action: 'copy', // action to perform
     handCursorEnabled: true, // whether to show hand cursor, or default pointer cursor
     cssEffects: true, // enable CSS mouse effects on dom container
     handlers: null, // user event handlers
     sized: false,

     glue: function (elem, title) {
         // glue to DOM element
         // elem can be ID or actual DOM element object
         this.domElement = ZeroClipboard_TableTools.$(elem);

         // float just above object, or zIndex 99 if dom element isn't set
         var zIndex = 99;
         if (this.domElement.style.zIndex) {
             zIndex = parseInt(this.domElement.style.zIndex) + 1;
         }
         debugger;
         // find X/Y position of domElement
         var box = ZeroClipboard_TableTools.getDOMObjectPosition(this.domElement);

         // create floating DIV above element
         this.div = document.createElement('div');
         var style = this.div.style;
         style.position = 'absolute';
         style.left ='0px';
         style.top = '0px';
         style.width = (box.width) + 'px';
         style.height = (box.height) + 'px';
         style.zIndex = zIndex;

         if (typeof title != "undefined" && title != "") {
             this.div.title = title;
         }
         if (box.width != 0 && box.height != 0) {
             this.sized = true;
         }

         style.backgroundColor = '#f00'; // debug
         if (this.domElement) {
             this.domElement.appendChild(this.div);
             this.div.innerHTML = this.getHTML(box.width, box.height);
         }
     },

     positionElement: function () {
         var box = ZeroClipboard_TableTools.getDOMObjectPosition(this.domElement);
         var style = this.div.style;

         style.position = 'absolute';
         //style.left = (this.domElement.offsetLeft)+'px';
         //style.top = this.domElement.offsetTop+'px';
         style.width = box.width + 'px';
         style.height = box.height + 'px';

         if (box.width != 0 && box.height != 0) {
             this.sized = true;
         } else {
             return;
         }

         var flash = this.div.childNodes[0];
         flash.width = box.width;
         flash.height = box.height;
     },

     getHTML: function (width, height) {
         // return HTML for movie
         var html = '';
         var flashvars = 'id=' + this.id +
             '&width=' + width +
             '&height=' + height;

         if (navigator.userAgent.match(/MSIE/)) {
             // IE gets an OBJECT tag
             var protocol = location.href.match(/^https/i) ? 'https://' : 'http://';
             html += '<object classid="clsid:D27CDB6E-AE6D-11cf-96B8-444553540000" codebase="' + protocol + 'download.macromedia.com/pub/shockwave/cabs/flash/swflash.cab#version=10,0,0,0" width="' + width + '" height="' + height + '" id="' + this.movieId + '" align="middle"><param name="allowScriptAccess" value="always" /><param name="allowFullScreen" value="false" /><param name="movie" value="' + ZeroClipboard_TableTools.moviePath + '" /><param name="loop" value="false" /><param name="menu" value="false" /><param name="quality" value="best" /><param name="bgcolor" value="#ffffff" /><param name="flashvars" value="' + flashvars + '"/><param name="wmode" value="transparent"/></object>';
         }
         else {
             // all other browsers get an EMBED tag
             html += '<embed id="' + this.movieId + '" src="' + ZeroClipboard_TableTools.moviePath + '" loop="false" menu="false" quality="best" bgcolor="#ffffff" width="' + width + '" height="' + height + '" name="' + this.movieId + '" align="middle" allowScriptAccess="always" allowFullScreen="false" type="application/x-shockwave-flash" pluginspage="http://www.macromedia.com/go/getflashplayer" flashvars="' + flashvars + '" wmode="transparent" />';
         }
         return html;
     },

     hide: function () {
         // temporarily hide floater offscreen
         if (this.div) {
             this.div.style.left = '-2000px';
         }
     },

     show: function () {
         // show ourselves after a call to hide()
         this.reposition();
     },

     destroy: function () {
         // destroy control and floater
         if (this.domElement && this.div) {
             this.hide();
             this.div.innerHTML = '';

             var body = document.getElementsByTagName('body')[0];
             try { body.removeChild(this.div); } catch (e) {; }

             this.domElement = null;
             this.div = null;
         }
     },

     reposition: function (elem) {
         // reposition our floating div, optionally to new container
         // warning: container CANNOT change size, only position
         if (elem) {
             this.domElement = ZeroClipboard_TableTools.$(elem);
             if (!this.domElement) this.hide();
         }

         if (this.domElement && this.div) {
             var box = ZeroClipboard_TableTools.getDOMObjectPosition(this.domElement);
             var style = this.div.style;
             style.left = '' + box.left + 'px';
             style.top = '' + box.top + 'px';
         }
     },

     clearText: function () {
         // clear the text to be copy / saved
         this.clipText = '';
         if (this.ready) this.movie.clearText();
     },

     appendText: function (newText) {
         // append text to that which is to be copied / saved
         this.clipText += newText;
         if (this.ready) { this.movie.appendText(newText); }
     },

     setText: function (newText) {
         // set text to be copied to be copied / saved
         this.clipText = newText;
         if (this.ready) { this.movie.setText(newText); }
     },

     setCharSet: function (charSet) {
         // set the character set (UTF16LE or UTF8)
         this.charSet = charSet;
         if (this.ready) { this.movie.setCharSet(charSet); }
     },

     setBomInc: function (bomInc) {
         // set if the BOM should be included or not
         this.incBom = bomInc;
         if (this.ready) { this.movie.setBomInc(bomInc); }
     },

     setFileName: function (newText) {
         // set the file name
         this.fileName = newText;
         if (this.ready) this.movie.setFileName(newText);
     },

     setAction: function (newText) {
         // set action (save or copy)
         this.action = newText;
         if (this.ready) this.movie.setAction(newText);
     },

     addEventListener: function (eventName, func) {
         // add user event listener for event
         // event types: load, queueStart, fileStart, fileComplete, queueComplete, progress, error, cancel
         eventName = eventName.toString().toLowerCase().replace(/^on/, '');
         if (!this.handlers[eventName]) this.handlers[eventName] = [];
         this.handlers[eventName].push(func);
     },

     setHandCursor: function (enabled) {
         // enable hand cursor (true), or default arrow cursor (false)
         this.handCursorEnabled = enabled;
         if (this.ready) this.movie.setHandCursor(enabled);
     },

     setCSSEffects: function (enabled) {
         // enable or disable CSS effects on DOM container
         this.cssEffects = !!enabled;
     },

     receiveEvent: function (eventName, args) {
         // receive event from flash
         eventName = eventName.toString().toLowerCase().replace(/^on/, '');

         // special behavior for certain events
         switch (eventName) {
             case 'load':
                 // movie claims it is ready, but in IE this isn't always the case...
                 // bug fix: Cannot extend EMBED DOM elements in Firefox, must use traditional function
                 this.movie = document.getElementById(this.movieId);
                 if (!this.movie) {
                     var self = this;
                     setTimeout(function () { self.receiveEvent('load', null); }, 1);
                     return;
                 }

                 // firefox on pc needs a "kick" in order to set these in certain cases
                 if (!this.ready && navigator.userAgent.match(/Firefox/) && navigator.userAgent.match(/Windows/)) {
                     var self = this;
                     setTimeout(function () { self.receiveEvent('load', null); }, 100);
                     this.ready = true;
                     return;
                 }

                 this.ready = true;
                 this.movie.clearText();
                 this.movie.appendText(this.clipText);
                 this.movie.setFileName(this.fileName);
                 this.movie.setAction(this.action);
                 this.movie.setCharSet(this.charSet);
                 this.movie.setBomInc(this.incBom);
                 this.movie.setHandCursor(this.handCursorEnabled);
                 break;

             case 'mouseover':
                 if (this.domElement && this.cssEffects) {
                     //this.domElement.addClass('hover');
                     if (this.recoverActive) this.domElement.addClass('active');
                 }
                 break;

             case 'mouseout':
                 if (this.domElement && this.cssEffects) {
                     this.recoverActive = false;
                     if (this.domElement.hasClass('active')) {
                         this.domElement.removeClass('active');
                         this.recoverActive = true;
                     }
                     //this.domElement.removeClass('hover');
                 }
                 break;

             case 'mousedown':
                 if (this.domElement && this.cssEffects) {
                     this.domElement.addClass('active');
                 }
                 break;

             case 'mouseup':
                 if (this.domElement && this.cssEffects) {
                     this.domElement.removeClass('active');
                     this.recoverActive = false;
                 }
                 break;
         } // switch eventName

         if (this.handlers[eventName]) {
             for (var idx = 0, len = this.handlers[eventName].length; idx < len; idx++) {
                 var func = this.handlers[eventName][idx];

                 if (typeof (func) == 'function') {
                     // actual function reference
                     func(this, args);
                 }
                 else if ((typeof (func) == 'object') && (func.length == 2)) {
                     // PHP style object + method, i.e. [myObject, 'myMethod']
                     func[0][func[1]](this, args);
                 }
                 else if (typeof (func) == 'string') {
                     // name of function
                     window[func](this, args);
                 }
             } // foreach event handler defined
         } // user defined handler for event
     }

 };

ZeroClipboard

　JSXmlExcel.js

 /***根据传入数据生成
 [
                { field: 'F_UserID', title: '公告ID', width: 100, sortable: true, hidden: true, rowspan: 3 },
                { field: 'F_RealName', title: '姓名', width: 100, sortable: true, rowspan: 3 },
                { field: 'F_LoginName', title: '登录名', width: 100, sortable: true, rowspan: 3 },
                { field: 'F_Password', title: '密码', width: 100, sortable: true, rowspan: 3 },
                { title: '多表头', colspan: 5 }
             ], [
                { field: 'F_UserNick', title: '昵称', width: 100, sortable: true ,rowspan:2},
                { field: 'F_IdNumber', title: '身份证号', width: 100, sortable: true,rowspan:2 },
                { title: '多表3', colspan: 3}
             ], [
                { field: 'F_Tel', title: '电话', width: 100, sortable: true },
                { field: 'F_BirthDate', title: '生日', width: 100, sortable: true },
                { field: 'F_EMail', title: '邮箱', width: 100, sortable: true },
             ]
 @param {Array} dataOpts.HeadInfo
 @param {Array} dataOpts.RowInfo
 @param {object} dataOpts.EwbInfo
 @param {int} dataOpts.RowStart
 @param {int} dataOpts.ColumStart
 @param {String} dataOpts.SheetName
 ***/
 var JSXmlExcel = {
     BulidXml: function (dataOpts) {
         if (!dataOpts.SheetName)
             dataOpts.SheetName = 'Sheet1';
         var headerXml = "";
         var columnInfo = "";
         var cloumIndex = ;
         var rowxml = "";
         var ExpandedColumnCount = dataOpts.ColumStart ? dataOpts.ColumStart -  : ;
         var ExpandedRowCount = dataOpts.RowStart ? dataOpts.RowStart -  : ;
         for (var i = ; i < dataOpts.HeadInfo.length; i++) {
             var rowindex = dataOpts.RowStart + i;
             headerXml += '<Row ss:Index="' + rowindex + '" ss:AutoFitHeight="0">';
             for (var cell = ; 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 -  : ;
                 var MergeAcross = curcell.colspan ? curcell.colspan -  : ;
                 if (curcell.field) {
                     cloumIndex = cloumIndex + ;
                     ExpandedColumnCount = ExpandedColumnCount + ;
                 }
                 headerXml += '<Cell ss:StyleID="TableHeadStyle" ss:Index="' + cellindex +
                     (MergeDown ===  ? '' : '" ss:MergeDown="' + MergeDown) +
                     (MergeAcross ===  ? '' : '" ss:MergeAcross="' + MergeAcross) +
                    '"><Data ss:Type="String">' + curcell.title + '</Data></Cell>';
             }
             headerXml += '</Row>';
             ExpandedRowCount = ExpandedRowCount + ;
         }
         headerXml = columnInfo + headerXml;
         //创建数据
         for (var i = ; i < dataOpts.RowInfo.length; i++) {
             rowxml += '<Row ss:AutoFitHeight="0">';
             for (var j = ; j < dataOpts.RowInfo[i].length; j++) {
                 var value = dataOpts.RowInfo[i][j];
                 rowxml += '<Cell ss:StyleID="TableHeadStyle" ' +
                     (j ===  ? 'ss:Index="' + dataOpts.ColumStart + '"' : ' ') +
                     ' ><Data ss:Type="String">' + value + '</Data></Cell>'
             }
             rowxml += '</Row> ';
             ExpandedRowCount = ExpandedRowCount + ;
         }
         //创建XMl尾部信息
         var TableHeadStyle = '<Style ss:ID="TableHeadStyle"> ' +
                            '<Alignment ss:Horizontal="Center" ss:Vertical="Center"/> ' +
                            '<Borders> ' +
                             '<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/> ' +
                            '<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/> ' +
                             '<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/> ' +
                             '<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/> ' +
                            '</Borders> ' +
                           '</Style> ';
         var Styles = TableHeadStyle;
         var WorkSheet = '<Worksheet ss:Name="' + dataOpts.SheetName + '">' +
       '<Table ss:ExpandedColumnCount="' + ExpandedColumnCount +
       '" ss:ExpandedRowCount="' + ExpandedRowCount +
       '" x:FullColumns="1" x:FullRows="1" ss:DefaultColumnWidth="100" ss:DefaultRowHeight="16">' + headerXml + rowxml +
         '</Table>';
         var xmlInfo = '<?xml version="1.0" encoding="utf-8"?>  ' +
                            '<?mso-application progid="Excel.Sheet"?>  ' +
                            '<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"  ' +
                            'xmlns:o="urn:schemas-microsoft-com:office:office"  ' +
                            'xmlns:x="urn:schemas-microsoft-com:office:excel"  ' +
                            'xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"  ' +
                            'xmlns:html="http://www.w3.org/TR/REC-html40">  ' +
                            '<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">  ' +
                            '</DocumentProperties>  ' +
                            '<OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">  ' +
                            '<RemovePersonalInformation/>  ' +
                            '</OfficeDocumentSettings>  ' +
                            '<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">  ' +
                            '</ExcelWorkbook><Styles>  ' + Styles + '</Styles>  ' +
                             WorkSheet + ' </Worksheet></Workbook>';
         return xmlInfo;
     }
 };

JSXmlExcel.js

有喜欢聊技术朋友也欢迎入群，若二维码失效可加我微信回复**前端**