Click here to Skip to main content
15,867,686 members
Articles / Web Development / ASP.NET
Tip/Trick

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

Rate me:
Please Sign up or sign in to vote.
4.74/5 (29 votes)
30 Jul 2013CPOL 304.3K   8.2K   39   34
How to read Excel file data into a 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.NET
<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>
<asp:Label ID="lblError" runat="server" />

C# Code

C#
using System;
using System.Data.OleDb;
using System.Data;
using System.IO;
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)
{
    try
        {
            // need to pass relative path after deploying on server
            string path = System.IO.Path.GetFullPath(Server.MapPath("~/InformationNew.xlsx"));
            /* 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. */

            if (Path.GetExtension(path) == ".xls")
            {
                oledbConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;
                Data Source=" + path + ";
                Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"");
            }
            else if (Path.GetExtension(path) == ".xlsx")
            {
                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();

            // passing list to drop-down list

            // selecting distinct 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");
            ddlSlno.DataSource = ds.Tables["dsSlno"].DefaultView;
            if (!IsPostBack)
            {
                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[1].DefaultView;
            grvData.DataBind();
        }
        // need to catch possible exceptions
        catch (Exception ex)
        {
            lblError.Text = ex.ToString();
        }
        finally
        {
            oledbConn.Close();
        }
}// close of method GemerateExceLData

Result

When you select the value from the dropdown list, the result would be: 

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Web Developer
India India
I am a 29 year old software Web Developer from Hyderabad, India. I have been working since approximately age 25. Where as in IT Development industry since 27. I am Microsoft Certified Technology Specialist.

I have taught myself in development, beginning with Microsoft's technologies ASP.NET, Approximately 3 years ago, I was given an opportunity to work as a freelance in the tech field. Now I am working as a web developer where my roles make me purely in web based technology solutions which manage and control access to applications and patient information stored in legacy systems, client-server applications.

I too had an opportunity to train some IT professionals with technical skills in development area. Which became my passion.

I have worked on various .NET framework versions(2.0 , 3.5, 4.0) and have been learning every new technology being introduced. Currently, I am looking forward to working in R & D in .Net to create distributed, reusable applications.

Comments and Discussions

 
QuestionAvoid hard coding Sheetname Pin
Sangram Nandkhile28-Aug-15 4:54
Sangram Nandkhile28-Aug-15 4:54 
AnswerRe: Avoid hard coding Sheetname Pin
Mannava Siva Aditya13-Oct-15 2:37
Mannava Siva Aditya13-Oct-15 2:37 
Questionsome Rows getting Blank after importing to dataset Pin
Sachin MCA201211-Aug-15 6:22
Sachin MCA201211-Aug-15 6:22 
QuestionHow can I query with Slno for entire Row Data Pin
Member 1162173113-Jun-15 0:26
Member 1162173113-Jun-15 0:26 
QuestionHow to avoid hidden columns while reading excel Pin
karthikv10127-May-15 19:01
karthikv10127-May-15 19:01 
AnswerRe: How to avoid hidden columns while reading excel Pin
Mannava Siva Aditya26-Jul-15 20:00
Mannava Siva Aditya26-Jul-15 20:00 
GeneralMy vote of 4 Pin
Dinesh_Shaw8-Nov-14 23:00
Dinesh_Shaw8-Nov-14 23:00 
QuestionAn error occured while running it Pin
Santhosh Sabarissh KM24-Aug-14 7:26
Santhosh Sabarissh KM24-Aug-14 7:26 
AnswerRe: An error occured while running it Pin
Mannava Siva Aditya16-Nov-14 19:50
Mannava Siva Aditya16-Nov-14 19:50 
GeneralNicely done and informative. Thanks. Pin
Serge-USA28-Jul-14 8:27
Serge-USA28-Jul-14 8:27 
GeneralMy vote of 4 Pin
Anwar Hossin28-Aug-13 0:33
Anwar Hossin28-Aug-13 0:33 
GeneralRe: My vote of 4 Pin
Mannava Siva Aditya28-Aug-13 18:06
Mannava Siva Aditya28-Aug-13 18:06 
GeneralMy vote of 5 Pin
raj ch1-Aug-13 0:17
raj ch1-Aug-13 0:17 
GeneralRe: My vote of 5 Pin
Mannava Siva Aditya28-Aug-13 18:06
Mannava Siva Aditya28-Aug-13 18:06 
GeneralMy vote of 1 Pin
philipli31-Jul-13 3:24
philipli31-Jul-13 3:24 
GeneralRe: My vote of 1 Pin
Mannava Siva Aditya31-Jul-13 3:32
Mannava Siva Aditya31-Jul-13 3:32 
GeneralMy vote of 5 Pin
FernandoUY30-Jul-13 3:56
professionalFernandoUY30-Jul-13 3:56 
GeneralMy vote of 5 Pin
Leo Jian24-Jul-13 15:03
Leo Jian24-Jul-13 15:03 
GeneralRe: My vote of 5 Pin
Mannava Siva Aditya24-Jul-13 19:28
Mannava Siva Aditya24-Jul-13 19:28 
GeneralMy vote of 1 Pin
Farhan usman6-Jun-13 20:06
Farhan usman6-Jun-13 20:06 
GeneralRe: My vote of 1 Pin
Mannava Siva Aditya15-Jul-13 23:55
Mannava Siva Aditya15-Jul-13 23:55 
GeneralRe: My vote of 1 Pin
Mannava Siva Aditya24-Jul-13 1:28
Mannava Siva Aditya24-Jul-13 1:28 
GeneralMy vote of 3 Pin
Singh_Vinay29-May-13 21:39
professionalSingh_Vinay29-May-13 21:39 
GeneralRe: My vote of 3 Pin
Mannava Siva Aditya29-May-13 23:50
Mannava Siva Aditya29-May-13 23:50 
GeneralThanks Pin
Alireza_136212-May-13 4:54
Alireza_136212-May-13 4:54 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.