【转】使用Office PIA生成Excel并释放资源

转自：http://blog.bossma.cn/csharp/office_pia_make_excel_release_resource/

    在All-In-One Code Framework 上下载了一个使用.NET操作Office的例程。文中详细介绍了如何使用Microsoft Excel Primary Interop Assembly (PIA)生成Excel及释放Com资源。这篇文章把代码提出来，简单翻译加上我的理解。

    使用Office PIA之前需要安装，如果没有安装Office，需要先下载安装：
Office 2007 Primary Interop Assemblies
Office 2003 Primary Interop Assemblies

PIA会安装到Visual Studio的安装目录，引用的时候需要注意，不是引用Com对象。

    当使用这个解决方案时，应当尽量避免通过通道调用访问器，因为这些对象会将堆中的Runtime Callable Wrapper (RCW)孤立，从而不能调用Marshal.ReleaseComObject释放这些资源。

RCW是在运行时通过CLR从Interop Assembly的元数据中获取相关信息动态的实例化而得到的。可以理解为介乎于COM和.Net应用程序之间的一个代理，.Net应用程序对COM组件的调用请求都是通过RCW中转。

例如：

查看源代码

打印?

Excel.Workbook oWB = oXL.Workbooks.Add(missing);

通过oXL.Workbooks.Add这样一个通道调用访问器，将会在GC堆中为Workbooks创建一个RCW，但是引用会被放到栈的范围之内，然后很快被丢弃。
这样将没有办法为这个RCW调用MarshalFinalReleaseComObject。

解决方案1：
如果明确的为每一个访问器分配一个变量，然后就可以释放它们了，例如:
可以通过下边的代码创建一个WorkBook：

查看源代码

打印?

Excel.Workbooks oWBs = oXL.Workbooks;

Excel.Workbook oWB = oWBs.Add(missing);

然后通过变量释放堆中的资源：

查看源代码

打印?

Marshal.FinalReleaseComObject(oWBs);

oWBs = null;

Marshal.FinalReleaseComObject(oWB);

oWB = null;

Solution1.cs

查看源代码

打印?

using System;

using System.Collections.Generic;

using System.Text;

using System.IO;

using System.Reflection;

using Excel = Microsoft.Office.Interop.Excel;

using System.Runtime.InteropServices;

namespace CSAutomateExcel

{

    static class Solution1

    {

        public static void AutomateExcel()

        {

            object missing = Type.Missing;

            Excel.Application oXL = null;

            Excel.Workbooks oWBs = null;

            Excel.Workbook oWB = null;

            Excel.Worksheet oSheet = null;

            Excel.Range oCells = null;

            Excel.Range oRng1 = null;

            Excel.Range oRng2 = null;

            try

            {

                //创建一个Microsoft Excel的实例，并设置为隐藏界面

                oXL = new Excel.Application();

                oXL.Visible = false;

                //创建一个新的工作表Workbook

                oWBs = oXL.Workbooks;

                oWB = oWBs.Add(missing);

                //获取工作表激活的Worksheet，并设置sheet的名称

                oSheet = oWB.ActiveSheet as Excel.Worksheet;

                oSheet.Name = "Report";

                #region 填充数据

                // 设置标题行

                oCells = oSheet.Cells;

                oCells[1, 1] = "First Name";

                oCells[1, 2] = "Last Name";

                oCells[1, 3] = "Full Name";

                // 创建一个数据数组

                string[,] saNames = new string[,] {

                {"John", "Smith"},

                {"Tom", "Brown"},

                {"Sue", "Thomas"},

                {"Jane", "Jones"},

                {"Adam", "Johnson"}};

                // 使用数组中的数据填充 A2:B6

                oRng1 = oSheet.get_Range("A2", "B6");

                oRng1.Value2 = saNames;

                // 使用公式(=A2 & " " & B2)填充C2:C6

                oRng2 = oSheet.get_Range("C2", "C6");

                oRng2.Formula = "=A2 & \" \" & B2";

                #endregion

                // 保存工作表，然后关闭

                string fileName = Path.GetDirectoryName(

                    Assembly.GetExecutingAssembly().Location) + "\\Sample1.xlsx";

                oWB.SaveAs(fileName, Excel.XlFileFormat.xlOpenXMLWorkbook,

                    missing, missing, missing, missing,

                    Excel.XlSaveAsAccessMode.xlNoChange,

                    missing, missing, missing, missing, missing);

                oWB.Close(missing, missing, missing);

                //当Excel或通过程序调用启动，并且Application.Visible = false时，Application.UserControl 的值是false。

                //如果UserControl为false，且还有未完成的调用时，Excel进程将会继续保留。

                //可以设置UserControl为true，强迫Quit方法调用时，应用程序被终止，而不管未完成的调用。

                oXL.UserControl = true;

                oXL.Quit();

            }

            catch (Exception ex)

            {

                Console.WriteLine("Solution1.AutomateExcel throws the error: {0}",

                    ex.Message);

            }

            finally

            {

                // 明确的释放非托管的COM资源

                // 调用Marshal.FinalReleaseComObject对所有的访问其对象. 

                if (oRng2 != null)

                {

                    Marshal.FinalReleaseComObject(oRng2);

                    oRng2 = null;

                }

                if (oRng1 != null)

                {

                    Marshal.FinalReleaseComObject(oRng1);

                    oRng1 = null;

                }

                if (oCells != null)

                {

                    Marshal.FinalReleaseComObject(oCells);

                    oCells = null;

                }

                if (oSheet != null)

                {

                    Marshal.FinalReleaseComObject(oSheet);

                    oSheet = null;

                }

                if (oWB != null)

                {

                    Marshal.FinalReleaseComObject(oWB);

                    oWB = null;

                }

                if (oWBs != null)

                {

                    Marshal.FinalReleaseComObject(oWBs);

                    oWBs = null;

                }

                if (oXL != null)

                {

                    Marshal.FinalReleaseComObject(oXL);

                    oXL = null;

                }

            }

        }

    }

}

解决方案2：
强制调用GC回收。

查看源代码

打印?

using System;

using System.Collections.Generic;

using System.Text;

using System.IO;

using System.Reflection;

using Excel = Microsoft.Office.Interop.Excel;

namespace CSAutomateExcel

{

static class Solution2

{

public static void AutomateExcel()

{

AutomateExcelImpl();

//当调用函数从栈中移除时，立即强制垃圾回收释放非托管的Excel Com资源

GC.Collect();

GC.WaitForPendingFinalizers();

//GC需要被调用两次

//第一次 产生将要结束的资源列表

//第二次 实际执行释放资源

GC.Collect();

GC.WaitForPendingFinalizers();

}

private static void AutomateExcelImpl()

{

object missing = Type.Missing;

try

{

// 创建一个Excel实例并隐藏

Excel.Application oXL = new Excel.Application();

oXL.Visible = false;

// 创建一个Workbook

Excel.Workbook oWB = oXL.Workbooks.Add(missing);

Console.WriteLine("A new workbook is created");

//创建一个Worksheet

Excel.Worksheet oSheet = oWB.ActiveSheet as Excel.Worksheet;

oSheet.Name = "Report";

#region 填充数据

// Set the column header

oSheet.Cells[1, 1] = "First Name";

oSheet.Cells[1, 2] = "Last Name";

oSheet.Cells[1, 3] = "Full Name";

// Construct an array of user names

string[,] saNames = new string[,] {

{"John", "Smith"},

{"Tom", "Brown"},

{"Sue", "Thomas"},

{"Jane", "Jones"},

{"Adam", "Johnson"}};

// Fill A2:B6 with an array of values (First and Last Names).

oSheet.get_Range("A2", "B6").Value2 = saNames;

// Fill C2:C6 with a relative formula (=A2 & " " & B2).

oSheet.get_Range("C2", "C6").Formula = "=A2 & \" \" & B2";

#endregion

// 保存文件并关闭对象

string fileName = Path.GetDirectoryName(

Assembly.GetExecutingAssembly().Location) + "\\Sample2.xlsx";

oWB.SaveAs(fileName, Excel.XlFileFormat.xlOpenXMLWorkbook,

missing, missing, missing, missing,

Excel.XlSaveAsAccessMode.xlNoChange,

missing, missing, missing, missing, missing);

oWB.Close(missing, missing, missing);

//当Excel或通过程序调用启动，并且Application.Visible = false时，Application.UserControl 的值是false。

//如果UserControl为false，且还有未完成的调用时，Excel进程将会继续保留。

//可以设置UserControl为true，当Quit方法调用时，强制应用程序终止，而不管未完成的调用。

oXL.UserControl = true;

//调用Quit退出

oXL.Quit();

}

catch (Exception ex)

{

Console.WriteLine("Solution2.AutomateExcel throws the error: {0}", ex.Message);

}

}

}

}