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
}
}