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

Question

Hi All,
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 question 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

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

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.

Answer 5

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: http://stackoverflow.com/questions/1273116/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.

Answer 6

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