VB.NET Excel 程序完成后让 EXCEL.EXE 浮动 [英] VB.NET Excel Program Leaves EXCEL.EXE floating after completion

问题描述

我正在编写一个程序，它遍历 Excel 工作簿中的所有工作表，并将每个工作表保存为自己的工作簿.结果比我预期的要复杂一些，因为 Sheet.Copy 方法创建了一个奇怪的对象(我认为相关的 MSDN 讨论见这里:http://msdn.microsoft.com/en-us/library/ms178779.aspx).

I am writing a program that loops through all of the sheets in an Excel workbook and saves each sheet as its own workbook. It turned out to be a bit trickier than I expected, since the Sheet.Copy method creates a strange object (see here for the MSDN discussion that I believe is relevant: http://msdn.microsoft.com/en-us/library/ms178779.aspx).

无论如何，我发现 另一篇 Stack Overflow 帖子 使我到达了我现在所在的位置，这基本上是完整的，除了程序完成后留下的一个挂起的 EXCEL.EXE 进程(检查更新以了解出现的另一个问题，但我认为它们是相关的).

Anyway, I found another Stack Overflow post that got me to where I am, which is essentially complete, outside of one hanging EXCEL.EXE process that is left after the program completes (check the update for one more issue that came up, but I think they are related).

这是我的代码:

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

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

Now I think the problem comes from the end of the loop, where I try to close the export file and the new worksheet it creates:

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

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

I can't figure out what to do to release the ComObject for the new worksheet that is created. I have been trying all sorts of things, but it always throws a COM error when I do it and if I try to define it as nothing (like I do with exportsheet) is says that it is read only by default, so I can't do it. It seems like it should be something as simple as:

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

但事实并非如此.我已经尝试了一些变体，我认为它与上面的 MSDN 链接有关，但我无法弄清楚该怎么做.所以代码对我的目的有效，除了在完成后留下一个 EXCEL.EXE.

But that is not the case. I have tried a few variants of this and I am thinking it has to do with the MSDN link above, but I can't quite sort out what to do. So the code works for my purposes, outside of leaving one EXCEL.EXE after it is done.

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

As far as a test file, I am just using an Excel file with 3 sheets and I put some information on each and change the sheet name, so it is easy to see if it is working or not.

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

Any thoughts would be appreciated, thanks.

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

Update: I just turned the visibility off from my main Excel application and things still pop up, which leads me to believe that the way I am using Copy is creating a new Excel application, but I am not really sure how to reference it. If anyone knows how to turn off visibility there as well, it would be much appreciated.

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

Final Update: On the off chance that some poor soul ran into the same issue I was running into, the first update should solve it, but it is also important to note that the excel.exe will hang until you close the application. I am report automation code as a windows form application (so coworkers can give the file location and such) and there is going to be an excel.exe process running until you close the pop up window from the program. Maybe garbage collect does not run until you close the application window or it just hangs on to an instance of excel.exe for some other reason.

推荐答案

我最终遇到的解决方案是添加

The solution I finally came across was to add

System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp1.ActiveWorkbook)
xlApp1.ActiveWorkbook.Close()

进入循环.我在 第一个答案的评论中找到了这个答案另一个堆栈溢出帖子.基本上，我遇到的问题是 worksheet.copy 方法创建了一个没有引用的工作簿对象，但事实证明它可以通过引用 activesheet 来引用.如果你想用它做更多的事情，而不仅仅是像我一样把它踢出去，我想你可以创建一个新的工作簿对象并分配它，或者按照我链接到的帖子的建议，你也可以将它另存为.对于我的目的，保存我想要的工作表后释放它就好了，这消除了我的 excel.exe 挂起过程.

to the loop. I found this answer in the comments of The first answer on another stack overflow post. Basically, the problem I was having is that the worksheet.copy method creates a workbook object with no reference, but it turns out it can be referenced by referring to the activesheet. If you wanted to do more with it than just kick it out the door like I am, I imagine you could create a new workbook object and assign it or as the post I link to suggests, you can save it as something as well. For my puposes, it is just fine to release it after saving the worksheet I want and this removed my excel.exe hanging process.

如果你想要更优雅的代码选项，你应该查看 ron tornbe 的帖子，他在那里为每个循环我做了一个我设法不正确的循环，而不是我创建的不稳定的东西.基本上你会想在我的代码中使用他的循环，然后你就可以把它全部设置好.一如既往地感谢堆栈溢出.

If you want a bit more elgant code option you should check out the ron tornambe post where he does the for each loop I managed to not get right instead of the wonky thing I create. Basically you would want to use his loop in my code and you would have it all set up. Thanks as always stack overflow.

这篇关于VB.NET Excel 程序完成后让 EXCEL.EXE 浮动的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！