Export Database to Excel in C#: Practical Guide with SQL Example

Exporting data from a database to Excel using C# is a frequent requirement in business applications—be it for internal reporting, audit logs, data migration, or ad-hoc analysis. Excel's portability and familiarity make it a go-to format for sharing structured data with both technical and non-technical users.

In this guide, you'll learn how to export database records to Excel using C# and Spire.XLS for .NET. We’ll walk through retrieving data from a SQL Server database and writing it into a well-formatted Excel file. The same workflow applies to other relational databases such as SQLite, MySQL, or Oracle with only minimal adjustments.

Table of Contents:

• Prerequisites and Environment Setup
• Exporting Data from SQL Database to Excel in C#
• Format the Excel Output
• Alternative Approaches to Read Data
• Common Issues and Troubleshooting
• Conclusion
• FAQ

Prerequisites and Environment Setup

Before we dive into code, ensure your development environment is ready:

• .NET Version: .NET Framework or .NET Core / .NET 6 / .NET 8

• IDE: Visual Studio (Community or higher)

• Database: A relational database (e.g., SQL Server, SQLite, MySQL, Oracle). This tutorial uses SQL Server Express as the example. By default, the connection uses Windows Authentication, but you can switch to SQL Authentication if needed.

• Libraries:

  • Spire.XLS for .NET (Install via NuGet: Install-Package Spire.XLS)
  • Microsoft.Data.SqlClient (Install via NuGet if using SQL Server)

Sample Data

In the following examples, we'll use a simple Employees table stored in SQL Server Express. Here's the SQL script to create and populate it:

CREATE TABLE Employees (
    Id INT PRIMARY KEY IDENTITY,
    Name NVARCHAR(100) NOT NULL,
    Department NVARCHAR(50) NOT NULL,
    Position NVARCHAR(50),
    HireDate DATE NOT NULL,
    Salary DECIMAL(10, 2) NOT NULL,
    IsFullTime BIT NOT NULL
);

INSERT INTO Employees (Name, Department, Position, HireDate, Salary, IsFullTime) VALUES
('Alice Johnson', 'Human Resources', 'HR Manager', '2018-05-01', 5500.00, 1),
('Bob Smith', 'IT', 'Software Engineer', '2020-09-15', 7200.50, 1),
('Charlie Lee', 'Finance', 'Accountant', '2019-11-20', 6300.75, 0),
('Diana Chen', 'Marketing', 'Content Specialist', '2021-02-10', 4800.00, 1);

If you're using another database system like MySQL or SQLite, just adjust the SQL syntax and connection string accordingly. The export logic remains the same.

Exporting Data from SQL Database to Excel in C#

Let’s walk through how to retrieve data from a database and export it to an Excel file using Spire.XLS for .NET.

Step 1: Connect to the SQL Server Database

We start by establishing a connection to the database using SqlConnection. Here's an example connection string targeting SQL Server Express:

string connectionString = @"Data Source=YourServer\SQLEXPRESS;Initial Catalog=YourDatabaseName;Integrated Security=True;";

The above connection string uses Windows Authentication (Integrated Security=True). If you prefer SQL Server Authentication, replace it with: User ID=yourUsername;Password=yourPassword;Encrypt=True;TrustServerCertificate=True;

Make sure that your SQL Server Express instance is running, and that the specified database and table exist.

Step 2: Retrieve Data into a DataTable

To make the data ready for export, we use SqlDataAdapter to fill a DataTable with the results of a SQL query:

using System.Data;
using Microsoft.Data.SqlClient;

DataTable dataTable = new DataTable();
using (SqlConnection conn = new SqlConnection(connectionString))
{
    conn.Open();
    string query = "SELECT * FROM Employees";
    using (SqlDataAdapter adapter = new SqlDataAdapter(query, conn))
    {
        adapter.Fill(dataTable);
    }
}

Spire.XLS can directly import data from a DataTable using InsertDataTable, which makes it ideal for structured exports from relational databases.

Step 3: Export the DataTable to Excel Using Spire.XLS

Once the DataTable is populated, we can use Spire.XLS to write its contents into a new Excel worksheet:

using Spire.Xls;

// Create a new workbook
Workbook workbook = new Workbook();
// Clear the default sheets and create a new one
workbook.Worksheets.Clear();
Worksheet sheet = workbook.Worksheets.Add("Employees");

// Insert data starting from row 1, column 1, and include column headers
sheet.InsertDataTable(dataTable, true, 1, 1);

// Save the workbook as an Excel xlsx file
workbook.SaveToFile("Employees.xlsx", ExcelVersion.Version2013);

Key classes and methods used:

• Workbook: The main entry point for creating or loading Excel files.
• Worksheet: Represents a single sheet in the workbook. Use workbook.Worksheets[] to access a sheet, or Worksheets.Add() to add more.
• InsertDataTable(DataTable dataTable, bool columnHeaders, int firstRow, int firstColumn):
  • columnHeaders = true tells Spire.XLS to write column names as the first row.
  • firstRow, firstColumn specify where the data begins (1-based index).
• Workbook.SaveToFile(string fileName, ExcelVersion version): Saves the workbook to a file. Spire.XLS supports saving Excel workbooks to various formats, including .xlsx, .xls, and .csv. You can also save to a stream using SaveToStream().

Here’s what the resulting Excel file looks like with the raw data exported from the database.

Step 4: Format the Excel Output (Optional but Recommended)

While the data is already exported, applying some formatting can significantly improve readability for end users:

// Write data to Excel, including column names, starting at row 1, column 1
sheet.InsertDataTable(dataTable, true, 1, 1);

// Make header row bold and highlight with background color
sheet.Rows[0].Style.Font.IsBold = true;
sheet.Rows[0].Style.Font.Size = 14;
sheet.Rows[0].Style.HorizontalAlignment = HorizontalAlignType.Center;
sheet.Rows[0].Style.Color = System.Drawing.Color.LightGray;

// Format data rows
for (int i = 1; i < sheet.Rows.Count(); i++)
{
    CellRange dataRow = sheet.Rows[i];
    dataRow.Style.Font.Size = 12;
    dataRow.Style.HorizontalAlignment = HorizontalAlignType.Left;
}

// Set font name
sheet.AllocatedRange.Style.Font.FontName = "Arial";

// Set borders
sheet.AllocatedRange.BorderAround(LineStyleType.Thin, System.Drawing.Color.Black);
sheet.AllocatedRange.BorderInside(LineStyleType.Medium, System.Drawing.Color.Black);

// Auto-fit columns
sheet.AllocatedRange.AutoFitColumns();

Here's what the Excel file looks like after formatting.

Spire.XLS provides full access to cell styles, fonts, colors, borders, alignment, and more—making it suitable for generating production-quality Excel reports.

If you need advanced number formatting, learn how to set number formats for Excel cells using C#.

Alternative Approaches to Read Data

The export process relies on having a DataTable, but how you populate it can vary based on your application architecture:

A. Using Entity Framework (ORM)

If you use EF Core or EF6, you can load data via LINQ and manually insert it into Excel:

var employees = dbContext.Employees.ToList();

To export, either convert this list into a DataTable, or use a loop to write rows manually using sheet.Range[row, col].Value = value.

B. Using Stored Procedures

Stored procedures allow encapsulating SQL logic. You can execute them using SqlCommand and fill the result into a DataTable:

SqlCommand cmd = new SqlCommand("GetEmployees", conn);
cmd.CommandType = CommandType.StoredProcedure;

C. Reading from SQLite

For lightweight scenarios, replace the connection string and class:

using (SQLiteConnection conn = new SQLiteConnection("Data Source=mydb.db"))

Export logic remains identical—fill a DataTable and use InsertDataTable.

D. Reading from MySQL or Oracle

Same pattern applies—just change the connection class:

using (MySqlConnection conn = new MySqlConnection("server=localhost;uid=root;pwd=123;database=test"))

Make sure to install the appropriate ADO.NET data provider (e.g., Microsoft.Data.SqlClient, Microsoft.Data.Sqlite, or MySql.Data) via NuGet when connecting to different databases.

As long as you populate a DataTable, Spire.XLS handles the Excel generation the same way.

You may also like: How to Import Data from Excel to Database – learn how to complete the full data exchange cycle using Spire.XLS.

Common Issues and Troubleshooting

Conclusion

Exporting a database to Excel in C# can be done efficiently using Spire.XLS for .NET. By retrieving data into a DataTable and exporting it with InsertDataTable(), you can automate reporting and data extraction without needing Microsoft Office installed.

This solution can also be integrated into scheduled tasks, background services, or web applications for automated report generation.

To unlock all features during development or testing, you can apply for a free 30-day temporary license. For smaller projects, Free Spire.XLS for .NET may also be sufficient.

FAQ

How do I export SQL to Excel in C#?

Use SqlConnection to retrieve data into a DataTable, and export it using Spire.XLS’s InsertDataTable() method.

Can I use this method with SQLite or MySQL?

Yes. Just change the connection type and query, then pass the resulting DataTable to Spire.XLS.

Do I need Excel installed to use Spire.XLS?

No. Spire.XLS is a standalone library and does not require Microsoft Excel on the machine.

Can I export multiple tables to Excel?

Yes. Use Workbook.Worksheets.Add() to create additional worksheets, and export each DataTable separately.