Reputation: 10481

C# read open Excel file through OleDb

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.

Upvotes: 4

Answers (4)

Pavan

Reputation: 11

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

Upvotes: 1

Boppity Bop

Reputation: 10481

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.

Upvotes: 2