C# Excel doesn't close itself when using OleDbConnection's Open method

Question

I am creating and releasing references to excel com interfaces to manipulate excel's worksheets.

In this situation Excel closes itself correctly. If I use OleDbDataAdapter connection to fetch data then excel is still in the memory.

I have read almost everything on this subject.

1. I have created subroutines to appropriate release references.
2. I am using:

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

What else can I do?

This seems to be obscure problem..

Here is the code:

namespace ExcelTestCode
{
 class Program
 {
   static void Main(string[] args)
   {
     Application excel = null;
     Workbook workbook = null;
     Worksheet workSheet = null;
     object oMissing = Missing.Value;

     excel = new Application { Visible = false };
     workbook = excel.Workbooks.Open(@"c:\temp.xls", 0, false, 5, "", "", 
                true, XlPlatform.xlWindows, "\t", false, false, 0, true, true, oMissing);
     workSheet = (Worksheet)workbook.Sheets[1];

     try
     {
       string strError = "";
       System.Data.DataTable dtTable = null;

       //If I remove the following line, everything is allright
       dtTable = ImportDataTableFromExcelIMEX(@"c:\temp.xls", out strError);
     }
     finally
     {
       if (workSheet != null)
       {
         Marshal.ReleaseComObject(workSheet);
         workSheet = null;
       }
       if (workbook != null)
       {
         workbook.Close(false, oMissing, oMissing);
         Marshal.ReleaseComObject(workbook);
         workbook = null;
       }

       if (excel != null)
       {
         excel.Quit();
         Marshal.ReleaseComObject(excel);
         excel = null;
       }
       GC.Collect();
       GC.WaitForPendingFinalizers();
       GC.Collect(); 
     }
   }

   public static System.Data.DataTable ImportDataTableFromExcelIMEX(string filename, out string error)
   {
     string connstring = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filename + @";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1""";
     OleDbConnection upocn = new OleDbConnection(connstring);
     try
     {
       upocn.Open();

       System.Data.DataTable dt = null;
       dt = upocn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

       using (OleDbDataAdapter upoda = new OleDbDataAdapter("select * from [" + dt.Rows[0]["TABLE_NAME"].ToString() + "]", upocn))
       {
         DataSet upods = new DataSet();
         error = string.Empty;

         upoda.Fill(upods);

         if (!string.IsNullOrEmpty(error))
           return null;

         return upods.Tables[0];
       }
     }
     catch (Exception ex)
     {
       error = ex.Message;
     }
     finally
     {
       upocn.Close();
       upocn = null;
     }
     return null;
   }
 }
}

Answer 1

try using (OleDbConnection upocn = new OleDbConnection(connectionString)), or call upocn.Dispose()

From MSDN OleDbConnection.Dispose: Releases all resources used by the System.ComponentModel.Component. OleDbConnection.close: Closes the connection to the data source

Updated I can produce this problem if i do connection.close like in above code, but when i call dispose it works fine, I dont see any instance of excel. Below is code that works for me. Ensure you clean running instance from task manager before you test

class Program
{
    static void Main(string[] args)
    {
        Application excel = null;
        Workbook workbook = null;
        Worksheet workSheet = null;
        object oMissing = Missing.Value;

        excel = new Application { Visible = false };
        workbook = excel.Workbooks.Open(@"c:\Book1.xls", 0, false, 5, "", "",
                   true, XlPlatform.xlWindows, "\t", false, false, 0, true, true, oMissing);
        workSheet = (Worksheet)workbook.Sheets[1];

        try
        {
            string strError = "";
            System.Data.DataTable dtTable = null;

            //If I remove the following line, everything is allright 
            dtTable = ImportDataTableFromExcelIMEX(@"c:\Book1.xls", out strError);
        }
        finally
        {
            if (workSheet != null)
            {
                Marshal.ReleaseComObject(workSheet);
                workSheet = null;
            }
            if (workbook != null)
            {
                workbook.Close(false, oMissing, oMissing);
                Marshal.ReleaseComObject(workbook);
                workbook = null;
            }

            if (excel != null)
            {
                excel.Quit();                   
                Marshal.ReleaseComObject(excel);
                excel = null;
            }
            GC.Collect();
            GC.WaitForPendingFinalizers();
            GC.Collect();
        }
    }

    public static System.Data.DataTable ImportDataTableFromExcelIMEX(string filename, out string error)
    {
        string connstring = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filename + @";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1""";

        try
        {
            using (OleDbConnection upocn = new OleDbConnection(connstring))
            {
                upocn.Open();
                System.Data.DataTable dt = null;
                dt = upocn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

                using (OleDbDataAdapter upoda = new OleDbDataAdapter("select * from [" + dt.Rows[0]["TABLE_NAME"].ToString() + "]", upocn))
                {
                    DataSet upods = new DataSet();
                    error = string.Empty;

                    upoda.Fill(upods);

                    if (!string.IsNullOrEmpty(error))
                        return null;

                    return upods.Tables[0];
                }
            }
        }
        catch (Exception ex)
        {
            error = ex.Message;
        }

        return null;
    }
}

Answer 2

I had the same problem. Basically I had to put

    finally
{
    if (xlCmd != null)
    {
        xlCmd.Dispose();
        xlCmd = null;
    }
}

before initializing a new OleDBCommand xlCmd and

    finally
{
    if (xlCon != null)
        xlCon.Dispose();
}

after telling OleDBConnection xlCon to close. Make sure you also initialize your excel connection with a using block such as below:

    using (OleDbConnection xlCon = new OleDbConnection("Provider = Microsoft.ACE.OLEDB.12.0; " +
    "Data Source = " + xlFile + ";Mode=ReadWrite;" +
    "Extended Properties='Excel 12.0;HDR=YES;'"))   //Automatically creates a new excel file (Do not include IMEX=1)
{

These manual cleanups are required if you want to open your file while your application is running and you're done with your export.