CODEWITHSUNDEEP
c#-2.0oledbexcel-2003
Kanini
Kanini

Reputation: 1993

Reading Excel using C# and OleDB - How to identify the used range?

I am reading an Excel file using good old OleDBConnections and OleDBCommands. How do I identify the used range of the excel sheet?

For example, if I were to use InterOp, I can always do this - 

Excel.Application xlApp = new Excel.Application();
Excel.Workbook xWb;
Excel.Worksheet xWs;
Excel.Range range;

xWb = xlApp.Workbooks.Open(@"D:\Technical\C#\WorkingFolder\HelloWorld.xls", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
xWs = (Excel.Worksheet)xWb.Worksheets.get_Item(1);
range = xWs.UsedRange;

Now, I get a list of all the used columns. With OleDB, how do I get it. Now, even if I were to nail down the column list, how will I know that the rows end here.

Basically, my requirement is this.

I am about to receive an Excel (2003) file. The data will always include Headers (i.e., column name). It does not mean that the data will always be populated from A1:J200

It can also be from A5:J204 too.

So, in using OleDBCommand

string sConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Technical\C#\WorkingFolder\HelloWorld.xls;Extended Properties=""Excel 8.0;HDR=YES;""";
string sCommandString = "SELECT Employee_Id, First_Name FROM [Sheet1$A1:J200]";
OleDbConnection xlConnection = new OleDbConnection(sConnectionString);
xlConnection.Open();
OleDbCommand xlCommand = new OleDbCommand(sCommandString, xlConnection);
OleDbDataAdapter tempDataAdapter = new OleDbDataAdapter();
tempDataAdapter.SelectCommand = xlCommand;
DataTable tempDataTable = new DataTable("Whatever_I_Want");
tempDataAdapter.Fill(tempDataTable);

Now, the line

sCommandString has the hardcoded value of A1:J200, what if the excel I receive has the data range as A5:204, what will I do?

Any help would be great!

Upvotes: 2

Views: 3865

Answers (1)

Hans Olsson
Hans Olsson

Reputation: 54999

Untried but I think you could do it by first creating a DataAdapter with a SELECT * FROM [Sheet] statement and then use that in a call to FillSchema after which you should be able to call .Columns.Count on the DataTable you just filled.

Upvotes: 2

Related Questions