A couple of gotchas with the ClosedXML library

Barry B

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 comment