C# and Excel

Ismael Oliveira

1y1.2kC#

Hi. I'm working with Visual Studio 2022 and I have an application that uses excel. I'd like to know your opinion on the interfaces available: Interop, ClosedXML or any other. Which one is the best?
Thank you.

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Mayooran Navamany1y ago
Accepted answer
For working with Excel in .NET applications, there are several libraries you can choose from, each with its strengths. Here's a comparison of some of the most popular ones:
1. Interop (Microsoft.Office.Interop.Excel)
- Pros:
  
  Provides full access to all Excel features and capabilities because it directly interacts with Excel.
  
  Well-suited for tasks where complex Excel functionalities (macros, formatting, advanced formulas) are needed.
- Cons:
  
  Requires Excel to be installed on the machine where the application is running.
  
  Slower performance, especially in server environments or large-scale processing.
  
  Not suitable for use in ASP.NET or other web applications due to the need for Excel to be installed.
  
  Can be prone to memory leaks if not managed carefully.
Best For: Desktop applications where you need complete Excel functionality and the environment is guaranteed to have Excel installed.
2. ClosedXML
- Pros:
  
  Lightweight and doesn't require Excel to be installed on the server or machine.
  
  Very easy to use, with a clean, intuitive API for basic Excel tasks like reading/writing data, styling, and handling tables.
  
  Excellent performance for reading/writing Excel files.
  
  Doesn't suffer from the same memory management issues as Interop.
- Cons:
  
  Limited to more standard Excel tasks—doesn't support macros or other advanced features like Interop does.
  
  Might be a bit limited if you need very advanced Excel manipulations.
Best For: Applications that need to generate or read simple to moderately complex Excel files without requiring Excel to be installed. Great for server-based applications like web apps.
3. EPPlus
- Pros:
  
  Similar to ClosedXML in that it doesn't require Excel to be installed.
  
  Fast and has better support for advanced Excel features (formulas, charts, etc.) compared to ClosedXML.
  
  Works well for both reading and writing Excel files.
- Cons:
  
  Licensing concerns: EPPlus moved to a LGPL license, which might require you to purchase a commercial license depending on your usage.
  
  Some limitations in comparison to Interop for very advanced features.
Best For: Applications that need more advanced Excel features but want the performance of a non-Interop solution.
+7
Leon D1y ago
You can also check Spire.XLS, it has a free version named Free Spire.XLS:

https://www.nuget.org/packages/Spire.XLS

https://www.nuget.org/packages/FreeSpire.XLS

This library supports various excel formats, including .xls, .xlsx, .xlsm, .xlsb, and more. Additionally, it operates independently, so there’s no need to have MS Office or any other software installed. Here is the library's official documentation for your reference:

https://www.e-iceblue.com/Tutorials/Spire.XLS/Spire.XLS-Program-Guide/Spire.XLS-Program-Guide-Content.html
+3
Tuhin Paul1y ago
```
 
using OfficeOpenXml;

class EPPlusExample
{
    public void CreateExcelFile()
    {
        // Create a new Excel package
        using (ExcelPackage package = new ExcelPackage())
        {
            // Add a new workbook
            ExcelWorkbook workbook = package.Workbook;

            // Get the first sheet
            ExcelWorksheet worksheet = workbook.Worksheets.Add("Sheet1");

            // Write data to cells
            worksheet.Cells[1, 1].Value = "Hello";
            worksheet.Cells[1, 2].Value = "World";

            // Save the package
            package.SaveAs(new FileInfo("example.xlsx"));
        }
    }
}
```
- The ExcelPackage class represents an Excel file. This creates a new instance of an Excel package, which will be the Excel file in memory.
- The using statement ensures that the resources used by the ExcelPackage will be automatically disposed of once the operation is complete.
- The Workbook property of the ExcelPackage object gets the workbook associated with the Excel file. A workbook can have multiple sheets.
- The Cells property is used to access specific cells by their row and column indices. The cell at position [1, 1] (i.e., A1) is assigned the value "Hello", and [1, 2] (i.e., B1) is assigned the value "World".
- The SaveAs method saves the Excel package to a file. Here, the file is saved as "example.xlsx" in the current directory. The FileInfo class is used to specify the file location and name.
+3
Vishal Yelve1y ago
Hi Ismael,
Do refer my article

https://www.c-sharpcorner.com/article/how-to-read-data-from-an-excel-sheet/
+6
Tahir Ansari1y ago
Refer this article

https://www.c-sharpcorner.com/article/creating-excel-reports-with-epplus-in-asp-net-core-8-0/
+5