Generate CSV file in ASP.Net

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;
}

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 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<div class="su-column su-column-size-1-2"><div class="su-column-inner su-u-clearfix su-u-trim"></div></div>;

            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.

Method 2

Same as NickW’s solution, but more concisely using LINQ:

//Append column names
builder.Append(String.Join(",", 
    from DataColumn c in dataTable.Columns
    select c.ColumnName
)).Append("n");

//Append data from datatable
builder.Append(string.Join("n", 
    from DataRow row in dataTable.Rows
    select String.Join("n", 
        String.Join(",", row.ItemArray)
    )
));

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

Method 3

Finally I think i figured it out, we need to write a http handler if we intend to generate excel files on asp.net pages, now my button_click just redirects to TestHandler.ashx page and it renders the excel file. 🙂

Thank you very much all you guyz

public class TestHandler : IHttpHandler
{

    public void ProcessRequest(HttpContext context)
    {
        StringWriter textWriter = new StringWriter();
        Html32TextWriter htmlTextWriter = new Html32TextWriter(textWriter);
        DataGrid dg = new DataGrid();
        dg.DataSource = GetData();

        //Get the html for the control
        dg.EnableViewState = false;
        dg.DataBind();
        dg.RenderControl(htmlTextWriter);

        //Write the HTML back to the browser.
        context.Response.Clear();

        //context.Response.AddHeader("Content-Disposition", string.Format("attachment;filename=test.csv"));
        //context.Response.ContentType = "text/csv";
        context.Response.AddHeader("Content-Disposition", string.Format("attachment;filename=abc.xls"));
        context.Response.ContentType = "application/vnd.ms-excel";
        context.Response.Write(textWriter.ToString());
        context.Response.End();
    }

    public bool IsReusable
    {
        get
        {
            return false;
        }
    }
}

Method 4

Try changing this:

Response.ContentType = "application/text";

Or

Response.ContentType = "text/csv";

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0