Windows窗体应用程序中的Excel工作表，而无需打开Excel应用程序 [英] Excel worksheet in Windows form application without opening Excel application

问题描述

我正在将几个VBA项目转换为Windows窗体应用程序.我唯一的问题是Excel的某些功能对于应用程序是必不可少的，例如R1C1公式.我不想实例化Excel应用程序或访问保存的工作表.通过查询Oracle数据库检索所有数据.二维数组不是一种选择，因为列包含不同的数据类型，并且DataGridViews太慢而无法使用.

I am converting several VBA projects to Windows form applications. The only problem I have is that some of the functionality of Excel is essential to the application, such as R1C1 formulas. I do not want to instantiate the Excel application or access saved worksheets. All of the data is retrieved by querying Oracle databases. 2-Dimensional arrays are not an option because the columns contain differing datatypes, and DataGridViews are too slow to work with.

我认为仅使Microsoft.Office.Interop.Excel.Worksheet对象变暗就足够了，但是程序不断失败，并且在调试模式下检查该对象的元素时，我发现每个值都表示这样:

I thought simply dimming a Microsoft.Office.Interop.Excel.Worksheet object would be enough, but the program kept failing and upon inspecting the object's elements in debug mode, I found that every value says this:

{无法将类型为'Microsoft.Office.Interop.Excel.WorksheetClass'的COM对象转换为接口类型'Microsoft.Office.Interop.Excel._Worksheet'.此操作失败，因为对COM组件的QueryInterface调用具有IID'{000208D8-0000-0000-C000-000000000046}'的接口由于以下错误而失败:不支持此类接口(HRESULT异常:0x80004002(E_NOINTERFACE)).}

{"Unable to cast COM object of type 'Microsoft.Office.Interop.Excel.WorksheetClass' to interface type 'Microsoft.Office.Interop.Excel._Worksheet'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{000208D8-0000-0000-C000-000000000046}' failed due to the following error: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE))."}

如果有人能够告诉我如何在不打开Excel的情况下获取工作表对象，或者至少提供合理的选择，我将非常感谢您的帮助.

I would really appreciate the help if someone is able to tell me how to get a worksheet object without opening Excel, or at least offer a reasonable alternative.

推荐答案

您可能需要正确实例化对象.工作表需要Excel COM对象，因此通常您首先实例化它，然后访问工作表.这是一些示例代码:

You probably need to instantinate your object correctly. Worksheet requires Excel COM object, so you normally instantinate its first and then access a sheet. Here is some sample code:

Dim xl As Microsoft.Office.Interop.Excel.Application
xl = New Microsoft.Office.Interop.Excel.Application
Dim wb As Microsoft.Office.Interop.Excel.Workbook
wb = xl.Workbooks.Add()
Dim ws As Microsoft.Office.Interop.Excel.Worksheet
ws = wb.ActiveSheet

现在您可以使用ws了.请注意，我没有使用Dim .. as New实例化它.

Now you can work with your ws. Notice, that I do not instantinate it using Dim .. as New.

这样做，您将在后台运行一个不可见的Excel实例.完成操作后，必须显式关闭应用程序以防止其保留在内存中:

By doing this you will get an invisible instance of Excel running in your background. You must close your application explicitly after you are done to prevent it from staying in memory:

/// after your are finished
xl.Quit()
Marshal.ReleaseComObject(xl)

如果在某种循环中使用它，这一点尤其重要.

This is especially important if you using it in a kind of loop.

这篇关于Windows窗体应用程序中的Excel工作表，而无需打开Excel应用程序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！