VB6 客户端应用程序与 C# 客户端应用程序的 Excel 自动化性能
我投入了大量时间将一些旧的 VB6 / *.xla 代码转换为 C# 应用程序，现在发现我已经陷入了一个主要的性能漏洞。从 C# (VS 2010) 自动化 Excel 似乎对性能产生了某种重大影响。我已经在测试 VB6 应用程序中编写了“精确”代码，并在大约 1-2 秒内运行了计算。 C# 代码需要一分钟多的时间。代码的一般流程是这样的……（其中客户端是 VB6 或 C# 应用程序）。
-- 在客户端应用程序的生命周期内仅执行一次
客户端创建并打开 Excel 应用程序
客户端自动执行 Excel 加载所需的加载项需要进行计算。
-- 完成每个执行的计算
客户端自动执行 Excel 关闭任何现有的 *.xls 文件并打开所需的 *.xls 文件。
客户端使用 ExcelAppObject.Run( "AddIn.xla!GetConfiguration" ) 从 Excel 添加中调用宏，以获取在步骤 3 中打开的 *.xls 的配置。
客户端使用 ExcelAppObject.Run( 从 Excel 添加中调用宏“AddIn.xla！LoadInputs”，InputsXmlString）
- 该宏将 InputsXmlString 加载到 MSXML.DOMDocument40 对象中。
- 宏将 Application.Calculation = xlCalculationManual（以加快填充多个输入“表”）
- 宏设置 Application.EnableEvents = false
- 宏循环“输入”工作表上的所有“配置”输入/表并清除它们（以防传入的 xml 不包含“所有”输入）
- 该宏循环传入的所有 xml 并加载到“输入”工作表上的适当位置。
客户端使用 ExcelAppObject.Run( "AddIn.xla!GetResults", DataXmlString ) 从 Excel 添加中调用宏
- 该宏将 DataXmlString 加载到 MSXML.DOMDocument40 对象中。
- 宏循环“输入”工作表上的所有“配置”数据值并清除它们（以防传入的 xml 不包含“所有”数据或清除之前的计算）
这与 LoadInputs 不同，因为我们可以进行批量计算，其中每次计算的输入都相同，但每次计算的“参与者数据”显然不同 - 宏将 DataXmlString 中的所有数据加载到“输入”工作表上的适当位置
- 宏设置 Application.EnableEvents = true
- 宏将 Application.Calculation = xlCalculationAutomatic （以确保在所有数据/输入均已加载后进行计算）
- 该宏循环所有配置的“结果单元格”并通过 Xml 字符串返回它们。
正如您所看到的，对于每个计算，我只有三个从客户端到 Excel 的“跨进程”调用（GetConfiguration、LoadInputs 和 GetResults），以尝试最大程度地减少已知的“较差”性能问题。问题是，当从 VB6 应用程序调用完全相同的代码时，步骤 4-6 大约需要 2 秒。当客户端应用程序是C#应用程序时，4-6大约需要70秒。当我将计算转回自动时，这 70 秒的所有时间都发生在步骤 6 上。
与旧版 VB6 应用程序相比，C# 应用程序自动化 Excel 是否存在已知问题和/或是否有任何建议的解决方法，以便我可以保留我的 C# 代码，但以某种方式实现与 VB6 相同的性能？
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
Client creates and opens and Excel Application
Client automates Excel to load required add-ins needed for calculations.
-- Done for each calculation performed
Client automates Excel to close any existing *.xls file and open the desired *.xls file.
Client calls macro from Excel add in using ExcelAppObject.Run( "AddIn.xla!GetConfiguration" ) to get the configuration of *.xls opened in step 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.
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
从性能角度来看，使用 C# 通过 COM Interop 与 Excel 交互通常非常差：
我建议，如果您想继续使用 C#，您可以查看 Excel DNA 或 Addin Express，这两者都使您能够使用 C# 中更快的 XLL 接口。
请参阅
C# 的主要性能瓶颈通常是将数据从 Excel 传输到 C#：每个数据传输调用都会产生大量开销，因此使用数组传输大数据块通常要快得多。
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.