10

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

Community
  • 1
  • 1
Ben
  • 3,926
  • 12
  • 54
  • 87

7 Answers7

6

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.

Community
  • 1
  • 1
Mark Byers
  • 811,555
  • 193
  • 1,581
  • 1,452
  • You might want to also read this thread: http://stackoverflow.com/questions/2191489/releasing-temporary-com-objects – Henrik Feb 04 '10 at 11:05
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.

George Polevoy
  • 7,450
  • 3
  • 36
  • 61
2

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.

Pablo Rodriguez
  • 582
  • 4
  • 18
  • Yes, i find this one particullary annoying. I've had this on several occurences when writing apps and debugging (so not always exiting the app in the correct manor, therefore the correct dispose methods being called) and having ghosted processes. PSKill.exe is very helpful in these instances. – Ben Feb 04 '10 at 11:15
2

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

Sergey Mirvoda
  • 3,209
  • 2
  • 26
  • 30
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.

Joe Erickson
  • 7,077
  • 1
  • 31
  • 31
  • The error messages are a complete nonsensical and completely contravene Microsofts coding standards for error messages. From their "Microsoft User Experience Interaction Guidelines" - "Effective error messages inform users that a problem occurred, explain why it happened, and provide a solution so users can fix the problem." – Ben Feb 04 '10 at 17:31
  • @Ben try SSIS Excel errors is like miracle compared to SSIS cryptic errors:) – Sergey Mirvoda Feb 05 '10 at 06:52
0

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.

Community
  • 1
  • 1
Ian
  • 33,605
  • 26
  • 118
  • 198
0

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...)

Community
  • 1
  • 1
Pat Mustard
  • 1,852
  • 9
  • 31
  • 58