How to read excel data and show on GridView in asp.net?
In this article i am going to show how to read excel sheet data and show it in a gridview. You can read both .xls and .xlsx excel sheet. Sometimes we need to import data from excel sheet to sql server then you can use this method to read excel data and then store data in sql server.
This article mainly describes how to read data from an Excel Sheet and display them in a GridView.
Here I am going to show you the step by step process to read data from Excel and display them in a GridView. To do so follow the below steps :Step 1 :
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>Read Excel File Data and Show in GridView</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Label ID="lblHeading" runat="server" Text="Select File To Upload : "></asp:Label>
<asp:FileUpload ID="FileUpload1" runat="server" />
<br />
<br />
<asp:Button ID="btnUpload" runat="server" Text="Upload"
onclick="btnUpload_Click" /> ?
<br />
<asp:Label ID="lblerror" runat="server" ForeColor ="Red" Text=""></asp:Label>
<br /><br />
<asp:GridView ID="GridView1" AutoGenerateColumns ="true" HeaderStyle-BackColor="ActiveCaption" HeaderStyle-ForeColor ="White" runat="server">
</asp:GridView>
</div>
</form>
</body>
</html>Step 2 :
Here is the C# code of the Default.aspx.cs file:
using System;
using System.Configuration;
using System.Data;
using System.Linq;
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;
using System.IO;
using System.Data.OleDb;
using System.Text;
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)
{
if (FileUpload1.HasFile)
{
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);
oleconn = new OleDbConnection(GetOleDbConnectionString(SavePath));
string strQuery = "SELECT * FROM [Sheet1$]";
DataTable dt = new DataTable();
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;
}
}
}
#region GetOleDbConnectionString(string savepath)
public string GetOleDbConnectionString(string savepath)
{
var finfo = new FileInfo(savepath);
if (!finfo.Exists)
{
throw new FileNotFoundException(savepath);
}
var fileExtension = finfo.Extension.ToLower();
switch (fileExtension)
{
case ".xls":
return string.Format(ConfigurationManager.AppSettings["xlsOleDBConnection"], savepath);
case ".xlsx":
return string.Format(ConfigurationManager.AppSettings["xlsxOleDBConnection"], savepath);
default:
throw new NotSupportedException(String.Format("This file type {0} is not supported!", fileExtension));
}
}
#endregion
#region DeleteFile(string savepath)
public void DeleteFile(string savepath)
{
if (File.Exists(savepath))
{
try
{
File.Delete(savepath);
}
catch { }
}
}
#endregion
}
Reference: http://dotnetsquare.com/resources/45-read-excel-data-and-show-on-gridview-in-Asp-Net
Follow
Nice article . It will help too much