C#: Read from excel file

I used .NET 2.0 framework. Reading from excel file is depends on excel's version, not entirely but it will affect your source code little bit.

Here is little tip about reading excel file.

string mFileName; // file name which you will read from.
System.Data.OleDb.OleDbConnection connection;
string mSheetName; // excel file consists of many sheets / pages.
System.Data.OleDb.OleDbDataAdapter dtAdapter;

Reading from excel file is as same as reading from database, so that you will need connection string which helps you to connect your program to excel sheets, as well as dataAdapter which will retrieve data from excel sheet.

This function accepts 2 arguments which are describe excel file's name, and it's version.
If you are using excel 2007 then send true as a second argument, or else send false when you call this function.

public void setConnectionString(string FileName, bool IsExcel2007)
{
    try
    {
        mFileName = FileName;
        string connstr = "";
        if (IsExcel2007) //IsExcel2007 indicates that excel's version.
         connstr = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + mFileName +  
";Extended Properties=Excel 12.0";
        else
         connstr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + mFileName + "; Jet OLEDB:Engine Type=5;" + "Extended Properties=\"Excel 4.0;Imex=1; HDR=YES; FOOTER=NO;\"";
        connection = new System.Data.OleDb.OleDbConnection(connstr);
     }
     catch (Exception ex)
     { }
}

Creates connection string for you, depends on which version you are using.

Once you created connection, now you will have to read data from excel sheet using dataAdapter as shown below.

public System.Data.DataSet readFile(string SheetName)
{
    try
    {
        mSheetName = SheetName;
        connection.Open();
        DataSet ds = new DataSet();
        dtAdapter = new System.Data.OleDb.OleDbDataAdapter("SELECT * FROM [" + mSheetName + 
"$]", connection);
        dtAdapter.Fill(ds);
        connection.Close();
        return ds;
     }
     catch (Exception ex)
     {
        throw ex;
     }
}

Creates empty dataset called ds, and retrieves all data from excel sheet based on your connection, and put that data into ds. Now you have your data within ds dataset.
Since our function supposed to return dataset, we should return ds as a result.

You will find more detailed explanation here:
DataSet[^] and DataAdapter[^]

dtAdapter = new System.Data.OleDb.OleDbDataAdapter("SELECT * FROM [" + mSheetName + "$]", connection);

As you can see, this line of code will retrieve all data from excel sheet based on your connection.

Now using these 2 methods...

private void button1_Click(object sender, EventArgs e)
{
    DataSet ds1;
    string firstColumn, secondColumn;
    try
    {
        setConnectionString("excelFileName.xls", false);
          // i used excel 2003 that is why second argument is false. If you are using 2007 send true instead of false
          // and you can read any excel file.
        ds1 = readFile("excelSheetName");
//send name of a sheet which you want to read
        for (int i = 0; i < ds1.Tables[0].Rows.Count; i++)
        {
            firstColumn = ds1.Tables[0].Rows[i][0].ToString();
            secondColumn = ds1.Tables[0].Rows[i][1].ToString();
 
this.richTextBox1.AppendText(firstColumn);
            this.richTextBox1.AppendText("\t");
            this.richTextBox1.AppendText(secondColumn);
            this.richTextBox1.AppendText("\n");
            this.label_totalNumOfRows.Text = Convert.ToString(ds1.Tables[0].Rows.Count);
         }
     }
     catch (Exception ex)
     {
         throw ex;
     }
}

As we discussed before readFile function returns dataset along with its data and we are passing that dataset into another dataset, called ds1. Now we have every thing inside ds1.
In our case, i just read first two columns and put it in richTextBox.

firstColumn = ds1.Tables[0].Rows[i][0].ToString();
secondColumn = ds1.Tables[0].Rows[i][1].ToString();

Result would looks like this:
name1            age1
name2            age2
name3            age3
... etc.

Now put all codes together.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.IO;

namespace readExcel
{
    public partial class Form1 : Form
    {
        string mFileName, mSheetName;
System.Data.OleDb.OleDbConnection connection;
System.Data.OleDb.OleDbDataAdapter dtAdapter;

        public void setConnectionString(string FileName, bool IsExcel2007)
{
           try
           {
             mFileName = FileName;
             string connstr = "";
             if (IsExcel2007) //IsExcel2007 indicates that excel's version.
               connstr = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + mFileName + 
";Extended Properties=Excel 12.0";
             else
              connstr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + mFileName + ";    Jet OLEDB:Engine Type=5;" + "Extended Properties=\"Excel 4.0;Imex=1; HDR=YES; FOOTER=NO;\"";
        

             connection = new System.Data.OleDb.OleDbConnection(connstr);
           }
           catch (Exception ex)
           { }
}

        public System.Data.DataSet readFile(string SheetName)
{
           try
           {
              mSheetName = SheetName;
              connection.Open();
        DataSet ds = new DataSet();
              dtAdapter = new System.Data.OleDb.OleDbDataAdapter("SELECT * FROM [" + mSheetName + "$]", connection);
              dtAdapter.Fill(ds);
              connection.Close();
              return ds;
           }
           catch (Exception ex)
           {
              throw ex;
           }
}

        private void button1_Click(object sender, EventArgs e)
{
           DataSet ds1;
    string firstColumn, secondColumn;
           try
    {
              setConnectionString("excelFileName.xsl", false);
                // i used excel 2003 that is why second argument is false. If you are using version 2007, send true instead of false.
                // and you can read any excel file.
              ds1 = readFile("excelSheetName");
//send name of a sheet which you want to read from excel
              for (int i = 0; i < ds1.Tables[0].Rows.Count; i++)
              {
            firstColumn = ds1.Tables[0].Rows[i][0].ToString();
                secondColumn = ds1.Tables[0].Rows[i][1].ToString();
 
this.richTextBox1.AppendText(firstColumn);
            this.richTextBox1.AppendText("\t");
                this.richTextBox1.AppendText(secondColumn);
                this.richTextBox1.AppendText("\n");
                this.label_totalNumOfRows.Text = Convert.ToString(ds1.Tables[0].Rows.Count);
              }
            }
            catch (Exception ex)
            {
              throw ex;
            }
}
}   
}

Good luck :)