How to Read Excel Sheet in C#

using System.IO;
using System.Data.OleDb;
 
private void openFileDialog1_FileOk(object sender, CancelEventArgs e)
        {
            string filePath = openFileDialog1.FileName;
            string extension = Path.GetExtension(filePath);
          
            string header = "NO";
            string conStr, sheetName;

            conStr = string.Empty;
            switch (extension)
            {

                case ".xls": //Excel 97-03
                    conStr = string.Format(Excel03ConString, filePath, header);
                    break;

                case ".xlsx": //Excel 07
                    conStr = string.Format(Excel07ConString, filePath, header);
                    break;
            }
            conStr = string.Format(Excel07ConString, filePath, header);
            //Get the name of the First Sheet.
            using (OleDbConnection con = new OleDbConnection(conStr))
            {
                using (OleDbCommand cmd = new OleDbCommand())
                {
                    cmd.Connection = con;
                    con.Open();
                    DataTable dtExcelSchema = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                    sheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
                    con.Close();
                }
            }

            //Read Data from the First Sheet.
            using (OleDbConnection con = new OleDbConnection(conStr))
            {
                using (OleDbCommand cmd = new OleDbCommand())
                {
                    using (OleDbDataAdapter oda = new OleDbDataAdapter())
                    {
                        DataTable dt = new DataTable();
                        cmd.CommandText = "SELECT * From [" + sheetName + "]";
                        cmd.Connection = con;
                        con.Open();
                        oda.SelectCommand = cmd;
                        oda.Fill(dt);
                        con.Close();

                        //Populate DataGridView.
                        dataGridView1.DataSource = dt;                       
                    }
                }
            }
        }

Share on facebook Google Twitter

Web Engineers here !


Grideb is the group of engineers who provide excellent solution for domain scenario with experienced, talented and high skilled engineers. We work hard to reduce complication of user interface and improve better quality of service.

-- Chief Executive Officer, GRIDEB SERVICES


www.grideb.com

Contact Us

GRIDEB SERVICES
Office-1,FF ,One Square Office Mall
Shaktikhand-III,Indirapuram,Ghaziabad
Uttar Pradesh 201004 (INDIA)
P: +91 (120) 260-6044
M: +91 90-1300-3421
Email ID
info@grideb.com




Grideb Services 2013-2014