Most efficient way to export DataTable to Excel File (.xlsx)

Question

I wanted to know what is the most efficient way to export a DataTable or DataSet to an .xlsx file in terms of speed.

I have tables of 200K rows and looping is useless, so I want to make like a bulk export or something like that.

Anything easy to implement answer my question?

SOLUTION: I finally used OpenXml by this way, if someone needs it. It exports 100k in about 1 minute:

    private void ExportDataSet(DataSet ds, string destination)
    {
        using (var workbook = SpreadsheetDocument.Create(destination, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook))
        {
            var workbookPart = workbook.AddWorkbookPart();

            workbook.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();

            workbook.WorkbookPart.Workbook.Sheets = new DocumentFormat.OpenXml.Spreadsheet.Sheets();

            foreach (System.Data.DataTable table in ds.Tables)
            {

                var sheetPart = workbook.WorkbookPart.AddNewPart<WorksheetPart>();
                var sheetData = new DocumentFormat.OpenXml.Spreadsheet.SheetData();
                sheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(sheetData);

                DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = workbook.WorkbookPart.Workbook.GetFirstChild<DocumentFormat.OpenXml.Spreadsheet.Sheets>();
                string relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart);

                uint sheetId = 1;
                if (sheets.Elements<DocumentFormat.OpenXml.Spreadsheet.Sheet>().Count() > 0)
                {
                    sheetId =
                        sheets.Elements<DocumentFormat.OpenXml.Spreadsheet.Sheet>().Select(s => s.SheetId.Value).Max() + 1;
                }

                DocumentFormat.OpenXml.Spreadsheet.Sheet sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet() { Id = relationshipId, SheetId = sheetId, Name = table.TableName };
                sheets.Append(sheet);

                DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row();

                List<String> columns = new List<string>();
                foreach (System.Data.DataColumn column in table.Columns)
                {
                    columns.Add(column.ColumnName);

                    DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
                    cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
                    cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(column.ColumnName);
                    headerRow.AppendChild(cell);
                }


                sheetData.AppendChild(headerRow);

                foreach (System.Data.DataRow dsrow in table.Rows)
                {
                    DocumentFormat.OpenXml.Spreadsheet.Row newRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
                    foreach (String col in columns)
                    {
                        DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
                        cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
                        cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow[col].ToString()); //
                        newRow.AppendChild(cell);
                    }

                    sheetData.AppendChild(newRow);
                }

            }
        }
    }

Answer 1

You must be looping through the records, and that's why it's so slow.

Try something like this.

var lines = new List<string>();

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

var header = string.Join(",", columnNames);
lines.Add(header);

var valueLines = dataTable.AsEnumerable()
                   .Select(row => string.Join(",", row.ItemArray));            
lines.AddRange(valueLines);

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

Or, turn the DataTable into an Excel file.

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

Answer 2

Personally I prefer Syncfusion's Excel library just because it's pretty intuitive, well documented and free with the community license.

You can download it here: XlsIO Product Page

And the documentation can be found here: XlsIO Documentation

It's free to use if you claim the free community license.

Here is a code sample for writing a DataTable to an xlsx file. I don't think the size of your table should be a problem at all but I'm not 100% sure, the largest files I've written with this library have about 90k rows with 60 columns and I did not have any issues.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using Syncfusion.XlsIO;
namespace ConsoleApp3
{
    class Program
    {
        static void Main(string[] args)
        {
            DataTable Table = new DataTable();
            Table.Columns.Add("Column1");
            Table.Columns.Add("Column2");
            Table.Columns.Add("Column3");
            Table.Rows.Add("Item1", "Item2", "Item3");

            ExcelEngine ExcelEngineObject = new Syncfusion.XlsIO.ExcelEngine();
            IApplication Application = ExcelEngineObject.Excel;
            Application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook Workbook = Application.Workbooks.Create(1);
            IWorksheet Worksheet = Workbook.Worksheets[0];
            Worksheet.ImportDataTable(Table, true, 1, 1);
            Workbook.SaveAs("YourExcelFile.xlsx");
            Workbook.Close();
            ExcelEngineObject.Dispose();

        }
    }
}