How to Resolve ClosedXML Error

One of my projects requires capability to create and download pre-formatted excel file to the web client. In the process of finding the suitable application packages that will handle this task, I’ve found ClosedXML utility that seems to fulfill this demand. It is also an open source application which has some advantage of cost reduction 🙂

So, I decided to perform some assessment of this software before deploy it to production stage. I’ve created my template excel file, save it in the Open XML format.

After passing several feature testing, I stop at an obscure error trying to perform copy method of the excel worksheet:

ox01

The error says “Sequence contains no matching element”. To isolate the problem, the routine is moved to desktop application. After that I obtain and compile the source code of ClosedXML application.

The debugging result as follows:

ox02

The application crashes when trying to enumerate existing ranges called “_Regression_Int” and the Ranges object throws exception because it points to non existent physical range. I don’t recall creates this weird range name, it must be an artifact of previous version of office application or there are some flaws in name creation routine.

The Open XML format is actually consist of compressed files, so I decided to decompress and examine the Workbook.xml inside the folder as follows:

ox03

There’s a range definition but there are no actual range object, so I decided to remove this pesky thing, and perform compression again. This time, the worksheet can be copied without any error.

Advertisements

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


%d bloggers like this: