C# and Excel

Question

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.

Mayooran Navamany · 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.

Vishal Yelve · Answer

Hi Ismael, Do refer my article https://www.c-sharpcorner.com/article/how-to-read-data-from-an-excel-sheet/

Tahir Ansari · Answer

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

Leon D · Answer

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

Tuhin Paul · Answer

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.