Csharp Not able to read excel using oledb

Question

I was trying to read an Excel file from an .aspx page using Visual Studio localhost. I kept the Excel in the root folder, but when trying to read it, it shows the following exception:

The Microsoft Office 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 oledb code

string connString = ConfigurationManager.ConnectionStrings["xlsx"].ConnectionString;
OleDbConnection oledbConn = new OleDbConnection(connString);
try
{
    // Open connection
    oledbConn.Open();

    // Create OleDbCommand object and select data from worksheet Sheet1
    OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Sheet1$]", oledbConn);

    // Create new OleDbDataAdapter
    OleDbDataAdapter oleda = new OleDbDataAdapter();

    oleda.SelectCommand = cmd;

    // Create a DataSet which will hold the data extracted from the worksheet.
    DataSet ds = new DataSet();

    // Fill the DataSet from the data extracted from the worksheet.
    oleda.Fill(ds, "Employees");

    // Bind the data to the GridView
    GridView1.DataSource = ds.Tables[0].DefaultView;
    GridView1.DataBind();
}
catch(Exception ex)
{
    throw ex;
}
finally
{
    // Close connection
    oledbConn.Close();
}

connection string

<connectionStrings>
    <add name="xls" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=ExcelToParse.xls;Extended Properties=Excel 8.0"/>
    <add name="xlsx" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=ExcelToParse.xlsx;Extended Properties=Excel 12.0"/>
</connectionStrings>

Can somebody point what I am doing wrong here?

Smells like a user permission issue. Can you try running as an admin user? Also, if memory serves, the OLEDB stuff might run in a different process/user space... which would obviate my first notion haha ... but those two areas are where I'd look — Malachi
Why don't you use the mighty NPOI library? It works even when you don't have installed excel on your machine... — Unhandled exception

Souvik Ghosh Souvik Ghosh · Accepted Answer · 2017-03-17T06:59:57

First run this command in command prompt to kill all open Excel processes.

taskkill /f /im excel.exe

You need to dispose the connection object. Then change your code in your finally block to-

finally
{
    // Close connection
    oledbConn.Close();
    oledbConn.Dispose();
    System.Runtime.InteropServices.Marshal.ReleaseComObject(oledbConn);
}

Please let me know if it works.

Csharp Not able to read excel using oledb

1 Answers