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.
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.
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
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;
}
});
15 comments:
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
Its not working. File is getting downloaded but ts not .xls or .xml ,its just return statement you have written.
It is not working.
Not working for me too.
Not worky ....
@Mandar - I didn't get you which return statement are you getting. Is it downloading to a file ? Give me more details.
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
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
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() + '';
}
}
it's working with static grid, but if dynamic grid doesn't working, is it just for static grid ?
it's working with static grid, but if dynamic grid doesn't working, is it just for static grid ?
it's not working......
it's really really not working....
grid.getExcelXml() gets the data but Base64.encode(grid.getExcelXMl()) is not writing the data to EXcel why?
I cn get the data from the grid.getExcelXMl() but the Base64.encode(data.getExcelXml())
Post a Comment