Getting Started with ClosedXML in C#

⁠ClosedXML is a free, open-source .NET library used to read, manipulate, and write Excel 2007+ (.xlsx) files. It acts as a user-friendly wrapper around the complex OpenXML API, allowing you to generate professional spreadsheets without needing Microsoft Excel installed on the host machine. 1. Installation

To start, add the ⁠ClosedXML NuGet Package to your .NET project. Using .NET CLI: dotnet add package ClosedXML Use code with caution. Using Package Manager Console: powershell Install-Package ClosedXML Use code with caution. 2. Creating and Writing a New Excel File

You can create a new workbook, populate cells using Excel-style notation (like “A1”), format headers, and save the file. using Use code with caution. ClosedXML.Excel Use code with caution.

; // Initialize a new workbook instance using var workbook = new XLWorkbook(); // Add a worksheet to the workbook var worksheet = workbook.Worksheets.Add(“Project Report”); // Add simple text and number data worksheet.Cell(“A1”).Value = “Product Name”; worksheet.Cell(“B1”).Value = “Price”; worksheet.Cell(“A2”).Value = “Laptop”; worksheet.Cell(“B2”).Value = 1200; worksheet.Cell(“A3”).Value = “Mouse”; worksheet.Cell(“B3”).Value = 25; // Apply styles to headers var headerRow = worksheet.Row(1); headerRow.Style.Font.Bold = true; headerRow.Style.Fill.BackgroundColor = XLColor.LightBlue; // Auto-fit column widths based on content length worksheet.Columns().AdjustToContents(); // Save the workbook to your storage disk workbook.SaveAs(“SalesReport.xlsx”); Use code with caution. 3. Reading an Existing Excel File

To parse an existing document, load it into an XLWorkbook instance and iterate over the populated rows. using Use code with caution. ClosedXML.Excel Use code with caution.

; using System; // Open the workbook file path using var workbook = new XLWorkbook(“SalesReport.xlsx”); // Get the target sheet by its assigned name var worksheet = workbook.Worksheet(“Project Report”); // Loop through rows that contain actual data foreach (var row in worksheet.RowsUsed()) { // Skip the first row if it is a header if (row.RowNumber() == 1) continue; // Extract cell values safely using strong-typing string productName = row.Cell(1).GetValue(); decimal price = row.Cell(2).GetValue(); Console.WriteLine($“Product: {productName}, Price: {price}”); } Use code with caution. 4. Working with Excel Formulas

ClosedXML contains an internal evaluation engine, allowing you to inject native Excel formulas directly into target cells. using Use code with caution. ClosedXML.Excel Use code with caution.

; using var workbook = new XLWorkbook(); var worksheet = workbook.Worksheets.Add(“Budget”); worksheet.Cell(“A1”).Value = 500; worksheet.Cell(“A2”).Value = 250; // Assign a formula string using standard FormulaA1 syntax worksheet.Cell(“A3”).FormulaA1 = “SUM(A1:A2)”; workbook.SaveAs(“BudgetReport.xlsx”); Use code with caution. 5. Important Performance Rules

Memory Constraints: ClosedXML loads the entire spreadsheet file into RAM. It works perfectly for small-to-medium files but may trigger high memory overhead on files with hundreds of thousands of rows.

Thread Safety: The library is strictly thread-unsafe. Do not manipulate the same workbook object across parallel threads.

If you would like to expand on this, let me know if you want to see how to bind a C# List directly to an Excel table or if you need help with exporting Excel files in ⁠ASP.NET Core.

ClosedXML is a .NET library for reading, manipulating and writing Excel 2007+ (.xlsx, .xlsm) files. It aims to provide an intuitive and user-friendly interface to dealing with the underlying OpenXML API. · GitHub