How to Read and Write Excel Files Easily with ClosedXML Handling Excel files in .NET applications often feels cumbersome, typically requiring heavy COM interop or low-level OpenXML manipulation. ClosedXML changes this by offering an intuitive, user-friendly wrapper around the OpenXML SDK, making reading, writing, and styling Excel (.xlsx) files remarkably easy.
This article explores how to get started with ClosedXML to streamline your Excel automation tasks. 1. Prerequisites and Installation
ClosedXML is a .NET library designed for .NET Framework 4.6+ and .NET Core/5/6/7/8/9.
Install via NuGet: Open your Package Manager Console and run: powershell Install-Package ClosedXML Use code with caution. Or via .NET CLI: dotnet add package ClosedXML Use code with caution. 2. Creating and Writing Excel Files
Creating an Excel file with ClosedXML is straightforward. You start by creating a XLWorkbook object, adding a worksheet, manipulating cells, and saving. Example: Generate a Spreadsheet using Use code with caution. ClosedXML.Excel Use code with caution.
; public void CreateExcel() { // 1. Create a new Workbook using (var workbook = new XLWorkbook()) { // 2. Add a Worksheet var worksheet = workbook.Worksheets.Add(“Sample Sheet”); // 3. Add data to cells (using A1 notation or Row/Column) worksheet.Cell(“A1”).Value = “Product”; worksheet.Cell(“B1”).Value = “Price”; // Styling headers worksheet.Range(“A1:B1”).Style.Font.Bold = true; worksheet.Range(“A1:B1”).Style.Fill.BackgroundColor = XLColor.LightGray; // Adding data worksheet.Cell(2, 1).Value = “Laptop”; worksheet.Cell(2, 2).Value = 1200; worksheet.Cell(3, 1).Value = “Mouse”; worksheet.Cell(3, 2).Value = 25; // Auto-fit columns worksheet.Columns().AdjustToContents(); // 4. Save the file workbook.SaveAs(“Products.xlsx”); } } Use code with caution.
Key strengths include setting formulas (cell.FormulaA1 = “…”) and easy range styling. 3. Reading Excel Files
Reading is just as simple. You open an existing file, navigate to a worksheet, and iterate through rows and columns. Example: Reading Data
using ClosedXML.Excel; public void ReadExcel() { // 1. Load the Workbook using (var workbook = new XLWorkbook(“Products.xlsx”)) { // 2. Get the first worksheet var worksheet = workbook.Worksheet(1); // 3. Find the used range to know the data boundaries var rows = worksheet.RangeUsed().RowsUsed(); foreach (var row in rows) { // Skip header if needed if (row.RowNumber() == 1) continue; string product = row.Cell(1).GetString(); double price = row.Cell(2).GetValue Use code with caution.
You can read data row-by-row or iterate directly through worksheet.Cells(). 4. Key ClosedXML Features
Cell Addressing: Supports both Excel notation (“A1”) and Row/Column index (1, 1).
Data Types: Automatically handles various types (string, int, double, DateTime) when assigning to cell.Value.
Styling: Rich styling options (colors, fonts, borders, alignments).
Ranges: Easy manipulation of ranges (e.g., worksheet.Range(“A1:C10”)).
Formula Support: Easily insert formulas like worksheet.Cell(“C1”).FormulaA1 = “=A1*B1”;.
Performance: Excellent for most business applications, as it doesn’t require MS Office interop.
ClosedXML provides a powerful, clean API for Excel manipulation in C#. Whether you are generating reports, importing data from spreadsheets, or formatting output files, it significantly reduces code complexity compared to lower-level libraries.
If you are looking to do more advanced tasks, such as creating charts, pivot tables, or handling specific cell validation rules, I can explain how to implement those in ClosedXML, too. What type of Excel task are you looking to automate next? Reading from Excel File using ClosedXML – Stack Overflow
Comments. Add a comment. 2. It works easily. XLWorkbook workbook = new XLWorkbook(FilePath); var rowCount = workbook.Worksheet(1). Stack Overflow Import/Read Excel files in C# Using ClosedXML
Leave a Reply