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 ）。

无论如何，我发现另一个Stack Overflow文章，让我到我所在的地方，这本质上是完整的，在一个悬挂的EXCEL.EXE进程之后，剩下的是程序完成（检查更新一个问题，但我认为它们是相关的）。

这是我的代码：

 导入System.Data 
导入System.IO 
导入Microsoft.Office.Interop 
导入Office = Microsoft.Office.Core 
导入xlNS = Microsoft.Office.Interop.Excel 
导入System.Runtime.InteropServices 
 
类Form1 
 
 Private Sub Button1_Click（ByVal sender As System。对象，ByVal e As System.EventArgs）句柄Button1.Click 
 
'从文本框中获取信息
 Dim InputLocation As String 
 Dim OutputLocation As String 
 
 InputLocation = Inp utLoc.Text& \& FileName.Text 
 
如果OutputLoc.Text =然后
 OutputLocation = InputLoc.Text 
 Else 
 OutputLocation = OutputLoc.Text 
 End If 
 
'将文件保存在
'中获取文件名中的日期和时间以及
 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（MMMddyyyyHHmmss）
 TLDate = TLMonth.ToString + TLDay.ToString + TLYear.ToString 
 TLTime = TLHour.ToString + TLMinute.ToString 
 TLDateTime = TLDate +_+ TLTime 
 
尝试
 Directory.CreateDirectory（OutputLocation& \& 现场销售报告图& TLDateTime）
 OutputLocation = OutputLocation& \& 现场销售报告图& TLDateTime 
 Catch 
 MsgBox（尝试创建一个存在的文件，请删除它。如果文件不存在，请确保输出位置存在）
 End尝试
 
'打开Excel文件中的信息
 
 Dim xlApp1 As Excel.Application 
 Dim locs As Excel.Workbook 
 
 Dim exportheet As Excel $工作表
 xlApp1 =新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 
 
对于counter = 1 To wkshtcount + 1 
'标识和复印表移动
 exportheet = CType（locs.Worksheets（counter），Excel.Worksheet）
 fileNames.Add（exportsheet.Name）
 exportsheet.Copy（Type .Missing，Type.Missing）
 
 export heet = xlApp1.Workbooks（Book&计数器）。（1）
 
导出表.SaveAs（文件名：= OutputLocation&\& fileNames（counter-1）&.xlsx）
 
'close excel和release com对象
 System.Runtime.InteropServices.Marshal.ReleaseComObject（exportheet）
 exportheet = Nothing 
 System.Runtime.InteropServices.Marshal.ReleaseComObject（xlApp1.ActiveWorkbook） 
 xlApp1.ActiveWorkbook.Close（False）
 
下一个
'关闭excel和发布com对象
 locs.Close（False）
 System.Runtime。 InteropServices.Marshal.ReleaseComObject（locs）
 locs = Nothing 
 xlApp1.Quit（）
 System.Runtime.InteropServices.Marshal.ReleaseComObject（xlApp1）
 xlApp1 = Nothing 
 
 
 End Sub 
结束类
  

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

  'close excel and release com objects 
 System.Runtime.InteropServices.Marshal.ReleaseComObject（exportheet）
 exportheet = Nothing 
 xlApp1.Workbooks（fileNames（counter-1））。Close（False） 
  

我无法确定如何发布 ComObject 为创建的新工作表。我一直在尝试各种各样的事情，但是当我这样做时总是会引发一个COM错误，如果我尝试将它定义为没有（就像我对exportheet一样）说的是默认只读，所以我可以'不要这样做似乎应该是一样简单：

  System.Runtime.InteropServices.Marshal.ReleaseComObject（xlApp1.Workbooks（fileNames （counter-1）））
  

但情况并非如此。我已经尝试了一些这样的变体，我认为它与上面的MSDN链接有关，但我不太清楚该做什么。所以代码可以为我的目的而工作，在完成之后离开一个EXCEL.EXE。

就测试文件而言，我只是使用Excel文件3张，我把每个信息放在一起，并更改表单，所以很容易看到它是否工作。

任何想法都不胜感激，谢谢。

更新：我刚刚从我的主Excel应用程序和东西仍然弹出，这使我相信我使用复制的方式是创建一个新的Excel应用程序，但我不太确定如何引用它。如果有人知道如何关闭能见度，那将是非常感激的。

最后更新：在一些可怜的灵魂遇到同样的问题的机会正在运行，第一个更新应该解决它，但同样重要的是要注意，excel.exe将挂起，直到你关闭应用程序。我将报表自动化代码作为Windows窗体应用程序（所以同事可以给出文件位置等），并且将直接运行excel.exe进程，直到从程序关闭弹出窗口。可能垃圾收集不会运行，直到您关闭应用程序窗口，或者因为某些其他原因才挂起excel.exe实例。

解决方案

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

  System.Runtime.InteropServices.Marshal.ReleaseComObject（xlApp1.ActiveWorkbook ）
 xlApp1.ActiveWorkbook.Close（）
  

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

如果你想要一些更多的代码选项，你应该检查ron tornambe的帖子，他做的每一个循环，我设法不正确，而不是我创造的凶猛的事情。基本上你会想要在我的代码中使用他的循环，你可以把它全部设置好。感谢总是堆栈溢出。

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).

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).

Here is my code:

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)

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)))

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.

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.

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.

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()

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.

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屋！