read excel fiels using ado.net in Vb.net

Question

hi

i want to read an excel file using ado.net(if thre is soem other simple way,tht too) I want the code in VB.net

Thanks in advance

Answer 1

If you wish to use ADO, the data provider is available to connect to EXCEL file. Microsoft Jet is one of the best ones.

Otherwise, you can use EXCEL object itself for any other manipulation if needed.

Answer 2

You can see the following and think in that line:

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

using (DbConnection connection = factory.CreateConnection()) { connection.ConnectionString = connectionString;

using (DbCommand command = connection.CreateCommand())
{
    // Cities$ comes from the name of the worksheet
    command.CommandText = "SELECT ID,City,State
                                  FROM [Cities$]";

    connection.Open();

    using (DbDataReader dr = command.ExecuteReader())
    {
        while (dr.Read())
        {
            Debug.WriteLine(dr["ID"].ToString());
        }
    }
}

}