How to export DataTable to Excel

Question

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.

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.

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

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

Using DocumentFormat.OpenXml nuget package, I have created a singleton class which handles export to excel from either a DataTable or DataSet. Tables will be represented as separate sheets in a workbook.

The main class

public sealed class OfficeOpenXML
    {
        private static Lazy<OfficeOpenXML> _instance = new Lazy<OfficeOpenXML>(() => new OfficeOpenXML());
        private OfficeOpenXML()
        {

        }
        public static OfficeOpenXML GetInstance()
        {
            return _instance.Value;
        }

        public MemoryStream GetExcelStream(DataSet ds, bool firstRowAsHeader = false)
        {
            if (ds == null || ds.Tables.Count == 0)
            {
                return null;
            }

            MemoryStream stream = new MemoryStream();
            using (var excel = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook))
            {
                //create doc and workbook
                WorkbookPart workbookPart = excel.AddWorkbookPart();
                Workbook workbook = new Workbook();
                Sheets sheets = new Sheets();
                //loop all tables in the dataset
                for (int iTable = 0; iTable < ds.Tables.Count; iTable++)
                {
                    var table = ds.Tables[iTable];
                    //create sheet part
                    WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
                    Worksheet worksheet = new Worksheet();
                    SheetData data = new SheetData();
                    List<Row> allRows = new List<Row>();

                    //setting header of the sheet
                    Row headerRow = new Row() { RowIndex = 1 };
                    for (int iColumn = 0; iColumn < table.Columns.Count; iColumn++)
                    {
                        var col = table.Columns[iColumn];
                        //if first row of table is not the header then set columns of table as header of sheet
                        if (!firstRowAsHeader)
                        {
                            headerRow.Append(new Cell
                            {
                                DataType = CellValues.String,
                                CellValue = new CellValue(col.ColumnName)
                            });
                        }
                        else
                        {
                            headerRow.Append(new Cell
                            {
                                DataType = CellValues.String,
                                CellValue = new CellValue(Convert.ToString(table.Rows[0][col]))
                            });
                        }
                    }
                    allRows.Add(headerRow);

                    //setting other data rows
                    if (table.Rows != null && table.Rows.Count != 0)
                    {
                        for (int iRow = firstRowAsHeader ? 1 : 0; iRow < table.Rows.Count; iRow++)
                        {
                            var row = table.Rows[iRow];
                            Row valueRow = new Row { RowIndex = (uint)(iRow + (firstRowAsHeader ? 1 : 2)) };

                            for (int iColumn = 0; iColumn < table.Columns.Count; iColumn++)
                            {
                                var col = table.Columns[iColumn];
                                valueRow.Append(new Cell
                                {
                                    DataType = Format(col.DataType),
                                    CellValue = new CellValue(Convert.ToString(row[col]))
                                });
                            }
                            allRows.Add(valueRow);
                        }
                    }

                    //add rows to the data
                    data.Append(allRows);
                    worksheet.Append(data);
                    worksheetPart.Worksheet = worksheet;
                    worksheetPart.Worksheet.Save();

                    //add worksheet to main sheets
                    sheets.Append(new Sheet
                    {
                        Name = string.IsNullOrWhiteSpace(table.TableName) ? "Sheet" + (iTable + 1) : table.TableName,
                        Id = workbookPart.GetIdOfPart(worksheetPart),
                        SheetId = (uint)iTable + 1
                    });
                }//single table processing ends here

                //add created sheets to workbook
                workbook.Append(sheets);
                excel.WorkbookPart.Workbook = workbook;
                excel.WorkbookPart.Workbook.Save();


                excel.Close();
            }
            stream.Seek(0, SeekOrigin.Begin);  
            stream.Capacity = (int)stream.Length;  
            return stream;


        }
        public MemoryStream GetExcelStream(DataTable dt, bool firstRowAsHeader = false)
        {
            DataSet ds = new DataSet();
            ds.Tables.Add(dt);
            return GetExcelStream(ds, firstRowAsHeader);
        }



        #region Excel Helpers

        CellValues Format(Type t)
        {

            switch (t.ToString())
            {

                case "System.String":
                    return CellValues.String;
                case "System.DateTime":
                    return CellValues.Date;
                case "System.Boolean":
                    return CellValues.Boolean;
                case "System.Int16":
                    return CellValues.Number;
                case "System.Int32":
                    return CellValues.Number;
                case "System.Int64":
                    return CellValues.Number;
                case "System.UInt16":
                    return CellValues.Number;
                case "System.UInt32":
                    return CellValues.Number;
                case "System.UInt64":
                    return CellValues.Number;
                case "System.Decimal":
                    return CellValues.Number;
                case "System.Double":
                    return CellValues.Number;
                case "System.Single":
                    return CellValues.Number;
                default:
                    return CellValues.String;
            }
        }
        #endregion
    }

Save to a file

        var excelApp = OfficeOpenXML.GetInstance();
        var dt = GetDataTableFromDB();
        using (var stream = excelApp.GetExcelStream(dt, false))//use true to hide datatable columns from excel
        {

            using (FileStream fs = new FileStream(@"C:\Users\Public\myexcel.xlsx", FileMode.Create))
            {
                stream.CopyTo(fs);
                fs.Flush();
            }
        }

Download in an MVC application

public ActionResult DownloadReport()
{
    var ds = GetDataSetFromDB();
    var excelApp = OfficeOpenXML.GetInstance();
    var file = excelApp.GetExcelStream(ds, false);
    return File(file, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", Guid.NewGuid().ToString() + ".xlsx");
}

The best and easiest way

private void exportToExcel(DataTable dt)
    {

        /*Set up work book, work sheets, and excel application*/
        Microsoft.Office.Interop.Excel.Application oexcel = new Microsoft.Office.Interop.Excel.Application();
        try
        {
            string path = AppDomain.CurrentDomain.BaseDirectory;
            object misValue = System.Reflection.Missing.Value;
            Microsoft.Office.Interop.Excel.Workbook obook = oexcel.Workbooks.Add(misValue);
            Microsoft.Office.Interop.Excel.Worksheet osheet = new Microsoft.Office.Interop.Excel.Worksheet();


          //  obook.Worksheets.Add(misValue);

            osheet = (Microsoft.Office.Interop.Excel.Worksheet)obook.Sheets["Sheet1"];
            int colIndex = 0;
            int rowIndex = 1;

            foreach (DataColumn dc in dt.Columns)
            {
                colIndex++;
                osheet.Cells[1, colIndex] = dc.ColumnName;
            }
            foreach (DataRow dr in dt.Rows)
            {
                rowIndex++;
                colIndex = 0;

                foreach (DataColumn dc in dt.Columns)
                {
                    colIndex++;
                    osheet.Cells[rowIndex, colIndex] = dr[dc.ColumnName];
                }
            }

            osheet.Columns.AutoFit();
            string filepath = "C:\\Temp\\Book1";

            //Release and terminate excel

            obook.SaveAs(filepath);
            obook.Close();
            oexcel.Quit();
            releaseObject(osheet);

            releaseObject(obook);

            releaseObject(oexcel);
            GC.Collect();
        }
        catch (Exception ex)
        {
            oexcel.Quit();
            log.AddToErrorLog(ex, this.Name);
        }
    }