What's New?

[CLOSED] Problems Export to Excel

  1. #1
    Jul 22, 2014, 10:40 PM

    [CLOSED] Problems Export to Excel

    Hi-Team !!

    I get the following problem when i try to export the records from the control gridpanel to MS excel, an error occurs when the header titles have a separation space.

    Additionally, i want to incorporate a header for the generated report, but it does not work. In the following example i represent my problem.

    
<%@ 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 ToXml(object sender, EventArgs e)
    {
        string json = this.Hidden1.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)
    {
        try
        {
            string json = this.Hidden1.Value.ToString();
            StoreSubmitDataEventArgs eSubmit = new StoreSubmitDataEventArgs(json, null);
            XmlNode xml = eSubmit.Xml;

            this.Response.Clear();
            //this.Response.Buffer = true;
            //this.Response.Write("<table cellpadding='0' cellspacing='0' border='1'>");
            //this.Response.Write("<tr>");
            //this.Response.Write("<td colspan='5' align ='center' valign='middle' bgcolor='#C0C0C0'>Export Data from GridPanel into XML, Excel or CSV using a full PostBack - Ext.NET Examples</td>");
            //this.Response.Write("</tr>");
            //this.Response.Write("</table>"); 
                 
            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();
        }
        catch (Exception ex)
        {
            Label1.Text = "Error : " + ex.Message;
        }
    }

    protected void ToCsv(object sender, EventArgs e)
    {
        string json = this.Hidden1.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();
    }
</script>

<!DOCTYPE html>

<html>
<head id="Head1" 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" />    
    
    <script>
        var saveData = function ()
        {
            var data = App.GridPanel1.getRowsValues(),
                columns = App.GridPanel1.getView().getGridColumns(),
                headers = {};

            Ext.each(columns, function (c)
            {
                headers[c.dataIndex] = c.text.replace(/ /g, "_");
                headers[c.dataIndex] = headers[c.dataIndex].replace(/?/g, "a");
                headers[c.dataIndex] = headers[c.dataIndex].replace(/?/g, "e");
                headers[c.dataIndex] = headers[c.dataIndex].replace(/?/g, "i");
                headers[c.dataIndex] = headers[c.dataIndex].replace(/?/g, "o");
                headers[c.dataIndex] = headers[c.dataIndex].replace(/?/g, "u");
                headers[c.dataIndex] = headers[c.dataIndex].replace(/?/g, "n");
            });

            Ext.each(data, function (record)
            {
                for (var field in record)
                {
                    record[headers[field]] = record[field];
                    delete record[field];
                }
            });

            App.Hidden1.setValue(Ext.encode(data));
        };
    </script>
</head>
<body>
    <form id="Form1" runat="server">
        <ext:ResourceManager ID="ResourceManager1" runat="server" />
        
        <h1>Export Data from GridPanel into XML, Excel or CSV using a full PostBack</h1>
        
        <ext:Hidden ID="Hidden1" runat="server" />
        
        <ext:Store ID="Store1" runat="server">
            <Model>
                <ext:Model ID="Model1" runat="server">
                    <Fields>
                        <ext:ModelField Name="id" Type="Int" />
                        <ext:ModelField Name="company" />
                        <ext:ModelField Name="price" Type="Float" />
                        <ext:ModelField Name="change" Type="Float" />
                        <ext:ModelField Name="pctChange" Type="Float" />
                        <ext:ModelField Name="lastChange" Type="Date" DateFormat="M/d hh:mmtt" />
                    </Fields>
                </ext:Model>
            </Model>
        </ext:Store>        
        <ext:Label ID="Label1" runat="server">
        </ext:Label>
        <ext:GridPanel 
            ID="GridPanel1" 
            runat="server" 
            StoreID="Store1"
            Title="Export Data"
            Width="600" 
            Height="350">
            <ColumnModel ID="ColumnModel1" runat="server">
                <Columns>
                    <ext:Column ID="Column1" runat="server" Text="Company" DataIndex="company" Flex="1" />
                    <ext:Column ID="Column2" runat="server" Text="Price" Width="75" DataIndex="price">
                        <Renderer Format="UsMoney" />
                    </ext:Column>
                    <ext:Column ID="Column3" runat="server" Text="Change" Width="75" DataIndex="change" />
                    <ext:Column ID="Column4" runat="server" Text="Pct.Change" Width="75" DataIndex="pctChange" />
                    <%--<ext:DateColumn ID="DateColumn1" runat="server" Text="Last_Updated" Width="85" DataIndex="lastChange" Format="dd-mm-yyyy" />--%>
                    <ext:DateColumn ID="DateColumn2" runat="server" Text="Last Updated" Width="85" DataIndex="lastChange" Format="dd-mm-yyyy" />
                </Columns>
            </ColumnModel>
            <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>  
    </form>
</body>
</html>
    Watch for any help and / or suggestions
    Last edited by Daniil; Jul 30, 2014 at 5:30 AM. Reason: [CLOSED]
  2. #2
    Jul 23, 2014, 12:54 PM
    Hi @opendat2000,

    Unfortunately, the test case throws a JavaScript error for me on initial load.

    an error occurs
    What error? Please always provide more information about any errors. For example, an Exception's message and stack trace.

    Also please read this thread. It contains some general statements about Ext.NET and exporting a data set to Excel.
    http://forums.ext.net/showthread.php?17972
  3. #3
    Jul 23, 2014, 2:07 PM
    Hi Daniil

    I modified the code so that you can see the error.

    I attached a screeshot, as i said, this error occurs when the header titles from a gridpanel is composed of more than one word and a space separator.

    For this case i use the Text="Last Updated", but if you correct the text to "Last_Updated", the error does not occurs.

    Click image for larger version.  Name: Export Excel 1.JPG  Views: 28  Size: 32.3 KB  ID: 13781
  4. #4
    Jul 24, 2014, 8:50 AM
    Posting error messages in English is appreciated. At least, as raw text. I cannot even copy the message from the screenshot to put to an English translator. Also, I don't see the error's stack trace.

    In any way, I guess this error is not related to Ext.NET.
  5. #5
    Jul 24, 2014, 3:19 PM
    Hi Daniil

    The error message is "The ' ' character hexadecimal value 0x20, cannot be included in a name", i get this error when i run the following code line:

    XmlNode xml = eSubmit.Xml;
    Like i mention before, i modified the code so you can copy and run the aplication and get the indicated error.

    According to your words: "In any way, I guess this error is not related to Ext.NET.", i differ on your comment, if the header text has a space, the export to excel does not works.

    This works:
    <%--<ext:DateColumn ID="DateColumn1" runat="server" Text="Last_Updated" Width="85" DataIndex="lastChange" Format="dd-mm-yyyy" />--%>
    This does not works:
     <ext:DateColumn ID="DateColumn2" runat="server" Text="Last Updated" Width="85" DataIndex="lastChange" Format="dd-mm-yyyy" />
    Last edited by Daniil; Jul 24, 2014 at 3:48 PM. Reason: Please use [CODE] tags
  6. #6
    Jul 24, 2014, 3:53 PM
    Ext.NET doesn't export the data to Excel. Using Ext.NET you just submit the data from a client to a server for exporting.

    As for the Exception
    The ' ' character, hexadecimal value 0x20, cannot be included in a name.
    I tried to search it on the Internet, there is a lot of links. For example,
    http://stackoverflow.com/questions/2...0-cannot-be-in

    So, the Exception is a common one for XML. White spaces are not allowed in elements' names.
    Last edited by Daniil; Jun 17, 2015 at 12:54 PM.
  7. #7
    Jul 28, 2014, 9:28 PM
    Hi Daniil

    I modified the code to display the header gridpanel, replacing " " with "_", code attached.

    Ext.each (columns, function (c) 
{
   headers[c.dataIndex] = c.text.replace(/ /g, "_");
   headers[c.dataIndex] = headers[c.dataIndex].replace(/?/g, "a");
   headers[c.dataIndex] = headers[c.dataIndex].replace(/?/g, "e");
   headers[c.dataIndex] = headers[c.dataIndex].replace(/?/g, "i");
   headers[c.dataIndex] = headers[c.dataIndex].replace(/?/g, "o");
   headers[c.dataIndex] = headers[c.dataIndex].replace(/?/g, "u");
   headers[c.dataIndex] = headers[c.dataIndex].replace(/?/g, "n");
});
    How can I add a header to the Excel report?

    Any idea or help will be appreciated.

    Saludos
    Mauricio.
    Last edited by Daniil; Jul 29, 2014 at 5:34 PM. Reason: Please use [CODE] tags
  8. #8
    Jul 29, 2014, 7:21 AM
    Hi,

    You need to change your XSLT file and include required changes, XSLT changes are out of Ext.Net scope
    Please investigate the following threads
    http://forums.ext.net/showthread.php?26140
    http://forums.ext.net/showthread.php...xport-to-excel
  9. #9
    Jul 29, 2014, 8:49 PM
    Daniil and Vladimir

    Thank you very much for your support and comments

    Thread closed ....

    Saludos
    Mauricio.

Similar Threads

  1. Export to Excel
    By JonC in forum 1.x Help
    Replies: 9
    Last Post: Nov 13, 2012, 4:59 AM
  2. Export to Excel
    By ChrisO in forum 2.x Help
    Replies: 1
    Last Post: Jun 21, 2012, 4:08 PM
  3. Excel Export
    By maephisto in forum 1.x Help
    Replies: 1
    Last Post: May 13, 2011, 11:47 AM
  4. vb example of export to Excel
    By grosenbrock in forum 1.x Help
    Replies: 2
    Last Post: Aug 27, 2010, 6:22 PM
  5. Excel export, with numbers
    By FreddieBooo in forum 1.x Help
    Replies: 0
    Last Post: Jun 01, 2010, 5:46 AM

Posting Permissions