mvc 4 asp.net - export data to .csv from a local .xls file

Question

I would be grateful if you could please help me with the code below: I am fairly new to C# and Razor. I am trying to get data from an excel sheet and displaying it on the screen using a jQuery Jtable. I can get it to be displayed but its not exporting the data to CSV file. I am using MVC 4 Razor ASP.NET

here's my controller action code:

    private void ExportToCsv(object sender, System.EventArgs e)
    {
        string Path = @"C:\\5Newwithdate.xls";

        OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source= '" + Path + "';Extended Properties=" + (char)34 + "Excel 8.0;IMEX=1;" + (char)34 + "");

        OleDbDataAdapter da = new OleDbDataAdapter("select * from [Sheet1$]", con);

        con.Close();

        System.Data.DataTable data = new System.Data.DataTable();

        da.Fill(data);

        SQLDBBillingProvider sql = new SQLDBBillingProvider();
    //    var billingList = sql.GetAllBilling(jtStartIndex, jtPageSize, jtSorting);

        //  data.Rows.OfType<DataRow>().Select(dr => dr.Field<MyType>(columnName)).ToList();

        List<TopPlayed> daa = new List<TopPlayed>();

        foreach (DataRow p in data.Rows)
        {
            //daa.Add(p.Field<string>("Track Statistics"));

            //daa.Add(p.Field<string>("Track Name"));

            TopPlayed top = new TopPlayed()
            {
                TrackID = p.Field<double>("ID").ToString(),
                TrackName = p.Field<string>("Track Name"),
                ArtistName = p.Field<string>("Artist Name"),
                Times = p.Field<double>("NoOfPlays").ToString()
            };

            daa.Add(top);
        }

        var toptracks = new List<TopPlayed>();

        // toptracks.Add(GetHeader());
        int k = -5;
        for (int i = 0; i < 5; i++)
        {
            //static data 
            var trackInfo = new TopPlayed();

            trackInfo.TrackID = "abc" + i;
            trackInfo.TrackName = "xyz" + i;
            trackInfo.ArtistName = "" + i;
            trackInfo.Times = "" + i;
            toptracks.Add(trackInfo);
        }
        System.Web.UI.WebControls.GridView gridvw = new System.Web.UI.WebControls.GridView();
        gridvw.DataSource = toptracks.ToList().Take(7); //bind the datatable to the gridview
        gridvw.DataBind();
        Response.ClearContent();
        Response.ContentType = "application/vnd.ms-excel;name='Excel'";
        Response.AddHeader("content-disposition", "attachment;filename=TopTracks.csv");
        StringWriter swr = new StringWriter();
        HtmlTextWriter tw = new HtmlTextWriter(swr);
        gridvw.RenderControl(tw);
        Response.Write(swr.ToString());

        Response.End();
    }

Thanks in advance.

The code below was take from one of my production websites, so has all the problems I found resolved. You should be able to slot the second example straight into your code. If you have any problems, just ask. — iCollect.it Ltd
– iCollect.it Ltd, Commented Mar 18, 2014 at 12:17
Yeah that made a lot of sense and thanks for your time in typing up and helping :) You're the Man! — pv619
– pv619, Commented Mar 19, 2014 at 9:41
yeah.. i just done it :) anyway i can add+ rep to you as well? :) — pv619
– pv619, Commented Mar 25, 2014 at 11:07
yeah man that looks really awesome.. i will have to do something very similar in my next project. saved it in my bookmarks :) cheers for that. I 'll do that now. thanks again :) — pv619
– pv619, Commented Mar 25, 2014 at 11:43

iCollect.it Ltd · Accepted Answer · 2014-03-19 09:16:09Z

From an existing, working, project:

   HttpContext.Response.AddHeader("content-disposition", "attachment; filename=" + filename);
   var sw = new StreamWriter(new MemoryStream());

   // Write the strings here..
   sw.WriteLine(...) etc

   // Flush the stream and reset the file cursor to the start
   sw.Flush();
   sw.BaseStream.Seek(0, SeekOrigin.Begin);

   // return the stream with Mime type
   return new FileStreamResult(sw.BaseStream, "text/csv");

Just tweak the variables to suit your filename and data writing method.

e.g.

   HttpContext.Response.AddHeader("content-disposition", "attachment; filename=" + filename);
   var sw = new StreamWriter(new MemoryStream());

   // Write the data here..
   HtmlTextWriter tw = new HtmlTextWriter(sw);
   gridvw.RenderControl(tw);

   // Flush the stream and reset the file cursor to the start
   sw.Flush();
   sw.BaseStream.Seek(0, SeekOrigin.Begin);

   // return the stream with Mime type
   return new FileStreamResult(sw.BaseStream, "text/csv");

In the context of your original question it will look something like:

    public ActionResult ExportToCsv()
    {
        string Path = @"C:\\5Newwithdate.xls";

        OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source= '" + Path + "';Extended Properties=" + (char)34 + "Excel 8.0;IMEX=1;" + (char)34 + "");

        OleDbDataAdapter da = new OleDbDataAdapter("select * from [Sheet1$]", con);

        con.Close();

        System.Data.DataTable data = new System.Data.DataTable();

        da.Fill(data);

        SQLDBBillingProvider sql = new SQLDBBillingProvider();
        //    var billingList = sql.GetAllBilling(jtStartIndex, jtPageSize, jtSorting);

        //  data.Rows.OfType<DataRow>().Select(dr => dr.Field<MyType>(columnName)).ToList();

        List<TopPlayed> daa = new List<TopPlayed>();

        foreach (DataRow p in data.Rows)
        {
            //daa.Add(p.Field<string>("Track Statistics"));

            //daa.Add(p.Field<string>("Track Name"));

            TopPlayed top = new TopPlayed()
            {
                TrackID = p.Field<double>("ID").ToString(),
                TrackName = p.Field<string>("Track Name"),
                ArtistName = p.Field<string>("Artist Name"),
                Times = p.Field<double>("NoOfPlays").ToString()
            };

            daa.Add(top);
        }

        var toptracks = new List<TopPlayed>();

        // toptracks.Add(GetHeader());
        int k = -5;
        for (int i = 0; i < 5; i++)
        {
            //static data 
            var trackInfo = new TopPlayed();

            trackInfo.TrackID = "abc" + i;
            trackInfo.TrackName = "xyz" + i;
            trackInfo.ArtistName = "" + i;
            trackInfo.Times = "" + i;
            toptracks.Add(trackInfo);
        }
        System.Web.UI.WebControls.GridView gridvw = new System.Web.UI.WebControls.GridView();
        gridvw.DataSource = toptracks.ToList().Take(7); //bind the datatable to the gridview
        gridvw.DataBind();
        HttpContext.Response.ClearContent();
        HttpContext.Response.AddHeader("content-disposition", "attachment; filename=filename=TopTracks.csv");
        HttpContext.Response.AddHeader("Expires", "0");
        var sw = new StreamWriter(new MemoryStream());

        // Write the data here..
        HtmlTextWriter tw = new HtmlTextWriter(sw);
        gridvw.RenderControl(tw);

        // Flush the stream and reset the file cursor to the start
        sw.Flush();
        sw.BaseStream.Seek(0, SeekOrigin.Begin);

        // return the stream with Mime type
        return new FileStreamResult(sw.BaseStream, "text/csv");

    }

Hey TrueBlueAussie, thanks for your help. I tried to put your second code in but it didn't work. Maybe what i tried to do was wrong with the data writing method.

Collectives™ on Stack Overflow

mvc 4 asp.net - export data to .csv from a local .xls file

1 Answer 1

In the context of your original question it will look something like:

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

In the context of your original question it will look something like:

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related