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.

Format Specifier in C#

By and large there are two type of format specifiers in C# / .NET world : Standard Format Specifier and Custom Format Specifier. This post summarizes frequently used format specifiers.

The general syntax for output formatter does look like this:

{n, w:tp}
      n: argument number
      w: width
      t: data type
      p: precision

      ex)  string.Format("{0,9:N2}", v);

The below format specifiers are frequently used by developers.

Standard Format Specifiers for Numeric Type

(Case insensitive)
C : Currency
      string.Format("{0:C}", 1234567) => $1,234,567
D : Decimal
      string.Format("{0:D7}", 123456) => 0123456
E : Scientific
      string.Format("{0:E}", 12345.6) => 1.23456E+004
F : Fixed point
      string.Format("{0:F3}",12345.6) => 12345.600
N : Number
      string.Format("{0:N2}",1234.567)=> 1,234.57
X : Hexadecimal
      string.Format("{0:X}", 255) => FF

Standard Format Specifiers for DateTime Type

d : Short date
     string.Format("{0:d}", today) => 11/21/2011
D : Long date
     string.Format("{0:D}", today) => Firday, July 5, 2010
t : Short time
     string.Format("{0:t}", today) => 4:05 PM
T : Long time
     string.Format("{0:T}", today) => 4:05:55 PM
g : General datetime (short time)
     string.Format("{0:g}", today) => Firday, July 5, 20104:05:55 PM
G : General datetime
     string.Format("{0:g}", today) => Firday, July 5, 20104:05:55 PM

Custom Format Specifiers for Numeric Type

# : Digit placeholder (no leading zeros)
0 : Zero placeholder (w/ leading zeros)
. : Decimal point
, : Thousands operator
; : Section separator

ex)
   val = 12345;
   string.Format("{0:#,##0;(#,##0)}", val)
   => 12,345

   val = -12345;
   string.Format("{0:#,##0;(#,##0)}", val)
   => (12,345)

   val = 0;
   string.Format("{0:#,##0;(#,##0);Zero}", val)
   => Zero

Custom Format Specifiers for DateTime Type

M   : Month. Single digit for <10
MM  : Two digit month
MMM : Abbreviated name of the month
d   : Day. Single digit
dd  : Two digit day
ddd : Abbreviated name of the day
yy  : Two digit year 
yyyy: Four digit year
h   : Hour (12hr)
hh  : Two digit hour (12hr)
H / HH : Hour (24 hr)
m   : Minute
mm  : Two digit minute
s   : Second
ss  : Two digit second
tt  : AM / PM

ex) string.Format("{0:yyyy/MM/dd}", DateTime.Today) => 2011/11/22

How to check MouseDown event for all controls in WinForm

When a Form contains several child controls and nested UserControls, mouse click event is not propagated to parent form in WinForm. If one uses WPF, there is no need to add your code since WPF basically supports RoutedEvents. However, WinForm does not support Routed Events, so you have to manually implements the appropriate mechanism.

One way of doing is using Application.AddMessageFilter() method which basically add custom message filter to the application. Your custom MessageFilter should implement IMessageFilter interface which only has one method - IMessageFilter.PreFilterMessage(). In this method, you determine whether you want to filter out the message or you can add your custom code for specific message.

Here is an example of using MessageFilter. To check whether any control in the Form is clicked, I added a static property but this is only for simplified demonstration purpose, more options are available.

using System;
using System.Windows.Forms;
namespace WindowsFormsApplication2
{
   static class Program
   {
      [STAThread]
      static void Main()
      {
         Application.EnableVisualStyles();
         Application.SetCompatibleTextRenderingDefault(false);

         // Add Message Filter to precheck MouseDown
         IMessageFilter myFilter = new MyMessageFilter();
         Application.AddMessageFilter(myFilter);

         Application.Run(new Form1());
      }
   }

   public class MyMessageFilter : IMessageFilter
   {      
      public bool PreFilterMessage(ref Message m)
      {
         if (m.Msg == 0x201) //WM_LBUTTONDOWN
         {
            Form1.FormClicked = true; 
         }
         return false; // do not filter
      }
   }
}

namespace WindowsFormsApplication2
{
   public partial class Form1 : Form
   {            
      public static bool FormClicked {get; internal set;}      
      public Form1()
      {
         InitializeComponent();
      }
   }
}