How to display Excel File records in an ASP.NET Gridview?

How to display Excel File records in an ASP.NET Gridview?

Due to the popularity of MS Excel, Still today more than 75% of Organizations prefers to maintain their data in MS-Excel. Whether it’s the matter of project management or a budget plan, MS-Excel is an awesome tool. I noticed in my organization more then 80% percent records are in Excel. One day my boss come to me with a plan that he need to update “Utilization_Reports.xlsx” using User Interface. For him I had taken the ownership to develop this tool. During this development phases I found how to fetch data from an Excel File like a database table using SQL Queries. This stay interesting for me. Sharing the same for your reference.

Gridview

<asp:GridView ID="grdRecords" runat="server" AutoGenerateColumns="false" AllowPaging="true" PageSize="10" Font-Names="Arial" Font-Size="11pt" AlternatingRowStyle-BackColor="#C2D69B" HeaderStyle-BackColor="green" HeaderStyle-ForeColor="#FFFFFF" OnPageIndexChanging="OnPaging" AutoGenerateEditButton="true" Width="100%">
<Columns>
<asp:TemplateField  HeaderText="Employee Name" SortExpression="Name">
<ItemTemplate>
<asp:Label ID="lblEmpName" runat="server" Text='<%# Bind("Name")%>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Employee ID" SortExpression="ID">
<ItemTemplate>
<asp:Label ID="lblEmpID" runat="server" Text='<%# Bind("ID")%>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField>
<HeaderTemplate>
Status:
<asp:DropDownList ID="ddlStatus" runat="server" OnSelectedIndexChanged="CountryChanged" AutoPostBack="true" AppendDataBoundItems="true">
<asp:ListItem Text = "ALL" Value = "ALL"></asp:ListItem>
</asp:DropDownList>
</HeaderTemplate>
<EditItemTemplate>
<asp:DropDownList ID="ddlRowStatus" runat="server" AutoPostBack="true">
<asp:ListItem>thisis</asp:ListItem>
</asp:DropDownList>
</EditItemTemplate>
<ItemTemplate>
<%# Eval("Status") %>
</ItemTemplate>
</asp:TemplateField>                    
</Columns>
</asp:GridView>

As we mentioned above here I want to fetch an excel file like a database table. So first let us declare the connection string for MS-Excel. That’s what I did in below web.config file.

Web.Config

<appSettings>
<add key="FolderPath" value="Files/"/>
</appSettings>
<connectionStrings>
<!--<add name ="Excel03ConString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}; Extended Properties='Excel 8.0'" />-->
<add name ="ConnString" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0}; Extended Properties='Excel 8.0'"/>
</connectionStrings>

To bind data to the Gridview in Codebehind I am with the below Subroutine. In BindGrid I am passing FilePath and File Extention as the parameters. These are simple string varibales. Declare as global variables. To fetch data from Excel like a database First I am traking the 0th Sheet of Excel. Then running a SQL Query over the table of my Excel File. Finally using a ASP.NET DataAdapter I am filling data to the gridview.

BindGrid

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("Filter").ToString() = "ALL" Then
cmdExcel.CommandText = "SELECT * From [" &amp; SheetName &amp; "]"
Else
cmdExcel.CommandText = "SELECT * From [" &amp; SheetName &amp; "] WHERE Status='" &amp; ViewState("Filter").ToString() &amp; "'"
End If

oda.SelectCommand = cmdExcel
oda.Fill(dtbl)
connExcel.Close()

'Bind Data to GridView 
grdRecords.DataSource = dtbl
grdRecords.DataBind()

End Sub

The above subroutine is responsible to Fill the Gridview. But to display data in Gridview we need to Call this during page load. That’s what I am doing in below. Inside the not page.ispostback event I am calling the Function with required parameters.

In Page Load

Dim FilePath as String = "C:/demo.xlsx"
Dim Extension as String = ".xlsx"

If Not Page.IsPostBack Then
ViewState("Filter") = "ALL"
BindGrid(FilePath, Extension)
End If