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.

tring 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();
}
}