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

3. Client automates Excel to close any existing *.xls file and open the desired *.xls file.

4. Client calls macro from Excel add in using ExcelAppObject.Run( "AddIn.xla!GetConfiguration" ) to get the configuration of *.xls opened in step 3.

5. Client calls macro from Excel add in using ExcelAppObject.Run( "AddIn.xla!LoadInputs", InputsXmlString )

   1. The macro loads InputsXmlString into MSXML.DOMDocument40 object.
   2. The macro turns Application.Calculation = xlCalculationManual (to speed up populating several 'tables' of inputs)
   3. The macro sets Application.EnableEvents = false
   4. 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)
   5. The macro loops all xml passe开发者_如何学JAVAd in and loads into appropriate locations on the 'Input' worksheet.

6. Client calls macro from Excel add in using ExcelAppObject.Run( "AddIn.xla!GetResults", DataXmlString )

   1. The macro loads DataXmlString into MSXML.DOMDocument40 object.
   2. 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

   3. The macro loads all the data from DataXmlString into appropriate locations on 'Input' worksheet
   4. The macro sets Application.EnableEvents = true
   5. The macro turns Application.Calculation = xlCalculationAutomatic (to make sure calculation occurs now that all data/inputs have been loaded)
   6. 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?

Using C# to interact with Excel through COM Interop is generally extremely poor from a performance point of view: I would suggest that if you want to continue with C# you look at Excel DNA or Addin Express, both of which enable you to use the much faster XLL interace from C#. See http://fastexcel.wordpress.com/2011/07/07/excel-udf-technology-choices-snakes-ladders-with-vba-vb6-net-c-com-xll-interop/ for my brief evaluation of the currently available technology choices for interacing with Excel.

The main performance bottleneck with C# is usually transferring dat from Excel to C#: each data transfer call acrries a large overhead, so its usually much faster to transfer large blocks of data using an array.