Exception thrown when opening a Excel spreedsheet from C# stating file is being used by another user.

Question

I am currently developing an application that requires an excel spreadsheet, location selected by the user, to be read into a DataTable and then stored in a sql server database.

The application works perfectly in my development environment, however when it is deployed into my production environment an exception is thrown with the following message.

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

My code to read the excel file is as follows:

OleDbConnection objConn = null;

DataSet objDataset1 = null;

string fileLocation = GetFileLocation();

string sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;"
+ "Data Source=" + fileLocation
+ ";" + "Extended Properties=Excel 8.0;";

objConn = new OleDbConnection(sConnectionString);

objConn.Open(); //This is where the exception is thrown

OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM [Sheet1$]", objConn);

OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();

objAdapter1.SelectCommand = objCmdSelect;

objDataset1 = new DataSet();

objAdapter1.Fill(objDataset1, "XLData");

DataTable dt = objDataset1.Tables["XLData"];

Note the application and excel file are on different servers in the same domain.

Digging around various forumns and knowledge bases it would appear that the exception is thrown when the "user" does not have permission to use the file. Although not recommended permissions on the file have been set to Full Access for all users.

Apart from file permissions what else could cause this exception to be thrown?

Answer 1

Not sure if this is helpful but...

The error:

The Microsoft Jet database engine cannot open the file '.xls'.

seems to point to a file named .xls, as opposed to something named, say myspreadsheet.xls or prodfile.xls.

It may be a long shot, but can you do some debugging to make sure that the file Jet is trying to open actually exists? It may be that the filename is not being constructed properly, for some reason.

Answer 2

Here's a few answers to the same root problem:

http://stackoverflow.com/questions/849602/read-from-excel-using-oledb-in-a-windows-service