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