EXCEL.EXE remains in processes after Dispose...

[Vagabond Software]

Scenario:
The end-user selects File-Save from my applications (WinForm) menu, specifies a filename, and waits waits for my app to write the contents of a dataset to an Excel workbook. The SaveXLReport method completes successfully, calls myXLClass.Dispose, and returns control to the user form at a base state.

At this point, there is still an EXCEL.EXE in processes and the specified workbook cannot be saved until that process is ended from Task Manager or until the end-user exits my application, which also ends the EXCEL.EXE process.

Here are relevant snippets from myXL:

public class MyXL : System.IDisposable
{
private excel9.Application xlapp;

public MyXL()
{
xlapp = null;
}

~MyXL()
{
if (xlapp != null)
Dispose(false);
}

public void Dispose()
{
Dispose(true);
GC.SuppressFinalize(this);
}

public virtual void Dispose(bool disposing)
{
if (!this.disposed)
{
this.xlapp.Quit();
int referenceCount = 0;
do
{
referenceCount = System.Runtime.InteropServices.Marshal.ReleaseComObject(this.xlapp);
}
while (referenceCount > 0);

this.xlapp = null;
if (disposing)
{
GC.SuppressFinalize(this);
}
}
disposed = true;
}
}

Here is the SaveXLReport method from the Report class, which is a windows form:

private bool SaveXLReport(DataSet ds)
{
bool failed = true;
string filename = ds.ExtendedProperties["ReportName"].ToString();
for (int i = 0; i < ds.Tables.Count; i++)
UpdateXLWorksheetData(filename, ds.Tables);
if (cymerXL != null)
{
cymerXL.SaveOpenWorkbook();
cymerXL.Dispose();
failed = false;
}
return failed;
}

 

[Colin R. R. Johnson]

I think this is a bug in the COM for Excel.

I ran into the exact same problem doing reporting from a simple Access
database into a complex Excel spreadsheet.

I literally spent a couple of days trying to solve it, without any luck.

Scenario:
The end-user selects File-Save from my applications (WinForm) menu, specifies a filename, and waits waits for my app to write the contents of a dataset to an Excel workbook. The SaveXLReport method completes successfully, calls myXLClass.Dispose, and returns control to the user form at a base state.

At this point, there is still an EXCEL.EXE in processes and the specified workbook cannot be saved until that process is ended from Task Manager or until the end-user exits my application, which also ends the EXCEL.EXE process.

Here are relevant snippets from myXL:

public class MyXL : System.IDisposable
{
private excel9.Application xlapp;

public MyXL()
{
xlapp = null;
}

~MyXL()
{
if (xlapp != null)
Dispose(false);
}

public void Dispose()
{
Dispose(true);
GC.SuppressFinalize(this);
}

public virtual void Dispose(bool disposing)
{
if (!this.disposed)
{
this.xlapp.Quit();
int referenceCount = 0;
do
{
referenceCount = System.Runtime.InteropServices.Marshal.ReleaseComObject(this.xlapp);
}
while (referenceCount > 0);

this.xlapp = null;
if (disposing)
{
GC.SuppressFinalize(this);
}
}
disposed = true;
}
}

Here is the SaveXLReport method from the Report class, which is a windows form:

private bool SaveXLReport(DataSet ds)
{
bool failed = true;
string filename = ds.ExtendedProperties["ReportName"].ToString();
for (int i = 0; i < ds.Tables.Count; i++)
UpdateXLWorksheetData(filename, ds.Tables);
if (cymerXL != null)
{
cymerXL.SaveOpenWorkbook();
cymerXL.Dispose();
failed = false;
}
return failed;
}

 

[Vagabond Software]

I was fortunate enough to resolve this problem this morning.

I created an ArrayList to store every Excel object that is created and use my Dispose method to iterate through the ArrayList and get rid of all Excel objects. I also added a GC.Collect call after the Dispose call in the Reporter class. Here is the new code in the Dispose method of my Excel class.

for (int i = 0; i < alXlObjects.Count; i++)
{
int referenceCount = 0;
do
{
referenceCount = System.Runtime.InteropServices.Marshal.ReleaseComObject(alXlObjects);
alXlObjects = null;
}
while (referenceCount > 0);
}

Thanks for the reply.

carl

I think this is a bug in the COM for Excel.

I ran into the exact same problem doing reporting from a simple Access
database into a complex Excel spreadsheet.

I literally spent a couple of days trying to solve it, without any luck.

Scenario:
The end-user selects File-Save from my applications (WinForm) menu, specifies a filename, and waits waits for my app to write the contents of a dataset to an Excel workbook. The SaveXLReport method completes successfully, calls myXLClass.Dispose, and returns control to the user form at a base state.

At this point, there is still an EXCEL.EXE in processes and the specified workbook cannot be saved until that process is ended from Task Manager or until the end-user exits my application, which also ends the EXCEL.EXE process.

Here are relevant snippets from myXL:

public class MyXL : System.IDisposable
{
private excel9.Application xlapp;

public MyXL()
{
xlapp = null;
}

~MyXL()
{
if (xlapp != null)
Dispose(false);
}

public void Dispose()
{
Dispose(true);
GC.SuppressFinalize(this);
}

public virtual void Dispose(bool disposing)
{
if (!this.disposed)
{
this.xlapp.Quit();
int referenceCount = 0;
do
{
referenceCount = System.Runtime.InteropServices.Marshal.ReleaseComObject(this.xlapp);
}
while (referenceCount > 0);

this.xlapp = null;
if (disposing)
{
GC.SuppressFinalize(this);
}
}
disposed = true;
}
}

Here is the SaveXLReport method from the Report class, which is a windows form:

private bool SaveXLReport(DataSet ds)
{
bool failed = true;
string filename = ds.ExtendedProperties["ReportName"].ToString();
for (int i = 0; i < ds.Tables.Count; i++)
UpdateXLWorksheetData(filename, ds.Tables);
if (cymerXL != null)
{
cymerXL.SaveOpenWorkbook();
cymerXL.Dispose();
failed = false;
}
return failed;
}

 

[Stefan Simek]

I would just like to add this...

There is a document somewhere in the MSDN clarifying the COM interop. I
don't remember where exactly it was, but it says something about the runtime
creating only 1 reference to each object through a proxy, and reusing this
one afterwards. After some experiments, the simplest solution I found is
what I've posted below (And it's the ONLY case so far where I found it
useful to call the GC.Collect() explicitly). Keeping track of the objects
that are to be disposed is pretty hard, and after all, it might not work
well if you for example want to leave the excel open after doing whatever
you want to do. The GC.Collect() ways is pretty clean, as you really don't
have to care about any reference counting.

void DoSomethingWithExcel()
{
// do all your automation only in this method, leaving no references
around
..
...
...
}

void DoItNow()
{
DoSomethingWithExcel();
GC.Collect();
}

This way, you don't even need to bother about calling dispose, etc.

HTH,
Stefan

I was fortunate enough to resolve this problem this morning.

I created an ArrayList to store every Excel object that is created and use
my Dispose method to iterate through the ArrayList and get rid of all Excel
objects. I also added a GC.Collect call after the Dispose call in the
Reporter class. Here is the new code in the Dispose method of my Excel
class.

for (int i = 0; i < alXlObjects.Count; i++)
{
int referenceCount = 0;
do
{
referenceCount =
System.Runtime.InteropServices.Marshal.ReleaseComObject(alXlObjects);
alXlObjects = null;
}
while (referenceCount > 0);
}

Thanks for the reply.

carl

I think this is a bug in the COM for Excel.

I ran into the exact same problem doing reporting from a simple Access
database into a complex Excel spreadsheet.

I literally spent a couple of days trying to solve it, without any luck.

Scenario:
The end-user selects File-Save from my applications (WinForm) menu,
specifies a filename, and waits waits for my app to write the contents
of a dataset to an Excel workbook. The SaveXLReport method completes
successfully, calls myXLClass.Dispose, and returns control to the user
form at a base state.

At this point, there is still an EXCEL.EXE in processes and the
specified workbook cannot be saved until that process is ended from Task
Manager or until the end-user exits my application, which also ends the
EXCEL.EXE process.

Here are relevant snippets from myXL:

public class MyXL : System.IDisposable
{
private excel9.Application xlapp;

public MyXL()
{
xlapp = null;
}

~MyXL()
{
if (xlapp != null)
Dispose(false);
}

public void Dispose()
{
Dispose(true);
GC.SuppressFinalize(this);
}

public virtual void Dispose(bool disposing)
{
if (!this.disposed)
{
this.xlapp.Quit();
int referenceCount = 0;
do
{
referenceCount =
System.Runtime.InteropServices.Marshal.ReleaseComObject(this.xlapp);
}
while (referenceCount > 0);

this.xlapp = null;
if (disposing)
{
GC.SuppressFinalize(this);
}
}
disposed = true;
}
}

Here is the SaveXLReport method from the Report class, which is a
windows form:

private bool SaveXLReport(DataSet ds)
{
bool failed = true;
string filename = ds.ExtendedProperties["ReportName"].ToString();
for (int i = 0; i < ds.Tables.Count; i++)
UpdateXLWorksheetData(filename, ds.Tables);
if (cymerXL != null)
{
cymerXL.SaveOpenWorkbook();
cymerXL.Dispose();
failed = false;
}
return failed;
}