Hanging Excel.exe process in automated program

Question

var tempFileName = file.Replace(".xlsx", "_Temp.xlsx");
Application excelApp = new Application();
Workbooks books = excelApp.Workbooks;
Workbook excelFile = books.Open(file);
Sheets sheets = excelFile.Worksheets;
var app = excelApp.Application;
DeleteRows(sheets, 3);
excelFile.SaveAs(tempFileName);
excelFile.Close();
books.Close();
app.Quit();
excelApp.Quit();
Marshal.FinalReleaseComObject(sheets);
Marshal.ReleaseComObject(excelFile);
Marshal.ReleaseComObject(books);
Marshal.FinalReleaseComObject(excelApp);
sheets = null;
excelFile = null;
excelApp = null;
books = null;

I am trying to dispose of my excel objects, however even the above code still leaves me with hanging EXCEL.EXE processes after the program has finished running. I have seen this question on here a number of times, but most of the askers had not tried the ReleaseComObject() method, however that did not fix anything for me.

EDIT: This is the code i use to read the table after deleting the header

  System.Data.DataTable schemaTable = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
  DataRow schemaRow = schemaTable.Rows[0];
  string sheet = schemaRow["TABLE_NAME"].ToString();
  if (!sheet.EndsWith("_"))
  {
      string query = "SELECT  * FROM [Daily Payment$]";
      OleDbDataAdapter daexcel = new OleDbDataAdapter(query, excelConnection);
      dtexcel.Locale = CultureInfo.CurrentCulture;
      daexcel.Fill(dtexcel);
      var reader = dtexcel.CreateDataReader();

and here is the code for DeleteRows:

void DeleteRows(Sheets sheets, int n)
{ 
    foreach (Worksheet workSheet in sheets)
    {
        Range range = workSheet.get_Range("A1", "A" + n);
        Range row = range.EntireRow;
        row.Delete(XlDirection.xlUp);
        Marshal.ReleaseComObject(workSheet);
    }
}

FINAL EDIT: I have reposted the new code that I have at the top of the post. I am still getting one lingering object (even though i run the process multiple times). Calling Gc.Collect() works, but as a relative neophyte, i figured it was best to avoid something so powerful.

Answer 1

For freeing Office COM interop objects, you can go by the 'one release per . rule'. That is, when you have a chain of member accesses, you need to free all the intermediate objects.

This often means splitting up your code into additional lines.

Following that rule, you're missing one. You need to keep a handle to the Workbooks object you've implicitly created.

Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
Workbooks books = excelApp.Workbooks;
Workbook excelFile = books.Open(file);
DeleteRows(excelFile, 3);

Then you need to call:

Marshal.ReleaseComObject(books);

at the end.

Failing this, you can also try calling:

books.Close()

before you quit Excel. And to go overkill, you can change all your ReleaseComObject calls to FinalReleaseComObject.

EDIT: In your newly posted update, you are not releasing your Row or Range objects. Call FinalReleaseComObject on these, and I'd expect the problem to be solved.