In this article I will explain with an example, how to display Excel file in Web Page in ASP.Net using C# and VB.Net.
The Excel File will be read into a DataTable using OLEDB and ADO.Net and the DataTable will be used to display the Excel File in ASP.Net GridView.
 
 
Concept
1. User browses and selects an Excel Workbook.
2. User selects whether Header row is present in Excel Sheet or not using radio buttons.
3. User uploads the Excel Workbook.
4. Uploaded File is read by the application and displayed on the web page using GridView.
5. GridView has paging enabled so that user can view the records easily.
 
 
Connection Strings
Since there Excel 97-2003 and Excel 2007 use different providers two connection strings keys are used in the Web.Config one for Excel 97 – 2003 format which uses Microsoft Jet driver and another one for Excel 2007 format which uses Microsoft Ace driver.
Placeholders are used for Data Source {0} and the HDR {1} property so that easily replace it in the front end based on the Excel File selected and the Headers property selected by the user through the RadioButtonList respectively.
<connectionStrings>
 <add name ="Excel03ConString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'"/>
 <add name ="Excel07ConString" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR={1}'"/>
</connectionStrings >
 
 
HTML Markup
The following HTML Markup contains of a FileUpload Control, a Button which will be used to upload the Excel File, RadioButtonList for the user to select whether headers are present and a GridView for which AllowPaging property set to true.
<asp:FileUpload ID="FileUpload1" runat="server" />
<asp:Button ID="btnUpload" runat="server" Text="Upload" OnClick="btnUpload_Click" />
<br />
<asp:Label ID="Label1" runat="server" Text="Has Header ?"></asp:Label><br />
<asp:RadioButtonList ID="rbHDR" runat="server" RepeatLayout = "Flow">
    <asp:ListItem Text="Yes" Value="Yes" Selected="True"></asp:ListItem>
    <asp:ListItem Text="No" Value="No"></asp:ListItem>
</asp:RadioButtonList>
<asp:GridView ID="GridView1" runat="server" OnPageIndexChanging="PageIndexChanging"
    AllowPaging="true">
</asp:GridView>
 
 
Display Excel file in Web Page
When the Upload button is clicked, File will be saved to a Folder (Directory) on Server's disk whose path is defined in the AppSettings section in the Web.Config using the following key.
<appSettings>
 <add key ="FolderPath" value ="Files/"/>
</appSettings >
 
Once the File is saved in the Folder (Directory) on Server's disk, the Import_To_Grid method called.
Inside the Import_To_Grid function, the saved Excel file is read using the selected OLEDB driver and the selected choice of Headers or without Headers.
Then the Schema of the Excel file is read and the Name of the first Sheet is determined.
Finally the data from the Excel sheet is read into a DataTable which is used to populate the GridView.
C#
protected void btnUpload_Click(object sender, EventArgs e)
{
    if (FileUpload1.HasFile)
    {
        string FileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
        string Extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
        string FolderPath = ConfigurationManager.AppSettings["FolderPath"];
 
        string FilePath = Server.MapPath(FolderPath + FileName);
        FileUpload1.SaveAs(FilePath);
        Import_To_Grid(FilePath, Extension, rbHDR.SelectedItem.Text);
    }
}
private void Import_To_Grid(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);
    OleDbConnection connExcel = new OleDbConnection(conStr);
    OleDbCommand cmdExcel = new OleDbCommand();
    OleDbDataAdapter oda = new OleDbDataAdapter();
    DataTable dt = new DataTable();
    cmdExcel.Connection = connExcel;
 
    //Get the name of First Sheet
    connExcel.Open();
    DataTable dtExcelSchema;
    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
    GridView1.Caption = Path.GetFileName(FilePath);
    GridView1.DataSource = dt;
    GridView1.DataBind();
}
 
VB.Net
Protected Sub btnUpload_Click(ByVal sender As Object, ByVal e As System.EventArgs)
    If FileUpload1.HasFile Then
        Dim FileName As String = Path.GetFileName(FileUpload1.PostedFile.FileName)
        Dim Extension As String = Path.GetExtension(FileUpload1.PostedFile.FileName)
        Dim FolderPath As String = ConfigurationManager.AppSettings("FolderPath")
 
        Dim FilePath As String = Server.MapPath(FolderPath + FileName)
        FileUpload1.SaveAs(FilePath)
        Import_To_Grid(FilePath, Extension, rbHDR.SelectedItem.Text)
    End If
End Sub
Private Sub Import_To_Grid(ByVal FilePath As String, ByVal Extension As String, ByVal isHDR As String)
    Dim conStr As String = ""
    Select Case Extension
        Case ".xls"
            'Excel 97-03
            conStr = ConfigurationManager.ConnectionStrings("Excel03ConString").ConnectionString
            Exit Select
        Case ".xlsx"
            'Excel 07
            conStr = ConfigurationManager.ConnectionStrings("Excel07ConString").ConnectionString
            Exit Select
    End Select
    conStr = String.Format(conStr, FilePath, isHDR)
 
    Dim connExcel As New OleDbConnection(conStr)
    Dim cmdExcel As New OleDbCommand()
    Dim oda As New OleDbDataAdapter()
    Dim dt 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()
    cmdExcel.CommandText = "SELECT * From [" & SheetName & "]"
    oda.SelectCommand = cmdExcel
    oda.Fill(dt)
    connExcel.Close()
 
    'Bind Data to GridView
    GridView1.Caption = Path.GetFileName(FilePath)
    GridView1.DataSource = dt
    GridView1.DataBind()
End Sub
 
 
Pagination in GridView
The Paging is implemented using the OnPageIndexChanging event handler, the Excel file is again read and the Page Index of the GridView is updated based on the selected Page. 
C#
protected void PageIndexChanging(object sender, GridViewPageEventArgs e)
{
    string FolderPath = ConfigurationManager.AppSettings["FolderPath"] ;
    string FileName = GridView1.Caption;
    string Extension = Path.GetExtension(FileName);
    string FilePath = Server.MapPath(FolderPath + FileName);
 
    Import_To_Grid(FilePath, Extension, rbHDR.SelectedItem.Text); 
    GridView1.PageIndex = e.NewPageIndex;
    GridView1.DataBind(); 
}
 
VB.Net
Protected Sub PageIndexChanging(ByVal sender As Object, ByVal e As GridViewPageEventArgs)
   Dim FolderPath As String = ConfigurationManager.AppSettings("FolderPath")
   Dim FileName As String = GridView1.Caption
   Dim Extension As String = Path.GetExtension(FileName)
   Dim FilePath As String = Server.MapPath(FolderPath + FileName)
 
   Import_To_Grid(FilePath, Extension, rbHDR.SelectedItem.Text)
   GridView1.PageIndex = e.NewPageIndex
   GridView1.DataBind()
End Sub
 
 
Screenshots
Excel File read with Headers
Display Excel file in Web Page in ASP.Net using C# and VB.Net
 
Excel File read without Headers
Display Excel file in Web Page in ASP.Net using C# and VB.Net
 
 
Errors
The following error (exception) will occur if you try to read the Excel file when it is open or used by some other application.
Server Error in 'ASP.Net' Application.
 
The process cannot access the file 'F:\ReadExcelToGrid\Files\Excel07.xlsx' because it is being used by another process.
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.IO.IOException: The process cannot access the file 'F:\ReadExcelToGrid\Files\Excel07.xlsx' because it is being used by another process.
 
 
Downloads
Download Code