Read Data From Excel files in C#.Net

Read Data From Excel files in C#.Net:

The below sample code will help you to get data from excel sheet. You can use same code in both Winforms and web applications.  Different type of provider available to read different type of the excel version. Here I have given two type of provider to read excel sheet. One is old file format XLS excel file reader (Excel 2007) and another one is latest file format of XLSX file reader (Excel 2010).

using System.Data.OleDb;

 //Reading from XLS file (Office 2007 and before)

        private void button1_Click(object sender, EventArgs e)

        {

            try

            {

                OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\FileName.xls;Extended Properties='Excel 8.0;HDR=Yes;'");

                OleDbDataAdapter da = new OleDbDataAdapter("select * from [Sheet1$]", con);

                DataSet ds = new DataSet();

                da.Fill(ds);

            }

            catch (Exception ex)

            {

                MessageBox.Show(ex.Message);

            }

        }

        //Reading from XLSX file (Office 2007 & After)

        private void button2_Click(object sender, EventArgs e)

        {

            try

            {

                OleDbConnection con = new OleDbConnection(@"Provider= Microsoft.ACE.OLEDB.12.0;Data Source=D:\FileName.xlsx; Extended Properties='Excel 12.0;HDR=YES;'"); 

                OleDbDataAdapter da = new OleDbDataAdapter("select * from [Sheet1$]", con);

                DataSet ds = new DataSet();

                da.Fill(ds);

            }

            catch (Exception ex)

            {

                MessageBox.Show(ex.Message);

            }

        }

The above given example of reading my local hard drives excel sheets file first page data to my C# data set. If you want to change sheet you can specify the select statement [Sheet2$].

Thanks to for giving this example  Mr.Antony.