Excel not closing when using OleDBAdapter
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 4 of 4

Thread: Excel not closing when using OleDBAdapter

  1. #1
    Join Date
    Nov 2008
    Posts
    0

    Excel not closing when using OleDBAdapter

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

  2. #2
    Join Date
    Mar 2005
    Location
    Vienna, Austria
    Posts
    4,537

    Re: Excel not closing when using OleDBAdapter

    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.
    Jonny Poet

    To be Alive is depending on the willingsness to help others and also to permit others to help you. So lets be alive. !
    Using Code Tags makes the difference: Code is easier to read, so its easier to help. Do it like this: [CODE] Put Your Code here [/code]
    If anyone felt he has got help, show it in rating the post.
    Also dont forget to set a post which is fully answered to 'resolved'. For more details look to FAQ's about Forum Usage. BTW I'm using Framework 3.5 and you ?
    My latest articles :
    Creating a Dockable Panel-Controlmanager Using C#, Part 1 | Part 2 | Part 3 | Part 4 | Part 5 | Part 6 | Part 7

  3. #3
    Join Date
    Mar 2005
    Location
    Vienna, Austria
    Posts
    4,537

    Re: Excel not closing when using OleDBAdapter

    Code:
    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
    Jonny Poet

    To be Alive is depending on the willingsness to help others and also to permit others to help you. So lets be alive. !
    Using Code Tags makes the difference: Code is easier to read, so its easier to help. Do it like this: [CODE] Put Your Code here [/code]
    If anyone felt he has got help, show it in rating the post.
    Also dont forget to set a post which is fully answered to 'resolved'. For more details look to FAQ's about Forum Usage. BTW I'm using Framework 3.5 and you ?
    My latest articles :
    Creating a Dockable Panel-Controlmanager Using C#, Part 1 | Part 2 | Part 3 | Part 4 | Part 5 | Part 6 | Part 7

  4. #4
    Join Date
    Mar 2005
    Location
    Vienna, Austria
    Posts
    4,537

    Re: Excel not closing when using OleDBAdapter

    Hi !
    You obviously need to insert
    Code:
    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
    Code:
    Marshal.ReleaseComObject(masterws);
    Marshal.ReleaseComObject(masterWorkbook);
    Marshal.ReleaseComObject(masterExcelApp);
    GC.Collect();
    GC.WaitForPendingFinalizers(); 
    GC.Collect();
    GC.WaitForPendingFinalizers(); 
    I did instead
    Code:
    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
    Code:
    
    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 !
    Last edited by JonnyPoet; December 1st, 2008 at 04:43 PM.
    Jonny Poet

    To be Alive is depending on the willingsness to help others and also to permit others to help you. So lets be alive. !
    Using Code Tags makes the difference: Code is easier to read, so its easier to help. Do it like this: [CODE] Put Your Code here [/code]
    If anyone felt he has got help, show it in rating the post.
    Also dont forget to set a post which is fully answered to 'resolved'. For more details look to FAQ's about Forum Usage. BTW I'm using Framework 3.5 and you ?
    My latest articles :
    Creating a Dockable Panel-Controlmanager Using C#, Part 1 | Part 2 | Part 3 | Part 4 | Part 5 | Part 6 | Part 7

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Azure Activities Information Page

Windows Mobile Development Center


Click Here to Expand Forum to Full Width

This is a CodeGuru survey question.


Featured


HTML5 Development Center