In ASP.NET Gridview is a best approach to present Tabular data. Looking into the advanced functionalities of a Gridview control using this you can fetch data from any data media like SQL, MySQL or even an Excel File. In the below demo I have an Excel File with 5 columns of data. Using Gridview we are presenting those excel records in tabular shape on a web page.
To try the below demo, Create an ASP.NET Web Application. Then to prepare data open an Excel File. Add any 5 columns of data with single word header. Name the File as temp.xlsx. Then in your application under root folder create a folder named as “FILES”. Move the temp.xlsx File inside. To debug the below app in your new app, Add the Default.aspx file with it’s code-behind file in VB.NET. To get started Copy the below Gridview Control in your Default.aspx page.
Gridview using Auto Generate Edit Button
<asp:GridView ID="grdRecords" runat="server" AutoGenerateColumns="false" AutoGenerateEditButton="true" AllowPaging="true" PageSize="20" Font-Names="Arial" Font-Size="11pt" AlternatingRowStyle-BackColor="#C2D69B" HeaderStyle-BackColor="green" HeaderStyle-ForeColor="#FFFFFF" OnPageIndexChanging="OnPaging" Width="100%">
<Columns>
<asp:TemplateField HeaderText="Emp Name">
<ItemTemplate>
<asp:Label ID="lblEmpName" runat="server" Text='<%# Bind("Name")%>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Emp ID">
<ItemTemplate>
<asp:Label ID="lblEmpID" runat="server" Text='<%# Bind("ID")%>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Status">
<EditItemTemplate>
<asp:DropDownList ID="ddlRowStatus" runat="server" AutoPostBack="true">
<asp:ListItem>thisis</asp:ListItem>
</asp:DropDownList>
</EditItemTemplate>
<ItemTemplate>
<%# Eval("Status") %>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Starts">
<ItemTemplate>
<asp:Label ID="lblStarts" runat="server" Text='<%# Bind("Starts")%>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Ends">
<ItemTemplate>
<asp:Label ID="lblEnds" runat="server" Text='<%# Bind("Ends")%>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
You must noticed here how I am binding data to the above Gridview. Status Field is the Field which I want to edit using Auto Generate Edit Button. That’s why inside template field for this column I am with 2 sub-entries ItemTemplate and EditItemTemplate. EditItemTemplate holding that control which will display in the place of Status column at the time of Edit.
The below Code is from the Code-behind File. Logic is very simple. Using BindGrid() sub-routine we are displaying records in the Grid. 3 more gridview events I used grdRecords_RowCancelingEdit, grdRecords_RowDataBound, grdRecords_RowUpdating. Using RowCancelingEdit() event I am handling the Cancel button functionalities. While using RowDataBound() we are binding data for Status dropdownlist in Grid. Finally after user Edit the record we are updating the Excel File under RowUpdating() event. FilePath and Extention these two are used as parameters in many Functions. That’s why it is declared as global variable.
Code-behind Codes for Gridview using Edit button
Imports System.IO
Imports System.Data.OleDb
Imports System.Data
Partial Class _Default
Inherits System.Web.UI.Page
Dim FilePath As String = "C:\Users\biswabhusan_panda\Documents\Visual Studio 2013\WebSites\ExcelApp\Files\temp.xlsx"
Dim Extension As String = ".xlsx"
Dim ddlRowStatus As DropDownList
Dim ConStr As String = ConfigurationManager.ConnectionStrings("ConnString").ConnectionString()
Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
If Not Page.IsPostBack Then
ViewState("Filter") = "ALL"
BindGrid(FilePath, Extension)
End If
End Sub
Protected Sub OnPaging(sender As Object, e As GridViewPageEventArgs)
grdRecords.PageIndex = e.NewPageIndex
Me.BindGrid(FilePath, Extension)
End Sub
Private Sub BindGrid(ByVal FilePath As String, ByVal Extension As String)
ConStr = String.Format(ConStr, FilePath)
Dim connExcel As New OleDbConnection(ConStr)
Dim cmdExcel As New OleDbCommand()
Dim oda As New OleDbDataAdapter()
Dim dtbl As New DataTable()
cmdExcel.Connection = connExcel
'Get the name of First Sheet
connExcel.Open()
Dim dtExcelSchema As DataTable
dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
Dim SheetName As String = dtExcelSchema.Rows(0)("TABLE_NAME").ToString()
connExcel.Close()
'Read Data from First Sheet
connExcel.Open()
If ViewState("idFilter") <> "" Then
cmdExcel.CommandText = "SELECT * From [" & SheetName & "] WHERE ID LIKE '%" & ViewState("idFilter").ToString() & "%'"
ElseIf ViewState("nameFilter") <> "" Then
cmdExcel.CommandText = "SELECT * From [" & SheetName & "] WHERE NAME LIKE '%" & ViewState("nameFilter").ToString() & "%'"
Else
If ViewState("Filter").ToString() = "ALL" Then
cmdExcel.CommandText = "SELECT * From [" & SheetName & "]"
Else
cmdExcel.CommandText = "SELECT * From [" & SheetName & "] WHERE Status='" & ViewState("Filter").ToString() & "'"
End If
End If
oda.SelectCommand = cmdExcel
oda.Fill(dtbl)
connExcel.Close()
'Bind Data to GridView
grdRecords.DataSource = dtbl
grdRecords.DataBind()
End Sub
Protected Sub grdRecords_RowCancelingEdit(sender As Object, e As GridViewCancelEditEventArgs) Handles grdRecords.RowCancelingEdit
grdRecords.EditIndex = -1
Me.BindGrid(FilePath, Extension)
End Sub
Protected Sub grdRecords_RowDataBound(sender As Object, e As GridViewRowEventArgs) Handles grdRecords.RowDataBound
If e.Row.RowType = DataControlRowType.DataRow Then
If (e.Row.RowState And DataControlRowState.Edit) > 0 Then
ddlRowStatus = DirectCast(e.Row.FindControl("ddlRowStatus"), DropDownList)
If (ddlRowStatus.ID = "ddlRowStatus") Then
Dim list As New ArrayList
ConStr = String.Format(ConStr, FilePath)
Dim ConnExcel As New OleDbConnection(ConStr)
Dim CmdA As New OleDbCommand()
Dim CmdB As New OleDbCommand()
Dim oda As New OleDbDataAdapter()
Dim dtbl As New DataTable()
CmdA.Connection = ConnExcel
CmdB.Connection = ConnExcel
'Get the name of First Sheet
ConnExcel.Open()
Dim dtExcelSchema As DataTable
dtExcelSchema = ConnExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
Dim SheetName As String = dtExcelSchema.Rows(0)("TABLE_NAME").ToString()
ConnExcel.Close()
'Read Data from First Sheet
ConnExcel.Open()
CmdA.CommandText = "SELECT DISTINCT Status From [" & SheetName & "]"
Dim reader As OleDbDataReader = CmdA.ExecuteReader()
If reader.HasRows Then
Do While reader.Read()
list.Add(reader(0).ToString())
Loop
End If
reader.Close()
ddlRowStatus.DataSource = list
ddlRowStatus.DataBind()
End If
End If
End If
End Sub
Protected Sub grdRecords_RowEditing(sender As Object, e As GridViewEditEventArgs) Handles grdRecords.RowEditing
grdRecords.EditIndex = e.NewEditIndex
Me.BindGrid(FilePath, Extension)
End Sub
Protected Sub grdRecords_RowUpdating(sender As Object, e As GridViewUpdateEventArgs) Handles grdRecords.RowUpdating
ddlRowStatus = DirectCast(grdRecords.Rows(e.RowIndex).FindControl("ddlRowStatus"), DropDownList)
Dim lblEmpID As Label = DirectCast(grdRecords.Rows(e.RowIndex).FindControl("lblEmpID"), Label)
ConStr = String.Format(ConStr, FilePath)
Dim connExcel As New OleDbConnection(ConStr)
Dim cmdExcel As New OleDbCommand()
Dim oda As New OleDbDataAdapter()
Dim dtbl As New DataTable()
cmdExcel.Connection = connExcel
'Get the name of First Sheet
connExcel.Open()
Dim dtExcelSchema As DataTable
dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
Dim SheetName As String = dtExcelSchema.Rows(0)("TABLE_NAME").ToString()
connExcel.Close()
'Read Data from First Sheet
connExcel.Open()
cmdExcel.CommandText = "UPDATE [" & SheetName & "] SET Status='" & ddlRowStatus.SelectedValue & "' WHERE ID=" & lblEmpID.Text.ToString() & ""
cmdExcel.ExecuteNonQuery()
connExcel.Close()
grdRecords.EditIndex = -1
Me.BindGrid(FilePath, Extension)
End Sub
End Class
