Excel programming from .net

[kaisersoze]

I have to do extensive MS Excel programming from windows .net, like creating Excel (.xls), creating multiple work sheets in the excel, formating, coloring, etc.

 

can anyone let me know which component i should use. any feedback is appreciated. In VB6.0 this was a peice of cake.

[Mike_R]

It's still largely the same. If you are using Automation, then it's really the same. If you are making a DLL Addin then you would make a DLL and expose it to COM. From there Excel cannot even know that you are using .NET behind the scenes. However, deployment ("trust" issues) can become trickier with .NET, but this has nothing to do with .NET <--> Excel interaction per se.

 

If you are using Automation, then I would give this a read, for starters:

 

Automating Office Programs with VB.Net / COM Interop

 

If you are making an Addin, then the following could be of help:

 

(1) How To Build an Office COM Add-in by Using C#

(2) How To Build an Office COM Add-in by Using Visual Basic .NET

(3) Creating Office Managed COM Add-Ins with Visual Studio .NET

 

Hope this helps, I can provide more links or explain further if you need...

 

-- Mike

[Mike_R]

I should add however, some problems with .NET and Excel:

 

(1) It is slower, period. The interop stands between your code and Excel. So the usual tricks about copying a Range to an Array then manipulating the Array and then passing back the results (instead of looping and editing cell-by-cell) are at even more of a premium. For the most part it is acceptible, but there is a cost. However, from time-testing that I've seen done from others, user-defined functions can run up to 10x slower, which is very noticable. I would therefore not use .NET if you are making a library of worksheet functions.

 

(2) Backwards compatibility from Excel '97 through 2003 can be much harder if using .NET. Excel 2002 has PIA's available for download. Excel 2003 has them installed by default (generally). But Excel 2000 and Excel '97 don't have PIAs, so you can install them yourself, or ignore the issue and let .NET create local PIA's for you (which is what I do, and it should be fine, but I've never tried to deploy a solution using a local PIA).

 

(3) As I mentioned above, deployment is harder. No harder than .NET is on its own, but it is a different scenario than COM DLLs.

 

Overall, I would start with a VERY small project and then hit your minimum deployment needs with it. In other words, start with a program that has one button and when you click it, puts the word "Hello" in cell A1 of the Active Sheet. That's it.

 

From there, get this working in all versions that you need (which is easiest if you only need Excel 2002 and higher) and try deploying it to a few client machines. I expect that you'll hit a few snags even doing this. However, once you get these minimal deployment needs working then you'll be able to program it pretty much just as you did with VB 6.0...

 

Just my 2c!

Mike