How to create excel file in c# using dataset

You can also directly interact with COM objects like Microsoft.Office.Interop.Excel.

How to create excel file in c# using dataset?

Open your Visual Studio, then create new Windows Forms application project.

Drag and drop the DataGridView and Button controls from Visual Studio toolbox onto your form designer, then you can design your form as shown below.

Select your DataGridView, then configure the datasource

Select your datasource

Click 'Add Project Data Source...' to add new datasource

Select Database, then click Next

Select DataSet, then click Next

Click 'New Connnection...'

Select 'Microsoft SQL Server', then click Continue

Enter your sql account, then click OK

Select your database, then click Next

Select 'Yes, save the connection as:'

Select your table you want to get data, then click Finish

When you add a BindingSource to a Windows Forms application in Visual Studio, it is often automatically added to the form's designer.

Press F5 to run your application

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

To install the EPPlus package, you can use the NuGet Package Manager or run this command in your Package Manager Console

Install-Package EPPlus

Create an ExportDataSetToExcel method allow you to export dataset to excel file in c# as shown below.

public static void ExportDataSetToExcel(DataSet dataSet, string filePath)
{
    // Initialize EPPlus package
    using (var package = new ExcelPackage())
    {
        // Loop through each DataTable in the DataSet
        foreach (DataTable table in dataSet.Tables)
        {
            // Add a new worksheet to the Excel file for each DataTable
            var worksheet = package.Workbook.Worksheets.Add(table.TableName);
            // Load the DataTable into the worksheet starting from the first cell (A1)
            worksheet.Cells["A1"].LoadFromDataTable(table, true);
            // Optionally, format the header row (bold text)
            using (var header = worksheet.Cells[1, 1, 1, table.Columns.Count])
            {
                header.Style.Font.Bold = true;
            }
        }
        // Save the Excel file to disk
        FileInfo fi = new FileInfo(filePath);
        package.SaveAs(fi);
    }
}

Add a click event handler to Export button

private void btnExport_Click(object sender, EventArgs e)
{
    try
    {
        using (SaveFileDialog sfd = new SaveFileDialog() { Filter = "Excel Workbook|*.xlsx" })
        {
            if (sfd.ShowDialog() == DialogResult.OK)
            {
                ExportDataSetToExcel(this.northwindDataSet, sfd.FileName);
                MessageBox.Show("Excel file has been created!", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
}

The ExcelPackage is the main class for creating and managing Excel files.

Using package.Workbook.Worksheets.Add() creates a new worksheet for each DataTable in the DataSet.

Using worksheet.Cells["A1"].LoadFromDataTable() loads data from the DataTable into the Excel worksheet starting from cell A1.

The package.SaveAs(file) method saves the Excel file to the specified path on disk.

If you got an error "Please set the ExcelPackage.LicenseContext property. See https://epplussoftware.com/developers/licenseexception"

You can fix by adding this line to Form_Load event handler

// If you use EPPlus in a noncommercial context according to the Polyform Noncommercial license:
ExcelPackage.LicenseContext = OfficeOpenXml.LicenseContext.NonCommercial;

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

If you want to use ClosedXML instead of EPPlus, you can follow a similar approach.

To install the ClosedXML package, you can use the NuGet Package Manager.

Here's an example using ClosedXML

public static 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).InsertTable(table);
            // Format the header row (bold)
            worksheet.Row(1).Style.Font.Bold = true;
        }
        workbook.SaveAs(filePath);
    }
}

Both EPPlus and ClosedXML are excellent choices for working with Excel files in C#.