In 2004 I was working at a job which involved projects that made heavy use of VBA; from an Access application that would literally build an entire PowerPoint presentation from scratch, to Excel workbooks that would do all sorts of things that Excel was probably never designed to do. None of them would have been possible without the very thorough and extensive COM-based API that Microsoft makes available for all its Office applications.
The one place where the Office API falls down though is in web applications. Being single threaded, the code does not play nice in that kind of environment, and it is not officially supported by Microsoft (though it is not strictly impossible to use it that way). Recently I needed to have an ASP.NET web application of mine open an Excel template and add some data. I figured I would need to do something like build a separate web service to handle the Excel processing, using the same API I know and sort of love. Then I discovered Microsoft had published an SDK specifically for interacting with the XML-based default file formats introduced in Office 2007. And to my delight, they provided a managed DLL that would integrate nicely with ASP.NET. Life was good.
Or so I thought. Turns out the new API is not as nice as the COM-based original. The main issue I had is it takes a very XML-centric approach to doing things, which made it hard to implement the specific Excel things I wanted, like cell formatting, copying rows, etc. The old API was much more closely aligned with the individual Office apps, making it fairly straightforward to utilize whatever document features you wanted via code. Fortunately I wasn’t the first to feel like the new way was harder than it had to be. I recently came across ClosedXML, which is a .NET wrapper around the new API that makes doing Excel-specific things so much easier. Many thanks to MDeLeon for his excellent creation.