Excel Automation Performance of VB6 client application versus C# client application
Excel Automation Performance of VB6 client application versus C# client application
Excel Automation Performance of VB6 client application versus C# client application
Excel Automation Performance of VB6 client application versus C# client application
Excel Automation Performance of VB6 client application versus C# client application
Excel Automation Performance of VB6 client application versus C# client application Excel Automation Performance of VB6 client application versus C# client application Excel Automation Performance of VB6 client application versus C# client application Excel Automation Performance of VB6 client application versus C# client application Excel Automation Performance of VB6 client application versus C# client application Excel Automation Performance of VB6 client application versus C# client application Excel Automation Performance of VB6 client application versus C# client application Excel Automation Performance of VB6 client application versus C# client application
Excel Automation Performance of VB6 client application versus C# client application Excel Automation Performance of VB6 client application versus C# client application
Excel Automation Performance of VB6 client application versus C# client application
Go Back  Xtreme Visual Basic Talk > > > Excel Automation Performance of VB6 client application versus C# client application


Reply
 
Thread Tools Display Modes
  #1  
Old 08-05-2011, 01:09 PM
taney taney is offline
Newcomer
 
Join Date: Apr 2008
Posts: 9
Default Excel Automation Performance of VB6 client application versus C# client application


I've invested a lot of time in converting some old VB6 / *.xla code into a C# application and now have come to find that I've dug myself into a major performance hole. It seems that automating Excel from C# (VS 2010) has some sort of major performance impact. I've written the 'exact' code in a test VB6 application and get calculations running in about 1-2 seconds. Where as the code in C# takes over a minute. The general flow of the code is like this…(where Client is either VB6 or C# application).

-- Only done once for lifetime of client application
  1. Client creates and opens and Excel Application
  2. Client automates Excel to load required add-ins needed for calculations.
-- Done for each calculation performed
  1. Client automates Excel to close any existing *.xls file and open the desired *.xls file.
  2. Client calls macro from Excel add in using ExcelAppObject.Run( "AddIn.xla!GetConfiguration" ) to get the configuration of *.xls opened in step 3.
  3. Client calls macro from Excel add in using ExcelAppObject.Run( "AddIn.xla!LoadInputs", InputsXmlString )
    • The macro loads InputsXmlString into MSXML.DOMDocument40 object.
    • The macro turns Application.Calculation = xlCalculationManual (to speed up populating several 'tables' of inputs)
    • The macro sets Application.EnableEvents = false
    • The macro loops all 'configured' inputs/tables on the 'Input' worksheet and clears them (in case the passed in xml doesn't contain 'all' inputs)
    • The macro loops all xml passed in and loads into appropriate locations on the 'Input' worksheet.
  4. Client calls macro from Excel add in using ExcelAppObject.Run( "AddIn.xla!GetResults", DataXmlString )
    • The macro loads DataXmlString into MSXML.DOMDocument40 object.
    • The macro loops all 'configured' data values on the 'Input' worksheet and clears them (in case the passed in xml doesn't contain 'all' data or clearing out previous calculation)
    • This is different from LoadInputs because we could have a batch calculation where inputs are same for every calculation but the 'participant data' obviously is different for each calculation
    • The macro loads all the data from DataXmlString into appropriate locations on 'Input' worksheet
    • The macro sets Application.EnableEvents = true
    • The macro turns Application.Calculation = xlCalculationAutomatic (to make sure calculation occurs now that all data/inputs have been loaded)
    • The macro loops all the configured 'result cells' and returns them via Xml string.

So as you can see, for each calculation, I only have three 'cross process' calls from client to Excel (GetConfiguration, LoadInputs, and GetResults) to try and minimize that known 'poor' performance issue. The problem is, when the exact same code is called from a VB6 application steps 4-6 take about 2 seconds. When the client application is a C# application, 4-6 take about 70 seconds. All of this 70 seconds occurs on step 6 when I turn calculation back to Automatic.

Is there a known issue with C# applications automating Excel versus legacy VB6 applications and/or are there any proposed work arounds so that I can keep my C# code but somehow achieve the same performance as VB6?
Reply With Quote
  #2  
Old 08-11-2011, 05:41 PM
DennisW's Avatar
DennisW DennisW is offline
Junior Contributor
 
Join Date: Mar 2006
Location: Östersund, Sweden
Posts: 268
Default

Hi,

First of I'm not surprised with the current performance. Even if You minimize the cross calls the C# code may create indirectly a great number of Excel Objects which have a negative impact on the performance.

Instead of setting fcous on the C# You may consider to create an XLL file where You place Your User Defined Formulas (UDFs).
__________________
Kind regards,
Dennis

.NET & Excel | 2nd edition PED | MVP
Reply With Quote
Reply

Tags
csharp, excel automation, performance, visual basic 6


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

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
Excel Automation Performance of VB6 client application versus C# client application
Excel Automation Performance of VB6 client application versus C# client application
Excel Automation Performance of VB6 client application versus C# client application Excel Automation Performance of VB6 client application versus C# client application
Excel Automation Performance of VB6 client application versus C# client application
Excel Automation Performance of VB6 client application versus C# client application
Excel Automation Performance of VB6 client application versus C# client application Excel Automation Performance of VB6 client application versus C# client application Excel Automation Performance of VB6 client application versus C# client application Excel Automation Performance of VB6 client application versus C# client application Excel Automation Performance of VB6 client application versus C# client application Excel Automation Performance of VB6 client application versus C# client application Excel Automation Performance of VB6 client application versus C# client application
Excel Automation Performance of VB6 client application versus C# client application
Excel Automation Performance of VB6 client application versus C# client application
 
Excel Automation Performance of VB6 client application versus C# client application
Excel Automation Performance of VB6 client application versus C# client application
 
-->