1

I want to load a large Excel workbook, modify a few fields and then save the result. Unfortunately, ClosedXML errors on save on functions it doesn't support, eg:

NameNotRecognizedException: The identifier `_xlfn.IFNA` was not recognised.

Looking through issues and docs, I'm aware that certain functions can't be evaluated by ClosedXML, but I haven't found a way to suppress function evaluation on save (I've tried messing with Save options and no dice), and would like to know if that's not possible because of how xlsx or ClosedXML works, or if I'm just not reading carefully enough.

Or if skipping formula evaluation is not possible, not even if I fork ClosedXML, I'm looking for a well-supported alternative, free or paid. I'd rather not use Excel Interop though.

Any kind soul able to enlighten me here?

(PS: I'm also aware that I can rewrite formulae like IFNA and IFS in order to get near-equivalent behavior, but I'd like to preserve the end-user quality of life implicit in things like IFS.)

Matthew Lowe
  • 1,350
  • 1
  • 17
  • 29

1 Answers1

0

After discovering that not all of the workbooks I was testing exhibited this problem, I figured out that the problem was caused by what looks like an error in the xlsx itself.

Normally when a field is prefixed with a single quote, it forces Excel to treat it as a literal string, and Excel stashes its value in storedStrings and gives it a style that with the attribute quotePrefix="1". The files that were breaking had empty cells that for some reason had been assigned styles with quotePrefix="1". Removing that attribute from styles.xml allowed the workbook to load and save without errors.

As far as I know those template spreadsheets had only been touched with Excel, so I have to assume this is something that was introduced in some previous version of Excel and never cleaned up.

Matthew Lowe
  • 1,350
  • 1
  • 17
  • 29