How to insert data to Excel file using OLEDB
In the previous examples we used Microsoft Excel 12.0 Object Library for read or write to and Excel file . In C# without using Excel Object we can insert , edit , delete , select etc. in cell content of an Excel file using OLEDB .
You have to import System.Data in the project for doing these operations . For add new content in the cell or insert a new content , We can use the INSERT command like in SQL Operations.
sample UPDATE sql
sql = "Insert into [Sheet1$] (id,name) values('5','e')"
The following picture shows before and after update of the Sheet.
using System;
using System.Drawing;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;
namespace WindowsApplication1
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
try
{
System.Data.OleDb.OleDbConnection MyConnection ;
System.Data.OleDb.OleDbCommand myCommand = new System.Data.OleDb.OleDbCommand();
string sql = null;
MyConnection = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='c:\\csharp.net-informations.xls';Extended Properties=Excel 8.0;");
MyConnection.Open();
myCommand.Connection = MyConnection;
sql = "Insert into [Sheet1$] (id,name) values('5','e')";
myCommand.CommandText = sql;
myCommand.ExecuteNonQuery();
MyConnection.Close();
}
catch (Exception ex)
{
MessageBox.Show (ex.ToString());
}
}
}
} |
|