为c#互操作对象创建可重用的Excel样式
本文关键字:Excel 样式 互操作 对象 创建 | 更新日期: 2023-09-27 18:12:53
我正在使用c#生成几个电子表格,并且我正在尝试创建一个可重用的样式,可以应用于每个电子表格中的特定范围。我遇到的问题是,当我的方法完成执行时,Excel过程没有正确退出。事实上,我已经能够将问题区域缩小到我创建Excel Style
的代码点。
Workbooks books = excelApplication.Workbooks;
_Workbook wBook = books.Add("");
_Worksheet wSheet = (_Worksheet)wBook.ActiveSheet;
Styles styles = wBook.Styles;
Style columnHeader = styles.Add("ColumnHeader");
columnHeader.Font.Size = 12; // if I comment this out, excel quits correctly
Marshal.ReleaseComObject(wSheet);
Marshal.ReleaseComObject(wBook);
Marshal.ReleaseComObject(books);
当我应用它时,样式按预期工作,但是当我退出excelApplication时,Excel进程不会退出。如果我注释掉columnHeader.Font.Size = 12;
行,Excel进程将正确退出。我错过什么了吗?
我修改了政府的示例WinForms应用程序,以反映我的类的结构,这是创建多个电子表格。他的应用程序正确退出Excel进程,但我修改的版本没有:
using System;
using Excel = Microsoft.Office.Interop.Excel;
using System.Windows.Forms;
namespace ExcelCOMReferenceTesting
{
public partial class Form1 : Form
{
private Excel.Application excelApplication;
private Excel.Style columnHeader;
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
DoMyExcelStuff();
GarbageCleanup();
}
private void DoMyExcelStuff()
{
StartExcel();
var wBook = GenerateWorksheet();
excelApplication.Range["A1"].Value = "Name";
excelApplication.Range["A1"].Style = columnHeader;
wBook.SaveAs(@"c:'Test'tst" + DateTime.Now.ToString("mmss") + ".xlsx");
// No need for Marshal.ReleaseComObject(...)
// No need for ... = null
StopExcel();
}
private void StartExcel()
{
excelApplication = new Excel.Application();
excelApplication.Visible = false;
}
private void StopExcel()
{
excelApplication.UserControl = false;
excelApplication.Quit();
}
private Excel._Workbook GenerateWorksheet()
{
Excel.Workbooks books = excelApplication.Workbooks;
Excel.Workbook wBook = books.Add("");
Excel.Styles styles = wBook.Styles;
columnHeader = styles.Add("ColumnHeader");
columnHeader.Font.Size = 12;
columnHeader.Font.Bold = true;
return wBook;
}
private void GarbageCleanup()
{
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
}
可能是对Font/columnHeader或styles的挂起引用,在Excel互操作中访问属性的属性会导致Excel进程挂起;试试这个
Workbooks books = excelApplication.Workbooks;
_Workbook wBook = books.Add("");
_Worksheet wSheet = (_Worksheet)wBook.ActiveSheet;
Styles styles = wBook.Styles;
Style columnHeader = styles.Add("ColumnHeader");
Font font = columnHeader.Font;
font.Size = 12;
Marshal.ReleaseComObject(font);
Marshal.ReleaseComObject(columnHeader);
Marshal.ReleaseComObject(styles);
Marshal.ReleaseComObject(wSheet);
Marshal.ReleaseComObject(wBook);
Marshal.ReleaseComObject(books);
font = null;
columnHeader = null;
styles = null;
wSheet = null;
wBook = null;
books = null;
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
在这种情况下不需要调用Marshal.ReleaseComObject
。运行时完全能够跟踪COM对象,并在不再引用它们时释放它们。调用Marshal.ReleaseComObject
是一个令人困惑的反模式,可悲的是,甚至一些微软文档错误地建议。您也不必将本地变量设置为null
-当方法完成时,引用将超出作用域。
要使Excel关闭,需要调用excelApplication.Quit()
,然后确保没有对Excel COM对象的活动引用,然后调用垃圾收集器进行清理。您应该调用垃圾收集器两次——您可能会遇到引用形成循环的情况,第一次GC调用将打破循环,但是COM对象可能只在第二次调用时才被正确释放。
在调试构建中,您还必须小心处理这类代码。方法中的引用被人为地保持活动状态,直到方法结束,以便在调试器中仍然可以访问它们。这意味着您的本地Excel COM对象可能不会通过调用该方法中的GC来清理。为了避免这个问题,您可以遵循这样的模式:
public void DoMyExcelStuffAndCleanup()
{
DoMyExcelStuff();
// Call GC twice to ensure that cleanup after cycles happens immediately
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
}
public void DoMyExcelStuff()
{
Application excelApplication = ...
// Here you access all those Excel objects ...
// No need for Marshal.ReleaseComObject(...)
// No need for ... = null
excelApplication.Quit();
}
我现在用一个新的WinForms应用程序进行了测试,我添加了一个按钮和下面的代码:
using System;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;
namespace WinFormsComCleanup
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
DoMyExcelStuff();
GarbageCleanup();
}
private void DoMyExcelStuff()
{
Excel.Application excelApplication = new Excel.Application();
Excel.Workbooks books = excelApplication.Workbooks;
Excel.Workbook wBook = books.Add("");
Excel.Worksheet wSheet = (Excel.Worksheet)wBook.ActiveSheet;
Excel.Styles styles = wBook.Styles;
Excel.Style columnHeader = styles.Add("ColumnHeader");
columnHeader.Font.Size = 12;
columnHeader.Font.Bold = true;
excelApplication.Range["A1"].Value = "Name";
excelApplication.Range["A1"].Style = columnHeader;
wBook.SaveAs(@"c:'Temp'tst" + DateTime.Now.ToString("mmss") +".xlsx");
// No need for Marshal.ReleaseComObject(...)
// No need for ... = null
excelApplication.Quit();
}
private void GarbageCleanup()
{
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
}
一切正常,Excel进程在GarbageCleanup()
调用期间停止。
问题更新后,我建议修改如下:
private void StopExcel()
{
excelApplication.UserControl = false;
excelApplication.Quit();
// Set the form-level variables to null, so that no live references to Excel remain
columnHeader = null;
excelApplication = null;
}
这将确保在更新的问题中引入的对象级字段在GC运行之前删除其引用。
在我的测试中,通过这个修改,Excel再次退出。这与我的理解是一致的,即。net运行时正确地跟踪COM引用,并且Marshal.ReleaseComObject
从来不需要Excel互操作。