Walkthrough: Working with Offline SQL Server Data in Excel

  • Article

 

Brian A. Randell
MCW Technologies

September 2003

Applies to:
    Microsoft® Visual Studio® Tools for the Microsoft Office System
    Microsoft Office Excel 2003
    Microsoft Visual Studio .NET 2003

Summary: Microsoft Office Excel makes it easy to work with rectangular data, such as data from an external database. In this walkthrough, you'll load data from Microsoft SQL Server into an ADO.NET dataset. You'll then use the Excel object model to support synchronizing changes made to the data in Excel with SQL Server. (20 printed pages)

Contents

Introduction
Prerequisites
Getting Started
Laying Out the Worksheet
Hooking Up the Event Handlers
Adding Code to Import Data
Synchronizing Changes with the Server
Running Code Before Closing the Workbook
Conclusion

Introduction

In this walkthrough, you will download data from the Microsoft® SQL Server™ Northwind sample database into a local DataSet object, releasing your connection to the server. You will then load the data from the dataset into a sheet within a Microsoft Office Excel workbook as shown in Figure 1. Once the data is loaded, you'll make some changes and send the results back to SQL Server. Once you're done, the example will display a message box informing you of the number of records updated.

Figure 1. Excel with data loaded from SQL Server

Prerequisites

To complete this walkthrough, the following software and components must be installed on the development computer:

  • Microsoft Visual Studio® .NET 2003 or Microsoft Visual Basic® .NET Standard 2003
  • Microsoft Visual Studio Tools for the Microsoft Office System
  • Microsoft Office Professional Edition 2003
  • Microsoft SQL Server or Microsoft SQL Server Desktop Engine (MSDE) 7.0 or 2000, with the Northwind sample database installed. This demonstration assumes that you've set up SQL Server/MSDE allowing access using integrated security.

**Tip   **This demonstration assumes that if you're a Visual Basic .NET programmer, you've set the Option Strict setting in your project to On (or have added the Option Strict statement to each module in your project), although it is not required. Setting the Option Strict setting to On requires a bit more code, as you'll see, but it also ensures that you do not perform any unsafe type conversions. You can get by without it, but in the long run, the discipline required by taking advantage of this option will far outweigh the difficulties it adds as you write code.

Getting Started

In order to get started, you'll need to create a new Visual Studio .NET project that works with Microsoft Office Excel 2003.

To create an Excel Workbook project

  1. Start Visual Studio .NET, and on the File menu, point to New, and click Project.

  2. In the Project Types pane, expand Microsoft Office System Projects, and then select Visual Basic Projects or Visual C# Projects.

  3. In the Templates pane, select Excel Workbook.

  4. Name the project ExcelSQLServerData, and store it in a convenient local path.

  5. Accept the defaults in the Microsoft Office Project Wizard, and click Finish to create the project.

    Visual Studio .NET opens the ThisWorkbook.vb or ThisWorkbook.cs file in the Code Editor for you.

Laying Out the Worksheet

In order to load the data and synchronize changes, you'll need to add some way to start the code running. For this demonstration, you'll create two hyperlinks within the workbook, and react to the SheetFollowHyperlink event of the Workbook object to run your code.

To format the worksheet

  1. Press F5 to run the project, loading Excel and your new workbook.

  2. Rename Sheet1 to Products. If you'd like, remove Sheet2 and Sheet3.

  3. Within Excel, put the cursor in cell G1, and select Hyperlink on the Insert menu.

  4. On the left side of the Insert Hyperlink dialog box, in the Link to pane, select Place in This Document.

  5. Set the Text to display value to Load Data.

  6. Make sure the cell reference in the dialog box matches the location of your hyperlink. When you're done, the dialog box should look like Figure 2. Click OK to dismiss the dialog box. You should see the new hyperlink within the workbook.

    Figure 2. The finished Insert Hyperlink dialog box

  7. Repeat the process, putting the cursor in cell G2, and put Update Data in the Text to display field.

  8. Select Save on the File menu to save your changes.

    The workbook should look something like Figure 3.

    Figure 3. Your Excel workbook ready to go

Hooking Up the Event Handlers

In order to start your code running, you'll need to react to the Workbook.SheetFollowHyperlink event. In this section, you'll add support for reacting to this event in Visual Basic .NET and in Microsoft Visual C#.

Hook Up the Event Handlers (Visual Basic Only)

Follow these steps to hook up the event handler in Visual Basic .NET:

To add the event handler (Visual Basic)

  1. From the Class Name drop-down list in the upper-left corner of the Code Editor, select ThisWorkbook.

  2. From the Method Name drop-down list in the upper-right corner of the Code Editor, select SheetFollowHyperlink.

    Visual Studio .NET creates the event handler stub for you.

  3. Add the following procedure stubs to the current class. You'll fill in the details later:

    ' Visual Basic
Private Sub LoadData()

End Sub

Private Sub UpdateData()

End Sub

  4. Modify the ThisWorkbook_SheetFollowHyperlink procedure, adding the following code:

    ' Visual Basic
Try
    Select Case Target.Name
        Case "Load Data"
            LoadData()
        Case "Update Data"
            UpdateData()
    End Select
Catch ex As Exception
    MessageBox.Show(ex.Message, ex.Source, _ 
        MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try

Hook Up the Event Handlers (C# Only)

You'll need to react to the Workbook.SheetFollowHyperlink event. Later, you'll need to react to the SheetChange event. In this section, you'll add support for reacting to these events in Visual C#®.

To add the event handler (C#)

  1. Within the OfficeCodeBehind class, locate the existing declarations for the openEvent and beforeCloseEvent variables.

    Add new variables representing the workbook's SheetFollowHyperlink and SheetChange events:

    // C#
private Excel.WorkbookEvents_SheetFollowHyperlinkEventHandler
    sheetFollowHyperlinkEvent;
private Excel.WorkbookEvents_SheetChangeEventHandler 
    sheetChangeEvent;

  2. Add the following procedure stubs to the class:

    // C#
protected void ThisWorkbook_SheetFollowHyperlink(
    Object sh, Excel.Hyperlink Target)
{

}

protected void ThisWorkbook_SheetChange(
    Object sh, Excel.Range Target)
{

}

  3. Add the following code to the ThisWorkbook_Open procedure:

    // C#
sheetFollowHyperlinkEvent = 
    new Excel.WorkbookEvents_SheetFollowHyperlinkEventHandler(
    ThisWorkbook_SheetFollowHyperlink);
thisWorkbook.SheetFollowHyperlink += sheetFollowHyperlinkEvent;

sheetChangeEvent = 
    new Excel.WorkbookEvents_SheetChangeEventHandler( 
    ThisWorkbook_SheetChange);
thisWorkbook.SheetChange += sheetChangeEvent;

  4. Add the following procedure stubs to the class:

    // C#
void LoadData()
{
}

void UpdateData()
{
}

  5. Modify the ThisWorkbook_SheetFollowHyperlink procedure, adding the following code:

    // C#
try
{
    switch (Target.Name)
    {
        case "Load Data":
            LoadData();
            break;
        case "Update Data":
            UpdateData();
            break;
    }
}
catch (Exception ex)
{
    MessageBox.Show(ex.Message, ex.Source, 
        MessageBoxButtons.OK, MessageBoxIcon.Error);
}

Adding Code to Import Data

Next, you'll need to add the code that creates a dataset, loading the data from SQL Server, and add code to copy that data into the Products sheet within the Excel workbook.

To import data into the worksheet

  1. Scroll to the top of the code module, and add the following statement, which will reduce the amount of typing required to refer to the objects and members you'll reference:

    ' Visual Basic
Imports System.Data.SqlClient

// C#
using System.Data.SqlClient;
using System.Data;

  2. Add the following declarations, immediately beneath the existing declarations for the ThisApplication and ThisWorkbook variables:

    ' Visual Basic
Private DisableWorkSheetChanges As Boolean = False

Private mda As SqlDataAdapter
Private mds As DataSet
Private mdt As System.Data.DataTable

Private xlSheet As Excel.Worksheet
Private rngUC As Excel.Range
Private rngData As Excel.Range

// C#
private Boolean DisableWorkSheetChanges = false;

private SqlDataAdapter mda = null;
private DataSet mds = null;
private System.Data.DataTable mdt = null;

private Excel.Worksheet xlSheet = null;
private Excel.Range rngUC = null;
private Excel.Range rngData = null;

    **Warning   **Excel provides a DataTable object, as does ADO.NET. It's important to distinguish between the two, and the code uses an explicit namespace reference to avoid the ambiguity.

  3. Add the following procedure to the OfficeCodeBehind class.

    This procedure connects to SQL Server on the local computer, using integrated security, and fills a dataset:

    ' Visual Basic
Private Sub GetDataSet()
    Dim cnn As SqlConnection

    Try
        If mds Is Nothing Then
            mds = New DataSet
        Else
            mds.Tables.Remove(mdt)
        End If

        cnn = New SqlConnection( _
            "Server='.';" & _
            "Database=Northwind;" & _
            "Integrated Security=true")

        Dim strSQL As String = "SELECT " & _
            "ProductId AS [Id], ProductName AS [Name], " & _
            "UnitsInStock AS [On Hand], " & _
            "UnitsOnOrder AS [On Order], " & _
            "ReorderLevel AS [Reorder Level] " & _
            "FROM Products WHERE Discontinued = 0 " & _
            "ORDER BY UnitsInStock"

        Dim cmd As New SqlCommand(strSQL, cnn)

        mda = New SqlDataAdapter(cmd)
        mda.Fill(mds)
        mdt = mds.Tables(0)

        Dim cb As New SqlCommandBuilder(mda)
        mda.UpdateCommand = cb.GetUpdateCommand()

    Catch ex As Exception
        MessageBox.Show(ex.Message, ex.Source, _
        MessageBoxButtons.OK, MessageBoxIcon.Error)
    End Try
End Sub

// C#
private void GetDataSet() 
{
    SqlConnection cnn;

    try 
    {
        if (mds == null) 
        {
            mds = new DataSet();
        } 
        else 
        {
            mds.Tables.Remove(mdt);
        }

        cnn = new SqlConnection("Server=.;" + 
            "Database=Northwind;Integrated Security=true");

        string strSQL = "SELECT " +
            "ProductId AS [Id], ProductName AS [Name], " +
            "UnitsInStock AS [On Hand], " +
            "UnitsOnOrder AS [On Order], " +
            "ReorderLevel AS [Reorder Level] " +
            "FROM Products WHERE Discontinued = 0 " +
            "ORDER BY UnitsInStock";

        SqlCommand cmd = new SqlCommand(strSQL, cnn);

        mda = new SqlDataAdapter(cmd);
        mda.Fill(mds);
        mdt = mds.Tables(0);

        SqlCommandBuilder  cb = new SqlCommandBuilder(mda);
        mda.UpdateCommand = cb.GetUpdateCommand();
    } 
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message, ex.Source,    
            MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
}

  4. Add the following procedure to the class.

    This procedure creates the column headings in the worksheet using the field names from the Columns collection of the data table contained within the new dataset, and provides basic formatting:

    ' Visual Basic
Private Sub SetupWorksheet()
    Try
        DisableWorkSheetChanges = True

        xlSheet = DirectCast( _
            ThisWorkbook.Worksheets("Products"), Excel.Worksheet)
        rngUC = DirectCast(xlSheet.Cells(2, 1), Excel.Range)
        rngUC.CurrentRegion.Clear()

        Dim i As Integer = 0
        Dim col As DataColumn
        Dim rng As Excel.Range

        For Each col In mdt.Columns
            I += 1
            rng = DirectCast(xlSheet.Cells(1, i), Excel.Range)
            rng.Value = col.ColumnName
            rng.Font.Bold = True
        Next
    Finally
        DisableWorkSheetChanges = False
    End Try
End Sub

// C#
private void SetupWorksheet() 
{
    try 
    {
        DisableWorkSheetChanges = true;

        xlSheet = (Excel.Worksheet)ThisWorkbook.
            Worksheets["Products"];
        rngUC = (Excel.Range)xlSheet.Cells[2, 1];
        rngUC.CurrentRegion.Clear();

        int i = 0;
        Excel.Range rng;

        foreach (DataColumn col in mdt.Columns)
        {
            i++;
            rng = (Excel.Range)xlSheet.Cells[1, i];
            rng.Value2 = col.ColumnName;
            rng.Font.Bold = true;
        } 
    } 
    finally 
    {
        DisableWorkSheetChanges = false;
    }
}

  5. Add the following procedure, which takes the data from the data table in the previously loaded data set and puts it into the Products worksheet:

    ' Visual Basic
Private Sub PutDataInXL()
    DisableWorkSheetChanges = True

    Dim i As Integer = 0
    Dim j As Integer = 0
    Dim dr As DataRow

    Try
        ThisApplication.ScreenUpdating = False

        If Not mdt Is Nothing Then
            For i = 0 To mdt.Rows.Count - 1
                dr = mdt.Rows(i)
                For j = 0 To mdt.Columns.Count - 1
                    rngUC.Offset(i, j).Value = dr(j).ToString()
                Next j
            Next
            rngData = rngUC.CurrentRegion
        End If

    Finally
        ThisApplication.ScreenUpdating = True
        DisableWorkSheetChanges = False
    End Try
End Sub

// C#
private void PutDataInXL() 
{
    DisableWorkSheetChanges = true;

    int i = 0;
    int j = 0;
    DataRow dr;

    try 
    {
        ThisApplication.ScreenUpdating = false;
        if (mdt != null) 
        {
            for (i = 0; i <= mdt.Rows.Count - 1; i++)
            {
                dr = mdt.Rows[i];
                for (j = 0 ; j <= mdt.Columns.Count - 1; j++)
                    rngUC.get_Offset(i, j).Value2 = dr[j].ToString();
            }
        } 
        rngData = rngUC.CurrentRegion;
    } 
    finally 
    {
        ThisApplication.ScreenUpdating = true;
        DisableWorkSheetChanges = false;
    }
}

  6. Add the following procedure, which applies some formatting to the newly loaded data in Excel:

    ' Visual Basic
Private Sub FormatColumns()
    Try
        DisableWorkSheetChanges = True

        rngData.Columns.NumberFormat = "0"
        rngData.Columns.AutoFit()

    Finally
        DisableWorkSheetChanges = False
    End Try
End Sub

// C#
private void FormatColumns() 
{
    try 
    {
        DisableWorkSheetChanges = true;

        rngData.Columns.NumberFormat = "0";
        rngData.Columns.AutoFit();
    } 
    finally 
    {
        DisableWorkSheetChanges = false;
    }
}

  7. Add the following code to the LoadData procedure created earlier:

    ' Visual Basic
GetDataSet()
SetupWorksheet()
PutDataInXL()
FormatColumns()

// C#
GetDataSet();
SetupWorksheet();
PutDataInXL();
FormatColumns();

  8. Select Save All on the File menu to save the entire solution.

  9. Press F5 to run the project, loading Excel and your workbook.

  10. Within Excel, click the Load Data link you added previously, and verify that your code has imported and formatted the data, as shown in Figure 1.

  11. Close Excel and the workbook, saving changes if you desire, and return to Visual Studio .NET.

Synchronizing Changes with the Server

You need to add some code to trap the SheetChange event of the Workbook and put any changes made to the loaded data into the correct columns in the Rows collection of the DataTable object. Then, you'll add code to actually send the changes to SQL Server when you click the Update Data hyperlink.

To synchronize changes with the server

  1. Do one of the following, depending on the language you are using:

    (Visual Basic only)

    1. From the Class Name drop-down list in the upper-left corner of the Code Editor, select ThisWorkbook.

    2. From the Method Name drop-down list in the upper-right corner of the Code Editor, select ThisWorkbook_SheetChange.

      Visual Studio .NET creates the event handler for you.

    (C# only)

    1. Within the OfficeCodeBehind class, locate the existing declarations for the OpenEvent and BeforeCloseEvent variables.

    2. Add a new variable representing the workbook's SheetChange event:

      // C#
    private Excel.WorkbookEvents_SheetChangeEventHandler 
        sheetChangeEvent;

    3. Add the following procedure:

      // C#
private Excel.Range GetIntersect(
    Excel.Range rng1, Excel.Range rng2)
{
    return ThisApplication.Intersect(rng1, rng2, Type.Missing,
        Type.Missing, Type.Missing, Type.Missing, Type.Missing,
        Type.Missing, Type.Missing, Type.Missing, Type.Missing,
        Type.Missing, Type.Missing, Type.Missing, Type.Missing,
        Type.Missing, Type.Missing, Type.Missing, Type.Missing,
        Type.Missing, Type.Missing, Type.Missing, Type.Missing,
        Type.Missing, Type.Missing, Type.Missing, Type.Missing,
        Type.Missing, Type.Missing, Type.Missing);
}

Visual Basic and C#

  1. Modify the ThisWorkbook_SheetChange procedure, so that it looks like the following:

    ' Visual Basic
Private Sub ThisWorkbook_SheetChange( _
    ByVal Sh As Object, ByVal Target As Excel.Range) _
    Handles ThisWorkbook.SheetChange

    If Not DisableWorkSheetChanges Then
       If Not ThisApplication. _
           Intersect(rngData, Target) Is Nothing Then
            ' Subtract 1 because Excel is 1-based, 
            ' then subtract one row for the header. 
            ' Don't handle changes to the 
            ' header row, however.
            If Target.Row > 1 Then
                Dim intRow As Integer = Target.Row - 2
                Dim intCol As Integer = Target.Column - 1
                mdt.Rows(intRow)(intCol) = Target.Value
            End If
        End If
    End If
End Sub

// C#
protected void ThisWorkbook_SheetChange(
    Object sh, Excel.Range Target)
{
    if (!DisableWorkSheetChanges) 
    {
        if (GetIntersect(rngData, Target) != null) 
        {
            // Subtract 1 because Excel is 1-based, 
            // then subtract one row for the header. 
            // Don't handle changes to the 
            // header row, however.
            if (Target.Row > 1) 
            {
                int intRow = Target.Row - 2;
                int intCol = Target.Column - 1;
                mdt.Rows[intRow][intCol] = Target.Value2;
            }
        }
    }
}

  2. Add the following code to the existing UpdateData procedure stub, sending the modified rows of the DataTable to SQL Server:

    ' Visual Basic
Try
    If mds.HasChanges Then
        Dim intRV As Integer = mda.Update(mdt)
        If intRV > 0 Then
            MessageBox.Show(String.Format( _
                "{0} records were updated successfully.", _
                intRV.ToString()), _
                "Success", MessageBoxButtons.OK, _
                MessageBoxIcon.Information)
        End If
    End If

Catch ex As Exception
    MessageBox.Show(ex.Message, ex.Source, _
       MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try

// C#
try 
{
    if (mds.HasChanges()) 
    {
        int intRV = mda.Update(mdt);
        if (intRV > 0) 
        {
            MessageBox.Show(String.Format(
                "{0} records were updated successfully.",   
                intRV.ToString()), "Success", 
                MessageBoxButtons.OK, MessageBoxIcon.Information);
        }
    }

} 
catch (Exception ex) 
{
    MessageBox.Show(ex.Message, ex.Source,   
        MessageBoxButtons.OK, MessageBoxIcon.Error);
}

  3. Save your project, then press F5 to run it.

  4. Within Excel, click the Load Data link, and verify that you've successfully loaded the data.

  5. Modify any of the loaded data and click the Update Data hyperlink to send your changes back to SQL Server.

  6. Close Excel, but this time, don't save the changes made to the workbook. Once back in Visual Studio .NET, press F5 again to start debugging again.

  7. Click the Load Data link again, and verify that that your submitted changes show up.

  8. Close Excel (saving the workbook, if you like), returning to Visual Studio .NET.

Running Code Before Closing the Workbook

To complete the walkthrough, you need to add code to the BeforeClose event of the workbook. This event will check to see if there are any changes that have not yet been submitted to the server. If so, the code displays an alert asking if changes should be submitted.

To check for changes to the workbook

  1. Add the following code to the ThisWorkbook_BeforeClose procedure:

    ' Visual Basic
If mds.HasChanges Then
    If MessageBox.Show( _
        "There are changes that need to submitted. " & _
        "Do you want to send the changes in now?", _
        "Question", MessageBoxButtons.YesNo, _
        MessageBoxIcon.Question) = DialogResult.Yes Then

        UpdateData()
    End If
End If

// C#
if (mds.HasChanges()) 
{
      if (MessageBox.Show(    
          "There are changes that need to submitted. " +
          "do you want to send the changes in now?", "Question", 
          MessageBoxButtons.YesNo, MessageBoxIcon.Question) ==
          DialogResult.Yes) 
    {
        UpdateData();
    }
}

  2. Save your project, then press F5 to run it.

  3. Within Excel, click the Load Data link.

  4. Modify any of the loaded data and then attempt to close the workbook. You will see an alert indicating that you have changes to be saved. Click Yes to save your changes.

  5. When Excel prompts you to save the workbook, select No.

  6. Once back in Visual Studio .NET, press F5 again to start debugging again.

  7. Click the Load Data link again, and verify that that your changes show up.

  8. Close Excel (saving the workbook, if you like), returning to Visual Studio .NET.

Conclusion

Excel makes it easy to work with data from an external database. This walkthrough demonstrated how to create an Excel workbook project using Visual Studio Tools for the Microsoft Office System, and how to use the Excel object model to support synchronizing changes made to data in Excel with SQL Server.