I am assuming that you already have an Excel file with one “Sheet1” data table in it. Microsoft ACE driver is already installed;
Standard syntax to query Excel table is “SELECT * FROM [Sheet1$]”. Here is the code that does the magic.
// Connect EXCEL sheet with OLEDB using connection string
using (OleDbConnection conn = new OleDbConnection(connectionString))
{
conn.Open();
OleDbDataAdapter objDA = new System.Data.OleDb.OleDbDataAdapter
("select * from [Sheet1$]", conn);
DataSet excelDataSet = new DataSet();
objDA.Fill(excelDataSet);
dataGridView1.DataSource = excelDataSet.Tables[0];
}
//In above code '[Sheet1$]' is the first sheet name with '$' as default selector,
// with the help of data adaptor we can load records in dataset
//write data in EXCEL sheet (Insert data)
using (OleDbConnection conn = new OleDbConnection(connectionString))
{
try
{
conn.Open();
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = conn;
cmd.CommandText = @"Insert into [Sheet1$] (month,mango,apple,orange)
VALUES ('DEC','40','60','80');";
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
//exception here
}
finally
{
conn.Close();
conn.Dispose();
}
}
//update data in EXCEL sheet (update data)
using (OleDbConnection conn = new OleDbConnection(connectionString))
{
try
{
conn.Open();
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = conn;
cmd.CommandText = "UPDATE [Sheet1$] SET month = 'DEC' WHERE apple = 74;";
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
//exception here
}
finally
{
conn.Close();
conn.Dispose();
}
}
$ in select statement means that table already exists in Excel File. If we are going to create a new worksheet then we will not use $ sign. OLEDB does not support DELETE query.
Resource;
https://www.codingame.com/playgrounds/9014/read-write-excel-file-with-oledb-in-c-without-interop