Read Excel Data and Stored into Dataset - Asp.Net

Following Method to read data from excel sheet and stored into dataset

This method have one parameter which need to be passed full path of excel file.

For example

ReadExcels("c:\\mydata.xls");

Excel file may contain  .xls file  or .xlsx file.

For the connection string you can store in webconfig file.

WebConfig Setting

<add connectionstring="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=@@FILEPATH@@;Extended Properties=Excel 8.0" name="xls"/>
<add connectionstring="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=@@FILEPATH@@;Extended Properties=Excel 12.0" name="xlsx"/>

@@FILEPATH@@ replace your full excel file path. replace logic have already implement in the ReadExcels() method

Method

public DataSet ReadExcels(string XFilePath)
        {
            string connString = "";
            System.IO.FileInfo fi = new FileInfo(XFilePath);
            if (fi.Extension.ToLower().Replace(".","") == "xlsx")
                connString = ConfigurationManager.ConnectionStrings["xlsx"].ConnectionString;
            else
                connString = ConfigurationManager.ConnectionStrings["xls"].ConnectionString;

            connString = connString.Replace("@@FILEPATH@@", XFilePath);
            // Create the connection object
            OleDbConnection oledbConn = new OleDbConnection(connString);
            DataSet ds = null;
            OleDbConnection myConn = null;
            try
            {
                // Create OleDbCommand object and select data from worksheet Sheet1
                myConn = new OleDbConnection(connString);
                myConn.Open();

                //Getting WorkSheet In Excel File
                DataTable dtSchema = myConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                string TableName = dtSchema.Rows[0]["TABLE_NAME"].ToString();
                if (myConn.State == ConnectionState.Open)
                {
                    myConn.Close();
                }
                // Open connection
                oledbConn.Open();

                OleDbCommand cmd = new OleDbCommand("SELECT * FROM [" + TableName + "]", oledbConn);

                // Create new OleDbDataAdapter
                OleDbDataAdapter oleda = new OleDbDataAdapter();

                oleda.SelectCommand = cmd;

                // Create a DataSet which will hold the data extracted from the worksheet.
                ds = new DataSet();

                // Fill the DataSet from the data extracted from the worksheet.
                oleda.Fill(ds);
            }
            catch (Exception exc)
            {
                throw exc;
            }
            finally
            {
                if (myConn != null && myConn.State == ConnectionState.Open)
                {
                    myConn.Close();
                }
                oledbConn.Close();
            }
            return ds;
        }