Export the grid to excel

**kakagu** · Apr 07, 2012, 3:02 PM

First row is excel header, you can define any string you want, next all row is record, used the Store field name.

**Mr.Techno** · Apr 09, 2012, 8:28 AM

Hi kakagu,
Thanks for the reply. what is getcolumnstate method? I dint find anywhere in the API.

Please can u provide some working sample?

**Mr.Techno** · Apr 09, 2012, 8:45 AM

Hi Danill,

I found the problem.. I am getting empty data when my browsers document mode is IE8 standards (My browser is IE9 and my project runs in intranet and i cant change browser mode)

Is there any way to solve it?

When i tried to implement paging to the grid and want to export only current visible page data to the excel sheet then its not working,
even though i set visibleOnly:true for getRowsValues method.

Here is the sample running code

<%@ 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,visibleOnly: true}),
        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>
             <BottomBar>
                    <ext:PagingToolbar ID="localPaging" runat="server" PageSize="10" HideRefresh="true" />
                </BottomBar>
        </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();
    }
}

Can you tell why it is not working? I tried with different browser modes but no use...

**Daniil** · Apr 09, 2012, 9:10 AM

Is it MasterPage required to reproduce the problem?

**Mr.Techno** · Apr 09, 2012, 9:38 AM

without master page also i am getting same problem.
In my page i also have gridpanel with pageproxy (remote paging). For this remote paging i am not able to export entire data but able to get page wise data, even though what ever i set to "visibleOnly" propety.

for local paging
1.able to get entire data
2.not able to get page wise data

for remote paging
1.able to get page wise data
2.not able to get entire data.

Can you suggest how to solve this two situations?

**Daniil** · Apr 09, 2012, 9:42 AM

Could you provide a sample without MasterPage? With the steps to reproduce.

**Mr.Techno** · Apr 09, 2012, 10:18 AM

this is the code for without masterpage.

use the same code behind which i posted earlier.

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="ExportToExcelWithoutMaster.aspx.cs"
    Inherits="_1_TestPages_ExportToExcelWithoutMaster" %>
<%@ Register Assembly="Ext.Net" Namespace="Ext.Net" TagPrefix="ext" %>
<!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>Untitled Page</title>
    <ext:XScript ID="XScript1" 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,visibleOnly: true}),
        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>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <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>
             <BottomBar>
                    <ext:PagingToolbar ID="localPaging" runat="server" PageSize="10" HideRefresh="true" />
                </BottomBar>
        </ext:gridpanel>
    </div>
    </form>
</body>
</html>

run this in IE9 browser and see the results.

**kakagu** · Apr 10, 2012, 4:46 AM

Sorry, I am a Chinese, so my English is poor. I think you don't understand the xslt template for export. Following code is changed by me, from my project, first "for" create excel table header from gridpanel header, second "for" create all fields of records from Store.

protected static string gxslt1 = @"<?xml version=\""1.0\"" encoding=\""utf-8\""?>
<xsl:stylesheet version=\""1.0\"" xmlns:xsl=\""http://www.w3.org/1999/XSL/Transform\"" xmlns:msxsl=\""urn:schemas-microsoft-com:xslt\"" exclude-result-prefixes=\""msxsl\"">
 <xsl:output method=\""xml\"" indent=\""yes\""/>
 <xsl:template match=\""/\"">
 <xsl:processing-instruction name=\""mso-application\"">progid=\""Excel.Sheet\""</xsl:processing-instruction>
 <Workbook xmlns=\""urn:schemas-microsoft-com:office:spreadsheet\""
  xmlns:o=\""urn:schemas-microsoft-com:office:office\""
  xmlns:x=\""urn:schemas-microsoft-com:office:excel\""
  xmlns:ss=\""urn:schemas-microsoft-com:office:spreadsheet\""
  xmlns:html=\""http://www.w3.org/TR/REC-html40\"">
  <DocumentProperties xmlns=\""urn:schemas-microsoft-com:office:office\"">
   <Author>Kakagu</Author>
   <Created>2012-02-09T14:17:54Z</Created>
   <Company>None</Company>
   <Version>14.00</Version>
  </DocumentProperties>
  <OfficeDocumentSettings xmlns=\""urn:schemas-microsoft-com:office:office\"">
   <AllowPNG/>
  </OfficeDocumentSettings>
  <ExcelWorkbook xmlns=\""urn:schemas-microsoft-com:office:excel\"">
   <WindowHeight>14535</WindowHeight>
   <WindowWidth>19320</WindowWidth>
   <WindowTopX>120</WindowTopX>
   <WindowTopY>90</WindowTopY>
   <ProtectStructure>False</ProtectStructure>
   <ProtectWindows>False</ProtectWindows>
  </ExcelWorkbook>
  <Styles>
   <Style ss:ID=\""Default\"" ss:Name=\""Normal\"">
    <Alignment ss:Vertical=\""Bottom\""/>
    <Borders/>
    <Font ss:FontName=\""Batang\"" x:CharSet=\""134\"" ss:Size=\""11\"" ss:Color=\""#000000\""/>
    <Interior/>
    <NumberFormat/>
    <Protection/>
   </Style>
  </Styles>
  <Names>
   <NamedRange ss:Name=\""Devices\"" />
  </Names>
  <Worksheet ss:Name=\""Devices\"">
   <Table x:FullColumns=\""1\"" x:FullRows=\""1\"" ss:DefaultColumnWidth=\""54\"" ss:DefaultRowHeight=\""13.5\"">";

protected string gxslt2 = @"  </Table>
  <WorksheetOptions xmlns=\""urn:schemas-microsoft-com:office:excel\"">
   <PageSetup>
    <Header x:Margin=\""0.3\""/>
    <Footer x:Margin=\""0.3\""/>
    <PageMargins x:Bottom=\""0.75\"" x:Left=\""0.7\"" x:Right=\""0.7\"" x:Top=\""0.75\""/>
   </PageSetup>
   <Print>
    <ValidPrinterInfo/>
    <PaperSizeIndex>9</PaperSizeIndex>
    <HorizontalResolution>600</HorizontalResolution>
    <VerticalResolution>600</VerticalResolution>
   </Print>
   <Selected/>
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
 </Worksheet>
</Workbook>";


//
        protected string GetStoreFiledDataType(string name)
        {
            int count = Store_Main.Reader.Reader.Fields.Count;
            for (int i = 0; i < count; i++)
            {
                if (Store_Main.Reader.Reader.Fields[i].Name == name)
                {
                    return Store_Main.Reader.Reader.Fields[i].Type.ToString();
                }
            }
            return "";
        }

        protected string CreateXslt()
        {
            string xml = gxslt1 + "\r\n    <Row>\r\n";
        ColumnCollection cols = GridPanel_Main.ColumnModel.Columns;
            string name = "", type="";

        //create excel header
            for (int i = 2; i < cols.Count; i++) //skip command column and number column
            {
                name = cols[i].Header.ToString();
                xml += "     <Cell><Data ss:Type=\"String\">" + name + "</Data></Cell>\r\n";
            }
            xml += "    </Row>\r\n";

            xml += "   <xsl:for-each select=\"*[position() = 1]/*\">\r\n";
            xml += "    <Row ss:AutoFitHeight=\"0\">\r\n";

            //create records
            for (int j = 2; j < cols.Count; j++)
            {
                name = cols[m].DataIndex;
                type = GetStoreFiledDataType(name);
                if (type == "Int") //change data type for xml file
                    type = "Number";
                else if (type == "Date")
                    type = "String";
                xml += "     <Cell><Data ss:Type=\"" + type + "\"><xsl:value-of select=\"" + name + "\"/></Data></Cell>\r\n";
            }
            xml += "    </Row>\r\n   </xsl:for-each>";

            xml += gxslt2;
            xml += "\r\n</xsl:template>\r\n</xsl:stylesheet>";
            xml = xml.Replace("\\\"", "\"");

            return xml;
        }

        protected void ExportToExcel(object sender, EventArgs e)
        {
            try
            {
                string json="";
                string xslt = CreateXslt();
                string filename = "attachment; filename=";
                filename = filename + Server.UrlEncode("Device.xls");
        json = GridData.Value.ToString();// change this 
                StoreSubmitDataEventArgs eSubmit = new StoreSubmitDataEventArgs(json, null);
                XmlNode xml = eSubmit.Xml;

                XmlTextReader reader = new XmlTextReader(new System.IO.StringReader(xslt));
                this.Response.Clear();
                this.Response.ContentType = "application/vnd.ms-excel";
                this.Response.AddHeader("Content-Disposition", filename);
                XslCompiledTransform xtExcel = new XslCompiledTransform();
                xtExcel.Load(reader);
                xtExcel.Transform(xml, null, this.Response.OutputStream);
                this.Response.End();
            }
            catch (Exception ex)
            {
                X.Msg.Alert("错误", "发生错误了，请联系管理员。").Show();
                X.Msg.Alert("异常", ex.ToString()).Show();
            }
        }

<ext:Store ID="Store_Main" runat="server" />
    <Reader>
                <ext:JsonReader IDProperty="ID">
                    <Fields>
                        <ext:RecordField Name="ID" Type="Int" />
                        <ext:RecordField Name="Name" Type="String" />
                        <ext:RecordField Name="LastDate" Type="Date" />
                    </Fields>
                </ext:JsonReader>
            </Reader>
</ext:Store>

**Mr.Techno** · Apr 10, 2012, 7:19 AM

Hi kakagu,
thanks for providing working example.
But no data is exporting to excel sheet. I am very new to xslt files.

with bit modifications i am providing fully working example here..
Create a master page and use the following code. I guess the problem is in xslt file.
Can u tell me where is the problem.

<%@ 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 saveExportData = function () {
            #{GridData}.setValue(Ext.encode(#{GridPanel_Main}.getRowsValues({selectedOnly : false})));
        };
    
</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="Store_Main" runat="server">
        <Reader>
            <ext:JsonReader IDProperty="ID">
                <Fields>
                    <ext:RecordField Name="ID" Type="Int" />
                    <ext:RecordField Name="Name" Type="String" />
                    <ext:RecordField Name="LastDate" Type="Date" />
                </Fields>
            </ext:JsonReader>
        </Reader>
    </ext:Store>
    <ext:GridPanel ID="GridPanel_Main" runat="server" StoreID="Store_Main" Width="600"
        Height="350">
        <TopBar>
            <ext:Toolbar runat="server">
                <Items>
                    <ext:Button ID="Button4" runat="server" Text="To Excel" AutoPostBack="true" OnClick="ExportToExcel"
                        Icon="PageExcel">
                        <Listeners>
                            <Click Fn="saveExportData" />
                        </Listeners>
                    </ext:Button>
                </Items>
            </ext:Toolbar>
        </TopBar>
        <ColumnModel>
            <Columns>
                <ext:Column Header="Export this1" DataIndex="Name" />
                <ext:Column Header="Export this2" DataIndex="LastDate" />
            </Columns>
        </ColumnModel>
    </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)
        {
           
            //create datatable to bing
            DataTable table = new DataTable();
            table.Columns.Add("ID", typeof(int));
            table.Columns.Add("Name", typeof(string));
            table.Columns.Add("LastDate", typeof(DateTime));

           
            table.Rows.Add(25, "Indocin", DateTime.Now);
            table.Rows.Add(50, "Enebrel",  DateTime.Now);
            table.Rows.Add(10, "Hydralazine",  DateTime.Now);
            table.Rows.Add(21, "Combivent",  DateTime.Now);
            table.Rows.Add(100, "Dilantin",  DateTime.Now);

            Store_Main.DataSource = table;
            Store_Main.DataBind();
        }
    }
   

    //Programatically create xslt and export grid data
    protected static string gxslt1 = @"<?xml version=\""1.0\"" encoding=\""utf-8\""?>
<xsl:stylesheet version=\""1.0\"" xmlns:xsl=\""http://www.w3.org/1999/XSL/Transform\"" xmlns:msxsl=\""urn:schemas-microsoft-com:xslt\"" exclude-result-prefixes=\""msxsl\"">
<xsl:output method=\""xml\"" indent=\""yes\""/>
<xsl:template match=\""/\"">
<xsl:processing-instruction name=\""mso-application\"">progid=\""Excel.Sheet\""</xsl:processing-instruction>
<Workbook xmlns=\""urn:schemas-microsoft-com:office:spreadsheet\""
xmlns:o=\""urn:schemas-microsoft-com:office:office\""
xmlns:x=\""urn:schemas-microsoft-com:office:excel\""
xmlns:ss=\""urn:schemas-microsoft-com:office:spreadsheet\""
xmlns:html=\""http://www.w3.org/TR/REC-html40\"">
<DocumentProperties xmlns=\""urn:schemas-microsoft-com:office:office\"">
<Author>Kakagu</Author>
<Created>2012-02-09T14:17:54Z</Created>
<Company>None</Company>
<Version>14.00</Version>
</DocumentProperties>
<OfficeDocumentSettings xmlns=\""urn:schemas-microsoft-com:office:office\"">
<AllowPNG/>
</OfficeDocumentSettings>
<ExcelWorkbook xmlns=\""urn:schemas-microsoft-com:office:excel\"">
<WindowHeight>14535</WindowHeight>
<WindowWidth>19320</WindowWidth>
<WindowTopX>120</WindowTopX>
<WindowTopY>90</WindowTopY>
<ProtectStructure>False</ProtectStructure>
<ProtectWindows>False</ProtectWindows>
</ExcelWorkbook>
<Styles>
<Style ss:ID=\""Default\"" ss:Name=\""Normal\"">
<Alignment ss:Vertical=\""Bottom\""/>
<Borders/>
<Font ss:FontName=\""Batang\"" x:CharSet=\""134\"" ss:Size=\""11\"" ss:Color=\""#000000\""/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
</Styles>
<Names>
<NamedRange ss:Name=\""Devices\"" />
</Names>
<Worksheet ss:Name=\""Devices\"">
<Table x:FullColumns=\""1\"" x:FullRows=\""1\"" ss:DefaultColumnWidth=\""54\"" ss:DefaultRowHeight=\""13.5\"">";

    protected string gxslt2 = @" </Table>
<WorksheetOptions xmlns=\""urn:schemas-microsoft-com:office:excel\"">
<PageSetup>
<Header x:Margin=\""0.3\""/>
<Footer x:Margin=\""0.3\""/>
<PageMargins x:Bottom=\""0.75\"" x:Left=\""0.7\"" x:Right=\""0.7\"" x:Top=\""0.75\""/>
</PageSetup>
<Print>
<ValidPrinterInfo/>
<PaperSizeIndex>9</PaperSizeIndex>
<HorizontalResolution>600</HorizontalResolution>
<VerticalResolution>600</VerticalResolution>
</Print>
<Selected/>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
</Workbook>";


    //
    protected string GetStoreFiledDataType(string name)
    {
        int count = Store_Main.Reader.Reader.Fields.Count;
        for (int i = 0; i < count; i++)
        {
            if (Store_Main.Reader.Reader.Fields[i].Name == name)
            {
                return Store_Main.Reader.Reader.Fields[i].Type.ToString();
            }
        }
        return "";
    }

    protected string CreateXslt()
    {
        string xml = gxslt1 + "\r\n <Row>\r\n";
        ColumnCollection cols = GridPanel_Main.ColumnModel.Columns;
        string name = "", type = "";

        //create excel header
        for (int i = 2; i < cols.Count; i++) //skip command column and number column
        {
            name = cols[i].Header.ToString();
            xml += " <Cell><Data ss:Type=\"String\">" + name + "</Data></Cell>\r\n";
        }
        xml += " </Row>\r\n";

        xml += " <xsl:for-each select=\"*[position() = 1]/*\">\r\n";
        xml += " <Row ss:AutoFitHeight=\"0\">\r\n";

        //create records
        for (int j = 2; j < cols.Count; j++)
        {
            name = cols[j].DataIndex;
            type = GetStoreFiledDataType(name);
            if (type == "Int") //change data type for xml file
                type = "Number";
            else if (type == "Date")
                type = "String";
            xml += " <Cell><Data ss:Type=\"" + type + "\"><xsl:value-of select=\"" + name + "\"/></Data></Cell>\r\n";
        }
        xml += " </Row>\r\n </xsl:for-each>";

        xml += gxslt2;
        xml += "\r\n</xsl:template>\r\n</xsl:stylesheet>";
        xml = xml.Replace("\\\"", "\"");

        return xml;
    }

    protected void ExportToExcel(object sender, EventArgs e)
    {
        try
        {
            string json = "";
            string xslt = CreateXslt();
            string filename = "attachment; filename=";
            filename = filename + Server.UrlEncode("ExportExcel.xls");
            json = GridData.Value.ToString();
            StoreSubmitDataEventArgs eSubmit = new StoreSubmitDataEventArgs(json, null);
            XmlNode xml = eSubmit.Xml;

            XmlTextReader reader = new XmlTextReader(new System.IO.StringReader(xslt));
            this.Response.Clear();
            this.Response.ContentType = "application/vnd.ms-excel";
            this.Response.AddHeader("Content-Disposition", filename);
            XslCompiledTransform xtExcel = new XslCompiledTransform();
            xtExcel.Load(reader);
            xtExcel.Transform(xml, null, this.Response.OutputStream);
            this.Response.End();
        }
        catch (Exception ex)
        {
            X.Msg.Alert("Title", "Error Occured").Show();
            X.Msg.Alert("Error", ex.ToString()).Show();
        }
    }
}

**Daniil** · Apr 10, 2012, 10:43 AM

Hi @Mr.Techno,

I was able to reproduce the problem under IE9 in IE8 document mode using the sample you posted.

I can suggest the following saveData code to fix.

Example

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

Thread: Export the grid to excel

Thread Tools

Search Thread

Display

Similar Threads

Export grid panel data to excel

Export to excel: Grid Panel with multiple pages.

Export grid panel data to excel with UI format

Export grid panel data to excel

Excel Export in Grid

Tags for this Thread

Posting Permissions

Sitemap

Downloads

Resources