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

Install System.Data.OleDb package

In order to install System.Data.OleDb library using Nuget, please refer my article Install System.Data.OleDb Nuget Package.
 
 

Connection Strings

Excel 97-2003

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=Yes'" />
 

Excel 2007 and higher

Excel 2007 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=Yes'" />
 
 

HTML Markup

The HTML Markup contains of following controls:
FileUpload – For uploading Excel file.
Button – For reading and importing the Excel file data.
 
GridView – For displaying the imported data.
<asp:FileUpload ID="fuUpload" runat="server" />
<asp:Button ID="btnUpload" runat="server" Text="Upload" OnClick="Upload" />
<hr />
<asp:GridView ID="gvCustomers" runat="server"></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
 
 

Reading the Excel Sheet using C# and VB.Net

When the Upload Button is clicked, the path of the Folder (Directory) where uploaded files will be saved is fetched.
Then, the Excel file is saved to a Folder (Directory) on Server's disk using SaveAs method of FileUpload control.
Based on the selected Excel file extension i.e. .xlx or .xlsx the connection string is built by replacing the placeholder and the saved Excel file is read using the selected OLEDB driver.
Next, the Schema of the Excel file is read and the Name of the first Sheet is determined as the name of the Table.
Finally, the data from the Excel sheet is read into a DataTable object which finally assigned to the DataSource property of the GridView and the GridView is populated.
C#
protected void Upload(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);
        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);
        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.DataSource = dt;
                        gvCustomers.DataBind();
                    }
                }
            }
        }
    }
}
 
VB.Net
Protected Sub Upload(sender As Object, ByVal 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)
        Dim constr As String = ""
        SelectCase extension
            Case".xls"'Excel 97-03
                constr = ConfigurationManager.ConnectionStrings("Excel03ConString").ConnectionString
            Case".xlsx"'Excel 07
                constr = ConfigurationManager.ConnectionStrings("Excel07ConString").ConnectionString
        End Select
        constr = String.Format(constr, filePath)
        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
                        connExcel.Open()
                        Dim dtExcelSchema As DataTable = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
 
                        'Get the name of First Sheet.
                        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.DataSource = dt
                        gvCustomers.DataBind()
                    End Using
                End Using
            End Using
        End Using
    End If
End Sub
 
 

Screenshots

The Excel File

Read and Import Excel Sheet using ADO.Net and C#
 

Imported data

Read and Import Excel Sheet using ADO.Net and C#
 
 

Downloads

Download Code