Performance problems with .NET/Excel COM Interop

We have an application need to delivering fairly significant amounts of financial data from our web server into excel. We will need to do a lot of filtering/sorting of this data before/once it arrives, and the natural choice for end users is to have the data reports show up in Excel.

One way to do this is via .NET WinForms application that runs on local desktop, loads data via webservices from the webserver, based on the users’ choices (made in the rich WinForms environment) and then connects to Excel and writes out the data.

The other choice is to use Excel VBA to talk directly to the webservices layer (web services calls via VB Subs approach).

I would prefer the first option, for various reasons, but am very concerned about performance.

Just as a test, I set up the following code (below). All the relevant Excel 10.0 assembly references are there. (The code works), referenced functions do the obvious thing.

The problem is, even though I am only updating 20x20=400 cells the code takes 10 Seconds to run !!

What on earth could be going wrong here? More to the point is there another way to dump data from C# to Excel that is a bit more efficient??

If not, we will have to dump this approach and go with Excel VBA, because we are going to need to update thousands of rows at a time.

Thanks in advance for your help..ExcelApp.ScreenUpdating = false;
int TEST_ROWS=20;
int TEST_COLS=20;
for (int i = 1; i < TEST_ROWS; i++)
{
for (int j = 0; j < TEST_COLS; j++)
{
Excel.Range theRange = TheSheet.Cells.get_Range(GetExcelIndexToLetter(j) + (i + 1).ToString(),
GetExcelIndexToLetter(j) + (i + 1).ToString());
theRange.Value2 = "test";
}
}

ExcelApp.ScreenUpdating = true;

Your concerns are dead on. I have not run any time tests using Automation in .Net, but using VB 6.0, I have found automation to run at least 50x slower than VBA running in behind Excel. Using .Net working through the COM Interop, I would think, would be just as bad or possibly worse. (Can't see how it could be better!)

I would have a read here to get a sense of the issue: Performance of Excel macros vs. VB 6.0 (http://www.visualbasicforum.com/showthread.php?p=619967#post619967)

That said, there are ways to address it, if you can move a lot of data in one block, one large NxM Range at a time:
Speeding up VB 6.0 Automation (http://www.visualbasicforum.com/showthread.php?p=633774#post633774). But if you are updating individual cells fragmented throughout a Workbook, then these issues are tough to get around.

Within your own code that you show, I know it was just a test, it looks like you were trying to get .Cells(i,j) or .Cells.Item(i,j). If you need to get the block of cells from (i,j) to (k,l), however, you would need a structure somewhat similar to what you used.

Lastly, when using Automation in .Net, it is shockingly easy to create hanging instances of Excel. Often it's not a problem other than taking up memory, but run your program a few times and these hanging instances running in the background multiply and start to really take up resources.

You could have a read here VB.Net Office Integration Tutorial (http://www.visualbasicforum.com/t129690.html) to get a sense of these issues.

It is this last issue that I find the most onerous in terms of trying to use .Net Automation. I find it so clunky/tricky to operate the COM Interop that I just don't go near it.

I have not played with it, but it is possible that compiling to a COM Interop "DLL" may not have these hanging problems because the "DLL" is running in-process, and it is the User in this case closing the process. That said, I've not tried it and just consider the whole experiment a risk. I would, personally, play it safe and use VBA code or use VB6 to compile to a COM DLL. Unless you have a lot of time to test and work out the kinks, I think it will save a lot of hassle and minimize risks of issues down the line...

I hope this helps...

-- Mike