Read Sheets Name from Excel using OLEDB Connection

In this article I am going to explain how the read the sheets name form excel using OLEDB connection

‘Declarations

Dim connect As System.Data.OleDb.OleDbConnection = Nothing

Dim dtExcelSheets As Data.DataTable = Nothing

Dim index As Integer = 0

Dim excelSheetNames As [String]() = Nothing

'this connection string will change the excel file into the file which we will search

Dim strconnection As String

strconnection = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + strPath + "; Extended Properties=Excel 8.0;" 

Try

   connect = New System.Data.OleDb.OleDbConnection(strconnection)

   connect.Open()

  'get the data table containing the schema

   dtExcelSheets = connect.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)

   'check if datatable got the correct schema

    If dtExcelSheets Is Nothing Or dtExcelSheets.Rows.Count = 0 Then

     Return Nothing

    ElseIf dtExcelSheets.Rows.Count <> 0 Then

     excelSheetNames = New [String](dtExcelSheets.Rows.Count - 1) {}

    'add the sheet names to the string array

     For Each row As Data.DataRow In dtExcelSheets.Rows

      excelSheetNames(index) = row("Table_Name").ToString()

      index += 1

     Next

    End If

Catch ex As Exception

        Finally

        connect.Close()          

End Try

Using this code you can retrieve sheet name from the excel.

I hope this article will be very helpful to all. Thanks for reading this article.

“Keep reading and share the knowledge”  

“Grow more trees to save the Earth”

Excel to Dataset using OLEDB Connection

There is an easy way to fetch the data from Excel using OLEDB connection. In this article I am going to explain how the fetch the data from the Excel using OLEDB.

You can use this code to retrieve the data from Excel to Dataset

Dim strconnection As String

Dim dadap As New OleDbDataAdapter

Dim ds As New DataSet

Dim excelSheetName As String

'Sheet Name in work book

excelSheetName = "Summary Sheet$"

'Connection string for retrieving the data from the Excel

strconnection = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + strpath + "; Extended Properties=Excel 8.0;"

dadap = New OleDbDataAdapter("SELECT * FROM [" + excelSheetName +]", strconnection)

'Fill data into the Dataset

dadap.Fill(ds, "sheet1")

datagridview.datasource= ds.Tables(0)

Here I have used OLEDB objects. After Exection this lines of code you will get the data from DataGridview.

Note: This connection string works only for .xls format that means before MS Office 2007. You need change the connection if your going to work for .xlsx format, and the connection string will be

strconnection = "Provider =Microsoft.ACE.OLEDB.12.0; Data Source =" + strPath + "; Extended Properties =Excel 12.0"

Other than there are some parameter you can add to the connection string . but this parameter are not mandatory 

“HDR=Yes;” - indicates that the first row contains columnnames, not data.

 “HDR=No;” - indicates the opposite. 

“IMEX=1” - is a safer way to retrieve data for mixed data columns. Consider the scenario that one Excel file might work fine cause that file's data causes the driver to guess one data type while another file, containing other data, causes the driver to guess another data type. This can cause your app to crash. 

If we comporise all parametes we will get

strconnection = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + strpath + ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1;"""

Note: when we use HRD and IMEX parameter we need to supply with double quotes “” otherwise it will throw error. And HRD and IMEX is same for .xls and .xlsx

I hope this article will be very helpful to all. Thanks for reading this article.

“Keep reading and share the knowledge”  

“Grow more trees to save the Earth”

Remove Special Characters from the String

You can remove the special character by comparing character by character; otherwise you can use Regex Class. By using Regex class, instead of remove the character we can mention what character that string should contain.  I have created a method that wills the return the string without any special character.

VB.NET

Namespace required: Imports System.Text.RegularExpressions

Public Function RemoveSpecialCharacter(ByVal str As String)

  Dim strresult As String = ""

  strresult =Regex.Replace(str, "[^A-Z,0-9,a-z]", "")

  Return strresult

End Function

C#

Namespace required: using System.Text.RegularExpressions ; 

public string RemoveSpecialCharacter(string str)

{

  string strresult = "";

  strresult =Regex.Replace (str,"[^A-Z,0-9,a-z]","");

  return strresult;

}

Note : I am Added alphanumeric characters only. If you want add to any other like / or \ you can add to the expression

I hope this article will be very helpful to all. Thanks for reading this article.

“Keep reading and share the knowledge”  

“Grow more trees to save the Earth”