A Complete Tutorial on Exporting Data to Excel Using Spire.XLS
Exporting data to Excel is a core requirement for modern enterprise applications. While there are many libraries available, Spire.XLS by e-iceblue stands out as a powerful, standalone library that does not require Microsoft Office to be installed on the system.
This tutorial provides a step-by-step guide on how to export data to Excel using Spire.XLS in a C# .NET environment, covering everything from basic setup to advanced formatting. Why Choose Spire.XLS?
Independence: Runs smoothly without Microsoft Excel installed on the server or client machine.
Format Support: Supports XLS, XLSX, XLSM, and XLSB formats natively.
Performance: Highly optimized for handling large datasets efficiently.
Platform Compatibility: Works across .NET Framework, .NET Core, .NET Standard, and Mono. 1. Setting Up Your Project
To get started, you need to add the Spire.XLS library to your project. You can easily install it using the NuGet Package Manager.
Open the NuGet Package Manager Console in Visual Studio and run the following command: Install-Package Spire.XLS Use code with caution.
Alternatively, you can search for Spire.XLS in the Visual Studio NuGet Package Manager UI and install it from there. 2. Basic Data Export: Writing Array Data to Excel
The simplest way to export data is by creating a workbook, accessing a worksheet, and inserting data directly into specific cells.
Here is a quick example of creating an Excel file from scratch and writing basic data:
using Spire.Xls; class Program { static void Main(string[] args) { // 1. Initialize a new Workbook instance Workbook workbook = new Workbook(); // 2. Clear default worksheets and create a fresh one workbook.Worksheets.Clear(); Worksheet sheet = workbook.Worksheets.Add(“Sales Report”); // 3. Define headers and sample data string[] headers = { “Product ID”, “Product Name”, “Units Sold”, “Price” }; object[,] data = { { 101, “Laptop”, 15, 899.99 }, { 102, “Smartphone”, 45, 599.99 }, { 103, “Headphones”, 120, 49.99 }, { 104, “Monitor”, 30, 199.99 } }; // 4. Write headers to the first row for (int i = 0; i < headers.Length; i++) { sheet.Range[1, i + 1].Value = headers[i]; } // 5. Write rows of data for (int row = 0; row < data.GetLength(0); row++) { for (int col = 0; col < data.GetLength(1); col++) { // Spire.XLS uses 1-based indexing for rows and columns sheet.Range[row + 2, col + 1].Value = data[row, col].ToString(); } } // 6. Save the workbook to a file workbook.SaveToFile(“SalesReport.xlsx”, ExcelVersion.Version2016); System.Console.WriteLine(“Data exported successfully!”); } } Use code with caution. 3. High-Speed Exporting: Using DataTable
Looping through cell ranges can be slow for massive datasets. Spire.XLS offers an optimized method called InsertDataTable to import structured data rapidly into a worksheet.
using System.Data; using Spire.Xls; class Program { static void Main(string[] args) { Workbook workbook = new Workbook(); Worksheet sheet = workbook.Worksheets[0]; // Create a mock DataTable DataTable table = new DataTable(); table.Columns.Add(“Employee ID”, typeof(int)); table.Columns.Add(“Name”, typeof(string)); table.Columns.Add(“Department”, typeof(string)); table.Rows.Add(1, “Alice Smith”, “HR”); table.Rows.Add(2, “Bob Jones”, “IT”); table.Rows.Add(3, “Charlie Brown”, “Finance”); // Insert DataTable into the sheet starting at Row 1, Column 1 // The boolean parameters control whether to import headers and styles sheet.InsertDataTable(table, true, 1, 1); workbook.SaveToFile(“EmployeeData.xlsx”, ExcelVersion.Version2016); } } Use code with caution. 4. Beautifying the Export: Styles, Fonts, and Colors
Raw data can be difficult to read. Spire.XLS lets you programmatically format cells, change background colors, modify text fonts, and set borders to deliver a polished report.
using System.Drawing; using Spire.Xls; class Program { static void Main(string[] args) { Workbook workbook = new Workbook(); Worksheet sheet = workbook.Worksheets[0]; // Add dummy headers sheet.Range[“A1”].Value = “Category”; sheet.Range[“B1”].Value = “Revenue”; // 1. Create a custom style for headers CellStyle headerStyle = workbook.Styles.AddStyle(“HeaderStyle”); headerStyle.Font.IsBold = true; headerStyle.Font.Color = Color.White; headerStyle.Font.Size = 12; headerStyle.Font.FontName = “Segoe UI”; headerStyle.FillPattern = ExcelPatternType.Solid; headerStyle.KnownColor = ExcelColors.Navy; headerStyle.HorizontalAlignment = HorizontalAlignType.Center; // Apply style to the first row (Header) sheet.Range[“A1:B1”].CellStyle = headerStyle; // Add dummy data sheet.Range[“A2”].Value = “Software Purchases”; sheet.Range[“B2”].NumberValue = 24500.50; // 2. Format a data column as Currency sheet.Range[“B2”].NumberFormat = “$#,##0.00”; // 3. Auto-fit column widths to prevent text truncation sheet.AllocatedRange.AutoFitColumns(); workbook.SaveToFile(“StyledReport.xlsx”, ExcelVersion.Version2016); } } Use code with caution. 5. Adding Math Formulas
Excel reports often require dynamic mathematical calculations. Spire.XLS interprets standard Excel formulas effortlessly.
using Spire.Xls; class Program { static void Main(string[] args) { Workbook workbook = new Workbook(); Worksheet sheet = workbook.Worksheets[0]; // Populate values sheet.Range[“A1”].NumberValue = 150; sheet.Range[“A2”].NumberValue = 300; sheet.Range[“A3”].NumberValue = 450; // Inject standard Excel formulas sheet.Range[“A4”].Formula = “=SUM(A1:A3)”; sheet.Range[“B4”].Formula = “=AVERAGE(A1:A3)”; // Force Spire.XLS to calculate the formula values before saving workbook.CalculateAllValue(); workbook.SaveToFile(“FormulaCalculations.xlsx”, ExcelVersion.Version2016); } } Use code with caution. Best Practices for Spire.XLS Data Export
Dispose of Objects: Wrap your Workbook creation in a using statement or call workbook.Dispose() manually to free up memory system resources when generating huge reports.
Batch Operations: Always use bulk insertion features like InsertDataTable or InsertArray rather than iterating through individual cells to save execution time.
Save Options: Ensure you target the modern ExcelVersion.Version2016 (XLSX) format rather than legacy XLS formats whenever possible to benefit from better compression and feature compatibility. Conclusion
Exporting data using Spire.XLS provides a scalable, fast, and feature-rich way to build spreadsheets out of your applications. Whether you need a simple row dump or a highly formatted financial breakdown complete with native formulas, Spire.XLS covers all bases seamlessly.
To make this tutorial more specific to your project, let me know: What specific version of .NET are you developing on?