I’ve generated a report or two in my day. My usual tools of choice have been C# and a 3rd party (free) Excel library. Some notable 3rd party libraries are EPPlus and NPOI.
I’ve used this approach because I usually had more time than money. This means time to deal with the gaps that are common in free 3rd party libraries instead of paying the money for a professional library.
Those times are gone. I’m now working on multiple applications, time is scarce, and I hope the cost of a license will pay for itself by reducing development time.
There’s another unfortunate turn: I can’t do some of my current reports in an Excel template. I’m now in a completely new world. Word and PowerPoint world – and it’s scary. A few searches show there are very few 3rd party libraries exist for these document types.
Create a printable form for a change order.
I can’t use Excel for this because the format is very different. I considered using an HTML to PDF library but didn’t find a (free) 3rd party library that was a good fit.
This means I need to generate the the template in some way, so Word it is.
Aspose.Words and MailMerge.
There’s a few quick tutorial videos and API references available:
This was way easier than I thought it was going to be
Aspose.Words takes advantage of built-in Word features to do data replacement: Mail Merge and Merge Fields. They even show you how to do it as part of their tutorial. This is huge, in my opinion. Many third party libraries may use existing features but won’t include that in the demonstration of how to use their library.
Overall, it took me about 4 hours to get the template 90% completed. This includes the time I spent making the template (which needed to happen anyway). The rest of the time was spent converting my existing business objects into DataTables to load into the Merge Fields I put into my template. In total, I only wrote 200 lines of code which includes retrieving the all the data from the database, converting it into DataTables, and saving the template. I could probably optimize more if I wanted to. This far exceeded my expectations of time savings.
The other huge advantage of this is that it will really the development effort needed when the template is updated. Even an end user could update the template and just move the Merge Fields around. This is a great thing for developers whose company is frequently re-branding, or need to support different template formats for different regions.
I expect I’ll get some questions, like:
Why not the Office Interop API?
If you’re asking this question, you’ve obviously never tried to generate many files from a server with this. It will inevitably leave many instances of the application floating around, eating up RAM and CPU until you need to do some admin intervention. Until Microsoft puts some serious effort into their API, it shouldn’t even be discussed.
There are 3rd party libraries that support MailMerge, so what?
NPOI also supports Excel, sort of. I have hit a wall many times when editing Excel files in both NPOI and EPPlus. I really like both libraries, but there’s no denying they have their limitations and bugs. Once your Excel template has some charts and graphs, you will hit a wall. And I mean a big giant brick wall with turrets on top and armed guards. I suspect that Microsoft intentionally adds “features” that conveniently breaks the efforts of third party libraries. If you want a library that is going to keep up with the changes, you’re going to have to pay for it. You can pay with your time by contributing to third party libraries, or you can pay with your money to a vendor like Aspose. As I mentioned before, time is definitely not an option these days.