How To Import Excel data to ASP.Net

Import excel data to ASP.NET

Hello Friends

In our daily life there are various situations in which we have to show entire excel sheet data to our ASP.Net GRIDVIEW/REPEATER etc.

So to solve this type of problem is very easy.

Firstly please define the column name in excel file, so that we can compare it in our ASP.Net code to bind it in the GRIDVIEW/REPEATER.
Suppose we have two columns in excel file (First is(A1)-->Name, Second is(B1)-->Mobile)
So to do this firstly we create a connection for excel file.
Then read the data from excel sheet.
Save it in a datatable.
Bind it to GRIDVIEW/REPEATER.
Dispose the datatable.
In below code i have remove duplicate mobile numbers also.

Design:

<title>Import Excel Data Using ASP.Net By Mohammad Adil</title>

.style1

{

width: 33%;

}

</style>

</head>

<body>

<div>

<tr>

</td>

<asp:Label ID="lblMsg" runat="server"></asp:Label>

</td>

</tr>

<tr>

</td>

<asp:FileUpload ID="fileuploadExcel" runat="server" />

<asp:RegularExpressionValidator ID="REVfupload" runat="server" Font-Bold="true" ErrorMessage="Upload only .xls or .xlsx file."

ValidationExpression="^.*\.(xls|XLS|XLSX|xlsx)$"

ControlToValidate="fileuploadExcel" ForeColor="Red"></asp:RegularExpressionValidator>

</td>

<td> 

</td>

</tr>

<tr>

</td>

<asp:Button ID="btnExcel" runat="server" OnClick="btnExcel_Click" Text="Display Excel Data" />

</td>

<td> 

</td>

</tr>

<tr>

<asp:GridView ID="GrdExcel" runat="server" CellPadding="6" BorderColor="#336699"

RowStyle-HorizontalAlign="Center" GridLines="Both" AutoGenerateColumns="false"

AllowPaging="false" BorderStyle="Solid" BorderWidth="1px" Width="70%">

<asp:TemplateField HeaderText="SNo.">

<%#Container.DataItemIndex+1 %>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Name">

<asp:Label ID="lblName" runat="server" Text='<%# Eval("Name") %>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Mobile">

<asp:Label ID="lblMobile" runat="server" Text='<%# Eval("Mobile") %>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

</Columns>

</asp:GridView>

</td>

</tr>

</table>

</div>

</form>

</body>

====================================================================

Code:

using System;

using System.Collections.Generic;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.IO;

using System.Data.OleDb;

using System.Data;

using System.Collections;

public partial class _Default : System.Web.UI.Page

{

string FileToConvert;

string connectionString;

protected void Page_Load(object sender, EventArgs e)

{

FileToConvert = "";

FileToConvert = Server.MapPath(".") + "/temp.xls";

lblMsg.ForeColor = System.Drawing.Color.Red;

lblMsg.Visible = true;

if (!IsPostBack)

{

try

{

}

catch (Exception ex)

{

}

protected void btnExcel_Click(object sender, EventArgs e)

{

string fileName = fileuploadExcel.FileName;

if (String.IsNullOrEmpty(fileName))

{

lblMsg.Text = "Please browse the file...!";

lblMsg.ForeColor = System.Drawing.Color.Red;

lblMsg.Visible = true;

return;

}

if (fileuploadExcel.HasFile)

{

if (fileName.Contains(".xls") || fileName.Contains(".xlxs"))

{

lblMsg.Visible = false;

fileuploadExcel.SaveAs(FileToConvert);//temporarily saves the file to work with (will be cleaned up two lines down)

ImportExcel(lblMsg);

if (lblMsg.Text.Contains("Invalid Column Name"))

{

return;

}

DataTable dt = (DataTable)System.Web.HttpContext.Current.Session["ImportExcel"];

GrdExcel.DataSource = dt;

GrdExcel.DataBind();

RemoveDuplicateRows(dt, "Mobile"); // Here Mobile is Column name of table

GrdExcel.DataSource = dt;

GrdExcel.DataBind();

File.Delete(FileToConvert);//cleans up the temporary file that was stored

}

else

{

lblMsg.Text = "Only Excel files are allowed...!";

lblMsg.ForeColor = System.Drawing.Color.Red;

lblMsg.Visible = true;

}

public override void VerifyRenderingInServerForm(Control control)

{

}

private void ImportExcel(Label lbl)

{

string strFileType = Path.GetExtension(fileuploadExcel.FileName).ToLower();

if (strFileType.Trim() == ".xls")

{

connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FileToConvert + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";

}

else if (strFileType.Trim() == ".xlsx")

{

connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FileToConvert + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";

}

try

{

OleDbConnection connection = new OleDbConnection(connectionString);

connection.Open();

//this next line assumes that the file is in default Excel format with Sheet1 as the first sheet name, adjust accordingly

OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", connection);

DataSet ds = new DataSet();

DataTable dt = new DataTable();

adapter.Fill(ds);//now you have your dataset ds now filled with the data and ready for manipulation

dt = ds.Tables[0];

System.Web.HttpContext.Current.Session["ImportExcel"] = dt;

connection.Close();

int number_of_columns = dt.Columns.Count;

string[] columnNames = new string[number_of_columns];

for (int j = 0; j < number_of_columns; j++)

{

columnNames[j] = dt.Columns[j].ToString();

}

if (columnNames[0] != "Name")

{

lblMsg.Text = "Invalid Column Name " + columnNames[0].ToString() + " .It should be 'Name'";

lblMsg.ForeColor = System.Drawing.Color.Red;

lblMsg.Visible = true;

//connection.Close();

return;

}

if (columnNames[1] != "Mobile")

{

lblMsg.Text = "Invalid Column Name " + columnNames[1].ToString() + " .It should be 'Mobile'";

lblMsg.ForeColor = System.Drawing.Color.Red;

lblMsg.Visible = true;

//connection.Close();

return;

}

catch (Exception ex)

{

lbl.Text = "Error loading file !!!, Make sure that the file is in '.xls' or '.xlsx' format";

lbl.Visible = true;

lbl.ForeColor = System.Drawing.Color.Red;

return;

}

public DataTable RemoveDuplicateRows(DataTable dTable, string colName)

{

Hashtable hTable = new Hashtable();

ArrayList duplicateList = new ArrayList();

foreach (DataRow dtRow in dTable.Rows)

{

if (hTable.Contains(dtRow[colName]))

duplicateList.Add(dtRow);

else

hTable.Add(dtRow[colName], string.Empty);

}

foreach (DataRow dtRow in duplicateList)

dTable.Rows.Remove(dtRow);

return dTable;

}

Now execute the solution to get the outpu.

you can download the source code form here... Download Code

ASP.Net Code By Adil

June 03, 2014