Introduction

You will find three different techniques to import-export data between Microsoft Excel and SQL Server/SQL Azure using Visual Studio Tools for Office solutions and useful developer tips.

You may download a working example to test the techniques with a little time.

Table of Contents

Excel-SQL Server Import-Export Approaches

Excel-SQL Server Import-Export includes at least the three typical tasks:

  1. SQL Server data import to Excel.
  2. SQL Server data refresh in Excel.
  3. Excel data export to SQL Server.

The three basic approaches exists for import-export implementation:

  1. Use of a native Excel ListObject object with QueryTable connected to a database table.
  2. Use of a native Excel Range object with fully programmed control.
  3. Use of a Visual Studio Tools for Office ListObject object with DataSource connected to a database table.

All typical tasks can be implemented using any approach:

Excel QueryTable Excel Range VSTO ListObject
SQL Server data import to Excel yes yes yes
SQL Server data refresh in Excel yes yes yes
Excel data export to SQL Server yes yes yes

Each approach requires the coding and has the both advantages and disadvantages.

Import-Export using Excel QueryTable Object

Advantages of the use of QueryTable object:

  • A native Excel object is used.
    So users feel a "native" interface with extended features.
  • A workbook can be used without VSTO customization for data refresh as a regular workbook.

Disadvantages of the use of QueryTable object:

  • The QueryTable object does not support the data change tracking features.
    So a developer should code the logic for server data update.

The simplest way to update server data is to export the entire Excel data to a temporary database table and then use a stored procedure to update a source database table using the temporary data.

The Excel part of an application is quite universal. The business logic is implemented in a database.

Import-Export using Excel Range Object

Advantages of the use of Excel Range object:

  • Full control over the imported data, maybe.

Disadvantages of the use of Excel Range object:

  • A program data refresh is required.
    So users should use a non-standard action for a standard feature.
  • The QueryTable object does not support the data change tracking features.
    So a developer should code the logic for server data update.

The way to solve the data change task is the same as described above for the QueryTable object.

Import-Export using VSTO ListObject Object

Advantages of the use of VSTO ListObject object:

  • A ready functionality of the data changes tracking and a data source update.
  • A ready functionality of the data constraints and data relations validation.

Disadvantages of the use of VSTO ListObject object:

  • A program data refresh is required.
    So users should use a non-standard action for a standard feature.
  • An additional coding is required to save and restore VSTO and .NET objects between workbook Close and Open events as Excel does not store this information.

This approach is very similar to a traditional .NET data application programming.

The most complicated task is a data store in a closed workbook which has no standard solutions.

To top

About the Example

You may download a working example of described import-export techniques.

The example is a Visual Studio 2010 document-level customization project.

On the screenshot below the three data tables has been inserted using different methods.
The ribbon is sensitive to the selected cell and enables only available options.

Excel-SQL Server Import-Export using VSTO Example

To top

Developer's Notes

If you only start a project, you should make some technical decisions.

I would like to share my personal views on some aspects to help you.

Application-level Add-in vs. Document-level Customization

The decision about the Visual Studio project type depends on the project task.

Start with a good MSDN articles:

Visual Basic vs. CSharp

I like the both languages.

However, I strongly recommend using Visual Basic for Microsoft Office projects even you are new to VB.

The advantages of Visual Basic:

  1. You may use the Excel Macro Recorder to build a code sample.
    Then you can change the sample code to a working one with a little time.
  2. You may find on the Internet and adapt a huge amount of VBA code for different tasks as VBA has a long history.
  3. You may test your ideas right in the Excel Visual Basic Editor.

My personal productivity increases significantly when I switch to VB for Microsoft Office projects.

Visual Studio 2010 vs. Visual Studio 2008

Visual Studio 2010 is perfect for Microsoft Office projects unlike Visual Studio 2008.

In addition .NET Framework 4.0 is available in vs. 2010 only.

.NET Framework 4.0 vs. 3.5

.NET Framework 4.0 is rather different from .NET Framework 3.5.

See the difference in the MSDN article:
Migrating Office Solutions to the .NET Framework 4

I think that .NET Framework 4.0 is the best solution.

To top

Microsoft Excel Configuration before the Project Start

If access to the VBA project object model is not trusted you receive the following error message when you create a VSTO project:

Visual Studio Office Project - Trust Access VBA Project Error

Use the following configuration steps before the project start.

Microsoft Excel 2007 configuration steps:

  • Office Button
  • Excel Options
  • Trust Center
  • Macro Settings
  • Trust Access to the VBA project object model

Microsoft Excel 2010 configuration steps:

  • File
  • Options
  • Trust Center
  • Trust Center Settings
  • Macro Settings
  • Trust Access to the VBA project object model

To top

Import-Export using Native Excel Objects: QueryTable and Range

To use the Excel ListObject object with the QueryTable object connected to a database table:

  1. Create a ListObject using CreateQueryTable function.
  2. Use the Excel standard feature to refresh data.
  3. Use ExportRangeToDatabase function to export data to a database table.

To use the Excel Range object:

  1. Insert database table data to a range using the ImportSQLtoRange function.
  2. Use the same function to refresh data.
  3. Use ExportRangeToDatabase function to export data to a database table.

Data export requires additional stored procedures to update database table data. See the ExportRangeToDatabase function description.

Function CreateQueryTable

The function creates an Excel native ListObject with QueryTable connected to the OLE DB data source specified by the connectionString.

The result is nearly the same as a result of the standard Excel connection dialog.

Public Function CreateQueryTable(ByVal connectionString As String, _
    ByVal commandText As String, ByVal target As Excel.Range) As Integer

    On Error Resume Next

    Dim ws As Excel.Worksheet = target.Worksheet

    If Not target.ListObject Is Nothing Then
        ws.ShowAllData()               ' Excel has a bug of the filtered ListObject deletion
        target.ListObject.Delete()
    End If

    ' Excel QueryTable requires "OLEDB;" at the start of the connection string
    connectionString = "OLEDB;" & connectionString

    ' Connection string of QueryTable has length limit
    Dim source(1) As Object
    source(0) = Mid(connectionString, 1, 200)
    source(1) = Mid(connectionString, 201)

    Dim lo As Excel.ListObject = _
        ws.ListObjects.Add(SourceType:=0, source:=source, Destination:=target.Cells(1, 1))

    ' lo.DisplayName = ws.Name & "_Table"

    With lo.QueryTable
        Select Case UCase(Left(Trim(commandText), 4))
            Case "EXEC", "SELE"
                .CommandType = Excel.XlCmdType.xlCmdSql
            Case Else
                .CommandType = Excel.XlCmdType.xlCmdTable
        End Select

        .CommandText = commandText
        .SavePassword = True
        '.RowNumbers = True
        .PreserveColumnInfo = True
        .PreserveFormatting = True
        .AdjustColumnWidth = False

        .Refresh(BackgroundQuery:=False)
    End With

    CreateQueryTable = 0

End Function

Function ImportSQLtoRange

The function inserts the query results to the target range.

Public Function ImportSQLtoRange(ByVal connectionString As String, _
    ByVal commandText As String, ByVal target As Excel.Range) As Integer

    Try
        Dim ws As Excel.Worksheet = target.Worksheet

        Dim arr(0, 0) As Object

        ExecuteOleDbDataReader(connectionString, commandText, AddressOf OleDbDataReaderToArray, arr)

        Dim rowCount As Long = arr.GetLength(1)
        Dim colCount As Integer = arr.GetLength(0)

        Dim r As Excel.Range   ' The complete target range with arr dimensions
        With ws
            r = .Application.Range( _
                .Cells(target.Row, target.Column), _
                .Cells(target.Row + rowCount - 1, target.Column + colCount - 1) _
            )
        End With

        ' This method works very fast
        r.Value = ws.Application.WorksheetFunction.Transpose(arr)

        ws.ListObjects.Add(Excel.XlListObjectSourceType.xlSrcRange, r, , Excel.XlYesNoGuess.xlYes)

        ImportSQLtoRange = 0

    Catch ex As Exception
        ImportSQLtoRange = 1
        LastErrorMessage = ex.Message
    End Try

End Function

Code comments:

  • LastErrorMessage is a global variable that contains the last exception message if the function result is not zero.
  • ExecuteOleDbDataReader is a function that processes the result OleDBDataReader using the OleDbDataReaderToArray delegate function and arr as input-output object.
    See description below.
  • The query results are read into an array, and the array values are inserted to a range using Range.Value property very fast.
  • In the end, the target range is converted to a ListObject.

Function ExportRangeToDatabase

The function exports the sourceRange data to a table with the tableName.

The optional beforeSQL is executed before the export, and the optional afterSQL is executed after the export.

The common logic of the export process:

  1. Delete all data from a temporary import table.
  2. Export Excel data to the empty temporary import table.
  3. Update desired tables from the temporary import table data.

Specially developed stored procedures are used in the first and third steps.
Moreover, a universal code is used to transfer Excel data to a destination table.

Public Function ExportRangeToDatabase(ByVal sourceRange As Excel.Range, _
    ByVal connectionString As String, ByVal tableName As String, _
    Optional ByVal beforeSQL As String = "", _
    Optional ByVal afterSQL As String = "") As Integer

    Dim commandText As String = "SELECT * FROM " & tableName
    Dim cmd As OleDbCommand

    Try
        Using connection As New OleDbConnection(connectionString)

            If beforeSQL > "" Then
                cmd = New OleDbCommand(beforeSQL, connection)
                Try
                    cmd.CommandType = CommandType.Text
                    connection.Open()
                    cmd.ExecuteNonQuery()
                Finally
                    If Not cmd Is Nothing Then : cmd.Dispose() : End If
                End Try
            End If

            Dim dataAdapter As New OleDbDataAdapter(commandText, connection)
            Try
                Dim dataTable As New DataTable
                Try
                    dataAdapter.Fill(dataTable)

                    Dim builder As OleDbCommandBuilder =  _
                        New OleDbCommandBuilder(dataAdapter)
                    Try
                        dataAdapter.InsertCommand = builder.GetInsertCommand()

                        ' Column mappings

                        Dim rangeFields(100) As Integer
                        Dim tableFields(100) As Integer
                        Dim tableFieldTypes(100) As String

                        Dim exportFieldsCount As Integer
                        exportFieldsCount = 0

                        Dim col As Integer
                        Dim index As Integer

                        ' Set the range before .Match call!
                        Dim rangeHeaders As Excel.Range = sourceRange.Rows(1)

                        Dim cellFormat As String

                        For col = 0 To dataTable.Columns.Count - 1
                            index = sourceRange.Application.Match( _
                                dataTable.Columns(col).ColumnName, rangeHeaders, 0)
                            If index > 0 Then
                                exportFieldsCount = exportFieldsCount + 1
                                rangeFields(exportFieldsCount) = index
                                tableFields(exportFieldsCount) = col
                                tableFieldTypes(exportFieldsCount) = _
                                    dataTable.Columns(col).DataType.ToString

                                ' Transformation for the Time datatype
                                If tableFieldTypes(exportFieldsCount) = _
                                    "System.String" Then
                                    cellFormat = _
                                        sourceRange.Cells(2, index).NumberFormat
                                    If cellFormat.Contains(":") Then
                                        tableFieldTypes(exportFieldsCount) = "Time"
                                    End If
                                End If
                            End If
                        Next

                        If exportFieldsCount = 0 Then
                            ExportRangeToDatabase = 1
                            Exit Function
                        End If

                        ' Fast read of Excel range values to an array
                        ' for further fast work with the array

                        Dim arr As Object
                        arr = sourceRange.Value

                        ' The range data transfer to the dataTable

                        Dim row As Long
                        Dim rowCount As Long
                        rowCount = UBound(arr, 1)

                        Dim value As Object

                        Dim dataRow As DataRow

                        For row = 2 To rowCount
                            dataRow = dataTable.NewRow()
                            For col = 1 To exportFieldsCount
                                value = arr(row, rangeFields(col))
                                If value Is Nothing Then
                                ElseIf tableFieldTypes(col) = "System.DateTime" Then
                                    If value.GetType.ToString() = _
                                        "System.Double" Then
                                        dataRow(tableFields(col)) = _
                                            DateTime.FromOADate(value)
                                    Else
                                        dataRow(tableFields(col)) = value
                                    End If
                                ElseIf tableFieldTypes(col) = "Time" Then
                                    If value.GetType.ToString() = _
                                        "System.Double" Then
                                        dataRow(tableFields(col)) = _
                                            DateTime.FromOADate(value).ToString("HH:mm:ss")
                                    Else
                                        dataRow(tableFields(col)) = _
                                            DateTime.Parse(value).ToString("HH:mm:ss")
                                    End If
                                Else
                                    dataRow(tableFields(col)) = value
                                End If
                            Next
                            dataTable.Rows.Add(dataRow)
                        Next

                        dataAdapter.Update(dataTable)

                    Finally
                        builder.Dispose()
                    End Try
                Finally
                    dataTable.Dispose()
                End Try
            Finally
                dataAdapter.Dispose()
            End Try

            If afterSQL > "" Then
                cmd = New OleDbCommand(afterSQL, connection)
                Try
                    cmd.CommandType = CommandType.Text
                    cmd.ExecuteNonQuery()
                Finally
                    If Not cmd Is Nothing Then : cmd.Dispose() : End If
                End Try
            End If
        End Using

        ExportRangeToDatabase = 0
        LastErrorMessage = Nothing

    Catch sqlExc As OleDbException
        ExportRangeToDatabase = 1
        LastErrorMessage = sqlExc.Message

    Catch ex As Exception
        ExportRangeToDatabase = 1
        LastErrorMessage = ex.Message

    End Try

End Function

The main idea of the function is the use of the standard DataTable, DataAdapter and CommandBuilder objects to export the range data.

The main lines:

dataAdapter.Fill(dataTable)
dataAdapter.InsertCommand = builder.GetInsertCommand()
dataAdapter.Update(dataTable)

The preliminary column mappings is used for fast transfer of Excel range column data to a datatable column.

Code comments:

  • LastErrorMessage is a global variable that contains the last exception message if the function result is not zero.
  • Excel can hold date and time values as a double.
    So database date and time types are checked and DateTime.FromOADate function is used when the Excel cell value is double.
  • Do not use date and time database types if possible. Use general datetime type that has a full Excel equivalent. Otherwise, periodical convert problems will appear.

Function ExecuteOleDbDataReader

The function implements a common logic for the ExecuteReader command and calls the input processor delegate function to execute a data reader processing. See an example of a delegate function below.

context is a parameter that can hold any input-output data.

Delegate Sub ExecuteOleDbDataReaderDelegate(ByVal dataReader As OleDbDataReader, _
    ByRef context As Object)


Public Function ExecuteOleDbDataReader(ByVal connectionString As String, _
    ByVal commandText As String, _
    ByVal processor As ExecuteOleDbDataReaderDelegate, _
    ByRef context As Object) As Boolean

    Try
        Using connection As New OleDbConnection(connectionString)

            Dim cmd As New OleDbCommand(commandText, connection)
            Try
                cmd.CommandType = CommandType.Text
                connection.Open()
                Using dataReader As OleDbDataReader = _
                    cmd.ExecuteReader(CommandBehavior.CloseConnection)

                    processor(dataReader, context)

                End Using
            Finally
                If Not cmd Is Nothing Then : cmd.Dispose() : End If
            End Try
            Return True
        End Using
    Catch sqlExc As OleDbException
        MsgBox(sqlExc.Message, MsgBoxStyle.Exclamation)
        Return False
    Catch ex As Exception
        MsgBox(ex.Message, MsgBoxStyle.Exclamation)
        Return False
    End Try

End Function

Function OleDbDataReaderToArray

The function is used as a delegate for ExecuteOleDbDataReader function and reads the dataReader data to a context array.

The context variable must be declared as:

Dim arr(0, 0) As Object

The transposed array is used as only the last array dimension can be changed and the second dimension is used for the unknown count of datareader rows. See a use example in the ImportSQLtoRange function description above.

Public Sub OleDbDataReaderToArray(ByVal dataReader As OleDbDataReader, _
    ByRef context As Object)

    Dim row As Long = -1
    Dim colCount As Integer = dataReader.FieldCount
    Dim rowCapacity As Long = 1000

    ReDim context(colCount - 1, rowCapacity - 1)

    row = row + 1
    For col As Integer = 0 To colCount - 1
        context(col, row) = dataReader.GetName(col)
    Next

    Do While dataReader.Read
        row = row + 1
        If row = rowCapacity Then
            rowCapacity = rowCapacity + 1000
            ReDim Preserve context(colCount - 1, rowCapacity - 1)
        End If
        For col As Integer = 0 To colCount - 1
            context(col, row) = dataReader(col).ToString
        Next
    Loop

    ReDim Preserve context(colCount - 1, row)

End Sub

Function GetConnectionString

This is a simple function that constructs an OLE DB connection string suitable for SQL Server/SQL Azure connection using OleDbConnection object and Excel QueryTable one with "OLEDB;" prefix.

For SQL Azure TrustServerCertificate=True is used. This is less secure, but the False option can cause an SQL Azure connection problem.

Public Function GetConnectionString( _
    ByVal server As String, _
    ByVal database As String, _
    ByVal username As String, _
    ByVal password As String, _
    Optional ByVal extended As String = "") As String

    If (extended = "") And UCase(server).Contains(".NET") Then
      extended = "Extended Properties=""Encrypt=True;TrustServerCertificate=True"";"
    End If

    If username = "" Then
        Return String.Format("Provider=SQLOLEDB.1;Integrated Security=SSPI{2};" _
            & "Persist Security Info=True;" _
            & "Data Source={0};Use Procedure for Prepare=1;Auto Translate=True;" _
            & "Packet Size=4096;Initial Catalog={1}", _
            server, database, extended)
    Else
        Return String.Format("Provider=SQLOLEDB.1;Password={3};User ID={2};{4}" _
            & "Persist Security Info=True;" _
            & "Data Source={0};Use Procedure for Prepare=1;Auto Translate=True;" _
            & "Packet Size=4096;Initial Catalog={1}", _
            server, database, username, password, extended)
    End If

End Function

To top

Import-Export using VSTO Objects

Use the ImportToolsListObject function to insert a data table connected to a database table.

Use the RefreshToolsListObject function to refresh data table data.

Use the ExportToolsListObject function to export data table data to a database table.

All the VSTO and .NET Framework objects are alive only at the VSTO customization run-time.
So the developer should care about the save and restore of the objects.
The save and restore logic is not implemented in the example.

Function ImportToolsListObject

The function creates and fills DataTable, DataAdapter and CommandBulder objects.

Then the function creates VSTO ListObject for input VSTO WorksheetBase and sets the DataTable as ListObject DataSource.

The DataAdapter is added to DataAdapters SortedList collection for further use in the ExportToolsListObject and RefreshToolsListObject functions.

Public DataAdapters As New SortedList(Of String, OleDbDataAdapter)


Function ImportToolsListObject( _
    ByVal connectionString As String, _
    ByVal commandText As String, _
    ByVal sh As WorksheetBase, _
    ByVal target As Excel.Range) As Integer

    Dim listObjectName As String = _
        String.Format("DataTable_{0}", DataAdapters.Count + 1)

    Try
        Using connection As New OleDbConnection(connectionString)

            Dim dataTable As New DataTable
            Dim dataAdapter As New OleDbDataAdapter(commandText, connection)

            dataAdapter.Fill(dataTable)

            Dim builder As OleDbCommandBuilder = New OleDbCommandBuilder(dataAdapter)
            With dataAdapter
                .InsertCommand = builder.GetInsertCommand()
                .UpdateCommand = builder.GetUpdateCommand()
                .DeleteCommand = builder.GetDeleteCommand()
            End With

            DataAdapters.Add(listObjectName, dataAdapter)

            With sh.Controls.AddListObject(target, listObjectName)
                .AutoSetDataBoundColumnHeaders = True
                .DataSource = dataTable
            End With

            ' Don't call Dispose for these objects:
            ' builder.Dispose()
            ' dataAdapter.Dispose()
            ' dataTable.Dispose()
        End Using

        ImportToolsListObject = 0
        LastErrorMessage = Nothing

    Catch ex As Exception
        ImportToolsListObject = 1
        LastErrorMessage = ex.Message

    End Try

End Function

Function ExportToolsListObject

This function updates the source database table with VSTO ListObject dataTable changes.

The database connection is restored before the update and is closed after.

The dataTable is cleared and refilled to get updated database data.

Function ExportToolsListObject(ByVal connectionString As String, _
    ByVal lo As Microsoft.Office.Tools.Excel.ListObject) As Integer

    If DataAdapters.Count = 0 Then
        ExportToolsListObject = 1
        LastErrorMessage = "There are no DataAdapters"
        Exit Function
    End If

    Try
        Dim dataTable As DataTable = lo.DataSource

        Using connection As New OleDbConnection(connectionString)
            With DataAdapters(lo.DisplayName)

                .SelectCommand.Connection = connection
                .InsertCommand.Connection = connection
                .UpdateCommand.Connection = connection
                .DeleteCommand.Connection = connection

                .Update(dataTable)

                dataTable.Clear()

                .Fill(dataTable)
            End With
        End Using

        ExportToolsListObject = 0
        LastErrorMessage = Nothing

    Catch ex As Exception
        ExportToolsListObject = 1
        LastErrorMessage = ex.Message

    End Try

End Function

Function RefreshToolsListObject

The function clears and refills VSTO ListObject DataSource to get updated database data.

Function RefreshToolsListObject(ByVal connectionString As String, _
    ByVal lo As Microsoft.Office.Tools.Excel.ListObject) As Integer

    If DataAdapters.Count = 0 Then
        RefreshToolsListObject = 1
        LastErrorMessage = "There are no DataAdapters"
        Exit Function
    End If

    Try
        Dim dataTable As DataTable = lo.DataSource

        dataTable.Clear()

        Using connection As New OleDbConnection(TestConnectionString)
            With DataAdapters(lo.DisplayName)

                .SelectCommand.Connection = connection
                .Fill(dataTable)
            End With
        End Using

        RefreshToolsListObject = 0
        LastErrorMessage = Nothing

    Catch ex As Exception
        RefreshToolsListObject = 1
        LastErrorMessage = ex.Message

    End Try

End Function

To top

Conclusion

The article describes the solutions for three typical tasks: import, refresh, and export for three approaches: using native Excel QueryTable and Range objects and VSTO ListObject object.

Excel QueryTable Excel Range VSTO ListObject
SQL Server data import to Excel yes yes yes
SQL Server data refresh in Excel yes yes yes
Excel data export to SQL Server yes yes yes

You may use any approach in line with your tasks.

Download the complete Visual Studio 2010 project to quick start.

Download

Downloads
Importing and Exporting Excel and SQL Server Data Using VSTO
Includes source codes and examples for working with SQL Server using VSTO add-ins
Version: 1.7 | 10/23/2015 | 0.1MB | Article
SaveToDB Add-In for Microsoft Excel (Trial 30 days)
Allows using Microsoft Excel as a database client
Version: 6.10 | 11/02/2016 | 20.9MB | Homepage | Article 1 | Article 2

To top

Download