在 vb.net 中声明/打开 Excel 文件

Question

我已经尝试在 vb.net 中声明或打开 Excel 工作表一段时间了。 我已经阅读了 vb.net 中的 Excel 文件 和其他链接，但它不起作用。

我添加了 Microsoft Excel 12.0 对象库。 我包括：

Imports Microsoft.VisualBasic
Imports System.Net.Mime.MediaTypeNames
Imports Microsoft.Office.Interop 

我想在模块中声明/打开 Excel 文件：

Public Module postleitzahlen_array

Dim myarray As String

Dim xlApp As Excel.Application
xlApp = New Excel.ApplicationClass ' here is the error, XlApp "has to be declared"

有人可以帮助我吗？

编辑：

好的，我注意到我使用 excel 2007，并且有一个区别 - 现在我使用 http://vb.net-informations.com/excel-2007/vb.net_excel_2007_create_file.htm

Sub test()
        Dim xlApp As Excel.Application
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet

        Dim misValue As Object = System.Reflection.Missing.Value

        xlApp = New Excel.ApplicationClass
        xlWorkBook = xlApp.Workbooks.Add(misValue)
        xlWorkSheet = xlWorkBook.Sheets("sheet1")
        xlWorkSheet.Cells(1, 1) = "http://vb.net-informations.com"
        xlWorkSheet.SaveAs("D:\vbexcel.xlsx")

        xlWorkBook.Close()
        xlApp.Quit()

        releaseObject(xlApp)
        releaseObject(xlWorkBook)
        releaseObject(xlWorkSheet)

        MsgBox("Excel file created , you can find the file c:\")
    End Sub


    Private Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub

但我在 xlWorkSheet = xlWorkBook.Sheets("sheet1") 说“（HRESULT异常：0x8002000B（DISP_E_BADINDEX））

Edit2： 我使用德语 excel，因此“sheet1”会引发错误 --> “tabelle1”是正确的词:)

Answer 1

我编写了一个用于在 VB.NET 中获取工作簿的函数，该函数将尝试获取任何正在运行的 Excel 进程的句柄，并且仅在找不到时打开一个新的 Excel 实例。

    ''' <summary>
    ''' Open an Excel file and get a handle to the object.
    ''' If you want to quit Excel later use: `thisWorkbook.Application.Quit`
    ''' </summary>
    ''' <param name="fileName">full path to the workbook you want to open.</param>
    ''' <returns>The open Excel Workbook handle.</returns>
    Public Function OpenExcelFile(fileName As String) As Excel.Workbook
        Dim thisWorkbook As Excel.Workbook

        Dim excelApp As Excel.Application
#If DEBUG Then
        excelApp = New Excel.Application With {
            .Visible = True,
            .EnableEvents = False   ' Suppress the Workbook_Open event
            }
#Else
        Try ' To attach to running process
            excelApp = Marshal.GetActiveObject("Excel.Application")
        Catch ex As Exception
            ' Open Excel if it isn't already
            excelApp = New Excel.Application With {
                .Visible = False,       ' Ninja mode
                .EnableEvents = False,  ' Suppress the Workbook_Open event
                .DisplayAlerts = False  ' Supress messages
            }
        End Try
#End If

        Try
            Dim excelBooks As Excel.Workbooks = excelApp.Workbooks
            thisWorkbook = excelBooks.Open(fileName)
        Catch ex As Exception
            MsgBox("Error opening Excel file: " & fileName & vbCrLf & ex.Message)
        End Try

        Return thisWorkbook
    End Function

Answer 2

至于您的错误，将 ApplicationClass 替换为简单的 Application 已经解决了我的问题。