Hybrid View

  1. #1
    Sencha User
    Join Date
    Jul 2008
    Posts
    12
    Vote Rating
    0
    kimmking is on a distinguished road

      0  

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

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


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

  2. #2
    Sencha User
    Join Date
    Jul 2008
    Posts
    12
    Vote Rating
    0
    kimmking is on a distinguished road

      0  

    Default


    Is everyone testing the demo?
    Or have a idea about it?

  3. #3
    Sencha User
    Join Date
    Aug 2007
    Posts
    64
    Vote Rating
    0
    jelt is on a distinguished road

      0  

    Default


    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)

  4. #4
    Ext User
    Join Date
    Aug 2008
    Posts
    2
    Vote Rating
    0
    lu_pp is on a distinguished road

      0  

    Default


    used it on IE7
    if (fld.type == 'date')

    "type" is null or not object

  5. #5
    Sencha User
    Join Date
    Aug 2007
    Posts
    64
    Vote Rating
    0
    jelt is on a distinguished road

      0  

    Default


    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

  6. #6
    Ext User
    Join Date
    Aug 2008
    Posts
    2
    Vote Rating
    0
    lu_pp is on a distinguished road

      0  

    Default


    Quote Originally Posted by jelt View Post
    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