Export excel from Extjs Grid

exporter.js:
/**
 * allows for downloading of grid data (store) directly into excel
 * Method: extracts data of gridPanel store, uses columnModel to construct XML excel document,
 * converts to Base64, then loads everything into a data URL link.
 *
 * @author      Animal      
 *
 */

/**
 * base64 encode / decode
 *
 * @location    http://www.webtoolkit.info/
 *
 */

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.override(Ext.grid.GridPanel, {
    getExcelXml: function(includeHidden) {
        var worksheet = this.createWorksheet(includeHidden);
        var totalWidth = this.getColumnModel().getTotalWidth(includeHidden);
        return '' +
            '' +
            '' + this.title + '' +
            '' +
            '' + worksheet.height + '' +
            '' + worksheet.width + '' +
            'False' +
            'False' +
            '' +
            '' +
            '' +
            '' +
            '' +
            '' +
            '' +
            '' +
            '' +
            '' +
            '' +
            '' +
            '' +
            '' +
            '' +
            '' +
            '' +
            '' +
            '' +
            '' +
            '' +
            '' +
            '' +
            '' +
            '' +
            '' +
            '' +
            '' +
            '' +
            '' +
            '' +
            '' +
            '' +
            '' +
            '' +
            '' +
            '' +
            '' +
            '' +
            '' +
            '' +
            '' +
            '' +
            '' +
            '' +
            '' +
            '' +
            '' +
            '' +
            '' +
            '' +
            worksheet.xml +
            '';
    },

    createWorksheet: function(includeHidden) {
        // Calculate cell data types and extra class names which affect formatting
        var cellType = [];
        var cellTypeClass = [];
        var cm = this.getColumnModel();
        var totalWidthInPixels = 0;
        var colXml = '';
        var headerXml = '';
        var visibleColumnCountReduction = 0;
        var colCount = cm.getColumnCount();
        for (var i = 0; i < colCount; i++) {
            if ((cm.getDataIndex(i) != '')
                && (includeHidden || !cm.isHidden(i))) {
                var w = cm.getColumnWidth(i)
                totalWidthInPixels += w;
                if (cm.getColumnHeader(i) === ""){
                    cellType.push("None");
                    cellTypeClass.push("");
                    ++visibleColumnCountReduction;
                }
                else
                {
                    colXml += '';
                    headerXml += '' +
                        '' + cm.getColumnHeader(i) + '' +
                        '';
                    var fld = this.store.recordType.prototype.fields.get(cm.getDataIndex(i));
                    switch(fld.type) {
                        case "int":
                            cellType.push("Number");
                            cellTypeClass.push("int");
                            break;
                        case "float":
                            cellType.push("Number");
                            cellTypeClass.push("float");
                            break;
                        case "bool":
                        case "boolean":
                            cellType.push("String");
                            cellTypeClass.push("");
                            break;
                        case "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.
        var t = '' +
            '' +
            '' +

            '' +
            '' +
            colXml +
            '' +
            '' +
            '' +
            '' +
            '' +
            '' +
            '' +
            headerXml +
            '';

        // Generate the data rows from the data in the Store
        for (var i = 0, it = this.store.data.items, l = it.length; i < l; i++) {
            t += '';
            var cellClass = (i & 1) ? 'odd' : 'even';
            r = it[i].data;
            var k = 0;
            for (var j = 0; j < colCount; j++) {
                if ((cm.getDataIndex(j) != '')
                    && (includeHidden || !cm.isHidden(j))) {
                    var v = r[cm.getDataIndex(j)];
                    if (cellType[k] !== "None") {
                        t += '';
                        if (cellType[k] == 'DateTime') {
                            t += v.format('Y-m-d');
                        } else {
                            t += v;
                        }
                        t +='';
                    }
                    k++;
                }
            }
            t += '';
        }

        result.xml = t + '' +
            '' +
            '' +
            '' +
            '' +
            '' +
            '' +
            '' +
            '' +
            'Blank' +
            '1' +
            '32767' +
            '' +
            '600' +
            '' +
            '' +
            '' +
            'False' +
            'False' +
            '' +
            '';
        return result;
    }
});
Button Code:
{
                            text : "导出到excel",
                            style : {
                                marginRight : '20px'
                            },
                            handler : function() {
                                var vExportContent = gridpanel.getExcelXml(); //获取数据
                            if (Ext.isIE8||Ext.isIE6 || Ext.isIE7 || Ext.isSafari
                                    || Ext.isSafari2 || Ext.isSafari3) { //判断浏览器

                                var fd = Ext.get('frmDummy');
                                if (!fd) {
                                    fd = Ext.DomHelper.append(
                                            Ext.getBody(), {
                                                tag : 'form',
                                                method : 'post',
                                                id : 'frmDummy',
                                                action : 'exportUrl.jsp',
                                                target : '_blank',
                                                name : 'frmDummy',
                                                cls : 'x-hidden',
                                                cn : [ {
                                                    tag : 'input',
                                                    name : 'exportContent',
                                                    id : 'exportContent',
                                                    type : 'hidden'
                                                } ]
                                            }, true);
                                    
                                }
                                fd.child('#exportContent').set( {
                                    value : vExportContent
                                });
                                fd.dom.submit();
                            } else {
                                document.location = 'data:application/vnd.ms-excel;base64,' + Base64
                                        .encode(vExportContent);
                            }
                        }}
Exporter.jsp
<%
response.setHeader("Content-Type","application/force-download");
response.setHeader("Content-Type","application/vnd.ms-excel");
response.setHeader("Content-Disposition","attachment;filename=export.xls");
out.print(request.getParameter("exportContent"));
%>
References:
http://www.blogjava.net/rockblue1988/archive/2012/02/29/370977.html
http://extjs.org.cn/node/324

1 .Download Ext.ux.Exporter plugin at here (included full source code and examples).
2 .Include your Ext.ux.Exporter plugin to your html code.
 
 
 
 
//Including plugin 


3. Set up a simple grid with a export button
//Create the Download button and add it to the top toolbar 
var exportButton = new Ext.ux.Exporter.Button({ 
  component: grid, 
  text     : "Download as .xls" 
}); 
//Add download button to the top grid toolbar 
grid.getTopToolbar().add(exportButton);
Reference:
http://extjstutorial.net/discussion/3/export-extjs-grid-to-excel/p1

留言

這個網誌中的熱門文章

Multiple writable mappings exist for the field. Only one may be defined as writable, all others must be specified read-only.

java.lang.NoClassDefFoundError: org/apache/xerces/jaxp/datatype/XMLGregorianCalendarImpl$Parser

Cordova iOS with Failed to load webpage with error: Could not connect to the server.