Read excel to dataset

This sample show how to read data from excel file by using Microsoft.Jet.OLEDB.

More information about excel connectionstring see http://www.connectionstrings.com/excel

Let's sample code.

Private Function ReadExcelFile(ByVal filePath As String, ByVal fileName As String) As DataSet

'' Excel 2003

Dim sConn As String = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}", filePath + fileName)

sConn += ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1;"""

'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";

Dim sSql As String = "SELECT * FROM [Sheet1$]"

'Select excel range command

' Dim sSql As String = "SELECT * FROM [Sheet1$A1:D3]"

Dim pFile As New FileInfo(fileName)

If (pFile.Extension.ToLower() = ".xlsx") Then

' Excel 2007

sConn = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0}", filePath + fileName)

sConn += ";Extended Properties=""Excel 12.0 Xml;HDR=YES;"""

End If

Dim ds As New DataSet

Dim pConn As New OleDbConnection(sConn)

Try

pConn.Open()

Dim pCmd As New OleDbCommand()

pCmd.Connection = pConn

pCmd.CommandText = sSql

Dim pAdp As New OleDbDataAdapter()

pAdp.SelectCommand = pCmd

pAdp.Fill(ds)

Catch ex As Exception

Return Nothing

Finally

pConn.Close()

End Try

Return ds

End Function

Note

If found error Could not find installable ISAM that mean your connectionstring was wrong.

FileInfo Class

Use the FileInfo class for typical operations such as copying, moving, renaming, creating, opening, deleting, and appending to files

Many of the FileInfo methods return other I/O types when you create or open files. You can use these other types to further manipulate a file. For more information, see specific FileInfo members such as Open, OpenRead, OpenText, CreateText, or Create.

Sample Code

Class FileManagement
Private m_pFile As IO.FileInfo
Private m_sTempFile As String = "C:\Test.Txt"


Public Sub SampleCreateFile()
m_pFile = New IO.FileInfo(m_sTempFile)

' Check exist file name
If Not m_pFile.Exists Then
Dim objStream As IO.StreamWriter = m_pFile.CreateText()

objStream.WriteLine("Welcome to My Blog")
objStream.WriteLine("Thank you")

objStream.Flush()
objStream.Close()
objStream.Dispose()
End If

End Sub

Public Sub SampleReadFile()
m_pFile = New IO.FileInfo(m_sTempFile)

If m_pFile.Exists Then
'Read Text
Dim objStream As IO.StreamReader = m_pFile.OpenText()

' Peek returns the next aviable charactor
While objStream.Peek() >= 0
Console.WriteLine(objStream.ReadLine)
End While

End If
End Sub

Public Sub SampleFileInfomation()

' Use class file to get file attributes

' Get Time
Console.WriteLine(IO.File.GetCreationTime(Me.m_sTempFile).ToLongTimeString())
Console.WriteLine(IO.File.GetLastAccessTime(Me.m_sTempFile).ToLongTimeString())
Console.WriteLine(IO.File.GetLastWriteTime(Me.m_sTempFile).ToLongTimeString())


' Set Time a
IO.File.SetCreationTime(m_sTempFile, DateTime.Now)
IO.File.SetLastAccessTime(m_sTempFile, DateTime.Now)
IO.File.SetLastWriteTime(m_sTempFile, DateTime.Now)

End Sub

Public Sub SampleOther()
m_pFile = New IO.FileInfo(m_sTempFile)

' Copy File
m_pFile.CopyTo("C:\test2.txt", True)

' Move File
m_pFile.MoveTo("C:\testMove.txt")

' Delete
m_pFile = New IO.FileInfo("C:\test2.txt")
m_pFile.Delete()
End Sub
End Class