Controls and Properties:
1. FileUpload Control. name=”FileUpload1″
2. Button. name=”btnUpload” Text=”Upload”
3. GridView: name=”GridView1″
NameSpaces:
using System;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.IO;
using System.Configuration;
Source Code:
<form id="form1" runat="server">
<div>
<asp:fileupload id="FileUpload1" runat="server" forecolor="#993399" xmlns:asp="#unknown" />
<asp:button id="btnUpload" runat="server" text="Upload" onclick="btnUpload_Click" xmlns:asp="#unknown" />
<asp:gridview id="GridView1" runat="server" xmlns:asp="#unknown">
OnPageIndexChanging="PageIndexChanging" BackColor="White" BorderColor="#999999"
BorderStyle="None" BorderWidth="1px" CellPadding="3" GridLines="Vertical">
<headerstyle cssclass="hdr" backcolor="#000084" font-bold="True">
ForeColor="White" />
<alternatingrowstyle backcolor="#DCDCDC" />
<footerstyle cssclass="ftr" backcolor="#CCCCCC" forecolor="Black" />
<pagerstyle backcolor="#999999" forecolor="Black" horizontalalign="Center" />
<rowstyle backcolor="#EEEEEE" forecolor="Black" />
<selectedrowstyle backcolor="#008A8C" font-bold="True" forecolor="White" />
<sortedascendingcellstyle backcolor="#F1F1F1" />
<sortedascendingheaderstyle backcolor="#0000A9" />
<sorteddescendingcellstyle backcolor="#CAC9C9" />
<sorteddescendingheaderstyle backcolor="#000065" />
</headerstyle></asp:gridview>
</div>
</form>
aspx.cs Page Code:
1. Uploading an Excel File:
protected void btnUpload_Click(object sender, EventArgs e)
{
if (FileUpload1.HasFile)
{
string FileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
string Extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
string FolderPath = ConfigurationManager.AppSettings["FolderPath"];
string FilePath = Server.MapPath(FolderPath + FileName);
FileUpload1.SaveAs(FilePath);
Import_To_Grid(FilePath, Extension);
}
}
2. Import Excel File in GridView Control:protected void Page_Load(object sender, EventArgs e)
private void Import_To_Grid(string FilePath, string Extension)
{
string conStr = "";
switch (Extension)
{
case ".xls":
conStr = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
break;
case ".xlsx":
conStr = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
break;
}
conStr = String.Format(conStr, FilePath, 1);
OleDbConnection connExcel = new OleDbConnection(conStr);
OleDbCommand cmdExcel = new OleDbCommand();
OleDbDataAdapter oda = new OleDbDataAdapter();
DataTable dt = new DataTable();
cmdExcel.Connection = connExcel;
connExcel.Open();
DataTable dtExcelSchema;
dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
connExcel.Close();
connExcel.Open();
cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";
oda.SelectCommand = cmdExcel;
oda.Fill(dt);
connExcel.Close();
GridView1.DataSource = dt;
GridView1.DataBind();
}
protected void PageIndexChanging(object sender, GridViewPageEventArgs e)
{
string FolderPath = ConfigurationManager.AppSettings["FolderPath"];
string FileName = GridView1.Caption;
string Extension = Path.GetExtension(FileName);
string FilePath = Server.MapPath(FolderPath + FileName);
Import_To_Grid(FilePath, Extension);
GridView1.PageIndex = e.NewPageIndex;
GridView1.DataBind();
}