Read/Write Data in Excel using OleDb (C#)

There are many ways of read/write data in Excel file. This post shows a simple example of using System.Data.OleDb in C#. One of advantages of using this method is no need to have Office in client machine.

The code snippet below shows how to connect to Excel using OleDB, how to select, update and insert data. There is DELETE operation here since it is not supported by OLEDB provider.

private void Run()
{
   string szConn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\temp\test.xls;Extended Properties='Excel 8.0;HDR=No'";
   OleDbConnection conn = new OleDbConnection(szConn);
   conn.Open();

   // Select
   OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Sheet1$]", conn);
   OleDbDataAdapter adpt = new OleDbDataAdapter(cmd);
   DataSet ds = new DataSet();
   adpt.Fill(ds);

   foreach (DataRow dr in ds.Tables[0].Rows)
   {
      string data = string.Format("F1:{0}, F2:{1}, F3:{2}", dr[0], dr[1], dr[2]);
      MessageBox.Show(data);
   }
         
   // Update
   cmd = new OleDbCommand("UPDATE [Sheet1$] SET F2='Hello' WHERE F1='a'", conn); 
   cmd.ExecuteNonQuery();

   cmd = new OleDbCommand("UPDATE [Sheet1$A2:C2] SET F2='World'", conn);
   cmd.ExecuteNonQuery();

   // Insert
   cmd = new OleDbCommand("INSERT INTO [Sheet1$](F1,F2,F3) VALUES ('A3','B3','C3')", conn);
   cmd.ExecuteNonQuery();

   conn.Close();
}

Before:

After:

For connection string, HDR=Yes is default value and it specifies the first row of Excel is used for Column name. Having header in first row is typical case but if you do not want to use HDR, you have to specify HDR=No explicitly.

The Table(?) name in Excel is sheet name and its syntax is "[sheetName$]"
For example, [Sheet1$] is first sheet 'Sheet1' in the Excel file. In Excel, you can specify specific region by adding "range" after $ sign. That is, [Sheet1$A1:C1] represents first 3 columns (A to C) in first row (1).

Select/Update/Delete syntax is pretty much the same as other data store. One thing to note is that if HDR is NO, the column names are automatically generated and so F1 means first field, F2 means 2nd field, and so on. If HDR is used, you can use the same column name as you define in first header row.