CODEWITHSUNDEEP
excel-2007
Nick Kahn
Nick Kahn

Reputation: 20078

Closing Excel file after reading

Here is my code for opening the Excel file and reading the data, everything is working fine but what I would like is to close once the Excel file is read, how would i do that? I try Dispose the object but did not help.

public static DataTable ExcelWorkbook(string workbookName)
        {
            string connectionString = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=YES;IMEX=1;""", FILENAME);
            string query = String.Format("select * from [{0}$]", workbookName);

            OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, connectionString);
            DataSet dataSet = new DataSet();
            dataAdapter.Fill(dataSet);

            dataAdapter.Dispose();

            DataTable myTable = dataSet.Tables[0];
            if (myTable != null)
                return myTable;

            return null;
        }

Upvotes: 1

Views: 1918

Answers (2)

Lemonade
Lemonade

Reputation: 503

Your code should look sth like that: 

OleDbConnection connection;
OleDbDataAdapter clientsAdapter new OleDbDataAdapter();
DataSet myDataSet = new DataSet();

connectionString = (@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0}.xlsx;Extended Properties=""Excel 12.0 Xml;HDR=YES""",FILENAME);
connection = new OleDbConnection(connectionString);
connection.Open();

clientsAdapter.SelectCommand = new OleDbCommand("SELECT * FROM [{0}$]", connection);

DataTable data = new DataTable("MyTable");
clientsAdapter.Fill(data);
myDataSet.Tables.Add(data);

connection.Close();

After the connection is closed, the excel file will be unlocked.

Upvotes: 1

Eric J.
Eric J.

Reputation: 150118

You are disposing the data adapter that read the data, not the reference to the Excel file itself.

Somewhere in your code, you will have opened the workbook. You need to call

workbook.Close();

http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.workbookclass.close(v=office.14).aspx

Upvotes: 0

Related Questions