Read Excel file and save data in List using C# in ASP.Net

Hi fr3eddy,

Check this example. Now please take its reference and correct your code.

HTML

<asp:FileUpload ID="FileUpload1" runat="server" />
<asp:Button ID="btnUpload" runat="server" Text="Upload" OnClick="btnUpload_Click" />

Namespaces

C#

using System.IO;
using System.Configuration;
using System.Data.OleDb;
using System.Data;

Code

C#

protected void btnUpload_Click(object sender, EventArgs e)
{
    if (FileUpload1.HasFile)
    {
        string FileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
        string Extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
        string FolderPath = ConfigurationManager.AppSettings["FolderPath"];
        string FilePath = Server.MapPath(FolderPath + FileName);
        FileUpload1.SaveAs(FilePath);
        Import_To_Grid(FilePath, Extension, "Yes");
    }
}

private void Import_To_Grid(string FilePath, string Extension, string isHDR)
{
    string conStr = "";
    switch (Extension)
    {
        case ".xls": 
            conStr = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
            break;
        case ".xlsx":
            conStr = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
            break;
    }

    conStr = String.Format(conStr, FilePath, isHDR);
    OleDbConnection connExcel = new OleDbConnection(conStr);
    OleDbCommand cmdExcel = new OleDbCommand();
    OleDbDataAdapter oda = new OleDbDataAdapter();
    DataTable dt = new DataTable();
    cmdExcel.Connection = connExcel;
    connExcel.Open();
    DataTable dtExcelSchema;
    dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
    string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
    connExcel.Close();
    connExcel.Open();
    cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";
    oda.SelectCommand = cmdExcel;
    oda.Fill(dt);
    connExcel.Close();
    List<DataRow> list = dt.Select().ToList();
}

Connection String

Web.config

<connectionStrings>
  <add name ="Excel03ConString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}; Extended Properties='Excel 8.0;HDR={1}'"/>
  <add name ="Excel07ConString" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0}; Extended Properties='Excel 8.0;HDR={1}'"/>
</connectionStrings>
<appSettings>
  <add key ="FolderPath" value ="Files/"/>
</appSettings >

Screenshot