PDA

View Full Version : Export GridPanel directly into Excel.



Animal
25 Jun 2010, 2:26 AM
Updated to work with Ext 3.2.

This needs a browser that supports data URLs. FF, Opera and IE8 will support this.

In the example, the URL of a LinkButton is dynamically set to be a generated data URL containing an XML formated Excel spreadsheet in Excel Office 2002 format according to http://msdn.microsoft.com/en-us/library/aa140066%28office.10%29.aspx

Drop this HTML page into examples/<anywhere>



<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Grid export to Excel Example</title>

<!-- ** CSS ** -->
<!-- base library -->
<link rel="stylesheet" type="text/css" href="../../resources/css/ext-all.css" />
<link rel="stylesheet" type="text/css" href="../ux/css/RowEditor.css" />

<!-- overrides to base library -->

<!-- page specific -->
<link rel="stylesheet" type="text/css" href="../shared/examples.css" />
<link rel="stylesheet" type="text/css" href="grid-examples.css" />

<style type=text/css>
/* style rows on mouseover */
.x-grid3-row-over .x-grid3-cell-inner {
font-weight: bold;
}
.x-grid3-cell-inner table {
table-layout: auto!important;
}

/* Style LinkButtons */
table.x-btn .x-btn-small td.x-btn-mc em a {
color: inherit;
text-decoration: none;
display: block;
height: 12px;
padding-bottom: 2px;
padding-top: 2px;
}

body.ext-opera table.x-btn .x-btn-small td.x-btn-mc em a,
body.ext-chrome table.x-btn .x-btn-small td.x-btn-mc em a {
padding-top: 1px;
}

body.ext-chrome table.x-btn .x-btn-small td.x-btn-mc em a {
padding-bottom: 3px;
}
</style>

<!-- ** Javascript ** -->
<!-- ExtJS library: base/adapter -->
<script type="text/javascript" src="../../adapter/ext/ext-base.js"></script>

<!-- ExtJS library: all widgets -->
<script type="text/javascript" src="../../ext-all.js"></script>
<script type="text/javascript" src="../ux/RowEditor.js"></script>

<!-- page specific -->
<script type="text/javascript">
/**
* @class Ext.LinkButton
* @extends Ext.Button
* A Button which encapsulates an &lt;a> element to enable navigation, or downloading of files.
* @constructor
* Creates a new LinkButton
*/
Ext.LinkButton = Ext.extend(Ext.Button, {
template: new Ext.Template(
'<table id="{4}" cellspacing="0" class="x-btn {3}">',
'<tbody class="{1}">',
'<tr><td class="x-btn-tl"><i> </i></td><td class="x-btn-tc"></td><td class="x-btn-tr"><i> </i></td></tr>',
'<tr>',
'<td class="x-btn-ml"><i> </i></td>',
'<td class="x-btn-mc">',
'<em class="{2}" unselectable="on">',
'<a href="{5}" style="display:block" target="{6}" class="x-btn-text">{0}</a>',
'</em>',
'</td>',
'<td class="x-btn-mr"><i> </i></td>',
'</tr>',
'<tr><td class="x-btn-bl"><i> </i></td><td class="x-btn-bc"></td><td class="x-btn-br"><i> </i></td></tr>',
'</tbody>',
'</table>').compile(),

buttonSelector : 'a:first',

/**
* @cfg String href
* The URL to create a link for.
*/
/**
* @cfg String target
* The target for the &lt;a> element.
*/
/**
* @cfg Object
* A set of parameters which are always passed to the URL specified in the href
*/
baseParams: {},

// private
params: {},

getTemplateArgs: function() {
return Ext.Button.prototype.getTemplateArgs.apply(this).concat([this.getHref(), this.target]);
},

onClick : function(e){
if(e.button != 0){
return;
}
if(this.disabled){
e.stopEvent();
} else {
if (this.fireEvent("click", this, e) !== false) {
if(this.handler){
this.handler.call(this.scope || this, this, e);
}
}
}
},

setHref: function(href) {
this.href = href
if (this.rendered) {
this.el.child(this.buttonSelector, true).href = this.href;
}
},

// private
getHref: function() {
var result = this.href;
var p = Ext.urlEncode(Ext.apply(Ext.apply({}, this.baseParams), this.params));
if (p.length) {
result += ((this.href.indexOf('?') == -1) ? '?' : '&') + p;
}
return result;
},

/**
* Sets the href of the link dynamically according to the params passed, and any {@link #baseParams} configured.
* @param {Object} Parameters to use in the href URL.
*/
setParams: function(p) {
this.params = p;
this.el.child(this.buttonSelector, true).href = this.getHref();
}
});
Ext.reg('linkbutton', Ext.LinkButton);

/**
*
* 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.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 = [],
cellTypeClass = [],
cm = this.getColumnModel(),
totalWidthInPixels = 0,
colXml = '',
headerXml = '',
v;
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 Ext.data.Types.INT:
cellType.push("Number");
cellTypeClass.push("int");
break;
case Ext.data.Types.FLOAT:
cellType.push("Number");
cellTypeClass.push("float");
break;
case Ext.data.Types.BOOLEAN:
cellType.push("String");
cellTypeClass.push("");
break;
case Ext.data.Types.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 (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)) {
t += '<ss:Cell ss:StyleID="' + cellClass + cellTypeClass[k] + '">';
if (v = r[cm.getDataIndex(j)]) {
t += '<ss:Data ss:Type="' + cellType[k] + '">';
if (cellType[k] == 'DateTime') {
t += v.format('Y-m-d');
} else {
t += v;
}
t +='</ss:Data>';
k++;
}
t +='</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;
}
});

Ext.onReady(function(){

// sample static data for the store
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']
];

/**
* Custom function used for column renderer
* @param {Object} val
*/
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;
}

/**
* Custom function used for column renderer
* @param {Object} val
*/
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.ArrayStore({
fields: [
{name: 'company'},
{name: 'price', type: 'float'},
{name: 'change', type: 'float'},
{name: 'pctChange', type: 'float'},
{name: 'lastChange', type: 'date', dateFormat: 'n/j h:ia'}
],
listeners: {
load: function() {
grid.getBottomToolbar().excelButton.setHref('data:application/vnd.ms-excel;base64,' + Base64.encode(grid.getExcelXml()));
}
}
});

// create the Grid
var grid = new Ext.grid.GridPanel({
title: 'Array 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,
bbar: new Ext.PagingToolbar({
store: store,
pageSize: 12,
items: [{
xtype: 'linkbutton',
ref: 'excelButton',
text: 'Excel'
}]
})
});

// manually load local data. This will cause the LinkButton's href to be updated
store.loadData(myData);

// render the grid to the specified div in the page
grid.render(document.body);
});
</script>
</head>
<body></body>
</html>

wm003
25 Jun 2010, 4:43 AM
Well done. Works like a charm :) Thanks, Animal.

steffen.rahn
27 Jun 2010, 1:15 AM
Great! Thanks!

But I think you forgott to change one counter var ...




t += '<ss:Cell ss:StyleID="' + cellClass + cellTypeClass[k] + '"><ss:Data ss:Type="' + cellType[k] + '">'; // celltype must be also k and not j

Animal
27 Jun 2010, 2:08 AM
Thanks! I just edited post #1.

steffen.rahn
29 Jun 2010, 3:15 AM
Hmm I can't access the data URI in IE8 64bit. And as far as I know for now, it can't be done in IE

http://msdn.microsoft.com/en-us/library/cc848897%28VS.85%29.aspx

Please tell me that I'm wrong ;)

Animal
29 Jun 2010, 10:07 AM
No, it appears IE8 64b can't handle it!

Who uses IE anyway? You'd have to be crazy.

steffen.rahn
29 Jun 2010, 10:19 PM
In my case 95% of the companies I work for :(

So I will continue without Client-Side xls generation :( It's not that big thing, just a nice to have. Maybe Microsoftt will loosen the restrictions in IE 10 ;)

dbrin
7 Jul 2010, 11:16 PM
Hello,
I ran into an issue with XL7 when some of the date fields in the store where empty. Seems XL does not like to have empty <data> elements.
So here is my proposed fix for this issue:


// Generate the data rows from the data in the Store
for (var icount = 0, it = this.store.data.items, l = it.length; icount < l; icount++) {
t += '<ss:Row>';
var cellClass = (icount & 1) ? 'odd' : 'even';
r = it[icount].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] + '">'; //note decoupled Cell and Data elements for better control

if (v){ //adding logic to verify non-empty value, otherwise SpreadsheetML freaks
t+='<ss:Data ss:Type="' + cellType[k] + '">';
if (cellType[k] == 'DateTime') {
t += v.format('Y-m-d');
} else {
t += v;
}
t +='</ss:Data>';
}
t+='</ss:Cell>';
k++;
}
}
t += '</ss:Row>';
}Also note slightly altered counter variable names- JSLint complained about "i" var already defined above.
Thanks,
Dmitry.

Animal
8 Jul 2010, 2:03 AM
Fix in post #1. Thanks.

dbrin
8 Jul 2010, 10:29 AM
I have not tested it this way but you are removing the ss:cell element if v is undefined. I was a bit more cautious and decoupled the line that was adding cell and data elements so that I could keep the cell but skip the ss:data element. Just a thought.

Animal
8 Jul 2010, 11:43 AM
you're right. it will need the cell

mpacheco
12 Jul 2010, 12:49 PM
Hi,

I'm trying to use this Extension but i get two errors:

1.-Line 188 there is a blank space and the Base64 function does not load.

2.-Ext.data.Types is undefined line 145

Any idea?

Thanks,

Martin

Animal
12 Jul 2010, 1:24 PM
Use Ext 3.2

mpacheco
14 Jul 2010, 5:37 AM
Hi Animal, thanks for the reply.

I was able to export the XML into an excel but I cannot change the part when the date is converted to this:

Mon Jun 28 2010 00:00:00 GMT-0300 (Argentina)

Is Ext doing the convertion or the ux?

Thanks again,

Martin.-

HandsOnUk
25 Aug 2010, 4:34 AM
Hi Animal,

Thanks your your posts.

I'm getting this error

String contains an invalid character" code: "5

on line

encode : (typeof btoa == 'func... btoa(input); } : function (input) {

The cause of the problem appears to be the unicode character ħ in the data.

Any ideas?

Thanks in advance
HO

ash.eldritch@gmail.com
1 Sep 2010, 6:10 PM
Thanks Animal, this is very cool stuff (even though I still can't get it to work... spits out an invalid worksheet)

This breaks if using an Ext.grid.CheckboxSelectionModel, here in createWorksheet():


var fld = this.store.recordType.prototype.fields.get(cm.getDataIndex(i));

...the reason being the checkbox column has no dataIndex. The fix is a trivial check:


for (var i = 0; i < cm.getColumnCount(); i++) {
if (includeHidden || !cm.isHidden(i) && !Ext.isEmpty(cm.getDataIndex(i))) {
var w = cm.getColumnWidth(i)

gksnnsnmz
29 Feb 2012, 3:33 AM
turkish characters please help :-/

luisgon1
7 Mar 2012, 8:17 AM
I need this for extjs 3.2 IE7 any threads on this?

lthomps4
3 Feb 2014, 3:02 PM
Hi there, Is this still the best way to export to Excel for ExtJS 3.4? Or is there something else out there that will work for IE8 and Chrome?