developer.* Blogs - Lessons Learned Automating Excel from .NET - Comments

VSTO Envy

Daniel Read — Tue, 20 Mar 2007 11:22:52 -0700

Thank you for providing this additional info, Trevor. It sounds like a worthy alternative. However, I have a strong doubt that this solution would have worked for my client's company, which was still on Office 2000 and not planning to upgrade anytime soon. The first version of my solution was early bound, and I had to rewrite it to be late bound when it did not test well with Office 2000. Converting from early bound to late bound was relatively straightforward, but I shudder to think that I would have had to do a near-total rewrite if I had started with VSTO, which I had not heard of at the time (and which I am only assuming would have backwards compatibility problems with Office 2000--though I would be willing to be money that it does since Microsoft has a history of being obnoxious about driving Office upgrades with their Office development hook-ins).

Anyway, I don't mean to crap on your suggested solution, Trevor. I appreciate it. If the circumstances are right, VSTO sounds like a great option, and one I'd like to explore further for future projects.

Dan

VSTO

trevor.conn — Tue, 20 Mar 2007 10:04:25 -0700

If you need to couple .NET to Excel or any of the Office suite of applications, I would highly recommend using VSTO (Visual Studio Tools for Office) instead of the above techniques. When you install VSTO, you get a series of project templates that start you off working directly with, say, an Excel spreadsheet as part of your application. You can then use the spreadsheet (or Word doc) as a front-end to do just about anything you want. At my current job I had to create a spreadsheet that allowed a user to provide a customerID, click a button, and return a history of invoice summaries that had to populate a range. I wrote it all in C# just as if I were using a Windows Form. It was quite easy. The only hurdle I see is that if you have to deploy a VSTO application to a client's machine (or throughout an office) then you have to go through extra hoops including VSTO and some dependent assemblies in your MSI installer. Plus you need to set some security policy to grant execution rights on the client machine to the required assemblies.

But in terms of the development itself, I found VSTO to be much easier and quicker because it allowed me to use everything natively in .NET. All of the COM+ interop is wrappered for you.

Wrapping COM with COM

Daniel Read — Wed, 14 Mar 2007 15:27:07 -0700

Kalpesh, I don't think your suggestion to implement the needed Excel/Office functionality in a VB 6 DLL is stupid at all. I think it would work well, and I still have fond feelings for VB 6. However, I suspect that in a larger organization, or in a consulting situation, there could be managers or others who would not want to introduce VB 6 code into a solution for fear that they would have trouble maintaining it. That would depend on the situation, though...

Thanks,
Dan

Other way to automate excel

Kalpesh — Wed, 14 Mar 2007 14:17:20 -0700

I havent done any office automation using .net. However, I feel, it will be better to create a COM component using VB6, which does all the work expected from the application

This COM component can be called from c# or vb.net. So, one need not go through reflection & optional parameter thing, variant. Also, VB code will do the cleanup of objects instantiated (e.g. set myrange = nothing)

How about VSTO? Does it do any better?
Hope this is a good suggestion & not a stupid one.

Goes for alll of Office

kevinDwhite — Tue, 13 Mar 2007 15:12:13 -0700

These are great tips that apply to the whole office suite. I recently had the pleasure of automating Word and PowerPoint. Unfortunately I wrote the whole thing in C#. I firmly agree with Tip #1. Never ever automate Office if you can find another way. With Excel at least you have the option of dumping the whole thing to a CSV and then manipulating the data. The one exception is PowerPoint which should never ever be touched by code, full stop. PowerPoint is the cistern of the Office automation sewer. No third-party is going to help you there.

Chill

Edward G Nilges — Thu, 08 Mar 2007 18:26:42 -0800

My friends at Microsoft aren't "stupid bollockers". Instead, they are often people who were excluded from those universities who developed the culture of computing elegance by their parents' finances and who, like an older generation of IBM employees, got some dignity out of a full time professional job from a man, Gates or Watson, who didn't care about their having a fancy education.

Gates made a decision to protect his intellectual property in the shark tank which then and now is the American economy, a thieves' economy, by using binary and proprietary formats, because he saw people lose their shirts in the 1970s to men who smiled and stole.

This site is not a football match, mate, nor is real software development, either proprietary or open. You want to fight, see me on usenet.

Not here.

Well, all this shows how

Guest — Thu, 08 Mar 2007 01:06:13 -0800

Well, all this shows how crap the Microsoft binary formats are. If these stupid bollockers just used a nice text based programming interface in Unix spirit, let us say Excel listening on a socket, accepting text "messages", everything would be 100x easier.
Hell to them, piece of Microsoft piss.

Alternatives to calling Excel

Edward G Nilges — Wed, 07 Mar 2007 17:51:04 -0800

An incredible story of the effort needed to communicate from .Net to an old and very complex COM object, Dan.

I have another suggestion. If you're calling Excel to do trivial computations, to allow the user to work on a spreadsheet in your GUI, consider creating your own version of Excel.

The main problems:

(1) Restricting the "language" of what can be entered in a cell to a subset of what is supported by Excel and which you feel comfortable compiling

(2) Solving the problem of recalculating the linked cells in a spreadsheet by finding the top of the "tree" of computations and working downward. This was solved by Dan Bricklin in the first spreadsheet and his solution was patented, but it's fairly easy to reinvent, at least I think I did so in 1987 for a real estate appraisal system that presented the user with a spreadsheet of linked fields.

If this sounds too complex, consider getting the free Open Office suite: this is written in modern code, it isn't COM, it presents no license or patent problems, and it MAY be easy to work with.

Another solution would be "arms length". Send keystrokes to an Excel application running in a different process entirely, save the result using these keystrokes, and parse it for the answer.

The bottom line: if you define the problem (or it is defined for you) as "I gotta use Excel as a COM object in a .Net application" you're in for an interesting time, and as Dan has done, you need to use tryCatch a LOT.

The need for wait loops to wait while Excel gets its act together implies for me a mini GUI to allow power users to control the timing and possibly progress screens. I say this because time-sensitive code can appear to hang a user's machine when it doesn't indicate what it is waiting for, and often needs to be tuned for different machines.

The object2String() method found in the utilities library shipped with Build Your Own .Net Language and Compiler, btw, handles the problem of serializing (converting to character in my lexicon) any .Net object, and I found it useful in a .Net project that used variants from an Office application.

Lessons Learned Automating Excel from .NET

Daniel Read — Wed, 07 Mar 2007 17:26:21 -0800

Recently I had occasion to write a moderately complex component that used "automation" (using the old fashioned COM term) to communicate with the Microsoft Office Excel application installed on the same computer. In this post I share several tips and tricks that may help you in your Excel automation adventures.