In this article I will explain with an example, how to read and import
Excel sheet into ASP.Net GridView control using
OLEDB and
ADO.Net in C# and VB.Net.
Download System.Data.OleDb DLL
There are two ways you can download the System.Data.OleDb DLL.
MSI Installer:
Nuget:
Concept
Excel File works similar to that of Database where an Excel file can be imagined as Database while the Sheets resemble Tables.
Thus, OLEDB allows us to query Excel files as if it is a Database.
Connection Strings
The first thing is to build connection strings to Excel files and Excel files are broadly divided into two types and Excel 97-2003 and Excel 2007 and higher.
Excel 97 – 2003 format which uses Microsoft Jet driver and the Excel version is set to 8.0 and Header is set to YES i.e. first row in Excel sheet will be Header Row.
<add name="Excel03ConString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'" />
Excel 2007 and higher format which uses Microsoft Ace driver and the Excel version is set to 12.0 and Header is set to YES i.e. first row in Excel sheet will be Header Row.
<add name="Excel07ConString" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR={1}'" />
Queries
The following SQL query selects records from Excel file.
SELECT * From [" + sheetName + "]
HTML Markup
The HTML Markup consists of following controls:
FileUpload – For selecting file.
Button – For uploading file.
The Button has been assigned with an OnClick event hander.
Label – For displaying message.
RadioButtonList – For capturing user input.
The RadioButtonList consists of two ListItem.
GridView – For displaying the imported Excel file.
Properties
AllowPaging – For enabling paging in the GridView control.
PageSize – For permitting maximum number of rows to be displayed per page.
Events
The GridView has been assigned with an OnPageIndexChanging event handler.
<asp:FileUpload ID="fuUpload" runat="server" />
<asp:Button ID="btnUpload" runat="server" Text="Upload" OnClick="OnUpload" />
<br />
<br />
<asp:Label ID="lblHasHeader" runat="server" Text="Has Header?"></asp:Label>
<asp:RadioButtonList ID="rbHDR" runat="server" RepeatDirection="Horizontal">
<asp:ListItem Text="Yes" Value="Yes" Selected="True"></asp:ListItem>
<asp:ListItem Text="No" Value="No"></asp:ListItem>
</asp:RadioButtonList>
<asp:GridView ID="gvCustomers" runat="server" CssClass="Grid" AllowPaging="true" PageSize="10"
OnPageIndexChanging="OnPageIndexChanging">
</asp:GridView>
Namespaces
You will need to import the following namespaces.
C#
using System.IO;
using System.Data;
using System.Data.OleDb;
using System.Configuration;
VB.Net
Imports System.IO
Imports System.Data
Imports System.Data.OleDb
Imports System.Configuration
Uploading Excel file using C# and VB.Net
When Upload Button is clicked, the FileUpload is checked for file and if it has file then the name and extension of the file are determined using GetFileName and GetExtension methods respectively of the Path class.
The selected file is saved in the Folder (Directory) named Files.
Finally, the ImportToGrid method (explained later) is called with filePath, extension and selected RadioButton text as parameter.
C#
protected void OnUpload(object sender, EventArgs e)
{
if (fuUpload.HasFile)
{
string fileName = Path.GetFileName(fuUpload.PostedFile.FileName);
string extension = Path.GetExtension(fuUpload.PostedFile.FileName);
string filePath = Server.MapPath("~/Files/" + fileName);
fuUpload.SaveAs(filePath);
this.ImportToGrid(filePath, extension, rbHDR.SelectedItem.Text);
}
}
VB.Net
Protected Sub OnUpload(sender As Object, e As EventArgs)
If fuUpload.HasFile Then
Dim fileName As String = Path.GetFileName(fuUpload.PostedFile.FileName)
Dim extension As String = Path.GetExtension(fuUpload.PostedFile.FileName)
Dim filePath As String = Server.MapPath("~/Files/" & fileName)
fuUpload.SaveAs(filePath)
Me.ImportToGrid(filePath, extension, rbHDR.SelectedItem.Text)
End If
End Sub
Importing the Excel File Sheet to GridView control
Inside the
ImportToGrid method, the switch case statement is executed and the
Excel file extension is determined and based on the extension the connection string is built by replacing the placeholder.
Then, the name of the first sheet is determined and then the sheet data is read into a DataTable which is ultimately bound to the GridView control.
C#
private void ImportToGrid(string filePath, string extension, string isHDR)
{
string constr = "";
switch (extension)
{
case ".xls"://Excel 97-03
constr = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
break;
case ".xlsx"://Excel 07
constr = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
break;
}
constr = string.Format(constr, filePath, isHDR);
using (OleDbConnection connExcel = new OleDbConnection(constr))
{
using (OleDbCommand cmdExcel = new OleDbCommand())
{
using (OleDbDataAdapter oda = new OleDbDataAdapter())
{
using (DataTable dt = new DataTable())
{
cmdExcel.Connection = connExcel;
//Get the name of First Sheet.
connExcel.Open();
DataTable dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string sheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
connExcel.Close();
//Read Data from First Sheet.
connExcel.Open();
cmdExcel.CommandText = "SELECT * From [" + sheetName + "]";
oda.SelectCommand = cmdExcel;
oda.Fill(dt);
connExcel.Close();
//Bind Data to GridView.
gvCustomers.Caption = Path.GetFileName(filePath);
gvCustomers.DataSource = dt;
gvCustomers.DataBind();
}
}
}
}
}
VB.Net
Private Sub ImportToGrid(filePath As String, extension As String, isHDR As String)
Dim constr As String = ""
Select Case extension'Excel 97-03
Case ".xls"
constr = ConfigurationManager.ConnectionStrings("Excel03ConString").ConnectionString
Case ".xlsx" 'Excel 07
constr = ConfigurationManager.ConnectionStrings("Excel07ConString").ConnectionString
End Select
constr = String.Format(constr, filePath, isHDR)
Using connExcel As OleDbConnection = New OleDbConnection(constr)
Using cmdExcel As OleDbCommand = New OleDbCommand()
Using oda As OleDbDataAdapter = New OleDbDataAdapter()
Using dt As DataTable = New DataTable()
cmdExcel.Connection = connExcel
'Get the name of First Sheet.
connExcel.Open()
Dim dtExcelSchema As DataTable = 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 = "SELECT * From [" & sheetName &"]"
oda.SelectCommand = cmdExcel
oda.Fill(dt)
connExcel.Close()
'Bind Data to GridView.
gvCustomers.Caption = Path.GetFileName(filePath)
gvCustomers.DataSource = dt
gvCustomers.DataBind()
End Using
End Using
End Using
End Using
End Sub
Implement Paging in GridView
Inside the OnPageIndexChanging event handler, the filename is set to the Caption of GridView.
The PageIndex property of the GridView is updated with the new Page Number which was clicked.
Finally, the ImportToGrid method (explained earlier) is called with filePath, extension and selected RadioButton text as parameter and the GridView is again populated.
C#
protected void OnPageIndexChanging(object sender, GridViewPageEventArgs e)
{
string fileName = gvCustomers.Caption;
string extension = Path.GetExtension(fileName);
string filePath = Server.MapPath("~/Files/" + fileName);
gvCustomers.PageIndex = e.NewPageIndex;
this.ImportToGrid(filePath, extension, rbHDR.SelectedItem.Text);
}
VB.Net
Protected Sub OnPageIndexChanging(sender As Object, e As GridViewPageEventArgs)
Dim fileName As String = gvCustomers.Caption
Dim extension As String = Path.GetExtension(fileName)
Dim filePath As String = Server.MapPath("~/Files/" & fileName)
gvCustomers.PageIndex = e.NewPageIndex
Me.ImportToGrid(filePath, extension, rbHDR.SelectedItem.Text)
End Sub
Error
The following error occurs when you try to render a control such as GridView to HTML using the RenderControl method.
Server Error in '/ASP.Net' Application.
Control gvCustomers of type 'GridView' must be placed inside a form tag with runat=server.
Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.Exception Details:System.Web.HttpException: Control 'gvCustomers' of type 'GridView' must be placed inside a form tag with runat=server.
Solution
The solution to this problem is to override VerifyRenderingInServerForm event handler.
Screenshot
![Read and Import Excel Sheet into ASP.Net GridView Control]()
Downloads
