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>
<asp:Label ID="lblError" runat="server" />
C# Code
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
{
string path = System.IO.Path.GetFullPath(Server.MapPath("~/InformationNew.xlsx"));
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();
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();
}
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);
grvData.DataSource = ds.Tables[1].DefaultView;
grvData.DataBind();
}
catch (Exception ex)
{
lblError.Text = ex.ToString();
}
finally
{
oledbConn.Close();
}
}
Result
When you select the value from the dropdown list, the result would be:
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.