Quick Tip: How to work with Excel files in VB.Net (Update)

23 בפברואר 2009

This is an update for previous post.

Added new function to read entire content of Excel File:

      "' <summary>
      "' Reads the data from excel file.
      "' </summary>
      "' <param name="strFilePath">The file path.</param>
      "' <returns>DataSet</returns>
      Public Shared Function ReadDataFromExcelFile(ByVal strFilePath As String) As DataSet
            If strFilePath Is Nothing Then Throw New NullReferenceException("strFilePath")
            If Not File.Exists(strFilePath) Then Throw New FileNotFoundException(strFilePath) 

            ' copy source excel file to temp. folder (because the original file can be locked by other process)
            Dim tmpFolder As String = Environment.GetFolderPath(Environment.SpecialFolder.InternetCache)
            If Not Directory.Exists(tmpFolder) Then tmpFolder = Environment.CurrentDirectory 

            Dim tmpPath As String = _
            Path.Combine(tmpFolder, Path.GetFileNameWithoutExtension(strFilePath) & ".tmp") 

            File.Copy(strFilePath, tmpPath, True)
            strFilePath = tmpPath 

            ' "connect" to excel using jet engine
            Dim selectConnection As New OleDbConnection(String.Format( _
             "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};" & _
             "Extended Properties=""Excel 8.0;HDR=YES;""", strFilePath))

            Try
                  selectConnection.Open()
            Catch exception1 As Exception
                  If selectConnection IsNot Nothing Then selectConnection.Dispose()
                  Throw exception1
            End Try 

            ' get db schema from excel file (includes list of excel sheets)
            Dim ds As New DataSet
            Dim oleDbSchemaTable As DataTable = _
            selectConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing) 

            Dim enumerator As IEnumerator = Nothing
            Try
                  enumerator = oleDbSchemaTable.Rows.GetEnumerator
                  Do While enumerator.MoveNext
                        Dim current As DataRow = DirectCast(enumerator.Current, DataRow)
                        Dim strTblName As String = current.Item("TABLE_NAME").ToString
                        Dim adapter As New OleDbDataAdapter( _
                        String.Format("SELECT * FROM [{0}];", strTblName), selectConnection)
                        Dim dsTable As New DataSet(strTblName) 

                        Try
                              adapter.Fill(dsTable)
                              If dsTable.Tables.Count < 1 Then Continue Do 

                              Dim table2 As DataTable = dsTable.Tables.Item(0) 

                              ' format sheet name (replace chars)
                              table2.TableName = strTblName.Replace("$", String.Empty). _
                                Replace("#", ".").TrimStart("'").TrimEnd("'").Trim() 

                              ds.Tables.Add(table2.Copy)
                        Catch exception3 As Exception
                              If adapter IsNot Nothing Then adapter.Dispose()
                              If adapter IsNot Nothing Then dsTable.Dispose()
                              Throw exception3
                        Finally
                              If adapter IsNot Nothing Then adapter.Dispose()
                              If dsTable IsNot Nothing Then dsTable.Dispose()
                        End Try
                  Loop
            Finally
                  If enumerator IsNot Nothing Then
                        If TypeOf enumerator Is IDisposable Then
                              TryCast(enumerator, IDisposable).Dispose()
                        End If
                  End If
            End Try

            If selectConnection IsNot Nothing Then
                  selectConnection.Close()
                  selectConnection.Dispose()

            End If

            Return ds
      End Function

Project sources can be downloaded from here download sources.

Enjoy 🙂

PS
I'm not responsible for any damage that may be caused from using of this post or shared project sources. Blah, blah, blah…

Technorati Tags: ,,
Add comment
facebook linkedin twitter email

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

6 comments

  1. SEO Services16 ביולי 2009 ב 15:33

    Thanks for sharing very useful tips.

    Reply
  2. Josey30 ביוני 2010 ב 21:59

    It cuts off long column names to a length of 64 characters. Is there a way around this???

    Reply
  3. Laflamme28 במרץ 2013 ב 13:43

    Thanks for finally talking about >Quick Tip: How to work
    with Excel files in VB.Net (Update) – Maxim Reply

  4. Lively28 במרץ 2013 ב 16:24

    Do you have any video of that? I'd love to find out some additional information.

    Reply
  5. Albrecht8 באפריל 2013 ב 13:35

    Great delivery. Sound arguments. Keep up the amazing effort.

    Reply
  6. Demarco22 באפריל 2013 ב 18:53

    Hmm is anyone else experiencing problems with the images on this blog loading?

    I'm trying to figure out if its a problem on my end or if it's the blog.

    Any suggestions would be greatly appreciated.

    Reply