Skip links
prakash software logo

Looking for collaboration for your next project? Do not hesitate to contact us to say hello.

info@prakashinfotech.com

Facebook Twitter Github Linkedin Instagram Stack-overflow
prakash software logo
Prakash Software Solutions
openxml vs closedxml
Published in: .NET

OpenXML vs ClosedXML: Understanding ClosedXML and Its Differences from OpenXML

Author
Published on:

OpenXML vs ClosedXML, most of you reading this content piece are well aware about the terms OpenXML and ClosedXML. In this blog post I will take you through the important pointers of OpenXML vs ClosedXML.

So, let’s get started.

ClosedXML is a .NET library designed for reading, manipulating, and writing Excel 2007 and later file formats (.xlsx, .xlsm). Its goal is to offer an intuitive and user-friendly interface for interacting with the underlying OpenXML API.

Install the library through .NET CLI

C:\source> dotnet add package ClosedXML

ClosedXML allows you to create Excel files without the Excel application. The typical example is creating Excel reports on a web server.

using var workbook = new XLWorkbook();
var worksheet = workbook.AddWorksheet("Sample Sheet");
worksheet.Cell("A1").Value = "Hello World!";
worksheet.Cell("A2").FormulaA1 = "MID(A1, 7, 5)";
workbook.SaveAs("HelloWorld.xlsx");

Types

An Excel workbook is essentially a 2D array of input/output cells, where each cell can hold a value of one of the following types:

  • Blank: An empty value, typically used for blank cells or optional arguments of functions.
  • Logical: A boolean value.
  • Number: A double-precision floating-point value, excluding NaN or Infinity. Numbers also represent dates and timespans through serial datetime.
  • Text: A string of up to 32,767 characters.
  • Error: An Excel error value, such as #DIV/0.

We will refer to a union of these types as a scalar value.

A workbook also contains formulas, which are recipes that take input values and calculate new values. Formulas are used in:

  • Cell formulas: The output of the formula is written into the cell.
  • Names: Although primarily used to refer to a range of cells, names can contain any formula expression (e.g., 1+2).
  • Array formulas: These formulas change the values of a 2D array of cells.

Values used during formula evaluation can be of the types listed above and can also include:

  • Array: A 2D array of scalar values.
  • Reference: A reference to a range of cells, which may be non-contiguous.

Advantages of OpenXML Formats

OpenXML offers several advantages over the traditional binary format:

  • Compact Files: OpenXML files are compressed, reducing file size by up to 75%. The format compresses all XML files into a zip format.
  • Improved Damaged-File Recovery: Different components are stored in separate XML files, enhancing the ability to recover damaged files.
  • Support for Advanced Features: OpenXML supports advanced features like Autosave and Accessibility Checker, which are not available in the binary “.xls” format.
  • Better Privacy and Control Over Personal Information: With OpenXML, you can use the Document Inspector to identify and remove personally identifiable information if needed.
  • Enhanced Integration and Interoperability: Zipped XML files are easier to integrate with other applications, such as LibreOffice, facilitating better business data interoperability.
  • Easier Detection of Documents Containing Macros: Files with macros have an ‘m’ at the end of their file extension, making it easy to identify macro-enabled documents.

Let’s now move to ClosedXML.

ClosedXML

ClosedXML provides an extensive API for creating and extracting content in Excel files. These API functions allow you to modify every detail in an Excel sheet or workbook.

Features

It supports most major Excel features, including:

  • Formulas
  • Validation
  • Hyper-Links
  • Protection (Sheet and Cell level)
  • Conditional Formatting
  • Freeze Panes
  • Tables
  • Ranges
  • Styling
  • Page Setup (Printing)
  • Auto-Filters
  • Comments

Unsupported Features

While ClosedXML is a powerful library, some OpenXML features remain unimplemented. These include:

  • Macros: ClosedXML does not support macros, as the underlying OpenXML library also lacks this capability.
  • Embedding: You cannot embed files into Excel using ClosedXML, as no APIs are available for this purpose.
  • Charts: Functionality related to creating or manipulating charts is not present.

Overall, the number of unsupported features in ClosedXML is limited, with macros being a notable exception due to the constraints of the base OpenXML library.

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");
}

Wrapping Up

If you plan to work with Excel files and have experience in C#, my personal recommendation is ClosedXML. This powerful library can effectively meet your evolving requirements for Excel file manipulation and automation.

Happy Exploring!

You may also like