Excel spreadsheets are a universal tool for data analysis, reporting, and data management, widely used in businesses of all sizes. One common need in software development, especially when dealing with Excel, is the ability to export data programmatically. This ability can significantly improve automation, data integration, and data export processes in applications. Here, we will explore five different methods to export Excel sheets using C#, highlighting their unique features, best use cases, and how they can be implemented in your projects.
Method 1: Using EPPlus
EPPlus is an excellent library for reading and writing Excel 2007⁄2010 files, supporting all cell data types, formatting, and various Excel features.
- Installation: Begin by installing EPPlus through NuGet Package Manager Console or the Package Manager UI.
- Exporting Data: Below is a simple code snippet for exporting data to an Excel file:
using OfficeOpenXml;
using System.IO;
public void ExportUsingEPPlus(List<MyData> data, string filePath)
{
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
using (ExcelPackage pck = new ExcelPackage(new FileInfo(filePath)))
{
var ws = pck.Workbook.Worksheets.Add("Sheet1");
ws.Cells["A1"].LoadFromCollection(data, true);
pck.Save();
}
}
📌 Note: EPPlus supports Excel 2007 (.xlsx) format and above. It doesn't natively support the older .xls format.
Method 2: Interop Excel
The Microsoft.Office.Interop.Excel library allows interaction with Excel as if it were an automated Excel session.
- Requirements: This method requires Excel installed on the server where the code will run.
- Creating and Exporting: Here's how to use this library:
using Excel = Microsoft.Office.Interop.Excel;
public void ExportUsingInterop(List<MyData> data, string filePath)
{
var excelApp = new Excel.Application();
excelApp.Visible = false;
var workbook = excelApp.Workbooks.Add();
var worksheet = workbook.Sheets[1];
// Populate the worksheet with data
for (int i = 0; i < data.Count; i++)
{
worksheet.Cells[i + 1, 1] = data[i].Property1;
worksheet.Cells[i + 1, 2] = data[i].Property2;
// etc...
}
workbook.SaveAs(filePath);
workbook.Close();
excelApp.Quit();
Marshal.ReleaseComObject(worksheet);
Marshal.ReleaseComObject(workbook);
Marshal.ReleaseComObject(excelApp);
}
Method 3: Using OpenXML SDK
For a lightweight, yet powerful solution, consider using the OpenXML SDK by Microsoft, designed for .NET Framework.
- Benefits: No Excel installation required, uses XML for file manipulation.
- Creating and Exporting:
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
public void ExportUsingOpenXML(List<MyData> data, string filePath)
{
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(filePath, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
worksheetPart.Worksheet = new Worksheet();
// Set up cells
// ...
workbookpart.Workbook.Save();
spreadsheetDocument.Close();
}
}
📌 Note: OpenXML can be complex for intricate formatting, but is extremely fast for basic data export.
Method 4: Using ClosedXML
ClosedXML is a wrapper library around OpenXML, simplifying many tasks involved in Excel manipulation.
- Simplicity: ClosedXML provides a more intuitive API for developers familiar with Excel manipulation.
- Example Usage:
using ClosedXML.Excel;
public void ExportUsingClosedXML(List<MyData> data, string filePath)
{
using (var workbook = new XLWorkbook())
{
var worksheet = workbook.Worksheets.Add("Sample Sheet");
worksheet.Cell(1, 1).InsertTable(data);
workbook.SaveAs(filePath);
}
}
Method 5: Using ExcelDataReader
While primarily used for reading Excel files, ExcelDataReader can also help export data when combined with a third-party library like EPPlus or OpenXML.
- Combining Libraries: Below is an example of using ExcelDataReader with EPPlus:
using ExcelDataReader;
using OfficeOpenXml;
public void ExportUsingCombinedMethods(List<MyData> data, string filePath)
{
using (var stream = File.Open(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite))
{
using (var reader = ExcelReaderFactory.CreateReader(stream))
{
// Read existing data if needed
// ...
// Create new ExcelPackage from existing file
using (ExcelPackage package = new ExcelPackage(new FileInfo(filePath)))
{
var worksheet = package.Workbook.Worksheets.Add("Sheet1");
worksheet.Cells["A1"].LoadFromCollection(data, true);
package.Save();
}
}
}
}
This article has discussed five different methods for exporting Excel sheets in C#. Each method has its unique benefits:
- EPPlus offers simplicity and extensive formatting options but requires a separate package and has limitations on older Excel formats.
- Interop Excel is straightforward but requires Excel installation and can lead to performance issues on servers.
- OpenXML SDK is powerful for manipulating Excel files without the need for Excel installation but can be complex for detailed formatting.
- ClosedXML simplifies the use of OpenXML with a more user-friendly API, suitable for many general tasks.
- ExcelDataReader when used with other libraries, can provide a hybrid approach for reading and writing Excel files.
When choosing a method, consider the context in which the export will be performed, such as the need for advanced formatting, whether Excel is installed, performance concerns, and the complexity of the task at hand. Understanding these tools allows developers to tailor the export process to meet specific project requirements.
Frequently Asked Questions
Which method is best for large datasets?
+
OpenXML SDK is highly efficient for exporting large datasets due to its performance capabilities and XML-based file structure.
Can these methods handle complex Excel formatting?
+
EPPlus and ClosedXML are known for their straightforward handling of complex Excel formatting, whereas OpenXML SDK requires more manual configuration.
Are there licensing issues when using these libraries?
+
EPPlus is licensed under LGPL, ClosedXML under MIT, and OpenXML SDK is part of the Microsoft SDKs, which can be freely used with .NET Framework projects.
Can these libraries work with both Windows and non-Windows environments?
+
EPPlus and OpenXML can work in a Windows environment. Interop Excel requires Windows and Excel installed. ClosedXML can run on Windows, Linux, and macOS with the appropriate .NET Core support.
