Prase Each Worksheet From Excel Worksheet Into DataSet Using C#

 By: Unknown    01:26     No comments

In this Post I will explain how to read each Worksheet From Excel File into a DataSet using C#.

using OLE DB Driver:-

OLEDB Driver(more information about OLEDB Click here) support Excel Reader Class to Read Excel Sheet in Easy Way..

Imports System
Imports System.Data.OleDb
Imports System.Reflection
Imports System.Runtime.InteropServices
Imports System.IO

Module Module1

Sub Main()
   
Try
   'in array list mention what are the worksheet u need to read
    Dim arr2 As String() = {"Camera", "DVD_Writer"}

    Dim path As String = System.IO.Path.GetFullPath("E:\ICT_RESOURCES.xls")
         
 Dim oledbConn As OleDbConnection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=2""")
           
oledbConn.Open()
Dim cmd As New OleDbCommand()
Dim oleda As New OleDbDataAdapter()
Dim ds As New DataSet()
ds.Clear()
     
For i As Integer = 0 To arr2.Count
  cmd.Connection = oledbConn
  cmd.CommandType = CommandType.Text
  cmd.CommandText = "SELECT * FROM [" & arr2(i) & "]"
  oleda = New OleDbDataAdapter(cmd)
  oleda.Fill(ds)

  For Each dr As DataRow In ds.Tables(0).Rows
   ' mention column name present in excel sheet
     Console.WriteLine(dr("bid").ToString + " " + dr("bookBy").ToString  )            
  Next
         
  Next
     
 Catch ex As Exception
 Console.WriteLine(ex.Message)
 End Try
 
  Console.ReadLine()
   
End Sub

End Module

Note:- 

Becoz of platform will get error like (microsoft.oledb.12.0 not registered  on local machine)

 If u Getting any error Like (microsoft.oledb.12.0 not registered  on local machine ) try to run or build your application in 32  bit platform.

 Other wise just download the AccessDatabaseEngine.exe  supported Drivers to run application in 64bit Platform  (install this driver in Your System to run application in 64bit platform)