How to read Excel file using .Net

Recently while working on a reporting application, one of the tasks was to read and update an Excel file using .Net code. There are couple of approaches you can use to do it. If you are looking for a well defined Excel API to accomplish the tasks then you will have to use Excel Object model that gets installed on your machine when you install Microsoft Office. But this is not an option when you are running your application from a server where installing Microsoft Excel may not be an option. In that case, you can use ADO.Net objects to accomplish the tasks. What you need to is simple add reference to System.Data.OleDb in your project and you are good to go. Following code snippet shows how you can read an excel file. This code just iterates over all rows that it can find in a given worksheet. In subsequent posts I will demonstrate different CRUD operations that can be performed using ADO.Net on excel file.

class Program
{
 static void Main(string[] args)
 {
    int columnCount = 3;
    var excelFileConn = new OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;
       Data Source='Data_20101213.xls';Extended Properties=Excel 8.0;");
    excelFileConn.Open();
    try
    {
     var cmd = new OleDbCommand("select * from [12-13-2010$]", excelFileConn);
     var da = new OleDbDataAdapter(cmd);
     var dt = new DataTable();
     da.Fill(dt);
     if (dt.Rows.Count != 0)
     {
        foreach (DataRow dr in dt.Rows)
        {
          Console.WriteLine("{0}\t{1}\t{2}", dr[0], dr[1], dr[2]);
        }
      }
     }
     finally
     {
       if (null != excelFileConn &&
           excelFileConn.State == ConnectionState.Open)
       {
          excelFileConn.Close();
       }
      }
    }
comments powered by Disqus

Blog Tags