PDA

View Full Version : GridPanel to real Excel file(Only By IE5+,Windows,Office)



kimmking
24 Jul 2008, 9:57 AM
GridPanel to real Excel file(Only By IE5+,Windows,Office)


What ?
There is only one static function in the object Ext.ux.Grid2Excel ,
You can use it anywhere to convert the data from a gridpanel to a local real excel file.

Where ?
It use a object(ActiveXObject("Excel.application") to create a real excel file,so it works at only windows platform with the office software installed.And then modifying the settings of the IE security to activate the activex may be necessary too.

How ?
Like this:
Ext.ux.Grid2Excel.Save2Excel(grid);

Core Code List and Demo

grid2excel.js:

/**
* @author qinjinwei
*
* time: 2008-7-24 20:28:02
*/
var idTmr = "";
Ext.ux.Grid2Excel = {

Save2Excel : function(grid)
{
var cm = grid.getColumnModel();
var store = grid.getStore();

var it = store.data.items;
var rows = it.length;

var oXL = new ActiveXObject("Excel.application");
var oWB = oXL.Workbooks.Add();
var oSheet = oWB.ActiveSheet;

for (var i = 0; i < cm.getColumnCount(); i++) {

if (!cm.isHidden(i)) {
oSheet.Cells(1, i + 1).value = cm.getColumnHeader(i);
}

for (var j = 0; j < rows; j++) {
r = it[j].data;
var v = r[cm.getDataIndex(i)];
var fld = store.recordType.prototype.fields.get(cm.getDataIndex(i));
if(fld.type == 'date')
{
v = v.format('Y-m-d');
}

oSheet.Cells(2 + j, i + 1).value = v;
}
}
oXL.DisplayAlerts = false;
oXL.Save();
oXL.DisplayAlerts = true;
oXL.Quit();
oXL = null;
idTmr = window.setInterval("Cleanup();",1);
}
};
function Cleanup() {
window.clearInterval(idTmr);
CollectGarbage();
};


Demo :
[CODE]<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<link rel="stylesheet" type="text/css" href="ext/resources/css/ext-all.css">
<link rel="stylesheet" type="text/css" href="./ext/resources/css/ext-all.css">
<script type="text/javascript" src="./ext/adapter/ext/ext-base.js">
</script>
<script type="text/javascript" src="./ext/ext-all-debug.js">
</script>
<script type="text/javascript" src="grid2excel.js">
</script>
</head>
<body>
<script type="text/javascript">
Ext.onReady(function(){

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

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

// create the Grid
var grid = new Ext.grid.GridPanel({
id: 'static-grid',
store: store,
renderTo: 'grid-example',
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,
dataIndex: 'change'
}, {
header: "% Change",
width: 75,
sortable: true,
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: [{
text: '

kimmking
25 Jul 2008, 4:29 AM
Is everyone testing the demo?
Or have a idea about it?

jelt
25 Jul 2008, 6:52 AM
Great :)

I do similar, but hardcoded with some enhancement (like auto filter on the header line, border, colors... specific to my project requirement).

I read your code and have some questions :
- you let an excel column empty when hidden in grid ?
- dont you use the renderer function ? data sent to excel are in internal format ? (except date)

lu_pp
20 Aug 2008, 10:18 PM
used it on IE7
if (fld.type == 'date')

"type" is null or not object

jelt
21 Aug 2008, 1:00 AM
I have encountered same problem on my own project.

Solved by using this kind of code :


if (typeof(v) == 'object')


But i am interrested if another way exist :)

lu_pp
21 Aug 2008, 7:11 PM
I have encountered same problem on my own project.

Solved by using this kind of code :


if (typeof(v) == 'object')
But i am interrested if another way exist :)

thx,jelt

mjhaston
9 Dec 2008, 12:55 PM
My button isn't showing up in my grid. I'm assuming it should be in the tool bar?

abhilashca
17 Apr 2011, 3:47 AM
My button isn't showing up in my grid. I'm assuming it should be in the tool bar?

Include this in your code


// Toolbar
var tb = new Ext.Toolbar({
items: [{
xtype: 'button',
text: 'Export to Excel (ActiveX)',
handler: function() {
Ext.ux.Grid2Excel.Save2Excel(grid);
}
}]
});

And inside your 'grid', include this field also:



// create the Grid
var grid = new Ext.grid.GridPanel({
store: store,
tbar: tb,
// Rest of the grid fields

This will bring a Toolbar to the top, along with a Button with text: "Export to Excel (ActiveX)"

anuragchaturvedi
11 May 2012, 2:49 AM
Hi,
I am using "var oXL = new ActiveXObject("Excel.Application");" this code and goting error "SCRIPT429: Automation server can't create object".
can you help me?

abhilashca
11 May 2012, 5:38 PM
Add your website url to Trusted Sites and choose Custom Levels. Inside that, enable ActiveX settings for Unsigned ActiveX.

Thanks