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



OleDbConnection objConnection = null;

if (fileExtension == “.xls”)


objConnection = newOleDbConnection(ConnStr);


//Step 3:Open connection


//Step 4:fire Command

OleDbCommand nonQueryCommand = newOleDbCommand(query);

nonQueryCommand.Connection = objConnection;

nonQueryCommand.CommandText = query;


//Step 5:Close connection



catch (Exception ex)


//handle exception here




Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s