A couple of gotchas with the ClosedXML library

OpenXML is the file-structure standard upon which modern MS Office documents are created.  ClosedXML is an open-source .NET code library built to more easily implement that standard and allow .NET developers an abstraction against that standard for which they can more easily and efficiently code.

I like the OpenXML standard for Microsoft Office kinds of documents.  However, the open-source library ClosedXML that supports development of spreadsheets to the OpenXML standard needs a little tweaking and more developer contributions.

OPENXML ClosedXML_small3

 

 

A clarification is in order in case you’re confused (and rightly so):  OpenXML is the file-structure standard upon which modern MS Office documents are created.  ClosedXML is an open-source .NET code library (on GitHub) built to more easily implement that standard and allow .NET developers an abstraction against that standard for which they can more easily and efficiently code.  ClosedXML – probably not the best name for the library/project, but it is what was chosen.

A couple of things:

  • Error Handling:  If you generate a formula-string for a cell, and then ascribe the formula-string to the value of that cell, but somehow leave the style of the cell to .Number (because the formula will generate a number), like:
    • worksheet.Cell(rowCount, columnCount - 3).DataType = XLCellValues.Number;
  • …then ClosedXML will not be able to handle it and stack-overflow. Of course, the error handling library/code you implement should can handle this and let you know about it, but that’s not my point.
  • Reference Documentation / Intellisense:  It’s practically non-existent, except a few articles and lots of forum discussion on GitHub.  I find that plain inadequate, especially on the Intellisense side of things.

Another random note on ClosedXML:  If you put a formula-string into a cell, separate Excel function parameters (if applicable to your situation) using commas, not semi-colons (like would be standard in a normal Excel function call). You’ll thank me later. You’re welcome in advance.

So I am putting my money where my mouth is and starting to contribute to work on the ClosedXML library, because I use it fairly frequently and want to make it better, especially so I can use it with less frustration.

Is EPPlus (a competing OpenXML library for spreadsheets) any better on these fronts?  I used it a long time ago, but don’t know the progression of the library.  Start a discussion in the comments and let me know.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s