Easily load Excel WorkSheet to DataTable Using OLEDB

If you are in a hurry and you don't want to use Excel object you could load the Excel worksheet data into the DataTable using OLEDB. It is quick, easy and a simple method loading data. Here's how:

Check out the example code. You can download the project file at the end of this tutorial. It contain examples both in C# and VB.NET, Default.aspx.vb and Default2.aspx.cs

C# Version:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.OleDb;
 
public partial class Default2 : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        string filePath = Server.MapPath("~\\sample.xls");
        string conn = string.Format("Provider=Microsoft.Jet.OLEDB.4.0; data source={0}; Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";", filePath);
        DataTable dt = new DataTable();
        string query = string.Format("SELECT * FROM [{0}$]", "BulkUpload");
 
        System.Data.OleDb.OleDbConnection MyConnection = new System.Data.OleDb.OleDbConnection(conn);
 
        using (OleDbDataAdapter myCommand = new OleDbDataAdapter(query, conn))
        {
 
            //Fill the data table with excel data
            myCommand.Fill(dt);
 
            //Close the connection
            MyConnection.Close();
 
            MyConnection = new System.Data.OleDb.OleDbConnection();
 
            for (int x = 1; x <= dt.Rows.Count - 1; x++)
            {
                DataRow r = dt.Rows[x];
 
                //Now loop through the data to process the data.
            }
        }
 
    }
}
 
VB.NET Version:
 
Imports System.Data
Imports System.Data.OleDb
 
Partial Class _Default
    Inherits System.Web.UI.Page
 
 
    Public Sub New()
 
        Dim filePath As String = Server.MapPath("~\\sample.xls")
        Dim conn As String = String.Format("Provider=Microsoft.Jet.OLEDB.4.0; data source={0}; Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";", filePath)
        Dim dt As DataTable = New DataTable
        Dim query As String = String.Format("SELECT * FROM [{0}$]", "BulkUpload")
 
        Dim MyConnection As System.Data.OleDb.OleDbConnection = New OleDb.OleDbConnection(conn)
        Dim myCommand As System.Data.OleDb.OleDbDataAdapter = New System.Data.OleDb.OleDbDataAdapter(query, MyConnection)
 
 
        'Fill the data table with excel data
        myCommand.Fill(dt)
 
        'Close the connection
        MyConnection.Close()
 
        MyConnection = New System.Data.OleDb.OleDbConnection
 
        For x As Integer = 1 To dt.Rows.Count - 1
            Dim r As DataRow = dt.Rows(x)
 
            'Now loop through the data to process the data.
        Next
 
    End Sub
End Class

Download Files

Comments (3)

|59.90.189.xxx |2011-05-12 03:17:56 prakash - propplem in coneection string

Dim con As New Data.OleDb.OleDbConnection("Provider=sqloledb;Data Source=182.18.128.71;Integrated Security=SSPI;Initial Catalog=RanchiRealEstate;User Id=prakash;Password=123456;")

by usinf this connection string it alwaya gives error to open connection

|59.90.189.xxx |2011-05-12 03:18:42 prakash

Dim con As New Data.OleDb.OleDbConnection("Provider=sqloledb;Data Source=182.18.128.71;Integrated Security=SSPI;Initial Catalog=RanchiRealEstate;User Id=prakash;Password=123456;")

by this connection is not open what can i do

|27.33.185.xxx |2012-02-21 18:07:59 richardalgor - C# Excel

Some more C# excel automations is great.

< Prev		Next >