What is the most annoying / strangest thing that has happened to you using Excel Interop

After developing using the Excel Interop with .Net for a while, i have become increasingly annoyed with the how many "odd things" happen - such as this questio开发者_如何学运维n i posted earlier - My Problem.

I appreciate that this is not a direct question and more of a collaberation of experiences, but i do think that it would be helpful however to find out peoples greatest annoyances / strange things that they have had and how they overcame them.

That way i can find out what issues I may run into in the future :)

Thanks

The most annoying feature of Excel interop for me is that every time you do anything it creates COM objects behind the scenes, but these all need disposing otherwise Excel won't close when you call Close(). And if you miss one it's often difficult to figure out where.

Luckily I found this thread on here that suggests a few ways to solve the problem.

Memory exhaution because of many open different instances of the Office applications.

Careful programming may sort it out, but internal errors in the applications may ruin your assumptions.

You will get a different Interop compiled on a machine with a different MS Office version.

This basically means an additional machine (physical or virtual) and additional Visual Studio, Windows, and MS Office licences to develop for additional version.

While deploying a version to a client i had to archive a virtual machine image to compile that version, because i could not guarantee i will be using the same version of MS Office on my development machine.

The MOST weird is the optional parameters in all Office methods. For me as c# programmer Missing.Value is like course.

for example SaveAs method takes 12 arguments and only one of them required and you ended up with code like that

result.SaveAs('file',Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value)

Also signature depends on office interop version, each major Excel version adds some parameters to signature and completely destroy your code.

ref and out also unusable constructs.
One recommendation - use VB.NET for office interop - it is the right tool for such a thing, or wait for c#4.0

The most annoying thing to me is that you get seemingly random errors / exceptions / crashes.

For example, I sometimes need to convert a large set of thousands of workbooks between formats (xls / xlsx) from a C# console application. Excel will rarely process all of these workbooks in one pass without error. Running multiple times causes problems on different files. So, if a.xls and b.xls are in my set of files, Excel might fail on a.xls on the first pass and b.xls on the 2nd pass.

The machine has far more memory / disk space than the application needs. The application is single threaded, so there is not an issue of multiple instances of Excel creating havoc.

I have observed this behavior with Excel 2003 and with Excel 2007.

I eventually modified my application to account for this fact, and keep track of which workbooks have been successfully converted so that a 2nd pass can clean up the mess left by the first pass.

The lack of support for automation...

The fact that you can't run Excel in an automated or non-interactive environment for example on a server. It can be done, but not reliably and not without hacking the systems, which often isn't viable for product environments. But this isn't limited to Excel.

For more info see here. I did a recent study on some alternatives that you can find here: Reading Excel Files as a Server Process

This has led to countless problems for me and others, and I've read many many posts on Stackoverflow about issues, all regarding the use of Excel on a server. It's just really not worth the hassle of going down this route, especially since Vista and above simply don't work with Office 2k7 through automation.

1 - The fact that to write to a sheet from another thread, you have to implement IMessageFilter and even with that, you still have to whack it with a hammer

2 - As Mark Byers mentioned above, the "one dot rule"... sigh...

3 - And of course, that in no clear place is ANY of this flagged so instead we have to trawl the darkest corners of the web hoping to stumble upon some rationale...

(Andrew Whitechapel has however kindly written lots of extremely helpful articles - thanks, Andrew, it's just a pity you had to...)