Walkthrough: Creating a Chart in Excel Based on SQL Server Data

  • Article

 

Ken Getz
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:   Demonstrates how you can create a chart based on data from Microsoft SQL Server, using the Excel object model and code written in Microsoft Visual Basic .NET or Microsoft Visual C#. (16 printed pages)

Contents

Introduction
Prerequisites
Getting Started
Hooking Up the Event Handler
Adding Code to Import Data
Adding the Chart
Conclusion

Introduction

In this walkthrough, you'll copy data from the Microsoft® SQL Server Northwind sample database into its own page in the Microsoft Office Excel workbook, formatting the data for display as shown in Figure 1. Given the new range of data, you'll create a chart that looks like the one shown in Figure 2.

Figure 1. Formatted data within Excel

Figure 2. Chart based on data in the Northwind sample database

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), (MSDE 7.0 or MSDE 2000), with the Northwind sample database installed.

**Note   **This example code counts on using integrated security. If you are not able to log onto your SQL Server computer with your own account, you will need to get information from your SQL Server administrator on how to log on, and you will need to modify the connection information in the code, as well. See the SqlConnection.ConnectionString property in the .NET Framework documentation for more information, if you need to modify the sample code.

**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 will 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

To get started, create a Visual Studio .NET project that works with Microsoft Office Excel 2003.

Create the Project

Use Visual Studio Tools for the Microsoft Office System to create an Excel Workbook project using Visual Basic .NET or Microsoft Visual C#®development tool.

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. From the Templates pane of the New Project dialog box, select Excel Workbook.

  4. Name the project ExcelChartDemo, 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.

Lay Out the Spreadsheet

To load the data and create the chart, you'll need to add some way to start the code running. Although you could do this from the workbook's Open event, that option doesn't make sense in this case. For this demonstration, you'll create a hyperlink within the workbook, and react to the FollowHyperlink event of the Workbook object.

To create a hyperlink in the workbook

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

  2. Within Excel, select Hyperlink from the Insert menu.

  3. In the Insert Hyperlink dialog box, set the Text to display value to Create New Chart.

  4. In the Link to pane on the left side of the dialog box, select Place in This Document. 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 3. Click OK to dismiss the dialog box. You should see the new hyperlink within the workbook.

    Figure 3. The finished Insert Hyperlink dialog box

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

    Close Excel, returning to Visual Studio .NET.

Hooking Up the Event Handler

T 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 and C#.

(Visual Basic Only) Hook up the Event Handler

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

To hook up 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. Modify the ThisWorkbook_SheetFollowHyperlink procedure, adding the following code (you'll add the LoadDataAndCreateChart procedure in a later step):

    ' Visual Basic
If Target.Name = "Create New Chart" Then
    LoadDataAndCreateChart()
End If

(C# Only) Hook Up the Event Handler

Follow these steps to hook up the event handler in C#:

To hook up the event handler (C#)

  1. Within the OfficeCodeBehind class, locate the existing declarations for the openEvent and beforeCloseEvent variables. Add a new variable representing the workbook's SheetFollowHyperlink event:

    // C#
private Excel.WorkbookEvents_SheetFollowHyperlinkEventHandler 
    sheetFollowHyperlinkEvent;

  2. Add the following procedure stub to the class:

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

}

  3. Add the following code to the ThisWorkbook_Open procedure:

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

  4. Modify the ThisWorkbook_SheetFollowHyperlink procedure, adding the following code (you'll add the LoadDataAndCreateChart procedure in a later step):

    // C#
if (Target.Name == "Create New Chart")
{
    LoadDataAndCreateChart();
}

Adding Code to Import Data

Next, you'll need to add the code that creates a SqlDataReader, loading the data from SQL Server, and add code to copy that data into a new sheet within the Excel workbook. This demonstration adds new sheets to the current workbook—so that you can run the demonstration multiple times, you'll also add code to remove all the sheets but the active sheet before creating the chart.

**Tip   **Excel handles charts that appear on their own sheets separately from normal sheets. Therefore, the ResetWorkbook procedure must be able to handle two sets of objects—both the Worksheets collection and the Charts collection properties of the Application object.

To import data into the workbook

  1. Scroll to the top of the code module and add the following statements, which 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;
using System.Data.SqlClient;

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

    ' Visual Basic
Private xlSheet As Excel.Worksheet
Private xlChart as Excel.Chart

// C#
private Excel.Worksheet xlSheet = null;
private Excel.Chart xlChart = null;

  3. Within the OfficeCodeBehind class (created by the project template), add the following procedure, which refreshes the workbook and its contents for you:

    ' Visual Basic
Private Sub ResetWorkbook()
    ' Get rid of all but the original worksheet.

    Try
        ThisApplication.DisplayAlerts = False

        Dim ws As Excel.Worksheet
        For Each ws In ThisWorkbook.Worksheets
            If Not ws Is ThisApplication.ActiveSheet Then
                ws.Delete()
            End If
    Next

        Dim cht As Excel.Chart
        For Each cht In ThisWorkbook.Charts
            cht.Delete()
        Next

    Finally
        ThisApplication.DisplayAlerts = True
    End Try
End Sub

// C#
private void ResetWorkbook() 
{
    // Get rid of all but the original worksheet.

    try 
    {
        ThisApplication.DisplayAlerts = false;

        foreach (Excel.Worksheet ws in ThisWorkbook.Worksheets)
            if (ws != ThisApplication.ActiveSheet)
            {
                ws.Delete();
            }
        foreach ( Excel.Chart cht in ThisWorkbook.Charts)
            cht.Delete();
    } 
    finally 
    {
        ThisApplication.DisplayAlerts = true;
    }
}

    **Tip   **Excel normally "complains" when you attempt to delete sheets programmatically. The sample code sets the Application.DisplayAlerts property to False before removing items, and then sets it back to True once it's done.

  4. Add the following procedure to the OfficeCodeBehind class. This procedure connects to SQL Server on the local computer, using integrated security, and returns a SqlDataAdapter ready to retrieve data for use in the workbook:

    ' Visual Basic
Private Function GetDataReader() As SqlDataReader
    Dim cnn As New SqlConnection("Server='.';" & _
        "Database=Northwind;Integrated Security=true")

    Dim cmd As New SqlCommand( _
        "SELECT Categories.CategoryName," & _
        " AVG([Order Details].Quantity) AS AvgQty " & _
        "FROM Products INNER JOIN [Order Details] " & _
        "ON Products.ProductID = [Order Details].ProductID " & _
        "INNER JOIN Categories ON " & _
        " Products.CategoryID = Categories.CategoryID " & _
        "GROUP BY Categories.CategoryName " & _
        "ORDER BY AVG([Order Details].Quantity) DESC, " & _
        " Categories.CategoryName", cnn)

    cnn.Open()
    Return cmd.ExecuteReader(CommandBehavior.CloseConnection)
End Function

// C#
private SqlDataReader GetDataReader() 
{
    SqlConnection  cnn = new SqlConnection(
        "Server=.;" +
        "Database=Northwind;" +
        "Integrated Security=true");

    SqlCommand cmd = new SqlCommand(
        "SELECT Categories.CategoryName," + 
        " AVG([Order Details].Quantity) AS AvgQty " + 
        "FROM Products INNER JOIN [Order Details] " + 
        "ON Products.ProductID = [Order Details].ProductID " + 
        "INNER JOIN Categories ON " + 
        " Products.CategoryID = Categories.CategoryID " + 
        "GROUP BY Categories.CategoryName " + 
        "ORDER BY AVG([Order Details].Quantity) DESC, " + 
        " Categories.CategoryName", cnn);

    cnn.Open();
    return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}

  5. Add the following procedure to the class. This procedure creates the sheet that holds the data from SQL Server, and formats column headings as shown in Figure 1:

    ' Visual Basic
Private Sub SetupWorksheet()
    '  Add a new sheet after the active sheet.
    xlSheet = DirectCast(ThisWorkbook.Worksheets.Add( _
        After:=ThisWorkbook.ActiveSheet), Excel.Worksheet)

    xlSheet.Name = "Average Order Volume"

    ' Copy field names to Excel.
    ' Bold the column headings.
    With DirectCast(xlSheet.Cells(1, 1), Excel.Range)
        .Formula = "AvgQty"
        .Font.Bold = True
    End With
    With DirectCast(xlSheet.Cells(1, 2), Excel.Range)
        .Formula = "CategoryName"
        .Font.Bold = True
    End With
End Sub

// C#
private void SetupWorksheet() 
{
    // Add a new sheet after the active sheet.
    xlSheet = (Excel.Worksheet)ThisWorkbook.
        Worksheets.Add(Type.Missing, ThisWorkbook.ActiveSheet, 
        Type.Missing, Type.Missing);

    xlSheet.Name = "Average Order Volume";

    // Copy field names to Excel.
    // Bold the column headings.
    Excel.Range rng = (Excel.Range)xlSheet.Cells[1, 1];
    rng.Formula = "AvgQty";
    rng.Font.Bold = true;

    rng = (Excel.Range)xlSheet.Cells[1, 2];
    rng.Formula = "CategoryName";
    rng.Font.Bold = true;
}

  6. Add the following procedure, which retrieves the data from the SqlDataReader, loads it into the correct locations within the new sheet, and formats the data for display:

    ' Visual Basic
Private Sub LoadAndFormatData(ByVal dr As SqlDataReader)
    Dim i, j As Integer

    ' Copy the data in from the SqlDataReader.
    i = 1
    While dr.Read
        i = i + 1
        For j = 0 To 1
            xlSheet.Cells(i, j + 1) = dr(j).ToString()
        Next j
    End While
    dr.Close()

    ' Format the columns.
    DirectCast(xlSheet.Columns(1), _
        Excel.Range).AutoFit()


    With DirectCast(xlSheet.Columns(2), _
        Excel.Range)
        .NumberFormat = "0.00"
        .AutoFit()
    End With
End Sub

// C#
private void LoadAndFormatData( SqlDataReader dr) 
{
    int row;
    int col;

    // Copy the data in from the SqlDataReader.
    // Start at row 2.
    row = 1;
    while (dr.Read())
    {
        row += 1;
        for (col = 0 ; col <= 1; col++)
        {
            xlSheet.Cells[row, col + 1] = dr[col].ToString();
        }
    }
    dr.Close();

    // Format the columns.
    ((Excel.Range)xlSheet.Columns[1, Type.Missing]).AutoFit();

    Excel.Range rng = (Excel.Range)xlSheet.Columns[2, Type.Missing];
    rng.NumberFormat = "0.00";
    rng.AutoFit();
}

  7. Add the following procedure, which calls the other procedures you created to load the data into the Excel workbook:

    ' Visual Basic
Private Sub LoadDataAndCreateChart()
    ResetWorkbook()
    SetupWorksheet()
    Dim dr As SqlDataReader = GetDataReader()
    LoadAndFormatData(dr)
End Sub

// C#
private void LoadDataAndCreateChart() 
{
    ResetWorkbook();
    SetupWorksheet();
    SqlDataReader dr = GetDataReader();
    LoadAndFormatData(dr);
}

  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 link you added previously, and verify that your code has imported and formatted the data, as shown in Figure 1.

  11. Save the workbook. Close Excel and return to Visual Studio .NET.

Adding the Chart

To complete this walkthrough, you need to add the code that uses the ChartWizard method provided by the Excel Chart object to create the new chart. This method accepts a large number of optional parameters, and this example uses only a few of the many possible options.

To add a chart

  1. Add the following procedure to the OfficeCodeBehind class.

    This procedure creates the chart, calls the ChartWizard method of the chart, and finally adds some formatting to the chart elements:

    ' Visual Basic
Private Sub CreateChart()
    ' Now create the chart.
    xlChart = _
        DirectCast(ThisWorkbook.Charts.Add(After:=xlSheet), _
        Excel.Chart)

    Dim cellRange As Excel.Range = _
        DirectCast(xlSheet.Cells(1, 1), Excel.Range)
    xlChart.ChartWizard( _
        Source:=cellRange.CurrentRegion, _
        Gallery:=Excel.Constants.xl3DBar, _
        PlotBy:=Excel.XlRowCol.xlColumns, _
        CategoryLabels:=1, _
        SeriesLabels:=1, _
        HasLegend:=False, _
        Title:=xlSheet.Name)

    ' Apply some formatting to the chart.
    xlChart.Name = xlSheet.Name & " Chart"

    With DirectCast(xlChart.ChartGroups(1), _
        Excel.ChartGroup)
        .GapWidth = 20
        .VaryByCategories = True
    End With
    With xlChart.ChartTitle
        .Font.Size = 16
        .Shadow = True
        .Border.LineStyle = Excel.Constants.xlSolid
    End With
End Sub

// C#
private void CreateChart() 
{
    // Now create the chart.
    Excel.Chart xlChart = (Excel.Chart)ThisWorkbook.Charts.
        Add(Type.Missing, xlSheet, Type.Missing, Type.Missing);

    Excel.Range cellRange = (Excel.Range)xlSheet.Cells[1, 1];
    xlChart.ChartWizard(cellRange.CurrentRegion, 
        Excel.Constants.xl3DBar, Type.Missing, 
        Excel.XlRowCol.xlColumns, 1, 2, false, 
        xlSheet.Name, Type.Missing, Type.Missing, 
        Type.Missing);

    // Apply some formatting to the chart.
    xlChart.Name = xlSheet.Name + " Chart";

    Excel.ChartGroup grp = (Excel.ChartGroup)xlChart.ChartGroups(1);
    grp.GapWidth = 20;
    grp.VaryByCategories = true;
    xlChart.ChartTitle.Font.Size = 16;
    xlChart.ChartTitle.Shadow = true;
    xlChart.ChartTitle.Border.LineStyle = Excel.Constants.xlSolid;
}

    **Tip   **If you need to create your own charts, your best bet in attempting to decipher all the options available to the ChartWizard method is to take advantage of the Excel macro recorder. That is, within in Excel, once you've loaded your data, select the Tools menu, point to Macro, and click Record New Macro. Once you've turned on the macro recorder, select Chart on the Insert menu, and follow the prompts to create your chart. Once you're done, turn off the macro recorder (select Tools, point to Macro, and click Stop Recording). Finally, you can view the code Excel recorded within the Visual Basic for Applications (VBA) editor in Excel—select Tools, point to Macro, and click VBA Editor to find the code. With little effort, you should be able to copy and paste the code from VBA into your Visual Studio .NET project. You may need to fix up some references, but once you've investigated the Excel object model a little, this shouldn't be terribly difficult. (Of course, all bets are off for C# developers—the conversion from VBA to C# is challenging, at best.)

  2. Modify the LoadDataAndCreateChart method, adding a call to the new CreateChart method, so that it looks like the following:

    ' Visual Basic
Private Sub LoadDataAndCreateChart()
    ResetWorkbook()
    Dim dr As SqlDataReader = GetDataReader()
    SetupWorksheet()
    LoadAndFormatData(dr)
    CreateChart()
End Sub

// C#
private void LoadDataAndCreateChart() 
{
    ResetWorkbook();
    SetupWorksheet();
    SqlDataReader dr = GetDataReader();
    LoadAndFormatData(dr);
    CreateChart();
}

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

  4. Within Excel, once again click the Create New Chart link, and verify that you've successfully loaded the data, and created and formatted the chart.

  5. Click the link again, and verify that the code correctly deletes the extra sheets, and recreates them.

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

Conclusion

The ChartWizard object makes it easy to create formatted charts in Excel. This walkthrough demonstrated how to use Visual Studio Tools for Office to create a project that works with Microsoft Office Excel 2003. Then, using the SQL Server Northwind sample database, you can use the ChartWizard method provided by the Excel Chart object to create a chart based on SQL Server data.