C# Excel 自动化导致 Excel 内存泄漏

问题描述

我正在尝试使用 C# 和 COM Interop 库来打开一组非常繁重的 excel 工作簿.我必须使用 C#，因为我还需要启动宏，移动一些单元格，并启动我公司使用的自定义 excel 插件.

I'm trying to use C# with the COM Interop library to open a set of very heavy excel workbooks. I have to use C#, because I also need to start macros, move some cells around, and start a custom excel-add-in my company uses.

然后我的程序退出，工作簿保持打开状态，每个工作簿都在一个单独的 excel 实例中.我不希望在程序退出时关闭工作簿.

My program then exits, leaving the workbooks open, each in a separate excel instance. I DO NOT want the workbooks to be closed when the program exits.

问题是，当我的 C# 程序退出时，随着时间的推移，excel 工作簿会逐渐消耗更多内存，直到它们从原来的 500 mb 中消耗 3.5 gig 的内存.

The problem is that when my C# program exits, over time, the excel workbooks gradually consume more memory, until they're consuming 3.5 gigs of memory from an original 500 mb.

我曾经手动打开工作簿，而工作表从未消耗那么多内存.一旦我开始使用 C# 打开它们，它们就会因为极端的内存使用而中断.我的理论是，不知何故，当我与 COM Excel 对象交互时，我造成了内存泄漏.

I used to open the workbooks by hand, and the sheets never consumed that much memory. Once I started opening them using C#, they started to break because of extreme memory usage. My theory is that somehow, when I interact with the COM Excel object, I create a memory leak.

以下是我的原始代码:

using Excel = Microsoft.Office.Interop.Excel;
...
excelApp = new Excel.Application();
excelApp.Visible = true;
excelApp.Workbooks.Open(filename, misValue, misValue, misValue, misValue, misValue,
               true, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue);
excelApp.Calculation = Excel.XlCalculation.xlCalculationAutomatic;

我读到您需要如何使用 Marshal 来发布用途，所以我现在正在尝试以下代码，但没有简单的方法来测试它，除了打开所有工作表并查看它们是否消耗了太多数据.

I read about how you need to use Marshal to release uses, so I'm now trying the following code, but have no easy way to test it, other than opening all the sheets and seeing if they consume too much data.

            excelApp = new Excel.Application();
            excelApp.Visible = true;
            Excel.Workbooks currWorkbooks = excelApp.Workbooks;
            Excel.Workbook currWorkbook = currWorkbooks.Open(filename, misValue, misValue, misValue, misValue, misValue,
               true, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue);
            //excelApp.Calculation = Excel.XlCalculation.xlCalculationAutomatic;

            int x = Marshal.ReleaseComObject(currWorkbook);
            currWorkbook = null;

            int y = Marshal.ReleaseComObject(currWorkbooks);
            currWorkbooks = null;

推荐答案

在使用 MS Office COM Interop 库时，我遇到了一些避免内存泄漏的方法:

When using the MS Office COM Interop libraries, there are a couple of things I've come across to avoid the memory leaks:

首先，不要使用两个点"是记住它的最佳方式，但基本上，始终将新的 COM 对象引用分配给新变量，不要链调用成员，即使 Intellisense 鼓励这样做.链式调用会在后台执行一些阻止 .NET 框架正确发布的操作.以下是我用于启动 Excel 报告的一些代码:

First, "Don't use two dots" is the best way to remember it, but basically, always assign a new COM object reference to a new variable, do not chain-call members, even if Intellisense encourages it. Chained calling does some stuff in the background that prevents proper release by the .NET framework.. Here is some code I use for starting an Excel report:

//use vars for every COM object so references don't get leftover
//main Excel app
var excelApp = new Application();
var workbooks = excelApp.Workbooks;

//workbook template
var wbReport = workbooks.Add(@"C:MyTemplate.xltx");

//Sheets objects for workbook
var wSheetsReport = wbReport.Sheets;
var wsReport = (Worksheet)wSheetsReport.get_Item("Sheet1");

其次，为每个以创建逆序创建的变量调用Marshal.ReleaseComObject()，并在此之前调用几个垃圾收集方法:

Secondly, Call Marshal.ReleaseComObject() for each variable created in reverse order of creation, and call a couple of garbage collection methods before doing so:

//garbage collector
GC.Collect();
GC.WaitForPendingFinalizers();

//cleanup
Marshal.ReleaseComObject(wsReport);
Marshal.ReleaseComObject(wSheetsReport);
Marshal.ReleaseComObject(wbReport);
Marshal.ReleaseComObject(workbooks);
Marshal.ReleaseComObject(excelApp);

每次使用 Excel 时都使用这个方案解决了我的记忆问题，虽然我们不能像以前那样使用链接成员很乏味和悲伤.

Using this scheme every time I use Excel has solved my memory issues, though it is tedious and sad we can't use the chained members the way we're used to.

这篇关于C# Excel 自动化导致 Excel 内存泄漏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！