Ext JS Export Grid to Excel Example.

There was a forum Discussion that was running from long time. But it wasn't resolved yer. Here is the link to the Forum Post.
http://www.sencha.com/forum/showthread.php?136598

Ed spencer wrote one plugin but it took lot of time for me to get it working, at last I failed to use his plugin. His plugin can be downloaded from here.
https://github.com/edspencer/Ext.ux.Exporter

Above all I found a link which explained the same.
http://terryit.blogspot.in/2012/07/export-excel-from-extjs-grid.html.

After trying all of the above for more than a month at last I succeeded after doing some changes to the code.

Here is my version.

Usage Instructions :
1. Copy Exporter-all.js from this post and put it in js folder of your application.
2. Include this in your Home.html file or your Homepage html file.

<script src="js/Exporter-all.js" type="text/javascript"></script>

3. To use Export to Excel Plugin. Open the JS file where you want to use this and add the below snippet. Add this in GridPanel Docked Items.

 {

                      xtype:'button',

                      itemId: 'grid-excel-button',

                      iconCls : 'btn-report-excel',

                      //hidden : true,

                      text: 'Export to Excel',

                      handler: function(){

                          var vExportContent = app.yourGridPanel.getExcelXml();

                          document.location='data:application/vnd.ms-excel;base64,'
                                               + Base64.encode(vExportContent);

                      }


Exporter-all.js (Working Copy from my application)
The Code reads all the columns one by one and checks the datatype and converts the string to this datatype and forms xml . This XML is then opened using excel . We converted everything to String for easier conversion. We had a custom code in this snippet to convert the given date to Month



/**

*

*  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.Panel, {

    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="#FFFFFF" />' +

                '</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-1 ; i++) {

            if(cm[i].xtype == 'actioncolumn' || cm[i].text == "&#160;"){}

            else if (includeHidden || !cm[i].isHidden()) {

                var w = cm[i].width;

                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);

                if(fld!=undefined)

                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");

                        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-1 ; j++) {

                if (cm[j].xtype == 'actioncolumn' || cm[j].text == "&#160;") {}

                else 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 (v !=null) {

                        if (cm[j].dataIndex == "month" || cm[j].dataIndex == "expenseMonth") {

                            var monthNames = new Array('January','February','March','April','May',
'June','July','August','September','October','November','December');

                            t += monthNames[v.getMonth()] +'-'+v.getFullYear();

                        }else if (cm[j].xtype != undefined && cm[j].xtype.search('date') != -1) {

                            t += v.getMonth()+1 +'-'+v.getDate()+'-'+v.getFullYear();

                        } else {
                            t += v;
                        }
                    }else

                        t += ' ';

                    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;

    }

});







15 comments:

Mandar Dadpe said...

Its not working. I have followed all the instructions as you have mentioned. File is getting downloaded but it's not .xls, its just return statemnt you have written in code

Mandar Dadpe said...

Its not working. File is getting downloaded but ts not .xls or .xml ,its just return statement you have written.

Alex Reznitchenko said...

It is not working.

Alex Reznitchenko said...

Not working for me too.

Anonymous said...

Not worky ....

Venkat said...

@Mandar - I didn't get you which return statement are you getting. Is it downloading to a file ? Give me more details.

Gilmar Jorge said...

Good morning, I could not make it work in my application, when I click the button it does nothing, I realized that it does not enter into any of the functions contained in Exporter-all (getExcelXml, onClick, onRender, createWorksheet) it does not enter into any .
Can it be because I'm using Extjs 4.2 or also because in my application I create with Grids "Ext.define ()".

thank you

Gilmar Jorge said...

Good morning, I could not make it work in my application, when I click the button it does nothing, I realized that it does not enter into any of the functions contained in Exporter-all (getExcelXml, onClick, onRender, createWorksheet) it does not enter into any .
Can it be because I'm using Extjs 4.2 or also because in my application I create with Grids "Ext.define ()".

thank you

Anonymous said...

This worked great after a bit of cleanup. This is what I used to handle dates (esp. null dates):

if (Object.prototype.toString.call(it[i].data[cm[j].dataIndex]) === '[object Date]') {
if (v != null) {
var timeOffsetInHours = (new Date().getTimezoneOffset() / 60) * (-1);
v.setHours(v.getHours() + timeOffsetInHours);
t += '' + v.toISOString() + '';
}
}

Anonymous said...

it's working with static grid, but if dynamic grid doesn't working, is it just for static grid ?

Anonymous said...

it's working with static grid, but if dynamic grid doesn't working, is it just for static grid ?

Anonymous said...

it's not working......

Anonymous said...

it's really really not working....

Kalpana Srinivasamurthy said...

grid.getExcelXml() gets the data but Base64.encode(grid.getExcelXMl()) is not writing the data to EXcel why?

Kalpana Srinivasamurthy said...

I cn get the data from the grid.getExcelXMl() but the Base64.encode(data.getExcelXml())