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

use this code...

    dt = city.GetAllCity();//your datatable
    string attachment = "attachment; filename=city.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();

This snippet could be faster to implement:

// Example data
DataTable table = new DataTable();
table.Columns.AddRange(new[]{ new DataColumn("Key"), new DataColumn("Value") });
foreach (string name in Request.ServerVariables)
    table.Rows.Add(name, Request.ServerVariables[name]);

// This actually makes your HTML output to be downloaded as .xls file
Response.Clear();
Response.ClearContent();
Response.ContentType = "application/octet-stream";
Response.AddHeader("Content-Disposition", "attachment; filename=ExcelFile.xls");

// Create a dynamic control, populate and render it
GridView excel = new GridView();
excel.DataSource = table;
excel.DataBind();
excel.RenderControl(new HtmlTextWriter(Response.Output));

Response.Flush();
Response.End();

The most rank answer in this post work, however its is CSV file. It is not actual Excel file. Therefore, you will get a warning when you are opening a file.

The best solution I found on the web is using CloseXML https://github.com/closedxml/closedxml You need to Open XML as well.

 dt = city.GetAllCity();//your datatable
 using (XLWorkbook wb = new XLWorkbook())
    {
        wb.Worksheets.Add(dt);

        Response.Clear();
        Response.Buffer = true;
        Response.Charset = "";
        Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
        Response.AddHeader("content-disposition", "attachment;filename=GridView.xlsx");
        using (MemoryStream MyMemoryStream = new MemoryStream())
        {
            wb.SaveAs(MyMemoryStream);
            MyMemoryStream.WriteTo(Response.OutputStream);
            Response.Flush();
            Response.End();
        }
    }

Credit: http://www.aspsnippets.com/Articles/Solution-ASPNet-GridView-Export-to-Excel-The-file-you-are-trying-to-open-is-in-a-different-format-than-specified-by-the-file-extension.aspx