Working with Data in C#: Exporting DataSet to Excel Made Easy

In C# development, DataSet is widely used to manage in-memory data, often as a result of database queries or integration processes. There are many scenarios where you may need to create Excel files from DataSet in C# — for example, generating reports, sharing structured data with non-developers, or archiving records for future reference.

In this guide, we’ll walk through different approaches to export DataSet to Excel in C# using Spire.XLS for .NET, including creating an Excel file, writing multiple DataTables into separate sheets, applying formatting, and handling large data volumes.

Here's what's covered in this guide:

• DataSet Basics and Environment Setup
• Creating an Excel File from DataSet in C#
• Adding Formatting to Excel Sheets Using C#
• Handling Large DataSet Exports
• Read Excel into DataSet in C#
• Conclusion
• FAQ

1. DataSet Basics and Environment Setup for Excel Export

What is a DataSet?

A DataSet in C# is an in-memory representation of structured data. It can hold multiple DataTables, including their rows, columns, and relationships, making it useful for working with relational-style data without direct database connections.

Why Export DataSet to Excel?

• Data exchange – Excel is widely supported and easy to share across teams.
• Data analysis – Analysts can manipulate Excel data directly using formulas, pivot tables, and charts.
• Archiving – Storing query results or processed data in a readable, portable format.

Compared to raw text or CSV, Excel supports rich formatting, multiple sheets, and better readability.

Environment Setup

To export a DataSet to an Excel file in C#, we will use Spire.XLS for .NET, which provides APIs for handling Excel files. Install Spire.XLS via NuGet:

Install-Package Spire.XLS

Add the required namespaces:

using Spire.Xls;
using System.Data;
using System.Drawing; // for Color

2. Creating an Excel File from DataSet in C#

Exporting a DataSet to Excel involves two key steps: preparing the data and writing it into a workbook. In practice, the DataSet may come from queries or APIs, but for clarity, we’ll demonstrate with a simple example. First, we’ll build a DataSet in memory, then show how to export it into an Excel file where each DataTable becomes its own worksheet.

2.1 Initialize a DataSet with Sample Data

First, we’ll build a DataSet using C#. The following sample DataSet contains multiple business-style tables and a variety of column types (int, string, DateTime, decimal).

using System;
using System.Data;

class Program
{
    static DataSet CreateSampleDataSet()
    {
        DataSet ds = new DataSet("CompanyData");

        // Employees
        DataTable employees = new DataTable("Employees");
        employees.Columns.Add("ID", typeof(int));
        employees.Columns.Add("Name", typeof(string));
        employees.Columns.Add("DepartmentID", typeof(int));
        employees.Columns.Add("HireDate", typeof(DateTime));
        employees.Columns.Add("Salary", typeof(decimal));

        employees.Rows.Add(1, "Alice", 101, new DateTime(2020, 5, 12), 5500.00m);
        employees.Rows.Add(2, "Bob", 102, new DateTime(2019, 3, 8), 7200.50m);
        employees.Rows.Add(3, "Charlie", 103, new DateTime(2021, 11, 20), 4800.75m);

        // Departments
        DataTable departments = new DataTable("Departments");
        departments.Columns.Add("DepartmentID", typeof(int));
        departments.Columns.Add("DepartmentName", typeof(string));
        departments.Rows.Add(101, "HR");
        departments.Rows.Add(102, "IT");
        departments.Rows.Add(103, "Finance");

        // Projects
        DataTable projects = new DataTable("Projects");
        projects.Columns.Add("ProjectID", typeof(int));
        projects.Columns.Add("ProjectName", typeof(string));
        projects.Columns.Add("OwnerID", typeof(int));
        projects.Columns.Add("StartDate", typeof(DateTime));
        projects.Rows.Add(1001, "Recruitment System", 1, new DateTime(2023, 1, 15));
        projects.Rows.Add(1002, "ERP Upgrade", 2, new DateTime(2023, 4, 10));
        projects.Rows.Add(1003, "Budget Planning", 3, new DateTime(2023, 7, 5));

        ds.Tables.Add(employees);
        ds.Tables.Add(departments);
        ds.Tables.Add(projects);

        return ds;
    }
}

2.2 Export DataSet to Excel File

With the DataSet prepared, the next step is generating the Excel file. This involves creating a Workbook, iterating through the DataTables, inserting them into worksheets, and saving the workbook to an Excel file.

using Spire.Xls;
using System.Data;

class Program
{
    static void Main()
    {
        DataSet ds = CreateSampleDataSet();

        Workbook workbook = new Workbook();

        // Export each DataTable as a separate worksheet
        for (int i = 0; i < ds.Tables.Count; i++)
        {
            Worksheet sheet = (i == 0)
                ? workbook.Worksheets[0]
                : workbook.Worksheets.Add(ds.Tables[i].TableName);

            sheet.InsertDataTable(ds.Tables[i], true, 1, 1);
            sheet.Name = ds.Tables[i].TableName; // ensure sheet is named after the table
        }

        workbook.SaveToFile("DatasetToExcel.xlsx", ExcelVersion.Version2016);
    }
}

About the Exporting Process

• Each DataTable is written into a separate worksheet.
• InsertDataTable(DataTable table, bool columnHeaders, int row, int column) inserts data starting from a specific cell.
• SaveToFile() writes the workbook to disk in the specified format.

In addition to creating separate worksheets for each DataTable, you can also insert multiple DataTables into the same worksheet by adjusting the starting row and column parameters of the InsertDataTable method.

Result preview

Below is a quick preview of the output workbook showing three sheets populated from the DataSet.

For a practical example of exporting data directly from a database to Excel, see our guide on Export Database to Excel in C#.

3. Adding Formatting to Excel Sheets Using C#

Raw data often isn’t enough for reporting. Formatting improves readability and makes the Excel file more professional. With Spire.XLS, you can style fonts, apply background colors, add borders, and format numbers and dates.

using System.Drawing;
using Spire.Xls;

// Get the first sheet
Worksheet sheet1 = workbook.Worksheets["Employees"];

// 1) Header styling (A1:E1)
CellRange header = sheet1.AllocatedRange.Rows[0];
header.Style.Font.IsBold = true;
header.Style.Font.Size = 12;
header.Style.Font.Color = Color.White;
header.Style.Color = Color.SteelBlue;

// Borders around the header row
header.BorderAround(LineStyleType.Thin);

// 2) Number formats for entire columns (D: HireDate, E: Salary)
sheet1.AllocatedRange.Columns[3].Style.NumberFormat = "yyyy-mm-dd";
sheet1.AllocatedRange.Columns[4].Style.NumberFormat = "$#,##0.00";

// 3) Optional: zebra stripes for data area (A2:E4 here as example)
CellRange data = sheet1.Range["A2:E4"];
// CellRange data = sheet1.Range[2, 1, 4, 5];
data.Style.Color = Color.FromArgb(245, 247, 250);
data.BorderAround(LineStyleType.Thin);

// Auto-fit after formatting
sheet1.AllocatedRange.AutoFitColumns();
sheet1.AllocatedRange.AutoFitRows();

How Formatting Works

• Style.Font — font properties such as IsBold, Size, Color.
• Style.Color — background fill color for the selected range.
• Borders / BorderAround — draw borders on edges/around ranges with LineStyleType.
• NumberFormat — Excel-native formats (e.g., dates, currency, percentages).
• AutoFitColumns() / AutoFitRows() — adjust column widths / row heights to fit content.

For more formatting options, refer to the API reference for CellRange and CellStyle.

Formatting preview

The following image shows styled headers, borders, and proper date/currency formats applied.

4. Handling Large DataSet Exports

When exporting large datasets, performance and memory become critical. Consider:

• Split across sheets — When rows approach Excel/version limits or for logical separation.
• Batch writing — Insert data in segments (e.g., table-by-table or range-by-range).
• Lightweight formatting — Minimize heavy styling to reduce file size and processing time.
• Streaming (where applicable) — Prefer APIs that avoid loading everything into memory at once.

5. Bonus: Read Excel into DataSet in C#

In addition to exporting, the reverse workflow is equally important: reading Excel data back into a DataSet for processing or migration. This is useful when importing data from external reports, integrating spreadsheets with applications, or performing preprocessing before database insertion.

using System.Data;
using Spire.Xls;

class Program
{
    static DataSet ReadExcelIntoDataSet(string filePath)
    {
        DataSet ds = new DataSet();
        Workbook workbook = new Workbook();
        workbook.LoadFromFile(filePath);

        foreach (Worksheet sheet in workbook.Worksheets)
        {
            DataTable dt = sheet.ExportDataTable();
            dt.TableName = sheet.Name;
            ds.Tables.Add(dt);
        }

        return ds;
    }
}

The ExportDataTable method allows each worksheet to be converted into a DataTable object, preserving both the structure and the cell values. By assigning the sheet name to TableName and adding it into a DataSet, you can combine multiple sheets into a single in-memory data container that is ready for further processing.

For a complete workflow on persisting Excel data into a database, see our guide on Import Excel into Database in C#.

Conclusion

Exporting a DataSet to Excel in C# allows you to generate reports, share data, and make information easier to analyze or present. With Spire.XLS for .NET, you can create Excel files directly from DataSet objects, apply formatting, manage multiple sheets, and handle large datasets efficiently. You can also import Excel data back into a DataSet for integration with applications or databases.

To explore more advanced features, you may request a free temporary license or use Free Spire.XLS for .NET for smaller projects.

FAQ: C# DataSet and Excel Integration

Q1: How can I export multiple DataTables from a DataSet into different Excel sheets?

Loop through ds.Tables and call InsertDataTable for each one, creating a new worksheet per DataTable.

Q2: Can I export a DataSet to a specific worksheet in an existing Excel file?

Yes. Load the file using Workbook.LoadFromFile(), then choose the worksheet and use InsertDataTable.

Q3: Does exporting DataSet to Excel preserve column formatting and data types?

Values are exported with the same data types as in the DataSet. You can also apply formatting (date, currency, alignment, etc.) after inserting.

Q4: How do I handle very large DataSet exports (over 100,000 rows)?

Split into multiple sheets, use batch inserts, and reduce complex formatting to improve performance.