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.

Answer 1

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");

    }