.net - VB.NET Excel 程序完成后使 EXCEL.EXE float

Question

我正在编写一个程序，该程序循环遍历 Excel 工作簿中的所有工作表并将每个工作表另存为自己的工作簿。事实证明，这比我预期的要棘手一些，因为 Sheet.Copy 方法创建了一个奇怪的对象(请参阅此处，了解我认为相关的 MSDN 讨论: http://msdn.microsoft.com/en-us/library/ms178779.aspx )。

无论如何，我找到了another Stack Overflow post这让我到达了现在的位置，基本上已经完成了，在程序完成后留下的一个挂起的 EXCEL.EXE 进程之外(检查更新是否出现了另一个问题，但我认为它们是相关的)。

这是我的代码:

Imports System.Data
Imports System.IO
Imports Microsoft.Office.Interop
Imports Office = Microsoft.Office.Core
Imports xlNS = Microsoft.Office.Interop.Excel
Imports System.Runtime.InteropServices

Class Form1

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

    'Get information from text boxes
    Dim InputLocation As String
    Dim OutputLocation As String

    InputLocation = InputLoc.Text & "\" & FileName.Text

    If OutputLoc.Text = "" Then
        OutputLocation = InputLoc.Text
    Else
        OutputLocation = OutputLoc.Text
    End If

    'Make file to save files in
    ' Get date and time in filename as well
    Dim TLDateTime As String
    Dim TLDay As String
    Dim TLMonth As Integer
    Dim TLYear As Integer
    Dim TLHour As Integer
    Dim TLMinute As Integer
    Dim TLDate As String
    Dim TLTime As String
    Dim TLSecond As Integer

    TLDay = DateTime.Now.Day
    TLMonth = DateTime.Now.Month
    TLYear = DateTime.Now.Year
    TLHour = DateTime.Now.Hour
    TLMinute = DateTime.Now.Minute
    TLSecond = DateTime.Now.Second

    Dim MyDate As New DateTime(TLYear, TLMonth, TLDay, TLHour, TLMinute, TLSecond)
    Dim MyString As String = MyDate.ToString("MMMddyyyy_HHmmss")
    TLDate = TLMonth.ToString + TLDay.ToString + TLYear.ToString
    TLTime = TLHour.ToString + TLMinute.ToString
    TLDateTime = TLDate + "_" + TLTime

    Try
        Directory.CreateDirectory(OutputLocation & "\" & "Field Sales Report Graphs " & TLDateTime)
        OutputLocation = OutputLocation & "\" & "Field Sales Report Graphs " & TLDateTime
    Catch
        MsgBox("Trying to create a file that exists, please delete it. If the file does not exist check to make sure your output location exists")
    End Try

    'Open up excel file with information in it

    Dim xlApp1 As Excel.Application
    Dim locs As Excel.Workbook

    Dim exportsheet As Excel.Worksheet
    xlApp1 = New Excel.Application
    xlApp1.Visible = True
    xlApp1.Application.DisplayAlerts = False
    locs = xlApp1.Workbooks.Open(InputLocation)

    'locsws = locs.ActiveSheet
    Dim wkshtcount = locs.Worksheets.Count - 1
    Dim fileNames As New ArrayList

    For counter = 1 To wkshtcount + 1
        'identify and copy sheet to move
        exportsheet = CType(locs.Worksheets(counter), Excel.Worksheet)
        fileNames.Add(exportsheet.Name)
        exportsheet.Copy(Type.Missing, Type.Missing)

        exportsheet = xlApp1.Workbooks("Book" & counter).Sheets(1)

        exportsheet.SaveAs(Filename:=OutputLocation & "\" & fileNames(counter - 1) & ".xlsx")

        'close excel and release com objects
        System.Runtime.InteropServices.Marshal.ReleaseComObject(exportsheet)
        exportsheet = Nothing
        System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp1.ActiveWorkbook)
        xlApp1.ActiveWorkbook.Close(False)

    Next
    'close excel and release com objects
    locs.Close(False)
    System.Runtime.InteropServices.Marshal.ReleaseComObject(locs)
    locs = Nothing
    xlApp1.Quit()
    System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp1)
    xlApp1 = Nothing


End Sub
End Class

现在我认为问题来自循环的末尾，我尝试关闭导出文件及其创建的新工作表:

        'close excel and release com objects
        System.Runtime.InteropServices.Marshal.ReleaseComObject(exportsheet)
        exportsheet = Nothing
        xlApp1.Workbooks(fileNames(counter - 1)).Close(False)

我不知道如何为创建的新工作表释放 ComObject。我一直在尝试各种各样的事情，但当我这样做时，它总是会抛出一个 COM 错误，如果我尝试将其定义为空(就像我对导出表所做的那样)，则表示它默认是只读的，所以我可以'不要这样做。看起来应该很简单:

System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp1.Workbooks(fileNames(counter - 1)))

但事实并非如此。我已经尝试了一些变体，我认为它与上面的 MSDN 链接有关，但我不太清楚该怎么做。因此，该代码可以满足我的目的，除了在完成后留下一个 EXCEL.EXE 之外。

就测试文件而言，我只使用包含 3 个工作表的 Excel 文件，并在每个工作表上添加一些信息并更改工作表名称，因此很容易看出它是否正常工作。

如有任何想法，我们将不胜感激，谢谢。

更新:我刚刚关闭了主 Excel 应用程序的可见性，但内容仍然弹出，这让我相信我使用 Copy 的方式正在创建一个新的 Excel 应用程序，但我我不太确定如何引用它。如果有人知道如何关闭那里的可见性，我们将不胜感激。

最终更新:如果某些可怜的人遇到了与我遇到的相同问题，第一次更新应该可以解决它，但同样重要的是要注意，excel.exe 将挂起，直到您关闭应用程序。我将自动化代码报告为 Windows 窗体应用程序(以便同事可以提供文件位置等)，并且将有一个 excel.exe 进程运行，直到您从程序中关闭弹出窗口。也许垃圾收集在您关闭应用程序窗口之前不会运行，或者它只是由于某些其他原因卡在 excel.exe 的实例上。

Answer 1

下面是适合我的代码(注意我释放对象的顺序，这很重要)

xlWorkBook.Close()
xlApp.Quit()
ReleaseObject(xlWorkSheet)
ReleaseObject(xlWorkBook)
ReleaseObject(xlApp)

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