如何关闭Excel com对象并保存工作？

问题描述

这正在推动我坚果。我已经找到类似的Excel com对象在这里，但没有一个解决方案，我尝试可以成功关闭Excel，我的代码完成运行后。 Excel文件将在TaskManager上停留。

有人可以看看下面的代码，让我知道为什么Excel不会关闭？

 尝试
'首先获取应用程序实例
 xlApp =新建Excel.Application（）
'第二个打开你需要的工作簿
 xlBook = xlApp.Workbooks.Open（_FileInfo.FullName）
 xlSheet = xlBook.Worksheets（sheetName）
'设置可见性
 xlApp.Visible = False 
 Dim row As Integer = 2 
对于每个t在upsRecordList 
 While（row< upsRecordList.Count + 2）
 xlSheet.Cells（row，1）= t.StartDate 
 xlSheet.Cells（row，2）= t.AccountTypeCode 
 row + = 1 
结束而
下一个
捕获ex As Exception 
抛出ex 
最后
如果不是xlApp是没有
 GC.Collect（）
 GC.W aitForPendingFinalizers（）
 GC.Collect（）
 GC.WaitForPendingFinalizers（）
 NAR（xlSheet）
如果不是xlBook是没有
 xlBook.Close（SaveChanges：=真的）
 NAR（xlBook）
如果
 xlApp.Quit（）
 NAR（xlApp）
结束If 
 End尝试
 
 Private Sub NAR（ByVal o As Object）
尝试
 Marshal.FinalReleaseComObject（o）
 Catch 
最后
o = Nothing 
 End Try 
 End Sub 
  

解决方案

：

保持Excel实例的单例：

  //我们单例excel实例
私有静态Excel.Application实例; 
 
 ///< summary> 
 ///返回Excel应用程序的一个实例。 
 ///这个实例是一个单例。 
 ///< / summary> 
 public static Excel.Application GetInstance 
 {
 get 
 {
 if（instance == null）
 {
 try 
 {
 instance = new Excel.Application（）; 
 //给应用程序一个GUID，所以我们可以再杀死它
 instance.Caption = System.Guid.NewGuid（）。ToString（）。ToUpper（CultureInfo.CurrentCulture）; 
 instance.Visible = false; 
} 
 catch（COMException ce）
 {
 ShowMessage（ce.Message，MessageBoxIcon.Error）; 
} 
} 
 return instance; 
} 
} 
  

完成后，您可以销毁它：

  public static void DestroyInstance（）
 {
 if（instance！= null）
 {
 //关闭工作簿
 object missing = Type.Missing; 
 foreach（Excel.Workbook book in GetInstance.Workbooks）
 {
 book.Close（false，book.FullName，missing）; 
} 
 
 String appVersion = instance.Version; 
 String appCaption = instance.Caption; 
 IntPtr appHandle = IntPtr.Zero; 
 if（Convert.ToDouble（appVersion，CultureInfo.CurrentCulture）> = 10）
 {
 appHandle = new IntPtr（instance.Parent.Hwnd）; 
} 
 
 //退出并释放对象
 instance.Workbooks.Close（）; 
 instance.Quit（）; 
发布（实例）; 
 
 //强制清理。 MSDN文档显示他们使
 //两组收集和等待调用。 
 System.GC.Collect（）; 
 System.GC.WaitForPendingFinalizers（）; 
 System.GC.Collect（）; 
 System.GC.WaitForPendingFinalizers（）; 
 
 EnsureProcessKilled（appHandle，appCaption）; 
} 
} 
  

EnsureProcessKilled方法如下所示：

  ///< summary> 
 ///这个方法包含一些方法来尝试和杀死
 ///实例化时创建的Excel进程。 
 ///< / summary> 
 ///< param name =windowHandle>应用程序窗口的句柄< / param> 
 ///< param name =caption>存储为应用标题< / param>的自定义GUI 
 public static void EnsureProcessKilled（IntPtr windowHandle，String caption）
 {
 NativeMethods.SetLastError（0）; 
 if（IntPtr.Equals（windowHandle，IntPtr.Zero））
 {
 windowHandle = NativeMethods.FindWindow（null，caption）; 
} 
 if（IntPtr.Equals（windowHandle，IntPtr.Zero））
 {
 //找不到窗口;假设它是关闭的
 return; 
} 
 int resourceId; 
 int processId = 0; 
 resourceId = NativeMethods.GetWindowThreadProcessId（windowHandle，ref processId）; 
 if（processId == 0）
 {
 //无法获取进程ID 
 if（NativeMethods.EndTask（windowHandle）！= 0）
 {
返回; // Success 
} 
} 
 //无法结束，我们来杀死它
 System.Diagnostics.Process进程; 
 process = System.Diagnostics.Process.GetProcessById（processId）; 
 process.CloseMainWindow（）; 
 process.Refresh（）; 
 if（process.HasExited）
 {
 return; 
} 
 //如果我们到这里，它真的很固执。说再见，EXCEL.EXE！ 
 process.Kill（）; 
} 
  

而您将需要NativeMethods类：

  public static class NativeMethods 
 {
 
 [DllImport（user32.dll）] 
内部静态extern int EndTask（IntPtr windowHandle）; 
 
 [DllImport（user32.dll，CharSet = CharSet.Unicode）] 
内部静态extern IntPtr FindWindow（String className，String windowName）; 
 
 [DllImport（user32.dll）] 
内部静态extern int GetWindowThreadProcessId（IntPtr windowHandle，ref int processId）; 
 
 [DllImport（kernel32.dll）] 
内部静态extern IntPtr SetLastError（int errorCode）; 
 
} 
  

另一个小帮手功能：

  public static void Release（Object releasedable）
 {
 System.Runtime.InteropServices.Marshal.ReleaseComObject（可释放）; 
 releasesable = null; 
} 
  

This is driving me nut. I have found similar post regarding Excel com object here but none of the solution that I tried can successful close Excel after my code is finish running. The Excel.dll will linger on the TaskManager.

Can someone take a look at my code below and let me know why the Excel won't close?

Try
        'First get an application instance
        xlApp = New Excel.Application()
        'Second open the workbook you need
        xlBook = xlApp.Workbooks.Open(_FileInfo.FullName)
        xlSheet = xlBook.Worksheets(sheetName)
        'set visibility
        xlApp.Visible = False
        Dim row As Integer = 2
        For Each t In upsRecordList
            While (row < upsRecordList.Count + 2)
                xlSheet.Cells(row, 1) = t.StartDate
                xlSheet.Cells(row, 2) = t.AccountTypeCode
                row += 1
            End While
        Next
    Catch ex As Exception
        Throw ex
    Finally
        If Not xlApp Is Nothing Then
            GC.Collect()
            GC.WaitForPendingFinalizers()
            GC.Collect()
            GC.WaitForPendingFinalizers()
            NAR(xlSheet)
            If Not xlBook Is Nothing Then
                xlBook.Close(SaveChanges:=True)
                NAR(xlBook)
            End If
            xlApp.Quit()
            NAR(xlApp)
        End If
    End Try

 Private Sub NAR(ByVal o As Object)
    Try
        Marshal.FinalReleaseComObject(o)
    Catch
    Finally
        o = Nothing
    End Try
End Sub

解决方案

Here's what I've done:

Keep your Excel instance a singleton:

// Our singleton excel instance
private static Excel.Application instance;

/// <summary>
/// Returns an instance of an Excel Application.
/// This instance is a singleton.
/// </summary>
public static Excel.Application GetInstance
{
    get
    {
        if (instance == null)
        {
            try
            {
                instance = new Excel.Application();
                // Give the app a GUID so we can kill it later
                instance.Caption = System.Guid.NewGuid().ToString().ToUpper(CultureInfo.CurrentCulture);
                instance.Visible = false;
            }
            catch (COMException ce)
            {
                ShowMessage(ce.Message, MessageBoxIcon.Error);
            }
        }
        return instance;
    }
}

When you're done, you can destroy it:

   public static void DestroyInstance()
    {
        if (instance != null)
        {
            // Close the workbooks
            object missing = Type.Missing;
            foreach (Excel.Workbook book in GetInstance.Workbooks)
            {
                book.Close(false, book.FullName, missing);
            }

        String appVersion = instance.Version;
        String appCaption = instance.Caption;
        IntPtr appHandle = IntPtr.Zero;
        if (Convert.ToDouble(appVersion, CultureInfo.CurrentCulture) >= 10)
        {
            appHandle = new IntPtr(instance.Parent.Hwnd);
        }

        // Quit and release the object
        instance.Workbooks.Close();
        instance.Quit();
        Release(instance);

        // Force a cleanup.  MSDN documentation shows them making
        // two sets of collect and wait calls.
        System.GC.Collect();
        System.GC.WaitForPendingFinalizers();
        System.GC.Collect();
        System.GC.WaitForPendingFinalizers();

        EnsureProcessKilled(appHandle, appCaption);
    }
}

The EnsureProcessKilled method looks like:

/// <summary>
/// This method contains a number of ways to try and kill
/// the Excel process created when an instance is instantiated.
/// </summary>
/// <param name="windowHandle">Handle of the application window</param>
/// <param name="caption">A custom GUI stored as the app's caption</param>
public static void EnsureProcessKilled(IntPtr windowHandle, String caption)
{
    NativeMethods.SetLastError(0);
    if (IntPtr.Equals(windowHandle, IntPtr.Zero))
    {
        windowHandle = NativeMethods.FindWindow(null, caption);
    }
    if (IntPtr.Equals(windowHandle, IntPtr.Zero))
    {
        // Can't find the window; assumed it's closed
        return;
    }
    int resourceId;
    int processId = 0;
    resourceId = NativeMethods.GetWindowThreadProcessId(windowHandle, ref processId);
    if (processId == 0)
    {
        // Can't get process id
        if (NativeMethods.EndTask(windowHandle) != 0)
        {
            return; // Success
        }
    }
    // Couldn't end it nicely, let's kill it
    System.Diagnostics.Process process;
    process = System.Diagnostics.Process.GetProcessById(processId);
    process.CloseMainWindow();
    process.Refresh();
    if (process.HasExited)
    {
        return;
    }
    // If we get here, it's being really stubborn.  Say goodbye, EXCEL.EXE!
    process.Kill();
}

And you're going to need the NativeMethods class:

public static class NativeMethods
{

    [DllImport("user32.dll")]
    internal static extern int EndTask(IntPtr windowHandle);

    [DllImport("user32.dll", CharSet = CharSet.Unicode)]
    internal static extern IntPtr FindWindow(String className, String windowName);

    [DllImport("user32.dll")]
    internal static extern int GetWindowThreadProcessId(IntPtr windowHandle, ref int processId);

    [DllImport("kernel32.dll")]
    internal static extern IntPtr SetLastError(int errorCode);

}

And one other little helper function:

public static void Release(Object releasable)
{
    System.Runtime.InteropServices.Marshal.ReleaseComObject(releasable);
    releasable = null;
}

这篇关于如何关闭Excel com对象并保存工作？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！