create excel in ASP.NET

Question

I'm working on an ASP.NET web projec using VS2010,C#, I want my users to get excel file output from table reports, I know how to create CSV files but I'm going to create EXCEL files, for example a button that when clicked, enables user to download table data in EXCEL format, also I'm going to write some unicode text (persian, farsi language) into my excel, what are my options? currently I use following code to generate CSV file:

            Response.Clear();
        Response.Buffer = true;
        Response.AddHeader("content-disposition", "attachment;filename=" + "db" + ".csv");
        Response.Charset = "";
        Response.ContentType = "application/text";

        StringBuilder sb = new StringBuilder();
        sb.Clear();

        //append new line
        sb.Append("\r\n");

        for (int i = 0; i < tblDatabase.Rows.Count; i++)
        {
            //add separator
            for (int j = 0; j < tblDatabase.Rows[0].Cells.Count; j++)
                sb.Append(Table3.Rows[i].Cells[j].Text + ",");
            //append new line
            sb.Append("\r\n");
        }
        Response.Output.Write(sb.ToString());
        Response.Flush();
        Response.End();

Waqas Raja · Accepted Answer · 2011-10-13 05:04:52Z

1

There is no big change when modifying this code to create Excel file instead of csv.

Just change your extension

Response.AddHeader("content-disposition", "attachment;filename=" + "db" + ".xls");

Change content type

Response.ContentType = "application/vnd.ms-excel";

Here is how to write rows of data in Excel sheet.

Response.Write("<table border='1px' bordercolor='black'>");
Response.Write("<tr>");
// loop through column names to display a header row
foreach (DataColumn dc in tblDatabase.Columns)
{
    Response.Write("<td><strong>" + dc.ColumnName + "</strong></td>");
}
Response.Write("</tr>");

int i;
foreach (DataRow dr in tblDatabase.Rows)
{
    Response.Write("<tr>");
    for (i = 0; i < tblDatabase.Columns.Count; i++)
    {
        Response.Write("<td>" + dr[i].ToString() + "</td>");
    }
    Response.Write("</tr>");
}
// end table
Response.Write("</table>");

answered Oct 13, 2011 at 5:04

Waqas Raja

10.9k4 gold badges35 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ali_dotNet Over a year ago

thanks raja, your answer was really simple and effective! my file format is XLS, is it used for office 2003? when I tried to open it using office 2007, it asked me a question regarding format, should I change its extension name to XLSX?

seanb · Accepted Answer · 2011-10-13 05:09:46Z

0

I have had good results using NPOI, learning curve not too steep
http://npoi.codeplex.com/

Creates native excel files, rather than forcing excel to parse html tables.
The html tables option is ok for some jobs (quicker and easier), but if you have a requirement to make the "real" thing, then try out NPOI.

answered Oct 13, 2011 at 5:09

seanb

6,9642 gold badges35 silver badges34 bronze badges

Collectives™ on Stack Overflow

create excel in ASP.NET

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related