Search This Blog

Monday, September 8, 2014

Select/Insert/Update Excel 2007 File using c# in .net

//insert data into excel sheet
private void btnSave_Click(object sender, EventArgs e)
{
using (OleDbConnection excelConnection = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\TEMP\\Testing.xlsx;Extended Properties='Excel 8.0;HDR=YES;'"))
{
excelConnection.Open();

OleDbCommand command = new OleDbCommand("INSERT INTO [Sheet1$] ([Id],[Name]) VALUES(@value1, @value2)", excelConnection);
command.Connection = excelConnection;

command.Parameters.AddWithValue("@value1", txtId.Text);
command.Parameters.AddWithValue("@value2", txtName.Text);

command.ExecuteNonQuery();
}

btnGetData_Click(null, null);
}

//get data from excel sheet
private void btnGetData_Click(object sender, EventArgs e)
{
using (OleDbConnection excelConnection = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\TEMP\\Testing.xlsx;Extended Properties='Excel 8.0;HDR=YES;'"))
{
excelConnection.Open();
OleDbDataAdapter adapter = new OleDbDataAdapter("select Id, Name from [Sheet1$]", excelConnection);
DataSet ds= new DataSet();
adapter.Fill(ds);

dgvExcelData.DataSource = ds.Tables[0];
}
}

//update data of excel sheet
private void btnUpdate_Click(object sender, EventArgs e)
{
using (OleDbConnection excelConnection = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\TEMP\\Testing.xlsx;Extended Properties='Excel 8.0;HDR=YES;'"))
{
excelConnection.Open();

string sqlQuery = "update [Sheet1$] set [Name] = '" + txtName.Text + "' where [Id] = " + txtId.Text;

OleDbCommand command = new OleDbCommand(sqlQuery, excelConnection);
command.Connection = excelConnection;


command.ExecuteNonQuery();
}
}



Note: If the "id" column is numeric:

If the "id" column in the sheet is of "General" type, the Sql statement is:

UPDATE [Sheet1$] SET Name ='Nameddd' WHERE id=1

If the "id" column in the sheet is of "Text" type, the Sql statement is
UPDATE [Sheet1$] SET Name ='Nameddd' WHERE id="1"

Popular Posts