Reading Excel in .NET, how to get row number?

Question

Hi,

I have got this code to read an Excel file using C# .NET (from http://davidhayden.com/blog/dave/archive/2006/05/26/2973.aspx)

string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Book1.xls;Extended Properties=""Excel 8.0;HDR=YES;""";
DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");
DbCommand command = connection.CreateCommand()
command.CommandText = "SELECT City,State FROM [Cities$]";


I want to select only 10 rows starting from the 4th row, how can I do that?

Thanks in advance.

Answer 1

I'm no Excel querying expert, but this certainly works. Use TOP to limit the query to 13 rows. The first row is the header with column names so it may not count. Obviously change if I misunderstand. Then, keep track of a row ID and do stuff to rows on or after 4.

Hope this helps!

string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Book1.xls;Extended Properties=""Excel 8.0;HDR=YES;""";
using (System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(connectionString)) {
    System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand();
    cmd.Connection = conn;
    cmd.CommandText = "SELECT top 13 City,State FROM [Cities$]";

    conn.Open();
    System.Data.IDataReader dr = cmd.ExecuteReader();

    int row = 2;
    while (dr.Read()) {
        if (row++ >= 4) {
            // do stuff
            Console.WriteLine("{0}, {1}", dr[0], dr[1]);
        }
    }
}