Export DataTable to Excel File

Question

I have a DataTable with 30+ columns and 6500+ rows.I need to dump the whole DataTable values into an Excel file.Can anyone please help with the C# code.I need each column value to be in a cell.To be precise,I need the exact looking copy of DataTable in an Excel File.Please help.

Thanks, Vix

Solution 1

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();

Solution 2

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();

Solution 3

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

Solution 4

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

Solution 5

I use This in page.`

 public void DTToExcel(DataTable dt)
{
    // dosya isimleri ileride aynı anda birden fazla kullanıcı aynı dosya üzerinde işlem yapmak ister düşüncesiyle guid yapıldı. 
    string FileName = Guid.NewGuid().ToString();

    FileInfo f = new FileInfo(Server.MapPath("Downloads") + string.Format("\\{0}.xlsx", FileName));
    if (f.Exists)
        f.Delete(); // delete the file if it already exist.

    HttpResponse response = HttpContext.Current.Response;
    response.Clear();
    response.ClearHeaders();
    response.ClearContent();
    response.Charset = Encoding.UTF8.WebName;
    response.AddHeader("content-disposition", "attachment; filename=" + FileName + ".xls");
    response.AddHeader("Content-Type", "application/Excel");
    response.ContentType = "application/vnd.xlsx";
    //response.AddHeader("Content-Length", file.Length.ToString());


    // create a string writer
    using (StringWriter sw = new StringWriter())
    {
        using (HtmlTextWriter htw = new HtmlTextWriter(sw)) //datatable'a aldığımız sorguyu bir datagrid'e atayıp html'e çevir.
        {
            // instantiate a datagrid
            DataGrid dg = new DataGrid();
            dg.DataSource = dt;
            dg.DataBind();
            dg.RenderControl(htw);
            response.Write(sw.ToString());
            dg.Dispose();
            dt.Dispose();
            response.End();
        }
    }
}

View more solutions

Comments

[brian newman]

Of all the convoluted articles I came across on the web to accomplish this it was nice to find a short, simple solution that just works. Many thanks!

[brian newman]

Any suggestions to force Excel to treat all fields as string regardless of the data? For example, do not drop the leading zeros in '0000012'. I tried prefixing the values with an apostrophe but the apostrophe showed up in the spreadsheet.

[Glendale]

Why is it that this doesn't actually do anything for me? Even if I copy it verbatim, it just doesn't download the file...

[Sumit Jambhale]

What is used to merge the cells? \t is used to move to the new cell in same row, \n for move to the new row,

[Rubens Farias]

This code downloads a CSV file, without any formatting; if you need more flexibility with merging cells, background cell color and so on, you should read the other answers to this question.

[devlin carnate]

Why have you reposted the accepted answer as your answer?

[sam]

this not worked with me it is output excel file but it contains table related to https protocols like (SERVER_PORT_SECURE, SERVER_PROTOCOL,SERVER_SOFTWARE) and think i don't really what is it ... it seems better way sinces we do not need to override VerifyRenderingInServerForm... can you help me ??

[sam]

what I have missed ? I put that code in method and call it from button onclick event

[sam]

thanks Volkan that was best way sinces we do not need to override VerifyRenderingInServerForm .... Volkan I really appreciate it you set comments on each line to explain what's happening and that would be useful for others too.. I Recommend that way dears Searcher

[sam]

Volkan please dear when I uncomment that line ... I get error I was trying to understand it is purpose ? //response.AddHeader("Content-Length", file.Length.ToString());

[sam]

I have add some comment for the line I understands please provide more ... Thanks alot

[chungtinhlakho]

do i need a return anything?

[Rubens Farias]

@chungtinhlakho, this method renders an HTML table and writes it in the Response stream; if you're experiencing a different problem, ask a new question ;)

[Saman]

There are many solutions to accomplish this, with or without dependencies, but it's the shortest,easiest and probably fastest one. Thanks for sharing with us.

[Amir]

@jefissu it seems the close XML has known issues with older versions of the .Net Frame work github.com/ClosedXML/ClosedXML/issues/450

[Sudhakar Chavali]

Actually it is not an excel creation procedure. it generates the plain text in a tab-delimited form and saving the data in file having .xls extension. As Microsoft Excel software is aware of opening .such files, people think it is an excel document. Open the same file in any plain text editor such as "Notepad" or "EditPlus" and you will get to know. Microsoft Excel document follows OLE principles. I hope this helps whoever is trying to follow this thread.

[Deepak]

what is a type of `worksheet'. Where it is declared, initialized..

[Marcio]

It's not a pure Excel file, it's only text saved as Excel :(