【翻译】将Ext JS Grid转换为Excel表格

原文：Converting an Ext 5 Grid to Excel Spreadsheet

稍微迟来的礼物——Ext JS Grid转为Excel代码，现在支持Ext JS 5！

功能包括：
- 支持分组
- 数字的处理 VS 字符串数据类型
- 对于不支持客户端下载的浏览器会提交回服务器

Enjoy！

/*    Excel.js - convert an ExtJS 5 grid into an Excel spreadsheet using nothing but    javascript and good intentions.    By: Steve Drucker    Dec 26, 2014    Original Ext 3 Implementation by: Nige "Animal" White?    Contact Info:    e. [e-mail]    blog: druckit.wordpress.com    linkedin: www.linkedin.com/in/uberfig    git: http://github.com/sdruckerfig    company: Fig Leaf Software (http://www.figleaf.com / http://training.figleaf.com)    Invocation:  grid.downloadExcelXml(includeHiddenColumns,title)    Upgraded for ExtJS5 on Dec 26, 2014*/var Base64 = (function() {    // Private property    var keyStr = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/=";    // Private method for UTF-8 encoding    function utf8Encode(string) {        string = string.replace(/\r\n/g, "\n");        var utftext = "";        for (var n = 0; n < string.length; n++) {            var c = string.charCodeAt(n);            if (c < 128) {                utftext += String.fromCharCode(c);            } else if ((c > 127) && (c < 2048)) {                utftext += String.fromCharCode((c >> 6) | 192);                utftext += String.fromCharCode((c & 63) | 128);            } else {                utftext += String.fromCharCode((c >> 12) | 224);                utftext += String.fromCharCode(((c >> 6) & 63) | 128);                utftext += String.fromCharCode((c & 63) | 128);            }        }        return utftext;    }    // Public method for encoding    return {        encode: (typeof btoa == 'function') ? function(input) {            return btoa(utf8Encode(input));        } : function(input) {            var output = "";            var chr1, chr2, chr3, enc1, enc2, enc3, enc4;            var i = 0;            input = utf8Encode(input);            while (i < input.length) {                chr1 = input.charCodeAt(i++);                chr2 = input.charCodeAt(i++);                chr3 = input.charCodeAt(i++);                enc1 = chr1 >> 2;                enc2 = ((chr1 & 3) << 4) | (chr2 >> 4);                enc3 = ((chr2 & 15) << 2) | (chr3 >> 6);                enc4 = chr3 & 63;                if (isNaN(chr2)) {                    enc3 = enc4 = 64;                } else if (isNaN(chr3)) {                    enc4 = 64;                }                output = output +                    keyStr.charAt(enc1) + keyStr.charAt(enc2) +                    keyStr.charAt(enc3) + keyStr.charAt(enc4);            }            return output;        }    };})();Ext.define('MyApp.overrides.view.Grid', {    override: 'Ext.grid.GridPanel',    requires: 'Ext.form.action.StandardSubmit',    /*        Kick off process    */    downloadExcelXml: function(includeHidden, title) {        if (!title) title = this.title;        var vExportContent = this.getExcelXml(includeHidden, title);        /*           dynamically create and anchor tag to force download with suggested filename           note: download attribute is Google Chrome specific        */        if (Ext.isChrome) {            var gridEl = this.getEl();            var location = 'data:application/vnd.ms-excel;base64,' + Base64.encode(vExportContent);            var el = Ext.DomHelper.append(gridEl, {                tag: "a",                download: title + "-" + Ext.Date.format(new Date(), 'Y-m-d Hi') + '.xls',                href: location            });            el.click();            Ext.fly(el).destroy();        } else {            var form = this.down('form#uploadForm');            if (form) {                form.destroy();            }            form = this.add({                xtype: 'form',                itemId: 'uploadForm',                hidden: true,                standardSubmit: true,                url: 'http://webapps.figleaf.com/dataservices/Excel.cfc?method=echo&mimetype=application/vnd.ms-excel&filename=' + escape(title + ".xls"),                items: [{                    xtype: 'hiddenfield',                    name: 'data',                    value: vExportContent                }]            });            form.getForm().submit();        }    },    /*        Welcome to XML Hell        See: http://msdn.microsoft.com/en-us/library/office/aa140066(v=office.10).aspx        for more details    */    getExcelXml: function(includeHidden, title) {        var theTitle = title || this.title;        var worksheet = this.createWorksheet(includeHidden, theTitle);        if (this.columnManager.columns) {            var totalWidth = this.columnManager.columns.length;        } else {             var totalWidth = this.columns.length;        }        return ''.concat(            '<?xml version="1.0"?>',            '<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"><Title>' + theTitle + '</Title></DocumentProperties>',            '<OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office"><AllowPNG/></OfficeDocumentSettings>',            '<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">',            '<WindowHeight>' + worksheet.height + '</WindowHeight>',            '<WindowWidth>' + worksheet.width + '</WindowWidth>',            '<ProtectStructure>False</ProtectStructure>',            '<ProtectWindows>False</ProtectWindows>',            '</ExcelWorkbook>',            '<Styles>',            '<Style ss:ID="Default" ss:Name="Normal">',            '<Alignment ss:Vertical="Bottom"/>',            '<Borders/>',            '<Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="12" ss:Color="#000000"/>',            '<Interior/>',            '<NumberFormat/>',            '<Protection/>',            '</Style>',            '<Style ss:ID="title">',            '<Borders />',            '<Font ss:Bold="1" ss:Size="18" />',            '<Alignment ss:Horizontal="Center" ss:Vertical="Center" ss:WrapText="1" />',            '<NumberFormat ss:Format="@" />',            '</Style>',            '<Style ss:ID="headercell">',            '<Font ss:Bold="1" ss:Size="10" />',            '<Alignment ss:Horizontal="Center" ss:WrapText="1" />',            '<Interior ss:Color="#A3C9F1" ss:Pattern="Solid" />',            '</Style>',            '<Style ss:ID="even">',            '<Interior ss:Color="#CCFFFF" ss:Pattern="Solid" />',            '</Style>',            '<Style ss:ID="evendate" ss:Parent="even">',            '<NumberFormat ss:Format="yyyy-mm-dd" />',            '</Style>',            '<Style ss:ID="evenint" ss:Parent="even">',            '<Numberformat ss:Format="0" />',            '</Style>',            '<Style ss:ID="evenfloat" ss:Parent="even">',            '<Numberformat ss:Format="0.00" />',            '</Style>',            '<Style ss:ID="odd">',            '<Interior ss:Color="#CCCCFF" ss:Pattern="Solid" />',            '</Style>',            '<Style ss:ID="groupSeparator">',            '<Interior ss:Color="#D3D3D3" ss:Pattern="Solid" />',            '</Style>',            '<Style ss:ID="odddate" ss:Parent="odd">',            '<NumberFormat ss:Format="yyyy-mm-dd" />',            '</Style>',            '<Style ss:ID="oddint" ss:Parent="odd">',            '<NumberFormat Format="0" />',            '</Style>',            '<Style ss:ID="oddfloat" ss:Parent="odd">',            '<NumberFormat Format="0.00" />',            '</Style>',            '</Styles>',            worksheet.xml,            '</Workbook>'        );    },    /*        Support function to return field info from store based on fieldname    */    getModelField: function(fieldName) {        var fields = this.store.model.getFields();        for (var i = 0; i < fields.length; i++) {            if (fields[i].name === fieldName) {                return fields[i];            }        }    },    /*        Convert store into Excel Worksheet    */    generateEmptyGroupRow: function(dataIndex, value, cellTypes, includeHidden) {        var cm = this.columnManager.columns;        var colCount = cm.length;        var rowTpl = '<Row ss:AutoFitHeight="0"><Cell ss:StyleID="groupSeparator" ss:MergeAcross="{0}"><Data ss:Type="String"><html:b>{1}</html:b></Data></Cell></Row>';        var visibleCols = 0;        // rowXml += '<Cell ss:StyleID="groupSeparator">'        for (var j = 0; j < colCount; j++) {            if (cm[j].xtype != 'actioncolumn' && (cm[j].dataIndex != '') && (includeHidden || !cm[j].hidden)) {                // rowXml += '<Cell ss:StyleID="groupSeparator"/>';                visibleCols++;            }        }        // rowXml += "</Row>";        return Ext.String.format(rowTpl, visibleCols - 1, Ext.String.htmlEncode(value));    },    createWorksheet: function(includeHidden, theTitle) {        // Calculate cell data types and extra class names which affect formatting        var cellType = [];        var cellTypeClass = [];        console.log(this);        if (this.columnManager.columns) {            var cm = this.columnManager.columns;        } else {            var cm = this.columns;        }        console.log(cm);        var colCount = cm.length;        var totalWidthInPixels = 0;        var colXml = '';        var headerXml = '';        var visibleColumnCountReduction = 0;        for (var i = 0; i < cm.length; i++) {            if (cm[i].xtype != 'actioncolumn' && (cm[i].dataIndex != '') && (includeHidden || !cm[i].hidden)) {                var w = cm[i].getEl().getWidth();                totalWidthInPixels += w;                if (cm[i].text === "") {                    cellType.push("None");                    cellTypeClass.push("");                    ++visibleColumnCountReduction;                } else {                    colXml += '<Column ss:AutoFitWidth="1" ss:Width="' + w + '" />';                    headerXml += '<Cell ss:StyleID="headercell">' +                        '<Data ss:Type="String">' + cm[i].text.replace("<br>"," ") + '</Data>' +                        '<NamedCell ss:Name="Print_Titles"></NamedCell></Cell>';                    var fld = this.getModelField(cm[i].dataIndex);                    switch (fld.$className) {                        case "Ext.data.field.Integer":                            cellType.push("Number");                            cellTypeClass.push("int");                            break;                        case "Ext.data.field.Number":                            cellType.push("Number");                            cellTypeClass.push("float");                            break;                        case "Ext.data.field.Boolean":                            cellType.push("String");                            cellTypeClass.push("");                            break;                        case "Ext.data.field.Date":                            cellType.push("DateTime");                            cellTypeClass.push("date");                            break;                        default:                            cellType.push("String");                            cellTypeClass.push("");                            break;                    }                }            }        }        var visibleColumnCount = cellType.length - visibleColumnCountReduction;        var result = {            height: 9000,            width: Math.floor(totalWidthInPixels * 30) + 50        };        // Generate worksheet header details.        // determine number of rows        var numGridRows = this.store.getCount() + 2;        if ((this.store.groupField &&!Ext.isEmpty(this.store.groupField)) || (this.store.groupers && this.store.groupers.items.length > 0)) {            numGridRows = numGridRows + this.store.getGroups().length;        }        // create header for worksheet        var t = ''.concat(            '<Worksheet ss:Name="' + theTitle + '">',            '<Names>',            '<NamedRange ss:Name="Print_Titles" ss:RefersTo="=\'' + theTitle + '\'!R1:R2">',            '</NamedRange></Names>',            '<Table ss:ExpandedColumnCount="' + (visibleColumnCount + 2),            '" ss:ExpandedRowCount="' + numGridRows + '" x:FullColumns="1" x:FullRows="1" ss:DefaultColumnWidth="65" ss:DefaultRowHeight="15">',            colXml,            '<Row ss:Height="38">',            '<Cell ss:MergeAcross="' + (visibleColumnCount - 1) + '" ss:StyleID="title">',            '<Data ss:Type="String" xmlns:html="http://www.w3.org/TR/REC-html40">',            '<html:b>' + theTitle + '</html:b></Data><NamedCell ss:Name="Print_Titles">',            '</NamedCell></Cell>',            '</Row>',            '<Row ss:AutoFitHeight="1">',            headerXml +            '</Row>'        );        // Generate the data rows from the data in the Store        var groupVal = "";        var groupField = "";        if (this.store.groupers && this.store.groupers.keys.length > 0) {            groupField = this.store.groupers.keys[0];        } else if (this.store.groupField != '') {             groupField = this.store.groupField;        }        for (var i = 0, it = this.store.data.items, l = it.length; i < l; i++) {            if (!Ext.isEmpty(groupField)) {                if (groupVal != this.store.getAt(i).get(groupField)) {                    groupVal = this.store.getAt(i).get(groupField);                    t += this.generateEmptyGroupRow(groupField, groupVal, cellType, includeHidden);                }            }            t += '<Row>';            var cellClass = (i & 1) ? 'odd' : 'even';            r = it[i].data;            var k = 0;            for (var j = 0; j < colCount; j++) {                if (cm[j].xtype != 'actioncolumn' && (cm[j].dataIndex != '') && (includeHidden || !cm[j].hidden)) {                    var v = r[cm[j].dataIndex];                    if (cellType[k] !== "None") {                        t += '<Cell ss:StyleID="' + cellClass + cellTypeClass[k] + '"><Data ss:Type="' + cellType[k] + '">';                        if (cellType[k] == 'DateTime') {                            t += Ext.Date.format(v, 'Y-m-d');                        } else {                            t += Ext.String.htmlEncode(v);                        }                        t += '</Data></Cell>';                    }                    k++;                }            }            t += '</Row>';        }        result.xml = t.concat(            '</Table>',            '<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">',            '<PageLayoutZoom>0</PageLayoutZoom>',            '<Selected/>',            '<Panes>',            '<Pane>',            '<Number>3</Number>',            '<ActiveRow>2</ActiveRow>',            '</Pane>',            '</Panes>',            '<ProtectObjects>False</ProtectObjects>',            '<ProtectScenarios>False</ProtectScenarios>',            '</WorksheetOptions>',            '</Worksheet>'        );        return result;    }});

附：在原文底部有Ext JS 4版本的链接