使用的OleDbConnection的Open方法时,C#Excel不会关闭本身 [英] C# Excel doesn't close itself when using OleDbConnection's Open method
问题描述
我创建和释放引用到Excel COM接口来操纵Excel的工作表。
在这种情况下,Excel中正确关闭本身。
。如果我使用 OleDbDataAdapter的
连接来获取数据,然后Excel是仍然在内存中。
我有阅读关于这个问题几乎一切。
- 我创建子程序适当放开引用。
- 我现在用:
<预类=郎-CS prettyprint-覆盖>
GC.Collect的();
GC.WaitForPendingFinalizers();
我还能做什么?
这似乎是模糊的问题。
下面是代码:
命名空间ExcelTestCode
{
类节目
{
静态无效的主要(字串[] args)
{
应用的Excel = NULL;
工作簿簿= NULL;
工作表工作表= NULL;
对象oMissing = Missing.Value;
练成=新的应用程序{可见= FALSE};
=工作簿excel.Workbooks.Open(@C:\temp.xls,0,假,5,,,
真,XlPlatform.xlWindows,\t ,假的,假的,0,真的,真的,oMissing);
=工作表(表)workbook.Sheets [1];
试
{
串STRERROR =;
System.Data.DataTable dtTable = NULL;
//如果我删除以下行,一切都还好吧
dtTable = ImportDataTableFromExcelIMEX(@C:\temp.xls,出STRERROR);
}
终于
{
如果
{
对Marshal.ReleaseComObject(表)(工作表!= NULL);
表= NULL;
}
如果
{
workbook.Close(假,oMissing,oMissing)(练习册!= NULL);
对Marshal.ReleaseComObject(练习册);
工作簿= NULL;
}
如果(练成!= NULL)
{
excel.Quit();
对Marshal.ReleaseComObject(EXCEL);
的Excel = NULL;
}
GC.Collect的();
GC.WaitForPendingFinalizers();
GC.Collect的();
}
}
公共静态System.Data.DataTable ImportDataTableFromExcelIMEX(字符串文件名,出字符串错误)
{
串CONNSTRING = @供应商= Microsoft.Jet.OLEDB.4.0;数据源=+文件名+ @;扩展属性=的Excel 8.0; HDR =是; IMEX = 1
OleDbConnection的upocn =新的OleDbConnection(CONNSTRING);
试
{
upocn.Open();
System.Data.DataTable DT = NULL;
DT = upocn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,NULL);使用
(OleDbDataAdapter的upoda =新OleDbDataAdapter的(SELECT * FROM [+ dt.Rows [0] [TABLE_NAME]。的ToString()+],upocn))
{
的DataSet upods =新的DataSet();
错误=的String.Empty;
upoda.Fill(upods);
如果(string.IsNullOrEmpty(错误)!)
返回NULL;
返回upods.Tables [0];
}
}
赶上(异常前)
{
错误= ex.Message;
}
终于
{
upocn.Close();
upocn = NULL;
}
返回NULL;
}
}
}
尝试使用(OleDbConnection的upocn =新的OleDbConnection(的connectionString)),或致电upocn.Dispose()
从MSDN OleDbConnection.Dispose :释放由System.ComponentModel.Component使用的所有资源。
OleDbConnection.close :关闭与数据源的连接
更新我可以产生这个问题如果我做了Connection.close像上面的代码,但是当我打电话处置它工作得很好,我没有看到Excel中的任何实例。下面是对我的作品的代码。确保您清除从任务管理器在测试之前运行的实例
类节目
{
静态无效的主要(字串[] args)
{
应用练成= NULL;
工作簿簿= NULL;
工作表工作表= NULL;
对象oMissing = Missing.Value;
练成=新的应用程序{可见= FALSE};
=工作簿excel.Workbooks.Open(@C:\Book1.xls,0,假,5,,,
真,XlPlatform.xlWindows,\t ,假的,假的,0,真的,真的,oMissing);
=工作表(表)workbook.Sheets [1];
试
{
串STRERROR =;
System.Data.DataTable dtTable = NULL;
//如果我删除以下行,一切都还好吧
dtTable = ImportDataTableFromExcelIMEX(@C:\Book1.xls,出STRERROR);
}
终于
{
如果
{
对Marshal.ReleaseComObject(表)(工作表!= NULL);
表= NULL;
}
如果
{
workbook.Close(假,oMissing,oMissing)(练习册!= NULL);
对Marshal.ReleaseComObject(练习册);
工作簿= NULL;
}
如果(练成!= NULL)
{
excel.Quit();
对Marshal.ReleaseComObject(EXCEL);
的Excel = NULL;
}
GC.Collect的();
GC.WaitForPendingFinalizers();
GC.Collect的();
}
}
公共静态System.Data.DataTable ImportDataTableFromExcelIMEX(字符串文件名,出字符串错误)
{
串CONNSTRING = @供应商= Microsoft.Jet.OLEDB.4.0;数据源=+文件名+ @;扩展属性=的Excel 8.0; HDR =是; IMEX = 1
试
{使用(OleDbConnection的upocn =新的OleDbConnection(CONNSTRING))
{
upocn.Open()
;
System.Data.DataTable DT = NULL;
DT = upocn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,NULL);使用
(OleDbDataAdapter的upoda =新OleDbDataAdapter的(SELECT * FROM [+ dt.Rows [0] [TABLE_NAME]。的ToString()+],upocn))
{
的DataSet upods =新的DataSet();
错误=的String.Empty;
upoda.Fill(upods);
如果(string.IsNullOrEmpty(错误)!)
返回NULL;
返回upods.Tables [0];
}
}
}
赶上(异常前)
{
错误= ex.Message;
}
返回NULL;
}
}
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.
- I have created subroutines to appropriate release references.
- 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;
}
}
}
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;
}
}
这篇关于使用的OleDbConnection的Open方法时,C#Excel不会关闭本身的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!