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.

Answer 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.

Answer 2

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 ()); 

Answer 3

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

Does that work out for you?

Answer 4

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.