Generate CSV file in ASP.Net

Question

I am using the code below on an aspx page on button click event to generate csv file. This works when I do not name my file but when I try to use: Response.AddHeader("Content-Disposition", "attachment;filename=myfilename.csv");

to name the file as myfilename.csv, the excel sheet generated is the screen shot of the web page instead of having text in it. Can someone help me with this problem.
Thanks!

DataGrid dg = new DataGrid();
dg.DataSource = GetData();
htmlTextWriter.WriteLine("<b>Details</b>");

//Get the html for the control
dg.HeaderStyle.Font.Bold = true;
dg.HeaderStyle.BackColor = System.Drawing.Color.Gray;
dg.DataBind();
dg.RenderControl(htmlTextWriter);

//Write the HTML back to the browser.
Response.Clear();
Response.ContentType = "application/vnd.ms-excel";
//Response.AddHeader("Content-Disposition", "attachment;filename=myfilename.csv");
this.EnableViewState = false;
Response.Write(textWriter.ToString());
Response.End();

private System.Data.DataTable GetData()
{
    System.Data.DataTable dt = new System.Data.DataTable("TestTable");
    dt.Columns.Add("SSN");
    dt.Columns.Add("Employee ID");
    dt.Columns.Add("Member Last Name");
    dt.Columns.Add("Member First Name");
    dt.Columns.Add("Patient Last Name");
    dt.Columns.Add("Patient First Name");
    dt.Columns.Add("Claim No.");
    dt.Columns.Add("Service Line No.");
    dt.Columns.Add("Error Code");
    dt.Columns.Add("Error Message");                
    dt.Rows.Add(123456789,4455,"asdf","asdf","sdfg","xzcv","dsfgdfg123",1234,135004,"some error");            
    dt.Rows.Add(123456788,3344,"rth","ojoij","poip","wer","aadf124",1233,135005,"Some Error");
    dt.Rows.Add(123456787,2233,"dfg","sdfg","vcxb","cxvb","UHCAL125",1223,135006,"another error");
    return dt;
}

Answer 1

I am not exactly sure what you are aiming for here, so I have assumed that you are wanting to create a CSV file in a button click event and send that back to the user. What you currently have appears to write the HTML of the control into an XLS file.

Try this:

protected void Button1_Click(object sender, EventArgs e)
{
    var dataTable = GetData();
    StringBuilder builder = new StringBuilder();
    List<string> columnNames = new List<string>();
    List<string> rows = new List<string>();

    foreach (DataColumn column in dataTable.Columns)
    {
        columnNames.Add(column.ColumnName); 
    }

    builder.Append(string.Join(",", columnNames.ToArray())).Append("\n");

    foreach (DataRow row in dataTable.Rows)
    {
        List<string> currentRow = new List<string>();

        foreach (DataColumn column in dataTable.Columns)
        {
            object item = row[column];

            currentRow.Add(item.ToString());
        }

        rows.Add(string.Join(",", currentRow.ToArray()));
    }

    builder.Append(string.Join("\n", rows.ToArray()));

    Response.Clear();
    Response.ContentType = "text/csv";
    Response.AddHeader("Content-Disposition", "attachment;filename=myfilename.csv");
    Response.Write(builder.ToString());
    Response.End();
}

When I run this I am prompted by the browser to save the CSV file.

Edit:

If you would like to maintain your current approach (which is producing HTML, not CSV) then try this:

Response.AddHeader("Content-Disposition", "attachment;filename=myfilename.xls");

Note that I have simply changed the file extension from CSV to XLS. When using the CSV extension, the text appeared in Excel as HTML. Using XLS, it appears just as it does when the above line is commented out.