How to Read Excel File in Data Set Using ASP.Net

Introduction

This article explains how to read an Excel file into a Data Set Using ASP.NET.

 

Step 1

 

First of all, open a new Excel Sheet and enter the information that you want to add.

 

Now start Visual Studio and create a Web Application.

 

Right-click on this application and add the Excel File to this application.

 

Step 2

 

Now you need to add a Drop Down List, a List Item, a Label, and a Grid View to your application.

<asp:DropDownList ID="dropdown1" runat="server" OnSelectedIndexChanged="ddlSlno_SelectedIndexChanged"
    AutoPostBack="true" AppendDataBoundItems="True">
    <asp:ListItem Selected="True" Value="Choose">- Choose -</asp:ListItem>
</asp:DropDownList>
<asp:GridView ID="Grid1" runat="server">
</asp:GridView>
<asp:Label ID="lbl1" runat="server" />

Step 3

 

Now add this code in the selected index change of the drop-down list:

GenerateExcelData(dropdown1.SelectedValue);

For the GenerateExcelData method you need to add this code:

private void GenerateExcelData(string SlnoAbbreviation)
{
    try
    {
        string read = System.IO.Path.GetFullPath(Server.MapPath("~/empdetail.xlsx"));

        if (Path.GetExtension(read) == ".xls")
        {
            x = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + read + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"");
        }
        else if (Path.GetExtension(read) == ".xlsx")
        {
            x = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + read + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1;';");
        }
        x.Open();
        OleDbCommand y = new OleDbCommand();
        OleDbDataAdapter z = new OleDbDataAdapter();
        DataSet dset = new DataSet();
        y.Connection = x;
        y.CommandType = CommandType.Text;
        y.CommandText = "SELECT distinct([Slno]) FROM [Sheet1$]";
        z = new OleDbDataAdapter(y);
        z.Fill(dset, "Slno");
        dropdown1.DataSource = dset.Tables["Slno"].DefaultView;
        if (!IsPostBack)
        {
            dropdown1.DataTextField = "Slno";
            dropdown1.DataValueField = "Slno";
            dropdown1.DataBind();
        }
        if (!String.IsNullOrEmpty(SlnoAbbreviation) && SlnoAbbreviation != "Choose")
        {
            y.CommandText = "SELECT [Slno], [EmpName], [Salaray], [Location]" +
                "  FROM [Sheet1$] where [Slno]= @Slno_Abbreviation";
            y.Parameters.AddWithValue("@Slno_Abbreviation", SlnoAbbreviation);
        }
        else
        {
            y.CommandText = "SELECT [Slno],[EmpName],[Salary],[Location] FROM [Sheet1$]";
        }
        z = new OleDbDataAdapter(y);
        z.Fill(dset);

        Grid1.DataSource = dset.Tables[1].DefaultView;
        Grid1.DataBind();
    }
    catch (Exception ex)
    {
        lbl1.Text = ex.ToString();
    }
    finally
    {
        x.Close();
    }
}

This code will check both types of files to determine whether it's a .xls file or a .xlsx file.

 

After that I provided the connection for the Excel Sheet, in the starting it will show all the data but after that, it will fetch the data according to a Serial Number provided by you.

 

Step 4

 

Its complete code will be like this:

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

namespace ReadExcelInToDataSet
{
    public partial class Default : System.Web.UI.Page
    {
        OleDbConnection x;
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                GenerateExcelData("Choose");
            }
        }

        protected void ddlSlno_SelectedIndexChanged(object sender, EventArgs e)
        {
            GenerateExcelData(dropdown1.SelectedValue);
        }

        private void GenerateExcelData(string SlnoAbbreviation)
        {
            try
            {
                string read = System.IO.Path.GetFullPath(Server.MapPath("~/empdetail.xlsx"));

                if (Path.GetExtension(read) == ".xls")
                {
                    x = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + read + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"");
                }
                else if (Path.GetExtension(read) == ".xlsx")
                {
                    x = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + read + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1;';");
                }
                x.Open();
                OleDbCommand y = new OleDbCommand();
                OleDbDataAdapter z = new OleDbDataAdapter();
                DataSet dset = new DataSet();
                y.Connection = x;
                y.CommandType = CommandType.Text;
                y.CommandText = "SELECT distinct([Slno]) FROM [Sheet1$]";
                z = new OleDbDataAdapter(y);
                z.Fill(dset, "Slno");
                dropdown1.DataSource = dset.Tables["Slno"].DefaultView;
                if (!IsPostBack)
                {
                    dropdown1.DataTextField = "Slno";
                    dropdown1.DataValueField = "Slno";
                    dropdown1.DataBind();
                }
                if (!String.IsNullOrEmpty(SlnoAbbreviation) && SlnoAbbreviation != "Choose")
                {
                    y.CommandText = "SELECT [Slno], [EmpName], [Salaray], [Location]" +
                        "  FROM [Sheet1$] where [Slno]= @Slno_Abbreviation";
                    y.Parameters.AddWithValue("@Slno_Abbreviation", SlnoAbbreviation);
                }
                else
                {
                    y.CommandText = "SELECT [Slno],[EmpName],[Salary],[Location] FROM [Sheet1$]";
                }
                z = new OleDbDataAdapter(y);
                z.Fill(dset);

                Grid1.DataSource = dset.Tables[1].DefaultView;
                Grid1.DataBind();
            }
            catch (Exception ex)
            {
                lbl1.Text = ex.ToString();
            }
            finally
            {
                x.Close();
            }
        }
    }
}

Output

 

On page load, it will show all the data.

 

 

Now when we provide the specific Serial Number then it will show that data only.