Exporting DataTable to Excel in C#

In this tutorial, we will explore how to export a DataTable to an Excel file using C#. This is a common requirement in many applications, especially when working with data analysis or reporting. We will cover different approaches to achieve this, including using the ClosedXML library and the Microsoft.Office.Interop.Excel namespace.

Introduction to DataTable

A DataTable is a central object in the ADO.NET library, which represents an in-memory table of data. It consists of rows and columns, similar to an Excel spreadsheet or a database table. DataTables are often used as a data source for controls such as DataGridView or as a temporary storage for data manipulation.

Approach 1: Using ClosedXML

ClosedXML is a popular open-source library that allows you to create and manipulate Excel files (.xlsx) in C#. It provides an easy-to-use API and supports many features, including formatting, formulas, and charts. To export a DataTable using ClosedXML, follow these steps:

using ClosedXML.Excel;

// Create a new XLWorkbook object
XLWorkbook wb = new XLWorkbook();

// Add the DataTable to the workbook as a worksheet
wb.Worksheets.Add(dt, "WorksheetName");

// Save the workbook to a file
wb.SaveAs("example.xlsx");

Approach 2: Using Microsoft.Office.Interop.Excel

The Microsoft.Office.Interop.Excel namespace provides a set of interfaces and classes that allow you to interact with Excel from your C# code. This approach requires Excel to be installed on the machine running your application.

using Excel = Microsoft.Office.Interop.Excel;

// Create a new Excel Application object
Excel.Application excelApp = new Excel.Application();

// Add a new workbook and worksheet
excelApp.Workbooks.Add();
Excel._Worksheet workSheet = excelApp.ActiveSheet;

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

// Set data rows
for (int i = 0; i < dt.Rows.Count; i++)
{
    for (int j = 0; j < dt.Columns.Count; j++)
    {
        workSheet.Cells[i + 2, j + 1] = dt.Rows[i][j];
    }
}

// Save the workbook to a file
workSheet.SaveAs("example.xlsx");
excelApp.Quit();

Approach 3: Exporting as CSV

Another approach is to export the DataTable as a comma-separated values (CSV) file. This can be easily imported into Excel or other spreadsheet software.

using System.IO;
using System.Linq;

// Create a list of strings to hold the CSV data
List<string> lines = new List<string>();

// Add column headings
string[] columnNames = dt.Columns.Cast<DataColumn>().Select(column => column.ColumnName).ToArray();
lines.Add(string.Join(",", columnNames));

// Add data rows
foreach (DataRow row in dt.Rows)
{
    lines.Add(string.Join(",", row.ItemArray.Select(val => val.ToString())));
}

// Save the CSV data to a file
File.WriteAllLines("example.csv", lines);

Choosing the Right Approach

The choice of approach depends on your specific requirements. If you need to create complex Excel files with formatting and formulas, ClosedXML or Microsoft.Office.Interop.Excel may be a better choice. However, if you simply need to export data in a format that can be easily imported into Excel, exporting as CSV is a simple and efficient solution.

Conclusion

In this tutorial, we have explored three different approaches to exporting a DataTable to an Excel file using C#. By understanding the strengths and weaknesses of each approach, you can choose the best method for your specific needs.