My programming task is rather easy, but without the right tools it can be a lot of work. I have a table with a set of records that need to be exprorted into a Microsoft Excel sheet. In most companies you have two options:
- Office Automation importing the type libraries into Delphi – this very blog already has an example how to automate Microsoft Outlook. This approach does not require any additional tools and also does not incur any additional costs.
- Using a third party library that produces Microsoft Excel documents.
From what I wrote above, all your managers will go for the automation approach right away. Why? Because they consider it a freebie. Why invest into something you can also use for free. However, not so fast. Office Automation requires Microsoft Office to be installed on the system your software is supposed to produce the Microsoft Excel output on. Think about the term: You automate another application. You do not really produce a file in a different format.
Thus, a third party libraries might do the job without Microsoft Office being installed. Especially considering if you have a web service that needs to produce an Microsoft Excel or PDF document, the requirement to have Microsoft Excel installed might be a no go on a server system. Furthermore — it does not really apply to VCL, but Delphi offers more as we know — what if you have to write an app for the Mac and for Windows?
One of those products is FlexCel from TMSSoftware. I have been using FlexCel since 2004. Back then I used the .NET library inside a C# ASP.NET web application exactly because of the fact that my web hoster -obviously- does not host Microsoft Office … Try using Office automation on the iPhone… Luckily, FlexCel is also available for other frameworks:
Today, I was using the VCL variant of the product. I was surprised what has happened in the last few years. The library offer full reporting in a way that was not possible back in 2004. Basically, you create an Excel sheet with certain text elements and these elements get replaced with your content. The best thing is that you simply have to define one line and the reporting engine iterates and creates the content for all your records in the dataset. Of course, you have full control “how” the new lines are being added.
You might get hit at first by the sheer amount of quality documentation content that you get with the library, but as documentation is always forgotten, the documentation for this product really has to be mentioned properly. It is outstanding! I have seen my fair share of guides, but these really stand out: After you installed the product you do not get the regular release notes; you rather get a document that gives you 10 detailed examples about the most important features. Not only do you get a help file for the classes, a developer guide, no, you also get a design guide that teaches you how to set up the Excel sheets for the different usage scenarios.
Still, I was uncertain which approach to use for the task that was required. Then I noticed “APIMate”.
What a great tool that is… and it is hidden so well.
Basically, you feed this tool an existing Excel worksheet and it produces the Delphi or C++ source code that would be required to create that exact document using the FlexCel class library. Looking at the output and playing a little bit around by changing the Excel sheet and regenerating the source code, I immediately got a feeling for the class structure and which properties need to be used in order to get the task done. You can also direclty use code snippets if you set it up properly. And let me emphasize again: You will not need Excel on the PC you application is supposed to be installed on, but you can still produce 100% compatible Excel or PDF files.
Finally, there is a Demo application that hosts about 40 different examples for all the different usage tasks this library offers. The amount of features is immense, but the best thing is that you can get started right away and forget about all the advanced features if you want to. Going back to the task I had to solve in 2004, the customer required a signed, encrypted PDF document that was generated from the Excel content. Guess what, even that can be done with this library — (at least the .NET portion could do it in 2004, I did not check with the VCL version…)!
So, please spend 10 minutes with this library after reading this article for the last 5 minutes – it might be just the tool you need!