Read an excel file using c#.net

This article will help to Read a Excel file. OleDb Managed Data Provider in the .NET Framework to read an Excel Spreadsheet using ADO.NET and C# just like you would with a database.

string connectionString = @"The conectionsttring";


DbProviderFactory factory =   DbProviderFactories.GetFactory("System.Data.OleDb");

DbDataAdapter adapter = factory.CreateDataAdapter();

DbCommand selectCommand = factory.CreateCommand();
selectCommand.CommandText = "SELECT ID,City,State FROM [table]";

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

selectCommand.Connection = connection;

adapter.SelectCommand = selectCommand;

DataSet cities = new DataSet();

adapter.Fill(cities);

grid.SetDataBinding(cities.Tables[0], "");
grid.RetrieveStructure();
 

Inserting a Row into Excel Using ADO.NET:
 

DbProviderFactory factory =   DbProviderFactories.GetFactory("System.Data.OleDb");

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

    using (DbCommand command = connection.CreateCommand())
    {
        command.CommandText = "INSERT INTO [table] (ID, City, State) VALUES(4,\"Tampa\",\"Florida\")";

        connection.Open();

        command.ExecuteNonQuery();
    }
}
 

Updating Excel Using ADO.NET

DbProviderFactory factory =   DbProviderFactories.GetFactory("System.Data.OleDb");

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

    using (DbCommand command = connection.CreateCommand())
    {
        command.CommandText = "Update [table] Set City =\"Venice\" WHERE ID = 1";

        connection.Open();

        command.ExecuteNonQuery();
    }
}