Exporting a DataTable to Excel in C# is a common task in .NET development, such as generating reports, exporting logs, or preparing data for sharing across systems. By using a standalone component, developers can quickly export data and apply formatting to create professional, ready-to-use Excel files from DataTables without relying on complex setup or external dependencies.
To streamline this process, Spire.XLS for .NET offers a lightweight and fully independent library. In this article, you'll learn how to export a DataTable to Excel (.xlsx or .xls) in C#, apply formatting to improve readability, and address common export scenarios effectively.
Quick Navigation
- Install and Configure Spire.XLS
- Step-by-Step: Export DataTable to Excel
- Format and Style the Exported Excel File
- Common Issues and Solutions
- Conclusion
- Frequently Asked Questions
Install and Configure Spire.XLS
Before you start, make sure your project includes Spire.XLS for .NET.
Install Spire.XLS via NuGet
Run this command in the NuGet Package Manager Console:
Install-Package Spire.XLS
Spire.XLS works with .NET Framework, .NET Core, .NET 6/7+, and ASP.NET projects — no Microsoft Office installation required.
Step-by-Step: Export DataTable to Excel in C#
The following steps demonstrate how to export a DataTable to an Excel file using Spire.XLS, including data preparation, file generation, optional streaming, and output formatting.
1. Create a Sample DataTable
First, create a DataTable and add some sample rows:
DataTable dt = new DataTable("Employees");
// Insert columns
dt.Columns.Add("ID", typeof(int));
dt.Columns.Add("Name", typeof(string));
dt.Columns.Add("Department", typeof(string));
dt.Columns.Add("Join Date", typeof(DateTime));
dt.Columns.Add("Salary", typeof(double));
dt.Columns.Add("Full-Time", typeof(bool));
dt.Columns.Add("Bonus Rate", typeof(decimal));
// Insert rows
dt.Rows.Add(1001, "Alice", "HR", new DateTime(2021, 5, 10), 55000.5, true, 0.05m);
dt.Rows.Add(1002, "Bob", "IT", new DateTime(2020, 11, 23), 72000.0, false, 0.03m);
dt.Rows.Add(1003, "Carol", "Finance", new DateTime(2019, 8, 15), 63000.75, true, 0.07m);
dt.Rows.Add(1004, "David", "Marketing", new DateTime(2022, 3, 8), 48800.0, true, 0.06m);
Tip: This is just sample data — you can bind any DataTable your app generates.
2. Import DataTable and Save to Excel File
Next, initialize the Excel workbook, import the DataTable into a worksheet, and save the file:
// Create a new workbook
Workbook workbook = new Workbook();
// Clear the default worksheets and add a new one
workbook.Worksheets.Clear();
Worksheet sheet = workbook.Worksheets.Add(dt.TableName);
// Import the DataTable starting at cell A1
sheet.InsertDataTable(dt, true, 1, 1);
// Save as XLSX (recommended)
workbook.SaveToFile("EmployeeData.xlsx", FileFormat.Version2016);
// Or save as XLS (older format)
workbook.SaveToFile("EmployeeData.xls", FileFormat.Version97to2003);
Explanation:
- Workbook is the container for your Excel file.
- InsertDataTable(dataTable, includeColumnHeaders, startRow, startColumn) maps the entire table to the Excel grid.
- SaveToFile() writes the file to disk in your chosen format.
Output Preview
Example of the exported Excel file:
3. Export Excel File as Stream in ASP.NET
When building a web app, you might want to export the file directly as a stream instead of saving to disk:
MemoryStream stream = new MemoryStream();
workbook.SaveToStream(stream, FileFormat.Version2013);
stream.Position = 0;
Return this MemoryStream in your ASP.NET controller to trigger a file download in the browser.
For additional tips on managing Excel files in C#, check out How to Create and Manipulate Excel Files in C#.
Format and Style the Exported Excel File
Formatting is optional but recommended for creating professional Excel files. Below is how you can format the exported content using Spire.XLS.
// Style the header row
CellRange header = sheet.Rows[0];
header.Style.Font.IsBold = true;
header.Style.Font.FontName = "Arial";
header.Style.Font.Size = 13;
header.Style.Color = Color.LightGray;
header.Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thick;
// Style the data rows
for (int i = 1; i < sheet.Rows.Length; i++)
{
CellRange dataRow = sheet.Rows[i];
dataRow.Style.Font.FontName = "Times New Roman";
dataRow.Style.Font.Size = 11;
dataRow.BorderInside();
}
// Format date column to display as date
CellRange dateColumn = sheet.Range[2, 4, sheet.Rows.Length + 1, 4];
dateColumn.Style.NumberFormat = "yyyy-mm-dd";
// Auto-fit columns
sheet.AllocatedRange.AutoFitColumns();
Key Properties and Methods:
- Style: Applies font, color, border, number formatting, etc. to cells.
- AutoFitColumns(): Automatically adjusts column width to fit content.
- NumberFormat: Sets how dates or numbers are displayed in Excel, e.g., "yyyy-mm-dd".
- BorderInside(): Adds internal borders to improve table readability.
Formatted Output Preview
Excel file with formatted header and date column:
For more advanced number formatting options, see how to set number format in Excel using C#.
Common Issues and Solutions
- File won’t open or shows corruption error
Ensure streams are closed properly and file extensions match the format.
- Special characters or non-English text look garbled
Confirm strings are UTF-8 encoded and use appropriate fonts.
- Columns too narrow
Use AutoFitColumns() to adjust widths automatically or use CellRange.ColumnWidth to set a fixed column width.
Conclusion
Exporting a DataTable to Excel in C# is straightforward with Spire.XLS. This approach lets you create .xlsx or .xls files easily without relying on Office, while giving you full control over the output layout for both desktop and web applications.
If needed, you can also request a free temporary license to unlock the full feature set for evaluation.
Frequently Asked Questions
Q1: How to convert DataTable to Excel in C#?
You can use sheet.InsertDataTable() from Spire.XLS to load a DataTable into a worksheet, then save it as an Excel file using workbook.SaveToFile().
Q2: Is there a free library to export a DataTable to Excel in C#?
Yes — Free Spire.XLS for .NET is a standalone library that lets you create and export Excel files directly in C# without needing to install Microsoft Office.
Q3: Can I export DataTable to Excel in ASP.NET?
Yes, the same logic can be applied in ASP.NET by generating the workbook in a controller and streaming it back as a downloadable file.
Q4: What's the difference between .xlsx and .xls export?
.xlsx is the newer Office Open XML format, compatible with Excel 2007 and later. .xls supports legacy Excel 97–2003 but is limited to 65,536 rows.
