Performance problems with .NET/Excel COM Interop
Performance problems with .NET/Excel COM Interop
Performance problems with .NET/Excel COM Interop
Performance problems with .NET/Excel COM Interop
Performance problems with .NET/Excel COM Interop
Performance problems with .NET/Excel COM Interop Performance problems with .NET/Excel COM Interop Performance problems with .NET/Excel COM Interop Performance problems with .NET/Excel COM Interop Performance problems with .NET/Excel COM Interop Performance problems with .NET/Excel COM Interop Performance problems with .NET/Excel COM Interop Performance problems with .NET/Excel COM Interop
Performance problems with .NET/Excel COM Interop Performance problems with .NET/Excel COM Interop
Performance problems with .NET/Excel COM Interop
Go Back  Xtreme Visual Basic Talk > > > Performance problems with .NET/Excel COM Interop


Reply
 
Thread Tools Display Modes
  #1  
Old 02-25-2004, 10:06 PM
utunga utunga is offline
Newcomer
 
Join Date: Feb 2004
Posts: 1
Question 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..
Code:
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;

Last edited by herilane; 02-26-2004 at 03:39 AM. Reason: Added [code] tags and a line break - apologies if that breaks C# syntax rules :)
Reply With Quote
  #2  
Old 02-26-2004, 08:00 AM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

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

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. 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 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
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
COM AddIn Problems CorDharel Word, PowerPoint, Outlook, and Other Office Products 0 11-10-2003 07:03 AM
Is My Com Port Damaged? msdooley Communications 6 07-07-2003 12:30 AM
Reply all data received in a COM port to another COM in the same computer tbarbarini Communications 0 12-18-2002 04:38 PM
COM problems with ASP bbolte Web Programming 8 12-18-2002 02:15 PM
Com control problems? esmithz General 0 12-10-2002 03:27 PM

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
Performance problems with .NET/Excel COM Interop
Performance problems with .NET/Excel COM Interop
Performance problems with .NET/Excel COM Interop Performance problems with .NET/Excel COM Interop
Performance problems with .NET/Excel COM Interop
Performance problems with .NET/Excel COM Interop
Performance problems with .NET/Excel COM Interop Performance problems with .NET/Excel COM Interop Performance problems with .NET/Excel COM Interop Performance problems with .NET/Excel COM Interop Performance problems with .NET/Excel COM Interop Performance problems with .NET/Excel COM Interop Performance problems with .NET/Excel COM Interop
Performance problems with .NET/Excel COM Interop
Performance problems with .NET/Excel COM Interop
 
Performance problems with .NET/Excel COM Interop
Performance problems with .NET/Excel COM Interop
 
-->