PDA

View Full Version : GridPanel directly to Excel.



Pages : [1] 2

Animal
14 Apr 2008, 6:53 AM
This needs a browser that supports data URLs. FF, Opera and IE8 will support this.

Tested on FF2 and Opera 9.

This example extracts the data of a GridPanel's Store, and uses the ColumnModel to construct an XML Excel document, converts it to Base64, and loads it into a data URL in a link.

You can click the link, and "download" the grid straight into Excel.

Obviously this only works on the data in the Store - if you are using server-side paging, then perform this processing on the server. For quick and dirty conversion of a small table to Excel, this might be useful.

If the data in the Store is volatile (It gets reloaded or edited), the data URL will have to be recalculated.

Drop this file as examples/grid/array-grid.js:



/**
*
* 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.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 = [];
var cellTypeClass = [];
var cm = this.getColumnModel();
var totalWidthInPixels = 0;
var colXml = '';
var headerXml = '';
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 "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>
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 (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.getColumnCount(); j++) {
if (includeHidden || !cm.isHidden(j)) {
var v = r[cm.getDataIndex(j)];
t += '<ss:Cell ss:StyleID="' + cellClass + cellTypeClass[k] + '"><ss:Data ss:Type="' + cellType[k] + '">';
if (cellType[k] == 'DateTime') {
t += v.format('Y-m-d');
} else {
t += v;
}
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;
}
});

Ext.onReady(function(){

Ext.state.Manager.setProvider(new Ext.state.CookieProvider());

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']
];

// example of custom renderer function
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;
}

// example of custom renderer function
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.SimpleStore({
fields: [
{name: 'company'},
{name: 'price', type: 'float'},
{name: 'change', type: 'float'},
{name: 'pctChange', type: 'float'},
{name: 'lastChange', type: 'date', dateFormat: 'n/j h:ia'}
]
});
store.loadData(myData);


var linkButton = new Ext.LinkButton({
id: 'grid-excel-button',
text: 'Excel'
});

// create the Grid
var grid = new Ext.grid.GridPanel({
id: 'static-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,
title:'Array Grid',
bbar: new Ext.Toolbar({
buttons: [linkButton]
})
});

grid.render('grid-example');
linkButton.getEl().child('a', true).href = 'data:application/vnd.ms-excel;base64,' +
Base64.encode(grid.getExcelXml());

grid.getSelectionModel().selectFirstRow();
});

hendricd
14 Apr 2008, 9:27 AM
Nice one Animal !

mjlecomte
14 Apr 2008, 10:18 AM
Doug: just noticed what looks to be writing on the wall in your avatar thingy, what's it say?

hendricd
14 Apr 2008, 10:26 AM
Says: " F&@* Pete, that you? " :))

galdaka
14 Apr 2008, 12:55 PM
Sorry but my example not work :-? (IE6 & FF2 => this.bottomToolbar.render is not a function)

Can not create the bottom bar

Thanks in advance,

Animal
14 Apr 2008, 1:03 PM
you got to use the new toolbar i posted elsewhere

galdaka
14 Apr 2008, 1:30 PM
you got to use the new toolbar i posted elsewhere

Is this? http://extjs.com/forum/showthread.php?p=122647#post122647

I have same problem that: http://extjs.com/forum/showthread.php?p=124319#post124319

Thanks in advance,

antimatter15
14 Apr 2008, 5:39 PM
I don't think this is relavent, but firefox has a native Base64 encoding function that I've tested to be over 168 times faster.

window.btoa("i'm base64 encoded!")

Animal
14 Apr 2008, 11:03 PM
AH, thanks for that! I'll do conditional method creation and update post 1.

I'll also use a LinkButton rather than a DomHelper object as the download link. Ext 2.0 Toolbars don't know about DomHelper objects.

Animal
14 Apr 2008, 11:44 PM
OK, the latest code in post 1 will work with Ext 2.0, and will use the native btoa() if available.

Foggy
15 Apr 2008, 1:27 AM
Really nice extension, thanks for that animal.
Maybe a better solution would be to send the Excel xml to a backend and create a file? In this case you are not rely on any browser stuff...

Animal
15 Apr 2008, 1:55 AM
Well, if there's going to be a backend involved, it's better that that should just produce the XML itself. It has all the data, so it should just be able to process it into a valid XML spreadsheet. In our app, we have an Excel export button, but it just requests a document to be generated from the server, it does not send any info up.

Foggy
15 Apr 2008, 1:57 AM
it's better that that should just produce the XML itself. It has all the data, so it should just be able to process it into a valid XML spreadsheet.
Of course, you are right :)

galdaka
15 Apr 2008, 6:26 AM
Works fine!!

Good work!!

ghyster
16 Apr 2008, 5:01 AM
I found some bug with a grid containing hidden columns, there is a mismatch between excel cells type and values when you have different types of data

when retrieving the cell types I added a specific var for incrementation:



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] + '"><ss:Data ss:Type="' + cellType[k] + '">';
if (cellType[k] == 'DateTime') {
t += v.format('Y-m-d');
} else {
t += v;
}
t +='</ss:Data></ss:Cell>';
k++;
}
}


It works for me with this update

One more thing : why don't you use a basic toolbar button for exporting, which will automatically refresh the data url like this :



bbar: new Ext.Toolbar({
buttons: [{
id: 'grid-excel-button',
text: 'Excel',
handler: function(){
document.location='data:application/vnd.ms-excel;base64,' +Base64.encode(grid.getExcelXml());
}
}]
})

gelleneu
16 Apr 2008, 6:55 AM
And it will only works on ie8+ ? @Galdaka: you tested it on IE6?

Animal
16 Apr 2008, 11:18 AM
I found some bug with a grid containing hidden columns, there is a mismatch between excel cells type and values when you have different types of data

when retrieving the cell types I added a specific var for incrementation:



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] + '"><ss:Data ss:Type="' + cellType[k] + '">';
if (cellType[k] == 'DateTime') {
t += v.format('Y-m-d');
} else {
t += v;
}
t +='</ss:Data></ss:Cell>';
k++;
}
}


It works for me with this update

One more thing : why don't you use a basic toolbar button for exporting, which will automatically refresh the data url like this :



bbar: new Ext.Toolbar({
buttons: [{
id: 'grid-excel-button',
text: 'Excel',
handler: function(){
document.location='data:application/vnd.ms-excel;base64,' +Base64.encode(grid.getExcelXml());
}
}]
})


OK, thanks for this info. Can you bump this thread tomorrow, and I'll try ity out with hidden columns and fix it.

Yes, I was thinking of different ways to make the download smoother, if that works in all cases, I'll change the demo to use that.

anujg
20 Apr 2008, 12:51 PM
Great plugin Animal, I have a server side paging in my grid and I want to be able to export to excel , doing it on the server side is fine with me, but how do I bring up a file download dialog box once the data is back from the server? I am quite new to Ext and this simple thing is taking me a long time...

Also, in my toolbar I have a button "export to Excel" , and I want it to export only the data on the current page, not the whole store data, so how do I pass along the current data to the server side to generate a XML? any ideas ?

Animal
20 Apr 2008, 12:57 PM
If you just want the current page, then you can do it on the client.

If you are doing it on the server, that's totally up to you.

sanjshah
20 Apr 2008, 2:59 PM
Hi,

I'm testing this but getting an error message:


unterminated string constant

Any help? please!

mjlecomte
20 Apr 2008, 3:50 PM
Hi,

I'm testing this but getting an error message:


unterminated string constant

Any help? please!
Surely you can provide more details than that, firebug will report more information than that if the error is coming from firebug.

Animal
21 Apr 2008, 12:09 AM
Hi,

I'm testing this but getting an error message:


unterminated string constant

Any help? please!

It's creating quoted strings. There's probably some data in there which contains quotes. Break on that error in Firebug and fix it.

kevinwu8
21 Apr 2008, 3:11 AM
Dear Animal...

I create a grid via Ext.extend ,and use Ext.reg to register a xtype for this grid...
This extend grid have all of add,edit,delete function...
Now i wish can add export excel function to this extend grid...
Can you provide a example that integrate export grid to excel function...

Thx..

Animal
21 Apr 2008, 3:23 AM
Dear Animal...

I create a grid via Ext.extend ,and use Ext.reg to register a xtype for this grid...
This extend grid have all of add,edit,delete function...
Now i wish can add export excel function to this extend grid...
Can you provide a example that integrate export grid to excel function...

Thx..

http://extjs.com/forum/showthread.php?p=152522#post152522

sanjshah
21 Apr 2008, 6:38 AM
Thanks Animal,

Found the error - it was with copying and pasting the code - seems a line is not wrapped correctly, any all OK now thanks again!


It's creating quoted strings. There's probably some data in there which contains quotes. Break on that error in Firebug and fix it.

DamienValentine
30 Apr 2008, 3:25 AM
In the "//Calculate cell data types and extra class names which affect formatting" section you have to change i value in a loop like this:

for (var i = 1; i < cm.getColumnCount(); i++) {
:to avoid fld has no properties error. If you start with i=0 then your RowNumberer will be recognized as field.
There is other way to prevent this, but all my grids have such a field so I decided to change the i initial value.

NoahK17
1 May 2008, 12:27 PM
Alright, I've successfully implimented this mod. Here are the easy-mode directions for new users to ExtJS.

Create a new file, save it as ExportGridToExcel.js with this content:


/**
* allows for downloading of grid data (store) directly into excel
* Method: extracts data of gridPanel store, uses columnModel to construct XML excel document,
* converts to Base64, then loads everything into a data URL link.
*
* @author Animal <extjs support team>
*
*/

/**
* base64 encode / decode
*
* @location 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 = [];
var cellTypeClass = [];
var cm = this.getColumnModel();
var totalWidthInPixels = 0;
var colXml = '';
var headerXml = '';
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 "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>Generated by ExtJS</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.getColumnCount(); j++) {
if (includeHidden || !cm.isHidden(j)) {
var v = r[cm.getDataIndex(j)];
t += '<ss:Cell ss:StyleID="' + cellClass + cellTypeClass[k] + '"><ss:Data ss:Type="' + cellType[j] + '">';
if (cellType[k] == 'DateTime') {
t += v.format('Y-m-d');
} else {
t += v;
}
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;
}
});


Now, call this newly created JS file in the header of your application (php/html/etc), after you call your standard ExtJS files. You'll have to change your directory structure to however you have things set-up.


<script type="text/javascript" src="libs/js/ExportGridToExcel.js"></script>


Finally, we want to add the "Export to Excel" button using the bottom-bar of your grid. So in the config options, add this:



bbar: new Ext.Toolbar({
buttons: [{
id: 'grid-excel-button',
text: 'Export to Excel...',
handler: function(){
document.location='data:application/vnd.ms-excel;base64,' + Base64.encode(grid.getExcelXml());
}
}]
})


Now when you load your grid, at the bottom you should see a new button. Clicking that button will open the SAVE AS dialog box with the entire content of your grid available in the Excel format.

Cheers!

mjlecomte
1 May 2008, 12:55 PM
Tagging for later use. Thanks Animal!
use Thread Tools->subscribe

NoahK17
2 May 2008, 10:16 AM
So in order for this to print out just what you see on the screen (after column resorting/hiding/editing of values), you would need to call the Base64 function, and overwrite the button's A HREF tag after each column change?

Animal
2 May 2008, 10:37 AM
Yes, the URL referenced by the href actually is the spreadsheet, so it should be recalculated on every change of the Store.

Animal
2 May 2008, 10:39 AM
Actually, the way you changed it, you don't need to do anything else because your click handler recalculates and sets the document.location to the recalculated data URL, so you're OK.

NoahK17
2 May 2008, 11:04 AM
That's what I originally thought too Animal, but when you try to export to Excel, you get an XML error upon opening the file:




XML ERROR in Table
REASON: Bad Value
FILE: C:\DOCUME~1\NKRONE~1\LOCALS~1\Temp\ze37lz52.xls.xls
GROUP: Cell
TAG: Data
VALUE: 0.03

XML ERROR in Table
REASON: Bad Value
FILE: C:\DOCUME~1\NKRONE~1\LOCALS~1\Temp\ze37lz52.xls.xls
GROUP: Cell
TAG: Data
ATTRIB: Type
VALUE: undefined

XML ERROR in Table
REASON: Bad Value
FILE: C:\DOCUME~1\NKRONE~1\LOCALS~1\Temp\ze37lz52.xls.xls
GROUP: Cell
TAG: Data
VALUE: 1.47

XML ERROR in Table
REASON: Bad Value
FILE: C:\DOCUME~1\NKRONE~1\LOCALS~1\Temp\ze37lz52.xls.xls
GROUP: Cell
TAG: Data
ATTRIB: Type
VALUE: undefined

XML ERROR in Table
REASON: Bad Value
FILE: C:\DOCUME~1\NKRONE~1\LOCALS~1\Temp\ze37lz52.xls.xls
GROUP: Cell
TAG: Data
VALUE: 0.34

XML ERROR in Table
REASON: Bad Value
FILE: C:\DOCUME~1\NKRONE~1\LOCALS~1\Temp\ze37lz52.xls.xls
GROUP: Cell
TAG: Data
ATTRIB: Type
VALUE: undefined

XML ERROR in Table
REASON: Bad Value
FILE: C:\DOCUME~1\NKRONE~1\LOCALS~1\Temp\ze37lz52.xls.xls
GROUP: Cell
TAG: Data
VALUE: 0.02

XML ERROR in Table
REASON: Bad Value
FILE: C:\DOCUME~1\NKRONE~1\LOCALS~1\Temp\ze37lz52.xls.xls
GROUP: Cell
TAG: Data
ATTRIB: Type
VALUE: undefined

XML ERROR in Table
REASON: Bad Value
FILE: C:\DOCUME~1\NKRONE~1\LOCALS~1\Temp\ze37lz52.xls.xls
GROUP: Cell
TAG: Data
VALUE: 0.49

XML ERROR in Table
REASON: Bad Value
FILE: C:\DOCUME~1\NKRONE~1\LOCALS~1\Temp\ze37lz52.xls.xls
GROUP: Cell
TAG: Data
ATTRIB: Type
VALUE: undefined

XML ERROR in Table
REASON: Bad Value
FILE: C:\DOCUME~1\NKRONE~1\LOCALS~1\Temp\ze37lz52.xls.xls
GROUP: Cell
TAG: Data
VALUE: -1.54

XML ERROR in Table
REASON: Bad Value
FILE: C:\DOCUME~1\NKRONE~1\LOCALS~1\Temp\ze37lz52.xls.xls
GROUP: Cell
TAG: Data
ATTRIB: Type
VALUE: undefined

XML ERROR in Table
REASON: Bad Value
FILE: C:\DOCUME~1\NKRONE~1\LOCALS~1\Temp\ze37lz52.xls.xls
GROUP: Cell
TAG: Data
VALUE: 0.71

XML ERROR in Table
REASON: Bad Value
FILE: C:\DOCUME~1\NKRONE~1\LOCALS~1\Temp\ze37lz52.xls.xls
GROUP: Cell
TAG: Data
ATTRIB: Type
VALUE: undefined

XML ERROR in Table
REASON: Bad Value
FILE: C:\DOCUME~1\NKRONE~1\LOCALS~1\Temp\ze37lz52.xls.xls
GROUP: Cell
TAG: Data
VALUE: 1.39

XML ERROR in Table
REASON: Bad Value
FILE: C:\DOCUME~1\NKRONE~1\LOCALS~1\Temp\ze37lz52.xls.xls
GROUP: Cell
TAG: Data
ATTRIB: Type
VALUE: undefined

XML ERROR in Table
REASON: Bad Value
FILE: C:\DOCUME~1\NKRONE~1\LOCALS~1\Temp\ze37lz52.xls.xls
GROUP: Cell
TAG: Data
VALUE: 0.04

XML ERROR in Table
REASON: Bad Value
FILE: C:\DOCUME~1\NKRONE~1\LOCALS~1\Temp\ze37lz52.xls.xls
GROUP: Cell
TAG: Data
ATTRIB: Type
VALUE: undefined

XML ERROR in Table
REASON: Bad Value
FILE: C:\DOCUME~1\NKRONE~1\LOCALS~1\Temp\ze37lz52.xls.xls
GROUP: Cell
TAG: Data
VALUE: 1.28

XML ERROR in Table
REASON: Bad Value
FILE: C:\DOCUME~1\NKRONE~1\LOCALS~1\Temp\ze37lz52.xls.xls
GROUP: Cell
TAG: Data
ATTRIB: Type
VALUE: undefined

XML ERROR in Table
REASON: Bad Value
FILE: C:\DOCUME~1\NKRONE~1\LOCALS~1\Temp\ze37lz52.xls.xls
GROUP: Cell
TAG: Data
VALUE: 0.34

XML ERROR in Table
REASON: Bad Value
FILE: C:\DOCUME~1\NKRONE~1\LOCALS~1\Temp\ze37lz52.xls.xls
GROUP: Cell
TAG: Data
ATTRIB: Type
VALUE: undefined

XML ERROR in Table
REASON: Bad Value
FILE: C:\DOCUME~1\NKRONE~1\LOCALS~1\Temp\ze37lz52.xls.xls
GROUP: Cell
TAG: Data
VALUE: -0.64

XML ERROR in Table
REASON: Bad Value
FILE: C:\DOCUME~1\NKRONE~1\LOCALS~1\Temp\ze37lz52.xls.xls
GROUP: Cell
TAG: Data
ATTRIB: Type
VALUE: undefined

XML ERROR in Table
REASON: Bad Value
FILE: C:\DOCUME~1\NKRONE~1\LOCALS~1\Temp\ze37lz52.xls.xls
GROUP: Cell
TAG: Data
VALUE: -0.23

XML ERROR in Table
REASON: Bad Value
FILE: C:\DOCUME~1\NKRONE~1\LOCALS~1\Temp\ze37lz52.xls.xls
GROUP: Cell
TAG: Data
ATTRIB: Type
VALUE: undefined

XML ERROR in Table
REASON: Bad Value
FILE: C:\DOCUME~1\NKRONE~1\LOCALS~1\Temp\ze37lz52.xls.xls
GROUP: Cell
TAG: Data
VALUE: 3.74

XML ERROR in Table
REASON: Bad Value
FILE: C:\DOCUME~1\NKRONE~1\LOCALS~1\Temp\ze37lz52.xls.xls
GROUP: Cell
TAG: Data
ATTRIB: Type
VALUE: undefined

XML ERROR in Table
REASON: Bad Value
FILE: C:\DOCUME~1\NKRONE~1\LOCALS~1\Temp\ze37lz52.xls.xls
GROUP: Cell
TAG: Data
VALUE: 0.03

XML ERROR in Table
REASON: Bad Value
FILE: C:\DOCUME~1\NKRONE~1\LOCALS~1\Temp\ze37lz52.xls.xls
GROUP: Cell
TAG: Data
ATTRIB: Type
VALUE: undefined

XML ERROR in Table
REASON: Bad Value
FILE: C:\DOCUME~1\NKRONE~1\LOCALS~1\Temp\ze37lz52.xls.xls
GROUP: Cell
TAG: Data
VALUE: -0.08

XML ERROR in Table
REASON: Bad Value
FILE: C:\DOCUME~1\NKRONE~1\LOCALS~1\Temp\ze37lz52.xls.xls
GROUP: Cell
TAG: Data
ATTRIB: Type
VALUE: undefined

XML ERROR in Table
REASON: Bad Value
FILE: C:\DOCUME~1\NKRONE~1\LOCALS~1\Temp\ze37lz52.xls.xls
GROUP: Cell
TAG: Data
VALUE: 0.13

XML ERROR in Table
REASON: Bad Value
FILE: C:\DOCUME~1\NKRONE~1\LOCALS~1\Temp\ze37lz52.xls.xls
GROUP: Cell
TAG: Data
ATTRIB: Type
VALUE: undefined

XML ERROR in Table
REASON: Bad Value
FILE: C:\DOCUME~1\NKRONE~1\LOCALS~1\Temp\ze37lz52.xls.xls
GROUP: Cell
TAG: Data
VALUE: 1.58

XML ERROR in Table
REASON: Bad Value
FILE: C:\DOCUME~1\NKRONE~1\LOCALS~1\Temp\ze37lz52.xls.xls
GROUP: Cell
TAG: Data
ATTRIB: Type
VALUE: undefined

XML ERROR in Table
REASON: Bad Value
FILE: C:\DOCUME~1\NKRONE~1\LOCALS~1\Temp\ze37lz52.xls.xls
GROUP: Cell
TAG: Data
VALUE: 0.54

XML ERROR in Table
REASON: Bad Value
FILE: C:\DOCUME~1\NKRONE~1\LOCALS~1\Temp\ze37lz52.xls.xls
GROUP: Cell
TAG: Data
ATTRIB: Type
VALUE: undefined

XML ERROR in Table
REASON: Bad Value
FILE: C:\DOCUME~1\NKRONE~1\LOCALS~1\Temp\ze37lz52.xls.xls
GROUP: Cell
TAG: Data
VALUE: 0.09

XML ERROR in Table
REASON: Bad Value
FILE: C:\DOCUME~1\NKRONE~1\LOCALS~1\Temp\ze37lz52.xls.xls
GROUP: Cell
TAG: Data
ATTRIB: Type
VALUE: undefined

XML ERROR in Table
REASON: Bad Value
FILE: C:\DOCUME~1\NKRONE~1\LOCALS~1\Temp\ze37lz52.xls.xls
GROUP: Cell
TAG: Data
VALUE: 0.15

XML ERROR in Table
REASON: Bad Value
FILE: C:\DOCUME~1\NKRONE~1\LOCALS~1\Temp\ze37lz52.xls.xls
GROUP: Cell
TAG: Data
ATTRIB: Type
VALUE: undefined

XML ERROR in Table
REASON: Bad Value
FILE: C:\DOCUME~1\NKRONE~1\LOCALS~1\Temp\ze37lz52.xls.xls
GROUP: Cell
TAG: Data
VALUE: 2.4

XML ERROR in Table
REASON: Bad Value
FILE: C:\DOCUME~1\NKRONE~1\LOCALS~1\Temp\ze37lz52.xls.xls
GROUP: Cell
TAG: Data
ATTRIB: Type
VALUE: undefined

XML ERROR in Table
REASON: Bad Value
FILE: C:\DOCUME~1\NKRONE~1\LOCALS~1\Temp\ze37lz52.xls.xls
GROUP: Cell
TAG: Data
VALUE: 1.01

XML ERROR in Table
REASON: Bad Value
FILE: C:\DOCUME~1\NKRONE~1\LOCALS~1\Temp\ze37lz52.xls.xls
GROUP: Cell
TAG: Data
ATTRIB: Type
VALUE: undefined

XML ERROR in Table
REASON: Bad Value
FILE: C:\DOCUME~1\NKRONE~1\LOCALS~1\Temp\ze37lz52.xls.xls
GROUP: Cell
TAG: Data
VALUE: 0.54

XML ERROR in Table
REASON: Bad Value
FILE: C:\DOCUME~1\NKRONE~1\LOCALS~1\Temp\ze37lz52.xls.xls
GROUP: Cell
TAG: Data
ATTRIB: Type
VALUE: undefined

XML ERROR in Table
REASON: Bad Value
FILE: C:\DOCUME~1\NKRONE~1\LOCALS~1\Temp\ze37lz52.xls.xls
GROUP: Cell
TAG: Data
VALUE: 1.45

XML ERROR in Table
REASON: Bad Value
FILE: C:\DOCUME~1\NKRONE~1\LOCALS~1\Temp\ze37lz52.xls.xls
GROUP: Cell
TAG: Data
ATTRIB: Type
VALUE: undefined

XML ERROR in Table
REASON: Bad Value
FILE: C:\DOCUME~1\NKRONE~1\LOCALS~1\Temp\ze37lz52.xls.xls
GROUP: Cell
TAG: Data
VALUE: 0.58

XML ERROR in Table
REASON: Bad Value
FILE: C:\DOCUME~1\NKRONE~1\LOCALS~1\Temp\ze37lz52.xls.xls
GROUP: Cell
TAG: Data
ATTRIB: Type
VALUE: undefined

XML ERROR in Table
REASON: Bad Value
FILE: C:\DOCUME~1\NKRONE~1\LOCALS~1\Temp\ze37lz52.xls.xls
GROUP: Cell
TAG: Data
VALUE: 1.02

XML ERROR in Table
REASON: Bad Value
FILE: C:\DOCUME~1\NKRONE~1\LOCALS~1\Temp\ze37lz52.xls.xls
GROUP: Cell
TAG: Data
ATTRIB: Type
VALUE: undefined

XML ERROR in Table
REASON: Bad Value
FILE: C:\DOCUME~1\NKRONE~1\LOCALS~1\Temp\ze37lz52.xls.xls
GROUP: Cell
TAG: Data
VALUE: 0.02

XML ERROR in Table
REASON: Bad Value
FILE: C:\DOCUME~1\NKRONE~1\LOCALS~1\Temp\ze37lz52.xls.xls
GROUP: Cell
TAG: Data
ATTRIB: Type
VALUE: undefined

XML ERROR in Table
REASON: Bad Value
FILE: C:\DOCUME~1\NKRONE~1\LOCALS~1\Temp\ze37lz52.xls.xls
GROUP: Cell
TAG: Data
VALUE: 2.38

XML ERROR in Table
REASON: Bad Value
FILE: C:\DOCUME~1\NKRONE~1\LOCALS~1\Temp\ze37lz52.xls.xls
GROUP: Cell
TAG: Data
ATTRIB: Type
VALUE: undefined

XML ERROR in Table
REASON: Bad Value
FILE: C:\DOCUME~1\NKRONE~1\LOCALS~1\Temp\ze37lz52.xls.xls
GROUP: Cell
TAG: Data
VALUE: 0.88

XML ERROR in Table
REASON: Bad Value
FILE: C:\DOCUME~1\NKRONE~1\LOCALS~1\Temp\ze37lz52.xls.xls
GROUP: Cell
TAG: Data
ATTRIB: Type
VALUE: undefined

XML ERROR in Table
REASON: Bad Value
FILE: C:\DOCUME~1\NKRONE~1\LOCALS~1\Temp\ze37lz52.xls.xls
GROUP: Cell
TAG: Data
VALUE: 1.11

XML ERROR in Table
REASON: Bad Value
FILE: C:\DOCUME~1\NKRONE~1\LOCALS~1\Temp\ze37lz52.xls.xls
GROUP: Cell
TAG: Data
ATTRIB: Type
VALUE: undefined

XML ERROR in Table
REASON: Bad Value
FILE: C:\DOCUME~1\NKRONE~1\LOCALS~1\Temp\ze37lz52.xls.xls
GROUP: Cell
TAG: Data
VALUE: 1.47

XML ERROR in Table
REASON: Bad Value
FILE: C:\DOCUME~1\NKRONE~1\LOCALS~1\Temp\ze37lz52.xls.xls
GROUP: Cell
TAG: Data
ATTRIB: Type
VALUE: undefined

XML ERROR in Table
REASON: Bad Value
FILE: C:\DOCUME~1\NKRONE~1\LOCALS~1\Temp\ze37lz52.xls.xls
GROUP: Cell
TAG: Data
VALUE: 1.63

XML ERROR in Table
REASON: Bad Value
FILE: C:\DOCUME~1\NKRONE~1\LOCALS~1\Temp\ze37lz52.xls.xls
GROUP: Cell
TAG: Data
ATTRIB: Type
VALUE: undefined

I'm not the best with XML, but I'll let you know if I figure out a work-around.

Animal
2 May 2008, 11:08 AM
Yes, Excel is very fussy about the XML format.

Try saving it as text, and editing it with LiquidXML. Those error messages might give you some clue as to what it doesn't like. It looks like it is getting the undefined value from Record.get(dataIndex) and writing that into the XML.

I don't know why it doesn't like the numbers. You'd have to edit the XML to take a look.

NoahK17
2 May 2008, 11:14 AM
I found the bug....

OPEN:
ExportGridToExcel.js

CHANGE: (around line 216)

t += '<ss:Cell ss:StyleID="' + cellClass + cellTypeClass[k] + '"><ss:Data ss:Type="' + cellType[j] + '">';


TO:

t += '<ss:Cell ss:StyleID="' + cellClass + cellTypeClass[k] + '"><ss:Data ss:Type="' + cellType[k] + '">';

It was mentioned on the first page. Now everything works perfectly. Thanks Animal!!

NoahK17
2 May 2008, 11:41 AM
Animal: An issue arises in your Plugin if there are any Grid Modifications, such as GridRowExpander, which adds a blank title column, and "expand/collaspe" icons as cells.

Anyhoo, here is an updated ExportGridToExcel.js, with my changes highlighted in Red.


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 = [];
var cellTypeClass = [];
var cm = this.getColumnModel();
var totalWidthInPixels = 0;
var colXml = '';
var headerXml = '';
var visibleColumnCountReduction = 0;
for (var i = 0; i < cm.getColumnCount(); i++) {
if (includeHidden || !cm.isHidden(i)) {
var w = cm.getColumnWidth(i)
totalWidthInPixels += w;
if (cm.getColumnHeader(i) === ""){
cellType.push("None");
cellTypeClass.push("");
++visibleColumnCountReduction;
}
else
{
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 "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 - visibleColumnCountReduction;

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>Generated by ExtJS</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.getColumnCount(); j++) {
if (includeHidden || !cm.isHidden(j)) {
var v = r[cm.getDataIndex(j)];
if (cellType[k] !== "None") {

t += '<ss:Cell ss:StyleID="' + cellClass + cellTypeClass[k] + '"><ss:Data ss:Type="' + cellType[k] + '">';
if (cellType[k] == 'DateTime') {
t += v.format('Y-m-d');
} else {
t += v;
}
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;
}
});

pokerking400
5 May 2008, 1:05 AM
How many rowexpander plugins out there?. I try to use saki rowexpander plugin and Rowaction plugin , i have hit the wall.

Row expander + sign shows up and does n't drop and action column is there and no icons shows up.

i have spent 5 hrs on it still could n't make it work..sucks(:|

oh well...

johnlicy
5 May 2008, 1:41 AM
Hi

In our application we also require to export the grid data to excel. I followed the same steps as mentioned here. I am getting script error 'Invalid Syntax'.

Please help me out with this issue.

Regards
Licy Ambrose


Alright, I've successfully implimented this mod. Here are the easy-mode directions for new users to ExtJS.

Create a new file, save it as ExportGridToExcel.js with this content:


/**
* allows for downloading of grid data (store) directly into excel
* Method: extracts data of gridPanel store, uses columnModel to construct XML excel document,
* converts to Base64, then loads everything into a data URL link.
*
* @author Animal <extjs support team>
*
*/

/**
* base64 encode / decode
*
* @location 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 = [];
var cellTypeClass = [];
var cm = this.getColumnModel();
var totalWidthInPixels = 0;
var colXml = '';
var headerXml = '';
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 "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>Generated by ExtJS</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.getColumnCount(); j++) {
if (includeHidden || !cm.isHidden(j)) {
var v = r[cm.getDataIndex(j)];
t += '<ss:Cell ss:StyleID="' + cellClass + cellTypeClass[k] + '"><ss:Data ss:Type="' + cellType[j] + '">';
if (cellType[k] == 'DateTime') {
t += v.format('Y-m-d');
} else {
t += v;
}
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;
}
});


Now, call this newly created JS file in the header of your application (php/html/etc), after you call your standard ExtJS files. You'll have to change your directory structure to however you have things set-up.


<script type="text/javascript" src="libs/js/ExportGridToExcel.js"></script>


Finally, we want to add the "Export to Excel" button using the bottom-bar of your grid. So in the config options, add this:



bbar: new Ext.Toolbar({
buttons: [{
id: 'grid-excel-button',
text: 'Export to Excel...',
handler: function(){
document.location='data:application/vnd.ms-excel;base64,' + Base64.encode(grid.getExcelXml());
}
}]
})


Now when you load your grid, at the bottom you should see a new button. Clicking that button will open the SAVE AS dialog box with the entire content of your grid available in the Excel format.

Cheers!

DamienValentine
5 May 2008, 3:10 AM
I guess you should check line breaks. Sometimes line is divided into two separate blocks and second block is placed as a new line.

radtad
7 May 2008, 5:01 PM
I seem to be getting a weird output in Excel using the example code. I copied the code completely and the attachment is what I get in Excel 2000.

Also, I've tried the same code on the Linux side using gnumeric and receive a "EntityRef: expectiing ';' " erorr.

Both are running on FireFox 2+.

DamienValentine
8 May 2008, 1:05 AM
This problem is definitely in your ExportToExcel.js
Try to replace your file with mine - my file works good for me except in certain grids which I do not code completely.

radtad
8 May 2008, 11:16 AM
This problem is definitely in your ExportToExcel.js
Try to replace your file with mine - my file works good for me except in certain grids which I do not code completely.

Well I tried your js file with the example array grid. While I receive no errors now in gnumeric on the Linux side, it shows up with XML tags in the columns (see below).

When I open this up in Excel 2000, it shows no data at all. If I open it up in notepad, I see all the tags which is good and the data, so I know the data is there. I attached a copy of this below.

cocinerox
8 May 2008, 12:02 PM
Hi, I think you need Excel 2007 to read XML:
http://en.wikipedia.org/wiki/Microsoft_Excel#File_formats

radtad
8 May 2008, 12:08 PM
Hi, I think you need Excel 2007 to read XML:
http://en.wikipedia.org/wiki/Microsoft_Excel#File_formats

Well that would explain it. :( I should have Wiki'd the format for Excel. Thanks for the response which looks to be the reason.

DamienValentine
9 May 2008, 11:12 AM
It's OK. I saw your notepad.txt. It's looking good. I attached screenshot to this letter. It's your report but with my remark in first row - you should remove appropriate line in ExportToExcel.js. Please use search with "SEBN UA" parameter.

Jul
9 May 2008, 11:35 AM
If you have Office 2000, Office XP or Office 2003, you can install the Compatibility Pack which will enable you to read and write the new Office 2007 XML based formats.

Download link is here: Compatibility Pack (http://www.microsoft.com/downloads/details.aspx?FamilyId=941B3470-3AE9-4AEE-8F43-C6BB74CD1466&displaylang=en)

DamienValentine
10 May 2008, 5:51 AM
If you have Office 2000, Office XP or Office 2003, you can install the Compatibility Pack which will enable you to read and write the new Office 2007 XML based formats.

Download link is here: Compatibility Pack (http://www.microsoft.com/downloads/details.aspx?FamilyId=941B3470-3AE9-4AEE-8F43-C6BB74CD1466&displaylang=en)

File exported with Animal concept will open in Office 2003, I guess. It's working for me without Compatibility Packs. Or someone installed that software and I don't know about that :)

andreyz
13 May 2008, 2:42 AM
When unloading datetime fields detected a problem. No time to file excel. To address this problem need to change a few lines:


'<ss:Style ss:Parent="odd" ss:ID="odddate">' +
'<ss:NumberFormat ss:Format="dd/mm/yy\ h:mm;@" />' +
'</ss:Style>' +

'<ss:Style ss:Parent="even" ss:ID="evenint">' +
'<ss:NumberFormat ss:Format="0" />' +
'</ss:Style>' +

if (cellType[k] == 'DateTime') {
t += v.format('Y-m-d\\TH:i: s.000'); // no space betwen i: s
} else {
t += v;
}

krzak
13 May 2008, 3:10 PM
One more thing : why don't you use a basic toolbar button for exporting, which will automatically refresh the data url like this :



bbar: new Ext.Toolbar({
buttons: [{
id: 'grid-excel-button',
text: 'Excel',
handler: function(){
document.location='data:application/vnd.ms-excel;base64,' +Base64.encode(grid.getExcelXml());
}
}]
})


I don't know why but seems this hung my IE7. The same code works fine with FF2.

calvouze
14 May 2008, 3:33 AM
Same here, hung up on IE7, perfect in FF.

Anyone knows why ?

Thanks in advance.


Edit: not possible :


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

Tested on FF2 and Opera 9.

mask_hot
23 May 2008, 1:38 AM
Hi Animal,

in my grid's ColumnModel I have a CheckBoxSelectionModel with no DataIndex.

How do I ignore this column in the export?

thx

larsa
18 Jun 2008, 4:39 AM
How do i set the filename to be used for saving?
/Lars

fshort
18 Jun 2008, 10:52 AM
in the response header, set:

Content-Disposition:attachment;filename=export_filename.xls

larsa
18 Jun 2008, 11:04 AM
But how do i set the response header in the client, with Javascript?
/Lars



bbar: new Ext.Toolbar({
buttons: [{
id: 'grid-excel-button',
text: 'Exportera till Excel...',
handler: function(){
document.location='data:application/vnd.ms-excel;base64,' + Base64.encode(Ext.getCmp("mygrid").getExcelXml());
}
}]
})

}

thoreking
19 Jun 2008, 1:36 AM
Hi,
nice addon indeed.
I have some date in my grid and each time a cell is null ( no date ), the code report an error in


if (cellType[k] == 'DateTime') {
t += v.format('d-m-Y');
because v does not exist.

any idea how I could fix it ?

Thanks very much

T

bloon
19 Jun 2008, 4:58 PM
I think should be add with Ext.isEmpty ?
like this


if (cellType[k] == 'DateTime') {
if (Ext.isEmpty(v)==false) {
t+=v.format('d-m-Y');
}

thoreking
20 Jun 2008, 12:56 AM
thank you.

I fixed the error with empty date cell by doing that :

if (cellType[k] !== "None") {
if (!v) {t += '<ss:Cell ss:StyleID="' + cellClass +'"></ss:Cell>';}

else {
t += '<ss:Cell ss:StyleID="' + cellClass + cellTypeClass[k] + '"><ss:Data ss:Type="' + cellType[k] + '">';
if (cellType[k] == 'DateTime') {
t += v.format('Y-m-d');
} else {
t += v;
}
t +='</ss:Data></ss:Cell>';
}
}

and everything is fine.

still no idea how to make it work on ie ?

miihiir
20 Jun 2008, 12:01 PM
Hi,

I am using this .. but getting the following error -

fld has no properties

switch(fld.type) {
on line 164 of ExportGridToExcel.js ...

Any idea?

I have a Grouping enabled grid that has first column as hidden.

Thanks.

Mihir

gthe
21 Jun 2008, 10:10 AM
Hi,

I am using this .. but getting the following error -

fld has no properties

switch(fld.type) {
on line 164 of ExportGridToExcel.js ...

Any idea?

I have a Grouping enabled grid that has first column as hidden.

Thanks.

Mihir

First, sorry for my English.
I have same error. That because I add RowAction in ColumnModel and it create field, that don't have cm.getDataIndex() property and it generate error.
To fix :
1. (about 148 row)

for (var i = 0; i < cm.getColumnCount(); i++) {
if ((includeHidden || !cm.isHidden(i)) && (cm.getDataIndex(i))) {
2. (about 204 row):
ss:ExpandedColumnCount="' + (visibleColumnCount +2) +
'" ss:ExpandedRowCount="' + (this.store.getCount() + 2) + '">' +

May be it is dirty, but work for me

aproust94
24 Jun 2008, 1:34 PM
Hi All,

Animal, Thanks for this great extension.

I get an issue with utf-8 encoding. If I have a word like this one 'Cr

mankz
26 Jun 2008, 3:42 AM
It's all in the first post: This needs a browser that supports data URLs. FF, Opera and IE8 will support this.

alaska
27 Jun 2008, 12:36 PM
Just a note! did not work with Ext.grid.RowNumberer() im my tests

Animal
27 Jun 2008, 12:49 PM
Feel free to make it work.

krzak
27 Jun 2008, 1:07 PM
Just a note! did not work with Ext.grid.RowNumberer() im my tests

hm... it work for me with RowNumberer()... something else might break you tests.

alaska
27 Jun 2008, 1:09 PM
im using NoahK17 version, ill try the original, and report

aproust94
28 Jun 2008, 5:33 AM
It's all in the first post: This needs a browser that supports data URLs. FF, Opera and IE8 will support this.

Thank you mankz. I wrote something wrong about ie7. I know that's not work. But in all case the utf-8 issue is not due to the kind of browser. Maybe the encoding. I'd try many things, but nothing works.

If you got an idea ?

Al

aproust94
28 Jun 2008, 7:36 AM
Well, after reading many threads about downloading and more, I found a solution bases on this thread and jrh's message (#34) [URL="http://extjs.com/forum/showthread.php?t=23937&page=4"].
First of all, for utf8 issue, I don't use Base64 encoding. Except for data url.
So to get more compatibility with major browsers, I use this following code when I create a button (top toolbar). If the browser support data url, I use this way else I create a downloader :


id: 'grid-excel-button',
iconCls: 'icon-excel',
text: EXPORT_EXCEL_BUTTON,
handler: function(){
var exportContent = IssueListingEditorGrid.getExcelXml();
if (!Ext.isIE6 || !Ext.isIE7 || !Ext.isSafari || !Ext.isSafari2 || !Ext.isSafari3) {
document.location='data:application/vnd.ms-excel;base64,' + Base64.encode(exportContent);
} else {
if (!Ext.fly('frmDummy')) {
var frm = document.createElement('form');
frm.id = 'frmDummy';
frm.name = id;
frm.className = 'x-hidden';
document.body.appendChild(frm);
}
Ext.Ajax.request({
url: 'export/excel.php',
method : 'POST',
form: Ext.fly('frmDummy'),
callback: function(o, s, r){
alert(r.responseText);
},
isUpload: true,
params: { ex: exportContent }
});
}
}


Your PHP code should be like this one:



header("Pragma: public");
header("Expires: 0"); // set expiration time
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Content-Type: application/force-download");
header('Content-type: application/vnd.ms-excel');
header("Content-Disposition:attachment;filename=export");
echo $_REQUEST['ex'];


Hope this helps someone

Al

ckr
14 Jul 2008, 5:05 AM
Great work Animal! Works like a charm. =D>


For others using - FYI
One item I ran into was the length of the title of my grid. The grid title is used as the worksheet title, and it seems that Excel will only let you have 31 characters. You will get a warning when opening the data link saying that a string is too long. Excel will truncate the title, but the error message might be a bit bothersome. So anyone having a long grid title will need adjust accordingly. It is easily fixed.

miihiir
15 Jul 2008, 10:33 AM
Well, after reading many threads about downloading and more, I found a solution bases on this thread and jrh's message (#34) [url="http://extjs.com/forum/showthread.php?t=23937&page=4"].
First of all, for utf8 issue, I don't use Base64 encoding. Except for data url.
So to get more compatibility with major browsers, I use this following code when I create a button (top toolbar). If the browser support data url, I use this way else I create a downloader :


id: 'grid-excel-button',
iconCls: 'icon-excel',
text: EXPORT_EXCEL_BUTTON,
handler: function(){
var exportContent = IssueListingEditorGrid.getExcelXml();
if (!Ext.isIE6 || !Ext.isIE7 || !Ext.isSafari || !Ext.isSafari2 || !Ext.isSafari3) {
document.location='data:application/vnd.ms-excel;base64,' + Base64.encode(exportContent);
} else {
if (!Ext.fly('frmDummy')) {
var frm = document.createElement('form');
frm.id = 'frmDummy';
frm.name = id;
frm.className = 'x-hidden';
document.body.appendChild(frm);
}
Ext.Ajax.request({
url: 'export/excel.php',
method : 'POST',
form: Ext.fly('frmDummy'),
callback: function(o, s, r){
alert(r.responseText);
},
isUpload: true,
params: { ex: exportContent }
});
}
}


Your PHP code should be like this one:



header("Pragma: public");
header("Expires: 0"); // set expiration time
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Content-Type: application/force-download");
header('Content-type: application/vnd.ms-excel');
header("Content-Disposition:attachment;filename=export");
echo $_REQUEST['ex'];


Hope this helps someone

Al

Hey Al,

I am trying to use your code, however, no success. It's doing nothing. Is it working for anyone else?

It does not give any error, but does not do anything as well.

Thanks.

Mihir

Marcelo Delgado
16 Jul 2008, 12:51 PM
Hi Animal I think this is great ! I was looking over Internet a lot but I did not find nothing like this. My problem is that I am using gwt-ext in my application and I would like to have this functionallity in it. I really dont know how to include this in my application, can you give me some tips about this or point me where to go.

Any help it would be great

Thanks

Marelo

iwagon
17 Jul 2008, 6:30 AM
AH, thanks for that! I'll do conditional method creation and update post 1.

Правильнее будет:
encode : (typeof btoa == 'function') ? function(input) { return btoa(utf8Encode(input)); } : function (input) {

ajaxE
18 Jul 2008, 6:56 PM
This seems not working on IE6? When I clicked the "excel" link, nothing was shown up.

Anything am I missing?

Thanks!

mystix
18 Jul 2008, 7:25 PM
Anything am I missing?

definitely.


taken from the first line of the very first post (emphasis added)

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

ajaxE
18 Jul 2008, 8:00 PM
Thanks for your quick reply!:-)

I wish it works in IE6 so we can use it :-(.

psarunkumar
20 Jul 2008, 10:15 PM
/*
* Ext JS Library 2.1
* Copyright(c) 2006-2008, Ext JS, LLC.
* licensing@extjs.com
*
* http://extjs.com/license
*/
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.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="dd/mm/yy\ h:mm;@" />' +
'</ss:Style>' +
'<ss:Style ss:Parent="odd" ss:ID="evenint">' +
'<ss:NumberFormat ss:Format="0" />' +
'</ss:Style>' +
if (cellType[k] == 'DateTime') {
t += v.format('Y-m-d\\TH:i:s.000'); // no space betwen i: s
} else {
t += v;
}

'<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.getColumnModel();
var totalWidthInPixels = 0;
var colXml = '';
var headerXml = '';
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 "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>
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 (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.getColumnCount(); j++) {
if (includeHidden || !cm.isHidden(j)) {
var v = r[cm.getDataIndex(j)];
t += '<ss:Cell ss:StyleID="' + cellClass + cellTypeClass[k] + '"><ss:Data ss:Type="' + cellType[j] + '">';
if (cellType[k] == 'DateTime') {
t += v.format('Y-m-d');
} else {
t += v;
}
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;
}
});

Ext.onReady(function(){
Ext.state.Manager.setProvider(new Ext.state.CookieProvider());
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']
];
// example of custom renderer function
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;
}
// example of custom renderer function
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.SimpleStore({
fields: [
{name: 'company'},
{name: 'price', type: 'float'},
{name: 'change', type: 'float'},
{name: 'pctChange', type: 'float'},
{name: 'lastChange', type: 'date', dateFormat: 'n/j h:ia'}
]
});
store.loadData(myData);

var linkButton = new Ext.LinkButton({
id: 'grid-excel-button',
text: 'Excel'
});

// create the Grid
var grid = new Ext.grid.GridPanel({
id: 'static-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,
title:'Array Grid'
bbar: new Ext.Toolbar({
buttons: [linkButton]
})
});
grid.render('grid-example');
linkButton.getEl().child('a', true).href = 'data:application/vnd.ms-excel;base64,' +
Base64.encode(grid.getExcelXml());

grid.getSelectionModel().selectFirstRow();
});

this code i try but not work tell some suggestion to me





It's OK. I saw your notepad.txt. It's looking good. I attached screenshot to this letter. It's your report but with my remark in first row - you should remove appropriate line in ExportToExcel.js. Please use search with "SEBN UA" parameter.

Efex
21 Jul 2008, 1:36 PM
Hi,

I'm using a grid with RowActions and implemented solution suggested in this post:
http://extjs.com/forum/showthread.php?t=32400&page=6

It took care of the error but now I get another error saying: String contains an invalid character * code: *5
encode : (typeof btoa == 'funct..turn btoa(input);...........


Anyone with the same error??
Any ideas how to fix it?

gthe
21 Jul 2008, 10:30 PM
Hi,

I'm using a grid with RowActions and implemented solution suggested in this post:
http://extjs.com/forum/showthread.php?t=32400&page=6

It took care of the error but now I get another error saying: String contains an invalid character * code: *5
encode : (typeof btoa == 'funct..turn btoa(input);...........


Anyone with the same error??
Any ideas how to fix it?

Here is my variant that work with rowaction and CheckboxSelection:


/**
* allows for downloading of grid data (store) directly into excel
* Method: extracts data of gridPanel store, uses columnModel to construct XML excel document,
* converts to Base64, then loads everything into a data URL link.
*
* @author Animal <extjs support team>
*
*/

/**
* base64 encode / decode
*
* @location 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 = [];
var cellTypeClass = [];
var cm = this.getColumnModel();
var totalWidthInPixels = 0;
var colXml = '';
var headerXml = '';
var visibleColumnCountReduction = 0;
for (var i = 0; i < cm.getColumnCount(); i++) {
if (includeHidden || !cm.isHidden(i)) {
//debugger;
var w = cm.getColumnWidth(i)
totalWidthInPixels += w;
if ((cm.getColumnHeader(i) === "") || (cm.getColumnId(i) === "checker") || ((cm.getColumnId(i) === "actions") && (cm.getColumnHeader(i) === "Actions"))){
cellType.push("None");
cellTypeClass.push("");
++visibleColumnCountReduction;
}
else
{
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 "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 - visibleColumnCountReduction;

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>Cacti snmptt plugin export rezult (Generated by ExtJS)</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.getColumnCount(); j++) {
if (includeHidden || !cm.isHidden(j)) {
//debugger;
var v = r[cm.getDataIndex(j)];
if (cellType[k] !== "None") {

if (!v) {
t += '<ss:Cell ss:StyleID="' + cellClass +'"></ss:Cell>';
}else {
t += '<ss:Cell ss:StyleID="' + cellClass + cellTypeClass[k] + '"><ss:Data ss:Type="' + cellType[k] + '">';
if (cellType[k] == 'DateTime') {
t += v.format('Y-m-d\\TH:i:s.000'); // no space betwen i: s
} else {
t += v;
}
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;
}
});

Efex
22 Jul 2008, 6:48 AM
gthe,

When I use the code as you put it above, I get the error saying fld is undefined :s

Then I add what u mention on post #58 on this thread


To fix :
1. (about 148 row) for (var i = 0; i < cm.getColumnCount(); i++) {
if ((includeHidden || !cm.isHidden(i)) && (cm.getDataIndex(i))) {
2. (about 204 row): ss:ExpandedColumnCount="' + (visibleColumnCount +2) +
'" ss:ExpandedRowCount="' + (this.store.getCount() + 2) + '">' +
May be it is dirty, but work for me
But this takes me to the error of "string contains invalid character code" :s:s:-/:-/

Efex
22 Jul 2008, 6:58 AM
Ok. I figured out what is wrong :)

The invalid character error appears because I use spanish language on grid and there are some columns with (

maggiesnyder
22 Jul 2008, 9:06 AM
I'm using FF3 on a Windows machine.

I'm using this extension and so far it works very well except for two small things.

1. The filename seems to be a string of random letters.
2. When I try to save the file, the extension is weird - it shows up as .xls.part, and then when I actually save the file it adds another .xls to it so the filename ends up looking like this:

XR9oqU0s.xls.part.xls

The file opens fine, I'm just wondering if this has happened to anyone else?

Efex
24 Jul 2008, 12:33 PM
Hi,

Hope someone can help me with the character problem.

I solved the error of invalid character that function was giving me by placing this on my php DB code: query("SET NAMES 'utf8'");

But now I get an error when the file is trying to open on excel, so I guess the fuction now runs without problems but the error is on some encode, decode problem on the Ext function.
The error on Excel says there's an HTML importing error during loading on area "Table" and send me to see a log file but this file doesn't exist :s

Must say that if I hide column with utf8 characters as

kimmking
24 Jul 2008, 12:57 PM
Mr Animal's extension support a way to create a xhtml and open it by the Excel.

Efex
24 Jul 2008, 1:09 PM
Mr Animal's extension support a way to create a xhtml and open it by the Excel.

Is this in response to my question?? :-/

Just in case, I'm talking of course about the function to export to excel posted by Animal and modified by gthe :)

This function is giving me an error when opening the file in Excel (2007 BTW)

krzak
28 Jul 2008, 1:45 PM
Your PHP code should be like this one:


header("Pragma: public");
header("Expires: 0"); // set expiration time
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Content-Type: application/force-download");
header('Content-type: application/vnd.ms-excel');
header("Content-Disposition:attachment;filename=export");
echo $_REQUEST['ex'];



there should be


echo stripslashes($_REQUEST['ex']);

frpucci
31 Jul 2008, 7:37 AM
there should be


echo stripslashes($_REQUEST['ex']);

Hi all,

I tried this code and all seemed to be right, until I had used a machine with Excel2000.

Someone knows the reason why this code is right for 2003 and is wrong for 2000?

Thanks a lot ;)

cvieira
17 Aug 2008, 4:11 PM
So in order for this to print out just what you see on the screen (after column resorting/hiding/editing of values), you would need to call the Base64 function, and overwrite the button's A HREF tag after each column change?


Hi, this feature is very useful, but how are you using it when having grid with paging? The excel export will have only the contents of the current page, right? How can do this for getting the export to have the contents of all the pages?
Thanks

kristalgic
18 Aug 2008, 2:41 AM
I'm using FF3 on a Windows machine.

I'm using this extension and so far it works very well except for two small things.

1. The filename seems to be a string of random letters.
2. When I try to save the file, the extension is weird - it shows up as .xls.part, and then when I actually save the file it adds another .xls to it so the filename ends up looking like this:

XR9oqU0s.xls.part.xls

The file opens fine, I'm just wondering if this has happened to anyone else?

Did anybody else come accross this error? I am also using FF3 and getting the same exact result.

lakehouse
21 Aug 2008, 11:16 PM
I've used this tool to export from the grid with RowSelection and CheckBox column + UTF8 encoding with Russian characters, and the clue was to take fresh Base64 encoding class from www.webtoolkit.info

this is my code:
/*
* allows for downloading of grid data (store) directly into excel
* Method: extracts data of gridPanel store, uses columnModel to construct XML excel document,
* converts to Base64, then loads everything into a data URL link.
*
* @author Animal <extjs support team>
*
*/

/**
* base64 encode / decode
*
* @location http://www.webtoolkit.info/
*
*/

var Base64 = {

// private property
_keyStr : "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/=",

// public method for encoding
encode : function (input) {
var output = "";
var chr1, chr2, chr3, enc1, enc2, enc3, enc4;
var i = 0;

input = Base64._utf8_encode(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 +
this._keyStr.charAt(enc1) + this._keyStr.charAt(enc2) +
this._keyStr.charAt(enc3) + this._keyStr.charAt(enc4);

}

return output;
},

// public method for decoding
decode : function (input) {
var output = "";
var chr1, chr2, chr3;
var enc1, enc2, enc3, enc4;
var i = 0;

input = input.replace(/[^A-Za-z0-9\+\/\=]/g, "");

while (i < input.length) {

enc1 = this._keyStr.indexOf(input.charAt(i++));
enc2 = this._keyStr.indexOf(input.charAt(i++));
enc3 = this._keyStr.indexOf(input.charAt(i++));
enc4 = this._keyStr.indexOf(input.charAt(i++));

chr1 = (enc1 << 2) | (enc2 >> 4);
chr2 = ((enc2 & 15) << 4) | (enc3 >> 2);
chr3 = ((enc3 & 3) << 6) | enc4;

output = output + String.fromCharCode(chr1);

if (enc3 != 64) {
output = output + String.fromCharCode(chr2);
}
if (enc4 != 64) {
output = output + String.fromCharCode(chr3);
}

}

output = Base64._utf8_decode(output);

return output;

},

// private method for UTF-8 encoding
_utf8_encode : function (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;
},

// private method for UTF-8 decoding
_utf8_decode : function (utftext) {
var string = "";
var i = 0;
var c = c1 = c2 = 0;

while ( i < utftext.length ) {

c = utftext.charCodeAt(i);

if (c < 128) {
string += String.fromCharCode(c);
i++;
}
else if((c > 191) && (c < 224)) {
c2 = utftext.charCodeAt(i+1);
string += String.fromCharCode(((c & 31) << 6) | (c2 & 63));
i += 2;
}
else {
c2 = utftext.charCodeAt(i+1);
c3 = utftext.charCodeAt(i+2);
string += String.fromCharCode(((c & 15) << 12) | ((c2 & 63) << 6) | (c3 & 63));
i += 3;
}

}

return string;
}

}

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 = [];
var cellTypeClass = [];
var cm = this.getColumnModel();
var totalWidthInPixels = 0;
var colXml = '';
var headerXml = '';
var visibleColumnCountReduction = 0;
for (var i = 0; i < cm.getColumnCount(); i++) {
if (includeHidden || !cm.isHidden(i)) {
//debugger;
var w = cm.getColumnWidth(i)
totalWidthInPixels += w;
if ((cm.getColumnHeader(i) === "") || (cm.getColumnId(i) === "checker") || ((cm.getColumnId(i) === "actions") && (cm.getColumnHeader(i) === "Actions"))){
cellType.push("None");
cellTypeClass.push("");
++visibleColumnCountReduction;
}
else
{
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 "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 - visibleColumnCountReduction;

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>Данные получены из L\'Oreal PITM (www.pos-it.ru))</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.getColumnCount(); j++) {
if (includeHidden || !cm.isHidden(j)) {
//debugger;
var v = r[cm.getDataIndex(j)];
if (cellType[k] !== "None") {

if (!v) {
t += '<ss:Cell ss:StyleID="' + cellClass +'"></ss:Cell>';
}else {
t += '<ss:Cell ss:StyleID="' + cellClass + cellTypeClass[k] + '"><ss:Data ss:Type="' + cellType[k] + '">';
if (cellType[k] == 'DateTime') {
t += v.format('Y-m-d\\TH:i:s.000'); // no space betwen i: s
} else {
t += v;
}
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;
}
});


But still some issues left:

I can't download the file with FF3 - only via open->Save As (the problem described by kristalgic in previous post)
This tool doesn't work with IE 6 - i get Sytax Error in button handler code
function(){ document.location='data:application/vnd.ms-excel;base64,' + Base64.encode(posGrid.getExcelXml());}

g13013
27 Aug 2008, 12:44 AM
Great component, thank you Animal,
still the problem of the filename and i see that the DATA url dont support the file name param, the only way is to generate the file in the server side and set the header so that we can specify the file name.

am i wrong ?

tdg2008
27 Aug 2008, 1:11 AM
http://www.extjs.com/forum/showthread.php?p=214760#post214760

zhfxu_cs
31 Aug 2008, 5:20 AM
No tag "Workbook" defined in tag library imported with prefix "ss"

who can tell me why?

sam.zhang
1 Sep 2008, 11:24 PM
I'm testing this but getting an error message:

fld is undefined
why?

g13013
3 Sep 2008, 10:31 PM
I've got the same message and after ithe use of firebug ~o) i found one error in my store fields for example :


var reader = new Ext.data.JsonReader({root: 'results',totalProperty: 'total',id: 'id'},
[
{name:'data1', type:'string'},
{name:'data2', type:'string'},
{name:'data3', type:'string'},
{name:'data4', type:'string'},
{name:'data5', type:'string'},
{name:'data6', type:'string'},
{name:'data7', type:'string'},
{name:'data8', type:'string'},
{name:'data9', type:'string'}
]);

var CM = new xg.ColumnModel([
{header: 'header 1',dataIndex: 'data1'},
{header: 'header 2',dataIndex: 'data2'},
{header: 'header 3',dataIndex: 'data3'},
{header: 'header 4',dataIndex: 'data4'},
{header: 'header 5',dataIndex: 'data5'},
{header: 'header 6',dataIndex: 'data6'},
{header: 'header 7',dataIndex: 'data7'},
{header: 'header 8',dataIndex: 'data'},
// the correct dataindex is data8 because dataindex data does't exist in the json reader
{header: 'header 9',dataIndex: 'data9'}
]);


perhaps you have de same error, then if you use a special characters like [COLOR="DarkOrange"]

roopa
25 Sep 2008, 6:58 AM
Hi All,

Export to Excel works fine in FF, but in IE8 I get this error -

Message: The data area passed to a system call is too small.

Can anybody please tell me how to fix this?

Animal
25 Sep 2008, 9:22 AM
Sounds like an internal IE bug. I'd report it to McSoft.

loveyeah
27 Sep 2008, 10:02 PM
good extends ,your code work for the data in the page ,my store have many pages ,and next page's date still in oracle server ,so what can i do ,to export all the data to excel use your extends ,thank a lot! and in excel on sheet max row about 60000, if my data row large than 60000 and your excel can create a new sheet?

Animal
28 Sep 2008, 12:12 AM
What would a user want with a 60000 row spreadsheet? What sense could the human mind make out of that?

But if you really did want to do that for analysis purposes, you would do it on the server.

maggiesnyder
6 Oct 2008, 8:51 AM
Hi,
This isn't exactly a problem with this extension (it's awesome and I'm using it on a several different grids so far), but more of a question of how to implement it in grids that are created in a loop. I'm using FF3 and Ext 2.2.

In my code I create a bunch of grids with a loop - it looks like this:


this[status[j]+"gridOverall"]= new Ext.grid.GridPanel({...

bbar: new Ext.Toolbar({
buttons: [{
id: 'grid-excel-button',
text: 'Export to Excel...',
handler: function(){
document.location='data:application/vnd.ms-excel;base64,' + Base64.encode(this[status[j]+"gridOverall"].getExcelXml());
}

})But then I get this error from Firebug:

this[status[j] + "gridOverall"] is undefined
chrome://firebug/content/blank.gif document.location='data:applicati...status[j]+"gridOverall"].getExcelXml());

Does anyone know how I should be doing this? Sorry, I think this is probably a beginner question, but I've been stuck on it for a while. Thanks for any help!

devnull
6 Oct 2008, 1:15 PM
Thats just a scope issue, not a problem with this extension. What is 'this' at the point you create the new grid? What is 'this' within the button handler function?
You would do yourself a huge favor to learn how scope works in javascript.

maggiesnyder
6 Oct 2008, 1:42 PM
Yes, that's why I said it's not a problem with the extension.

But thanks...

Maybe I misphrased my question - why can't I call getExcelXml() with a variable name? Like this:


var name = "foo";
...
document.location= ... Base64.encode(name.getExcelXml());
...
I've been trying different variations on this and they don't work. getExcelXml thinks that "name" is a string and it doesn't get the value that I set name to. I get an error that says "name.getExcelXml is not a function" - why isn't javascript putting "foo" in for the variable name?

Sorry for being a newb at javascript and again, thanks for any help.

devnull
6 Oct 2008, 2:56 PM
...because 'name' is indeed just a string in that example, and doesnt have a 'getExcelXml' property?
Whatever object 'this' is at the point the grid is created is the object you need to refer to in the handler, or set its scope to.

devnull
6 Oct 2008, 3:05 PM
Animal, love the extension, but I had some grids with custom renderers that were being ignored, so I made the below changes. There currently is no consideration for the data type being set, and there is as far as i can tell no way to tell the difference between a custom renderer and the default renderer, but it works.


// 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.getColumnCount(); j++) {
if (includeHidden || !cm.isHidden(j)) {
var v = r[cm.getDataIndex(j)];
t += '<ss:Cell ss:StyleID="' + cellClass + cellTypeClass[k] + '"><ss:Data ss:Type="' + cellType[j] + '">';
t += cm.getRenderer(j).call(this, v, {css : null, attr : null}, this.getStore().getAt(i), i, j);
//if (cellType[k] == 'DateTime') {
// t += v.format('Y-m-d');
//} else {
// t += v;
//}
t += '</ss:Data></ss:Cell>';
k++;
}
}
t += '</ss:Row>';
}

korto
27 Oct 2008, 5:59 AM
Hi, I am using your cool feature but I am facing a problem. When I click the link to open the Excel, excel application does open but with an error message right at startup : "Problems came up with the following areas during load: Table". Have you got any clue on how to fix that?
Thanks a lot

Animal
27 Oct 2008, 8:48 AM
It also tells you where it places an error file. Somewhere in your C:\Documents and settings directory. Check that out, it tells you a little more.

Basically, it creates some XML. You can get in there in Firebug and examine the XML that it creates, there's some mistake. Excel needs perfect XML to load a spreadsheet.

You should be able to grab the XML, and copy it into an XML editor like Liquid XML Studio, and see what's wriong with it.

Here's the reference I used to generaet that XML: http://msdn.microsoft.com/en-us/library/aa140066(office.10).aspx

Animal
27 Oct 2008, 8:54 AM
Animal, love the extension, but I had some grids with custom renderers that were being ignored, so I made the below changes. There currently is no consideration for the data type being set, and there is as far as i can tell no way to tell the difference between a custom renderer and the default renderer, but it works.


// 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.getColumnCount(); j++) {
if (includeHidden || !cm.isHidden(j)) {
var v = r[cm.getDataIndex(j)];
t += '<ss:Cell ss:StyleID="' + cellClass + cellTypeClass[k] + '"><ss:Data ss:Type="' + cellType[j] + '">';
t += cm.getRenderer(j).call(this, v, {css : null, attr : null}, this.getStore().getAt(i), i, j);
//if (cellType[k] == 'DateTime') {
// t += v.format('Y-m-d');
//} else {
// t += v;
//}
t += '</ss:Data></ss:Cell>';
k++;
}
}
t += '</ss:Row>';
}


I don't agree with using renderers when creating a spreadsheet.

Renderers are used when you are finally "exiting" the application, and all data is being converted into readable format, ie converted to String.

In this case, the intention is to place real data into the spreadsheet. Dates as dates so that Excel can understand them, and process them. Floating point numbers as numbers so that they can be used in calculations.

We do not want string representations of the data to end up in the spreadsheet.

devnull
27 Oct 2008, 10:22 AM
In my case I have grids that have columns made entirely with data from other columns. My understanding of the this code was that it should present the data in excel exactly the same as it looks in the gridView rather than the raw data in the store, but perhaps I was mistaken :) I ended up adding an additional config to the columns to specifically set the output format when none was available. I also went on to make further changes to get it to export a grouping grid correctly.
And youre right, excel gets very unhappy very quickly if the xml isnt *perfect*!

Mthor
29 Oct 2008, 7:17 AM
roopa roopa is offline
Ext User

Join Date: Oct 2007
Posts: 1
roopa is infamous around these parts
Default Error in IE8
Hi All,

Export to Excel works fine in FF, but in IE8 I get this error -

Message: The data area passed to a system call is too small.

Can anybody please tell me how to fix this?


any body find a fix for this yet?

Thanks for the extension

Animal
29 Oct 2008, 8:35 AM
Report it to McSoft. I would guess it's an IE error caused by using a data: URL. If it's a large dataset, then the BASE64 encoding of an XML spreadsheet will be large. The whole content is encoded into the href of a link as the URL. Looks like IE8 just cannot cope.

angeldimitrov
11 Nov 2008, 7:56 PM
I have another problem, that i tried to solve alone, but without success. I have some german characters in my grid - "

Animal
12 Nov 2008, 5:10 AM
What is your page's default character encoding?

The XML is encoded as a UTF-8 bytestream before being encoded into BASE64, so try setting the URL like this:



'data:application/vnd.ms-excel;charset=UTF-8;base64,' + Base64.encode(grid.getExcelXml())


Let us know if that works, and I'll update post #1

angeldimitrov
12 Nov 2008, 3:18 PM
Hi Animal,
thanks for the fast response. The encoding on the page is utf-8 too.
I tried your suggestion, but without any success. Any other ideas ?

emredagli
15 Nov 2008, 6:16 AM
Hi Mr. Animal,
I plan to use your extension to export grid to excel.
But my grid use GroupingStore and I have group headers.
I also have group summary rows.
Is there a way to show these rows in excel?

I paste your code to GridView3 Grouping example.
But It is not export grouping headers to excel.

Thanks for your advance.

Animal
15 Nov 2008, 10:02 AM
You'll have to add them yourself. Here's a link to the MS documentation:

http://msdn.microsoft.com/en-us/library/aa140066(office.10).aspx

What I would do is practice handcoding the XML to represent the grouping rows, and then write the code to generate that XML.

It's pretty simple if you look at the generated XML. It's just a glorified <table>

emredagli
16 Nov 2008, 8:33 AM
Mr. Animal you are right. It is not too difficult to modify.
And I am use renderer functions to show field values.


t += cm.getRenderer(j)(v);


And also I have to modify ss:ExpandedRowCount property to add extra rows (group headers rows).

Thanks for your advance.

Ronhead
18 Nov 2008, 7:02 AM
hi animal

i have a problem with your plugin, error is


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

in ff running too fast but in IE fail it

if you know anything please tell me

saludos!
ron.

Animal
19 Nov 2008, 1:30 AM
This needs a browser that supports data URLs. FF, Opera and IE8 will support this.

.

emredagli
22 Nov 2008, 1:50 AM
Hii, Mr Animal,
I search all messages in this thread and people asked that "how can we set file name?".
Given solution is:
Content-Disposition:attachment;filename=export_filename.xls

Should I post new html page with header contains this parameters?

thanks.

Animal
22 Nov 2008, 2:44 AM
I don't think you can do this with data: URLs

http://tools.ietf.org/html/rfc2397

emredagli
22 Nov 2008, 3:10 PM
Hii Mr. Animal,
I search the link you post. But I couldn't find the solution...
I try many combinations like:


linkButton.getEl().child('a', true).href = 'data:application/vnd.ms-excel;Content-Disposition:attachment;filename=export_filename.xls;name=hebe.xls;base64,' + Base64.encode(grid.getExcelXml());
...
linkButton.getEl().child('a', true).href = 'data:application/vnd.ms-excel;Content-Disposition:attachment;file=export_filename.xls;name=hebe.xls;base64,' + Base64.encode(grid.getExcelXml());

When I search in google I found a thread and in there they says that there is no way to do this.
http://groups.google.com/group/mozilla.dev.tech.network/browse_thread/thread/ccdd1b37053742a4

Do I miss something?
you may regret to writing this component... (Because of questions...) :(

Animal
23 Nov 2008, 4:19 AM
You missed my last post it seems.;)

Animal
23 Nov 2008, 4:21 AM
Really, I only wrote it to illustrate how XML spreadsheets can be easily built using XML.

People should take whats illustrated here, and write some code in their server-side to generate the XML.

zhw511006
27 Nov 2008, 5:48 PM
Thank you for sharing your code!

But Error ! IE7:
The data area passed to a system call is too small.

mystix
27 Nov 2008, 6:05 PM
From post #1 one of this thread (emphasis added):


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

.

snoir
10 Dec 2008, 12:12 AM
Your PHP code should be like this one:



header("Pragma: public");
header("Expires: 0"); // set expiration time
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Content-Type: application/force-download");
header('Content-type: application/vnd.ms-excel');
header("Content-Disposition:attachment;filename=export");
echo $_REQUEST['ex'];




i don't understand php code,i want jsp code

jmcneese
10 Dec 2008, 8:30 AM
i don't understand php code,i want jsp code

dude. are you saying you use JSP and don't know enough to translate the above PHP code (which is about as simple as it gets) into JSP?

this forum isn't a soup kitchen. go hit up the php website (maybe http://www.php.net/echo and http://www.php.net/header will help) and figure it out.

snoir
10 Dec 2008, 10:30 PM
this is my js code:

Ext.Ajax.request({
url: '../../exportexcel.jsp',
method: 'POST',
form: Ext.fly('frmDummy'),
callback: function(o, s, r) {
//alert(r.responseText);
},
isUpload: true,
params: {exportContent:vExportContent}
})
this is jsp code:


<%
response.setHeader("Pragma","public");
response.setHeader("Expires","0");
response.setHeader("Cache-Control","must-revalidate, post-check=0, pre-check=0");
response.setHeader("Content-Type","application/force-download");
response.setHeader("Content-Type","application/vnd.ms-excel");
response.setHeader("Content-Disposition","attachment;filename=export");

out.print(request.getParameter("exportContent"));
%>

i can't get "exportContent" in jsp.........
i open the excel file,it's null....but in function callback can show the 'exportContent''s value......


when i delete 'form: Ext.fly('frmDummy')' ,jsp can get the params.......

How to resolve this issue???

emredagli
11 Dec 2008, 1:06 PM
Hii,
I found a way to save as different filename and also working in IE 7.0 by using .NET framework in server side.
.js code:


function post_to_url(path, params, method) {
method = method || "post";

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);
form.submit();
}

function exportExcell() {

var dataURL = 'CommonPages/ExportToExcelType1.aspx';

params =[{
name: 'Data',
value: resultGrid.getExcelXml() // Mr. Animal's part...
},{
name: 'FileName',
value: 'File1.xls'
}];

post_to_url(dataURL, params, 'post');
}


CommonPages/ExportToExcelType1.aspx.cs code is:



protected void Page_Load(object sender, EventArgs e)
{
Response.Clear();
Response.Buffer = true;
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Disposition", "attachment;filename=\"" + Request.Params["FileName"].ToString() + "\"");
Response.Charset = "";
this.EnableViewState = false;

System.IO.StringWriter SW = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter HTW = new System.Web.UI.HtmlTextWriter(SW);
HTW.WriteLine(Request.Params["Data"]);

Response.Write(SW.ToString());
Response.End();

}

And do not forget to put ValidateRequest="false" in the top of the ExportToExcelType1.aspx file like:


<%@ Page Language="C#" AutoEventWireup="true" CodeFile="ExportToExcelType1.aspx.cs" Inherits="CommonPages_ExportToExcelType1" ValidateRequest="false"%>


In this solution there may be unnecessary parts...
But it works nicelly.
I hope it helps you... Good luck...

snoir
11 Dec 2008, 6:38 PM
In this solution there may be unnecessary parts...
But it works nicelly.
I hope it helps you... Good luck...

thx,emredagli.the code is ok.:D

my js:

var exportMenu= {
text: 'ExportToExcel',
iconCls:'export',
tooltip:'ExportToExcel',
handler: function() {
var vExportContent = grid.getExcelXml();
if (Ext.isIE6 || Ext.isIE7 || Ext.isSafari || Ext.isSafari2 || Ext.isSafari3) {
var dataURL = '../../exportexcel.jsp';
params =[{
name: 'ex',
value: vExportContent
},{
name: 'FileName',
value: 'excel.xls'
}];
post_to_url(dataURL, params, 'post');
} else {
document.location = 'data:application/vnd.ms-excel;base64,' + Base64.encode(vExportContent);
}
}
};

function post_to_url(path, params, method) {
method = method || "post";
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);
form.submit();
}
my jsp:

<% response.setHeader("Pragma","public");
response.setHeader("Expires","0");
response.setHeader("Cache-Control","must-revalidate, post-check=0, pre-check=0");
response.setHeader("Content-Type","application/force-download");
response.setHeader("Content-Type","application/vnd.ms-excel");
response.setHeader("Content-Disposition","attachment;filename="+request.getParameter("FileName"));

out.print(request.getParameter("ex"));
%>
:D:D

vvreddy@12
14 Jan 2009, 9:55 PM
Hi snoir,

I have used the same code given by you but could not get the output.

I am getting a blank excel sheet.
please let me know what to do ????

Thanks,
Vishnu

emredagli
15 Jan 2009, 12:28 AM
Are you sure the excel datas created successfully?

(By putting break point on Firefox) Check the,


var vExportContent = grid.getExcelXml();
part.

vvreddy@12
15 Jan 2009, 1:38 AM
Hi Emredagli, (http://extjs.com/forum/member.php?u=39227)

The excel was blank and didn't get any data. Can you suggest me what was wrong in that piece of code.

Thanks,
Vishnu

emredagli
15 Jan 2009, 1:43 AM
(By putting break point on FIREBUG) Check the,
var vExportContent = grid.getExcelXml();
parameter.

vvreddy@12
15 Jan 2009, 9:23 PM
Hi,

I have put a break point in the firebug and found the result int the xml format. The problem which I am facing is not with firefox but with IE. The code is working fine with firefox and is not working with IE.

Please find the result of the variable vExportContnet as an attachment and at the same time I am sending my code to you.

Thanks,
Vishnu

Animal
16 Jan 2009, 12:42 AM
Could your problem be something to do with not reading the very first line in ths thread?



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

vvreddy@12
16 Jan 2009, 1:18 AM
Hi Animal,

I have seen that line in the early conversations but there was a thread given by emredagli in which he specified that he could export the data into an excel using IE 7. So we have started to use that piece of code in your code.

Thanks,
Vishnu

emredagli
16 Jan 2009, 2:41 AM
Hi vvreddy,
unfortunately, I haven't work with jsp files. Actually I test my code by using aspx files in .NET.
I want to ask firstly, What did you mean as getting blank page? If when you try to open document in excel and getting improper format, The problem may related with:


fields: [
{name: 'company'},
{name: 'price', type: 'float'},
{name: 'change', type: 'float'},
{name: 'pctChange', type: 'float'},
{name: 'lastChange', type: 'date', dateFormat: 'n/j h:ia'}
]

In "company" field, it's type isn't declared.
Or you can try by giving all formats as string (I used as string to all fields).

The second is if you change the part:


if (Ext.isIE6 || Ext.isIE7 || Ext.isSafari || Ext.isSafari2 || Ext.isSafari3) {
var dataURL = '../jsp/exportexcel.jsp';
params =[{
name: 'ex',
value: vExportContent
},{
name: 'FileName',
value: 'excel.xls'
}];
post_to_url(dataURL, params, 'post');
} else {

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


AS



var dataURL = '../jsp/exportexcel.jsp';
params =[{
name: 'ex',
value: vExportContent
},{
name: 'FileName',
value: 'excel.xls'
}];
post_to_url(dataURL, params, 'post');

You can test it in Firefox. Try this and look at the response you send in firebug.

And also I change the format of the file "result.txt" (you sended) as "result.xls". And I can open it in excel. (Problem probably related with jsp... :-?)

I hope you solve the problem.
Good luck.

whodat
6 Feb 2009, 12:18 PM
Obviously this only works on the data in the Store - if you are using server-side paging, then perform this processing on the server. For quick and dirty conversion of a small table to Excel, this might be useful.

Hi Animal.. great user extension and I was able to get this working in IE7 and FF3.

Quick question/possible bug in regards to the statement above.

I am using paging but decided to do it on the client side with Condor's extension. So I have all of the data on the client side in my grid's store. But when exporting to excel, I only get the rows visible to the grid. Any suggestions as to why?

Digging a lil into your code, I found the following comments and code


// 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.getColumnCount(); j++) {
if (includeHidden || !cm.isHidden(j)) {
var v = r[cm.getDataIndex(j)];
if (cellType[k] !== "None") {

t += '<ss:Cell ss:StyleID="' + cellClass + cellTypeClass[k] + '"><ss:Data ss:Type="' + cellType[k] + '">';
if (cellType[k] == 'DateTime') {
t += v.format('Y-m-d');
} else {
t += v;
}
t +='</ss:Data></ss:Cell>';
}
k++;
}
}
t += '</ss:Row>';
}
Does this piece actually take all data present in the store, because it does not seem to...

Please let me know and thanks again for the extension.

Animal
7 Feb 2009, 1:21 AM
Whuhhhhh?

MUST prevent myself going into Cox mode... MUST.....

http://i163.photobucket.com/albums/t283/Da_aliG85/Dr.Cox.jpg

whodat
7 Feb 2009, 4:18 AM
Whuhhhhh?

I'm no sure I get you, I don't watch scrubs but I know dude in the pic is on that show.

If you're stating that this extension was just a way to illustrate the capabilities then yes, I understand you.

My concern was this this.store.data.items I thought this had all items in the store and not what was only loaded into the grid and I was paging locally and not on the server how could I get all of the records exported without loading the grid.

Animal
8 Feb 2009, 3:01 AM
The Store can only expose what is in the current page, so you will only ever get the current page from the Store. If it didn't do this, it wouldn't be paged.

If you are using Condor's paging Store, then you need to ask him how to access the "real" Store containing the whole dataset, and use that.

Rafael
12 Feb 2009, 6:52 PM
i try but have a error in my code:

unterminated string literal
in line:
'<ss:Cell ss:StyleID="title" ss:MergeAcross="' + (visibleColumnCount - 1) + '">' +

stevenhzj
13 Feb 2009, 7:10 AM
Hii,
I found a way to save as different filename and also working in IE 7.0 by using .NET framework in server side.
.js code:


function post_to_url(path, params, method) {
method = method || "post";

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);
form.submit();
}

function exportExcell() {

var dataURL = 'CommonPages/ExportToExcelType1.aspx';

params =[{
name: 'Data',
value: resultGrid.getExcelXml() // Mr. Animal's part...
},{
name: 'FileName',
value: 'File1.xls'
}];

post_to_url(dataURL, params, 'post');
}


CommonPages/ExportToExcelType1.aspx.cs code is:



protected void Page_Load(object sender, EventArgs e)
{
Response.Clear();
Response.Buffer = true;
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Disposition", "attachment;filename=\"" + Request.Params["FileName"].ToString() + "\"");
Response.Charset = "";
this.EnableViewState = false;

System.IO.StringWriter SW = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter HTW = new System.Web.UI.HtmlTextWriter(SW);
HTW.WriteLine(Request.Params["Data"]);

Response.Write(SW.ToString());
Response.End();

}

And do not forget to put ValidateRequest="false" in the top of the ExportToExcelType1.aspx file like:


<%@ Page Language="C#" AutoEventWireup="true" CodeFile="ExportToExcelType1.aspx.cs" Inherits="CommonPages_ExportToExcelType1" ValidateRequest="false"%>


In this solution there may be unnecessary parts...
But it works nicelly.
I hope it helps you... Good luck...


hi emredagli
i use you code, and my store is any pages. any each page can show data,but i can get store.data.length is 0? anybody help me?

NoahK17
16 Feb 2009, 5:58 PM
I used to have this working, but even now when I try the new Example, the returned file is called "xxxxxx.xls.PART" -- What is causing the .part extension to be returned from the server? Here's the XML that's returned... can anyone make it a .xls file and see if Excel opens it properly? I only have OpenOffice on this computer.



<?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>Array Grid</o:Title></o:DocumentProperties><ss:ExcelWorkbook><ss:WindowHeight>9000</ss:WindowHeight><ss:WindowWidth>39350</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><ss:Worksheet ss:Name="Array Grid"><ss:Names><ss:NamedRange ss:Name="Print_Titles" ss:RefersTo="='Array Grid'!R1:R2" /></ss:Names><ss:Table x:FullRows="1" x:FullColumns="1" ss:ExpandedColumnCount="5" ss:ExpandedRowCount="31"><ss:Column ss:AutoFitWidth="1" ss:Width="1000" /><ss:Column ss:AutoFitWidth="1" ss:Width="75" /><ss:Column ss:AutoFitWidth="1" ss:Width="75" /><ss:Column ss:AutoFitWidth="1" ss:Width="75" /><ss:Column ss:AutoFitWidth="1" ss:Width="85" /><ss:Row ss:Height="38"><ss:Cell ss:StyleID="title" ss:MergeAcross="4"><ss:Data xmlns:html="http://www.w3.org/TR/REC-html40" ss:Type="String"><html:B><html:U><html:Font html:Size="15">Array Grid</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"><ss:Cell ss:StyleID="headercell"><ss:Data ss:Type="String">Company</ss:Data><ss:NamedCell ss:Name="Print_Titles" /></ss:Cell><ss:Cell ss:StyleID="headercell"><ss:Data ss:Type="String">Price</ss:Data><ss:NamedCell ss:Name="Print_Titles" /></ss:Cell><ss:Cell ss:StyleID="headercell"><ss:Data ss:Type="String">Change</ss:Data><ss:NamedCell ss:Name="Print_Titles" /></ss:Cell><ss:Cell ss:StyleID="headercell"><ss:Data ss:Type="String">% Change</ss:Data><ss:NamedCell ss:Name="Print_Titles" /></ss:Cell><ss:Cell ss:StyleID="headercell"><ss:Data ss:Type="String">Last Updated</ss:Data><ss:NamedCell ss:Name="Print_Titles" /></ss:Cell></ss:Row><ss:Row><ss:Cell ss:StyleID="even"><ss:Data ss:Type="String">3m Co</ss:Data></ss:Cell><ss:Cell ss:StyleID="evenfloat"><ss:Data ss:Type="Number">71.72</ss:Data></ss:Cell><ss:Cell ss:StyleID="evenfloat"><ss:Data ss:Type="Number">0.02</ss:Data></ss:Cell><ss:Cell ss:StyleID="evenfloat"><ss:Data ss:Type="Number">0.03</ss:Data></ss:Cell><ss:Cell ss:StyleID="evendate"><ss:Data ss:Type="DateTime">2009-09-01</ss:Data></ss:Cell></ss:Row><ss:Row><ss:Cell ss:StyleID="odd"><ss:Data ss:Type="String">Alcoa Inc</ss:Data></ss:Cell><ss:Cell ss:StyleID="oddfloat"><ss:Data ss:Type="Number">29.01</ss:Data></ss:Cell><ss:Cell ss:StyleID="oddfloat"><ss:Data ss:Type="Number">0.42</ss:Data></ss:Cell><ss:Cell ss:StyleID="oddfloat"><ss:Data ss:Type="Number">1.47</ss:Data></ss:Cell><ss:Cell ss:StyleID="odddate"><ss:Data ss:Type="DateTime">2009-09-01</ss:Data></ss:Cell></ss:Row><ss:Row><ss:Cell ss:StyleID="even"><ss:Data ss:Type="String">Altria Group Inc</ss:Data></ss:Cell><ss:Cell ss:StyleID="evenfloat"><ss:Data ss:Type="Number">83.81</ss:Data></ss:Cell><ss:Cell ss:StyleID="evenfloat"><ss:Data ss:Type="Number">0.28</ss:Data></ss:Cell><ss:Cell ss:StyleID="evenfloat"><ss:Data ss:Type="Number">0.34</ss:Data></ss:Cell><ss:Cell ss:StyleID="evendate"><ss:Data ss:Type="DateTime">2009-09-01</ss:Data></ss:Cell></ss:Row><ss:Row><ss:Cell ss:StyleID="odd"><ss:Data ss:Type="String">American Express Company</ss:Data></ss:Cell><ss:Cell ss:StyleID="oddfloat"><ss:Data ss:Type="Number">52.55</ss:Data></ss:Cell><ss:Cell ss:StyleID="oddfloat"><ss:Data ss:Type="Number">0.01</ss:Data></ss:Cell><ss:Cell ss:StyleID="oddfloat"><ss:Data ss:Type="Number">0.02</ss:Data></ss:Cell><ss:Cell ss:StyleID="odddate"><ss:Data ss:Type="DateTime">2009-09-01</ss:Data></ss:Cell></ss:Row><ss:Row><ss:Cell ss:StyleID="even"><ss:Data ss:Type="String">American International Group, Inc.</ss:Data></ss:Cell><ss:Cell ss:StyleID="evenfloat"><ss:Data ss:Type="Number">64.13</ss:Data></ss:Cell><ss:Cell ss:StyleID="evenfloat"><ss:Data ss:Type="Number">0.31</ss:Data></ss:Cell><ss:Cell ss:StyleID="evenfloat"><ss:Data ss:Type="Number">0.49</ss:Data></ss:Cell><ss:Cell ss:StyleID="evendate"><ss:Data ss:Type="DateTime">2009-09-01</ss:Data></ss:Cell></ss:Row><ss:Row><ss:Cell ss:StyleID="odd"><ss:Data ss:Type="String">AT&T Inc.</ss:Data></ss:Cell><ss:Cell ss:StyleID="oddfloat"><ss:Data ss:Type="Number">31.61</ss:Data></ss:Cell><ss:Cell ss:StyleID="oddfloat"><ss:Data ss:Type="Number">-0.48</ss:Data></ss:Cell><ss:Cell ss:StyleID="oddfloat"><ss:Data ss:Type="Number">-1.54</ss:Data></ss:Cell><ss:Cell ss:StyleID="odddate"><ss:Data ss:Type="DateTime">2009-09-01</ss:Data></ss:Cell></ss:Row><ss:Row><ss:Cell ss:StyleID="even"><ss:Data ss:Type="String">Boeing Co.</ss:Data></ss:Cell><ss:Cell ss:StyleID="evenfloat"><ss:Data ss:Type="Number">75.43</ss:Data></ss:Cell><ss:Cell ss:StyleID="evenfloat"><ss:Data ss:Type="Number">0.53</ss:Data></ss:Cell><ss:Cell ss:StyleID="evenfloat"><ss:Data ss:Type="Number">0.71</ss:Data></ss:Cell><ss:Cell ss:StyleID="evendate"><ss:Data ss:Type="DateTime">2009-09-01</ss:Data></ss:Cell></ss:Row><ss:Row><ss:Cell ss:StyleID="odd"><ss:Data ss:Type="String">Caterpillar Inc.</ss:Data></ss:Cell><ss:Cell ss:StyleID="oddfloat"><ss:Data ss:Type="Number">67.27</ss:Data></ss:Cell><ss:Cell ss:StyleID="oddfloat"><ss:Data ss:Type="Number">0.92</ss:Data></ss:Cell><ss:Cell ss:StyleID="oddfloat"><ss:Data ss:Type="Number">1.39</ss:Data></ss:Cell><ss:Cell ss:StyleID="odddate"><ss:Data ss:Type="DateTime">2009-09-01</ss:Data></ss:Cell></ss:Row><ss:Row><ss:Cell ss:StyleID="even"><ss:Data ss:Type="String">Citigroup, Inc.</ss:Data></ss:Cell><ss:Cell ss:StyleID="evenfloat"><ss:Data ss:Type="Number">49.37</ss:Data></ss:Cell><ss:Cell ss:StyleID="evenfloat"><ss:Data ss:Type="Number">0.02</ss:Data></ss:Cell><ss:Cell ss:StyleID="evenfloat"><ss:Data ss:Type="Number">0.04</ss:Data></ss:Cell><ss:Cell ss:StyleID="evendate"><ss:Data ss:Type="DateTime">2009-09-01</ss:Data></ss:Cell></ss:Row><ss:Row><ss:Cell ss:StyleID="odd"><ss:Data ss:Type="String">E.I. du Pont de Nemours and Company</ss:Data></ss:Cell><ss:Cell ss:StyleID="oddfloat"><ss:Data ss:Type="Number">40.48</ss:Data></ss:Cell><ss:Cell ss:StyleID="oddfloat"><ss:Data ss:Type="Number">0.51</ss:Data></ss:Cell><ss:Cell ss:StyleID="oddfloat"><ss:Data ss:Type="Number">1.28</ss:Data></ss:Cell><ss:Cell ss:StyleID="odddate"><ss:Data ss:Type="DateTime">2009-09-01</ss:Data></ss:Cell></ss:Row><ss:Row><ss:Cell ss:StyleID="even"><ss:Data ss:Type="String">Exxon Mobil Corp</ss:Data></ss:Cell><ss:Cell ss:StyleID="evenfloat"><ss:Data ss:Type="Number">68.1</ss:Data></ss:Cell><ss:Cell ss:StyleID="evenfloat"><ss:Data ss:Type="Number">-0.43</ss:Data></ss:Cell><ss:Cell ss:StyleID="evenfloat"><ss:Data ss:Type="Number">-0.64</ss:Data></ss:Cell><ss:Cell ss:StyleID="evendate"><ss:Data ss:Type="DateTime">2009-09-01</ss:Data></ss:Cell></ss:Row><ss:Row><ss:Cell ss:StyleID="odd"><ss:Data ss:Type="String">General Electric Company</ss:Data></ss:Cell><ss:Cell ss:StyleID="oddfloat"><ss:Data ss:Type="Number">34.14</ss:Data></ss:Cell><ss:Cell ss:StyleID="oddfloat"><ss:Data ss:Type="Number">-0.08</ss:Data></ss:Cell><ss:Cell ss:StyleID="oddfloat"><ss:Data ss:Type="Number">-0.23</ss:Data></ss:Cell><ss:Cell ss:StyleID="odddate"><ss:Data ss:Type="DateTime">2009-09-01</ss:Data></ss:Cell></ss:Row><ss:Row><ss:Cell ss:StyleID="even"><ss:Data ss:Type="String">General Motors Corporation</ss:Data></ss:Cell><ss:Cell ss:StyleID="evenfloat"><ss:Data ss:Type="Number">30.27</ss:Data></ss:Cell><ss:Cell ss:StyleID="evenfloat"><ss:Data ss:Type="Number">1.09</ss:Data></ss:Cell><ss:Cell ss:StyleID="evenfloat"><ss:Data ss:Type="Number">3.74</ss:Data></ss:Cell><ss:Cell ss:StyleID="evendate"><ss:Data ss:Type="DateTime">2009-09-01</ss:Data></ss:Cell></ss:Row><ss:Row><ss:Cell ss:StyleID="odd"><ss:Data ss:Type="String">Hewlett-Packard Co.</ss:Data></ss:Cell><ss:Cell ss:StyleID="oddfloat"><ss:Data ss:Type="Number">36.53</ss:Data></ss:Cell><ss:Cell ss:StyleID="oddfloat"><ss:Data ss:Type="Number">-0.03</ss:Data></ss:Cell><ss:Cell ss:StyleID="oddfloat"><ss:Data ss:Type="Number">-0.08</ss:Data></ss:Cell><ss:Cell ss:StyleID="odddate"><ss:Data ss:Type="DateTime">2009-09-01</ss:Data></ss:Cell></ss:Row><ss:Row><ss:Cell ss:StyleID="even"><ss:Data ss:Type="String">Honeywell Intl Inc</ss:Data></ss:Cell><ss:Cell ss:StyleID="evenfloat"><ss:Data ss:Type="Number">38.77</ss:Data></ss:Cell><ss:Cell ss:StyleID="evenfloat"><ss:Data ss:Type="Number">0.05</ss:Data></ss:Cell><ss:Cell ss:StyleID="evenfloat"><ss:Data ss:Type="Number">0.13</ss:Data></ss:Cell><ss:Cell ss:StyleID="evendate"><ss:Data ss:Type="DateTime">2009-09-01</ss:Data></ss:Cell></ss:Row><ss:Row><ss:Cell ss:StyleID="odd"><ss:Data ss:Type="String">Intel Corporation</ss:Data></ss:Cell><ss:Cell ss:StyleID="oddfloat"><ss:Data ss:Type="Number">19.88</ss:Data></ss:Cell><ss:Cell ss:StyleID="oddfloat"><ss:Data ss:Type="Number">0.31</ss:Data></ss:Cell><ss:Cell ss:StyleID="oddfloat"><ss:Data ss:Type="Number">1.58</ss:Data></ss:Cell><ss:Cell ss:StyleID="odddate"><ss:Data ss:Type="DateTime">2009-09-01</ss:Data></ss:Cell></ss:Row><ss:Row><ss:Cell ss:StyleID="even"><ss:Data ss:Type="String">International Business Machines</ss:Data></ss:Cell><ss:Cell ss:StyleID="evenfloat"><ss:Data ss:Type="Number">81.41</ss:Data></ss:Cell><ss:Cell ss:StyleID="evenfloat"><ss:Data ss:Type="Number">0.44</ss:Data></ss:Cell><ss:Cell ss:StyleID="evenfloat"><ss:Data ss:Type="Number">0.54</ss:Data></ss:Cell><ss:Cell ss:StyleID="evendate"><ss:Data ss:Type="DateTime">2009-09-01</ss:Data></ss:Cell></ss:Row><ss:Row><ss:Cell ss:StyleID="odd"><ss:Data ss:Type="String">Johnson & Johnson</ss:Data></ss:Cell><ss:Cell ss:StyleID="oddfloat"><ss:Data ss:Type="Number">64.72</ss:Data></ss:Cell><ss:Cell ss:StyleID="oddfloat"><ss:Data ss:Type="Number">0.06</ss:Data></ss:Cell><ss:Cell ss:StyleID="oddfloat"><ss:Data ss:Type="Number">0.09</ss:Data></ss:Cell><ss:Cell ss:StyleID="odddate"><ss:Data ss:Type="DateTime">2009-09-01</ss:Data></ss:Cell></ss:Row><ss:Row><ss:Cell ss:StyleID="even"><ss:Data ss:Type="String">JP Morgan & Chase & Co</ss:Data></ss:Cell><ss:Cell ss:StyleID="evenfloat"><ss:Data ss:Type="Number">45.73</ss:Data></ss:Cell><ss:Cell ss:StyleID="evenfloat"><ss:Data ss:Type="Number">0.07</ss:Data></ss:Cell><ss:Cell ss:StyleID="evenfloat"><ss:Data ss:Type="Number">0.15</ss:Data></ss:Cell><ss:Cell ss:StyleID="evendate"><ss:Data ss:Type="DateTime">2009-09-01</ss:Data></ss:Cell></ss:Row><ss:Row><ss:Cell ss:StyleID="odd"><ss:Data ss:Type="String">McDonald's Corporation</ss:Data></ss:Cell><ss:Cell ss:StyleID="oddfloat"><ss:Data ss:Type="Number">36.76</ss:Data></ss:Cell><ss:Cell ss:StyleID="oddfloat"><ss:Data ss:Type="Number">0.86</ss:Data></ss:Cell><ss:Cell ss:StyleID="oddfloat"><ss:Data ss:Type="Number">2.4</ss:Data></ss:Cell><ss:Cell ss:StyleID="odddate"><ss:Data ss:Type="DateTime">2009-09-01</ss:Data></ss:Cell></ss:Row><ss:Row><ss:Cell ss:StyleID="even"><ss:Data ss:Type="String">Merck & Co., Inc.</ss:Data></ss:Cell><ss:Cell ss:StyleID="evenfloat"><ss:Data ss:Type="Number">40.96</ss:Data></ss:Cell><ss:Cell ss:StyleID="evenfloat"><ss:Data ss:Type="Number">0.41</ss:Data></ss:Cell><ss:Cell ss:StyleID="evenfloat"><ss:Data ss:Type="Number">1.01</ss:Data></ss:Cell><ss:Cell ss:StyleID="evendate"><ss:Data ss:Type="DateTime">2009-09-01</ss:Data></ss:Cell></ss:Row><ss:Row><ss:Cell ss:StyleID="odd"><ss:Data ss:Type="String">Microsoft Corporation</ss:Data></ss:Cell><ss:Cell ss:StyleID="oddfloat"><ss:Data ss:Type="Number">25.84</ss:Data></ss:Cell><ss:Cell ss:StyleID="oddfloat"><ss:Data ss:Type="Number">0.14</ss:Data></ss:Cell><ss:Cell ss:StyleID="oddfloat"><ss:Data ss:Type="Number">0.54</ss:Data></ss:Cell><ss:Cell ss:StyleID="odddate"><ss:Data ss:Type="DateTime">2009-09-01</ss:Data></ss:Cell></ss:Row><ss:Row><ss:Cell ss:StyleID="even"><ss:Data ss:Type="String">Pfizer Inc</ss:Data></ss:Cell><ss:Cell ss:StyleID="evenfloat"><ss:Data ss:Type="Number">27.96</ss:Data></ss:Cell><ss:Cell ss:StyleID="evenfloat"><ss:Data ss:Type="Number">0.4</ss:Data></ss:Cell><ss:Cell ss:StyleID="evenfloat"><ss:Data ss:Type="Number">1.45</ss:Data></ss:Cell><ss:Cell ss:StyleID="evendate"><ss:Data ss:Type="DateTime">2009-09-01</ss:Data></ss:Cell></ss:Row><ss:Row><ss:Cell ss:StyleID="odd"><ss:Data ss:Type="String">The Coca-Cola Company</ss:Data></ss:Cell><ss:Cell ss:StyleID="oddfloat"><ss:Data ss:Type="Number">45.07</ss:Data></ss:Cell><ss:Cell ss:StyleID="oddfloat"><ss:Data ss:Type="Number">0.26</ss:Data></ss:Cell><ss:Cell ss:StyleID="oddfloat"><ss:Data ss:Type="Number">0.58</ss:Data></ss:Cell><ss:Cell ss:StyleID="odddate"><ss:Data ss:Type="DateTime">2009-09-01</ss:Data></ss:Cell></ss:Row><ss:Row><ss:Cell ss:StyleID="even"><ss:Data ss:Type="String">The Home Depot, Inc.</ss:Data></ss:Cell><ss:Cell ss:StyleID="evenfloat"><ss:Data ss:Type="Number">34.64</ss:Data></ss:Cell><ss:Cell ss:StyleID="evenfloat"><ss:Data ss:Type="Number">0.35</ss:Data></ss:Cell><ss:Cell ss:StyleID="evenfloat"><ss:Data ss:Type="Number">1.02</ss:Data></ss:Cell><ss:Cell ss:StyleID="evendate"><ss:Data ss:Type="DateTime">2009-09-01</ss:Data></ss:Cell></ss:Row><ss:Row><ss:Cell ss:StyleID="odd"><ss:Data ss:Type="String">The Procter & Gamble Company</ss:Data></ss:Cell><ss:Cell ss:StyleID="oddfloat"><ss:Data ss:Type="Number">61.91</ss:Data></ss:Cell><ss:Cell ss:StyleID="oddfloat"><ss:Data ss:Type="Number">0.01</ss:Data></ss:Cell><ss:Cell ss:StyleID="oddfloat"><ss:Data ss:Type="Number">0.02</ss:Data></ss:Cell><ss:Cell ss:StyleID="odddate"><ss:Data ss:Type="DateTime">2009-09-01</ss:Data></ss:Cell></ss:Row><ss:Row><ss:Cell ss:StyleID="even"><ss:Data ss:Type="String">United Technologies Corporation</ss:Data></ss:Cell><ss:Cell ss:StyleID="evenfloat"><ss:Data ss:Type="Number">63.26</ss:Data></ss:Cell><ss:Cell ss:StyleID="evenfloat"><ss:Data ss:Type="Number">0.55</ss:Data></ss:Cell><ss:Cell ss:StyleID="evenfloat"><ss:Data ss:Type="Number">0.88</ss:Data></ss:Cell><ss:Cell ss:StyleID="evendate"><ss:Data ss:Type="DateTime">2009-09-01</ss:Data></ss:Cell></ss:Row><ss:Row><ss:Cell ss:StyleID="odd"><ss:Data ss:Type="String">Verizon Communications</ss:Data></ss:Cell><ss:Cell ss:StyleID="oddfloat"><ss:Data ss:Type="Number">35.57</ss:Data></ss:Cell><ss:Cell ss:StyleID="oddfloat"><ss:Data ss:Type="Number">0.39</ss:Data></ss:Cell><ss:Cell ss:StyleID="oddfloat"><ss:Data ss:Type="Number">1.11</ss:Data></ss:Cell><ss:Cell ss:StyleID="odddate"><ss:Data ss:Type="DateTime">2009-09-01</ss:Data></ss:Cell></ss:Row><ss:Row><ss:Cell ss:StyleID="even"><ss:Data ss:Type="String">Wal-Mart Stores, Inc.</ss:Data></ss:Cell><ss:Cell ss:StyleID="evenfloat"><ss:Data ss:Type="Number">45.45</ss:Data></ss:Cell><ss:Cell ss:StyleID="evenfloat"><ss:Data ss:Type="Number">0.73</ss:Data></ss:Cell><ss:Cell ss:StyleID="evenfloat"><ss:Data ss:Type="Number">1.63</ss:Data></ss:Cell><ss:Cell ss:StyleID="evendate"><ss:Data ss:Type="DateTime">2009-09-01</ss:Data></ss:Cell></ss:Row></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></ss:Workbook>

mjlecomte
16 Feb 2009, 6:01 PM
It opens.

NoahK17
16 Feb 2009, 7:59 PM
It opens.Lame. Well... I'm downloading a 60 day trial of MS Excel, so we'll see what happens...

But do you have any idea why I am getting .PART returned as the extension? I googled it, but nothing really came up.

mystix
16 Feb 2009, 9:53 PM
Lame. Well... I'm downloading a 60 day trial of MS Excel, so we'll see what happens...

But do you have any idea why I am getting .PART returned as the extension? I googled it, but nothing really came up.

.part is the file extension Firefox tacks on to in-progress downloads.
there should be an accompanying file without the .part extension lying around somewhere.

Animal
17 Feb 2009, 1:09 AM
OpenOffice uses McSoft's proprietary XLS format of spreadsheets.

It does not use the open, documented XML format.

Rafael
17 Feb 2009, 7:24 AM
Why this undefined in excel ?

I test with alert


alert(this.title);and return undefined .


On the Problem of .part, i I decided with the following code http://extjs.com/forum/showthread.php?p=188047#post188047

Animal
17 Feb 2009, 8:19 AM
The Grid's title?

Rafael
17 Feb 2009, 8:41 AM
yes

TheBim
20 Feb 2009, 6:06 AM
i'm trying to use the code in the post 1, but something goes wrong. I'm using an EditorGridPanel....can i use that code with EditorGridPanel?

emredagli
20 Feb 2009, 1:40 PM
Hii stevenhzj,
Firstly welcome to ExtJS world. :)
You said "store.data.length = 0", you should use getCount() method of store.
since "data" is config params.

stevenhzj
20 Feb 2009, 6:10 PM
hi emredagli
ye i use getCount() is also 0.
the store is through asynchronous get. so in store.load(), use getCount() is 0,
can i use callback to getCount() ? how to do? THK!

emredagli
21 Feb 2009, 4:37 AM
Mr. stevenhzj,
It is better to send your code.
It is hard to say what you sholud do without knowing what you are trying to do.

stevenhzj
21 Feb 2009, 6:52 PM
hi emredagli

this the button code:


buttons: [{
id: 'grid-excel-button',
text: 'Export to Excel...',
handler: function(){
var max = store.getCount();
var downstore = new Ext.data.Store({
url: 'http://localhost/ExtTestWebService/Service_Test.asmx/GetArticles', // Web Service Site
reader: new Ext.data.XmlReader(
{
totalRecords: 'totalRecords',
record: 'record',
id: 'id'
},
[
{name: 'id'},
{name: 'name'},
{name: 'code'},
{name: 'joindate'}
]
),
remoteSort: true // });

downstore.on('beforeload', function(downstore) {
downstore.baseParams={consql:v_consql};
} );

downstore.load({params:{start:0,limit:max}}); //
vExportContent = grid.getExcelXml(store.data,false);
exportExcell();
}
}],

another WebService Code:


[WebMethod]
public DataSet GetArticles(string consql, int start, int limit, string sort, string dir)
{
DataSet ds = new DataSet("Operator");

string strSql = string.Format(
"select top {0} * from sys$vw_operator where 1=1 " + consql + " and Id not in (select top {1} Id from sys$vw_operator order by {2} {3}) order by {2} {3}",
limit, start, sort, dir);

SqlDataAdapter da = new SqlDataAdapter(strSql, _strConn);
DataTable dtRecord = new DataTable("record");
lock (da)
{
da.Fill(dtRecord);
}
ds.Tables.Add(dtRecord);
DataTable dtResult = new DataTable("results");
dtResult.Columns.Add("totalRecords");
DataRow dr = dtResult.NewRow();
using (SqlConnection conn = new SqlConnection(_strConn))
using (SqlCommand cmd = new SqlCommand("select count(*) from sys$vw_operator where 1=1" + consql, conn))
{
try
{
conn.Open();
dr["totalRecords"] = (int)cmd.ExecuteScalar();
}
catch
{
// do nothing
}
}
dtResult.Rows.Add(dr);
ds.Tables.Add(dtResult);
return ds;
}

stevenhzj
22 Feb 2009, 5:48 AM
and i want know how use ext and ajaxpro2, i trying to use ,but then conflict.
in Ajaxpro, js code is easy call function c# mothod, i to bo used to.
but i can use in ext, anybody can help me , hope Demo Sample, Ths.

rakesh
22 Feb 2009, 10:18 AM
This Code may be helpful to generate excel file on server side.


public void generateExcelFile(List<? extends EkaListingDO> listingResults,
HttpServletResponse response, HttpServletRequest request,
String gridName) throws SystemException {

IUserContext userContext = this.getUserContext(request);

try {

String fileName = gridName + "-"
+ EkaDateUtil.getTimeStamp(new Date());

String dependentObj = (String) request.getParameter("dependentObj");

JSONObject jsonDependentObj = null;

if (EkaStringUtil.isNotEmpty(dependentObj)) {
jsonDependentObj = JSONObject.fromObject(dependentObj);
}

String columnModelState = (String) request
.getParameter("columnModelState");

columnModelState = columnModelState.replaceAll(
"EKA_INTERNAL_ID_AMP", "&");
columnModelState = columnModelState.replaceAll(
"EKA_INTERNAL_ID_HASH", "#");

OutputStream out = null;

response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment; filename="
+ fileName + ".xls");
WritableWorkbook w = Workbook.createWorkbook(response
.getOutputStream());
WritableSheet s = w.createSheet(gridName, 0);

JSONArray array = JSONArray.fromObject(columnModelState);

List<String> listProperties = new ArrayList<String>();

for (int i = 0; i < array.size(); i++) {

JSONObject object = (JSONObject) array.get(i);

listProperties.add(object.getString("dataIndex"));

s.addCell(new Label(i, 0, object.getString("header")));

}

String value = null;

if (!listProperties.isEmpty()) {

for (int i = 0; i < listingResults.size(); i++) {
EkaListingDO ekaListingDO = (EkaListingDO) listingResults
.get(i);

for (int j = 0; j < listProperties.size(); j++) {

value = (String) EkaBeanUtil.getBeanProperties(
ekaListingDO, listProperties.get(j));

if (EkaStringUtil.isNullOrEmpty(value)) {
value = "";
}

if (jsonDependentObj != null
&& jsonDependentObj.containsKey(listProperties
.get(j))) {
JSONArray array2 = (JSONArray) jsonDependentObj
.get(listProperties.get(j));

for (int k = 0; k < array2.size(); k++) {
JSONObject innObj = array2.getJSONObject(k);

String property = innObj.getString("dependsOn");
String separator = innObj
.getString("separator");

String dependentValue = (String) EkaBeanUtil
.getBeanProperties(ekaListingDO,
property);

if (EkaStringUtil.isNullOrEmpty(dependentValue)) {
dependentValue = "";
}
if (EkaStringUtil.isNotEmpty(value)) {
value = value + separator + dependentValue;
}

}

}

if (EkaStringUtil.isDoubleContent(value)
|| EkaStringUtil.isOnlyNumericContent(value)) {
s.addCell(new Number(j, i + 1, EkaStringUtil
.convertStringToDouble(value)));
} else {
s.addCell(new Label(j, i + 1, value));
}

}
}
}

w.write();
w.close();

if (out != null) {
out.close();

}

} catch (Exception e) {
logger.error(userContext, e);
}

}

NoahK17
24 Feb 2009, 1:29 PM
As an update to my previous issue with OpenOffice vs. MS Excel -- having MS Excel installed worked like a charm opening the generated XLS file. Sorry OpenOffice, you lose this round!

hardc0re
16 Mar 2009, 12:34 AM
bad post

Animal
16 Mar 2009, 12:43 AM
So you didn't' read the message, and try to find the mistake then? You just blurted your bug here.

READ the XML section which the error message kindly tells you about, and you see



<Worksheet ss:Name=". la 31.12.2008.xls]Contracte valide si in vigoare">


Excel seems not to like that.

READ the free code which generates that section, and you see:



// Generate worksheet header details.
var t = '<ss:Worksheet ss:Name="' + this.title + '">' +


Do some work.

hardc0re
16 Mar 2009, 1:00 AM
thanks i think i did it

dtondo
17 Mar 2009, 4:56 AM
http://img8.imageshack.us/img8/1170/imagemdfw.png

Hi, Ext users. I'm facing a problem with Excel 2000. It is possible to fix?

thanks.

Animal
18 Mar 2009, 4:58 AM
http://msdn.microsoft.com/en-us/library/aa140066(office.10).aspx

dtondo
20 Mar 2009, 6:16 AM
http://msdn.microsoft.com/en-us/library/aa140066(office.10).aspx (http://msdn.microsoft.com/en-us/library/aa140066%28office.10%29.aspx)

All though this code look a little complex i will try

Thanks Animal :D

Animal
20 Mar 2009, 6:44 AM
You were just supposed to read the bit that said "Applies to: Microsoft® Excel 2002"8-|

dtondo
20 Mar 2009, 6:59 AM
You were just supposed to read the bit that said "Applies to: Microsoft® Excel 2002"8-|

I-|I-|I-| ...

Hummm, there is another way to make this functionality(Export to Excel) work's on Excel 2000?

Animal
20 Mar 2009, 7:01 AM
The XLS format is proprietary. Come on! Upgrade. Your Excel is getting to be a decade old. And that's a lifetime in software terms.

dtondo
20 Mar 2009, 9:37 AM
The XLS format is proprietary. Come on! Upgrade. Your Excel is getting to be a decade old. And that's a lifetime in software terms.

You are completely true. The hard part is try to explain this for my boss. :">


May i have to face the Lion ...

thanks for all! ;)

hzwei
22 Mar 2009, 12:31 AM
I use your method of gridpanel directly to excel ,But I found some questions in it:
------------------------------------------------------------------------------



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="yyyy-mm-dd" />' +
'</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="yyyy-mm-dd" />' +
'</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.getColumnModel();
var totalWidthInPixels = 0;
var colXml = '';
var headerXml = '';
var visibleColumnCountReduction = 0;
var colCount = cm.getColumnCount();
for (var i = 0; i < colCount; i++) {
if ((cm.getDataIndex(i) != '')
&& (includeHidden || !cm.isHidden(i))) {
var w = cm.getColumnWidth(i)
totalWidthInPixels += w;
if (cm.getColumnHeader(i) === ""){
cellType.push("None");
cellTypeClass.push("");
++visibleColumnCountReduction;
}
else
{
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 "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 - visibleColumnCountReduction;
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 + 2) +
'" 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>Generated by ExtJS</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 < colCount; j++) {
if ((cm.getDataIndex(j) != '')
&& (includeHidden || !cm.isHidden(j))) {
var v = r[cm.getDataIndex(j)];
if (cellType[k] !== "None") {
t += '<ss:Cell ss:StyleID="' + cellClass + cellTypeClass[k] + '"><ss:Data ss:Type="' + cellType[k] + '">';
if (cellType[k] == 'DateTime') {
t += v.format('Y-m-d');
} else {
t += v;
}
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;
}


this can only export gridpanel which has only header,but if the gridpanel has

multiple headers,the content of multiple headers can not be export,could you help me
solve this question?

Also,Could you post an example of a gridpanel which can be printed that also support for
multiple headers?

Thanks!

Animal
22 Mar 2009, 1:16 AM
"Multiple headers". I don't understand that.

"could not be export". I don't understand that either.

hzwei
22 Mar 2009, 4:26 AM
"Multiple headers" it means that the gridpanel which has GroupHeaderPlugin,for example:
var grid = new Ext.grid.GridPanel({
renderTo:"mylist",
width:1600,
store : ds,
colModel : cm,
region : 'center',
viewConfig: {
forceFit: true
},
border : true,
height :350,
plugins : [new Ext.ux.plugins.GroupHeaderGrid()],
tbar :tbar ,
listeners : {
"rowdblclick" : function(_grid, _rowindex, _e) {
var parentctl = Ext.getCmp("flashcontent");
var unitCode = _grid.view.getCell(_rowindex, 2).innerText;
//alert("unitCode is:"+unitCode);
displaygraphline(unitCode);
}
}
});
it can be export,but it only export the gridpanel which has not GroupHeader,the mehod you give can only export the gridpanel which has one level header,do you know what i mean?

Animal
22 Mar 2009, 4:32 AM
You have the code. All it does it concatenate up a glorified <table>

Have fun with it.

hzwei
22 Mar 2009, 5:17 AM
I am so sorry for my poor English!But can you know what I mean?
Could you help me solve theses questions?

Animal
22 Mar 2009, 8:17 AM
WHy can't you do it?

hzwei
22 Mar 2009, 4:36 PM
I don not know how to modify the code you give ,in my project I need to implement it,so can you help me?Thanks very much!

hastiok
23 Mar 2009, 9:00 AM
Dear Animal, thanks a lot for your example.
I'm now based on him to build a great part of my application .
However I have a small problem: The xsl file does not accept cell on my grid containing html tag and this is why it generate a bug
I give you exemple about theses cells:
Data[Park_List.length][j]='<font size="2"><b>'+summ+'</span>';
I need really a help.

thanks

hastiok
24 Mar 2009, 1:54 AM
I'm now thinking to use css with my grid as a possible solution that allow me to delete the HTM tag in my cells. But I don't know if it's possible to do that or not?
Can you help me please,
thanks

BitPoet
24 Mar 2009, 2:39 AM
Have a look at the documentation of setRenderer() in the Ext.grid.ColumnModel docs. The second parameter to the renderer function is a meta object whose "css" and "attr" properties you can change to format the cell content.

hastiok
24 Mar 2009, 3:05 AM
thank you for your reply.
I see your proposotion and it's a good slolution when we would like to render a column, but in my case I would like to render only the final row.:(
Until now I didn't find any solution allowing the use of css with an EXTJS grid ( without using HTML file as an
intermediate) : Besides the solution proposed by BitPoet :-)
can you help me?Thanks very much!

Animal
24 Mar 2009, 6:01 AM
This is all in the docs.

Want to style a column? Give it an ID!

http://extjs.com/deploy/dev/docs/?class=Ext.grid.ColumnModel&member=id

Animal
24 Mar 2009, 6:02 AM
Want to style a row?

http://extjs.com/deploy/dev/docs/?class=Ext.grid.GridView&member=getRowClass

hastiok
24 Mar 2009, 6:07 AM
Yes exactly , I want to style a particular row on my gridPanel using css.
I used Html tag on each cell of my row but it generate a bug when creating the xsl file (file to be opened by Excel).
can you help me?Thanks very much!

Animal
24 Mar 2009, 6:20 AM
Follow the link!!!!!!!

hastiok
24 Mar 2009, 7:14 AM
I followed the link that you send to me and I used this code but it does not work:

grid.getView().getRowClass = function(record, index){
return (index==6 ? 'blue-row' : ''); //6 is the number of the last row of my grid
};

:((:((:((

hastiok
24 Mar 2009, 8:28 AM
it is the right code but I did an error in the css file.:">

Finaly I have a good gridPanel with a colored Line using css file and it can be exported to Excel:D .


thank you: BitPoet and Animal;)

csqjean
26 Mar 2009, 1:11 AM
gridToExcel.js

fld is undefined

chrome://firebug/content/blank.gif switch(fld.type) {


??
pease tell me


Email:zhoushangbin@gmail.com

jsundquist
7 Apr 2009, 10:07 AM
Where might I find the where the colors are stored for the rows and headers?

Never mind found them.

Rafael
11 Apr 2009, 10:45 PM
gridToExcel.js

fld is undefined

chrome://firebug/content/blank.gif switch(fld.type) {


??
pease tell me


Email:zhoushangbin@gmail.com



Probably you are using sm: new Ext.grid.CheckboxSelectionModel (). He substitutes for sm: new Ext.grid.RowSelectionModel ({singleSelect: true}).

I had a same error and trade with him.

huling
26 Apr 2009, 10:53 PM
not support chinese character

BitPoet
27 Apr 2009, 5:07 AM
I'm using it without problems with chinese (utf8) characters. Make sure you have all involved files in the right encoding and the server sends the correct headers too.

Naokai
28 Apr 2009, 2:29 AM
Hi Animal thats an amazing Extension!

I have a Question is it possible to export only the selected Rows in a Grid?
and not the complete store.

Lars

tinakonda
30 Apr 2009, 11:07 AM
Emredagli,
Can u please tell me How did you add Group Headers to the SpreadSheet to work.

Thank you,
Tina.

TopKatz
6 May 2009, 10:01 AM
This working in 3.x. However OO does not like the output.

emredagli
6 May 2009, 10:53 AM
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');
}

emredagli
6 May 2009, 11:09 AM
I forgot to add attachment:
This is the output of my code. Excel file.

wayned@escc
18 May 2009, 1:36 AM
Hey - in the code on p1 - there seems to be a bug:




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.getColumnCount(); j++) {
if (includeHidden || !cm.isHidden(j)) {
var v = r[cm.getDataIndex(j)];
t += '<ss:Cell ss:StyleID="' + cellClass + cellTypeClass[k] + '"><ss:Data ss:Type="' + cellType[j] + '">';
if (cellType[k] == 'DateTime') {
t += v.format('Y-m-d');
} else {
t += v;
}
t +='</ss:Data></ss:Cell>';
k++;
}
}
t += '</ss:Row>';
}



Shouldn't that be:



t += '<ss:Cell ss:StyleID="' + cellClass + cellTypeClass[k] + '"><ss:Data ss:Type="' + cellType[k] + '">';


?

w://

Animal
19 May 2009, 3:57 AM
Indeed it should be. Well spotted. I've edited the post to match your fix. Thanks.

wayned@escc
19 May 2009, 7:20 AM
Just wondering if anyone had figured out a way to do grouping on the data?

w://

Animal
19 May 2009, 7:44 AM
It's certainly possible.

You'd just have to put a value change test where you loop through the store, and on change, insert a <ss:Row><ss:Cell colspan="?"> with some data in it.

I think it uses colspan. Check the docs I linked to ni the first post.

Animal
19 May 2009, 7:46 AM
http://msdn.microsoft.com/en-us/library/aa140066(office.10).aspx

it's ss:MergeAcross that you'd use to do a colspan.

Dumas
21 May 2009, 5:34 AM
Hi!

I'm allways getting that Ext.LinkButton is not a constructor, any ideas what's wrong?

thx
Dumas

tobiu
10 Jun 2009, 8:50 AM
hi nige,

i tested your ux a bit more (i told you i like it before, but again: nice work!).

i am using firefox3.1 and data that should be utf-8 inside the datastores.

when exporting to excel, the part



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


made different results in the output with btoa and the directly posted method.
in btoa seems to be just an encode to base64 without the utf8encode() before. so, with btoa special characters got "destroyed".

i just removed it therefore ;)



encode : function (input) {
var output = "";
var chr1, chr2, chr3, enc1, enc2, enc3, enc4;
var i = 0;
input = utf8Encode(input);
...


the other thing is, that gridPlugins like rowNumberer cause troubles. as a kind of hotfix i count from column 1 instead of 0 all the times (since i always use the plugin).

kind regards, tobiu

Boccara Jonathan
14 Jun 2009, 11:51 PM
Hello Animal.
I try to use your code with my GridPanel where the data are loaded with Ext.data.HttpProxy.
When I click on the button Excel I have the following error with firebug :

[Exception... "'<error>' when calling method: [nsIContentHandler::handleContent]" nsresult: "0x805d0001 (<unknown>)" location: "<unknown>" data: no]
Can you help me to resolve it.

Thank you.

vietschv
29 Jun 2009, 4:14 PM
Does anybody know why the export2excel plugin causes problems with non-europpean characters, like russian ones? I thought that this should not be a problem using utg8 encoding.

Thanks for any help,
vietschv

naranda
7 Jul 2009, 12:16 PM
Hi, I tried the next code for export to excel and runs on IE7, IE8 and FF






function export(dato){
if (!Ext.fly('frmDummy')) {
var frm = document.createElement('form');
frm.id = 'frmDummy';
frm.name = id;
frm.className = 'x-hidden';
document.body.appendChild(frm);
}
Ext.Ajax.request({
url: 'export_excel.php',
method : 'POST',
form: Ext.fly('frmDummy'),
callback: function(o, s, r){
alert(r.responseText);
},
isUpload: true,
params: { ex: dato }
});

}



bbar in grid:


bbar: new Ext.Toolbar({
buttons: [{
id: 'grid-excel-button',
text: 'Exportar a Excel',
handler: function(){

var exportContent =grid.getExcelXml();

export(exportContent);
}
}]
}),


See ya!!


PD: my english is bad

Galileo_Galilei
15 Jul 2009, 4:42 AM
Hy guys. It's been a while from the first recording of this problem:
Default Error in IE8
Hi All,

Export to Excel works fine in FF, but in IE8 I get this error -

Message: The data area passed to a system call is too small.

Can anybody please tell me how to fix this?

and I still face this. Dear Animal, are u kind to give us an update about this? Stil no fix for it? Your last update about this:
Report it to McSoft. I would guess it's an IE error caused by using a data: URL. If it's a large dataset, then the BASE64 encoding of an XML spreadsheet will be large. The whole content is encoded into the href of a link as the URL. Looks like IE8 just cannot cope.
Maybe I missed something from the thread?
Thank you for your constant support!

Joyfulbob
4 Aug 2009, 6:47 AM
thx,emredagli.the code is ok.:D

[/code]my jsp:

<% response.setHeader("Pragma","public");
response.setHeader("Expires","0");
response.setHeader("Cache-Control","must-revalidate, post-check=0, pre-check=0");
response.setHeader("Content-Type","application/force-download");
response.setHeader("Content-Type","application/vnd.ms-excel");
response.setHeader("Content-Disposition","attachment;filename="+request.getParameter("FileName"));

out.print(request.getParameter("ex"));
%>
:D:D

Thanks for this update. I ran it in IE7 and I get that 'data too small' error. I don't have jsp's; am using an .htm file; can you suggest equivalent code for the quoted above?
Thanks in advance!

teraphy
11 Aug 2009, 9:53 AM
Hy guys. It's been a while from the first recording of this problem:
Default Error in IE8
Hi All,

Export to Excel works fine in FF, but in IE8 I get this error -

Message: The data area passed to a system call is too small.

Can anybody please tell me how to fix this?

and I still face this. Dear Animal, are u kind to give us an update about this? Stil no fix for it? Your last update about this:
Report it to McSoft. I would guess it's an IE error caused by using a data: URL. If it's a large dataset, then the BASE64 encoding of an XML spreadsheet will be large. The whole content is encoded into the href of a link as the URL. Looks like IE8 just cannot cope.
Maybe I missed something from the thread?
Thank you for your constant support!

The first post is a little misleading. I assumed it would work in IE8 due to the first comment. I then skimmed the 21 pages to see if there was any response to a positive IE8, now I suspect no one can get it working in IE8. After quite a bit of testing and searching to figure this out it appears IE8 doesn't support data URIs for this situation.

IE8 only allows for data URIs in img tags and CSS. I simply tested by attempting to enter the data directly into the navigation bar with no luck for IE8 and I finally found this Mozilla article to confirm it: https://developer.mozilla.org/en/The_data_URL_scheme

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

Data URIs cannot be used for navigation

Jack_S
17 Aug 2009, 12:31 AM
Hello All,

I seem to be getting errors when I open up the worksheet in Excel. I've reviewed the log files and found the following.

I've recently upgraded all my grids to 3.0 to take advantage on some of the new features.

I'm using Office 2007, including the Formating pack and still I can't get this plugin to work with Grids 3.0.

Can anybody offer any assistance?? Here is snippet of my log file.

Thanks in advance.

Jack



XML ERROR in Table
REASON: Bad Value
FILE: C:\Users\JanS.OSWORILOCAL\Desktop\something.xls
GROUP: Cell
TAG: Data
ATTRIB: Type
VALUE: undefined

XML ERROR in Table
REASON: Bad Value
FILE: C:\Users\JanS.OSWORILOCAL\Desktop\something.xls
GROUP: Row
TAG: Cell
ATTRIB: StyleID
VALUE: evenundefined

XML ERROR in Table
REASON: Bad Value
FILE: C:\Users\JanS.OSWORILOCAL\Desktop\something.xls
GROUP: Cell
TAG: Data
ATTRIB: Type
VALUE: undefined

XML ERROR in Table
REASON: Bad Value
FILE: C:\Users\JanS.OSWORILOCAL\Desktop\something.xls
GROUP: Row
TAG: Cell
ATTRIB: StyleID
VALUE: oddundefined


I take it that it has to do with the part of the code which generates the cell data. I have not found anything but it seems that "odd" and "even" are undefined.



// 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.getColumnCount(); j++) {
if (includeHidden || !cm.isHidden(j)) {
var v = r[cm.getDataIndex(j)];
if (cellType[k] !== "None") {

t += '<ss:Cell ss:StyleID="' + cellClass + cellTypeClass[k] + '"><ss:Data ss:Type="' + cellType[k] + '">';
if (cellType[k] == 'DateTime') {
t += v.format('d/m/Y');
} else {
t += v;
}
t +='</ss:Data></ss:Cell>';
}
k++;
}
}
t += '</ss:Row>';
}

teraphy
17 Aug 2009, 6:25 AM
I think you're missing the styles that define even/odd.


'<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>' +

Jack_S
19 Aug 2009, 2:52 AM
Hi All,

Found what the issue was, the header were ignoring the cellRenders such as NumberedRow/CheckBox Slection, but inside the data is was actually counting from the rendered cell which means that the last cell was always undefined.

I used one of the simple fixes suggested by one of the posters and started to count from 1 rather then zero.

Thanks

Jack



//Around line 220
// ORIGINAL
// 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 = 1; j < cm.getColumnCount(); j++) {

bobjbain
16 Sep 2009, 3:16 AM
Hi, I tried the next code for export to excel and runs on IE7, IE8 and FF






function export(dato){
if (!Ext.fly('frmDummy')) {
var frm = document.createElement('form');
frm.id = 'frmDummy';
frm.name = id;
frm.className = 'x-hidden';
document.body.appendChild(frm);
}
Ext.Ajax.request({
url: 'export_excel.php',
method : 'POST',
form: Ext.fly('frmDummy'),
callback: function(o, s, r){
alert(r.responseText);
},
isUpload: true,
params: { ex: dato }
});

}

bbar in grid:


bbar: new Ext.Toolbar({
buttons: [{
id: 'grid-excel-button',
text: 'Exportar a Excel',
handler: function(){

var exportContent =grid.getExcelXml();

export(exportContent);
}
}]
}),
See ya!!


PD: my english is bad

naranda, I take it your PHP just takes the xml stream passed to it (via the POST) and saves it as a local file?

Could you maybe post the PHP too (would be helpful being a PHP noob)

Cheers

Pekka Karalahti
6 Oct 2009, 11:09 PM
I have been testing these issues 4 hours now and it isnt working. If somebody has working example of hole code where file name can be changed, it would be great. I am using .js files and java servlet also. Please tell me if there is easier way to do csv file from grid with these options. I havent found...

alexb
16 Oct 2009, 10:57 PM
The following code works with RowNumberer or without it

It also works for grids with columns that don't have corresponding fields in the underlying store.



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, {
headerBgColor: '#FFFFFF',
evenRowBgColor: '#FFFFFF',
oddRowBgColor: '#FFFFFF',
getExcelXml: function(includeHidden) {
var worksheet = this.createWorksheet(includeHidden);
var totalWidth = this.getColumnModel().getTotalWidth(includeHidden);
var border = '<ss:Borders>' +
'<ss:Border ss:Color="#a0a0a0" ss:Weight="1" ss:LineStyle="Continuous" ss:Position="Top" />' +
'<ss:Border ss:Color="#a0a0a0" ss:Weight="1" ss:LineStyle="Continuous" ss:Position="Bottom" />' +
'<ss:Border ss:Color="#a0a0a0" ss:Weight="1" ss:LineStyle="Continuous" ss:Position="Left" />' +
'<ss:Border ss:Color="#a0a0a0" ss:Weight="1" ss:LineStyle="Continuous" ss:Position="Right" />' +
'</ss:Borders>';

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" />' +
border +
'<ss:Interior ss:Pattern="Solid" ss:Color="'+this.headerBgColor+'" />' +
'</ss:Style>' +
'<ss:Style ss:ID="even">' +
border +
'<ss:Interior ss:Pattern="Solid" ss:Color="'+this.evenRowBgColor+'" />' +
'</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">' +
border +
'<ss:Interior ss:Pattern="Solid" ss:Color="'+this.oddRowBgColor+'" />' +
'</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.getColumnModel();
var totalWidthInPixels = 0;
var colXml = '';
var headerXml = '';
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));
if(!fld){
fld = {type:''};
}
switch(fld.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: this.getInnerWidth() //Math.floor(totalWidthInPixels * 30) + 50
width: Math.floor(totalWidthInPixels * 30) + 50
};

var isNumber = function (val)
{
// we need to explicitly handle null values
// because isNaN returns false when it should return true
if (null == val) return false;

// if it is an empty string or a string with just spaces
// isNaN returns false, but we really need it to return true
// this expression will remove spaces if the given value is a string type
if (typeof(val) == "string")
val = val.replace(/\s*/g, "");

if (val == "") return false;

return !isNaN(val);
};

// 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 (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.getColumnCount(); j++) {
if (includeHidden || !cm.isHidden(j)) {
var v;
var renderer = cm.getRenderer(j);
if(!renderer){
v = r[cm.getDataIndex(j)];
t += '<ss:Cell ss:StyleID="' + cellClass + cellTypeClass[k] + '"><ss:Data ss:Type="' + cellType[k] + '">';
if (cellType[k] == 'DateTime') {
t += v.format('Y-m-d');
} else {
t += v;
}
t +='</ss:Data></ss:Cell>';
}else{
v = renderer(r[cm.getDataIndex(j)], null, it[i], i);

var cType = !(isNumber(v))? 'String' : 'Number';
t += '<ss:Cell ss:StyleID="' + cellClass + '"><ss:Data ss:Type="'+cType+'">';
t += v;


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

MrRoyce
20 Oct 2009, 7:14 AM
(EDIT - ALL fixed! - I was using an 'editorgrid' xtype for my display grid. I changed it to use a 'new Ext.grid.EditorGridPanel' and it now works correctly)

Hi there, I am trying this on 3.0 with FF 3.5. When I click on the "export to Excel' button I get an error if firebug - grid.getExcelXml is not a function

I included the ExportGridToExcel.js file (I can see it when I view source - and the link is clikable)

I'm using the following toolbar (bbar: pagingToolBar)

Any ideas why I would get that error?


var pagingToolbar = {
xtype : 'paging',
store : remoteJsonStore,
pageSize : limit,
displayInfo : true,
items : [
'-',
{
text : 'Save Changes',
handler : onSave
},
'-',
{
text : 'Reject Changes',
handler : onRejectChanges
},
{
id: 'grid-excel-button',
text : 'Export to Excel...',
handler : function(){
document.location='data:application/vnd.ms-excel;base64,' + Base64.encode(grid.getExcelXml());}
},
'-'
]
}Thanks.

Spongerusher
11 Dec 2009, 11:14 AM
This needs a browser that supports data URLs. FF, Opera and IE8 will support this.
Drop this file as examples/grid/array-grid.js:

Hi, seems that IE8 not supports this plugin, i cant make it works with the array-grid example

More info here : http://www.extjs.com/forum/showthread.php?p=416029#post416029

megmefleg
16 Dec 2009, 3:01 AM
Hi,

For you information: the excel export function proposed by Animal (thanks a lot) works very well with FF but not with IE8.

Why?

In fact, if you look at the MSDN Documentation (http://go.microsoft.com/fwlink/?LinkId=125789), there are some different worried points:

"
Data URIs are supported only for the following elements and/or attributes.


object (http://msdn.microsoft.com/en-us/library/ms535859%28VS.85%29.aspx) (images only)
img (http://msdn.microsoft.com/en-us/library/ms535259%28VS.85%29.aspx)
input type=image (http://msdn.microsoft.com/en-us/library/ms535836%28VS.85%29.aspx)
link (http://msdn.microsoft.com/en-us/library/ms534119%28VS.85%29.aspx)
CSS declarations that accept a URL, such as background (http://msdn.microsoft.com/en-us/library/ms530722%28VS.85%29.aspx), backgroundImage (http://msdn.microsoft.com/en-us/library/ms530717%28VS.85%29.aspx), and so on.

Data URIs cannot be larger than 32,768 characters."

So, the "data:application/vnd.ms-excel;" will not work with IE8but only with images (data:image/png).:((

Pekka Karalahti
4 Jan 2010, 10:11 PM
I am getting error in Firebug, "fld is undefined", any ideas why i am getting this ? Grid is normal gridPanel.

stevieke
5 Jan 2010, 6:26 AM
Hello everyone

May i first say this is amazing work ;)
I'm trying to implement this, and it works in ff, but not in IE8

I always get the error: the data area passed to a system is too small.

I understand that this is something with the url: the url from internet explorer is too small for all my data. That's why it's work in ff but not in ie

Now,my question is: Can I do something that this also works in ie?

Thanx in advance
Steven

PS: Sorry for my poor english

tBSTAR
8 Jan 2010, 1:25 PM
Hi,

I tried the code to export a grid but I get this error: this.store.recordType is undefined
in line 154: var fld = this.store.rec...totype.fields.get(cm.getDataIndex(i));\r\n

I don't know why my recordType is undefined, any clue?

Sergii
11 Jan 2010, 12:08 AM
(EDIT - ALL fixed! - I was using an 'editorgrid' xtype for my display grid. I changed it to use a 'new Ext.grid.EditorGridPanel' and it now works correctly)

Hi there, I am trying this on 3.0 with FF 3.5. When I click on the "export to Excel' button I get an error if firebug - grid.getExcelXml is not a function

I included the ExportGridToExcel.js file (I can see it when I view source - and the link is clikable)

I'm using the following toolbar (bbar: pagingToolBar)

Any ideas why I would get that error?

var pagingToolbar = {
xtype : 'paging',
store : remoteJsonStore,
pageSize : limit,
displayInfo : true,
items : [
'-',
{
text : 'Save Changes',
handler : onSave
},
'-',
{
text : 'Reject Changes',
handler : onRejectChanges
},
{
id: 'grid-excel-button',
text : 'Export to Excel...',
handler : function(){
document.location='data:application/vnd.ms-excel;base64,' + Base64.encode(grid.getExcelXml());}
},
'-'
]
}Thanks.

Hi!
I have same problem.
Firebug info:



RefResListingEditorGrid.getExcelXml is not a function
chrome://firebug/content/blank.gif Base64.encode(RefResListingEditorGrid.getExcelXml());\r\n
My code:


// Grid from XML table

var RefResListingEditorGrid = new Ext.ux.grid.TableGrid("reference_res", {

id: 'static-grid',
title: 'Reference Results',
iconCls: 'icon-user-reference-result',
autoHeight: false,
height: MaxHeight - 1,
stripeRows: true,
autoscroll: true,

tbar: new Ext.Toolbar ({
buttons: [{
// Export button to Excel
id: 'grid-excel-button',
text: 'Export',
iconCls: 'icon-user-menu-export',
handler: function(){
document.location = 'data:application/vnd.ms-excel;base64,' +
Base64.encode(RefResListingEditorGrid.getExcelXml());
}
},{
// Import button from 1C
id: 'grid-1c-button',
text: 'Import',
iconCls: 'icon-user-menu-import',
handler: function(){
displayFormWindowImportRefRes();
}
}]
})
});
I read all 22 pages (GridPanel directly to Excel) but it don't help me for solved this problem.
Please help!:((

Pekka Karalahti
11 Jan 2010, 1:46 AM
Same problem still and i have tried to change everything, store, grid, readers... I am desperate. I have debug the code and it writes first column/header okay into csv file but when code is looping to second header it gives error fld is undefined.

vizcano
13 Jan 2010, 4:50 AM
I've got a problem trying to display hours, in da DateTime cell i've replaced t += v.format('Y-m-d'); for t += v.format('G:i:s'); because what i want to show is the hour and my excel crashes.

Another problem i'm dealing with is when de DateTime cell doesnt contain any value, the new excel crashes again saying "incorrect value" for those cells.

Could anyone help me with this problems?

myth
25 Jan 2010, 8:09 AM
thank for the thread i find all info y need to export a excel ( i need to read 2 o 3 times :"> but the infornation is here =D> )

Cassio11
13 Feb 2010, 5:47 AM
First of all, thanks for this great plugin!

However I ran into some trouble when I try to open the Excel-File.
It gives me an an error and directs me to the Log-File:

XML PARSE ERROR: Missing end-tag
Error occurs at or below this element stack:
<ss:Workbook>
<ss:Worksheet>
<ss:Table>
<ss:Row>
<ss:Cell>
<ss:Data>

None of my XML-Validators where able to find syntax errors.
I really ran into a wall here...


Edit:
Yay! Fixed it myself.
The cause were non-UTF-8 characters in the document, while my page uses iso-8859-1 - so I edited the xml header part in the exporttoexcel.js to ISO-8859-1.

StagnantIce
17 Mar 2010, 1:52 AM
Work fast in IE6 and IE7



createWorksheet: function(includeHidden) {

// 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 dataIndexes = [];

for (var i = 0, len = cm.getColumnCount(); i < len; 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 di = cm.getDataIndex(i);
dataIndexes.push(di);
var fld = this.store.recordType.prototype.fields.get(di);
switch(fld.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 A = [];

A.push('<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>');
for (var i = 0, it = this.store.data.items, l = it.length; i < l; i++) {
A.push('<ss:Row>');
var cellClass = (i & 1) ? 'odd' : 'even';
r = it[i].data;
var k = 0;
for (var j = 0, len = dataIndexes.length; j < len; j++) {
var v = r[dataIndexes[j]];
A.push('<ss:Cell ss:StyleID="' + cellClass + cellTypeClass[k] + '"><ss:Data ss:Type="' + cellType[k] + '">');
if (cellType[k] == 'DateTime') {
A.push(v.format('Y-m-d'));
} else {
A.push(v);
}
A.push('</ss:Data></ss:Cell>');
k++;
}
A.push('</ss:Row>');
}
A.push ('</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>');
result.xml = A.join('');
return result;

StagnantIce
17 Mar 2010, 3:36 AM
In IE send Ajax request with params is a bad case.

var data = grid.getExcelXml();
if ((!Ext.isIE7)&&(!Ext.isIE6)) document.location = 'data:application/vnd.ms-excel;base64,' + Base64.encode(data);
else
{
if (!Ext.fly('frmDummy'))
{
var frm = document.createElement('form');
frm.id = 'frmDummy';
frm.name = id;
frm.className = 'x-hidden';

var inp = document.createElement('input');
inp.type = "hidden";
inp.value = data;
inp.id = "data";
inp.name = "data"
frm.appendChild(inp);

document.body.appendChild(frm);
}
Ext.Ajax.request({
url: 'excel.php',
method : 'POST',
form: Ext.fly('frmDummy'),
callback: function(o, s, r){
},
isUpload: true
//params: { data: data }
});
}

shailendra
8 Apr 2010, 5:35 AM
I'm using FF3 on a Windows machine.

I'm using this extension and so far it works very well except for two small things.

1. The filename seems to be a string of random letters.
2. When I try to save the file, the extension is weird - it shows up as .xls.part, and then when I actually save the file it adds another .xls to it so the filename ends up looking like this:

XR9oqU0s.xls.part.xls

The file opens fine, I'm just wondering if this has happened to anyone else?

Hi!
I just entered here
Its happening to me also, any solution you found pls let me know

shailendra
8 Apr 2010, 5:38 AM
Thank you for sharing your code!

But Error ! IE7:
The data area passed to a system call is too small.



Hi!
sorry but i m also getting the same error in IE8
any solution ?
thanks

calavera
30 Apr 2010, 8:46 AM
Hi!
I just entered here
Its happening to me also, any solution you found pls let me know

Me to. I get the weird file name and when I try to open with Microsoft Excel, it gives me an error at "Table". Don't know more of it. Tested on Firefox 3.6.3 . Any solution ? Thanks.

Skunkerbr
11 May 2010, 11:27 AM
Hi..

I noticed that in Ext 3.2.1, the line:


var fld = this.store.recordType.prototype.fields.get(cm.getDataIndex(i));
..returns a Object type with another type in it. So now fld.type must be replace with fld.type.type in the switch to handle the field type right.



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;
}
Anyone getting the same on 3.2.1 ? Or just me

KrullWarKing
19 Jun 2010, 10:07 AM
Cant one just deliver the html to the client specifying a mime type of

application/vnd.ms-excel

Then anyone that has excel on their machine will open a simple table html as an excel worksheet.

:-/ I may be wrong here but this just seems like it should work :-/

maybe run in ajax or whatever
http://www.the-art-of-web.com/php/dataexport/

spor
24 Jun 2010, 12:55 AM
Hi..

I noticed that in Ext 3.2.1, the line:


var fld = this.store.recordType.prototype.fields.get(cm.getDataIndex(i));
..returns a Object type with another type in it. So now fld.type must be replace with fld.type.type in the switch to handle the field type right.



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;
}
Anyone getting the same on 3.2.1 ? Or just me

Hi Skunkerbr, that's interesting.

I had to change from


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


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

Otherwise it will fail, returning null. And this is right before the part that you addressed, which means that you didn't get the error that i got.

What about in your solution?

Skunkerbr
24 Jun 2010, 11:26 AM
Hi Skunkerbr, that's interesting.

I had to change from


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


var fld = this.store.recordType.prototype.fields.get(i);Otherwise it will fail, returning null. And this is right before the part that you addressed, which means that you didn't get the error that i got.

What about in your solution?

Hi...

I just changed the type mentioned earlier. Nothing more.
And is working fine.

Did your code just stoped on a version change? or you have started right on 3.2.x and are getting the null thing?

spor
24 Jun 2010, 11:30 PM
I started right on 3.2.0. How about you? Strange if nobody has encountered the same issue as me...

Animal
25 Jun 2010, 12:50 AM
Drop this into examples/<anywhere>

It works with Ext 3.2.



<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>&#160;</i></td><td class="x-btn-tc"></td><td class="x-btn-tr"><i>&#160;</i></td></tr>',
'<tr>',
'<td class="x-btn-ml"><i>&#160;</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>&#160;</i></td>',
'</tr>',
'<tr><td class="x-btn-bl"><i>&#160;</i></td><td class="x-btn-bc"></td><td class="x-btn-br"><i>&#160;</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 = [];
var cellTypeClass = [];
var cm = this.getColumnModel();
var totalWidthInPixels = 0;
var colXml = '';
var headerXml = '';
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 (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.getColumnCount(); j++) {
if (includeHidden || !cm.isHidden(j)) {
var v = r[cm.getDataIndex(j)];
t += '<ss:Cell ss:StyleID="' + cellClass + cellTypeClass[k] + '"><ss:Data ss:Type="' + cellType[j] + '">';
if (cellType[k] == 'DateTime') {
t += v.format('Y-m-d');
} else {
t += v;
}
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;
}
});

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>

spor
25 Jun 2010, 3:51 AM
I just tried with your code too, and it obviously works for your embedded grid. I have tried it on 3 of my own grids, 2 with data from webservices and one from local store and with checkboxes on the first row. They all worked when I modified to


var fld = this.store.recordType.prototype.fields.get(i); With the unmodified code, the local store based grid with checkboxes and the one of the other grids didn't work. One reason is that cm.getDataIndex(i) will return null on some indexes if it isnt mapped properly in the ColumnModel, e.g dataIndex. The checkbox also caused null value, not sure yet how to solve this issue.

Also,

encode : (typeof btoa == 'func... btoa(input); } : function (input) { has to be modified to

encode : function (input) {Otherwise you will get the error String contains an invalid character" code: "5.

SantaBarbarian
27 Jun 2010, 6:36 AM
Thanks Animal. This is a beautiful addition.

I'm wondering if anyone understands why this doesn't work for IE8 or Chrome (it does work in Firefox 3.6). The 'Excel' LinkButton is there and when I mouse over it, excel shows the right ip for downloading it (data:application/vnd.ms-excel;base64,...), however if I click the LinkButton nothing happens.

I'm using the ext-3.2.1 example posted just above by Animal without any changes.

spor
27 Jun 2010, 6:59 AM
It doesn't work with IE8. You get the "data is too small" error message if I remember correctly.

SantaBarbarian
27 Jun 2010, 10:42 AM
It doesn't work with IE8. You get the "data is too small" error message if I remember correctly.

IE8 doesn't get that far for me. The LinkButton doesn't press.

spor
28 Jun 2010, 12:47 AM
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)?

SantaBarbarian
28 Jun 2010, 5:24 AM
Strange. My copy of IE8 (running on vista) won't press the button. Neither will Chrome.

spor
28 Jun 2010, 5:30 AM
I suggest that you paste your code here.

SantaBarbarian
28 Jun 2010, 5:34 AM
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
12 Jul 2010, 10:17 AM
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
12 Jul 2010, 1:12 PM
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
16 Jul 2010, 4:54 AM
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
16 Jul 2010, 7:25 AM
Hi Animal,

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

Centurus
30 Jul 2010, 12:53 AM
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
12 Aug 2010, 12:55 AM
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
7 Sep 2010, 2:56 AM
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/library/cc848897%28VS.85%29.aspx

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