Export DataTable to Excel File

Below link is used to export datatable to excel in C# Code.

http://royalarun.blogspot.in/2012/01/export-datatable-to-excel-in-c-windows.html

  using System;      
   using System.Data;  
   using System.IO;  
   using System.Windows.Forms;  

    namespace ExportExcel  
    {      
        public partial class ExportDatatabletoExcel : Form  
        {  
            public ExportDatatabletoExcel()  
            {  
                InitializeComponent();  
            }  

            private void Form1_Load(object sender, EventArgs e)
            {

                DataTable dt = new DataTable();

                //Add Datacolumn
                DataColumn workCol = dt.Columns.Add("FirstName", typeof(String));

                dt.Columns.Add("LastName", typeof(String));
                dt.Columns.Add("Blog", typeof(String));
                dt.Columns.Add("City", typeof(String));
                dt.Columns.Add("Country", typeof(String));

                //Add in the datarow
                DataRow newRow = dt.NewRow();

                newRow["firstname"] = "Arun";
                newRow["lastname"] = "Prakash";
                newRow["Blog"] = "http://royalarun.blogspot.com/";
                newRow["city"] = "Coimbatore";
                newRow["country"] = "India";

                dt.Rows.Add(newRow);

                //open file
                StreamWriter wr = new StreamWriter(@"D:\\Book1.xls");

                try
                {

                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        wr.Write(dt.Columns[i].ToString().ToUpper() + "\t");
                    }

                    wr.WriteLine();

                    //write rows to excel file
                    for (int i = 0; i < (dt.Rows.Count); i++)
                    {
                        for (int j = 0; j < dt.Columns.Count; j++)
                        {
                            if (dt.Rows[i][j] != null)
                            {
                                wr.Write(Convert.ToString(dt.Rows[i][j]) + "\t");
                            }
                            else
                            {
                                wr.Write("\t");
                            }
                        }
                        //go to next line
                        wr.WriteLine();
                    }
                    //close file
                    wr.Close();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
        }
    }

Working code for Excel Export

 try
        {
            DataTable dt = DS.Tables[0];
            string attachment = "attachment; filename=log.xls";
            Response.ClearContent();
            Response.AddHeader("content-disposition", attachment);
            Response.ContentType = "application/vnd.ms-excel";
            string tab = "";
            foreach (DataColumn dc in dt.Columns)
            {
                Response.Write(tab + dc.ColumnName);
                tab = "\t";
            }
            Response.Write("\n");
            int i;
            foreach (DataRow dr in dt.Rows)
            {
                tab = "";
                for (i = 0; i < dt.Columns.Count; i++)
                {
                    Response.Write(tab + dr[i].ToString());
                    tab = "\t";
                }
                Response.Write("\n");
            }
            Response.End();
        }
        catch (Exception Ex)
        { }

Please try this, this will export your data table data's faster to excel.

Note: Range "FW", that I have hard coded is because I had 179 columns.

public void UpdateExcelApplication(SqlDataTable dataTable)
    {
        var objects = new string[dataTable.Rows.Count, dataTable.Columns.Count];

        var rowIndex = 0;

        foreach (DataRow row in dataTable.Rows)
        {
            var colIndex = 0;

            foreach (DataColumn column in dataTable.Columns)
            {
                objects[rowIndex, colIndex++] = Convert.ToString(row[column]);
            }

            rowIndex++;
        }

        var range = this.workSheet.Range[$"A3:FW{dataTable.Rows.Count + 2}"];
        range.Value = objects;

        this.workSheet.Columns.AutoFit();
        this.workSheet.Rows.AutoFit();
    }