In this article I will explain with an example, how to import data from Excel to GridView using OLEDB and ADO.Net in ASP.Net with C# and VB.Net
 
 

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

Following are the Excel 97-2003 and Excel 2007 use different providers I Have placed two connection strings in the Web.Config.
Here, you will notice there are two connection strings one for Excel 97 – 2003 format which uses Microsoft Jet driver and another one is for Excel 2007 format which uses Microsoft Ace driver.
I have used Placeholders for Data Source {0} and the HDR {1} property so that I can easily replace fill it in the front end based on the Excel File Selected and the Headers property selected by the user through the checkbox 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 HTML Markup consists of following controls:
FileUpload – For selecting Excel file.
Button – For upload Excel file.
The Button has been assigned with an OnClick event handler.
Label – for display text.
RadioButtonList - For the user to select whether headers are present or not by default Yes.
GridView – For displaying data.
<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">
</asp:GridView>
 
 

Uploading and Reading the Excel Sheet

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 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);
    }
}
 
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
Protected Sub OnUpload(sender As Object, e As EventArgs)
    If fuUpload.HasFileThen
        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
 
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
 
 

Screenshots

Excel File read with Headers

Import data from Excel to GridView in ASP.Net
 

Excel File read without Headers

Import data from Excel to GridView in ASP.Net
 
 

Error

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