Free Programming Tutorials & Source Code

A sample code shows how to read data from an Excel file using C#. It uses OledbConnection to create a connection to the excel file, and then fill data to a DataSet.

 
//Namespace requires
using System.Data.OleDb;

..and the code:

 
       protected void ReadfromExcel()
       {
 
          string filePath = "c:\\Test.xls";
         //Create a connection to the excel file
          OleDbConnection cn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=Excel 8.0");
         // choose source to read from
          string TableName = "Employees";
          cn.Open();
           try
           {
               DataSet dsEmployees = new DataSet();
               // select from the source
                OleDbDataAdapter cmd = new OleDbDataAdapter("select * from [" + TableName + "$]", cn);
               //Fill into the DataSet
                cmd.Fill(dsEmployees);
                cn.Close();
                string toString = "";
                // Loop through the DataSet
                foreach (DataRow drEmployees in dsEmployees.Tables[0].Rows)
                {
                  // Copy to an array
                   Object[] cells = drEmployees.ItemArray;
                   foreach (Object cellData in cells)
                   {     
                      // Copy the data to a string
                      toString = cellData.ToString();
                   }
                }
           }
           catch (Exception ex)
           {
               MessageBox.Show(ex.Message);
           }


       }