How to Export dataset to excel in C#

These libraries allow you to create and manipulate Excel files (.xlsx) in an easy and efficient way.

How to export a DataSet to an Excel file using EPPlus in C#?

EPPlus is a .NET library that allows reading and writing Excel files in the Office Open XML format (.xlsx), with no dependencies other than .NET.

In your project, go to Tools > NuGet Package Manager > Manage NuGet Packages for Solution, then search for EPPlus and install it.

// export dataset to excel in c# .xlsx files
public void ExportDatasetToExcel(DataSet dataSet, string filePath)
{
    //  Create a new Excel workbook
    using (var package = new ExcelPackage())
    {
        foreach (DataTable table in dataSet.Tables)
        {
            var worksheet = package.Workbook.Worksheets.Add(table.TableName);
            // Load the data from each DataTable in the DataSet to the worksheet
            worksheet.Cells["A1"].LoadFromDataTable(table, true);
        }
        var fi = new FileInfo(filePath);
        package.SaveAs(fi);
    }
}

How to export a DataSet to an Excel file using ClosedXML in C#?

ClosedXML enables the creation of Excel files without requiring the Excel application, commonly used for generating Excel reports on a web server.

Search for ClosedXML and install it.

// export dataset to excel in c# .xlsx files
public void ExportDatasetToExcel(DataSet dataSet, string filePath)
{
    using (var workbook = new XLWorkbook())
    {
        foreach (DataTable table in dataSet.Tables)
        {
            var worksheet = workbook.Worksheets.Add(table.TableName);
            worksheet.Cell(1, 1).Value = table.Columns[0].ColumnName; // Column header
            for (int i = 0; i < table.Rows.Count; i++)
            {
                for (int j = 0; j < table.Columns.Count; j++)
                    worksheet.Cell(i + 2, j + 1).Value = table.Rows[i][j];
            }
        }

        workbook.SaveAs(filePath);
    }
}

How to export a DataSet to an Excel file using NPOI in C#?

NPOI is the .NET version of the POI Java project (http://poi.apache.org/), an open-source tool for reading and writing XLS, DOC, and PPT files, with a wide range of applications.

You can use it to generate Excel reports without Microsoft Office installed on your server, extract text for full-text indexing (often for search engines), extract images from Office documents, and create Excel sheets with formulas.

Search for NPOI and install it.

// export dataset to excel in c# .xls and .xlsx files
public void ExportDatasetToExcel(DataSet dataSet, string filePath)
{
    XSSFWorkbook workbook = new XSSFWorkbook();
    foreach (DataTable table in dataSet.Tables)
    {
        XSSFSheet sheet = (XSSFSheet)workbook.CreateSheet(table.TableName);
        XSSFRow headerRow = (XSSFRow)sheet.CreateRow(0);
        // Add headers
        for (int i = 0; i < table.Columns.Count; i++)
            headerRow.CreateCell(i).SetCellValue(table.Columns[i].ColumnName);
        // Add rows
        for (int i = 0; i < table.Rows.Count; i++)
        {
            XSSFRow row = (XSSFRow)sheet.CreateRow(i + 1);
            for (int j = 0; j < table.Columns.Count; j++)
                row.CreateCell(j).SetCellValue(table.Rows[i][j].ToString());
        }
    }
    using (FileStream fileStream = new FileStream(filePath, FileMode.Create, FileAccess.Write))
    {
        workbook.Write(fileStream);
    }
}

Usage

// Init data
var ds = new DataSet();
var dt = new DataTable("Customers");
dt.Columns.Add("ID", typeof(int));
dt.Columns.Add("Name", typeof(string));
dt.Rows.Add(1, "Alice");
dt.Rows.Add(2, "Bob");
ds.Tables.Add(dt);
// export dataset to excel in c#
string filePath = "C:\\file.xlsx";
ExportDatasetToExcel(dataSet, filePath);

EPPlus and ClosedXML are both great libraries for working with .xlsx files.

NPOI supports both .xls and .xlsx formats, which is helpful if you need backward compatibility with older Excel formats.