How to export DataTable to Excel

Question

How can I export a DataTable to Excel in C#? I am using Windows Forms. The DataTable is associated with a DataGridView control. I have to export records of DataTable to Excel.

Solution 1

I would recommend ClosedXML -

You can turn a DataTable into an Excel worksheet with some very readable code:

XLWorkbook wb = new XLWorkbook();
DataTable dt = GetDataTableOrWhatever();
wb.Worksheets.Add(dt,"WorksheetName");

The developer is responsive and helpful. The project is actively developed, and the documentation is superb.

Solution 2

Try simple code, to convert DataTable to excel file as csv:

var lines = new List<string>();

string[] columnNames = dataTable.Columns
    .Cast<DataColumn>()
    .Select(column => column.ColumnName)
    .ToArray();

var header = string.Join(",", columnNames.Select(name => $"\"{name}\""));
lines.Add(header);

var valueLines = dataTable.AsEnumerable()
    .Select(row => string.Join(",", row.ItemArray.Select(val => $"\"{val}\"")));

lines.AddRange(valueLines);

File.WriteAllLines("excel.csv", lines);

This will write a new file excel.csv into the current working directory which is generally either where the .exe is or where you launch it from.

Solution 3

An elegant option is writing an extension method (see below) for the DataTable class of .net framework.

This extention method can be called as follows:

using System;
using System.Collections.Generic;
using System.Linq;
using Excel = Microsoft.Office.Interop.Excel;
using System.Data;
using System.Data.OleDb;

DataTable dt;
// fill table data in dt here 
...

// export DataTable to excel
// save excel file without ever making it visible if filepath is given
// don't save excel file, just make it visible if no filepath is given
dt.ExportToExcel(ExcelFilePath);

Extension method for DataTable class:

public static class My_DataTable_Extensions
{

    // Export DataTable into an excel file with field names in the header line
    // - Save excel file without ever making it visible if filepath is given
    // - Don't save excel file, just make it visible if no filepath is given
    public static void ExportToExcel(this DataTable tbl, string excelFilePath = null) {
        try {
            if (tbl == null || tbl.Columns.Count == 0)
                throw new Exception("ExportToExcel: Null or empty input table!\n");

            // load excel, and create a new workbook
            var excelApp = new Excel.Application();
            excelApp.Workbooks.Add();

            // single worksheet
            Excel._Worksheet workSheet = excelApp.ActiveSheet;

            // column headings
            for (var i = 0; i < tbl.Columns.Count; i++) {
                workSheet.Cells[1, i + 1] = tbl.Columns[i].ColumnName;
            }

            // rows
            for (var i = 0; i < tbl.Rows.Count; i++) {
                // to do: format datetime values before printing
                for (var j = 0; j < tbl.Columns.Count; j++) {
                    workSheet.Cells[i + 2, j + 1] = tbl.Rows[i][j];
                }
            }

            // check file path
            if (!string.IsNullOrEmpty(excelFilePath)) {
                try {
                    workSheet.SaveAs(excelFilePath);
                    excelApp.Quit();
                    MessageBox.Show("Excel file saved!");
                }
                catch (Exception ex) {
                    throw new Exception("ExportToExcel: Excel file could not be saved! Check filepath.\n"
                                        + ex.Message);
                }
            } else { // no file path is given
                excelApp.Visible = true;
            }
        }
        catch (Exception ex) {
            throw new Exception("ExportToExcel: \n" + ex.Message);
        }
    }
}

Solution 4

Solution based on tuncalik (thanks for idea) article, but in case of big tables is working much more faster (and is a little less clear).

public static class My_DataTable_Extensions
{
    /// <summary>
    /// Export DataTable to Excel file
    /// </summary>
    /// <param name="DataTable">Source DataTable</param>
    /// <param name="ExcelFilePath">Path to result file name</param>
    public static void ExportToExcel(this System.Data.DataTable DataTable, string ExcelFilePath = null)
    {
        try
        {
            int ColumnsCount;

            if (DataTable == null || (ColumnsCount = DataTable.Columns.Count) == 0)
                throw new Exception("ExportToExcel: Null or empty input table!\n");

            // load excel, and create a new workbook
            Microsoft.Office.Interop.Excel.Application Excel = new Microsoft.Office.Interop.Excel.Application();
            Excel.Workbooks.Add();

            // single worksheet
            Microsoft.Office.Interop.Excel._Worksheet Worksheet = Excel.ActiveSheet;

            object[] Header = new object[ColumnsCount];

            // column headings               
            for (int i = 0; i < ColumnsCount; i++)
                Header[i] = DataTable.Columns[i].ColumnName;

            Microsoft.Office.Interop.Excel.Range HeaderRange = Worksheet.get_Range((Microsoft.Office.Interop.Excel.Range)(Worksheet.Cells[1, 1]), (Microsoft.Office.Interop.Excel.Range)(Worksheet.Cells[1, ColumnsCount]));
            HeaderRange.Value = Header;
            HeaderRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
            HeaderRange.Font.Bold = true;

            // DataCells
            int RowsCount = DataTable.Rows.Count;
            object[,] Cells = new object[RowsCount, ColumnsCount];

            for (int j = 0; j < RowsCount; j++)
                for (int i = 0; i < ColumnsCount; i++)
                    Cells[j, i] = DataTable.Rows[j][i];

            Worksheet.get_Range((Microsoft.Office.Interop.Excel.Range)(Worksheet.Cells[2, 1]), (Microsoft.Office.Interop.Excel.Range)(Worksheet.Cells[RowsCount + 1, ColumnsCount])).Value = Cells;

            // check fielpath
            if (ExcelFilePath != null && ExcelFilePath != "")
            {
                try
                {
                    Worksheet.SaveAs(ExcelFilePath);
                    Excel.Quit();
                    System.Windows.MessageBox.Show("Excel file saved!");
                }
                catch (Exception ex)
                {
                    throw new Exception("ExportToExcel: Excel file could not be saved! Check filepath.\n"
                        + ex.Message);
                }
            }
            else    // no filepath is given
            {
                Excel.Visible = true;
            }
        }
        catch (Exception ex)
        {
            throw new Exception("ExportToExcel: \n" + ex.Message);
        }
    }
}

Solution 5

Try this function pass the datatable and file path where you want to export

public void CreateCSVFile(ref DataTable dt, string strFilePath)
{            
    try
    {
        // Create the CSV file to which grid data will be exported.
        StreamWriter sw = new StreamWriter(strFilePath, false);
        // First we will write the headers.
        //DataTable dt = m_dsProducts.Tables[0];
        int iColCount = dt.Columns.Count;
        for (int i = 0; i < iColCount; i++)
        {
            sw.Write(dt.Columns[i]);
            if (i < iColCount - 1)
            {
                sw.Write(",");
            }
        }
        sw.Write(sw.NewLine);

        // Now write all the rows.

        foreach (DataRow dr in dt.Rows)
        {
            for (int i = 0; i < iColCount; i++)
            {
                if (!Convert.IsDBNull(dr[i]))
                {
                    sw.Write(dr[i].ToString());
                }
                if (i < iColCount - 1)
                {
                    sw.Write(",");
                }
            }

            sw.Write(sw.NewLine);
        }
        sw.Close();
    }
    catch (Exception ex)
    {
        throw ex;
    }
}

View more solutions

Comments

[ʞᴉɯ]

Adding ~ 6 MB of referenced libraries would not make the application little heavy?

[hmqcnoesy]

Good question @MicheleVirgilio. I haven't done any testing to quantify an impact. But for what it's worth, it hasn't bothered me in any of the projects I've used it, in fact I can't say that I've ever noticed it.

[Banshee]

Note that this needs Excel to be installed.

[Banshee]

Note that this will not really use the table cells in the Excel doc, instead everything for each row will be printed on the first cell of every row.

[Tim]

private void releaseObject(object o) { try { while (System.Runtime.InteropServices.Marshal.ReleaseComObject(o) > 0) { } } catch { } finally { o = null; } }

[Mark Schultheiss]

ExcelFilePath != null && ExcelFilePath != "" could be !String.IsNullOrEmpty(ExcelFilePath)

[Wilsu]

tuncalik's answer took almost a minute for me, this is at 1 second if it takes long... I was actually startled.

[Ashok kumar]

What an excellent answer, dude. I don't have scope to give more than one up-vote to your answer, otherwise I could have given even more than 100 up-votes.

[NickG]

@Banshee No, Excel fully understands CSV files.

[suneel ranga]

@Cuong Le - If the cell has two commas, then it will be a problem at "string.Join(",")"

[Jogi]

@Cuong Le where will "excel.csv"location be?

[Tom Leys]

@RehanKhan - The file will be placed in the "working directory". Usually this is the same directory as the .exe file, or where you launch it from.

[Tom Leys]

@suneelranga - If a cell (i.e in row.ItemArray) contains a , (comma) then by the CSV standard that cell should be surrouned by quotes "," and then appear in the file as usual. So, yes - it will cause a problem because this code does not detect the , and apply quotes.

[Mehdi Khademloo]

File.WriteAllLines(ExcelFilePath, lines,Encoding.UTF8); is for unicodes

[Mitulát báti]

Doesn't work with my excel either. Each row's data is in the first cell.

[Elliott Addi]

(please be here) if there are more than one open excel file, does this release function destroy all of them or only the one passed as a parameter?

[Dave Kelly]

This is the fastest sample I have tried, great job. I had to use Marshal to release the file after. Excel.Quit(); Marshal.FinalReleaseComObject(Worksheet); Marshal.FinalReleaseComObject(HeaderRange); Marshal.FinalReleaseComObject(Excel);

[GrammatonCleric]

This works well, however, you never kill you Excel processes afterwards so I suggest adding this, replacing your SaveAs line as it's included here: 'XlObj.DisplayAlerts = false; WbObj.SaveAs(location); WbObj.Close(); XlObj.Quit(); Marshal.ReleaseComObject(WsObj); Marshal.ReleaseComObject(WbObj); Marshal.ReleaseComObject(XlObj);' To use the Marshal.ReleaseComObject method, add the using System.Runtime.InteropServices assembly to your project.

[It's a trap]

This code returned me an excel with a single column with value ClosedXML.Excel.XLWorkbook

[alex.pulver]

Another note: Microsoft does not recommended using Interop on a server support.microsoft.com/en-us/help/257757/…

[Si8]

@alex.pulver it also doesn't work when I tried to use it on a server either. Good point to mention.

[Si8]

Works like a charm but how do I just download it for the user instead of saving the file somewhere?

[TimmRH]

@Si8 once saved you could do a Process.Start(Your File) and it will open it for them. That is about as close as you can get I believe..

[Si8]

I agree, that's what I implemented already and it worked. Thanks for the response.

[Parshuram Kalvikatte]

Does it reuire Office to be installed?

[Alex M]

This will work but it's slow. It's best to copy to clipboard and paste into excel. If you work on more than 1000 records this will take a while.

[Jonathan DS]

This is awesome!

[Zaveed Abbasi]

Works perfectly otherwise, but my Header Back ground color is set to Black always while using this solution in console application. What could be the reason??

[Ken]

and if the op does not want to create DTO's for every table they are going to run this against ? Like for example foreach of my 1 thousand tables do this. Adding the report header attribute is not on the fly - a lot of coding there and that before even working on the real work. Not knocking your solution - just pointing out that the sloth principle is not utilized here as this solution adds the step of creating dto's and then compiling.. I should state - I like that you used the generics.

[Brandon Barkley]

Thanks a lot for putting the time into this answer. I have a client who has a working solution in Excel Interop, but is complaining about how slow it is. I saw a few other answers on questions guide me toward OpenXML, but am glad to have a shortcut on getting started.

[WATYF]

No problem. I still use COM, but only in environments that I have complete control over. I've been using this OpenXML approach in an app with a couple hundred users for a couple months and haven't had any issues compared to weekly errors with COM. I looked into 3rd party solutions too, like the ones mentioned here, but I prefer to write it myself so I can make it as efficient as possible.

[Xenophage]

Great answer. To build on this, you should also be checking if your data has quotes. If a double quote exists in your data, and you are using quote qualification, it will distort your data when you go to import it into a data processor. To get around this, you should implement some logic to switch to using tab delimiters, and no quotation qualifiers. I used some boolean's and a pre-scan of the data to figure out whats inside before deciding how to wrap the data. Most data importers accept pipe delimiters as well, just in case your data also contains tabs!

[Wilhelm]

The field delimiter is not always a comma! If the current locale decimal separator is a comma, then the field delimiter is a semi colon. So a CSV file created in middle Europe where in many countries the comma is the decimal separator, cannot be used in a country where the dot is a decimal separator. And vice versa.

[user890255]

I have modified the code slightly to support double quotes string.Join(",", columnNames.Select(name => $"\"{name.Replace("\"", "\"\"")}\"")); dataTable.AsEnumerable() .Select(row => string.Join(",", row.ItemArray.Select(val => $"\"{val.ToString().Replace("\"", "\"\"")}\"")));

[Andy G]

I used this code to download the Excel file, but I had to reset the stream with stream.Seek(0, SeekOrigin.Begin) otherwise the file had zero bytes. Add this before returning the stream from function GetExcelStream. I also had to use InlineString in places, otherwise Excel warns that it is recovering data from the file.

[Beingnin]

yeah...i forgot to address that. Will add it in the code

[grv_9098]

@MehdiKhademloo Thanks.

[MSS]

It's not free. You need to buy a license to use it.

[Jim G.]

@MSS Version 4.5.3.3 is free.

[glant]

Adding large datatable's (250k rows in my case) resulted in out of memory with ClosedXML and its a known open issue

[singhswat]

How to populate in the current open excel? this works best to save excel at location.

[Ruben]

This has resolve my question, just don't forget to save file as : XLWorkbook wb = new XLWorkbook(); DataTable dt = GetDataTableOrWhatever(); wb.Worksheets.Add(dt,"WorksheetName"); wb.SaveAs(filepath);