MVC Razor - export data to excel or csv

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1

    MVC Razor - export data to excel or csv - BADRESPONSE: Invalid character

    Last edited by geoffrey.mcgill; Nov 14, 2012 at 11:20 PM. Reason: please use [CODE] tags
  2. #2
  3. #3
    Last edited by geoffrey.mcgill; Nov 14, 2012 at 11:19 PM. Reason: please use [CODE] tags
  4. #4
    Please wrap all code samples in [CODE] tags.


    Forum Guidelines For Posting New Topics
    More Information Required
    Last edited by RCN; Nov 14, 2012 at 3:41 PM.
  5. #5
    1 - View
    <!DOCTYPE html>
    <html>
    <head runat="server">
        <title>Index</title>
        <script type="text/javascript">
            var exportExcel = function () {
                debugger;
                Ext.net.DirectMethod.request({
                    url: "/Example/ExportExcel",
                    cleanRequest: true,
                    isUpload: true,
                    params: {
                        data: App.GridPanel1.getRowsValues()
                    }
                });
            }
        </script>
    </head>
    <body>
        <ext:ResourceManager ID="ResourceManager1" runat="server" />
        <ext:Store ID="Store1" runat="server">
            <Model>
                <ext:Model ID="Model1" runat="server">
                    <Fields>
                        <ext:ModelField Name="ID" />
                        <ext:ModelField Name="Name" />
                        <ext:ModelField Name="Address" />
                    </Fields>
                </ext:Model>
            </Model>
        </ext:Store>
        <ext:GridPanel ID="GridPanel1" runat="server" Title="Person" Frame="false">
            <TopBar>
                <ext:Toolbar runat="server">
                    <Items>
                        <ext:Button ID="Button1" Text="Export Excel" Icon="PageExcel" runat="server">
                            <Listeners>
                                <Click Handler="exportExcel();" />
                            </Listeners>
                        </ext:Button>
                    </Items>
                </ext:Toolbar>
            </TopBar>
            <Store>
                <ext:Store runat="server" ID="Store2">
                    <Proxy>
                        <ext:AjaxProxy Url="/Example/LoadFakeRecords/">
                            <ActionMethods Read="POST" />
                            <Reader>
                                <ext:JsonReader Root="data" />
                            </Reader>
                        </ext:AjaxProxy>
                    </Proxy>
                    <Model>
                        <ext:Model ID="Model2" runat="server">
                            <Fields>
                                <ext:ModelField Name="ID" Type="String" />
                                <ext:ModelField Name="Name" Type="String" />
                                <ext:ModelField Name="Address" Type="String" />
                            </Fields>
                        </ext:Model>
                    </Model>
                    <Sorters>
                        <ext:DataSorter Property="Common" Direction="ASC" />
                    </Sorters>
                </ext:Store>
            </Store>
            <ColumnModel ID="ColumnModel1" runat="server">
                <Columns>
                    <ext:Column ID="Column1" runat="server" Text="ID" DataIndex="ID" />
                    <ext:Column ID="Column3" runat="server" Text="Name" DataIndex="Name" />
                    <ext:Column ID="Column2" runat="server" Text="Address" DataIndex="Address" />
                </Columns>
            </ColumnModel>
        </ext:GridPanel>
    </body>
    </html>
    2 - Actions
    public ActionResult ExportExcel(string data)
    {
        var xt = new XslCompiledTransform();
        var submitData = new Ext.Net.SubmitHandler(data);
        XmlNode xml = submitData.Xml;
    
        var s = new StringBuilder();
        var settings = new XmlWriterSettings()
        {
            ConformanceLevel = ConformanceLevel.Auto
        };
    
        FileContentResult result = null;
        xt.Load(Server.MapPath("~/Resources/Excel.xsl"));
        System.Xml.XmlWriter  writer = System.Xml.XmlWriter.Create(s, settings);
        xt.Transform(xml, writer);
        result = new FileContentResult(Encoding.UTF8.GetBytes(s.ToString()), "application/vnd.ms-excel");
        result.FileDownloadName = "Temp.xls";
    
        return result;
    }
    
    public StoreResult LoadFakeRecords()
    {
    
        List<Person> lst = new List<Person>();
    
        for (int index = 0; index < 20; index++)
        {
            lst.Add(new Person
            {
                ID = index,
                Name = "Name" + index,
                Address = "Address" + index,
            });
        }
    
        return new StoreResult(lst, lst.Count());
    }
    3 - Utility
    public class Person
    {
        public int ID { get; set; }
    
        public string Name { get; set; }
    
        public string Address { get; set; }
    }

    4 - Excel.Xsl: Do not forget to save this file on the following path: ~/Resources/Excel.xsl. Otherwise an exception will be thrown at Actions - Line 14 and you will get a BadResponse
    <xsl:stylesheet version="1.0"
        xmlns="urn:schemas-microsoft-com:office:spreadsheet"
        xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
     xmlns:msxsl="urn:schemas-microsoft-com:xslt"
     xmlns:user="urn:my-scripts"
     xmlns:o="urn:schemas-microsoft-com:office:office"
     xmlns:x="urn:schemas-microsoft-com:office:excel"
     xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"> 
     
    <xsl:template match="/">
      <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">
        <xsl:apply-templates/>
      </Workbook>
    </xsl:template>
    
    
    <xsl:template match="/*">
      <Worksheet>
      <xsl:attribute name="ss:Name">
      <xsl:value-of select="local-name(/*/*)" />
      </xsl:attribute>
        <Table x:FullColumns="1" x:FullRows="1">
          <Row>
            <xsl:for-each select="*[position() = 1]/*">
              <Cell><Data ss:Type="String">
              <xsl:value-of select="local-name()" />
              </Data></Cell>
            </xsl:for-each>
          </Row>
          <xsl:apply-templates/>
        </Table>
      </Worksheet>
    </xsl:template>
    
    
    <xsl:template match="/*/*">
      <Row>
        <xsl:apply-templates/>
      </Row>
    </xsl:template>
    
    
    <xsl:template match="/*/*/*">
      <Cell><Data ss:Type="String">
        <xsl:value-of select="." />
      </Data></Cell>
    </xsl:template>
    
    
    </xsl:stylesheet>
    Last edited by geoffrey.mcgill; Nov 14, 2012 at 11:21 PM.

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. How can Export data from GridPanel to Excel
    By delta in forum 2.x Help
    Replies: 2
    Last Post: Nov 14, 2012, 1:07 AM
  3. Export grid panel data to excel
    By sumesh in forum 1.x Help
    Replies: 0
    Last Post: May 19, 2012, 6:01 AM
  4. Export data to excel - in order columns
    By tanju_yayak in forum 1.x Help
    Replies: 2
    Last Post: Mar 22, 2012, 6:40 AM
  5. Data Export Excel Column Names
    By BLOZZY in forum 1.x Help
    Replies: 0
    Last Post: Feb 02, 2012, 2:02 PM

Tags for this Thread

Posting Permissions