Results 1 to 5 of 5

Thread: MVC Razor - export data to excel or csv

  1. #1
    Member
    Join Date
    Sep 2012
    Location
    Melbourne
    Posts
    19

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

    Hello Mate


    Does anyone have a sample export to excel or CSV?

    Basically,

    I’ve a form panel with few input boxes as filter parameter .

    Im getting Error - BADRESPONSE: Invalid character

    When a but is clicked I want call the controller action which returns a file (CSV/excel)

    Form looks like this
    Code:
    @( Html.X().FormPanel()                
                .Region(Region.North)           
                .Layout(LayoutType.Column)
                .ID("pnlSearchForm")
                .Buttons(                                     
                    Html.X().Button().Text("Export").Icon(Icon.PageExcel)
                    .Listeners(listeners =>
                    {
                        listeners.Click.Handler = "ExportToCSV()";
                    })                                   
                                
                )
               .Items(containers =>
                {
                        containers.Add(Html.X().FieldContainer()
                                    .Layout(LayoutType.Anchor)
                                    .Padding(10)
                                    .Margins("0 0 0 20")
                                    .Items(fields =>
                                    {
                                        fields.Add(Html.X().TextField()
                                            .LabelAlign(LabelAlign.Top)
                                            .FieldLabel("Service Id")
                                            .ID("txtServiceId")
                                            .Margins("0 0 4 4")
                                            .Width(400)
                                            .LabelWidth(200)
                                            .Cls("form-label"));
                                    }
                                ));
                        
                        
                            
                })
       )
    // Java script

    Code:
    var ExportToCSV = function () {
        Ext.net.DirectMethod.request({
            url: baseURL + '/Services/DownloadCSV/',
            isUpload: true,
            formProxyArg: "pnlSearchForm",
            cleanRequest: true,
            params: {
                JSONFilters: getServiceSearchFilters
            },
            success: function (result) {         //....Procession//  
                alert(result);
            }
    
        })
    };
    // Controller

    Code:
    public partial class ServicesController : SecuredController
        {
            [Dependency]
            public IServiceRepository ServiceRepository { get; set; }
            [Dependency]
            public IProductRepository ProductRepository { get; set; }
    
            public FileContentResult DownloadCSV(string JSONFilters)
            {
                // string JSONFilters = "";
                Dictionary<string, object> filters = JSON.Deserialize<Dictionary<string, object>>(JSONFilters);
    
                string id = filters["txtServiceId"].ToString();
               
                 ServiceGridRowVOCollection gridResults = new ServiceGridRowVOCollection();
                gridResults = ServiceRepository.SearchServices(id);
    
                MemoryStream ms = new MemoryStream();
                BinaryFormatter bf = new BinaryFormatter();
                bf.Serialize(ms, gridResults);
    
                var filename = "ExampleCSV.csv";
                var contenttype = "text/csv";
                Response.Clear();
                Response.ContentType = contenttype;
                Response.AddHeader("content-disposition", "attachment;filename=" + filename);
                Response.Cache.SetCacheability(HttpCacheability.NoCache);
                Response.BinaryWrite(ms.ToArray());
                Response.End();
    
    
    
            }
    
    }
    Thank you
    Last edited by geoffrey.mcgill; Nov 14, 2012 at 11:20 PM. Reason: please use [CODE] tags

  2. #2
    Premium Member RCN's Avatar
    Join Date
    Feb 2012
    Location
    Fortaleza, Brazil
    Posts
    974

  3. #3
    Member
    Join Date
    Sep 2012
    Location
    Melbourne
    Posts
    19
    Hello RCN

    Appreciate your reply.
    I’ve seen that example before and my requirement is quite different to that.
    To explain further my issue,
    My form contains two buttons, Search button will fetch first 20 records from the service based on the input and exportToExcel button will fetch all matching records (1000) and export to excel document.
    If I call my “ToExcel” action on the controller using href it works as perfectly
    Code:
    @using (Html.BeginForm())
    { 
    <a href="ToExcel" class="siteButton">To Excel</a>   
    }
    [AcceptVerbs(HttpVerbs.Post)]
     public ActionResult ToExcel(string JSONFilters )
    {
      
    }
    Issue is, I want to use Ext.net.DirectMethod.request.
    When I use the direct request , at the end of the request it throws the exception “BADRESPONSE:Invalid Character”
    Code:
    var ExportToCSV = function () {
        var config = {
            cleanRequest: true,
            isUpload: true,
            method: "POST",
            type: "submit", 
             frame: true,
            url: baseURL + '/Reports/ ToExcel /',
            eventMask: { showMask: true, msg: "Processing Report ..." },
            params: {
                JSONFilters: getServiceSearchFilters
            },
            success: function (result, response, extraParams, o) { },
            failure: function 
            (errorMessage, response, extraParams, o) {
                alert(errorMessage);
            }
        };
        Ext.net.DirectMethod.request(config);
    
    }
    Last edited by geoffrey.mcgill; Nov 14, 2012 at 11:19 PM. Reason: please use [CODE] tags

  4. #4
    Premium Member RCN's Avatar
    Join Date
    Feb 2012
    Location
    Fortaleza, Brazil
    Posts
    974
    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
    Premium Member RCN's Avatar
    Join Date
    Feb 2012
    Location
    Fortaleza, Brazil
    Posts
    974
    1 - View
    Code:
    <!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
    Code:
    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
    Code:
    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
    Code:
    <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