Excel not closing when using OleDBAdapter

[rypi]

I made a small app to re-create the problem I am having when opening an excel worksheet, performing some work on it, then closing it. In this example I am simply opening the excel file then closing it.

Open your task manager and view the processes and you'll see that when the
System.Data.DataTable table = GetSheetData(masterSpreadsheet, "AP South", data); statement is commented out the EXCEL.exe process closes. Leave it in and excel.exe is left open.
You'll have to add the Microsoft.Interop.Excel reference in order for this to work.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.Runtime.InteropServices;
using System.Text;
using System.Windows.Forms;
using Microsoft.Office.Interop.Excel;

namespace ExcelTest
{
public partial class Form1 : Form
{

public Form1()
{
InitializeComponent();
}

private void button1_Click(object sender, EventArgs e)
{
string masterSpreadsheet = "C:\\Data\\TestSheet.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;

DataSet data = new DataSet();
System.Data.DataTable table = GetSheetData(masterSpreadsheet, "Sheet1", data);

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

GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
}

private System.Data.DataTable GetSheetData(string fileName, string agencyName, DataSet data)
{
OleDbConnection connection = new OleDbConnection();

using (connection = CreateExcelConnection(fileName))
{
OleDbDataAdapter adapter = new OleDbDataAdapter(string.Format("SELECT * FROM [" + agencyName + "$]"), connection);
adapter.Fill(data);
adapter.SelectCommand.Dispose();
adapter.Dispose();
adapter = null;
connection.ConnectionString = null;
connection.Close();
connection.Dispose();
connection = null;
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
return data.Tables[0];
}
}

private OleDbConnection CreateExcelConnection(string fileName)
{
StringBuilder connectionStringBuilder = new StringBuilder(string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}", fileName));
const string EXCEL_EXTENDED_CONNECTION_PROPERTIES = @";Extended Properties=""Excel 8.0;HDR=Yes""";
connectionStringBuilder.Append(EXCEL_EXTENDED_CONNECTION_PROPERTIES);
return new OleDbConnection(connectionStringBuilder.ToString());
}
}
}

[JonnyPoet]

Please use codetags and repost your code so it can be orderly copied to a project and doesn't need to be reformatted by hand This is standard usage here in CG accordin to forum rules, because code without codetags losts format and looks like yours - terrible to read.

[JonnyPoet]

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.Runtime.InteropServices;
using System.Text;
using System.Windows.Forms;
using Microsoft.Office.Interop.Excel;
namespace ExcelTest {
    public partial class Form1 : Form {
        public Form1() {
            InitializeComponent();
        }
        private void button1_Click(object sender, EventArgs e) {
            string masterSpreadsheet = "C:\\Data\\TestSheet.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;
            DataSet data = new DataSet();
            System.Data.DataTable table = GetSheetData(masterSpreadsheet, "Sheet1", data);
            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);
            GC.Collect();
            GC.WaitForPendingFinalizers();
            GC.Collect();
            GC.WaitForPendingFinalizers();
        }
        private System.Data.DataTable GetSheetData(string fileName, string agencyName, DataSet data) {
            OleDbConnection connection = new OleDbConnection();
            using (connection = CreateExcelConnection(fileName)) {
                OleDbDataAdapter adapter = new OleDbDataAdapter(string.Format("SELECT * FROM [" + agencyName + "$]"), connection);
                adapter.Fill(data);
                adapter.SelectCommand.Dispose();
                adapter.Dispose();
                adapter = null;
                connection.ConnectionString = null;
                connection.Close();
                connection.Dispose();
                connection = null;
                GC.Collect();
                GC.WaitForPendingFinalizers();
                GC.Collect();
                GC.WaitForPendingFinalizers();
                return data.Tables[0];
            }
        }
        private OleDbConnection CreateExcelConnection(string fileName) {
            StringBuilder connectionStringBuilder = new StringBuilder(string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}", fileName));
            const string EXCEL_EXTENDED_CONNECTION_PROPERTIES = @";Extended Properties=""Excel 8.0;HDR=Yes""";
            connectionStringBuilder.Append(EXCEL_EXTENDED_CONNECTION_PROPERTIES);
            return new OleDbConnection(connectionStringBuilder.ToString());
        }
    }
}

I did it for you. Now lets see

[JonnyPoet]

Hi !
You obviously need to insert

masterWorkbook.Save();
// here you insert
table.Dispose();
masterWorkbook.Close(true, System.Reflection.Missing.Value, System.Reflection.Missing.Value);

BTW you may or not do the following code

Marshal.ReleaseComObject(masterws);
Marshal.ReleaseComObject(masterWorkbook);
Marshal.ReleaseComObject(masterExcelApp);
GC.Collect();
GC.WaitForPendingFinalizers(); 
GC.Collect();
GC.WaitForPendingFinalizers(); 

I did instead

Marshal.ReleaseComObject(masterws);
Marshal.ReleaseComObject(masterWorkbook);
Marshal.ReleaseComObject(masterExcelApp);
masterws = null;
masterWorkbook = null;
masterExcelApp = null;
GC.Collect();
GC.WaitForPendingFinalizers(); 
//GC.Collect();
//GC.WaitForPendingFinalizers(); 

And I also deleted in GetSheetData

using (connection = CreateExcelConnection(fileName)) {
....
                //connection.Dispose();
                //connection = null;
                //GC.Collect();
                //GC.WaitForPendingFinalizers();
                //GC.Collect();
                //GC.WaitForPendingFinalizers();
}

Because IMHO its not so good to do this inside the using statement. And it works without this !