A fast OpenXML Excel exporter which allows the user to format the worksheet.
I was working on a project where I needed to export a large amount of data to Excel and to have that data formatted in accordance with the stake holder’s specifications. I, of course, started this journey with a Google search on how to export data to Excel.
What I discovered was there were lots of examples of how to do this, but none of them met my criteria. Either the method had no formatting or had performance issues.
Narrowing my Google search, I discovered the Open XML SDK but that had issues, too; mostly too slow or memory issues for large data sets. I further discovered that in order to combat those issues, I needed to use an XML writer and stream the data. I discovered
one URL that had a pre-written Open XML using streaming data, which the author graciously shared. It worked great, generating 500,000 rows of data in less than 30 seconds. Rather impressive but he only allowed limited formatting and it was written in a way
I could not easily modify, as he wrote all the XML himself. Bless his heart, but that seemed like a lot of work.
So what I ended up doing is writing my own Open XML exporter that allows quite a bit of custom formatting. It was not an easy task as the documentation in the SDK is less than helpful.
What I have put together is the source code and a small demo project that creates a bunch of data in different formats and shows how to format it. Instead of writing a bunch of documentation, I like to show by doing. I believe there are enough comments for
you to figure out how to use this as you need.
The exporter is not 100% comprehensive, but it will do the following:
- Word wrap
- Number/date formatting (but only the standard Excel formats. I failed to get custom formats to work.)
- Font formatting
- Borders (now configurable)
- Freeze pane
- Up to three Sheets with naming
I hope this saves you the enormous amount of time that I spent researching and/or developing this utility tool.