Excel file - It is already opened exclusively by a

问题:

I am reading the excel file using C# and below is the code which is working as expected EXCEPT that every time i run the app, I have to close the excel file otherwise I get the below error message:

The Microsoft Access database engine cannot open or write to the file ''. It is already opened exclusively by another user, or you need permission to view and write its data..

my question is: is there a way i close the excel file once i am done reading?

public static  DataTable LoadExcelWorkbook(string workbookName)
        {
            OleDbConnection connection;

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

            using(OleDbConnection conn = new OleDbConnection(connectionString))
            {
                connection = new OleDbConnection(connectionString);
                connection.Open();

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

                DataTable myTable = dataSet.Tables[0];

                dataAdapter.Dispose();
                connection.Close();
                dataSet.Dispose();            

                //CLOSE THE EXCEL FILE?????????

                if (myTable != null)
                    return myTable;

                return null; 
            } 
        }

回答1:

use sheet1 name instead of workbook name