csv export showing HTML tags in the CSV file and only shows one record

Question

I would be very grateful if you could help me out with the following code.. I can read a local .xls file and show it on the browser using jQuery jTable, I can also export the data to .csv file but for some reason it is showing HTML tags in the downloaded file, which I believe is due to using

HtmlTextWriter tw = new HtmlTextWriter(sw);
gridvw.RenderControl(tw); 

Also, it only shows one record when downloaded the .CSV file.. I tried using TextWriter but doesn't show anything.

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();
    List<TopPlayed> daa = new List<TopPlayed>();

    foreach (DataRow p in data.Rows)
    {
        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()
        };

        System.Web.UI.WebControls.GridView gridvw = new System.Web.UI.WebControls.GridView();
        gridvw.DataSource = top.ArtistName.ToList().Take(7); 
        gridvw.DataBind();
        HttpContext.Response.ClearContent();
        HttpContext.Response.AddHeader("content-disposition", "attachment; 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");
    }

    return View();
}

Answer 1

So many issues , so little time:

You do not want to write out a grid at all. That was for your web view, but CSV can be generated directly from the data.

You want something like this:

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();
    List<TopPlayed> daa = new List<TopPlayed>();

    // Create a memory stream and a TextWriter that uses it for its output
    var sw = new StreamWriter(new MemoryStream());
    TextWriter tw = new TextWriter(sw);

    // Write the header row
    tw.WriteLine("\"ID\", \"Track\", \"Artist\", \"Plays\"");

    // Write the data here..
    foreach (DataRow p in data.Rows)
    {
        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()
        };
        // Write a single CSV line
        tw.WriteLine(string.Format("\"{0}\", \"{1}\", \"{2}\", \"{3}\"", top.TrackID, top.TrackName, top.ArtistName, top.Times);
    }

    // Now return the stream to the client/browser    
    HttpContext.Response.ClearContent();
    HttpContext.Response.AddHeader("content-disposition", "attachment; filename=TopTracks.csv");
    HttpContext.Response.AddHeader("Expires", "0");
    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");

}

You do not actually need the TopPlayed object, but I did not want to change too much at once :)

That could become:

// Write the data here..
foreach (DataRow p in data.Rows)
{
    // Write a single CSV line direct from the database record
    tw.WriteLine(string.Format("\"{0}\", \"{1}\", \"{2}\", \"{3}\"", p.Field<double>("ID"), p.Field<string>("Track Name"), p.Field<string>("Artist Name"), p.Field<double>("NoOfPlays"));
}

Please note you do not need (char)34 in your connection string. That represents a double-quote. Just escape any double-quote with 2 double quotes "" (in an @-style string) or " (in a normal string).

e.g.

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

Yet another version:

Also, you should always wrap objects that implement IDisposable in a using statement to ensure they are closed correctly/automatically when they go out of scope. If your SQLDBBillingProvider implements IDisposable it should also have a using.

I also noticed I do not need the extra TextWriter isa StreamWriter (i.e. it inherits TextWriter directly).

Please note I cannot compile this code as I do not have the missing parts, so there may be the odd typo:

public ActionResult ExportToCsv()
{
    string Path = @"C:\\5Newwithdate.xls";
    using (OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source= '" + Path + "';Extended Properties=" + (char)34 + "Excel 8.0;IMEX=1;" + (char)34 + ""))
    {
        using (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();
            List<TopPlayed> daa = new List<TopPlayed>();

            // Create a memory stream and a TextWriter that uses it for its output
            using (var sw = new StreamWriter(new MemoryStream()))
            {
                // Write the header row
                sw.WriteLine("\"ID\", \"Track\", \"Artist\", \"Plays\"");

                // Write the data here..
                foreach (DataRow p in data.Rows)
                {
                    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()
                    };
                    // Write a single CSV line
                    sw.WriteLine(string.Format("\"{0}\", \"{1}\", \"{2}\", \"{3}\"", top.TrackID, top.TrackName, top.ArtistName, top.Times);
                }
                // Now return the stream to the client/browser    
                HttpContext.Response.ClearContent();
                HttpContext.Response.AddHeader("content-disposition", "attachment; filename=TopTracks.csv");
                HttpContext.Response.AddHeader("Expires", "0");
                // 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");
            }
        }
    }
}