I have seen a lot of users asking how to import data from an excel sheet into an ASP.NET GridView.
Here in this article we will see how to import a excel sheet to the gridview, First we will browse the file and we will copy that file to the web directory from there we will bind the first excel sheet into the gridview.
Here we can load Excel 1997, 2003 and 2007.
ASPX Page:
Here in this article we will see how to import a excel sheet to the gridview, First we will browse the file and we will copy that file to the web directory from there we will bind the first excel sheet into the gridview.
Here we can load Excel 1997, 2003 and 2007.
ASPX Page:
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:ScriptManager ID="ScriptManager1" runat="server">
</asp:ScriptManager>
<asp:UpdatePanel ID="UpdatePanel1" runat="server">
<ContentTemplate>
<asp:GridView ID="GridView1" runat="server" CellPadding="4"
Font-Names="Verdana" Font-Size="Small" ForeColor="#333333">
<RowStyle BackColor="#EFF3FB" />
<FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
<SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
<HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<EditRowStyle BackColor="#2461BF" />
<AlternatingRowStyle BackColor="White" />
</asp:GridView>
</ContentTemplate>
</asp:UpdatePanel>
<table>
<tr>
<td><asp:FileUpload ID="fupExcel" runat="server" Font-Names="Verdana"
Font-Size="Small" /></td>
</tr>
<tr>
<td><asp:Button ID="btnUpload" runat="server" Text="Upload" Font-Names="Verdana"
Font-Size="Small" onclick="btnUpload_Click" /></td>
</tr>
</table>
</div>
</form>
</body>
</html>
Code Behind:
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.IO;
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.OleDb;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btnUpload_Click(object sender, EventArgs e)
{
try
{
HttpFileCollection uploads = HttpContext.Current.Request.Files;
for (int i = 0; i < uploads.Count; i++)
{
HttpPostedFile upload = uploads[i];
if (upload.ContentLength == 0)
continue;
string c = System.IO.Path.GetFileName(upload.FileName);
try
{
upload.SaveAs(Server.MapPath("Invoices\\") + c);
}
catch (Exception Exp)
{
throw (Exp);
}
}
if (fupExcel.PostedFile != null)
{
HttpPostedFile attFile = fupExcel.PostedFile;
int attachFileLength = attFile.ContentLength;
if (attachFileLength > 0)
{
if (fupExcel.PostedFile.ContentLength > 0)
{
string Extension = Path.GetExtension(fupExcel.PostedFile.FileName);
string inFileName = Path.GetFileName(fupExcel.PostedFile.FileName);
string pathDataSource = Server.MapPath("Invoices\\") + inFileName;
string conStr = "";
if (Extension == ".xls" || Extension == ".xlsx")
{
switch (Extension)
{
case ".xls": //Excel 1997-2003
conStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source='" + pathDataSource.ToString() + "';" + "Extended Properties=Excel 8.0;";
break;
case ".xlsx": //Excel 2007
conStr = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source='" + fupExcel.PostedFile.FileName.ToString() + "';" + "Extended Properties=Excel 8.0;";
break;
default:
break;
}
OleDbConnection connExcel = new OleDbConnection(conStr.ToString());
OleDbCommand cmdExcel = new OleDbCommand();
OleDbDataAdapter oda = new OleDbDataAdapter();
DataTable dt = new DataTable();
connExcel.Open();
DataSet ds = new DataSet();
//Selecting Values from the first sheet
//Sheet name must be as Sheet1
OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", conStr.ToString());
da.Fill(ds);
GridView1.DataSource = ds;
GridView1.DataBind();
if (GridView1.Columns.Count > 0)
{
}
connExcel.Close();
if (File.Exists(pathDataSource))
{
File.Delete(pathDataSource);
}
}
else
{
//Show your error in any error controls
}
}
else
{
fupExcel.Focus();
GridView1.DataSource = null;
GridView1.DataBind();
}
}
}
}
catch (Exception ex)
{
throw ex;
}
}
}
Screen Samples:
Initial screen will be like this with a simple asp.net file upload control and a button for upload the excel to the gridview.
After binding the excel using the "Upload" button the gridview will be visible and it will look like this.
All r super sarathi .Its really help s.
ReplyDelete