using IronXL; using System; using System.Linq; // Supported for XLSX, XLS, XLSM, XLTX, CSV and TSV WorkBook workBook = WorkBook.Load("sample.xlsx"); // Select worksheet at index 0 WorkSheet workSheet = workBook.WorkSheets[0]; // Get any existing worksheet WorkSheet firstSheet = workBook.DefaultWorkSheet; // Select a cell and return the converted value int cellValue = workSheet["A2"].IntValue; // Read from ranges of cells elegantly. foreach (var cell in workSheet["A2:A10"]) { Console.WriteLine("Cell {0} has value '{1}'", cell.AddressString, cell.Text); } // Calculate aggregate values such as Min, Max and Sum decimal sum = workSheet["A2:A10"].Sum(); // Linq compatible decimal max = workSheet["A2:A10"].Max(c => c.DecimalValue);

using IronSoftware.Drawing; using IronXL; using IronXL.Styles; using System.Linq; WorkBook workBook = WorkBook.Load("sample.xls"); WorkSheet workSheet = workBook.WorkSheets.First(); var range = workSheet["A1:H10"]; var cell = range.First(); // Set background color of the cell with an rgb string cell.Style.SetBackgroundColor("#428D65"); // Apply styling to the whole range. // Set underline property to the font // FontUnderlineType is enum that stands for different types of font underlying range.Style.Font.Underline = FontUnderlineType.SingleAccounting; // Define whether to use horizontal line through the text or not range.Style.Font.Strikeout = false; // Define whether the font is bold or not range.Style.Font.Bold = true; // Define whether the font is italic or not range.Style.Font.Italic = false; // Get or set script property of a font // Font script enum stands for available options range.Style.Font.FontScript = FontScript.Super; // Set the type of the border line // There are also TopBorder,LeftBorder,RightBorder,DiagonalBorder properties // BorderType enum indicates the line style of a border in a cell range.Style.BottomBorder.Type = BorderType.MediumDashed; // Indicate whether the cell should be auto-sized range.Style.ShrinkToFit = true; // Set alignment of the cell range.Style.VerticalAlignment = VerticalAlignment.Bottom; // Set border color range.Style.DiagonalBorder.SetColor("#20C96F"); // Define border type and border direction as well range.Style.DiagonalBorder.Type = BorderType.Thick; // DiagonalBorderDirection enum stands for direction of diagonal border inside cell range.Style.DiagonalBorderDirection = DiagonalBorderDirection.Forward; // Set background color of cells range.Style.SetBackgroundColor(Color.Aquamarine); // Set fill pattern of the cell // FillPattern enum indicates the style of fill pattern range.Style.FillPattern = FillPattern.Diamonds; // Set the number of spaces to intend the text range.Style.Indention = 5; // Indicate if the text is wrapped range.Style.WrapText = true; workBook.SaveAs("stylingOptions.xls");

Published January 15, 2024

C# Excel Library Open Source (Comparison)

Excel files are widely used in various business contexts, particularly for data analysis and reporting. They provide a versatile platform for organizing, storing, and manipulating data. Excel's popularity stems from its ability to handle large datasets, complex calculations, and varied formatting options.

In the .NET framework, managing spreadsheet files programmatically can be challenging. Key considerations include how to efficiently read, write, and manipulate Excel files, handle different file formats like XLSX and CSV, and integrate Excel functionality into .NET applications without relying on Microsoft Office.

This article will explore three C# Open Source Excel libraries designed to simplify Excel file manipulation in a .NET environment. We will discuss their functionalities, as well as the advantages and disadvantages of using them. The libraries are:

FastExcel Library
SpreadSheet Light
NExcel

We'll also discuss IronXL as a solid Excel library for all types of complex Excel operations in C#.

FastExcel: Streamlining Excel File Operations

C# Excel Library Open Source (Comparison): Figure 1 - FastExcel NuGet package page

FastExcel is an open-source C# .NET library designed for efficient reading and writing of Excel XLSX format spreadsheets. It stands out for its ability to handle these operations rapidly, thanks to its small memory footprint. This is achieved by bypassing the Open XML SDK for data interaction and working directly with the underlying existing Open XML spreadsheets of Excel.

The primary objective of FastExcel is to offer a lightweight and fast method for interacting with Excel data, focusing on basic Excel functionalities. It's not intended to replace more feature-rich Excel packages that include advanced features like formatting. However, it excels in operations such as reading and writing Excel files, fetching cell ranges, managing Excel tables, rows, and columns, inserting texts, adding images or new sheets, file protection, and formula calculation.

Pros of FastExcel

FastExcel excels in speedy reading and writing of Excel XLSX spreadsheets. This efficiency is crucial for applications that need to process large volumes of data quickly.
This library has a small memory footprint, making it ideal for applications where memory efficiency is a concern.
It allows for efficient data processing capabilities, including sorting, filtering, merging, and complex calculations on Excel files.
It supports a variety of operations like adding images and comments, managing cell ranges and rows, providing Excel encryption, and formula calculation.

Cons of FastExcel

FastExcel is designed for fast interaction with Excel data but may not be the best fit for scenarios requiring advanced Excel features, like extensive formatting.
It requires .NET Framework 4.5 or higher, which may limit its use in environments not supporting these versions.

Spreadsheet Light: Lightweight and Efficient

C# Excel Library Open Source (Comparison): Figure 2 - Spreadsheet Light webpage

Spreadsheet Light, an effective free library in the realm of C# Excel library open source, is based on the powerful Open XML SDK. It's designed for developers who need a lightweight yet powerful tool for working with Excel files in .NET applications. This library supports various aspects of Microsoft Excel, allowing for efficient manipulation of Excel file formats and Excel spreadsheets.

Pros of Spreadsheet Light

Spreadsheet Light is compatible with Microsoft Excel 2007/2010/2013 and LibreOffice Calc, offering a broad usage scope.
It is designed to be developer-friendly, and it simplifies the process of working with Excel spreadsheets.
It is efficient in both memory usage and speed; it's capable of handling large datasets effectively.
It supports a wide range of functionalities, including conditional formatting, charts, and table creation, making it versatile for various applications.
It supports various operations such as creating new Open XML spreadsheets, handling Excel tables, and manipulating Excel worksheets, making it a versatile tool for writing Excel files.
It focuses on being light on the learning curve and memory resources, which is ideal for developers looking for a straightforward tool.

Cons of Spreadsheet Light

It doesn't support the latest version of Excel file formats.
While Spreadsheet Light is efficient for most tasks, it might encounter limitations when handling extremely large Excel files or dealing with complex VBA code and conditional formatting.

NExcel: Handling Excel with Ease

C# Excel Library Open Source (Comparison): Figure 3 - NExcel webpage

NExcel is a C# open-source Excel library that enables .NET applications to read Excel spreadsheets. It's compatible with Excel versions from 97 onwards and is easy to use and deploy in both Windows and ASP.NET Web applications. Written entirely in C#, it doesn't require Microsoft Office Excel or any third-party libraries. NExcel supports features like reading data, font and number formatting, and formulas. However, the current version only supports reading, not writing, NExcel spreadsheets and can't process spreadsheets with images. It requires Microsoft .NET Framework 1.0 or later to operate.

Pros of NExcel

NExcel supports reading data from Excel 97, 2000, XP, and 2003 spreadsheets.
NExcel doesn't require the installation of Microsoft Office Excel or third-party libraries, making it a pure .NET library.
It supports multiple languages, including English, French, German, and Spanish.
NExcel is straightforward to deploy, requiring only the addition of NExcel.dll to the application.

Cons of NExcel

The current release of NExcel can only read, not write, Excel spreadsheets.
It cannot read spreadsheets with images.

IronXL: All In One Excel Solution

C# Excel Library Open Source (Comparison): Figure 4 - IronXL webpage

IronXL is a .NET Excel library tailored for managing and manipulating Excel files within the .NET environment. You can read and write Excel files with a few lines of code using IronXL. It is built to work seamlessly with C# and specializes in handling various spreadsheet formats such as XLSX, XLS, and CSV.

IronXL is particularly useful for projects where Excel file manipulation is required without installing Microsoft Office. This makes it a valuable tool for server-side applications, web applications, and other scenarios where Office Interop is impractical or impossible.

Pros of IronXL

IronXL is straightforward to install and use; it's available via the NuGet package manager and requires no third-party add-ons.
It's compatible across various operating systems, including Windows, macOS, and Linux, and supports all recent .NET and .NET Core frameworks.
IronXL does not require the installation of MS Office or Excel Interop, which simplifies deployment and reduces compatibility issues.
IronXL provides functionalities for editing formulas, sorting data, creating and editing charts, and adjusting layout, including freeze panes and auto-sizing rows/columns.
It also offers versatile styling options for cells and can handle a wide range of data formats. Its capabilities extend to detailed workbooks and worksheet management.
IronXL supports text, number, formula, date, currency, scientific, time, boolean, and custom formats for cells.
IronXL can import data from various formats and export worksheets to formats like CSV, JSON, and others.
IronXL offers robust support, with a dedicated team available for assistance 24/7, ensuring help is always at hand for users.
It provides a trial version for evaluating its full range of features in a production environment. You can request a trial key without any payment details.

Cons of IronXL

IronXL is a licensed product, which may be a consideration for budget-conscious projects or individuals.

Conclusion

After examining the capabilities of IronXL, FastExcel, Spreadsheet Light, and NExcel, it becomes apparent that IronXL is the standout choice between .NET Excel libraries. Its strengths lie in its ability to handle complex Excel tasks with ease and efficiency. It supports the latest operating systems and .NET frameworks, making it highly adaptable.

Regular updates ensure optimal performance and cutting-edge features. IronXL simplifies Excel file manipulation, offering robust functionality for reading, writing, and editing, all within a few lines of code. This library's ability to integrate seamlessly into diverse .NET applications makes it an invaluable tool for developers. License of IronXL starts from $599. You can get more information from the image below or the IronXL license page.

C# Excel Library Open Source (Comparison): Figure 5 - IronPDF licensing page

Ultimately, the choice of an open-source C# Excel library depends on the specific needs of your project, the data you're working with, and the framework within which you're operating. Whether your focus is on creating new worksheets, handling complex calculations, or simply writing Excel files efficiently, there's an open-source library suited to your requirements.