Closing Excel file after reading

Question

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

Lemonade · Accepted Answer · 2012-09-25 22:25:53Z

1

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.

answered Sep 25, 2012 at 22:25

Lemonade

5032 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Nick Kahn Over a year ago

after i implement your code and closing the connection, my excel file is still open.

Lemonade Over a year ago

i'm not sure how your whole program operates. you should use a "clean" project to evaluate the code. it might take a couple of seconds for the lock to be removed though.

Nick Kahn Over a year ago

i dont see where you have a line where it says closing the excel object? i have tried creating a fresh new project but it does not close.. after i run the code like reading the excel file i still see that my excel is open in read-only mode.

Eric J. · Accepted Answer · 2012-09-25 21:55:18Z

0

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

answered Sep 25, 2012 at 21:55

Eric J.

151k65 gold badges353 silver badges563 bronze badges

1 Comment

Nick Kahn Over a year ago

i am not using workbook i am just opening the excel file and reading and returning the DataTable

Collectives™ on Stack Overflow

Closing Excel file after reading

2 Answers 2

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related