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