Being a web developer I am always asked to create functionality to export sql server data in excel, actually Exporting data from SQL Server to Excel is a common requirement in many business applications. Although there are multiple ways to export SQL server data in excel but the easiest one is using Closed XML Whether you're building reporting tools or just need to share tabular data in a readable format, ClosedXML is one of the most powerful and beginner-friendly libraries for working with Excel files in .NET.
I prefer to use Closed XML cause it gives me multiple options to format and represent the SQL server data the way I want
Before starting to code the export functionality let me give you a brief idea about ClosedXML.
ClosedXML is a .NET library that makes it easy to create, read, and edit Excel files (.xlsx) without needing Microsoft Excel installed on your system.
It is built on top of OpenXML (Microsoft’s Excel file format standard) but provides a much simpler and more user-friendly API. This means you don’t have to deal with the complex XML structure that OpenXML uses internally.
In Simple Terms
Think of ClosedXML as a tool that lets you work with Excel files in C# just like you would in Excel itself — but through code.
What You Can Do with ClosedXML
- Create new Excel files
- Read existing Excel files
- Add or format worksheets, rows, and cells
- Apply styles such as fonts, colors, borders, and alignment
- Insert formulas
- Merge cells
- Export
DataTableor lists directly to Excel - Load Excel data into
DataTable
Example: Create a Simple Excel File
using ClosedXML.Excel;
var workbook = new XLWorkbook();
var worksheet = workbook.Worksheets.Add("MySheet");
// Write data
worksheet.Cell("A1").Value = "Name";
worksheet.Cell("B1").Value = "Age";
worksheet.Cell("A2").Value = "Sachin";
worksheet.Cell("B2").Value = 28;
// Save file
string folderPath = @"D:\Reports";
// Ensure folder exists
if (!Directory.Exists(folderPath))
Directory.CreateDirectory(folderPath);
// Create file path
string filePath = Path.Combine(folderPath, “sampleExcel.xlxs”);
workbook.SaveAs(filePath);
Example: Read an Existing Excel File
using ClosedXML.Excel;
string excelFilePath=@”D:\Reports\Sample.xlxs”;
using (var workbook = new XLWorkbook("excelFilePath"))
{
var worksheet = workbook.Worksheet("MySheet");
string name = worksheet.Cell("A2").GetString();
int age = worksheet.Cell("B2").GetValue<int>();
Console.WriteLine($"{name} is {age} years old.");
}
Why Use ClosedXML?
- Very easy to use compared to OpenXML SDK
- No need for Microsoft Excel to be installed on the server
- Works perfectly in ASP.NET, Console Apps, and Windows Services
- Actively maintained and open source
ClosedXML is an excellent choice if you want to handle Excel files efficiently in your .NET applications with minimal effort.
In this tutorial, we’ll go step by step through how to use ClosedXML and ADO.NET to:
- Fetch data from SQL Server using ADO.NET
- Generate a clean and formatted Excel file with single sheet using ClosedXML
- Generate a clean and formatted Excel file with multiple sheet using ClosedXML
- Generate a clean and formatted Excel file with multiple sheet with grouped data and merged cells
- Save the Excel file locally
By the end, you’ll have a fully functional C# console application capable of exporting both simple and grouped datasets to Excel — something highly reusable for admin dashboards, HR systems, or financial reports.
🧩 Prerequisites
Before starting, make sure you have:
- Visual Studio (any edition supporting .NET Framework 4.7.2 or later)
- SQL Server (Express or higher)
- A sample database table or copy paste the SQL script given below to generate one
- The ClosedXML NuGet package installed
Create a sample Database, Tables and import some dummy data
--Create Database named OfficeDB
Create database OfficeDB
GO
use officedb
GO
--Create Table named Department
Create table [dbo].[Department]
(
DeptID INT Identity primary key,
DeptName varchar(100) NULL
)
GO
--Create Table named Employee
Create table [dbo].[Employee]
(
EmpID INT Identity primary key,
EmpName varchar(100) NULL,
DOB Datetime NULL,
Gender Varchar(50) NULL,
DeptID INT NULL
)
GO
--Add Foreign Key Constraint
ALTER TABLE [dbo].[Employee]
ADD CONSTRAINT FK_Employee_Department
FOREIGN KEY (DeptID)
REFERENCES [dbo].[Department](DeptID);
GO
--Insert some dummy data
INSERT INTO [dbo].[Department] (DeptName)
SELECT 'Human Resources' UNION ALL
SELECT 'Finance' UNION ALL
SELECT 'IT' UNION ALL
SELECT 'Marketing' UNION ALL
SELECT 'Operations';
GO
INSERT INTO [dbo].[Employee] (EmpName, DOB, Gender, DeptID)
SELECT 'John Smith', '1990-05-14', 'Male', 1 UNION ALL
SELECT 'Priya Patel', '1988-07-22', 'Female', 2 UNION ALL
SELECT 'Rahul Mehta', '1995-03-10', 'Male', 3 UNION ALL
SELECT 'Ajay', '1995-03-10', 'Male', 3 UNION ALL
SELECT 'Vijay', '1995-03-10', 'Male', 3 UNION ALL
SELECT 'Sara Khan', '1992-11-28', 'Female', 4 UNION ALL
SELECT 'Amit Verma', '1989-01-05', 'Male', 5 UNION ALL
SELECT 'Emily Davis', '1993-09-19', 'Female', 3;
GO
Write a simple stored procedure to return three result sets one for Department data, one for Employee data and one for Employee With their Department Name
Create procedure [dbo].[GetEmployeeReport]
as
begin
--returns employee data
select * from Employee
--returns Department data
select * from Department
--returns all employee with department name and formatted DOB
select d.DeptName,e.EmpName,format(e.DOB,'dd MMM yyyy')DOB, Gender,
Cast(Row_Number() over(order by d.DeptID) as int)RN
from Department d
join Employee e
on d.DeptID=e.DeptID
order by d.DeptName
end
Note : I have also used ranking function Row_Number(), i will use this column (RN) to merge rows having same department name and having same gender within each department.
Install ClosedXML using NuGet:
Install-Package ClosedXMLWe’ll also use System.Data.SqlClient for connecting to SQL Server.
📦 Step 1: Create a New Console Application
Open Visual Studio → Create a new Console App → Name it ClosedXMLDemo.
Then, open the Program.cs file and import the required namespaces:
using System;
using System.Data;
using System.Data.SqlClient;
using ClosedXML.Excel;
using System.IO;
🧠 Step 2: Connect to the Database Using ADO.NET
We’ll write a helper function that executes our stored procedure and returns the result as a DataSet.
string cs = ConfigurationManager.ConnectionStrings["OfficeCs"].ConnectionString;
DataSet GetEmployeeData()
{
using (SqlConnection con = new SqlConnection(cs))
{
using (SqlCommand cmd = new SqlCommand("[dbo].[GetEmployeeReport]", con))
{
cmd.CommandType = CommandType.StoredProcedure;
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
DataSet ds = new DataSet();
da.Fill(ds);
return ds;
}
}
}
}
Note : I have kept the connection string in a configuration file.
To add a configuration file right click on the solution name -->Add-->Add New Item-->Application Configuration File. And then copy paste your connection string as shown below
📊 Step 3: Export Employee Data to Excel in a single sheet named Employee
void ExportEmployeeReport()
{
Console.WriteLine("Do you want to proceed?");
string ans = Console.ReadLine();
// Call your existing method that returns a DataSet
DataSet dataSet = GetEmployeeData();
// Create a folder path (example: D:\Reports)
string folderPath = @"D:\Reports";
// Ensure folder exists
if (!Directory.Exists(folderPath))
Directory.CreateDirectory(folderPath);
// Create file path with timestamp
string filePath = Path.Combine(folderPath, $"EmployeeReport_{DateTime.Now:yyyyMMdd_HHmmss}.xlsx");
// Create Excel workbook
using (XLWorkbook wb = new XLWorkbook())
{
// Add just Employee DataTable from DataSet
wb.Worksheets.Add(dataSet.Tables[0]);
// Rename sheets
if (wb.Worksheets.Count >= 1)
wb.Worksheet(1).Name = "Employee";
//Auto Adjust excell column width as per content
wb.Worksheet(1).Columns().AdjustToContents();
// Save workbook to the file
wb.SaveAs(filePath);
}
Console.WriteLine($"Excel file saved at: {filePath}");
}
//Call the method
ExportEmployeeReport();
🎨 Step 4: Export multiple sheets (Employee Data in Sheet 1, Department Data in sheet 2 and Employee + Department Data in sheet 3)
void ExportMultiSheet()
{
Console.WriteLine("Do you want to proceed?");
string ans = Console.ReadLine();
// Call your existing method that returns a DataSet
DataSet dataSet = GetEmployeeData();
// Create a folder path (example: D:\Reports)
string folderPath = @"D:\Reports";
// Ensure folder exists
if (!Directory.Exists(folderPath))
Directory.CreateDirectory(folderPath);
// Create file path with timestamp
string filePath = Path.Combine(folderPath, $"MultiReport_{DateTime.Now:yyyyMMdd_HHmmss}.xlsx");
// Create Excel workbook
using (XLWorkbook wb = new XLWorkbook())
{
// Add all DataTables from DataSet
wb.Worksheets.Add(dataSet);
// Rename sheets if you know how many tables are present
if (wb.Worksheets.Count >= 1)
wb.Worksheet(1).Name = "Employee";
if (wb.Worksheets.Count >= 2)
wb.Worksheet(2).Name = "Department";
if (wb.Worksheets.Count >= 3)
wb.Worksheet(3).Name = "Merged";
wb.Worksheet(1).Columns().AdjustToContents();
wb.Worksheet(2).Columns().AdjustToContents();
wb.Worksheet(3).Columns().AdjustToContents();
// Save workbook to the file
wb.SaveAs(filePath);
}
Console.WriteLine($"Excel file saved at: {filePath}");
}
I don't think I need to explain how ClosedXML works.
By now, you must have noticed that to export a single sheet, we simply need to add the respective DataTable to the Worksheets collection of the workbook.
To export all sheets, we can directly add the DataSet that already contains all the DataTables to the Worksheets collection.
Finally, to save the Excel file to the desired location, we use the SaveAs() method of the XLWorkbook class.
📚 Step 5: Create Grouped Excel Sheet with Merged Cells
Currently, our third sheet repeats the same Department Name and Gender in each row. I want to merge the cells vertically for rows that have the same Department Name and the same Gender within each department.
Now let’s modify the third sheet to display the merged data. Note that column "A" represents the Department Name and column "D" represents Gender in the Excel sheet. We need to merge the cells in these columns based on similar values.
To achieve this, we first need to determine the range of cells—for example, "A1:A3"—that we want to merge, and then call the Merge() method on that range. To achive this we will use group by and the RN column.
void ExportMergedReport()
{
Console.WriteLine("Do you want to proceed?");
string ans = Console.ReadLine();
// Call your existing method that returns a DataSet
DataSet dataSet = GetEmployeeData();
string[] arr =new string[] { "A","D"};
var emps = dataSet.Tables[2]
.AsEnumerable()
.Select(x => new
{
DeptName = x.Field("DeptName"),
Gender = x.Field("Gender"),
RowNumber = x.Field("RN")
})
.ToList();
// Create a folder path (example: D:\Reports)
string folderPath = @"D:\Reports";
// Ensure folder exists
if (!Directory.Exists(folderPath))
Directory.CreateDirectory(folderPath);
// Create file path with timestamp
string filePath = Path.Combine(folderPath, $"EmployeeReport_{DateTime.Now:yyyyMMdd_HHmmss}.xlsx");
// Create Excel workbook
using (XLWorkbook wb = new XLWorkbook())
{
// Add all DataTables from DataSet
wb.Worksheets.Add(dataSet);
// Rename sheets if you know how many tables are present
if (wb.Worksheets.Count >= 1)
wb.Worksheet(1).Name = "Employee";
if (wb.Worksheets.Count >= 2)
wb.Worksheet(2).Name = "Department";
if (wb.Worksheets.Count >= 3)
wb.Worksheet(3).Name = "Merged";
var deptGroups = emps.GroupBy(x => x.DeptName);
foreach (var deptGroup in deptGroups)
{
int minDeptRow = deptGroup.Min(x => x.RowNumber) + 1;
int maxDeptRow = deptGroup.Max(x => x.RowNumber) + 1;
var dm=wb.Worksheet(3).Range("A" + minDeptRow, "A" + maxDeptRow).Merge();
dm.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
dm.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
// 2️⃣ Within this department, merge Gender
var genderGroups = deptGroup.GroupBy(x => x.Gender);
foreach (var genderGroup in genderGroups)
{
int minGenderRow = genderGroup.Min(x => x.RowNumber) + 1;
int maxGenderRow = genderGroup.Max(x => x.RowNumber) + 1;
var gm= wb.Worksheet(3).Range("D" + minGenderRow, "D" + maxGenderRow).Merge();
gm.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
gm.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
}
}
wb.Worksheet(3).Columns().AdjustToContents();
// Save workbook to the file
wb.SaveAs(filePath);
}
Console.WriteLine($"Excel file saved at: {filePath}");
}
Step-by-step explanation of ExportMergedReport()
1) Ask for confirmation
Console.WriteLine("Do you want to proceed?");
string ans = Console.ReadLine();
This prints a prompt and waits for the user to press Enter.
It's a simple guard so the user can cancel if they launched the routine accidentally. (You could check ans if you want to allow a "no" response.)
2) Load the data you will export
DataSet dataSet = GetEmployeeData();
var emps = dataSet.Tables[2]
.AsEnumerable()
.Select(x => new
{
DeptName = x.Field("DeptName"),
Gender = x.Field("Gender"),
RowNumber = x.Field("RN")
})
.ToList();
Here you call your existing method that returns a DataSet. You then read the 3rd table (Tables[2]) and project each row into a small anonymous object that contains DeptName, Gender, and RowNumber (the RN column). The result (emps) is an in-memory list you will group and inspect to decide which Excel rows to merge.
3) Prepare the output file
string folderPath = @"D:\Reports";
if (!Directory.Exists(folderPath))
Directory.CreateDirectory(folderPath);
string filePath = Path.Combine(folderPath, $"EmployeeReport_{DateTime.Now:yyyyMMdd_HHmmss}.xlsx");
Create (if needed) a folder and generate a timestamped filename so each run writes a unique file.
4) Create the workbook and add the DataSet
using (XLWorkbook wb = new XLWorkbook())
{
wb.Worksheets.Add(dataSet);
if (wb.Worksheets.Count >= 1) wb.Worksheet(1).Name = "Employee";
if (wb.Worksheets.Count >= 2) wb.Worksheet(2).Name = "Department";
if (wb.Worksheets.Count >= 3) wb.Worksheet(3).Name = "Merged";
...
wb.SaveAs(filePath);
}
wb.Worksheets.Add(dataSet) creates one worksheet per DataTable in the DataSet.
You then optionally rename the first three sheets for readability.
Finally the workbook is saved to disk with SaveAs.
5) Group by department and compute merge ranges
var deptGroups = emps.GroupBy(x => x.DeptName);
foreach (var deptGroup in deptGroups)
{
int minDeptRow = deptGroup.Min(x => x.RowNumber) + 1;
int maxDeptRow = deptGroup.Max(x => x.RowNumber) + 1;
var dm = wb.Worksheet(3).Range("A" + minDeptRow, "A" + maxDeptRow).Merge();
dm.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
dm.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
var genderGroups = deptGroup.GroupBy(x => x.Gender);
foreach (var genderGroup in genderGroups)
{
int minGenderRow = genderGroup.Min(x => x.RowNumber) + 1;
int maxGenderRow = genderGroup.Max(x => x.RowNumber) + 1;
var gm = wb.Worksheet(3).Range("D" + minGenderRow, "D" + maxGenderRow).Merge();
gm.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
gm.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
}
}
This is the heart of the routine:
Step A: emps.GroupBy(x => x.DeptName) groups all rows that have the same department name. Each deptGroup is a collection of rows that share a department.
Step B: For each department group we calculate the smallest and largest RN (row number) in that group using Min and Max. we add + 1 cause the first row in excel is always the header.
Step C: we call Range(...).Merge() to merge the vertical block in column A for that department, and set horizontal/vertical centering.
Step D: Inside each department group we again group by Gender and compute min/max row numbers to merge column D (Gender) within that department block.
Why use GroupBy and RN?
GroupBy collects all rows that logically belong together (same Department or same Gender inside a department). That gives us an easy way to ask: “What is the top row and bottom row for this group?”
RN (RowNumber) provides the actual worksheet row indices so we can convert a logical group into concrete Excel row addresses like A10:A14. Merging requires a contiguous start and end row, so we need numeric boundaries — that’s exactly what Min(RN) and Max(RN) gives us.
Important assumptions & gotchas
Contiguity: This method assumes rows that share the same DeptName (and same Gender inside that department) occupy a contiguous block in the worksheet. If the same department appears in several non-contiguous places, computing Min and Max will produce a range that includes other rows in between — which may merge unrelated rows incorrectly and thats why we have used order by in our stored procedure
🧾 Step 6: Run all three Exports
ExportEmployeeReport();
ExportMultiSheet();
ExportMergedReport();
💡 Conclusion
ClosedXML provides a simple, elegant, and powerful way to generate Excel files directly from SQL data using C#. Compared to libraries like Interop or EPPlus, it’s much easier to learn and doesn’t require Excel to be installed on the system.
In this tutorial, we learned how to:
- Fetch SQL data using ADO.NET
- Export to Excel with ClosedXML
- Apply formatting and styles
- Create grouped Excel reports with merged cells
Now your task is to extend this example to add pivot tables, conditional formatting, or direct file downloads in ASP.NET applications. Please comment if you face any challenges achieving the functionality