locked
Excel doesn't close itself when using OleDbConnection's Open method RRS feed

  • Question

  • Hello,

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

    The original question thread comes from:

    http://stackoverflow.com/questions/8710510/c-sharp-excel-doesnt-close-itself-when-using-oledbconnections-open-method

    Someone on that forum could reproduce the problem and fixed it by not calling Close() method on

    OleDbConnection

    but for me it doesn't matter. Excel still resides in the memory when program quits.

    Could anyone give me a hint on this?

    Thanks!

    Tuesday, January 3, 2012 7:15 PM

Answers

  • For me is not clear what is still in memory. If it is your datatable, then this is correct.

    Your datatable lives in the Static Main stack, so as long as that exist the datatable will be in memory.

    Be aware that you have a little bit to much code.

    The DataAdatper opens and closes itself as long as you don't do that yourself.

    If the connection to the Excel sheet is not closed and therefore the sheet is unusable for the enduser then that is in my idea another problem.

    Be aware that for a dataadapter there is no need for an open and close, if you ommit that (so close direct after the schema read) then the dataadapter will do all those things itself.

    Also be aware that in this is often told to implement IDisposable, but that is implemented by a close of the connection and a datatable has no unmanaged resources so there is nothing to dispose (it will however stay unusable in memory).

     


    Success
    Cor
    • Proposed as answer by Dummy yoyo Monday, January 9, 2012 7:58 AM
    • Marked as answer by Dummy yoyo Thursday, January 12, 2012 2:33 AM
    Wednesday, January 4, 2012 1:32 PM

All replies

  • Take of look of implementing IDispose.

    http://msdn.microsoft.com/en-us/library/system.idisposable.aspx

    Or do you have another version of the driver to try?

    chanmm


    chanmm
    Wednesday, January 4, 2012 5:58 AM
  • This maybe an extremly bad workaround but as long as you haven't pinpointed the problem yet you could use the code.

     

    [DllImport("kernel32.dll", SetLastError = true)] 
    [return: MarshalAs(UnmanagedType.Bool)] 
    private static extern bool CloseHandle(IntPtr hObject); 
    
    public static bool KillExcelInstance(ref Excel.Application app) {
           try {            
                 IntPtr handle = (IntPtr)app.Hinstance;            
                 app.Quit(); app = null;            
                 return CloseHandle(handle);       
           }       
           catch(Exception e) {            
                 throw new Exception(MethodBase.GetCurrentMethod() + " " + e.Message);           
                 return false;       
           } 
    }

     

    • Edited by StukovCaino Wednesday, January 4, 2012 7:23 AM again... formating... forum does not like firefox?
    Wednesday, January 4, 2012 7:11 AM
  • Take of look of implementing IDispose.

    http://msdn.microsoft.com/en-us/library/system.idisposable.aspx

    Or do you have another version of the driver to try?

    chanmm


    chanmm

    What do you suggest? Am not I freeing something correctly?

    In the modified version of code I also used "using" for OleDbConnection just to ensure

    everything is free. But with no luck.

    What kind of driver may I use to fetch data from Excel?

     

     

      
    Wednesday, January 4, 2012 7:18 AM
  • Yep, This is some kind of a solution, and probably I will use it, but Just I would like to know

    what and why it is happening.

    Wednesday, January 4, 2012 7:20 AM
  • I don't know. It's Excel and COM, an evil combination.

    COM will not free the resources if one of them are still in use. And Excel will not Quit if COM is still using resources. Excel does not like to be passed around as parameter. But in this case:

    Are you sure the finally part is executed correctly?

     

    And second: You are using OleDB to open an Excelsheet as a Database. Why do you need Excel Interop?

    Wednesday, January 4, 2012 7:36 AM
  • Well If I remove the part:

     ImportDataTableFromExcelIMEX 

    then, Excel is freed from the memory.

    The block:

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

    actually gets rid of the excel from task manager.

    I know that every reference to the com interface must be explicitly released and

    I am trying to stick to this. This obscure situation only happens if I fetch data in the middle of the COM operations.

    If i Fetch data before using com interoperability then everything is all right.


    • Edited by Xor2 Wednesday, January 4, 2012 8:05 AM
    Wednesday, January 4, 2012 8:04 AM
  • That is very wierd.

    Maybe try this to find out which Method leaves the Excel in the background running:

    create a new project without excel interop references

    add only the OleDB part and execute it...

    Is there an Excel instance in the background?

     

     

    why I'm asking you this is that Excel has a bad habit of just "hijacking" an open Excel instance instead of creating a new one if it is not specified to create a new instance.

    I think that maybe this is what happend here. The Workbook might be still "open" in the background. try disposing the datatable (if it has a dispose method, can't remember).

    upocn should be also closed and nulled... putting it in the final clause doesnt do anything... because of the return in the try clause.

     

    • Edited by StukovCaino Wednesday, January 4, 2012 8:16 AM
    Wednesday, January 4, 2012 8:08 AM
  • With only OleDB part excel is not in the background.

    Excel is still there if I use it during com interoperability operations.

    I have also tried disposing table, no luck...

     

    Wednesday, January 4, 2012 10:29 AM
  • perhaps i got your point. in the past, i dev'ed a project and export data via Excel. User will find there are many excel processes on their machine so that they cannot open and close excel. But my temporary way to handle this issue is just to kill the Excel process as follows.
                finally
                {
                    foreach (var v in Process.GetProcessesByName("EXCEL"))
                    {
                        v.Kill();
                    }
                }
    

    Hope this helps.

    Werewolf,


    Just a newbie for everything.
    Wednesday, January 4, 2012 1:03 PM
  • For me is not clear what is still in memory. If it is your datatable, then this is correct.

    Your datatable lives in the Static Main stack, so as long as that exist the datatable will be in memory.

    Be aware that you have a little bit to much code.

    The DataAdatper opens and closes itself as long as you don't do that yourself.

    If the connection to the Excel sheet is not closed and therefore the sheet is unusable for the enduser then that is in my idea another problem.

    Be aware that for a dataadapter there is no need for an open and close, if you ommit that (so close direct after the schema read) then the dataadapter will do all those things itself.

    Also be aware that in this is often told to implement IDisposable, but that is implemented by a close of the connection and a datatable has no unmanaged resources so there is nothing to dispose (it will however stay unusable in memory).

     


    Success
    Cor
    • Proposed as answer by Dummy yoyo Monday, January 9, 2012 7:58 AM
    • Marked as answer by Dummy yoyo Thursday, January 12, 2012 2:33 AM
    Wednesday, January 4, 2012 1:32 PM
  • Sorry... But this is very very bad... You are basically killing every Excel Instance, even those that has nothing to do with your program.
    Wednesday, January 4, 2012 2:28 PM
  • But you know this is very very wierd... Not even logical. But it's Excel interop :D

    Well then I think you have to read the table first via OleDB thhen open Excel. Is the better solution than the Process kill.

    Wednesday, January 4, 2012 2:34 PM
  • Did you have a try to encapsulate excel data operations into one method and used GC.Collect() method after this method and it worked. Just like:

    ExcelBusiness.BSExcel.CreateExcel(filePath);
    GC.Collect();
    
    Werewolf,


    Just a newbie for everything.
    Wednesday, January 4, 2012 3:39 PM
  • I've read somewhere about encapsulating each excel's method in a c# method, but I don't know how it could help...

    Anyway, If I create excel Application - for instance - can I return excel's com interface reference just as a return type according to your metodology?

    Something like: Application CreateExcel(params) {} ?

     

     

     

     

    Wednesday, January 4, 2012 10:06 PM