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

8 ביולי 2008


Recently I've being asked by friend to show him a simple way to read/write data from/to MS Excel files. I found my old project with very simple method of working with excel files by using Microsoft.Jet.OLEDB.4.0 Provider (similar to MS Access). This is one of many methods to work with excel files.

Read data from Excel Sheet:

    "' <summary>
    "' Reads the data from excel file.
    "' </summary>
    "' <param name="strFilePath">The file path.</param>
    "' <param name="strSheetName">Name of the sheet.</param>
    "' <returns></returns>
    Public Function ReadDataFromExcelFile(ByVal strFilePath As String, ByVal strSheetName As String) As Data.DataSet
        If strFilePath Is Nothing Then Throw New NullReferenceException("strFilePath")
        If File.Exists(strFilePath) = False Then Throw New FileNotFoundException(strFilePath)
 
        If String.IsNullOrEmpty(strSheetName) Then Throw New Exception("Empty sheet name.")
 
        ' define con. params
        Dim str As String = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source={0};Extended Properties=""Excel 8.0;HDR=YES;""", strFilePath)
 
        ' init con.
        Dim cnn As New OleDb.OleDbConnection(str)
        ' open con. to xl file
        Try
            cnn.Open()
        Catch ex As Exception
            cnn.Dispose()
            Throw ex
        End Try
 
        ' query str. (like any ordinary SQL)
        Dim strSQL As String = String.Format("SELECT * FROM [{0}$];", strSheetName)
 
        ' init data adapter
        Dim da As New OleDb.OleDbDataAdapter(strSQL, cnn)
 
        ' init data set
        Dim ds As New DataSet(strSheetName)
 
        ' read xl data
        Try
            da.Fill(ds)
        Catch ex As Exception
            da.Dispose()
            ds.Dispose()
            cnn.Close()
            Throw ex
        End Try
 
        ' close connections and dispose objects
        da.Dispose()
        cnn.Close()
        cnn.Dispose()
 
        Return ds
    End Function

 

Write data to Excel Sheet:

    "' <summary>
    "' Saves the data to excel file.
    "' </summary>
    "' <param name="strFilePath">The file path.</param>
    "' <param name="strSheetName">Name of the sheet.</param>
    "' <param name="data">The sheet data.</param>
    Public Sub SaveDataToExcelFile(ByVal strFilePath As String, ByVal strSheetName As String, ByVal data As Data.DataSet)
        If strFilePath Is Nothing Then Throw New NullReferenceException("strFilePath")
        If String.IsNullOrEmpty(strSheetName) Then Throw New Exception("Empty sheet name.")
        If data Is Nothing Then Throw New NullReferenceException("data")
 
        ' define con. params
        Dim str As String = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source={0};Extended Properties=""Excel 8.0;HDR=YES;""", strFilePath)
 
        ' init con.
        Dim cnn As New OleDb.OleDbConnection(str)
 
        ' init cmd.
        Dim cmd As New OleDb.OleDbCommand()
        cmd.Connection = cnn
 
        ' open con. to xl file
        Try
            cnn.Open()
        Catch ex As Exception
            cmd.Dispose()
            cnn.Close()
            cnn.Dispose()
            Throw ex
        End Try
 
        ' if cur. xl file contains sheet with same name, delete the old one
        Try
            ' query str. (like any ordinary SQL)
            cmd.CommandText = String.Format("DROP TABLE [{0}];", strSheetName)
            ' exec. query
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            Console.WriteLine(ex)
        End Try
 
        ' init str. builders
        Dim bColNames As New System.Text.StringBuilder
        Dim bColDefs As New System.Text.StringBuilder
 
        ' read coloumn names and define query params
        For i As Integer = 0 To data.Tables(0).Columns.Count – 1
            bColDefs.AppendFormat("{0} nvarchar(255),", data.Tables(0).Columns(i).ColumnName)
            bColNames.AppendFormat("{0},", data.Tables(0).Columns(i).ColumnName)
        Next
 
        ' remove last ',' (comma)
        bColDefs.Remove(bColDefs.Length – 1, 1)
        bColNames.Remove(bColNames.Length – 1, 1)
 
        ' query str. (like any ordinary SQL)
        cmd.CommandText = String.Format("CREATE TABLE [{0}] ({1});", strSheetName, bColDefs)
 
        ' exec. query
        Try
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            cmd.Dispose()
            cnn.Close()
            cnn.Dispose()
            Throw ex
        End Try
 
        Try
            ' insert values from 'data' into sheet in opened xl file
            For i As Integer = 0 To data.Tables(0).Rows.Count – 1
                ' init str. builder
                Dim bVals As New Text.StringBuilder
 
                ' collect row vals. from src. sheet
                For j As Integer = 0 To data.Tables(0).Rows(i).ItemArray.Length – 1
                    bVals.AppendFormat("'{0}',", data.Tables(0).Rows(i).ItemArray(j))
                Next
 
                ' remove last ',' (comma)
                bVals.Remove(bVals.Length – 1, 1)
 
                ' query str. (like any ordinary SQL)
                cmd.CommandText = String.Format("INSERT INTO [{0}$] ({1}) " & _
                "VALUES({2});", strSheetName, bColNames, bVals)
 
                ' exec. query
                cmd.ExecuteNonQuery()
            Next
        Catch ex As Exception
            cmd.Dispose()
            cnn.Close()
            cnn.Dispose()
            Throw ex
        End Try
 
        cmd.Dispose()
        cnn.Close()
        cnn.Dispose()
    End Sub

 

Download example project from here.

Hope this post was helpful 🙂

 

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>

*

4 comments

  1. Ben11 ביולי 2008 ב 15:24

    Can this method be used with Excel 2003 file format as well as 2007 file format?

    Thanks,
    Ben.

    Reply
  2. Maxim12 ביולי 2008 ב 11:08

    I used this method with excel files from office versions 2000/xp/2003. I didn't test it yet on office 2007, but think that it will work.

    Reply
  3. tivoli6 במאי 2013 ב 10:16

    I require to admit that that is 1 wonderful insight. It surely gives a company the opportunity to have in around the ground floor and genuinely take part in making a thing unique and tailored to their needs. tivoli http://louisvuittontivolipmgmbags.webs.com/

    Reply