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.
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 following HTML Markup consists of:
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
PageSize – For permitting maximum number of rows to be displayed per page.
AllowPaging – For enabling paging in the GridView control.
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 and Reading the Excel Sheet
When the Upload button is clicked, the FileUpload is checked for file and if it has file then the name and extension of the file are fetched using GetFileName and GetExtension methods of Path class.
The selected file is saved in the Files Folder (Directory).
Finally, the ImportToGrid method 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.
The
Excel file extension is determined and based on the extension the connection string is built by replacing the placeholder and the
Excel file is read using the selected OLEDB driver.
Then, the name of the first sheet is determined and then the sheet data is read into a DataTable which ultimately is bound to the GridView control.
C#
private void ImportToGrid(string filePath, string extension, string isHDR)
{
string conString = "";
switch (extension)
{
case".xls": //Excel 97-03
conString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
break;
case".xlsx": //Excel 07
conString = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
break;
}
conString = string.Format(conString, filePath, isHDR);
using (OleDbConnection connExcel = new OleDbConnection(conString))
{
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 conString As String = ""
Select Case extension 'Excel 97-03
Case".xls"
conString = ConfigurationManager.ConnectionStrings("Excel03ConString").ConnectionString
Case".xlsx"'Excel 07
conString = ConfigurationManager.ConnectionStrings("Excel07ConString").ConnectionString
End Select
conString = String.Format(conString, filePath, isHDR)
Using connExcel As OleDbConnection = New OleDbConnection(conString)
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 is called with file Path, 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]()
Download
