C＃Excel自动化导致Excel内存泄漏 [英] C# Excel automation causes Excel memory leak

问题描述

我想用C＃与COM互操作库，打开一组非常沉重的Excel工作簿中。我必须使用C＃，因为我也需要开始宏，中移动的一些细胞，并启动自定义的Excel加载我公司使用。

我的程序然后退出，留下打开的工作簿，每一个单独的Excel实例。我不想在程序退出时，工作簿被关闭。

问题是，当我的C＃程序退出时，随着时间的推移，在Excel工作簿逐渐消耗更多的内存，直到他们从原来的500 MB消耗的内存3.5演出。

我用手动打开工作簿和床单从来没有消耗的多的内存。一旦我开始使用C＃打开它们，他们开始因为极端的内存使用量的突破。我的理论是，不知怎的，当我与COM Excel对象进行互动，我创建了一个内存泄漏。

下面是我原来的code：

 使用Excel =的Microsoft.Office.Interop.Excel;
...
excelApp =新Excel.Application（）;
excelApp.Visible =真;
excelApp.Workbooks.Open（文件名，misValue，misValue，misValue，misValue，misValue，
               真的，misValue，misValue，misValue，misValue，misValue，misValue，misValue，misValue）;
excelApp.Calculation = Excel.XlCalculation.xlCalculationAutomatic;
 

我读到你需要如何使用元帅发布用途，所以现在我想下面的code，但没有简单的方法来测试它，除了打开所有的床单，看他们是否消耗太多大量的数据。

  excelApp =新Excel.Application（）;
            excelApp.Visible =真;
            Excel.Workbooks currWorkbooks = excelApp.Workbooks;
            Excel.Workbook currWorkbook = currWorkbooks.Open（文件名，misValue，misValue，misValue，misValue，misValue，
               真的，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互操作库，有几件事情我已经遇到以避免内存泄漏：

首先，不要用两个点，就是要记住它​​的最好办法，但基本上，始终分配一个新的COM对象引用一个新的变量，不要链成员通话，即使智能感知鼓励它。链式调用做一些事情在prevents由.NET Framework适当的释放背景..下面是一些code我使用启动Excel报表：

  //使用瓦尔每一个COM对象，因此引用不要剩
//主Excel的应用
VAR excelApp =新的应用程序（）;
VAR工作簿= excelApp.Workbooks;//工作簿模板
VAR wbReport = workbooks.Add（@C：\\ MyTemplate.xltx）;//为工作簿表对象
VAR wSheetsReport = wbReport.Sheets;
VAR wsReport =（表）wSheetsReport.get_Item（工作表Sheet1）;
 

其次，呼叫对Marshal.ReleaseComObject（）在创建相反的顺序创建的每个变量，这样做之前叫了几个垃圾收集方法：

  //垃圾收集器
所以GC.Collect（）;
GC.WaitForPendingFinalizers（）;//清理
对Marshal.ReleaseComObject（wsReport）;
对Marshal.ReleaseComObject（wSheetsReport）;
对Marshal.ReleaseComObject（wbReport）;
对Marshal.ReleaseComObject（练习册）;
对Marshal.ReleaseComObject（excelApp）;
 

使用这个方案我每次使用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.

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.

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.

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.

Below is my original code:

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;

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;

解决方案

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

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

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

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内存泄漏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！