1. #281
    Sencha User
    Join Date
    Aug 2011
    Location
    Bangalore
    Posts
    66
    Vote Rating
    0
    Sabareesh is on a distinguished road

      0  

    Default Changing The Title

    Changing The Title


    Quote Originally Posted by Animal View Post
    This needs a browser that supports data URLs. FF, Opera and IE8 will support this.

    Tested on FF2 and Opera 9.

    This example extracts the data of a GridPanel's Store, and uses the ColumnModel to construct an XML Excel document, converts it to Base64, and loads it into a data URL in a link.

    You can click the link, and "download" the grid straight into Excel.

    Obviously this only works on the data in the Store - if you are using server-side paging, then perform this processing on the server. For quick and dirty conversion of a small table to Excel, this might be useful.

    If the data in the Store is volatile (It gets reloaded or edited), the data URL will have to be recalculated.

    Drop this file as examples/grid/array-grid.js:

    Code:
    /**
    *
    *  Base64 encode / decode
    *  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(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.LinkButton = Ext.extend(Ext.Button, {
        template: new Ext.Template(
            '<table border="0" cellpadding="0" cellspacing="0" class="x-btn-wrap"><tbody><tr>',
            '<td class="x-btn-left"><i> </i></td><td class="x-btn-center"><a class="x-btn-text" href="{1}" target="{2}">{0}</a></td><td class="x-btn-right"><i> </i></td>',
            "</tr></tbody></table>"),
        
        onRender:   function(ct, position){
            var btn, targs = [this.text || ' ', this.href, this.target || "_self"];
            if(position){
                btn = this.template.insertBefore(position, targs, true);
            }else{
                btn = this.template.append(ct, targs, true);
            }
            var btnEl = btn.child("a:first");
            btnEl.on('focus', this.onFocus, this);
            btnEl.on('blur', this.onBlur, this);
    
            this.initButtonEl(btn, btnEl);
            Ext.ButtonToggleMgr.register(this);
        },
    
        onClick : function(e){
            if(e.button != 0){
                return;
            }
            if(!this.disabled){
                this.fireEvent("click", this, e);
                if(this.handler){
                    this.handler.call(this.scope || this, this, e);
                }
            }
        }
    
    });
    
    Ext.override(Ext.grid.GridPanel, {
        getExcelXml: function(includeHidden) {
            var worksheet = this.createWorksheet(includeHidden);
            var totalWidth = this.getColumnModel().getTotalWidth(includeHidden);
            return '<?xml version="1.0" encoding="utf-8"?>' +
                '<ss:Workbook xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:o="urn:schemas-microsoft-com:office:office">' +
                '<o:DocumentProperties><o:Title>' + this.title + '</o:Title></o:DocumentProperties>' +
                '<ss:ExcelWorkbook>' +
                    '<ss:WindowHeight>' + worksheet.height + '</ss:WindowHeight>' +
                    '<ss:WindowWidth>' + worksheet.width + '</ss:WindowWidth>' +
                    '<ss:ProtectStructure>False</ss:ProtectStructure>' +
                    '<ss:ProtectWindows>False</ss:ProtectWindows>' +
                '</ss:ExcelWorkbook>' +
                '<ss:Styles>' +
                    '<ss:Style ss:ID="Default">' +
                        '<ss:Alignment ss:Vertical="Top" ss:WrapText="1" />' +
                        '<ss:Font ss:FontName="arial" ss:Size="10" />' +
                        '<ss:Borders>' +
                            '<ss:Border ss:Color="#e4e4e4" ss:Weight="1" ss:LineStyle="Continuous" ss:Position="Top" />' +
                            '<ss:Border ss:Color="#e4e4e4" ss:Weight="1" ss:LineStyle="Continuous" ss:Position="Bottom" />' +
                            '<ss:Border ss:Color="#e4e4e4" ss:Weight="1" ss:LineStyle="Continuous" ss:Position="Left" />' +
                            '<ss:Border ss:Color="#e4e4e4" ss:Weight="1" ss:LineStyle="Continuous" ss:Position="Right" />' +
                        '</ss:Borders>' +
                        '<ss:Interior />' +
                        '<ss:NumberFormat />' +
                        '<ss:Protection />' +
                    '</ss:Style>' +
                    '<ss:Style ss:ID="title">' +
                        '<ss:Borders />' +
                        '<ss:Font />' +
                        '<ss:Alignment ss:WrapText="1" ss:Vertical="Center" ss:Horizontal="Center" />' +
                        '<ss:NumberFormat ss:Format="@" />' +
                    '</ss:Style>' +
                    '<ss:Style ss:ID="headercell">' +
                        '<ss:Font ss:Bold="1" ss:Size="10" />' +
                        '<ss:Alignment ss:WrapText="1" ss:Horizontal="Center" />' +
                        '<ss:Interior ss:Pattern="Solid" ss:Color="#A3C9F1" />' +
                    '</ss:Style>' +
                    '<ss:Style ss:ID="even">' +
                        '<ss:Interior ss:Pattern="Solid" ss:Color="#CCFFFF" />' +
                    '</ss:Style>' +
                    '<ss:Style ss:Parent="even" ss:ID="evendate">' +
                        '<ss:NumberFormat ss:Format="[ENG][$-409]dd\-mmm\-yyyy;@" />' +
                    '</ss:Style>' +
                    '<ss:Style ss:Parent="even" ss:ID="evenint">' +
                        '<ss:NumberFormat ss:Format="0" />' +
                    '</ss:Style>' +
                    '<ss:Style ss:Parent="even" ss:ID="evenfloat">' +
                        '<ss:NumberFormat ss:Format="0.00" />' +
                    '</ss:Style>' +
                    '<ss:Style ss:ID="odd">' +
                        '<ss:Interior ss:Pattern="Solid" ss:Color="#CCCCFF" />' +
                    '</ss:Style>' +
                    '<ss:Style ss:Parent="odd" ss:ID="odddate">' +
                        '<ss:NumberFormat ss:Format="[ENG][$-409]dd\-mmm\-yyyy;@" />' +
                    '</ss:Style>' +
                    '<ss:Style ss:Parent="odd" ss:ID="oddint">' +
                        '<ss:NumberFormat ss:Format="0" />' +
                    '</ss:Style>' +
                    '<ss:Style ss:Parent="odd" ss:ID="oddfloat">' +
                        '<ss:NumberFormat ss:Format="0.00" />' +
                    '</ss:Style>' +
                '</ss:Styles>' +
                worksheet.xml +
                '</ss:Workbook>';
        },
    
        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 = '';
            for (var i = 0; i < cm.getColumnCount(); i++) {
                if (includeHidden || !cm.isHidden(i)) {
                    var w = cm.getColumnWidth(i)
                    totalWidthInPixels += w;
                    colXml += '<ss:Column ss:AutoFitWidth="1" ss:Width="' + w + '" />';
                    headerXml += '<ss:Cell ss:StyleID="headercell">' +
                        '<ss:Data ss:Type="String">' + cm.getColumnHeader(i) + '</ss:Data>' +
                        '<ss:NamedCell ss:Name="Print_Titles" /></ss:Cell>';
                    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;
    
            var result = {
                height: 9000,
                width: Math.floor(totalWidthInPixels * 30) + 50
            };
    
    //      Generate worksheet header details.
            var t = '<ss:Worksheet ss:Name="' + this.title + '">' +
                '<ss:Names>' +
                    '<ss:NamedRange ss:Name="Print_Titles" ss:RefersTo="=\'' + this.title + '\'!R1:R2" />' +
                '</ss:Names>' +
                '<ss:Table x:FullRows="1" x:FullColumns="1"' +
                    ' ss:ExpandedColumnCount="' + visibleColumnCount +
                    '" ss:ExpandedRowCount="' + (this.store.getCount() + 2) + '">' +
                    colXml +
                    '<ss:Row ss:Height="38">' +
                        '<ss:Cell ss:StyleID="title" ss:MergeAcross="' + (visibleColumnCount - 1) + '">' +
                          '<ss:Data xmlns:html="http://www.w3.org/TR/REC-html40" ss:Type="String">' +
                            '<html:B><html:U><html:Font html:Size="15">' + this.title +
                            '</html:Font></html:U></html:B>
    Generated by ExtJs</ss:Data><ss:NamedCell ss:Name="Print_Titles" />' +
                        '</ss:Cell>' +
                    '</ss:Row>' +
                    '<ss:Row ss:AutoFitHeight="1">' +
                    headerXml + 
                    '</ss:Row>';
    
    //      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 += '<ss:Row>';
                var cellClass = (i & 1) ? 'odd' : 'even';
                r = it[i].data;
                var k = 0;
                for (var j = 0; j < cm.getColumnCount(); j++) {
                    if (includeHidden || !cm.isHidden(j)) {
                        var v = r[cm.getDataIndex(j)];
                        t += '<ss:Cell ss:StyleID="' + cellClass + cellTypeClass[k] + '"><ss:Data ss:Type="' + cellType[k] + '">';
                            if (cellType[k] == 'DateTime') {
                                t += v.format('Y-m-d');
                            } else {
                                t += v;
                            }
                        t +='</ss:Data></ss:Cell>';
                        k++;
                    }
                }
                t += '</ss:Row>';
            }
    
            result.xml = t + '</ss:Table>' +
                '<x:WorksheetOptions>' +
                    '<x:PageSetup>' +
                        '<x:Layout x:CenterHorizontal="1" x:Orientation="Landscape" />' +
                        '<x:Footer x:Data="Page &amp;P of &amp;N" x:Margin="0.5" />' +
                        '<x:PageMargins x:Top="0.5" x:Right="0.5" x:Left="0.5" x:Bottom="0.8" />' +
                    '</x:PageSetup>' +
                    '<x:FitToPage />' +
                    '<x:Print>' +
                        '<x:PrintErrors>Blank</x:PrintErrors>' +
                        '<x:FitWidth>1</x:FitWidth>' +
                        '<x:FitHeight>32767</x:FitHeight>' +
                        '<x:ValidPrinterInfo />' +
                        '<x:VerticalResolution>600</x:VerticalResolution>' +
                    '</x:Print>' +
                    '<x:Selected />' +
                    '<x:DoNotDisplayGridlines />' +
                    '<x:ProtectObjects>False</x:ProtectObjects>' +
                    '<x:ProtectScenarios>False</x:ProtectScenarios>' +
                '</x:WorksheetOptions>' +
            '</ss:Worksheet>';
            return result;
        }
    });
    
    Ext.onReady(function(){
    
        Ext.state.Manager.setProvider(new Ext.state.CookieProvider());
    
        var myData = [
            ['3m Co',71.72,0.02,0.03,'9/1 12:00am'],
            ['Alcoa Inc',29.01,0.42,1.47,'9/1 12:00am'],
            ['Altria Group Inc',83.81,0.28,0.34,'9/1 12:00am'],
            ['American Express Company',52.55,0.01,0.02,'9/1 12:00am'],
            ['American International Group, Inc.',64.13,0.31,0.49,'9/1 12:00am'],
            ['AT&T Inc.',31.61,-0.48,-1.54,'9/1 12:00am'],
            ['Boeing Co.',75.43,0.53,0.71,'9/1 12:00am'],
            ['Caterpillar Inc.',67.27,0.92,1.39,'9/1 12:00am'],
            ['Citigroup, Inc.',49.37,0.02,0.04,'9/1 12:00am'],
            ['E.I. du Pont de Nemours and Company',40.48,0.51,1.28,'9/1 12:00am'],
            ['Exxon Mobil Corp',68.1,-0.43,-0.64,'9/1 12:00am'],
            ['General Electric Company',34.14,-0.08,-0.23,'9/1 12:00am'],
            ['General Motors Corporation',30.27,1.09,3.74,'9/1 12:00am'],
            ['Hewlett-Packard Co.',36.53,-0.03,-0.08,'9/1 12:00am'],
            ['Honeywell Intl Inc',38.77,0.05,0.13,'9/1 12:00am'],
            ['Intel Corporation',19.88,0.31,1.58,'9/1 12:00am'],
            ['International Business Machines',81.41,0.44,0.54,'9/1 12:00am'],
            ['Johnson & Johnson',64.72,0.06,0.09,'9/1 12:00am'],
            ['JP Morgan & Chase & Co',45.73,0.07,0.15,'9/1 12:00am'],
            ['McDonald\'s Corporation',36.76,0.86,2.40,'9/1 12:00am'],
            ['Merck & Co., Inc.',40.96,0.41,1.01,'9/1 12:00am'],
            ['Microsoft Corporation',25.84,0.14,0.54,'9/1 12:00am'],
            ['Pfizer Inc',27.96,0.4,1.45,'9/1 12:00am'],
            ['The Coca-Cola Company',45.07,0.26,0.58,'9/1 12:00am'],
            ['The Home Depot, Inc.',34.64,0.35,1.02,'9/1 12:00am'],
            ['The Procter & Gamble Company',61.91,0.01,0.02,'9/1 12:00am'],
            ['United Technologies Corporation',63.26,0.55,0.88,'9/1 12:00am'],
            ['Verizon Communications',35.57,0.39,1.11,'9/1 12:00am'],
            ['Wal-Mart Stores, Inc.',45.45,0.73,1.63,'9/1 12:00am']
        ];
    
        // example of custom renderer function
        function change(val){
            if(val > 0){
                return '<span style="color:green;">' + val + '</span>';
            }else if(val < 0){
                return '<span style="color:red;">' + val + '</span>';
            }
            return val;
        }
    
        // example of custom renderer function
        function pctChange(val){
            if(val > 0){
                return '<span style="color:green;">' + val + '%</span>';
            }else if(val < 0){
                return '<span style="color:red;">' + val + '%</span>';
            }
            return val;
        }
    
        // create the data store
        var store = new Ext.data.SimpleStore({
            fields: [
               {name: 'company'},
               {name: 'price', type: 'float'},
               {name: 'change', type: 'float'},
               {name: 'pctChange', type: 'float'},
               {name: 'lastChange', type: 'date', dateFormat: 'n/j h:ia'}
            ]
        });
        store.loadData(myData);
    
    
        var linkButton = new Ext.LinkButton({
            id: 'grid-excel-button',
            text: 'Excel'
        });
    
        // create the Grid
        var grid = new Ext.grid.GridPanel({
            id: 'static-grid',
            store: store,
            columns: [
                {id:'company',header: "Company", width: 160, sortable: true, dataIndex: 'company'},
                {header: "Price", width: 75, sortable: true, renderer: 'usMoney', dataIndex: 'price'},
                {header: "Change", width: 75, sortable: true, renderer: change, dataIndex: 'change'},
                {header: "% Change", width: 75, sortable: true, renderer: pctChange, dataIndex: 'pctChange'},
                {header: "Last Updated", width: 85, sortable: true, renderer: Ext.util.Format.dateRenderer('m/d/Y'), dataIndex: 'lastChange'}
            ],
            stripeRows: true,
            autoExpandColumn: 'company',
            height:350,
            width:600,
            title:'Array Grid',
            bbar: new Ext.Toolbar({
                buttons: [linkButton]
            })
        });
    
        grid.render('grid-example');
        linkButton.getEl().child('a', true).href = 'data:application/vnd.ms-excel;base64,' +
            Base64.encode(grid.getExcelXml());
    
        grid.getSelectionModel().selectFirstRow();
    });




    How to change the title of the excel sheet generated.I am getting the excel sheet but the name is encoded randomly with combination of numbers and alphabets .

  2. #282
    Sencha User
    Join Date
    Aug 2011
    Location
    Bangalore
    Posts
    66
    Vote Rating
    0
    Sabareesh is on a distinguished road

      0  

    Default Renaming the excel sheet

    Renaming the excel sheet


    Quote Originally Posted by NoahK17 View Post
    Alright, I've successfully implimented this mod. Here are the easy-mode directions for new users to ExtJS.

    Create a new file, save it as ExportGridToExcel.js with this content:
    Code:
    /**
     * 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        <extjs support team>
     *
     */
    
    /**
     * 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(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 '<xml version="1.0" encoding="utf-8">' +
                '<ss:Workbook xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:o="urn:schemas-microsoft-com:office:office">' +
                '<o:DocumentProperties><o:Title>' + this.title + '</o:Title></o:DocumentProperties>' +
                '<ss:ExcelWorkbook>' +
                    '<ss:WindowHeight>' + worksheet.height + '</ss:WindowHeight>' +
                    '<ss:WindowWidth>' + worksheet.width + '</ss:WindowWidth>' +
                    '<ss:ProtectStructure>False</ss:ProtectStructure>' +
                    '<ss:ProtectWindows>False</ss:ProtectWindows>' +
                '</ss:ExcelWorkbook>' +
                '<ss:Styles>' +
                    '<ss:Style ss:ID="Default">' +
                        '<ss:Alignment ss:Vertical="Top" ss:WrapText="1" />' +
                        '<ss:Font ss:FontName="arial" ss:Size="10" />' +
                        '<ss:Borders>' +
                            '<ss:Border ss:Color="#e4e4e4" ss:Weight="1" ss:LineStyle="Continuous" ss:Position="Top" />' +
                            '<ss:Border ss:Color="#e4e4e4" ss:Weight="1" ss:LineStyle="Continuous" ss:Position="Bottom" />' +
                            '<ss:Border ss:Color="#e4e4e4" ss:Weight="1" ss:LineStyle="Continuous" ss:Position="Left" />' +
                            '<ss:Border ss:Color="#e4e4e4" ss:Weight="1" ss:LineStyle="Continuous" ss:Position="Right" />' +
                        '</ss:Borders>' +
                        '<ss:Interior />' +
                        '<ss:NumberFormat />' +
                        '<ss:Protection />' +
                    '</ss:Style>' +
                    '<ss:Style ss:ID="title">' +
                        '<ss:Borders />' +
                        '<ss:Font />' +
                        '<ss:Alignment ss:WrapText="1" ss:Vertical="Center" ss:Horizontal="Center" />' +
                        '<ss:NumberFormat ss:Format="@" />' +
                    '</ss:Style>' +
                    '<ss:Style ss:ID="headercell">' +
                        '<ss:Font ss:Bold="1" ss:Size="10" />' +
                        '<ss:Alignment ss:WrapText="1" ss:Horizontal="Center" />' +
                        '<ss:Interior ss:Pattern="Solid" ss:Color="#A3C9F1" />' +
                    '</ss:Style>' +
                    '<ss:Style ss:ID="even">' +
                        '<ss:Interior ss:Pattern="Solid" ss:Color="#CCFFFF" />' +
                    '</ss:Style>' +
                    '<ss:Style ss:Parent="even" ss:ID="evendate">' +
                        '<ss:NumberFormat ss:Format="[ENG][$-409]dd\-mmm\-yyyy;@" />' +
                    '</ss:Style>' +
                    '<ss:Style ss:Parent="even" ss:ID="evenint">' +
                        '<ss:NumberFormat ss:Format="0" />' +
                    '</ss:Style>' +
                    '<ss:Style ss:Parent="even" ss:ID="evenfloat">' +
                        '<ss:NumberFormat ss:Format="0.00" />' +
                    '</ss:Style>' +
                    '<ss:Style ss:ID="odd">' +
                        '<ss:Interior ss:Pattern="Solid" ss:Color="#CCCCFF" />' +
                    '</ss:Style>' +
                    '<ss:Style ss:Parent="odd" ss:ID="odddate">' +
                        '<ss:NumberFormat ss:Format="[ENG][$-409]dd\-mmm\-yyyy;@" />' +
                    '</ss:Style>' +
                    '<ss:Style ss:Parent="odd" ss:ID="oddint">' +
                        '<ss:NumberFormat ss:Format="0" />' +
                    '</ss:Style>' +
                    '<ss:Style ss:Parent="odd" ss:ID="oddfloat">' +
                        '<ss:NumberFormat ss:Format="0.00" />' +
                    '</ss:Style>' +
                '</ss:Styles>' +
                worksheet.xml +
                '</ss:Workbook>';
        },
    
        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 = '';
            for (var i = 0; i < cm.getColumnCount(); i++) {
                if (includeHidden || !cm.isHidden(i)) {
                    var w = cm.getColumnWidth(i)
                    totalWidthInPixels += w;
                    colXml += '<ss:Column ss:AutoFitWidth="1" ss:Width="' + w + '" />';
                    headerXml += '<ss:Cell ss:StyleID="headercell">' +
                        '<ss:Data ss:Type="String">' + cm.getColumnHeader(i) + '</ss:Data>' +
                        '<ss:NamedCell ss:Name="Print_Titles" /></ss:Cell>';
                    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;
    
            var result = {
                height: 9000,
                width: Math.floor(totalWidthInPixels * 30) + 50
            };
    
            // Generate worksheet header details.
            var t = '<ss:Worksheet ss:Name="' + this.title + '">' +
                '<ss:Names>' +
                    '<ss:NamedRange ss:Name="Print_Titles" ss:RefersTo="=\'' + this.title + '\'!R1:R2" />' +
                '</ss:Names>' +
                '<ss:Table x:FullRows="1" x:FullColumns="1"' +
                    ' ss:ExpandedColumnCount="' + visibleColumnCount +
                    '" ss:ExpandedRowCount="' + (this.store.getCount() + 2) + '">' +
                    colXml +
                    '<ss:Row ss:Height="38">' +
                        '<ss:Cell ss:StyleID="title" ss:MergeAcross="' + (visibleColumnCount - 1) + '">' +
                          '<ss:Data xmlns:html="http://www.w3.org/TR/REC-html40" ss:Type="String">' +
                            '<html:B>Generated by ExtJS</html:B></ss:Data><ss:NamedCell ss:Name="Print_Titles" />' +
                        '</ss:Cell>' +
                    '</ss:Row>' +
                    '<ss:Row ss:AutoFitHeight="1">' +
                    headerXml +
                    '</ss:Row>';
    
            // 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 += '<ss:Row>';
                var cellClass = (i & 1) ? 'odd' : 'even';
                r = it[i].data;
                var k = 0;
                for (var j = 0; j < cm.getColumnCount(); j++) {
                    if (includeHidden || !cm.isHidden(j)) {
                        var v = r[cm.getDataIndex(j)];
                        t += '<ss:Cell ss:StyleID="' + cellClass + cellTypeClass[k] + '"><ss:Data ss:Type="' + cellType[j] + '">';
                            if (cellType[k] == 'DateTime') {
                                t += v.format('Y-m-d');
                            } else {
                                t += v;
                            }
                        t +='</ss:Data></ss:Cell>';
                        k++;
                    }
                }
                t += '</ss:Row>';
            }
    
            result.xml = t + '</ss:Table>' +
                '<x:WorksheetOptions>' +
                    '<x:PageSetup>' +
                        '<x:Layout x:CenterHorizontal="1" x:Orientation="Landscape" />' +
                        '<x:Footer x:Data="Page &amp;P of &amp;N" x:Margin="0.5" />' +
                        '<x:PageMargins x:Top="0.5" x:Right="0.5" x:Left="0.5" x:Bottom="0.8" />' +
                    '</x:PageSetup>' +
                    '<x:FitToPage />' +
                    '<x:Print>' +
                        '<x:PrintErrors>Blank</x:PrintErrors>' +
                        '<x:FitWidth>1</x:FitWidth>' +
                        '<x:FitHeight>32767</x:FitHeight>' +
                        '<x:ValidPrinterInfo />' +
                        '<x:VerticalResolution>600</x:VerticalResolution>' +
                    '</x:Print>' +
                    '<x:Selected />' +
                    '<x:DoNotDisplayGridlines />' +
                    '<x:ProtectObjects>False</x:ProtectObjects>' +
                    '<x:ProtectScenarios>False</x:ProtectScenarios>' +
                '</x:WorksheetOptions>' +
            '</ss:Worksheet>';
            return result;
        }
    });
    Now, call this newly created JS file in the header of your application (php/html/etc), after you call your standard ExtJS files. You'll have to change your directory structure to however you have things set-up.
    Code:
    <script type="text/javascript" src="libs/js/ExportGridToExcel.js"></script>
    Finally, we want to add the "Export to Excel" button using the bottom-bar of your grid. So in the config options, add this:

    Code:
                    bbar: new Ext.Toolbar({
                        buttons: [{
                            id: 'grid-excel-button',
                            text: 'Export to Excel...',
                            handler: function(){
                                document.location='data:application/vnd.ms-excel;base64,' + Base64.encode(grid.getExcelXml());
                            }
                        }]
                    })
    Now when you load your grid, at the bottom you should see a new button. Clicking that button will open the SAVE AS dialog box with the entire content of your grid available in the Excel format.

    Cheers!
    Everything is working fine , but how to rename the excel sheet generated , also it is working only in firefox , how to make it compatible with IE and other browsers.

    Thanks in advance

  3. #283

  4. #284
    Ext JS Premium Member
    Join Date
    Jan 2012
    Posts
    33
    Vote Rating
    0
    Vasanthoo7 is on a distinguished road

      0  

    Default


    Animal, how do i get this working with EXTJS 4.
    I get the following error:

    getE1 is null or not on object on 380.

    Code:
    linkButton.getEl().child('a', true).href = 'data:application/vnd.ms-excel;base64,' +
            Base64.encode(grid.getExcelXml());

  5. #285
    Sencha User
    Join Date
    Feb 2012
    Posts
    3
    Vote Rating
    0
    garrek99 is on a distinguished road

      0  

    Default Got it working in ExtJS 4

    Got it working in ExtJS 4


    First of all - Awesome job Animal. Thanks a lot. So many years after the original post this is still quite useful.

    Second - after some augmentations it is now ready for ExtJS 4. Some of the differences that needed to be addressed were regarding the now lacking columnModel and other small functions here and there.

    Code:
    
    /**
    *
    *  Base64 encode / decode
    *  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(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.LinkButton = Ext.extend(Ext.Button, {
        template: new Ext.Template(
            '<table border="0" cellpadding="0" cellspacing="0" class="x-btn-wrap"><tbody><tr>',
            '<td class="x-btn-left"><i> </i></td><td class="x-btn-center"><a class="x-btn-text" href="{1}" target="{2}">{0}</a></td><td class="x-btn-right"><i> </i></td>',
            "</tr></tbody></table>"),
    
    
        onRender: function (ct, position) {
            var btn, targs = [this.text || ' ', this.href, this.target || "_self"];
            if (position) {
                btn = this.template.insertBefore(position, targs, true);
            } else {
                btn = this.template.append(ct, targs, true);
            }
            var btnEl = btn.child("a:first");
            btnEl.on('focus', this.onFocus, this);
            btnEl.on('blur', this.onBlur, this);
    
    
            this.initButtonEl(btn, btnEl);
            Ext.ButtonToggleMgr.register(this);
        },
    
    
        onClick: function (e) {
            if (e.button != 0) {
                return;
            }
            if (!this.disabled) {
                this.fireEvent("click", this, e);
                if (this.handler) {
                    this.handler.call(this.scope || this, this, e);
                }
            }
        }
    
    
    });
    
    
    Ext.override(Ext.grid.GridPanel, {
        getExcelXml: function (includeHidden) {
            var worksheet = this.createWorksheet(includeHidden);
            var totalWidth = this.columns[1].getFullWidth();
            //var totalWidth = this.getColumnModel().getTotalWidth(includeHidden);
            return '<?xml version="1.0" encoding="utf-8"?>' +
                '<ss:Workbook xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:o="urn:schemas-microsoft-com:office:office">' +
                '<o:DocumentProperties><o:Title>' + this.title + '</o:Title></o:DocumentProperties>' +
                '<ss:ExcelWorkbook>' +
                    '<ss:WindowHeight>' + worksheet.height + '</ss:WindowHeight>' +
                    '<ss:WindowWidth>' + worksheet.width + '</ss:WindowWidth>' +
                    '<ss:ProtectStructure>False</ss:ProtectStructure>' +
                    '<ss:ProtectWindows>False</ss:ProtectWindows>' +
                '</ss:ExcelWorkbook>' +
                '<ss:Styles>' +
                    '<ss:Style ss:ID="Default">' +
                        '<ss:Alignment ss:Vertical="Top" ss:WrapText="1" />' +
                        '<ss:Font ss:FontName="arial" ss:Size="10" />' +
                        '<ss:Borders>' +
                            '<ss:Border ss:Color="#e4e4e4" ss:Weight="1" ss:LineStyle="Continuous" ss:Position="Top" />' +
                            '<ss:Border ss:Color="#e4e4e4" ss:Weight="1" ss:LineStyle="Continuous" ss:Position="Bottom" />' +
                            '<ss:Border ss:Color="#e4e4e4" ss:Weight="1" ss:LineStyle="Continuous" ss:Position="Left" />' +
                            '<ss:Border ss:Color="#e4e4e4" ss:Weight="1" ss:LineStyle="Continuous" ss:Position="Right" />' +
                        '</ss:Borders>' +
                        '<ss:Interior />' +
                        '<ss:NumberFormat />' +
                        '<ss:Protection />' +
                    '</ss:Style>' +
                    '<ss:Style ss:ID="title">' +
                        '<ss:Borders />' +
                        '<ss:Font />' +
                        '<ss:Alignment ss:WrapText="1" ss:Vertical="Center" ss:Horizontal="Center" />' +
                        '<ss:NumberFormat ss:Format="@" />' +
                    '</ss:Style>' +
                    '<ss:Style ss:ID="headercell">' +
                        '<ss:Font ss:Bold="1" ss:Size="10" />' +
                        '<ss:Alignment ss:WrapText="1" ss:Horizontal="Center" />' +
                        '<ss:Interior ss:Pattern="Solid" ss:Color="#A3C9F1" />' +
                    '</ss:Style>' +
                    '<ss:Style ss:ID="even">' +
                        '<ss:Interior ss:Pattern="Solid" ss:Color="#CCFFFF" />' +
                    '</ss:Style>' +
                    '<ss:Style ss:Parent="even" ss:ID="evendate">' +
                        '<ss:NumberFormat ss:Format="[ENG][$-409]dd\-mmm\-yyyy;@" />' +
                    '</ss:Style>' +
                    '<ss:Style ss:Parent="even" ss:ID="evenint">' +
                        '<ss:NumberFormat ss:Format="0" />' +
                    '</ss:Style>' +
                    '<ss:Style ss:Parent="even" ss:ID="evenfloat">' +
                        '<ss:NumberFormat ss:Format="0.00" />' +
                    '</ss:Style>' +
                    '<ss:Style ss:ID="odd">' +
                        '<ss:Interior ss:Pattern="Solid" ss:Color="#FFFFFF" />' +
                    '</ss:Style>' +
                    '<ss:Style ss:Parent="odd" ss:ID="odddate">' +
                        '<ss:NumberFormat ss:Format="[ENG][$-409]dd\-mmm\-yyyy;@" />' +
                    '</ss:Style>' +
                    '<ss:Style ss:Parent="odd" ss:ID="oddint">' +
                        '<ss:NumberFormat ss:Format="0" />' +
                    '</ss:Style>' +
                    '<ss:Style ss:Parent="odd" ss:ID="oddfloat">' +
                        '<ss:NumberFormat ss:Format="0.00" />' +
                    '</ss:Style>' +
                '</ss:Styles>' +
                worksheet.xml +
                '</ss:Workbook>';
        },
    
    
        createWorksheet: function (includeHidden) {
    
    
            //      Calculate cell data types and extra class names which affect formatting
            var cellType = [];
            var cellTypeClass = [];
            var cm = this.columns;
            var totalWidthInPixels = 0;
            var colXml = '';
            var headerXml = '';
            for (var i = 0; i < cm.length; i++) {
                if (includeHidden || !cm[i].isHidden()) {
                    var w = cm[i].getWidth()
                    totalWidthInPixels += w;
                    colXml += '<ss:Column ss:AutoFitWidth="1" ss:Width="' + w + '" />';
                    headerXml += '<ss:Cell ss:StyleID="headercell">' +
                        '<ss:Data ss:Type="String">' + cm[i].text + '</ss:Data>' +
                        '<ss:NamedCell ss:Name="Print_Titles" /></ss:Cell>';
                    var fld = this.store.model.prototype.fields.get(cm[i].dataIndex);
                    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;
    
    
            var result = {
                height: 9000,
                width: Math.floor(totalWidthInPixels * 30) + 50
            };
    
    
            //      Generate worksheet header details.
            var t = '<ss:Worksheet ss:Name="' + this.title + '"><ss:Names><ss:NamedRange ss:Name="Print_Titles" ss:RefersTo="=\'' + this.title + '\'!R1:R2" /></ss:Names><ss:Table x:FullRows="1" x:FullColumns="1" ss:ExpandedColumnCount="' + visibleColumnCount + '" ss:ExpandedRowCount="' + (this.store.getCount() + 2) + '">' + colXml + '<ss:Row ss:Height="38"><ss:Cell ss:StyleID="title" ss:MergeAcross="' + (visibleColumnCount - 1) + '"><ss:Data xmlns:html="http://www.w3.org/TR/REC-html40" ss:Type="String"><html:B><html:U><html:Font html:Size="15">' + this.title + '</html:Font></html:U></html:B></ss:Data><ss:NamedCell ss:Name="Print_Titles" /></ss:Cell></ss:Row><ss:Row ss:AutoFitHeight="1">' + headerXml + '</ss:Row>';
    
    
            //      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 += '<ss:Row>';
                var cellClass = (i & 1) ? 'odd' : 'even';
                r = it[i].data;
                var k = 0;
                for (var j = 0; j < cm.length; j++) {
                    if (includeHidden || !cm[j].isHidden()) {
                        var v = r[cm[j].dataIndex];
                        t += '<ss:Cell ss:StyleID="' + cellClass + cellTypeClass[k] + '"><ss:Data ss:Type="' + cellType[k] + '">';
                        if (cellType[k] == 'DateTime') {
                            t += v.format('Y-m-d');
                        } else {
                            t += v;
                        }
                        t += '</ss:Data></ss:Cell>';
                        k++;
                    }
                }
                t += '</ss:Row>';
            }
    
    
            result.xml = t + '</ss:Table>' +
                '<x:WorksheetOptions>' +
                    '<x:PageSetup>' +
                        '<x:Layout x:CenterHorizontal="1" x:Orientation="Landscape" />' +
                        '<x:Footer x:Data="Page &amp;P of &amp;N" x:Margin="0.5" />' +
                        '<x:PageMargins x:Top="0.5" x:Right="0.5" x:Left="0.5" x:Bottom="0.8" />' +
                    '</x:PageSetup>' +
                    '<x:FitToPage />' +
                    '<x:Print>' +
                        '<x:PrintErrors>Blank</x:PrintErrors>' +
                        '<x:FitWidth>1</x:FitWidth>' +
                        '<x:FitHeight>32767</x:FitHeight>' +
                        '<x:ValidPrinterInfo />' +
                        '<x:VerticalResolution>600</x:VerticalResolution>' +
                    '</x:Print>' +
                    '<x:Selected />' +
                    '<x:DoNotDisplayGridlines />' +
                    '<x:ProtectObjects>False</x:ProtectObjects>' +
                    '<x:ProtectScenarios>False</x:ProtectScenarios>' +
                '</x:WorksheetOptions>' +
            '</ss:Worksheet>';
            return result;
        }
    })

  6. #286
    Sencha User
    Join Date
    Apr 2011
    Posts
    1
    Vote Rating
    0
    lagrezd is on a distinguished road

      0  

    Default Browser compatibility ?

    Browser compatibility ?


    Thanks for this code !
    Someone would have found a tips for browser compatibility including Chrome?
    Best regards

  7. #287
    Sencha User
    Join Date
    Nov 2012
    Posts
    1
    Vote Rating
    0
    opik is on a distinguished road

      0  

    Default


    Quote Originally Posted by garrek99 View Post
    First of all - Awesome job Animal. Thanks a lot. So many years after the original post this is still quite useful.

    Second - after some augmentations it is now ready for ExtJS 4. Some of the differences that needed to be addressed were regarding the now lacking columnModel and other small functions here and there.

    Code:
    
    /**
    *
    *  Base64 encode / decode
    *  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(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.LinkButton = Ext.extend(Ext.Button, {
        template: new Ext.Template(
            '<table border="0" cellpadding="0" cellspacing="0" class="x-btn-wrap"><tbody><tr>',
            '<td class="x-btn-left"><i>  </i></td><td class="x-btn-center"><a  class="x-btn-text" href="{1}"  target="{2}">{0}</a></td><td  class="x-btn-right"><i> </i></td>',
            "</tr></tbody></table>"),
    
    
        onRender: function (ct, position) {
            var btn, targs = [this.text || ' ', this.href, this.target || "_self"];
            if (position) {
                btn = this.template.insertBefore(position, targs, true);
            } else {
                btn = this.template.append(ct, targs, true);
            }
            var btnEl = btn.child("a:first");
            btnEl.on('focus', this.onFocus, this);
            btnEl.on('blur', this.onBlur, this);
    
    
            this.initButtonEl(btn, btnEl);
            Ext.ButtonToggleMgr.register(this);
        },
    
    
        onClick: function (e) {
            if (e.button != 0) {
                return;
            }
            if (!this.disabled) {
                this.fireEvent("click", this, e);
                if (this.handler) {
                    this.handler.call(this.scope || this, this, e);
                }
            }
        }
    
    
    });
    
    
    Ext.override(Ext.grid.GridPanel, {
        getExcelXml: function (includeHidden) {
            var worksheet = this.createWorksheet(includeHidden);
            var totalWidth = this.columns[1].getFullWidth();
            //var totalWidth = this.getColumnModel().getTotalWidth(includeHidden);
            return '<?xml version="1.0" encoding="utf-8"?>' +
                '<ss:Workbook  xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"  xmlns:x="urn:schemas-microsoft-com:office:excel"  xmlns:o="urn:schemas-microsoft-com:office:office">' +
                '<o:DocumentProperties><o:Title>' + this.title + '</o:Title></o:DocumentProperties>' +
                '<ss:ExcelWorkbook>' +
                    '<ss:WindowHeight>' + worksheet.height + '</ss:WindowHeight>' +
                    '<ss:WindowWidth>' + worksheet.width + '</ss:WindowWidth>' +
                    '<ss:ProtectStructure>False</ss:ProtectStructure>' +
                    '<ss:ProtectWindows>False</ss:ProtectWindows>' +
                '</ss:ExcelWorkbook>' +
                '<ss:Styles>' +
                    '<ss:Style ss:ID="Default">' +
                        '<ss:Alignment ss:Vertical="Top" ss:WrapText="1" />' +
                        '<ss:Font ss:FontName="arial" ss:Size="10" />' +
                        '<ss:Borders>' +
                            '<ss:Border ss:Color="#e4e4e4" ss:Weight="1" ss:LineStyle="Continuous" ss:Position="Top" />' +
                            '<ss:Border ss:Color="#e4e4e4" ss:Weight="1" ss:LineStyle="Continuous" ss:Position="Bottom" />' +
                            '<ss:Border ss:Color="#e4e4e4" ss:Weight="1" ss:LineStyle="Continuous" ss:Position="Left" />' +
                            '<ss:Border ss:Color="#e4e4e4" ss:Weight="1" ss:LineStyle="Continuous" ss:Position="Right" />' +
                        '</ss:Borders>' +
                        '<ss:Interior />' +
                        '<ss:NumberFormat />' +
                        '<ss:Protection />' +
                    '</ss:Style>' +
                    '<ss:Style ss:ID="title">' +
                        '<ss:Borders />' +
                        '<ss:Font />' +
                        '<ss:Alignment ss:WrapText="1" ss:Vertical="Center" ss:Horizontal="Center" />' +
                        '<ss:NumberFormat ss:Format="@" />' +
                    '</ss:Style>' +
                    '<ss:Style ss:ID="headercell">' +
                        '<ss:Font ss:Bold="1" ss:Size="10" />' +
                        '<ss:Alignment ss:WrapText="1" ss:Horizontal="Center" />' +
                        '<ss:Interior ss:Pattern="Solid" ss:Color="#A3C9F1" />' +
                    '</ss:Style>' +
                    '<ss:Style ss:ID="even">' +
                        '<ss:Interior ss:Pattern="Solid" ss:Color="#CCFFFF" />' +
                    '</ss:Style>' +
                    '<ss:Style ss:Parent="even" ss:ID="evendate">' +
                        '<ss:NumberFormat ss:Format="[ENG][$-409]dd\-mmm\-yyyy;@" />' +
                    '</ss:Style>' +
                    '<ss:Style ss:Parent="even" ss:ID="evenint">' +
                        '<ss:NumberFormat ss:Format="0" />' +
                    '</ss:Style>' +
                    '<ss:Style ss:Parent="even" ss:ID="evenfloat">' +
                        '<ss:NumberFormat ss:Format="0.00" />' +
                    '</ss:Style>' +
                    '<ss:Style ss:ID="odd">' +
                        '<ss:Interior ss:Pattern="Solid" ss:Color="#FFFFFF" />' +
                    '</ss:Style>' +
                    '<ss:Style ss:Parent="odd" ss:ID="odddate">' +
                        '<ss:NumberFormat ss:Format="[ENG][$-409]dd\-mmm\-yyyy;@" />' +
                    '</ss:Style>' +
                    '<ss:Style ss:Parent="odd" ss:ID="oddint">' +
                        '<ss:NumberFormat ss:Format="0" />' +
                    '</ss:Style>' +
                    '<ss:Style ss:Parent="odd" ss:ID="oddfloat">' +
                        '<ss:NumberFormat ss:Format="0.00" />' +
                    '</ss:Style>' +
                '</ss:Styles>' +
                worksheet.xml +
                '</ss:Workbook>';
        },
    
    
        createWorksheet: function (includeHidden) {
    
    
            //      Calculate cell data types and extra class names which affect formatting
            var cellType = [];
            var cellTypeClass = [];
            var cm = this.columns;
            var totalWidthInPixels = 0;
            var colXml = '';
            var headerXml = '';
            for (var i = 0; i < cm.length; i++) {
                if (includeHidden || !cm[i].isHidden()) {
                    var w = cm[i].getWidth()
                    totalWidthInPixels += w;
                    colXml += '<ss:Column ss:AutoFitWidth="1" ss:Width="' + w + '" />';
                    headerXml += '<ss:Cell ss:StyleID="headercell">' +
                        '<ss:Data ss:Type="String">' + cm[i].text + '</ss:Data>' +
                        '<ss:NamedCell ss:Name="Print_Titles" /></ss:Cell>';
                    var fld = this.store.model.prototype.fields.get(cm[i].dataIndex);
                    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;
    
    
            var result = {
                height: 9000,
                width: Math.floor(totalWidthInPixels * 30) + 50
            };
    
    
            //      Generate worksheet header details.
            var t = '<ss:Worksheet ss:Name="' + this.title +  '"><ss:Names><ss:NamedRange ss:Name="Print_Titles"  ss:RefersTo="=\'' + this.title + '\'!R1:R2"  /></ss:Names><ss:Table x:FullRows="1" x:FullColumns="1"  ss:ExpandedColumnCount="' + visibleColumnCount + '"  ss:ExpandedRowCount="' + (this.store.getCount() + 2) + '">' + colXml +  '<ss:Row ss:Height="38"><ss:Cell ss:StyleID="title"  ss:MergeAcross="' + (visibleColumnCount - 1) + '"><ss:Data  xmlns:html="http://www.w3.org/TR/REC-html40"  ss:Type="String"><html:B><html:U><html:Font  html:Size="15">' + this.title +  '</html:Font></html:U></html:B></ss:Data><ss:NamedCell  ss:Name="Print_Titles" /></ss:Cell></ss:Row><ss:Row  ss:AutoFitHeight="1">' + headerXml + '</ss:Row>';
    
    
            //      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 += '<ss:Row>';
                var cellClass = (i & 1) ? 'odd' : 'even';
                r = it[i].data;
                var k = 0;
                for (var j = 0; j < cm.length; j++) {
                    if (includeHidden || !cm[j].isHidden()) {
                        var v = r[cm[j].dataIndex];
                        t += '<ss:Cell ss:StyleID="' + cellClass +  cellTypeClass[k] + '"><ss:Data ss:Type="' + cellType[k] + '">';
                        if (cellType[k] == 'DateTime') {
                            t += v.format('Y-m-d');
                        } else {
                            t += v;
                        }
                        t += '</ss:Data></ss:Cell>';
                        k++;
                    }
                }
                t += '</ss:Row>';
            }
    
    
            result.xml = t + '</ss:Table>' +
                '<x:WorksheetOptions>' +
                    '<x:PageSetup>' +
                        '<x:Layout x:CenterHorizontal="1" x:Orientation="Landscape" />' +
                        '<x:Footer x:Data="Page &amp;P of &amp;N" x:Margin="0.5" />' +
                        '<x:PageMargins x:Top="0.5" x:Right="0.5" x:Left="0.5" x:Bottom="0.8" />' +
                    '</x:PageSetup>' +
                    '<x:FitToPage />' +
                    '<x:Print>' +
                        '<x:PrintErrors>Blank</x:PrintErrors>' +
                        '<x:FitWidth>1</x:FitWidth>' +
                        '<x:FitHeight>32767</x:FitHeight>' +
                        '<x:ValidPrinterInfo />' +
                        '<x:VerticalResolution>600</x:VerticalResolution>' +
                    '</x:Print>' +
                    '<x:Selected />' +
                    '<x:DoNotDisplayGridlines />' +
                    '<x:ProtectObjects>False</x:ProtectObjects>' +
                    '<x:ProtectScenarios>False</x:ProtectScenarios>' +
                '</x:WorksheetOptions>' +
            '</ss:Worksheet>';
            return result;
        }
    })
    Thank's for the code but when i run with my grid
    i got this error (see with firebug)

    TypeError: fld is undefined

    [TABLE]

  8. #288
    Banned
    Join Date
    Oct 2013
    Posts
    34
    Vote Rating
    1
    shankar8rajah1 is on a distinguished road

      0  

    Default


    I got the following error when I open with excel:

    XML PARSE ERROR: Extraneous end-tag
    Error occurs at or below this element stack:
    <ss:Workbook>
    <ss:Worksheet>
    <ss:Table>
    <ss:Row>
    <ss:Cell>
    <ssata>

    Here is the following XML, but I can't seem to see where the extraneous tag is?:

    <?xml version="1.0" encoding="utf-8"?><ss:Workbook xmlnss="urnchemas-microsoft-com:officepreadsheet" xmlns:x="urnchemas-microsoft-com:office:excel" xmlns:o="urnchemas-microsoft-com:office:office"><oocumentProperties><o:Title>undefined</o:Title></oocumentProperties><ss:ExcelWorkbook><ss:WindowHeight>9000</ss:WindowHeight><ss:WindowWidth>36650</ss:WindowWidth><ss:ProtectStructure>False</ss:ProtectStructure><ss:ProtectWindows>False</ss:ProtectWindows></ss:ExcelWorkbook><ss:Styles><ss:Style ss:ID="Default"><ss:Alignment ss:Vertical="Top" ss:WrapText="1" /><ss:Font ss:FontName="arial" ss:Size="10" /><ss:Borders><ss:Border ss:Color="#e4e4e4" ss:Weight="1" ss:LineStyle="Continuous" ss:Position="Top" /><ss:Border ss:Color="#e4e4e4" ss:Weight="1" ss:LineStyle="Continuous" ss:Position="Bottom" /><ss:Border ss:Color="#e4e4e4" ss:Weight="1" ss:LineStyle="Continuous" ss:Position="Left" /><ss:Border ss:Color="#e4e4e4" ss:Weight="1" ss:LineStyle="Continuous" ss:Position="Right" /></ss:Borders><ss:Interior /><ss:NumberFormat /><ss:Protection /></ss:Style><ss:Style ss:ID="title"><ss:Borders /><ss:Font /><ss:Alignment ss:WrapText="1" ss:Vertical="Center" ss:Horizontal="Center" /><ss:NumberFormat ss:Format="@" /></ss:Style><ss:Style ss:ID="headercell"><ss:Font ss:Bold="1" ss:Size="10" /><ss:Alignment ss:WrapText="1" ss:Horizontal="Center" /><ss:Interior ss:Pattern="Solid" ss:Color="#A3C9F1" /></ss:Style><ss:Style ss:ID="even"><ss:Interior ss:Pattern="Solid" ss:Color="#CCFFFF" /></ss:Style><ss:Style ss:Parent="even" ss:ID="evendate"><ss:NumberFormat ss:Format="[ENG][$-409]dd-mmm-yyyy;@" /></ss:Style><ss:Style ss:Parent="even" ss:ID="evenint"><ss:NumberFormat ss:Format="0" /></ss:Style><ss:Style ss:Parent="even" ss:ID="evenfloat"><ss:NumberFormat ss:Format="0.00" /></ss:Style><ss:Style ss:ID="odd"><ss:Interior ss:Pattern="Solid" ss:Color="#FFFFFF" /></ss:Style><ss:Style ss:Parent="odd" ss:ID="odddate"><ss:NumberFormat ss:Format="[ENG][$-409]dd-mmm-yyyy;@" /></ss:Style><ss:Style ss:Parent="odd" ss:ID="oddint"><ss:NumberFormat ss:Format="0" /></ss:Style><ss:Style ss:Parent="odd" ss:ID="oddfloat"><ss:NumberFormat ss:Format="0.00" /></ss:Style></ss:Styles><ss:Worksheet ss:Name="undefined"><ss:Names><ss:NamedRange ss:Name="Print_Titles" ss:RefersTo="='undefined'!R1:R2" /></ss:Names><ss:Table x:FullRows="1" x:FullColumns="1" ss:ExpandedColumnCount="10" ss:ExpandedRowCount="3"><ss:Column ss:AutoFitWidth="1" ss:Width="110" /><ss:Column ss:AutoFitWidth="1" ss:Width="170" /><ss:Column ss:AutoFitWidth="1" ss:Width="80" /><ss:Column ss:AutoFitWidth="1" ss:Width="200" /><ss:Column ss:AutoFitWidth="1" ss:Width="70" /><ss:Column ss:AutoFitWidth="1" ss:Width="60" /><ss:Column ss:AutoFitWidth="1" ss:Width="130" /><ss:Column ss:AutoFitWidth="1" ss:Width="220" /><ss:Column ss:AutoFitWidth="1" ss:Width="100" /><ss:Column ss:AutoFitWidth="1" ss:Width="80" /><ss:Row ss:Height="38"><ss:Cell ss:StyleID="title" ss:MergeAcross="9"><ssata xmlns:html="http://www.w3.org/TR/REC-html40" ss:Type="String"><html:B><html:U><html:Font html:Size="15">undefined</html:Font></html:U></html:B></ssata><ss:NamedCell ss:Name="Print_Titles" /></ss:Cell></ss:Row><ss:Row ss:AutoFitHeight="1"><ss:Cell ss:StyleID="headercell"><ssata ss:Type="String">Name</ssata><ss:NamedCell ss:Name="Print_Titles" /></ss:Cell><ss:Cell ss:StyleID="headercell"><ssata ss:Type="String">Service Group</ssata><ss:NamedCell ss:Name="Print_Titles" /></ss:Cell><ss:Cell ss:StyleID="headercell"><ssata ss:Type="String">Azul Version</ssata><ss:NamedCell ss:Name="Print_Titles" /></ss:Cell><ss:Cell ss:StyleID="headercell"><ssata ss:Type="String">Instances</ssata><ss:NamedCell ss:Name="Print_Titles" /></ss:Cell><ss:Cell ss:StyleID="headercell"><ssata ss:Type="String">Host Tech</ssata><ss:NamedCell ss:Name="Print_Titles" /></ss:Cell><ss:Cell ss:StyleID="headercell"><ssata ss:Type="String">AIT Num</ssata><ss:NamedCell ss:Name="Print_Titles" /></ss:Cell><ss:Cell ss:StyleID="headercell"><ssata ss:Type="String">App Name</ssata><ss:NamedCell ss:Name="Print_Titles" /></ss:Cell><ss:Cell ss:StyleID="headercell"><ssata ss:Type="String">Filer-Mounts</ssata><ss:NamedCell ss:Name="Print_Titles" /></ss:Cell><ss:Cell ss:StyleID="headercell"><ssata ss:Type="String">Data Center</ssata><ss:NamedCell ss:Name="Print_Titles" /></ss:Cell><ss:Cell ss:StyleID="headercell"><ssata ss:Type="String">Env</ssata><ss:NamedCell ss:Name="Print_Titles" /></ss:Cell></ss:Row><ss:Row><ss:Cell ss:StyleID="even"><ssata ss:Type="String">USW</ssata></ss:Cell><ss:Cell ss:StyleID="even"><ssata ss:Type="String">est</ssata></ss:Cell><ss:Cell ss:StyleID="even"><ssata ss:Type="String">ZVM: 0</br></br>ZST:0</ssata></ss:Cell><ss:Cell ss:StyleID="even"><ssata ss:Type="String">QA=[PRISM]</ssata></ss:Cell><ss:Cell ss:StyleID="even"><ssata ss:Type="String">HOST </ssata></ss:Cell><ss:Cell ss:StyleID="even"><ssata ss:Type="String">50443</ssata></ss:Cell><ss:Cell ss:StyleID="even"><ssata ss:Type="String">NG</ssata></ss:Cell><ss:Cell ss:StyleID="even"><ssata ss:Type="String">null</ssata></ss:Cell><ss:Cell ss:StyleID="even"><ssata ss:Type="String">WEEHAWKEN</ssata></ss:Cell><ss:Cell ss:StyleID="even"><ssata ss:Type="String">REPLAY</ssata></ss:Cell></ss:Row></ss:Table><x:WorksheetOptions><x:PageSetup><x:Layout x:CenterHorizontal="1" x:Orientation="Landscape" /><x:Footer xata="Page &amp;P of &amp;N" x:Margin="0.5" /><x:PageMargins x:Top="0.5" x:Right="0.5" x:Left="0.5" x:Bottom="0.8" /></x:PageSetup><x:FitToPage /><x:Print><x:PrintErrors>Blank</x:PrintErrors><x:FitWidth>1</x:FitWidth><x:FitHeight>32767</x:FitHeight><x:ValidPrinterInfo /><x:VerticalResolution>600</x:VerticalResolution></x:Print><x:Selected /><xoNotDisplayGridlines /><x:ProtectObjects>False</x:ProtectObjects><x:ProtectScenarios>False</x:ProtectScenarios></x:WorksheetOptions></ss:Worksheet></ss:Workbook>

  9. #289
    Sencha User
    Join Date
    Feb 2012
    Posts
    3
    Vote Rating
    0
    garrek99 is on a distinguished road

      0  

    Default Made some changes since then.

    Made some changes since then.


    Hi all

    I have made some changes to the code but unfortunately it was some time ago and I forgot where the changes are. Give it a run and refactor to fit your situation.

    Good Luck

    Code:
    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(input); } : function (input) {
            //encode: 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, {
        getCSV: function () {
            var cm = this.columns;
            var result = '';
            //Generate the header row
            for (var i = 0; i < cm.length; i++) {
    			if (!cm[i].text.match(/Temp Assign/i)) {
    				if (i < cm.length - 1) {
    					result += cm[i].text + ',';
    				} else {
    					result += cm[i].text + '\n';
    				}
    			}
            }
            //Generate the data rows from the data in the Store
            for (var i = 0, it = PolycomStoreAll.data.items, l = it.length; i < l; i++) {
                var r = it[i].data;
                for (var j = 0; j < cm.length; j++) {
    				if (!cm[j].text.match(/Temp Assign/i)) {
    					if (j < cm.length - 1) {
    						result += '"' + r[cm[j].dataIndex] + '",';
    					} else {
    						result += '"' + r[cm[j].dataIndex] + '"\n';
    					}
    				}
                }
            }
            return result;
        },
    
    
        getExcel: function () {
            var worksheet = this.createIEWorksheet(false);
            var totalWidth = this.columns[1].getFullWidth();
            //var totalWidth = this.getColumnModel().getTotalWidth(includeHidden);
            return '<?xml version="1.0" encoding="utf-8"?>' +
                '<ss:Workbook xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:o="urn:schemas-microsoft-com:office:office">' +
                '<o:DocumentProperties><o:Title>Polycomm Array</o:Title></o:DocumentProperties>' +
                '<ss:ExcelWorkbook>' +
                    '<ss:WindowHeight>' + worksheet.height + '</ss:WindowHeight>' +
                    '<ss:WindowWidth>' + worksheet.width + '</ss:WindowWidth>' +
                    '<ss:ProtectStructure>False</ss:ProtectStructure>' +
                    '<ss:ProtectWindows>False</ss:ProtectWindows>' +
                '</ss:ExcelWorkbook>' +
                '<ss:Styles>' +
                    '<ss:Style ss:ID="Default">' +
                        '<ss:Alignment ss:Vertical="Top" ss:WrapText="0" />' +
                        '<ss:Font ss:FontName="arial" ss:Size="9" />' +
                        '<ss:Borders>' +
                            '<ss:Border ss:Color="#e4e4e4" ss:Weight="1" ss:LineStyle="Continuous" ss:Position="Top" />' +
                            '<ss:Border ss:Color="#e4e4e4" ss:Weight="1" ss:LineStyle="Continuous" ss:Position="Bottom" />' +
                            '<ss:Border ss:Color="#e4e4e4" ss:Weight="1" ss:LineStyle="Continuous" ss:Position="Left" />' +
                            '<ss:Border ss:Color="#e4e4e4" ss:Weight="1" ss:LineStyle="Continuous" ss:Position="Right" />' +
                        '</ss:Borders>' +
                        '<ss:Interior />' +
                        '<ss:NumberFormat />' +
                        '<ss:Protection />' +
                    '</ss:Style>' +
                    '<ss:Style ss:ID="title">' +
                        '<ss:Borders />' +
                        '<ss:Font />' +
                        '<ss:Alignment ss:WrapText="1" ss:Vertical="Center" ss:Horizontal="Center" />' +
                        '<ss:NumberFormat ss:Format="@" />' +
                    '</ss:Style>' +
                    '<ss:Style ss:ID="headercell">' +
                        '<ss:Font ss:Bold="1" ss:Size="9" />' +
                        '<ss:Alignment ss:WrapText="1" ss:Horizontal="Center" />' +
                        '<ss:Interior ss:Pattern="Solid" ss:Color="#A3C9F1" />' +
                    '</ss:Style>' +
                    '<ss:Style ss:ID="even">' +
                        '<ss:Interior ss:Pattern="Solid" ss:Color="#CCFFFF" />' +
                    '</ss:Style>' +
                    '<ss:Style ss:Parent="even" ss:ID="evendate">' +
            //'<ss:Font ss:Size="9" />' +
            //'<ss:NumberFormat ss:Format="Small Date;@" />' +
                        '<ss:NumberFormat ss:Format="[ENG][$-409]dd\-mmm\-yyyy;@" />' +
                    '</ss:Style>' +
                    '<ss:Style ss:Parent="even" ss:ID="evenint">' +
                        '<ss:NumberFormat ss:Format="0" />' +
                    '</ss:Style>' +
                    '<ss:Style ss:Parent="even" ss:ID="evenfloat">' +
                        '<ss:NumberFormat ss:Format="0.00" />' +
                    '</ss:Style>' +
                    '<ss:Style ss:ID="odd">' +
                        '<ss:Interior ss:Pattern="Solid" ss:Color="#FFFFFF" />' +
                    '</ss:Style>' +
                    '<ss:Style ss:Parent="odd" ss:ID="odddate">' +
            //'<ss:Font ss:Size="9" />' +
            //'<ss:NumberFormat ss:Format="Small Date;@" />' +
                        '<ss:NumberFormat ss:Format="[ENG][$-409]dd\-mmm\-yyyy;@" />' +
                    '</ss:Style>' +
                    '<ss:Style ss:Parent="odd" ss:ID="oddint">' +
                        '<ss:NumberFormat ss:Format="0" />' +
                    '</ss:Style>' +
                    '<ss:Style ss:Parent="odd" ss:ID="oddfloat">' +
                        '<ss:NumberFormat ss:Format="0.00" />' +
                    '</ss:Style>' +
                '</ss:Styles>' +
                worksheet.xml +
                '</ss:Workbook>';
        },
    
    
        createIEWorksheet: function (includeHidden) {
            //      Calculate cell data types and extra class names which affect formatting
            var cellType = [];
            var cellTypeClass = [];
            var cm = this.columns;
            var totalWidthInPixels = 0;
            var colXml = '';
            var headerXml = '';
            for (var i = 0; i < cm.length; i++) {
                if (!cm[i].text.match(/Temp Assign/i) && (includeHidden || !cm[i].isHidden())) {
                    var w = cm[i].getWidth()
                    totalWidthInPixels += w;
                    colXml += '<ss:Column ss:AutoFitWidth="1" ss:Width="' + w + '" />';
                    headerXml += '<ss:Cell ss:StyleID="headercell">' +
                        '<ss:Data ss:Type="String">' + cm[i].text + '</ss:Data>' +
                        '<ss:NamedCell ss:Name="Print_Titles" /></ss:Cell>';
                    var fld = Ext.getStore('PolycomStoreAll').model.prototype.fields.get(cm[i].dataIndex);
                    switch (fld.type.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");
                            cellType.push("String");
                            cellTypeClass.push("");
                            break;
                        default:
                            cellType.push("String");
                            cellTypeClass.push("");
                            break;
                    }
                }
            }
            var visibleColumnCount = cellType.length;
    
    
            var result = {
                height: 9000,
                width: Math.floor(totalWidthInPixels * 30) + 50
            };
    
    
            //      Generate worksheet header details.
            var t = '<ss:Worksheet ss:Name="Polycomm Array"><ss:Names><ss:NamedRange ss:Name="Print_Titles" ss:RefersTo="=\'Polycomm Array\'!R1:R2" /></ss:Names><ss:Table x:FullRows="1" x:FullColumns="1" ss:ExpandedColumnCount="' + visibleColumnCount + '" ss:ExpandedRowCount="' + (Ext.getStore('PolycomStoreAll').getCount() + 2) + '">' + colXml +
            //'<ss:Row ss:Height="38"><ss:Cell ss:StyleID="title" ss:MergeAcross="' + (visibleColumnCount - 1) + '"><ss:Data xmlns:html="http://www.w3.org/TR/REC-html40" ss:Type="String"><html:B><html:U><html:Font html:Size="15">Polycomm Array</html:Font></html:U></html:B></ss:Data><ss:NamedCell ss:Name="Print_Titles" /></ss:Cell></ss:Row>
            '<ss:Row ss:AutoFitHeight="1">' + headerXml + '</ss:Row>';
    
    
            //      Generate the data rows from the data in the Store
            for (var i = 0, it = Ext.getStore('PolycomStoreAll').data.items, l = it.length; i < l; i++) {
                t += '<ss:Row>';
                var cellClass = (i & 1) ? 'odd' : 'even';
                r = it[i].data;
                var k = 0;
                for (var j = 0; j < cm.length; j++) {
    				if (!cm[j].text.match(/Temp Assign/i) && (includeHidden || !cm[j].isHidden())) {
                        var v = r[cm[j].dataIndex];
                        t += '<ss:Cell ss:StyleID="' + cellClass + cellTypeClass[k] + '"><ss:Data ss:Type="' + cellType[k] + '">';
                        if (cellTypeClass[k] == 'date') {
                            t += Ext.Date.format(v, 'm/d/Y');
                        } else {
                            t += v;
                        }
                        t += '</ss:Data></ss:Cell>';
                        k++;
                    }
                }
                t += '</ss:Row>';
            }
    
    
            result.xml = t + '</ss:Table>' +
                '<x:WorksheetOptions>' +
                    '<x:PageSetup>' +
                        '<x:Layout x:CenterHorizontal="1" x:Orientation="Landscape" />' +
                        '<x:Footer x:Data="Page &amp;P of &amp;N" x:Margin="0.5" />' +
                        '<x:PageMargins x:Top="0.5" x:Right="0.5" x:Left="0.5" x:Bottom="0.8" />' +
                    '</x:PageSetup>' +
                    '<x:FitToPage />' +
                    '<x:Print>' +
                        '<x:PrintErrors>Blank</x:PrintErrors>' +
                        '<x:FitWidth>1</x:FitWidth>' +
                        '<x:FitHeight>32767</x:FitHeight>' +
                        '<x:ValidPrinterInfo />' +
                        '<x:VerticalResolution>600</x:VerticalResolution>' +
                    '</x:Print>' +
                    '<x:Selected />' +
                    '<x:DoNotDisplayGridlines />' +
                    '<x:ProtectObjects>False</x:ProtectObjects>' +
                    '<x:ProtectScenarios>False</x:ProtectScenarios>' +
                '</x:WorksheetOptions>' +
            '</ss:Worksheet>';
            return result;
        },
    
    
        getExcelXml: function () {
            var worksheet = this.createWorksheet(false);
            var totalWidth = this.columns[1].getFullWidth();
            //var totalWidth = this.getColumnModel().getTotalWidth(includeHidden);
            return '<?xml version="1.0" encoding="utf-8"?>' +
                '<ss:Workbook xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:o="urn:schemas-microsoft-com:office:office">' +
                '<o:DocumentProperties><o:Title>Polycomm Array</o:Title></o:DocumentProperties>' +
                '<ss:ExcelWorkbook>' +
                    '<ss:WindowHeight>' + worksheet.height + '</ss:WindowHeight>' +
                    '<ss:WindowWidth>' + worksheet.width + '</ss:WindowWidth>' +
                    '<ss:ProtectStructure>False</ss:ProtectStructure>' +
                    '<ss:ProtectWindows>False</ss:ProtectWindows>' +
                '</ss:ExcelWorkbook>' +
                '<ss:Styles>' +
                    '<ss:Style ss:ID="Default">' +
                        '<ss:Alignment ss:Vertical="Top" ss:WrapText="0" />' +
                        '<ss:Font ss:FontName="arial" ss:Size="9" />' +
                        '<ss:Borders>' +
                            '<ss:Border ss:Color="#e4e4e4" ss:Weight="1" ss:LineStyle="Continuous" ss:Position="Top" />' +
                            '<ss:Border ss:Color="#e4e4e4" ss:Weight="1" ss:LineStyle="Continuous" ss:Position="Bottom" />' +
                            '<ss:Border ss:Color="#e4e4e4" ss:Weight="1" ss:LineStyle="Continuous" ss:Position="Left" />' +
                            '<ss:Border ss:Color="#e4e4e4" ss:Weight="1" ss:LineStyle="Continuous" ss:Position="Right" />' +
                        '</ss:Borders>' +
                        '<ss:Interior />' +
                        '<ss:NumberFormat />' +
                        '<ss:Protection />' +
                    '</ss:Style>' +
                    '<ss:Style ss:ID="title">' +
                        '<ss:Borders />' +
                        '<ss:Font />' +
                        '<ss:Alignment ss:WrapText="1" ss:Vertical="Center" ss:Horizontal="Center" />' +
                        '<ss:NumberFormat ss:Format="@" />' +
                    '</ss:Style>' +
                    '<ss:Style ss:ID="headercell">' +
                        '<ss:Font ss:Bold="1" ss:Size="9" />' +
                        '<ss:Alignment ss:WrapText="1" ss:Horizontal="Center" />' +
                        '<ss:Interior ss:Pattern="Solid" ss:Color="#A3C9F1" />' +
                    '</ss:Style>' +
                    '<ss:Style ss:ID="even">' +
                        '<ss:Interior ss:Pattern="Solid" ss:Color="#CCFFFF" />' +
                    '</ss:Style>' +
                    '<ss:Style ss:Parent="even" ss:ID="evendate">' +
            //'<ss:Font ss:Size="9" />' +
            //'<ss:NumberFormat ss:Format="Small Date;@" />' +
                        '<ss:NumberFormat ss:Format="[ENG][$-409]dd\-mmm\-yyyy;@" />' +
                    '</ss:Style>' +
                    '<ss:Style ss:Parent="even" ss:ID="evenint">' +
                        '<ss:NumberFormat ss:Format="0" />' +
                    '</ss:Style>' +
                    '<ss:Style ss:Parent="even" ss:ID="evenfloat">' +
                        '<ss:NumberFormat ss:Format="0.00" />' +
                    '</ss:Style>' +
                    '<ss:Style ss:ID="odd">' +
                        '<ss:Interior ss:Pattern="Solid" ss:Color="#FFFFFF" />' +
                    '</ss:Style>' +
                    '<ss:Style ss:Parent="odd" ss:ID="odddate">' +
            //'<ss:Font ss:Size="9" />' +
            //'<ss:NumberFormat ss:Format="Small Date;@" />' +
                        '<ss:NumberFormat ss:Format="[ENG][$-409]dd\-mmm\-yyyy;@" />' +
                    '</ss:Style>' +
                    '<ss:Style ss:Parent="odd" ss:ID="oddint">' +
                        '<ss:NumberFormat ss:Format="0" />' +
                    '</ss:Style>' +
                    '<ss:Style ss:Parent="odd" ss:ID="oddfloat">' +
                        '<ss:NumberFormat ss:Format="0.00" />' +
                    '</ss:Style>' +
                '</ss:Styles>' +
                worksheet.xml +
                '</ss:Workbook>';
        },
    
    
        createWorksheet: function (includeHidden) {
            //      Calculate cell data types and extra class names which affect formatting
            var cellType = [];
            var cellTypeClass = [];
            var cm = this.columns;
            var totalWidthInPixels = 0;
            var colXml = '';
            var headerXml = '';
            for (var i = 0; i < cm.length; i++) {
    			var temp = cm[i].text;
                if (temp != "Temp Assign" && (includeHidden || !cm[i].isHidden())) {
                    var w = cm[i].getWidth()
                    totalWidthInPixels += w;
                    colXml += '<ss:Column ss:AutoFitWidth="1" ss:Width="' + w + '" />';
                    headerXml += '<ss:Cell ss:StyleID="headercell">' +
                        '<ss:Data ss:Type="String">' + cm[i].text + '</ss:Data>' +
                        '<ss:NamedCell ss:Name="Print_Titles" /></ss:Cell>';
                    var fld = this.store.model.prototype.fields.get(cm[i].dataIndex);
                    switch (fld.type.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");
                            cellType.push("String");
                            cellTypeClass.push("");
                            break;
                        default:
                            cellType.push("String");
                            cellTypeClass.push("");
                            break;
                    }
                }
            }
            var visibleColumnCount = cellType.length;
    
    
            var result = {
                height: 9000,
                width: Math.floor(totalWidthInPixels * 30) + 50
            };
    
    
            //      Generate worksheet header details.
            var t = '<ss:Worksheet ss:Name="Polycomm Array"><ss:Names><ss:NamedRange ss:Name="Print_Titles" ss:RefersTo="=\'Polycomm Array\'!R1:R2" /></ss:Names><ss:Table x:FullRows="1" x:FullColumns="1" ss:ExpandedColumnCount="' + visibleColumnCount + '" ss:ExpandedRowCount="' + (this.store.getCount() + 2) + '">' + colXml +
            //'<ss:Row ss:Height="38"><ss:Cell ss:StyleID="title" ss:MergeAcross="' + (visibleColumnCount - 1) + '"><ss:Data xmlns:html="http://www.w3.org/TR/REC-html40" ss:Type="String"><html:B><html:U><html:Font html:Size="15">Polycomm Array</html:Font></html:U></html:B></ss:Data><ss:NamedCell ss:Name="Print_Titles" /></ss:Cell></ss:Row>
            '<ss:Row ss:AutoFitHeight="1">' + headerXml + '</ss:Row>';
    
    
            //      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 += '<ss:Row>';
                var cellClass = (i & 1) ? 'odd' : 'even';
                r = it[i].data;
                var k = 0;
                for (var j = 0; j < cm.length; j++) {
    				if (!cm[j].text.match(/Temp Assign/i) && (includeHidden || !cm[j].isHidden())) {
                        var v = r[cm[j].dataIndex];
                        t += '<ss:Cell ss:StyleID="' + cellClass + cellTypeClass[k] + '"><ss:Data ss:Type="' + cellType[k] + '">';
                        if (cellTypeClass[k] == 'date') {
                            t += Ext.Date.format(v, 'm/d/Y');
                        } else {
                            t += v;
                        }
                        t += '</ss:Data></ss:Cell>';
                        k++;
                    }
                }
                t += '</ss:Row>';
            }
    
    
            result.xml = t + '</ss:Table>' +
                '<x:WorksheetOptions>' +
                    '<x:PageSetup>' +
                        '<x:Layout x:CenterHorizontal="1" x:Orientation="Landscape" />' +
                        '<x:Footer x:Data="Page &amp;P of &amp;N" x:Margin="0.5" />' +
                        '<x:PageMargins x:Top="0.5" x:Right="0.5" x:Left="0.5" x:Bottom="0.8" />' +
                    '</x:PageSetup>' +
                    '<x:FitToPage />' +
                    '<x:Print>' +
                        '<x:PrintErrors>Blank</x:PrintErrors>' +
                        '<x:FitWidth>1</x:FitWidth>' +
                        '<x:FitHeight>32767</x:FitHeight>' +
                        '<x:ValidPrinterInfo />' +
                        '<x:VerticalResolution>600</x:VerticalResolution>' +
                    '</x:Print>' +
                    '<x:Selected />' +
                    '<x:DoNotDisplayGridlines />' +
                    '<x:ProtectObjects>False</x:ProtectObjects>' +
                    '<x:ProtectScenarios>False</x:ProtectScenarios>' +
                '</x:WorksheetOptions>' +
            '</ss:Worksheet>';
            return result;
        }
    })

  10. #290
    Sencha Premium Member
    Join Date
    Nov 2013
    Posts
    35
    Vote Rating
    1
    tdobberphul is on a distinguished road

      0  

    Default adaptions for a treepanel

    adaptions for a treepanel


    Hi all,

    great work.
    I need this code for a treestore.
    Of course I face the problem, that a tree store has another structure.
    So in the code the problem occurs in the first line here:
    Code:
     //      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 += '<ss:Row>';
                var cellClass = (i & 1) ? 'odd' : 'even';
                r = it[i].data;
                var k = 0;
                for (var j = 0; j < cm.length; j++) {
                    if (includeHidden || !cm[j].isHidden()) {
                        var v = r[cm[j].dataIndex];
                        t += '<ss:Cell ss:StyleID="' + cellClass + cellTypeClass[k] + '"><ss:Data ss:Type="' + cellType[k] + '">';
                        if (cellType[k] == 'DateTime') {
                            t += v.format('Y-m-d');
                        } else {
                            t += v;
                        }
                        t += '</ss:Data></ss:Cell>';
                        k++;
                    }
                }
                t += '</ss:Row>';
            }
    Of course there is no data.items in this.store .
    I would need a function, that flattens() the treestore nodes to fit in this code,
    Does someone has an idea?

    Best regards,
    Tobias

Thread Participants: 142

  1. alexb (1 Post)
  2. galdaka (3 Posts)
  3. ghyster (1 Post)
  4. Condor (1 Post)
  5. TopKatz (1 Post)
  6. Jul (1 Post)
  7. mystix (3 Posts)
  8. Foggy (2 Posts)
  9. bloon (1 Post)
  10. fshort (1 Post)
  11. antimatter15 (1 Post)
  12. cocinerox (1 Post)
  13. tobiu (1 Post)
  14. ajaxE (2 Posts)
  15. andreyz (1 Post)
  16. devnull (4 Posts)
  17. mjlecomte (4 Posts)
  18. kevinwu8 (1 Post)
  19. rakesh (1 Post)
  20. hendricd (2 Posts)
  21. gelleneu (1 Post)
  22. isaac (1 Post)
  23. huling (1 Post)
  24. ckr (1 Post)
  25. roopa (1 Post)
  26. loveyeah (1 Post)
  27. zhfxu_cs (1 Post)
  28. Mthor (1 Post)
  29. johnlicy (1 Post)
  30. thoreking (2 Posts)
  31. csqjean (1 Post)
  32. mankz (1 Post)
  33. vietschv (1 Post)
  34. verbi (1 Post)
  35. sanjshah (2 Posts)
  36. mask_hot (1 Post)
  37. calavera (1 Post)
  38. vsmike (1 Post)
  39. vizcano (1 Post)
  40. lakehouse (1 Post)
  41. alaska (2 Posts)
  42. radtad (3 Posts)
  43. jmcneese (1 Post)
  44. Rafael (4 Posts)
  45. calvouze (1 Post)
  46. Joyfulbob (1 Post)
  47. angeldimitrov (2 Posts)
  48. Jack_S (2 Posts)
  49. g13013 (2 Posts)
  50. DamienValentine (5 Posts)
  51. Marcelo Delgado (1 Post)
  52. NoahK17 (8 Posts)
  53. anujg (1 Post)
  54. aproust94 (3 Posts)
  55. pokerking400 (1 Post)
  56. Ronhead (1 Post)
  57. krzak (3 Posts)
  58. cvieira (1 Post)
  59. Efex (5 Posts)
  60. gthe (2 Posts)
  61. miihiir (2 Posts)
  62. pops (1 Post)
  63. emredagli (12 Posts)
  64. larsa (2 Posts)
  65. maggiesnyder (3 Posts)
  66. sam.zhang (1 Post)
  67. kristalgic (1 Post)
  68. wiznia (3 Posts)
  69. frpucci (1 Post)
  70. tinakonda (1 Post)
  71. tBSTAR (1 Post)
  72. tdg2008 (1 Post)
  73. iwagon (1 Post)
  74. psarunkumar (1 Post)
  75. kimmking (1 Post)
  76. sKuD24 (1 Post)
  77. MartinL (2 Posts)
  78. BitPoet (2 Posts)
  79. snoir (3 Posts)
  80. naranda (1 Post)
  81. zhw511006 (1 Post)
  82. bobjbain (1 Post)
  83. korto (1 Post)
  84. wp.joju (1 Post)
  85. hastiok (6 Posts)
  86. dtondo (4 Posts)
  87. Dumas (1 Post)
  88. vvreddy@12 (4 Posts)
  89. TheBim (1 Post)
  90. whodat (2 Posts)
  91. Skunkerbr (2 Posts)
  92. stevenhzj (4 Posts)
  93. Naokai (1 Post)
  94. wayned@escc (2 Posts)
  95. MuadDib-DK (1 Post)
  96. jsundquist (1 Post)
  97. hardc0re (2 Posts)
  98. hzwei (4 Posts)
  99. Galileo_Galilei (1 Post)
  100. SantaBarbarian (4 Posts)
  101. stevieke (1 Post)
  102. megmefleg (1 Post)
  103. Boccara Jonathan (1 Post)
  104. Surinder singh (2 Posts)
  105. KrullWarKing (1 Post)
  106. brandnewdrew (1 Post)
  107. MrRoyce (1 Post)
  108. Spongerusher (1 Post)
  109. teraphy (2 Posts)
  110. Pekka Karalahti (3 Posts)
  111. Sergii (1 Post)
  112. Cassio11 (1 Post)
  113. myth (1 Post)
  114. spor (6 Posts)
  115. StagnantIce (2 Posts)
  116. shailendra (2 Posts)
  117. ext_user1 (1 Post)
  118. kenny_mk (1 Post)
  119. mpacheco (1 Post)
  120. Centurus (1 Post)
  121. andyfly (1 Post)
  122. darmandovargas@gmail.com (1 Post)
  123. zjuthhy (3 Posts)
  124. robshim (3 Posts)
  125. Tod (1 Post)
  126. bipen (2 Posts)
  127. alisyah (1 Post)
  128. Monz87 (1 Post)
  129. alssst (1 Post)
  130. aggie (1 Post)
  131. bradelsky (1 Post)
  132. lagrezd (1 Post)
  133. ma1986 (1 Post)
  134. Sabareesh (2 Posts)
  135. top_1 (1 Post)
  136. Vasanthoo7 (1 Post)
  137. garrek99 (2 Posts)
  138. opik (1 Post)
  139. zaburo (1 Post)
  140. shankar8rajah1 (1 Post)
  141. tdobberphul (4 Posts)
  142. jramis (1 Post)