GridPanel directly to Excel.

[SantaBarbarian]

Strange. My copy of IE8 (running on vista) won't press the button. Neither will Chrome.

[spor]

I suggest that you paste your code here.

[SantaBarbarian]

Just checked it on IE8 and the button does get pressed downwards. The error message is "The data area passed to a system call is too small."

By the way, anyone managed to export a a grid that has checkboxes (CheckboxSelectionModel)?

I used Animal's code from post 235. The only change was to point the references to extjs-3.2.1 to where I have it installed.

[mpacheco]

Hi,

I'm trying to get this working and all I can get is Base64 is undefined

Any idea? The js with the function is already loaded

Thanks,

Martin.-

[vsmike]

This exporter is the most unique I have found on the net. I was able to plug it into my code in just 5 min thanks for the help. Great Job!

[brandnewdrew]

This is working now, but I had some problems with my automatic Row Numbering -- I had to define some data with the column like this, for it to work:

new Ext.grid.RowNumberer({dataIndex: 'nu'}),

[ext_user1]

Hi Animal,

How can we have an icon for the export link, instead of text?

[Centurus]

tinakonda,
I could not remember well,
Sorry for that,
I modified Mr. Animal's code. So I am sending the my code. But please it is modified version for my requirements. It will not work if you try to use directly.

Ext.override(Ext.grid.GridPanel, {
    getExcelXml: function(includeHidden, title) {
        var worksheet = this.createWorksheet(includeHidden, title);
        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>' + 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="groupheadercell">' +
                    '<ss:Font ss:Italic="1" ss:Bold="1" ss:Size="10" />' +
                    '<ss:Alignment ss:WrapText="1"/>' +
                    '<ss:Interior ss:Pattern="Solid" ss:Color="#33FF99" />' +
                '</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="#FFFFCC" />' +
                '</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:Parent="even" ss:ID="evenmoney">' +
                    '<ss:Alignment ss:Horizontal="Right" />' +
                '</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:Style ss:Parent="odd" ss:ID="oddmoney">' +
                    '<ss:Alignment ss:Horizontal="Right" />' +
                '</ss:Style>' +    
                '<ss:Style ss:ID="right">' +
                    '<ss:Alignment ss:Horizontal="Right" />' +
                '</ss:Style>' +    
                '<ss:Style ss:Parent="right" ss:ID="rightGroupSummary">' +  
                   '<ss:Interior ss:Pattern="Solid" ss:Color="#CCFFFF" />' +
                '</ss:Style>' +        
                '<ss:Style ss:Parent="right" ss:ID="rightTotalSummary">' + 
                   '<ss:Interior ss:Pattern="Solid" ss:Color="#ADD8E6" />' +
                '</ss:Style>' +                    
                '<ss:Style ss:ID="left">' +
                    '<ss:Alignment ss:Horizontal="Left" />' +
                '</ss:Style>' +    
                '<ss:Style ss:Parent="left" ss:ID="leftGroupSummary">' +
                   '<ss:Interior ss:Pattern="Solid" ss:Color="#CCFFFF" />' +
                '</ss:Style>' +        
                '<ss:Style ss:Parent="left" ss:ID="leftTotalSummary">' +
                   '<ss:Interior ss:Pattern="Solid" ss:Color="#ADD8E6" />' +
                '</ss:Style>' +                    
            '</ss:Styles>' +
            worksheet.xml +
            '</ss:Workbook>';
    },

    createWorksheet: function(includeHidden, title) {

//      Calculate cell data types and extra class names which affect formatting
        var cellType = [];
        var cellTypeClass = [];
        var cm = this.getColumnModel();
        var totalWidthInPixels = 0;
        var colXml = '';
        var headerXml = '';
        var visibleColumnCount = 0;
        var i;
        var columnID;
        for (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));
                columnID = cm.getColumnId(i);
                var fld = cm.getColumnById(columnID).alignType; 
                switch(fld) {
                    case "right":
                        cellType[i] ="String";
                        cellTypeClass[i] ="money";
                        break;
                    default:
                        cellType[i] ="String";
                        cellTypeClass[i] ="";
                        break;
                }
                visibleColumnCount ++;
            }
        } 
        

        var result = {
            height: 9000,
            width: Math.floor(totalWidthInPixels * 30) + 50
        };
//      Counting group filed:
        var groupFieldCount = 0;
        var groupField = this.store.groupField;
        var previousGroupFieldValue = '';        
        for (i = 0, it = this.store.data.items, l = it.length; i < l; i++) {
            r = it[i].data;
            if (previousGroupFieldValue != r[groupField])
            {
                groupFieldCount++;
                previousGroupFieldValue = r[groupField];
            }
        }
//      Generate worksheet header details.
        var t = '<ss:Worksheet ss:Name="' + title + '">' +
            '<ss:Names>' +
                '<ss:NamedRange ss:Name="Print_Titles" ss:RefersTo="=\'' + title + '\'!R1:R2" />' +
            '</ss:Names>' +
            '<ss:Table x:FullRows="1" x:FullColumns="1"' +
                ' ss:ExpandedColumnCount="' + visibleColumnCount +
                '" ss:ExpandedRowCount="' + (this.store.getCount() + 2 + 2 * groupFieldCount + 1) + '">' +
                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">' + 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

        var groupFieldIndex = cm.findColumnIndex(groupField);
        previousGroupFieldValue = '';
        var groupCount = 0;
        for (i = 0, it = this.store.data.items, l = it.length; i < l; i++) {
            
            var cellClass = (i & 1) ? 'odd' : 'even';
            r = it[i].data;
            
            if (previousGroupFieldValue != r[groupField] && previousGroupFieldValue !='')
            {
                t += '<ss:Row>';
                for (var j = 0; j < cm.getColumnCount(); j++) {
                    if (groupSummaryValues[j] != null)
                    {
                        columnID = cm.getColumnId(j);
                        var fld = cm.getColumnById(columnID).alignType;
                        t += '<ss:Cell'+ (fld == 'right' ? ' ss:StyleID="rightGroupSummary"' : ' ss:StyleID="leftGroupSummary"') +'>';
                        t += '<ss:Data ss:Type="String">';
                        t += groupSummaryValues[j][groupCount];
                        t +='</ss:Data></ss:Cell>';
                    }
                }
                t += '</ss:Row>';
                groupCount++;
            }
            
            if (previousGroupFieldValue != r[groupField])
            {
                var v = r[groupField];

                t += '<ss:Row>' +
                    '<ss:Cell ss:StyleID="groupheadercell" ss:MergeAcross="' + (visibleColumnCount - 1) + '">' +
                      '<ss:Data ss:Type="String">' +
                        groupValues[groupCount] +
                        '</ss:Data>' + '</ss:Cell>' + '</ss:Row>';
        
                previousGroupFieldValue = r[groupField];
            }
            

            t += '<ss:Row>';
            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[j] + '"><ss:Data ss:Type="String">';
                    t += rendererValues[j][i]; //cm.getRenderer(j)(v);
                    t +='</ss:Data></ss:Cell>';
                }
            }
            t += '</ss:Row>';
        }

        //   Son Group Summary
        t += '<ss:Row>';
        for (var j = 0; j < cm.getColumnCount(); j++) {
            if (groupSummaryValues[j] != null)
            {
                columnID = cm.getColumnId(j);
                var fld = cm.getColumnById(columnID).alignType;
                t += '<ss:Cell'+ (fld == 'right' ? ' ss:StyleID="rightGroupSummary"' : ' ss:StyleID="leftGroupSummary"') +'>';
                t += '<ss:Data ss:Type="String">';
                t += groupSummaryValues[j][groupCount];
                t +='</ss:Data></ss:Cell>';
            }
        }
        t += '</ss:Row>';
                
        //  Total Summary
        t += '<ss:Row>';
        for (var j = 0; j < cm.getColumnCount(); j++) {
            if (summaryTotalValues[j] != null)
            {
                columnID = cm.getColumnId(j);
                var fld = cm.getColumnById(columnID).alignType; 
                 
                t += '<ss:Cell'+ (fld == 'right' ? ' ss:StyleID="rightTotalSummary"' : ' ss:StyleID="leftTotalSummary"') +'>';
                t += '<ss:Data ss:Type="String">';
                t += summaryTotalValues[j];
                t +='</ss:Data></ss:Cell>';
            }
        }
        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;
    }
});

function post_to_url(path, params, method) {
    method = method || "post"; // Set method to post by default, if not specified.

    // The rest of this code assumes you are not using a library.
    // It can be made less wordy if you use one.
    var form = document.createElement("form");
    form.setAttribute("method", method);
    form.setAttribute("action", path);

    for(var i=0; i<params.length; i++) {
        var hiddenField = document.createElement("input");
        hiddenField.setAttribute("type", "hidden");
        hiddenField.setAttribute("name", params[i].name);
        hiddenField.setAttribute("value", params[i].value);

        form.appendChild(hiddenField);
    }

    document.body.appendChild(form);    // Not entirely sure if this is necessary
    form.submit();
}

var excelFileCounter = 1;

function exportExcell() {

    var dataURL = 'CommonPages/Export.aspx';

    params =[{
        name: 'Data',
        value: resultGrid.getExcelXml(false,'Result '+resultsToolClass.selectedToolID)
    },{
        name: 'FileName',
        value: 'WISECTS-Results-'+excelFileCounter+'.xls'
    },{
        name: 'ContentType',
        value: 'application/vnd.ms-excel'
    }];
    
    excelFileCounter++;
    
    post_to_url(dataURL, params, 'post');
}

Hello. It seems not all code posted here.
Cant find where rendererValues[j][i], groupSummaryValues, e.t.c are defined.
Could you post these definitions? Or may be full example....
Will be very grateful.

[andyfly]

Hi,thanks for your code ,it is very useful!But I got a problem that when I opened the created xls file ,there was a prompt frame which said like this : the format of file which you try to open is different to the format of the file extension specified.But I still can open it.
Now I want to read the data from the created xls file, I failed in useing jxl.Workbook to read the data from xls which was created by the code up there .
Do you know how to read data from the xls file created by ext ? Thanks a lot ! Sorry to bother!

[MartinL]

Strange. My copy of IE8 (running on vista) won't press the button. Neither will Chrome.

I have exactly the same problem FF 3.6 works IE 8 not. All I could found was this link

http://msdn.microsoft.com/en-us/libr...8VS.85%29.aspx

There they mention that data:application does not work or the other way they say what is working.