-
November 30th, 2008, 12:03 PM
#1
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());
}
}
}
-
December 1st, 2008, 03:31 PM
#2
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
-
December 1st, 2008, 03:47 PM
#3
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
-
December 1st, 2008, 05:08 PM
#4
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 05: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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|