ClosedXML

ClosedXML is an open-source .NET library for reading, manipulating, and writing Excel 2007+ (.xlsx, .xlsm) files. It provides a user-friendly and intuitive API for working with Excel spreadsheets without requiring Microsoft Office to be installed on the machine.

Pros

• Easy to use API with a fluent interface for creating and modifying Excel files
• No dependency on Microsoft Office or COM Interop
• Supports a wide range of Excel features, including formulas, cell styles, and charts
• Active development and community support

Cons

• Performance can be slower compared to other Excel libraries for very large datasets
• Limited support for older Excel file formats (.xls)
• Some advanced Excel features may not be fully supported
• Learning curve for complex operations and advanced features

Code Examples

1. Creating a new workbook and adding data:

using ClosedXML.Excel;

using (var workbook = new XLWorkbook())
{
    var worksheet = workbook.Worksheets.Add("Sample Sheet");
    worksheet.Cell("A1").Value = "Hello World!";
    worksheet.Cell("A2").FormulaA1 = "=UPPER(A1)";
    workbook.SaveAs("HelloWorld.xlsx");
}

2. Reading data from an existing Excel file:

using ClosedXML.Excel;

using (var workbook = new XLWorkbook("existing_file.xlsx"))
{
    var worksheet = workbook.Worksheet(1);
    var cellValue = worksheet.Cell("A1").Value;
    var rangeValues = worksheet.Range("A1:C10").Values;
}

3. Applying styles to cells:

using ClosedXML.Excel;

using (var workbook = new XLWorkbook())
{
    var worksheet = workbook.Worksheets.Add("Styled Sheet");
    var cell = worksheet.Cell("A1");
    cell.Value = "Styled Cell";
    cell.Style.Font.Bold = true;
    cell.Style.Fill.BackgroundColor = XLColor.LightBlue;
    cell.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
    workbook.SaveAs("StyledSheet.xlsx");
}

Getting Started

To get started with ClosedXML, follow these steps:

1. Install the NuGet package in your .NET project:

   Install-Package ClosedXML
   

2. Add the following using statement to your C# file:

   using ClosedXML.Excel;
   

3. Create a new workbook or open an existing one:

   var workbook = new XLWorkbook(); // New workbook
   // OR
   var workbook = new XLWorkbook("existing_file.xlsx"); // Open existing file
   

4. Work with worksheets and cells:

   var worksheet = workbook.Worksheets.Add("My Sheet");
   worksheet.Cell("A1").Value = "Hello, ClosedXML!";
   

5. Save the workbook:

   workbook.SaveAs("MyWorkbook.xlsx");