Read Excel File into DataSet in ASP.NET Using C#

Introduction

In this example I will explain how to read Excel file data into a DataSet in ASP.NET using C#.

I have created an Excel file which contains the Slno, FirstName, LastName and Location which is shown below.

We would be putting all the data into the grid view and with the help of filter with the dropdown list box, we would filter the Slno and display accordingly, else display all the cell values in the grid.

ASP:

<asp:DropDownList ID="ddlSlno" runat="server" OnSelectedIndexChanged="ddlSlno_SelectedIndexChanged"
        AutoPostBack="true" AppendDataBoundItems="True">
<asp:ListItem Selected="True" Value="Select">- Select -</asp:ListItem>
</asp:DropDownList>
<asp:GridView ID="grvData" runat="server">
</asp:GridView>

C# code:

OleDbConnection oledbConn;
protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        GenerateExcelData("Select");
    }
}
 
protected void ddlSlno_SelectedIndexChanged(object sender, EventArgs e)
{
    GenerateExcelData(ddlSlno.SelectedValue);
}
 
private void GenerateExcelData(string SlnoAbbreviation)
{
    // need to pass relative path after deploying on server
    string path = System.IO.Path.GetFullPath(@"C:\InformationNew.xls");
 
    /* connection string  to work with excel file. HDR=Yes - indicates 
       that the first row contains columnnames, not data. HDR=No - indicates 
       the opposite. "IMEX=1;" tells the driver to always read "intermixed" 
       (numbers, dates, strings etc) data columns as text. 
    Note that this option might affect excel sheet write access negative. */
 
    oledbConn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + 
      path + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1;';");
    oledbConn.Open();
    OleDbCommand cmd = new OleDbCommand(); ;
    OleDbDataAdapter oleda = new OleDbDataAdapter();
    DataSet ds = new DataSet();
 
    // selecting distict list of Slno 
    cmd.Connection = oledbConn;
    cmd.CommandType = CommandType.Text;
    cmd.CommandText = "SELECT distinct([Slno]) FROM [Sheet1$]";
    oleda = new OleDbDataAdapter(cmd);
    oleda.Fill(ds, "dsSlno");
 
    // passing list of states to drop-down list
    ddlSlno.DataSource = ds.Tables["dsSlno"].DefaultView;
    ddlSlno.DataTextField = "Slno";
    ddlSlno.DataValueField = "Slno";
    ddlSlno.DataBind();
 
    // by default we will show form data for all states but if any state is selected then show data accordingly
    if (!String.IsNullOrEmpty(SlnoAbbreviation) && SlnoAbbreviation != "Select")
    {
        cmd.CommandText = "SELECT [Slno],[FirstName],[LastName],[Location]" + 
            "  FROM [Sheet1$] where [Slno]= @Slno_Abbreviation";
                        
        cmd.Parameters.AddWithValue("Slno_Abbreviation", SlnoAbbreviation);
    }
    else
    {
        cmd.CommandText = "SELECT [Slno],[FirstName],[LastName],[Location] FROM [Sheet1$]";
    }
    oleda = new OleDbDataAdapter(cmd);
    oleda.Fill(ds);
 
     // binding form data with grid view
     grvData.DataSource = ds.Tables[0].DefaultView;
     grvData.DataBind();
}
// need to catch possible exceptions
catch (Exception ex)
{}
finally
{
    oledbConn.Close();
}
}// close of method GemerateExceLData