How to Import Excel to Gridview:
In this article describes how to read data from an Excel Sheet and display them in a GridView.
step by step process to read data from Excel and display them in a GridView.
First Step:
Create New Web site with new webpage named as Default.aspx (Following is the code contained in the Default.aspx page).
First Step:
Create New Web site with new webpage named as Default.aspx (Following is the code contained in the Default.aspx page).
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Button ID="btImport" runat="server" OnClick="btImport_Click" Text="Import" />
<asp:Button ID="btExport" runat="server" OnClick="btExport_Click" Text="Export" />
<asp:FileUpload ID="FileUpload1" runat="server" />
<br />
<br />
<asp:GridView ID="GridView1" runat="server" BackColor="White" BorderColor="#E7E7FF"
BorderStyle="None" BorderWidth="1px" CellPadding="3" GridLines="Horizontal">
<FooterStyle BackColor="#B5C7DE" ForeColor="#4A3C8C" />
<RowStyle BackColor="#E7E7FF" ForeColor="#4A3C8C" />
<SelectedRowStyle BackColor="#738A9C" Font-Bold="True" ForeColor="#F7F7F7" />
<PagerStyle BackColor="#E7E7FF" ForeColor="#4A3C8C" HorizontalAlign="Right" />
<HeaderStyle BackColor="#4A3C8C" Font-Bold="True" ForeColor="#F7F7F7" />
<AlternatingRowStyle BackColor="#F7F7F7" />
</asp:GridView>
<br />
</div>
</form>
</body>
</html>
Second Step :
This C# code of the Default.aspx.cs file:
using System;
using System.Configuration;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
public partial class Default: System.Web.UI.Page
{
OleDbConnection oleconn;
StringBuilder sb = new StringBuilder();
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btnUpload_Click(object sender, EventArgs e)
{
{
string tempFileName = Guid.NewGuid().ToString("N") + "_" + FileUpload1.FileName;
string saveLocation = HttpContext.Current.Server.MapPath(ConfigurationManager.AppSettings["FileUploadLocation"]);
string SavePath = Path.Combine(saveLocation, tempFileName);
try
{
FileUpload1.SaveAs(SavePath);
string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + SavePath + "; Extended Properties=\"Excel 8.0;HDR=YES;\"";
OleDbConnection oleconn = new OleDbConnection(connectionString);
string strQuery = "SELECT * FROM [Sheet1$]";
DataTable dt = new DataTable();
OleDbCommand ocmd = new OleDbCommand(strQuery, oleconn);
// OleDbCommand ocmd = new OleDbCommand(strQuery, oleconn);
oleconn.Open();
OleDbDataReader odr = ocmd.ExecuteReader();
if (odr.HasRows)
{
dt.Load(odr);
GridView1.DataSource = dt;
GridView1.DataBind();
}
oleconn.Close();
// DeleteFile(SavePath);
}
catch (Exception ex)
{
lblerror.Text = "error : " + ex.Message;
}
}
}
}
No comments:
Post a Comment