What is the best and fastest way to write into Excel file using C#?

2 min read 09-10-2024

What is the best and fastest way to write into Excel file using C#?

When working with data in C#, especially for reporting and data analysis, writing to Excel files can be a common requirement. However, developers often find themselves wondering what the best and fastest method is for creating or updating Excel files. In this article, we'll explore some of the most popular approaches and help you decide which is best for your needs.

Understanding the Problem

The primary challenge is efficiently writing data to Excel files (.xlsx) within a C# application. Depending on the use case, the method you choose can significantly impact performance, especially when handling large datasets or requiring frequent updates. Let’s examine a typical scenario: you need to write a large number of records to an Excel spreadsheet quickly and efficiently.

The Original Code Example

Here’s a simple C# code snippet using the Microsoft.Office.Interop.Excel library to create an Excel file:

using System;
using Excel = Microsoft.Office.Interop.Excel;

class Program
{
    static void Main()
    {
        var excelApp = new Excel.Application();
        excelApp.Visible = false;

        var workbook = excelApp.Workbooks.Add();
        var worksheet = (Excel.Worksheet)workbook.Sheets[1];

        for (int i = 1; i <= 1000; i++)
        {
            worksheet.Cells[i, 1] = "Data " + i;
        }

        workbook.SaveAs("C:\\temp\\test.xlsx");
        workbook.Close();
        excelApp.Quit();
    }
}

This code initializes an Excel application, creates a workbook, fills a column with data, saves the workbook, and closes the application. While effective, this approach has some drawbacks, including performance issues when handling large datasets due to the interactivity with the Excel COM object.

Analysis and Better Alternatives

1. ClosedXML Library

One of the most popular and fastest ways to manipulate Excel files in C# is through the ClosedXML library. This library simplifies the process of creating and editing Excel files without needing to deal with the complexities of Excel Interop.

Installation: You can install ClosedXML via NuGet:

Install-Package ClosedXML

Code Example:

using ClosedXML.Excel;

class Program
{
    static void Main()
    {
        using (var workbook = new XLWorkbook())
        {
            var worksheet = workbook.Worksheets.Add("Data");
            for (int i = 1; i <= 1000; i++)
            {
                worksheet.Cell(i, 1).Value = "Data " + i;
            }
            workbook.SaveAs("C:\\temp\\test.xlsx");
        }
    }
}

2. EPPlus Library

Another great option is the EPPlus library, which also allows you to create and manipulate Excel files without relying on the Excel application. It supports many advanced Excel features and is also quite performant.

Installation: You can install EPPlus via NuGet:

Install-Package EPPlus

Code Example:

using OfficeOpenXml;

class Program
{
    static void Main()
    {
        ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
        using (var package = new ExcelPackage())
        {
            var worksheet = package.Workbook.Worksheets.Add("Data");
            for (int i = 1; i <= 1000; i++)
            {
                worksheet.Cells[i, 1].Value = "Data " + i;
            }
            package.SaveAs(new FileInfo("C:\\temp\\test.xlsx"));
        }
    }
}

Conclusion

When it comes to writing to Excel files in C#, both ClosedXML and EPPlus provide fast, efficient, and user-friendly APIs. They handle large datasets much better than the traditional Interop method by avoiding the overhead of the Excel application.

Additional Resources

By choosing either of these libraries, you can significantly improve the performance of your application while maintaining ease of use. Depending on your specific needs, you may prefer one library over the other, but both offer robust solutions for Excel manipulation in C#.