Export the grid to excel

Page 1 of 4 123 ... LastLast
  1. #1

    Export the grid to excel

    Hi,
    I am exporting gridpanel data to excel sheet. I implemented as per this example
    https://examples1.ext.net/#/GridPane...Data_PostBack/

    It is working fine. But this example exporting recordfield names as column headers in the excel sheet.
    I want to export the gridheaders as headers to excel.

    for example my store recordfield name is "val" but my grid header name for this recordfield is "Sales Value(K$)". Here i want "Sales Value(K$)" as a header in the excel sheet but i am getting "val" in the excel sheet.

    I guess exporting grid headers makes sense to the user.

    I don't want to change my datatable column names...Can u give me some suggestions how to acieve this.
    Last edited by Mr.Techno; Mar 22, 2012 at 10:41 AM.
  2. #2
    can anyone suggest me how to solve the above problem...


    Thank you.
  3. #3
    Hi,

    Well, there is no special thing in Ext.NET to export a grid data to an Excel file. If there is a problem in this part, i.e. creating and "writing" an Excel file, it would be best to ask on some specialized forums.

    Though I can help to retrieve a required data from a grid. So, could you reformulate your question in this aspect?

    A sample would be appreciated.
  4. #4
    Hi Danill,

    I dont have any problem in exporting to excel sheet. Everything is working fine.
    My porblem is Showing GridPanel headers as headers in the exported excel sheet, instead of store record field name as headers in the excel sheet.

    Modifying the sample from the above provided link (in first post)

    <%@ Page Language="C#" %>
    
    <%@ Import Namespace="System.Xml.Xsl" %>
    <%@ Import Namespace="System.Xml" %>
    
    <%@ Register Assembly="Ext.Net" Namespace="Ext.Net" TagPrefix="ext" %>
    
    <script runat="server">
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!this.IsPostBack)
            {
                this.Store1.DataSource = new object[]
                {
                    new object[] { 1, "3m Co", 71.72, 0.02, 0.03, "9/1 12:00am" },
                    new object[] { 2, "Alcoa Inc", 29.01, 0.42, 1.47, "9/1 12:00am" },
                    new object[] { 3, "Altria Group Inc", 83.81, 0.28, 0.34, "9/1 12:00am" },
                    new object[] { 4, "American Express Company", 52.55, 0.01, 0.02, "9/1 12:00am" },
                    new object[] { 5, "American International Group, Inc.", 64.13, 0.31, 0.49, "9/1 12:00am" },
                    new object[] { 6, "AT&T Inc.", 31.61, -0.48, -1.54, "9/1 12:00am" },
                    new object[] { 7, "Boeing Co.", 75.43, 0.53, 0.71, "9/1 12:00am" },
                    new object[] { 8, "Caterpillar Inc.", 67.27, 0.92, 1.39, "9/1 12:00am" },
                    new object[] { 9, "Citigroup, Inc.", 49.37, 0.02, 0.04, "9/1 12:00am" },
                    new object[] { 10, "E.I. du Pont de Nemours and Company", 40.48, 0.51, 1.28, "9/1 12:00am" },
                    new object[] { 11, "Exxon Mobil Corp", 68.1, -0.43, -0.64, "9/1 12:00am" },
                    new object[] { 12, "General Electric Company", 34.14, -0.08, -0.23, "9/1 12:00am" },
                    new object[] { 13, "General Motors Corporation", 30.27, 1.09, 3.74, "9/1 12:00am" },
                    new object[] { 14, "Hewlett-Packard Co.", 36.53, -0.03, -0.08, "9/1 12:00am" },
                    new object[] { 15, "Honeywell Intl Inc", 38.77, 0.05, 0.13, "9/1 12:00am" },
                    new object[] { 16, "Intel Corporation", 19.88, 0.31, 1.58, "9/1 12:00am" },
                    new object[] { 17, "International Business Machines", 81.41, 0.44, 0.54, "9/1 12:00am" },
                    new object[] { 18, "Johnson & Johnson", 64.72, 0.06, 0.09, "9/1 12:00am" },
                    new object[] { 19, "JP Morgan & Chase & Co", 45.73, 0.07, 0.15, "9/1 12:00am" },
                    new object[] { 20, "McDonald\"s Corporation", 36.76, 0.86, 2.40, "9/1 12:00am" },
                    new object[] { 21, "Merck & Co., Inc.", 40.96, 0.41, 1.01, "9/1 12:00am" },
                    new object[] { 22, "Microsoft Corporation", 25.84, 0.14, 0.54, "9/1 12:00am" },
                    new object[] { 23, "Pfizer Inc", 27.96, 0.4, 1.45, "9/1 12:00am" },
                    new object[] { 24, "The Coca-Cola Company", 45.07, 0.26, 0.58, "9/1 12:00am" },
                    new object[] { 25, "The Home Depot, Inc.", 34.64, 0.35, 1.02, "9/1 12:00am" },
                    new object[] { 26, "The Procter & Gamble Company", 61.91, 0.01, 0.02, "9/1 12:00am" },
                    new object[] { 27, "United Technologies Corporation", 63.26, 0.55, 0.88, "9/1 12:00am" },
                    new object[] { 28, "Verizon Communications", 35.57, 0.39, 1.11, "9/1 12:00am" },
                    new object[] { 29, "Wal-Mart Stores, Inc.", 45.45, 0.73, 1.63, "9/1 12:00am" }
                };
    
                this.Store1.DataBind();
            }
        }
    
        protected void ToExcel(object sender, EventArgs e)
        {
            string json = GridData.Value.ToString();
            StoreSubmitDataEventArgs eSubmit = new StoreSubmitDataEventArgs(json, null);
            XmlNode xml = eSubmit.Xml;
    
            this.Response.Clear();
            this.Response.ContentType = "application/vnd.ms-excel";
            this.Response.AddHeader("Content-Disposition", "attachment; filename=submittedData.xls");
            XslCompiledTransform xtExcel = new XslCompiledTransform();
            xtExcel.Load(Server.MapPath("Excel.xsl"));
            xtExcel.Transform(xml, null, this.Response.OutputStream);
            this.Response.End();
        }
    
    </script>
    
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" 
        "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
        <title>Export Data from GridPanel into XML, Excel or CSV using a full PostBack - Ext.NET Examples</title>
        <link href="../../../../resources/css/examples.css" rel="stylesheet" type="text/css" />    
        
        <script type="text/javascript">
            var saveData = function () {
                GridData.setValue(Ext.encode(GridPanel1.getRowsValues({selectedOnly : false})));
            };
        </script>
    </head>
    <body>
        <form runat="server">
            <ext:ResourceManager runat="server" />
            
            <h1>Export Data from GridPanel into XML, Excel or CSV using a full PostBack</h1>
            
            <ext:Hidden ID="GridData" runat="server" />
            
            <ext:Store ID="Store1" runat="server">
                <Reader>
                    <ext:ArrayReader>
                        <Fields>
                            <ext:RecordField Name="id" Type="Int" />
                            <ext:RecordField Name="company" />
                            <ext:RecordField Name="price" Type="Float" />
                            <ext:RecordField Name="change" Type="Float" />
                            <ext:RecordField Name="pctChange" Type="Float" />
                            <ext:RecordField Name="lastChange" Type="Date" DateFormat="M/d hh:mmtt" />
                        </Fields>
                    </ext:ArrayReader>
                </Reader>
                <DirectEventConfig IsUpload="true" />
            </ext:Store>        
            
            <ext:GridPanel 
                ID="GridPanel1" 
                runat="server" 
                StoreID="Store1" 
                StripeRows="true"
                Title="Export Data" 
                TrackMouseOver="true"
                Width="600" 
                Height="350"
                AutoExpandColumn="Company">
                <ColumnModel runat="server">
                    <Columns>
                        <ext:Column ColumnID="Company" Header="Col1-Show this in Excel" Width="160" DataIndex="company" />
                        <ext:Column Header="Price" Width="75" DataIndex="price">
                            <Renderer Format="UsMoney" />
                        </ext:Column>
                        <ext:Column Header="Col2-Show this in Excel" Width="75" DataIndex="change" />
                        <ext:Column Header="Col3-Show this in Excel" Width="75" DataIndex="pctChange" />
                        <ext:DateColumn Header="Col4-Show this in Excel" Width="85" DataIndex="lastChange" />
                    </Columns>
                </ColumnModel>
                <SelectionModel>
                    <ext:RowSelectionModel runat="server" />
                </SelectionModel>
                <TopBar>
                    <ext:Toolbar ID="Toolbar1" runat="server">
                        <Items>
                            <ext:ToolbarFill ID="ToolbarFill1" runat="server" />
                           
                            <ext:Button runat="server" Text="To Excel" AutoPostBack="true" OnClick="ToExcel" Icon="PageExcel">
                                <Listeners>
                                    <Click Fn="saveData" />
                                </Listeners>
                            </ext:Button>
                            
                        </Items>
                    </ext:Toolbar>
                </TopBar>
            </ext:GridPanel>  
        </form>
    </body>
    </html>
    please run the above example. In the above example i changed the gridpanel headers like "Col-Show this in Excel".

    Currently, after exporting i am able to see a table with table headers same as store record field Names, but i want gridpanel headers as table headers in the exported excel sheet.
  5. #5
    Can anyone tell me how to do this?
  6. #6
    I can suggest the following,

    Example
    var saveData = function () {
        var data = GridPanel1.getRowsValues({selectedOnly : false}),
            columns = GridPanel1.getColumnModel().config,
            headers = {
                id : "ID_header"
            };
    
        Ext.each(columns, function (c) {
            headers[c.dataIndex] = c.header.replace(/ /g, "_");
        });
    
        Ext.each(data, function (record) {
            for (var field in record) {
                record[headers[field]] = record[field];
                delete record[field];
            }
        });
    
        GridData.setValue(Ext.encode(data));
    };
  7. #7
    Hi Danill,

    Thanks for the reply

    Quote Originally Posted by Daniil View Post
    I can suggest the following,

    Example
        Ext.each(data, function (record) {
            for (var field in record) {
                record[headers[field]] = record[field];
                delete record[field];
            }
        });
     
    };
    This part has some problem. First it is replacing store record field names with grid headers and it is not stopping there. It is deleting modified records so finally we get empty data.

    Run the below code. Click on export to excel button. You will get empty data.
    <%@ Page Language="C#" MasterPageFile="~/Main.master" AutoEventWireup="true" CodeFile="ExportToExcel.aspx.cs" Inherits="_1_TestPages_ExportToExcel" Title="Untitled Page" %>
    
    <%@ Register Assembly="Ext.Net" Namespace="Ext.Net" TagPrefix="ext" %>
    <asp:Content ID="Content1" ContentPlaceHolderID="headContentPlaceHolder" Runat="Server">
    <ext:XScript runat="server">
    <script type="text/javascript">
    //var saveData = function () {
    //            #{GridData}.setValue(Ext.encode(#{GridPanel1}.getRowsValues({selectedOnly : false})));
    //        };
            var saveData = function () {
        var data = #{GridPanel1}.getRowsValues({selectedOnly : false}),
            columns = #{GridPanel1}.getColumnModel().config,
            headers = {
                id : "ID_header"
            };
     
        Ext.each(columns, function (c) {
            headers[c.dataIndex] = c.header.replace(/ /g, "_");
        });
     
        Ext.each(data, function (record) {
            for (var field in record) {
                record[headers[field]] = record[field];
                delete record[field];
            }
        });
     
        #{GridData}.setValue(Ext.encode(data));
    };
    </script>
    </ext:XScript>
    </asp:Content>
    <asp:Content ID="Content2" ContentPlaceHolderID="mainContentPlaceHolder" Runat="Server">
    <ext:ResourceManager ID="ResourceManager2" runat="server" />
    <ext:Hidden ID="GridData" runat="server" />
            
            <ext:Store ID="Store1" runat="server">
                <Reader>
                    <ext:ArrayReader>
                        <Fields>
                            <ext:RecordField Name="id" Type="Int" />
                            <ext:RecordField Name="company" />
                            <ext:RecordField Name="price" Type="Float" />
                            <ext:RecordField Name="change" Type="Float" />
                            <ext:RecordField Name="pctChange" Type="Float" />
                            <ext:RecordField Name="lastChange" Type="Date" DateFormat="M/d hh:mmtt" />
                        </Fields>
                    </ext:ArrayReader>
                </Reader>
                <DirectEventConfig IsUpload="true" />
            </ext:Store>        
            
            <ext:GridPanel 
                ID="GridPanel1" 
                runat="server" 
                StoreID="Store1" 
                StripeRows="true"
                Title="Export Data" 
                TrackMouseOver="true"
                Width="600" 
                Height="350"
                AutoExpandColumn="Company">
                <ColumnModel ID="ColumnModel1" runat="server">
                    <Columns>
                        <ext:Column ColumnID="Company" Header="Company-ExportThis" Width="160" DataIndex="company" />
                        <ext:Column Header="Price" Width="75" DataIndex="price">
                            <Renderer Format="UsMoney" />
                        </ext:Column>
                        <ext:Column Header="Change-ExportThis" Width="75" DataIndex="change" />
                        <ext:Column Header="Pct.Change-ExportThis" Width="75" DataIndex="pctChange" />
                        <ext:DateColumn Header="Last Updated-ExportThis" Width="85" DataIndex="lastChange" />
                    </Columns>
                </ColumnModel>
                <SelectionModel>
                    <ext:RowSelectionModel ID="RowSelectionModel1" runat="server" />
                </SelectionModel>
                <TopBar>
                    <ext:Toolbar ID="Toolbar1" runat="server">
                        <Items>
                            <ext:ToolbarFill ID="ToolbarFill1" runat="server" />
                            
                            <ext:Button ID="Button1" runat="server" Text="To XML" AutoPostBack="true" OnClick="ToXml" Icon="PageCode">
                                <Listeners>
                                    <Click Fn="saveData" />
                                </Listeners>
                            </ext:Button>
                            
                            <ext:Button ID="Button2" runat="server" Text="To Excel" AutoPostBack="true" OnClick="ToExcel" Icon="PageExcel">
                                <Listeners>
                                    <Click Fn="saveData" />
                                </Listeners>
                            </ext:Button>
                            
                            <ext:Button ID="Button3" runat="server" Text="To CSV" AutoPostBack="true" OnClick="ToCsv" Icon="PageAttach">
                                <Listeners>
                                    <Click Fn="saveData" />
                                </Listeners>
                            </ext:Button>
                        </Items>
                    </ext:Toolbar>
                </TopBar>
            </ext:GridPanel>  
    </asp:Content>
    code behind
    using System;
    using System.Collections;
    using System.Configuration;
    using System.Data;
    using System.Linq;
    using System.Web;
    using System.Web.Security;
    using System.Web.UI;
    using System.Web.UI.HtmlControls;
    using System.Web.UI.WebControls;
    using System.Web.UI.WebControls.WebParts;
    using System.Xml.Linq;
    using Ext.Net;
    using System.Xml;
    using System.Xml.Xsl;
    
    public partial class _1_TestPages_ExportToExcel : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!this.IsPostBack)
            {
                this.Store1.DataSource = new object[]
                {
                    new object[] { 1, "3m Co", 71.72, 0.02, 0.03, "9/1 12:00am" },
                    new object[] { 2, "Alcoa Inc", 29.01, 0.42, 1.47, "9/1 12:00am" },
                    new object[] { 3, "Altria Group Inc", 83.81, 0.28, 0.34, "9/1 12:00am" },
                    new object[] { 4, "American Express Company", 52.55, 0.01, 0.02, "9/1 12:00am" },
                    new object[] { 5, "American International Group, Inc.", 64.13, 0.31, 0.49, "9/1 12:00am" },
                    new object[] { 6, "AT&T Inc.", 31.61, -0.48, -1.54, "9/1 12:00am" },
                    new object[] { 7, "Boeing Co.", 75.43, 0.53, 0.71, "9/1 12:00am" },
                    new object[] { 8, "Caterpillar Inc.", 67.27, 0.92, 1.39, "9/1 12:00am" },
                    new object[] { 9, "Citigroup, Inc.", 49.37, 0.02, 0.04, "9/1 12:00am" },
                    new object[] { 10, "E.I. du Pont de Nemours and Company", 40.48, 0.51, 1.28, "9/1 12:00am" },
                    new object[] { 11, "Exxon Mobil Corp", 68.1, -0.43, -0.64, "9/1 12:00am" },
                    new object[] { 12, "General Electric Company", 34.14, -0.08, -0.23, "9/1 12:00am" },
                    new object[] { 13, "General Motors Corporation", 30.27, 1.09, 3.74, "9/1 12:00am" },
                    new object[] { 14, "Hewlett-Packard Co.", 36.53, -0.03, -0.08, "9/1 12:00am" },
                    new object[] { 15, "Honeywell Intl Inc", 38.77, 0.05, 0.13, "9/1 12:00am" },
                    new object[] { 16, "Intel Corporation", 19.88, 0.31, 1.58, "9/1 12:00am" },
                    new object[] { 17, "International Business Machines", 81.41, 0.44, 0.54, "9/1 12:00am" },
                    new object[] { 18, "Johnson & Johnson", 64.72, 0.06, 0.09, "9/1 12:00am" },
                    new object[] { 19, "JP Morgan & Chase & Co", 45.73, 0.07, 0.15, "9/1 12:00am" },
                    new object[] { 20, "McDonald\"s Corporation", 36.76, 0.86, 2.40, "9/1 12:00am" },
                    new object[] { 21, "Merck & Co., Inc.", 40.96, 0.41, 1.01, "9/1 12:00am" },
                    new object[] { 22, "Microsoft Corporation", 25.84, 0.14, 0.54, "9/1 12:00am" },
                    new object[] { 23, "Pfizer Inc", 27.96, 0.4, 1.45, "9/1 12:00am" },
                    new object[] { 24, "The Coca-Cola Company", 45.07, 0.26, 0.58, "9/1 12:00am" },
                    new object[] { 25, "The Home Depot, Inc.", 34.64, 0.35, 1.02, "9/1 12:00am" },
                    new object[] { 26, "The Procter & Gamble Company", 61.91, 0.01, 0.02, "9/1 12:00am" },
                    new object[] { 27, "United Technologies Corporation", 63.26, 0.55, 0.88, "9/1 12:00am" },
                    new object[] { 28, "Verizon Communications", 35.57, 0.39, 1.11, "9/1 12:00am" },
                    new object[] { 29, "Wal-Mart Stores, Inc.", 45.45, 0.73, 1.63, "9/1 12:00am" }
                };
    
                this.Store1.DataBind();
            }
        }
    
        protected void ToXml(object sender, EventArgs e)
        {
            string json = GridData.Value.ToString();
            StoreSubmitDataEventArgs eSubmit = new StoreSubmitDataEventArgs(json, null);
            XmlNode xml = eSubmit.Xml;
    
            string strXml = xml.OuterXml;
    
            this.Response.Clear();
            this.Response.AddHeader("Content-Disposition", "attachment; filename=submittedData.xml");
            this.Response.AddHeader("Content-Length", strXml.Length.ToString());
            this.Response.ContentType = "application/xml";
            this.Response.Write(strXml);
            this.Response.End();
        }
    
        protected void ToExcel(object sender, EventArgs e)
        {
            string json = GridData.Value.ToString();
            StoreSubmitDataEventArgs eSubmit = new StoreSubmitDataEventArgs(json, null);
            XmlNode xml = eSubmit.Xml;
    
            this.Response.Clear();
            this.Response.ContentType = "application/vnd.ms-excel";
            this.Response.AddHeader("Content-Disposition", "attachment; filename=submittedData.xls");
            XslCompiledTransform xtExcel = new XslCompiledTransform();
            xtExcel.Load(Server.MapPath("Excel.xsl"));
            xtExcel.Transform(xml, null, this.Response.OutputStream);
            this.Response.End();
        }
    
        protected void ToCsv(object sender, EventArgs e)
        {
            string json = GridData.Value.ToString();
            StoreSubmitDataEventArgs eSubmit = new StoreSubmitDataEventArgs(json, null);
            XmlNode xml = eSubmit.Xml;
    
            this.Response.Clear();
            this.Response.ContentType = "application/octet-stream";
            this.Response.AddHeader("Content-Disposition", "attachment; filename=submittedData.csv");
            XslCompiledTransform xtCsv = new XslCompiledTransform();
            xtCsv.Load(Server.MapPath("Csv.xsl"));
            xtCsv.Transform(xml, null, this.Response.OutputStream);
            this.Response.End();
        }
    }
    Last edited by Mr.Techno; Apr 02, 2012 at 8:46 AM.
  8. #8
    I can't see the MasterPage. Is it required? If no, could you provide a sample which would not depend on MasterPage?
  9. #9
    Hi Danill,
    thanks for the reply,

    I tried the same example without master page. It is working fine.
    Now my example page With master page also working fine.
    But my actual page which is using master page returning empty data.
    I observed the problem in this part using developer tool
    Ext.each(data, function (record) {
            for (var field in record) {
               
                record[headers[field]] = record[field];
                delete record[field];
            }
        });
    here "data" value having the results, when i go through each iteration headers are successfully changing... but the problem is, it is not stopping at the end of list...
    this loop iterating once again so all headers replacing with 'undefined'.

    how to solve this problem? I am using IE9 and Ext.NET 1.2
    i guess there is no problem in master page (because i am getting the data...)

    i guess implementing to stop operation after one iteration will solve the problem...
    can u suggest me how can i do it?

    Note: This problem is only happening in IE9, but FF11.0 it is working fine..
    See the images which i took in IE9
    Click image for larger version. 

Name:	afterheader change.JPG 
Views:	483 
Size:	95.1 KB 
ID:	4058Click image for larger version. 

Name:	deleting headers.JPG 
Views:	401 
Size:	94.5 KB 
ID:	4059Click image for larger version. 

Name:	End.JPG 
Views:	322 
Size:	98.3 KB 
ID:	4060
  10. #10
    What example can I use to test with?
Page 1 of 4 123 ... LastLast

Similar Threads

  1. Export grid panel data to excel
    By sumesh in forum 1.x Help
    Replies: 1
    Last Post: Sep 23, 2013, 9:07 AM
  2. Export to excel: Grid Panel with multiple pages.
    By breakyoheart in forum 2.x Help
    Replies: 0
    Last Post: Aug 02, 2012, 8:09 PM
  3. Replies: 1
    Last Post: May 28, 2012, 5:17 AM
  4. Export grid panel data to excel
    By sumesh in forum 1.x Help
    Replies: 0
    Last Post: May 19, 2012, 6:01 AM
  5. Excel Export in Grid
    By BLOZZY in forum 1.x Help
    Replies: 0
    Last Post: Oct 09, 2011, 11:37 AM

Tags for this Thread

Posting Permissions