Never, never, never do Office interop on the server

I’ve been telling people this for years and years, and still I stepped right into the mudhole a month ago. I blame stress, but still I shouldn’t have. It’s so easy to just add the interop assemblies and off you go… until you try running your stuff on the 2008 r2 server, hosted somewhere else, with just enough user rights. And even though I managed to get the !”#!¤¤”% working after a while, I got mem-leaks and follow-up probs due to the workarounds I had to do to get the interop working in the first place.

Then I sat down and relaxed for a minute and realized I need to do this the right way, which is the OpenXML way.

The thing is, OpenXML is – compared to Office-interop - not easy, and the Microsoft OpenXML SDK 2.0 is just lipstick on top of the XML and some things which were so easy to do with the interop assemblies gives you nightmares to do with OpenXML. Try fill in formsfields in a protected word document for example. I ditched the SDK and ended up doing it directly against the XML directly instead.

I also had to fill out a few Excel spreadsheets with data, which was somewhat easier to handle than the Word-story above. But I ran into problems here too, because I wanted to do such a simple thing as setting text wrap on a few cells! I don’t know, but I’m sure Microsoft could have done something better for us poor developers that need to create docs on the server! Are we supposed to spend 40% of the coding time to just create a few simple docs, just because we go the OpenXML way? There are a quite a few pages on MSDN with samples, as well as code snippets to download, but as soon as you need to do something outside the “demo-path” you’re banging your head. Maybe I’m just stupid and doesn’t get it, or maybe I’m getting grumpy, because OpenXML is not a developer-friendly framework! There, feels much better now…

Now, for Excel spreadsheets there’s good hope because you got more than a few decent packages out there to help you out, and the best one I’ve found so far is called ClosedXML on Codeplex. It’s free and it did everything I needed to do right out of the box! It took me 5 minutes to create the 2 different spreadsheets I needed for my project, including cell formatting, text wrapping and so on. Cheers to MDeLeon for doing this!