[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 667
  • Last Modified:

C# and Excel Interop resource problem

I am having an issue with releasing a reference to an excel spreadsheet after editing with c# code.

I open the excel file as follows:

Excel.Application excelApp = new Excel.ApplicationClass();
excelApp.DisplayAlerts = false;
excelApp.Visible = false;
excelApp.EnableEvents = false;
Excel.Workbook workbook = excelApp.Workbooks.Open(fileName, false, false, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Excel.Worksheet ws = (Excel.Worksheet) excelApp.ActiveSheet;
pipelineWS.Unprotect(AGENCY_PASSWORD);

Some data is entered into the sheet then I close excel as follows:

workbook.Save();
workbook.Close(true, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
app.Workbooks.Close();
app.Quit();
Marshal.ReleaseComObject(ws);
Marshal.ReleaseComObject(workbook);
Marshal.ReleaseComObject(excelApp);

However, when I look in the task manager processes the EXCEL.exe is never released.

Anyone have any clues?

Thanks in advance.
0
ksedran
Asked:
ksedran
  • 3
  • 3
  • 2
1 Solution
 
ShannonECommented:
see below
using Excel = Microsoft.Office.Interop.Excel;
...
 
Excel.Application xlApp = null;
Excel.Workbook xlWb = null;
Excel.Worksheet grid = null;
 
try {
xlApp = new Excel.ApplicationClass();
xlApp.Visible = false;
xlWb = xlApp.Workbooks.Open(fname, 0, false, 5, Missing.Value, Missing.Value, false, Missing.Value, Missing.Value, true, false, Missing.Value, false, false, false);
grid = (Excel.Worksheet)xlWb.Worksheets[<yourSheetName>];
...
do stuff
...
xlWb.Save();
}//end try
 
finally {
 
if (xlWb != null) { xlWb.Close(Missing.Value, Missing.Value, Missing.Value); }
 
if (xlApp != null) { xlApp.Quit(); }
}//end finally			
		

Open in new window

0
 
ShannonECommented:
sorry, for Missing.Value you need to also include:

using System.Reflection;

0
 
Rory ArchibaldCommented:
What is pipelineWS in your code? and why do you switch from excelApp to app?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
ksedranAuthor Commented:
Hey rorya, sorry, that should be ws not pipelineWS.

I just changed the variable names when posting on here.
0
 
Rory ArchibaldCommented:
If you run the code with the Excel application visible are there any dialogs displayed at the end?
0
 
ksedranAuthor Commented:
There are no dialog boxes displayed at the end.

I was doing some debugging and discovered that the resources are actually released, but not until the application is closed, or a garbage collection is forced and the method finishes.

I made a tiny app that simply opens then closes an excel workbook on a button click.

private void button1_Click(object sender, EventArgs e)
        {
            string masterSpreadsheet = "C:\\Data\\Master\\MasterRecommendation 2008.xls";

            Microsoft.Office.Interop.Excel.Application masterExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
            masterExcelApp.DisplayAlerts = false;
            masterExcelApp.Visible = true;
            masterExcelApp.EnableEvents = false;
            Microsoft.Office.Interop.Excel.Workbook masterWorkbook = masterExcelApp.Workbooks.Open(masterSpreadsheet, false, false, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            Microsoft.Office.Interop.Excel.Worksheet masterws = (Microsoft.Office.Interop.Excel.Worksheet)masterExcelApp.ActiveSheet;

            masterWorkbook.Save();
            masterWorkbook.Close(true, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
            masterExcelApp.Workbooks.Close();
            masterExcelApp.Quit();
            Marshal.ReleaseComObject(masterws);
            Marshal.ReleaseComObject(masterWorkbook);
            Marshal.ReleaseComObject(masterExcelApp);
        }

When I close the app the resources are released.

If I add a GC.Collect after the last line (Marshal.ReleaseComObject(masterExcelApp);)
then the resources are released after button_Click method is complete.

The problem is that I need the resources released before the method is complete because I am opening several sheets one after another in a loop. The resources have to be released right after the
Marshal.ReleaseComObject(masterExcelApp); line.

Anyone know how to force this?
0
 
Rory ArchibaldCommented:
I have read somewhere (I think in a book by Andrew Whitechapel) that you need to issue the GC.Collect twice:
 

GC.Collect();
GC.Waitforpendingfinalizers();
GC.Collect();
GC.Waitforpendingfinalizers();

Open in new window

0
 
ksedranAuthor Commented:
Thanks a million rorya!!!

That works.
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now