Reading Excel in .NET, how to get specific rows?
I have the following code here to read an Excel file using C # .NET:
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 only want to select 10 rows starting from the 4th row, how can I do that?
+1
source to share
1 answer
I'm not an Excel expert, but it certainly works. Use TOP to limit your query to 13 rows. The first line is a header with column names, so it cannot be read. Obviously change if I misunderstood. Then keep an eye on the line id and do stuff in lines 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]);
}
}
}
+1
source to share