C# read open Excel file through OleDb

Question

I need to connect to an open Excel 2003 file using .NET 3.5

It seems the OleDb connection which I am trying to use wants the file exclusively. But I need to have this file open in Excel in the same time.

Is non-locking reading possible?

EDIT: I resolved this by copying file before opening it.

score 2 · Accepted Answer · answered Jun 01 '11 at 09:26

2

the question seems have no answer. and I cant delete it....

my solution was - run macro on timer to save the excel file in question and C# app was copying the file to another one and reading it using OleDb.

answered Jun 01 '11 at 09:26

Boppity Bop

9,613
13
72
151

score 1 · Answer 2 · edited May 23 '17 at 11:58

1

This seems like a similar problem: Writing into excel file with OLEDB

Does that work out for you?

edited May 23 '17 at 11:58

Community

1
1

answered Apr 18 '11 at 09:57

Kristoffer Lindvall

2,674
1
18
27

no that guy cant write in a standalone file. i cant read from file which is already opened somewhere else. – Boppity Bop Apr 18 '11 at 10:58

score 1 · Answer 3 · answered Apr 18 '11 at 14:30

1

What parameters are you passing in when you open the Excel document? Could you set the "ReadOnly" parameter in Workbook.Open() to true? See here.

answered Apr 18 '11 at 14:30

dotNetkow

5,053
4
35
50

I tried to set Mode in OleDb connection string to Read and to Share Deny None and neither worked. – Boppity Bop Apr 19 '11 at 13:44
Ok, first - are you able to open the document as per my instructions above? If so, can you post the OleDb connection code that you're using? The more info, the better. – dotNetkow Apr 19 '11 at 14:46
cnnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + this.path + ";Mode=Share Deny None;Extended Properties=\"Excel 8.0;HDR=YES\""; <<< if the document is already opened in the Excel then I get exception in the C# code. Also tried Mode=Read. – Boppity Bop Apr 19 '11 at 15:02
Are you using the full path of the file in "this.path"? See [this](http://webcache.googleusercontent.com/search?q=cache:nwpk-LYkqc4J:social.msdn.microsoft.com/Forums/fi-FI/exceldev/thread/7a46aa61-2a62-4a7a-a722-4a39b480638c+excel+microsoft+jet+database+engine+cannot+open+the+file&cd=3&hl=en&ct=clnk&gl=us&source=www.google.com) similar question. What is the exact exception that you get? – dotNetkow Apr 19 '11 at 15:32

score 1 · Answer 4 · edited Nov 17 '12 at 03:30

Refer to the code below how to get the information of Excel data into an array. Then you will perform any validations on that Excel sheet.

var fileName = @"D:\Pavan\WorkDiployed.xlsx";
var connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0; data source={0}; Extended Properties=Excel 12.0;", fileName);
OleDbConnection con = new System.Data.OleDb.OleDbConnection(connectionString);
OleDbDataAdapter cmd = new System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", con);

con.Open();
System.Data.DataSet excelDataSet = new DataSet();
cmd.Fill(excelDataSet);
DataTable data = excelDataSet.Tables[0];

DataRow[] arrdata = data.Select();

foreach (DataRow rw in arrdata)
{
    object[] cval = rw.ItemArray;
}           

con.Close();
MessageBox.Show (excelDataSet.Tables[0].ToString ());

C# read open Excel file through OleDb

4 Answers4

Linked