How to update data in 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 .
Here we are using OleDbConnection , OleDbDataAdapter , DataSet for doing these operations in an Excel file. You have to import System.Data in the project for doing these operations . For update the content in the cell or modify the content in a cell , We can use the UPDATE command like in SQL Operations.
sample UPDATE sql
sql = "Update [Sheet1$] set name = 'New Name' where id=1"
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 = "Update [Sheet1$] set name = 'New Name' where id=1";
myCommand.CommandText = sql;
myCommand.ExecuteNonQuery();
MyConnection.Close();
}
catch (Exception ex)
{
MessageBox.Show (ex.ToString());
}
}
}
} |
|