Display Excel Data in GridView in asp.net
In this Article , I am trying to explain how to display excel data into GridView based on sheet selection. Here, first I fetch all the sheets into one Dropdownlist and based upon the Dropdown sheet selection we show the data in Gridview.
Display Excel Data in GridView in asp.net
In this Article, I am trying to explain how to display excel data into GridView based on sheet selection. Here, first I fetch all the sheets into one dropdownlist and based upon the dropdown sheet selection we show the data in gridview.
Follow the following simple steps to achieve this.Source Code:
Design a Page like below.
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="ExcelToGridView.aspx.cs" Inherits=" ExcelToGridView " %>
<!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>< /title>
< style type="text/css">
.style1
{
width: 420px;
}
< /style>
< /head>
< body>
< form id="form1" runat="server">
< div>
< table width="100%">
< thead>
< tr>
< td colspan="4" align="center" style="font-size:xx-large; font-style:italic; color:Red">
Display Excel Data in GridView in asp.net
< /td>
< /tr>
< tr>
< td>< /td>
< /tr>
< tr>
< td>< /td>
< /tr>
< /thead>
< tbody>
< tr>
< td colspan="3" align="right">
< asp:FileUpload ID="FileUpload1" runat="server" Width="354px"
style="margin-left: 0px" />
< /td>
< td colspan="1" width="30%">
< asp:Button ID="btnView" runat="server" Text="View" onclick="btnView_Click" />
< /td>
< /tr>
< tr>
< td colspan="3" align="right">
< asp:DropDownList ID="ddlSheet" runat="server" AutoPostBack="True" Width="49%" Visible="false"
Height="18px" OnSelectedIndexChanged="ddlSheet_OnSelectedIndexChanged">
< /asp:DropDownList>
< /td>
< td colspan="1" width="30%">
< /td>
< /tr>
< tr>
< td colspan="3" align="center">
< asp:Button ID="btnShow" runat="server" Text="Show" Visible="false"
onclick="btnShow_Click" />
< /td>
< /tr>
< tr>
< td>< /td>
< td class="style1">< /td>
< /tr>
< tr>
< td>< /td>
< td class="style1">< /td>
< /tr>
< tr>
< td colspan="4" width="100%" align="center">
< asp:GridView ID="gvExcelData" runat="server" CellPadding="4"
ForeColor="#333333" GridLines="None" >
< AlternatingRowStyle BackColor="White" />
< EditRowStyle BackColor="#7C6F57" />
<FooterStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
<HeaderStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#666666" ForeColor="White" HorizontalAlign="Center" />
<RowStyle BackColor="#E3EAEB" />
<SelectedRowStyle BackColor="#C5BBAF" Font-Bold="True" ForeColor="#333333" />
<SortedAscendingCellStyle BackColor="#F8FAFA" />
<SortedAscendingHeaderStyle BackColor="#246B61" />
<SortedDescendingCellStyle BackColor="#D4DFE1" />
<SortedDescendingHeaderStyle BackColor="#15524A" />
</asp:GridView>
</td>
</tr>
</tbody>
</table>
</div>
</form>
</body>
</html>Code Behind:
1) using System.Data.OleDb; , using this namespace we can read the excel sheet data.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.OleDb;
using System.Data;
using System.IO;
using System.Configuration;
using System.Data.SqlClient;
public partial class ExcelToGridView: System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
//here it's fetching all sheets available in excel book
private void Get_Sheets()
{
OleDbConnection oconn = null;
DataTable dt = null;
try
{
string FilePath = string.Empty;
string FileName = string.Empty;
if (FileUpload1.HasFile)
{
//Get File name
FileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
// Get File extension
string Extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
string FolderPath = "~/ExcelSheets/";
FilePath = Server.MapPath(FolderPath + FileName);
ViewState["FilePath"] = FilePath;
ViewState["FileName"] = FileName;
//File save
FileUpload1.SaveAs(FilePath);
}
//Microsoft Office 12.0 Access Database Engine OLE DB Provider
oconn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FilePath + ";Extended Properties=Excel 8.0");
oconn.Open();
dt = null;
// Get all tables include in that work sheet
dt = oconn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if (dt == null)
{
}
String[] sheet = new String[dt.Rows.Count];
int i = 0;
//Read All sheet names and store in one string Builder
foreach (DataRow dr in dt.Rows)
{
sheet[i] = dr["TABLE_NAME"].ToString();
i++;
}
string[] a = sheet;
int j = 0;
// Assign all sheet names to DropDownList
if (a != null && a.Length > 0)
{
ddlSheet.Visible = true;
//lblsheet.Visible = true;
for (j = 0; j < a.Length; j++)
{
ddlSheet.Items.Add(a[j]);
}
//Default selected value for DropDown
ddlSheet.Items.Insert(0, "<--- Select Excel Sheet --->");
}
else
{
ddlSheet.Visible = false;
//lblsheet.Visible = false;
}
}
catch (Exception ex)
{
}
finally
{
//Close the connection
if (oconn != null)
{
oconn.Close();
oconn.Dispose();
}
if (dt != null)
{
dt.Dispose();
}
}
}
// Using this button we fetch all sheets included in WrkBook
protected void btnView_Click(object sender, EventArgs e)
{
Get_Sheets();
}
protected void ddlSheet_OnSelectedIndexChanged(object sender, EventArgs e)
{
btnShow.Visible = true;
}
//Using this we display Excel sheet data into GridView control
private void Display()
{
OleDbConnection oconn = null;
DataTable dt1 = new DataTable();
//Add dummy columns to datatable.
dt1.Columns.Add("ENAME");
dt1.Columns.Add("JOB");
dt1.Columns.Add("MGR");
dt1.Columns.Add("SAL");
dt1.Columns.Add("COMM");
try
{
string FileName = ViewState["FileName"] as string;
string FilePath = ViewState["FilePath"] as string;
oconn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FilePath + ";Extended Properties=Excel 8.0");
//select file name based upon dropdown selecteditem.
OleDbCommand ocmd = new OleDbCommand("select * from [" + ddlSheet.SelectedItem.ToString() + "]", oconn);
oconn.Open();
//Read all rows and then store in DataTable
OleDbDataReader odr = ocmd.ExecuteReader();
string ENAME=string.Empty;
string JOB=string.Empty;
string MGR=string.Empty;
string SAL=string.Empty;
string COMM=string.Empty;
while (odr.Read())
{
ENAME = odr["ENAME"].ToString();
JOB = odr["JOB"].ToString();
MGR = odr["MGR"].ToString();
SAL = odr["SAL"].ToString();
COMM = odr["COMM"].ToString();
DataRow dr = dt1.NewRow();
dr["ENAME"] = ENAME;
dr["JOB"] = JOB;
dr["MGR"] = MGR;
dr["SAL"] = SAL;
dr["COMM"] = COMM;
dt1.Rows.Add(dr);
}
//Display data to gridview if Records are found
if (dt1.Rows.Count > 0)
{
Session["Table"] = dt1;
gvExcelData.Visible = true;
gvExcelData.DataSource = dt1;
gvExcelData.DataBind();
}
}
catch (DataException ex)
{
}
finally
{
if (oconn != null)
{
oconn.Close();
oconn.Dispose();
}
if (dt1 != null)
{
dt1.Dispose();
}
}
}
//here it is displaying all the records into one gridview control
protected void btnShow_Click(object sender, EventArgs e)
{
Display();
}
}OutPut:
Follow
Protected void Page_Load(object sender, EventArgs e)
{
OleDbConnection YourDBConnection = new
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Server.MapPath("~/ExcelData/Example.xls") + ";" + "Extended
Properties=\"Excel 8.0;HDR=Yes\"");
YourDBConnection.Open();
string SQLString = "SELECT * FROM [Sheet1$]";
OleDbCommand DBCommand = new OleDbCommand(SQLString, YourDBConnection);
IDataReader DBReader = DBCommand.ExecuteReader();
GridView1.DataSource = DBReader;
GridView1.DataBind();
DBReader.Close();
YourDBConnection.Close();
}
You can use below code for delete data in excel sheet
String stringDelete = "Delete from [" + stringSheetName + "$]";
MyExcel.Connection = con;
MyExcel.CommandType = CommandType.Text;
MyExcel.CommandText = stringDelete;
MyExcel.ExecuteNonQuery();