Hanging excel processes

[sand133]

Hi, I have a problem with hanging excel processes. My applications gets data from the database and populates a excel spreadsheet however all is well untill I view the task manager to find the EXCEL.EXE still running. I've managed to solve it by calling the GC.Collect method but I think thats quite a brute method to do it, is there a more elegant way of doing this?

my code is shown below.




Excel.ApplicationClass app = new ApplicationClass();

app.Workbooks.Add(Missing.Value);

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

app.Visible = true;
Worksheet worksheet = (Worksheet)app.ActiveSheet;


worksheet.SaveAs(@"C:\test.xls",
Missing.Value,
Missing.Value,
Missing.Value,
Missing.Value,
Missing.Value,
Missing.Value,
Missing.Value,
Missing.Value);

app.Workbooks.Close();
app.Quit();

Marshal.ReleaseComObject(app);
Marshal.ReleaseComObject(worksheet);

app = null;
worksheet = null;
GC.Collect(); -- DONT WANT TO DO THIS

 

[misterstick]

interop is rubbish at garbage collection.

you have to explicitly dispose of every object you create.

this includes objects created implicitly by referencing them.

at a brief glance, you're referencing but not releasing app.Cells, app.Cells[1, 1], app.ActiveSheet and app.Workbooks.

stick a Marshall.RealseComObject in for each of these and you should be able to get rid of GC.Collect().

good luck,


mr s. <

 

[misterstick]

you could also see the discussion at thread732-1300363.


mr s. <

 

[sand133]

thanks for your reply tried that still no luck,


Excel.ApplicationClass app = new ApplicationClass();
app.EnableEvents = false;
app.Workbooks.Add(Missing.Value);

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

Marshal.ReleaseComObject(app.Cells[1, 1]);
Marshal.ReleaseComObject(app.Cells);

app.Visible = true;
Worksheet worksheet = (Worksheet)app.ActiveSheet;

worksheet.SaveAs(@"C:\test.xls",
Missing.Value,
Missing.Value,
Missing.Value,
Missing.Value,
Missing.Value,
Missing.Value,
Missing.Value,
Missing.Value);

Marshal.ReleaseComObject(app.ActiveSheet);

app.Workbooks.Close();
app.Quit();

Marshal.ReleaseComObject(worksheet);
Marshal.ReleaseComObject(app.Workbooks);
Marshal.ReleaseComObject(app);




just out of interest is their a better slicker way of getting data from the db into excel? rather then using excel interop objects

 

[misterstick]

you'd be better off caching the references like this:

Cells myCells = app.Cells;
Cell myCell = myCells[1,1];
myCell.Value = "First Name";
Marshal.ReleaseComObject(myCell);
Marshal.ReleaseComObject(myCells);

i'm not entirely sure, but i suspect that myCells[1,1] is syntactic sugar for myCells.Rows(1).Cells(1) which means you'd have to create a pair of releases for each of rows and columns, too, one for the collection and one for the object itself.

this gets quite involved, i really liked splitting (word) [tt]ActiveDocument.Paragraphs(ActiveDocument.Paragraphs.Count - 1).Range.ListFormat.ApplyListTemplate ListTemplate:=Application.ListGalleries(wdNumberGallery).ListTemplates(7), ContinuePreviousList:=False, ApplyTo:=wdListApplyToWholeList, DefaultListBehavior:=wdWord9ListBehavior)[/tt] into its constituent parts.

i wish there were a better way, and if anyone can find one i'd use it in a shot.

mr s. <

 

[sand133]

thanks MR s, im going to go down the route of Marshal.ReleaseComObject followed by GC.Collect()

 

[MadJock]

sand133: I encountered exactly the same issue and resolved it using the same approach (ReleaseComObject and GC.Collect).

It works and I've had no problems with it but would be interested to hear if you come up with a better solution as you are developing!

Cheers,

Graeme

"Just beacuse you're paranoid, don't mean they're not after you

 

[sand133]

Hey madLock, I decided to go down the GC.Collect route i couldnt get it to work any other way. Works well though in a production enviroment.