与xls文件紧密连接的问题 [英] Problem with close connection to xls file

问题描述

大家好，

我使用OleDb来访问xls和xlsx文件.我在Excel文件中写了很多信息.当我关闭与此文件的连接时，我的程序已访问该文件.程序释放句柄到文件需要花费几分钟.如何立即生效?

Hi All,

I used OleDb to get access to xls and xlsx file. I wrote a lot of information to Excel file. When I''am closing connection to this file, my program has acces to file. It takes a couple of minutes when program release handle to file. How I can get immediate effect?

string sqlCommandText;

//czytanie plików Excel''a z wykorzystaniem OLEDB
OleDbConnection con;
int i = 0;

workSheetNames = new String[] { };

if (isOpenXMLFormat) //read 2007 file
    connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
        GeneratedFile + ";Mode=ReadWrite;Extended Properties=\"Excel 8.0;HDR=YES;\"";
else                //read 97-2003 file
    connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
        GeneratedFile + ";Mode=ReadWrite;Extended Properties=Excel 8.0;";

try
{
    con = new OleDbConnection(connectionString);
    con.Open();
}
catch (Exception e)
{
    return;
}

//pobiera wszystkie dostepne arkusze
DataTable dataSet = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

//pobiera nazwy arkuszy
workSheetNames = new String[dataSet.Rows.Count];
i = 0;
foreach (DataRow row in dataSet.Rows)
{
    //umieszcza nazwy arkuszy w tablicy i usuwa znaki $
    workSheetNames[i] = row["TABLE_NAME"].ToString().Trim(new[] { ''$'' });
    i++;
}
i = 0;

OleDbCommand insertCmd = null;
int j = 0;
try
{
    try
    {
        i = 0;
        foreach (DataRow dataRow in dt.Rows)
        {
            if (i > 3)
            {
                if (i % 100 == 0)
                {
                    con.Close();

                    while(FileInUse(GeneratedFile))
                        System.Threading.Thread.Sleep(250);
                }
                if (con.State == ConnectionState.Closed)
                    con.Open();

                sqlCommandText = "Insert INTO [" + workSheetNames[0] + "$](Nazwa, Telefon, IP, Grupa, Wlasciciel, TelnetPort) values (?, ?, ?, ?, ?, ?)";

                insertCmd = new OleDbCommand(sqlCommandText, con);
                OleDbParameter name = new OleDbParameter("@nazwa", OleDbType.VarChar, 50);
                name.Value = dataRow[1];
                insertCmd.Parameters.Add(name);
                OleDbParameter telefon = new OleDbParameter("@telefon", OleDbType.VarChar, 50);
                telefon.Value = dataRow[4];
                insertCmd.Parameters.Add(telefon);
                OleDbParameter ip = new OleDbParameter("@ip", OleDbType.VarChar, 20);
                ip.Value = dataRow[5];
                insertCmd.Parameters.Add(ip);
                OleDbParameter grupa = new OleDbParameter("@grupa", OleDbType.BSTR, 20);
                grupa.Value = tbAddGroup.Text;
                insertCmd.Parameters.Add(grupa);
                OleDbParameter wlasciciel = new OleDbParameter("@wlasciciel", OleDbType.BSTR, 20);
                wlasciciel.Value = tbAddOwner.Text;
                insertCmd.Parameters.Add(wlasciciel);
                OleDbParameter telnetPort = new OleDbParameter("@telnetport", OleDbType.BSTR, 20);
                if (String.IsNullOrEmpty(tbAddTelnetPort.Text))
                    telnetPort.Value = DBNull.Value;
                else
                    telnetPort.Value = tbAddTelnetPort.Text;
                insertCmd.Parameters.Add(telnetPort);

                insertCmd.CommandTimeout = 100;
                insertCmd.ExecuteNonQuery();
                Trace.WriteLine(con.State.ToString());

                if (i == dt.Rows.Count - 1 && con.State == ConnectionState.Open)
                {
                    con.Close();
                    System.Threading.Thread.Sleep(1000);
                }
            }
            i++;

            (sender as BackgroundWorker).ReportProgress(j++ * 100 / dt.Rows.Count);
        }

        if (con != null)
        {
            con.Close();
            con.Dispose();
        }

    }
    catch (OleDbException e)
    {
        MessageBox.Show(e.ToString());
    }
    catch (NullReferenceException e)
    {
        MessageBox.Show(e.ToString());
    }
    finally
    {
        // the closing of the reader is done in the BaseReportGenerator
        if (insertCmd != null)
            insertCmd.Dispose();

        if (con != null)
        {
            con.Close();
            con.Dispose();
        }

        if (dataSet != null)
            dataSet.Dispose();

        GC.Collect(GC.GetGeneration(con));
        GC.WaitForPendingFinalizers();

    }
}
catch (OleDbException ex)
{
    MessageBox.Show(ex.ToString());
}
catch (Exception ex)
{
    MessageBox.Show(ex.ToString());
}

请帮帮我.

在此先感谢.

Please help me.

Thanks in Advance.

推荐答案

workSheetNames [i] = row ["TABLE_NAME"].ToString().Trim(new [] {''

workSheetNames[i] = row["TABLE_NAME"].ToString().Trim(new[] { ''

''}); i ++; } i = 0; OleDbCommand insertCmd = null; int j = 0; 尝试 { 尝试 { i = 0; foreach(dt.Rows中的DataRow dataRow) { 如果(i> 3) { 如果(i％100 == 0) { con.Close(); while(FileInUse(GeneratedFile)) System.Threading.Thread.Sleep(250); } 如果(con.State == ConnectionState.Closed) con.Open(); sqlCommandText =插入INTO [" + workSheetNames [0] +"

'' }); i++; } i = 0; OleDbCommand insertCmd = null; int j = 0; try { try { i = 0; foreach (DataRow dataRow in dt.Rows) { if (i > 3) { if (i % 100 == 0) { con.Close(); while(FileInUse(GeneratedFile)) System.Threading.Thread.Sleep(250); } if (con.State == ConnectionState.Closed) con.Open(); sqlCommandText = "Insert INTO [" + workSheetNames[0] + "

(Nazwa，Telefon，IP，Grupa，Wlasciciel，TelnetPort)值(?，?，?，?，?， ?); insertCmd =新的OleDbCommand(sqlCommandText，con); OleDbParameter名称=新的OleDbParameter("@ nazwa"，OleDbType.VarChar，50); name.Value = dataRow [1]; insertCmd.Parameters.Add(name); OleDbParameter telefon =新的OleDbParameter("@ telefon"，OleDbType.VarChar，50); telefon.Value = dataRow [4]; insertCmd.Parameters.Add(telefon); OleDbParameter ip =新的OleDbParameter("@ ip"，OleDbType.VarChar，20); ip.Value = dataRow [5]; insertCmd.Parameters.Add(ip); OleDbParameter grupa =新的OleDbParameter("@ grupa"，OleDbType.BSTR，20); grupa.Value = tbAddGroup.Text; insertCmd.Parameters.Add(grupa); OleDbParameter wlasciciel =新的OleDbParameter("@ wlasciciel"，OleDbType.BSTR，20); wlasciciel.Value = tbAddOwner.Text; insertCmd.Parameters.Add(wlasciciel); OleDbParameter telnetPort =新的OleDbParameter("@ telnetport"，OleDbType.BSTR，20); 如果(String.IsNullOrEmpty(tbAddTelnetPort.Text)) telnetPort.Value = DBNull.Value; 别的 telnetPort.Value = tbAddTelnetPort.Text; insertCmd.Parameters.Add(telnetPort); insertCmd.CommandTimeout = 100; insertCmd.ExecuteNonQuery(); Trace.WriteLine(con.State.ToString()); 如果(i == dt.Rows.Count-1&&con.State == ConnectionState.Open) { con.Close(); System.Threading.Thread.Sleep(1000); } } i ++; (发送者为BackgroundWorker).ReportProgress(j ++ * 100/dt.Rows.Count); } 如果(con！= null) { con.Close(); con.Dispose(); } } 捕获(OleDbException e) { MessageBox.Show(e.ToString()); } 捕获(NullReferenceException e) { MessageBox.Show(e.ToString()); } 最后 { //阅读器的关闭是在BaseReportGenerator中完成的 如果(insertCmd！= null) insertCmd.Dispose(); 如果(con！= null) { con.Close(); con.Dispose(); } 如果(dataSet！= null) dataSet.Dispose(); GC.Collect(GC.GetGeneration(con)); GC.WaitForPendingFinalizers(); } } 抓(OleDbException ex) { MessageBox.Show(ex.ToString()); } 抓住(前例外) { MessageBox.Show(ex.ToString()); }

(Nazwa, Telefon, IP, Grupa, Wlasciciel, TelnetPort) values (?, ?, ?, ?, ?, ?)"; insertCmd = new OleDbCommand(sqlCommandText, con); OleDbParameter name = new OleDbParameter("@nazwa", OleDbType.VarChar, 50); name.Value = dataRow[1]; insertCmd.Parameters.Add(name); OleDbParameter telefon = new OleDbParameter("@telefon", OleDbType.VarChar, 50); telefon.Value = dataRow[4]; insertCmd.Parameters.Add(telefon); OleDbParameter ip = new OleDbParameter("@ip", OleDbType.VarChar, 20); ip.Value = dataRow[5]; insertCmd.Parameters.Add(ip); OleDbParameter grupa = new OleDbParameter("@grupa", OleDbType.BSTR, 20); grupa.Value = tbAddGroup.Text; insertCmd.Parameters.Add(grupa); OleDbParameter wlasciciel = new OleDbParameter("@wlasciciel", OleDbType.BSTR, 20); wlasciciel.Value = tbAddOwner.Text; insertCmd.Parameters.Add(wlasciciel); OleDbParameter telnetPort = new OleDbParameter("@telnetport", OleDbType.BSTR, 20); if (String.IsNullOrEmpty(tbAddTelnetPort.Text)) telnetPort.Value = DBNull.Value; else telnetPort.Value = tbAddTelnetPort.Text; insertCmd.Parameters.Add(telnetPort); insertCmd.CommandTimeout = 100; insertCmd.ExecuteNonQuery(); Trace.WriteLine(con.State.ToString()); if (i == dt.Rows.Count - 1 && con.State == ConnectionState.Open) { con.Close(); System.Threading.Thread.Sleep(1000); } } i++; (sender as BackgroundWorker).ReportProgress(j++ * 100 / dt.Rows.Count); } if (con != null) { con.Close(); con.Dispose(); } } catch (OleDbException e) { MessageBox.Show(e.ToString()); } catch (NullReferenceException e) { MessageBox.Show(e.ToString()); } finally { // the closing of the reader is done in the BaseReportGenerator if (insertCmd != null) insertCmd.Dispose(); if (con != null) { con.Close(); con.Dispose(); } if (dataSet != null) dataSet.Dispose(); GC.Collect(GC.GetGeneration(con)); GC.WaitForPendingFinalizers(); } } catch (OleDbException ex) { MessageBox.Show(ex.ToString()); } catch (Exception ex) { MessageBox.Show(ex.ToString()); }

请帮帮我.

在此先感谢.

Please help me.

Thanks in Advance.

这篇关于与xls文件紧密连接的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！