Introduction

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 complex OpenXml API.

To add ClosedXml to your project, you can just install the publicly available nuget package “ClosedXML”.

With ClosedXml, you can work with Excel without having to open it, this helps in backed automation, where you don’t have to deal with open excel instance which can be problematic.

Brief Introduction To OpenXml

The Open XML SDK 2.5 simplifies the task of manipulating Open XML packages and the underlying Open XML schema elements within a package.

With this SDK, we can create & modify Excel, Word, PowerPoint files.

Advantages of OpenXml Formats

OpenXml has many advantages over the binary format

1. Compact files – OpenXml files are compressed, up to 75% in some cases. OpenXml format compresses all the XML files in zip format
2. Improved damaged-file recovery – Different components are stored in separate files XML files
3. Support for advanced features – OpenXml supports advanced features like Autosave & Accessibility Checker, which are not possible in binary format “.xls”
4. Better privacy and more control over personal information – With OpenXml we can identify & remove personally identifiable information using Document Inspector, if needed.
5. Better integration and interoperability of business data – Zipped XML files are easier to integrate and work with other applications like LibreOffice.
6. Easier detection of documents that contain macros – All macro files are having ‘m’ at the end of file extension to easily identify if it contains macros.

ClosedXml

Features

ClosedXml has extensive API functions to handle creation & extraction of content in Excel.
With these API calls you can modify every little detail in Excel sheet or workbook.
It supports most of the major Excel features :-

1. Formulas
2. Validation
3. Hyper-Links
4. Protection (Sheet and Cell level)
5. Conditional Formatting
6. Freeze Panes
7. Tables
8. Ranges
9. Styling
10. Page Setup (Printing)
11. Auto-Filters
12. Comments

Unsupported Features

Some features of OpenXml are still not implemented. They are listed below.

1. Macros – ClosedXml doesn’t support macros as its base library OpenXml also doesn’t support it.
2. Embedding – We cannot embed any file into Excel using ClosedXml, no APIs built for that, so some features of OpenXml still need to be implemented.
3. Charts – No functionality related to charting is present.

Overall, there are not many features that ClosedXml doesn’t support, except for Macros which the base library OpenXml itself doesn’t support.

Example code for ClosedXml

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

Conclusion

If you’re planning on working with Excel files and have knowledge in C# then I would highly advocate ClosedXml. As it’s a powerful library that can help with your ever-growing requirements.