Convert the Data from Datatable to Excel using ASP.NET C#

In this post, I’ll give you an example of how to use ASP.NET C# to convert data from a DataTable to an Excel file.

Namespaces –
You must import the namespaces as below.

using System.Web.UI.WebControls;
using System.Web;
using System.Web.UI;

Using C# in ASP.Net, Convert DataTable to Excel –

static void dg_ItemCreated(object sender, DataGridItemEventArgs e)
        {
            if (e.Item.ItemType == ListItemType.Header)
            {
                foreach (TableCell tc in e.Item.Cells)
                {
                    tc.Style.Add("background-color", "#4f81bd");
                }
            }
            if (e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem)
            {
                foreach (TableCell tc in e.Item.Cells)
                {
                    tc.Style.Add("background-color", "#dbe5f1");
                }
            }
        }

public void DatatableToExcelConverter(DataTable downloadexcleDT, string filename)
        {
            DataTable dt = downloadexcleDT;
            DataSet ds = new DataSet();
            ds.Tables.Add(dt.Copy());
            HttpContext.Current.Response.Clear();
            HttpContext.Current.Response.Charset = string.Empty;

            //set the response mime type for excel
            HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
            HttpContext.Current.Response.AddHeader("content-disposition", string.Format("attachment; filename='" + filename + "'{0}.xls", DateTime.Now.ToString()));

            //create a string writer
            System.IO.StringWriter stringWrite = new System.IO.StringWriter();
            HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);

            //instantiate a datagrid
            DataGrid dg = new DataGrid();
            dg.ItemCreated += dg_ItemCreated;
            dg.AllowPaging = false;
            dg.HeaderStyle.Font.Size = new FontUnit(8);
            dg.ItemStyle.Font.Size = new FontUnit(8);
            dg.Font.Name = "Tahoma";
            dg.HeaderStyle.ForeColor = Color.White;
            dg.HeaderStyle.Font.Bold = true;
            dg.HeaderStyle.HorizontalAlign = align;
            dg.ItemStyle.HorizontalAlign = align;
            dg.HeaderStyle.VerticalAlign = VerticalAlign.Top;
            dg.ItemStyle.VerticalAlign = VerticalAlign.Top;

            //set the datagrid datasource to the dataset passed in
            dg.DataSource = ds.Tables[0];

            //bind the datagrid
            dg.DataBind();

            //tell the datagrid to render itself to our htmltextwriter
            dg.RenderControl(htmlWrite);

            //all that's left is to output the html
            HttpContext.Current.Response.Write("<META HTTP-EQUIV=\"Content-Type\" CONTENT=\"application/vnd.ms-excel; charset=utf-8\">");
            HttpContext.Current.Response.Write("<META HTTP-EQUIV=\"Content-Language\" CONTENT=\"" + CultureInfo.CurrentUICulture.TwoLetterISOLanguageName + "\">");
            HttpContext.Current.Response.Write(stringWrite.ToString());
            HttpContext.Current.Response.End();
        }