Inserting Records in Excel Sheet using OLEDB Connection

Inserting Records in Excel Sheet using OLEDB Connection:

Its Very Easy to Insert Record in Excel Just Follow the Steps

as Given below.

Create OleDbConnection

Pass Connection string

Create & fire command Object

and Close The connection

public void FillExcel()

{

//Step1: Get File

string path = Path.Combine(Server.MapPath(“~/MyFolder/”),“My-Template.xls”);

string fileExtension = System.IO.Path.GetExtension(path);

//Connection String

string country=”India”;

string state=”MS”;

string city=”Nagpur”;

string ConnStr = “Provider=Microsoft.Jet.OLEDB.4.0;” +

“Data Source=” + path +

“;Extended Properties=\”Excel 8.0;HDR=Yes;IMEX=2\”;”;

//Insert statement

string query = string.Format(“INSERT INTO [Sheet1$] (CountryName,State,City)

values (‘” + country + “‘,'” + state + “‘,'”+city+“‘);”);

// Step 2: Create Connection

try

{

OleDbConnection objConnection = null;

if (fileExtension == “.xls”)

{

objConnection = newOleDbConnection(ConnStr);

}

//Step 3:Open connection

objConnection.Open();

//Step 4:fire Command

OleDbCommand nonQueryCommand = newOleDbCommand(query);

nonQueryCommand.Connection = objConnection;

nonQueryCommand.CommandText = query;

nonQueryCommand.ExecuteNonQuery();

//Step 5:Close connection

objConnection.Close();

}

catch (Exception ex)

{

//handle exception here

}

}