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:

Code:

/**
 * @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 :

Code:

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

But i am interrested if another way exist

**lu_pp** · 21 Aug 2008 7:11 PM

Originally Posted by jelt

I have encountered same problem on my own project.

Solved by using this kind of code :

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

Originally Posted by mjhaston

My button isn't showing up in my grid. I'm assuming it should be in the tool bar?

Include this in your code

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:

Code:

// 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

GridPanel to real Excel file(Only By IE5+,Windows,Office)

Thread Tools

Search Thread

Display

GridPanel to real Excel file(Only By IE5+,Windows,Office)

GridPanel to real Excel file(Only By IE5+,Windows,Office)