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 == " "){}
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 == " ") {}
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 &P of &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;
}
});
