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