The topic you requested is included in another documentation set. For convenience, it's displayed below. Choose Switch to see the topic in its original location.

Lesson 3: Data Input

Lesson 3: Data Input

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

Download The Lesson 3: Data Input sample files are available from the Microsoft Download Center.

Topics in this lesson

    Using a Form to Get Shape Properties
    Reading Shape Properties from an Excel Spreadsheet
Summary: Data Input


In this lesson we extend the COM add-in from Lesson 2: Event Handling, to invoke a wizard that gathers user preferences for creating a simple flowchart. Through the wizard, the user can determine the number of flowchart shapes to draw and modify shape properties. We extend the DocumentCreator class from the previous lesson so that the COM add-in creates a DocumentCreator object using the wizard data.

We demonstrate extending the COM add-in to get user input from a wizard implemented using Microsoft® Windows® Forms and create a flowchart. For comparison, we also discuss how the COM add-in could get its data from a Microsoft Excel spreadsheet. This lesson explains how both data input methods work and their differences in implementation.

This lesson shows you how to:

  • Manage shape properties through a custom ShapeProperties class and store them using the ArrayList class.
  • Implement the TutorialAddin wizard using Windows Forms.
  • Display shape properties using the DataGrid class and allow the user to edit shape properties.
  • Draw the flowchart shapes and connect them.

To see the implementation of the sample application for this lesson, open the solution using Microsoft Visual Studio® .NET. The solution is found in the Lesson3 folder and is named Lesson3.sln.

Using a Form to Get Shape Properties

As shown in Lesson 2, the TutorialAddin sample responds to the user creating a new Visio document based on the Managed Tutorial.vst template. However, instead of simply drawing a single shape as in Lesson 2, we have extended the sample code to invoke the TutorialAddin wizard.

The wizard walks the user through the following sequence of steps:

  1. It asks the user whether to create a 4- or 8-shape (representing the steps) flowchart.
  1. It displays the default shape properties.
  1. It displays a summary page when the user has finished selecting the shape properties.
  • When the user clicks the
  • Finish
  • button, the COM add-in draws the flowchart.
Managing shape properties

We will need to manage two pieces of information about the flowchart that the COM add-in will draw: the number of shapes and the properties for each shape.

We will store this information in private members of the Wizard class, as shown in the following declarations:

Private shapeCountValue As Integer = 0
Private shapeListArray As New ArrayList()

The values in these private members represent the form state for the wizard. In order to maintain this state information, the DocumentCreator class instance is given a reference to a Wizard object when it is created. The Wizard object displays the wizard and holds the form state information. The Wizard class exposes two public properties to allow access to the wizard data: ShapeCount and ShapeList.

The shape properties are stored using the ArrayList class. ArrayList is a smart array, which means it dynamically grows and shrinks as elements are added to or removed from it. If we want to add a new element to the ArrayList, we call the Add method. The ArrayList makes it easy to bind the shape properties to the DataGrid control for display to the user. Note that there is no need to declare the type of the elements stored in our ArrayList member because ArrayList can contain heterogeneous objects.

For the shape properties that the wizard allows the user to modify, we create a ShapeProperties class. Each element of the shapeList member will be a ShapeProperties object. The ShapeProperties class contains five private members, representing the shape master, fill color, line color, shape text, and hyperlink, as shown in the following declarations:

Private shapeTypeString As String
Private fillColorString As String
Private lineColorString As String
Private textString As String
Private hyperlinkString As String

The ShapeProperties class exposes public properties to get and set these values. It also has a constructor that allows you to initialize all the members. This allows us to initialize a default flowchart as follows:

Dim shapeIndex As Integer
shapeCountValue = 4
' Initialize shape properties.
For shapeIndex = 1 To ShapeCount
    shapeListArray.Add(New ShapeProperties(PROCESS_MASTER, _
    COLOR_WHITE, COLOR_BLACK, "Add shape text", _
    "Add shape hyperlink"))
Using Windows Forms to build the TutorialAddin wizard

Now that we have a way to store the information for customizing the flowchart, we can build the TutorialAddin wizard. Each page of the wizard is a separate Form class, which is declared as follows:

Imports System.Windows.Forms
Public Class Screen1
    Inherits Form

Inheriting from the Windows Forms base class gives us access to a wide array of form controls for customizing our wizard pages. The first wizard page contains two RadioButton controls, which allow the user to specify a 4- or 8-step flowchart.

To display this wizard page, we use the following code:

Dim screen As Form
Dim result As DialogResult
result = screen.ShowDialog()

The ShowDialog method does the following:

  • Makes the wizard modal with respect to Visio.
  • Displays the form (Figure 1).

Figure 1.  First page of the TutorialAddin wizard allows the user to specify a 4- or 8-step flowchart

Figure 1. First page of the TutorialAddin wizard allows the user to specify a 4- or 8-step flowchart

To actually affect the shapeCountValue member of the Wizard object, we need to pass a reference of the object to the form. This is done by overloading the Form class�s New operator as follows:

Public Sub New(ByVal wizard As Wizard)

To detect the user�s selection, we listen for the Click event on the NextButton button. When the user makes a selection, we use the public properties on Wizard to modify its state.

The following code in the Click event handler examines the Checked property of the radio button and modifies the shape properties depending on whether the user has chosen 4 or 8 shapes:

If Shapes4RadioButton.Checked Then
    If wizardObject.ShapeList.Count > 4 Then
        wizardObject.ShapeList.RemoveRange(4, 4)
    End If
    wizardObject.ShapeCount = 4
    Dim addShapes As Integer = 0
    If wizardObject.ShapeList.Count < 8 Then
        For addShapes = 1 To 4
            wizardObject.ShapeList.Add(New ShapeProperties( _
                "Add shape text", "Add shape hyperlink"))
    End If
    wizardObject.ShapeCount = 8
End If
Displaying shape properties

Now that we know the number of shapes, we can start modifying the shape properties. We can use the Windows Forms DataGrid control to display our five customizable shape properties to the user.

Choosing the ArrayList class to store our shape properties makes it easy to bind the shape properties data to the DataGrid control, as shown in the following code:

Dim tableStyle As New DataGridTableStyle()
Dim ShapeTypeColumn As New DataGridComboBoxColumn()
Dim ShapeTextColumn As New DataGridTextBoxColumn()
Dim FillColorColumn As New DataGridComboBoxColumn()
Dim LineColorColumn As New DataGridComboBoxColumn()
Dim HyperlinkColumn As New Windows.Forms.DataGridTextBoxColumn()
' Create a custom tableStyle for column display.
DataGrid1.DataSource = wizardObject.ShapeList()
tableStyle.MappingName = "ArrayList"
' Display shape type in a drop-down list
ShapeTypeColumn.MappingName = "ShapeType"
ShapeTypeColumn.HeaderText = "Type"
ShapeTypeColumn.ColumnComboBox.DropDownStyle = _
tableStyle.PreferredRowHeight = _
     (ShapeTypeColumn.ColumnComboBox.Height + 3)
' Diplay shape text in an edit box
ShapeTextColumn.MappingName = "Text"
ShapeTextColumn.HeaderText = "Text"
ShapeTextColumn.TextBox.MaxLength = 30
' Display fill color in a drop-down list
FillColorColumn.MappingName = "FillColor"
FillColorColumn.HeaderText = "Color"
FillColorColumn.ColumnComboBox.DropDownStyle = _
' Display line color in a drop-down list
LineColorColumn.MappingName = "LineColor"
LineColorColumn.HeaderText = "Line color"
LineColorColumn.ColumnComboBox.DropDownStyle = _
' Diplay hyperlink in an edit box
HyperlinkColumn.MappingName = "Hyperlink"
HyperlinkColumn.HeaderText = "Hyperlink"
HyperlinkColumn.TextBox.MaxLength = 255

Each GridColumnStyles item that we add to the DataGrid control allows us to specify the header text and the order that the columns are displayed in.

Notice how the MappingName property corresponds to each public property that we expose in the ShapeProperties class. The following figure shows the second page in the wizard.

Figure 2. Displaying shape properties

Figure 2. Displaying shape properties

Navigating the wizard pages

On the second wizard page, we need to detect more than the standard Cancel or OK return value, so we implement a WizardResult property for the form. The IWizardResult interface defines this property, which is implemented by each wizard form.

The WizardResult property returns the following additional return values, defined in the WizardResults enumeration:

Public Enum WizardResults
    buttonNone = 100
    buttonNext1 = 101
    buttonBack2 = 102
    buttonNext2 = 103
    buttonBack3 = 104
    buttonFinish = 105
End Enum

In the form, we set the private member wizardResultValue, whose value is returned by the WizardResult property, in the button�s Click event handler:

Private Sub BackButton_ClickEventHandler( _
    ByVal sender As System.Object, _
    ByVal eventData As System.EventArgs) _
    Handles BackButton.Click
    wizardResultValue = WizardResults.buttonBack2
End Sub

Because each wizard form implements the IWizardResult interface, we can define a screen variable of the generic Form type and use polymorphism. We set screen to the derived form type depending on which wizard screen is currently shown. Then we cast screen to the IWizardResult interface type and check the return value. The Wizard class�s GetShapeData method drives the wizard navigation process as follows:

Dim screen As Form
Dim result As DialogResult
Dim wizardResult As WizardResults
Dim wizardScreen1 As New Screen1(Me)
Dim wizardScreen2 As New Screen2(Me)
Dim wizardScreen3 As New Screen3(Me)
    ' Show wizard.
    screen = wizardScreen1
        result = screen.ShowDialog()
        If result = System.Windows.Forms.DialogResult.Cancel Then
            Return False
            wizardResult = CType(screen, IWizardResult).WizardResult
        End If
            Select Case wizardResult
                Case Is = WizardResults.buttonNext1
                    screen = wizardScreen2
                Case Is = WizardResults.buttonBack2
                    screen = wizardScreen1
                Case Is = WizardResults.buttonNext2
                    screen = wizardScreen3
                Case Is = WizardResults.buttonBack3
                    screen = wizardScreen2
            End Select
    Loop Until wizardResult = WizardResults.buttonFinish
    Return True
Catch err As Exception
    MsgBox("Exception in GetShapeData: " & _
        err.Message, , ERROR_TITLE)
End Try
Editing shape properties

The DataGrid control allows the user to select a shape. When the user clicks a cell in the grid, the cell�s control is enabled for editing. For example, a drop-down list is displayed (Figure 3) that allows the user to edit the properties for the selected shape. This is implemented by overriding the DataGridTextBoxColumn control�s Edit method as follows:

Protected Overloads Overrides Sub Edit(ByVal source As CurrencyManager, _
    ByVal rowNumber As Integer, ByVal bounds As Rectangle, _
    ByVal readOnly1 As Boolean, ByVal instantText As String, _
    ByVal cellIsVisible As Boolean)
    MyBase.Edit(source, rowNumber, bounds, readOnly1, instantText, _
    gridRowNumber = rowNumber
    sourceCurrencyManager = source
    ColumnComboBox.Parent = Me.TextBox.Parent
    ColumnComboBox.Location = Me.TextBox.Location
    ColumnComboBox.Size = New Size(Me.TextBox.Size.Width, _
    ColumnComboBox.Text = Me.TextBox.Text
    Me.TextBox.Visible = False
    ColumnComboBox.Visible = True
End Sub

This code replaces the text box for the shape type cell with a drop-down list box. The text box control is hidden and the list box is made visible, displaying the list of possible shape types. When the user selects a shape type from the list, the flowchart shape properties are modified by overriding the DataGridTextBoxColumn control�s Commit method as follows:

Protected Overloads Overrides Function Commit( _
    ByVal dataSource As CurrencyManager, ByVal rowNumber As Integer) _
    As Boolean
    SetColumnValueAtRow(dataSource, rowNumber, ColumnComboBox.Text)

Figure 3. Editing shape properties

Figure 3. Editing shape properties

Now that the shape properties are set, we can display a wizard summary page as shown in Figure 4.

Figure 4. Wizard summary page

Figure 4. Wizard summary page

Drawing the flowchart shapes

Once the user is finished using the TutorialAddin wizard, the add-in is ready to draw the flowchart. To draw the shapes, the CreateDrawing method iterates over the shapeList array, reading the master and dropping a shape of that type on the page in a random location as follows:

pinX = CInt(random.NextDouble() * 10)
pinY = CInt(random.NextDouble() * 10)
shapeProp = CType(shapeList(stepID - 1), ShapeProperties)
shape = DropMasterOnPage(page, _
    shapeProp.ShapeType, _

Note how we have to perform an explicit cast to the ShapeProperties type. This is because the ArrayList class stores its elements as generic objects.

The CreateDrawing method passes the following parameters to the DropMasterOnPage method: the name of the flowchart stencil, the master as specified by the user, and coordinates for dropping the shape. We use random coordinates because we will lay out and center the flowchart after all shapes are drawn and connected.

Now we can apply the remaining shape properties. Reading from the ShapeProperties element for each shape, we can set each property for the current shape. For example, the following code sets the shape text:

shape.Characters.Text = shapeProp.Text
Connecting the flowchart shapes

Once each shape is created, we connect the shapes that represent the flowchart steps. The CreateDrawing method calls the ConnectWithDynamicGlueAndConnector procedure as follows:

' Connect previous step to the one just created.
If stepID > 1 Then
    ' Connect the two shapes.
    ConnectWithDynamicGlueAndConnector( _
        prevShape, shape)
End If
' Keep reference to previous shape for connecting steps.
prevShape = shape

The ConnectWithDynamicGlueAndConnector procedure accesses the flowchart stencil and its Dynamic Connector master. By holding a reference to the previous shape, the CreateDrawing method connects each new shape that it drops on the page to the previous shape.

Reading Shape Properties from an Excel Spreadsheet

Another implementation of the DocumentCreator class would allow the flowchart data to be read from a Microsoft Excel spreadsheet. To do so requires a well-defined layout for the spreadsheet and methods in our DocumentCreator class to open the spreadsheet, read the shape properties, and use the properties to create the flowchart. A full implementation of a COM add-in that reads from an Excel spreadsheet is not provided in the sample application installation; however, code examples are provided in the following sections to illustrate the primary implementation steps. By comparing the two implementations, you can decide which method suits your needs.

Opening the spreadsheet

To draw a flowchart, the user creates an Excel spreadsheet named Flowchart.xls and saves it in the same directory as the COM add-in. Each worksheet in Flowchart.xls represents a new flowchart drawing. Each row of a worksheet represents a new flowchart step (shape). Each worksheet column represents a shape property.

Figure 5. Example of Flowchart.xls

Figure 5. Example of Flowchart.xls (click to enlarge)

To read this spreadsheet, the DocumentCreator class first needs a reference to the Excel Primary Interop Assembly (PIA):

Imports Microsoft.Office.Interop.Excel

The CreateDrawing method creates a new instance of the Excel Application object as follows:

Dim excelApp As Excel.Application
Dim excelFile As String
Dim excelWorkbook    As Excel.Workbook
excelApp = New Excel.Application()
excelFile = GetFlowchartSamplePath() & "Flowchart.xls"
excelWorkbook = excelApp.Workbooks.Open(excelFile)

The Excel Application object gives the code access to the Excel type library�s object model so we can obtain a reference to the Workbook object representing Flowchart.xls. Note that the previous code qualifies the namespace of the Excel Application object. Because we are also using the Visio PIA, this eliminates any ambiguity about the Microsoft Office Application object we are working with. The GetFlowchartSamplePath method is a helper function to find the path of Flowchart.xls, assuming it is located in the same directory as the COM add-in, as the following code shows:

Public Function GetFlowchartSamplePath() As String
    Dim path As String = ""
    Dim sampleAddIn As System.Reflection.Module
        sampleAddIn = [Assembly].GetExecutingAssembly().GetModules()(0)
        path = sampleAddIn.FullyQualifiedName()
        path = path.Substring(0, InStrRev(path, "\"))
    Catch err as Exception
        MessageBox.Show("Exception in GetFlowchartSamplePath: " _
            & err.Message, ERROR_TITLE)
        End Try
    Return path
End Function
Storing shape properties

Next, the CreateDrawing method iterates over each Excel worksheet found in Flowchart.xls. Each flowchart is drawn on a separate Visio page starting with the following:

page = document.Pages(1)

The default property of the Pages collection, Item, returns a reference to a Page object. We then call a function on the DocumentCreator object called DrawPageFromExcelSheet, which specifies a Visio page for the new flowchart drawing, and the Excel worksheet to get data from as follows:

Private Function DrawPageFromExcelSheet( _
    page As Page, excelSheet As Excel.WorkSheet) As Boolean

The DrawPageFromExcelSheet function iterates over the worksheet cells to gather shape properties. First, we get a reference to the Cells object, and then we create a structure to store the shape properties, as shown in the following code:

Dim excelCells As Excel.Range
Dim stepID As String
Dim shape as Shape
excelCells = excelSheet.Cells
shapeIDs = New SortedList()

We use the SortedList class, rather than the ArrayList class used by the TutorialAddin sample, because the flowchart shapes defined in the worksheet can be in any order.

The SortedList class represents a collection of key-and-value pairs that are sorted by the keys and are accessible by key and by index. When we want to add a new shape that represents a flowchart step, we use the Add method, specifying the stepID read from the worksheet as the key, and the reference to the Visio Shape object as the value.

The following code shows how to add a new value to the list:

shapeIDs.Add(stepID, shape.ID)

The list is sorted by the key value, which must implement the IComparable interface. In our code, the key is a String type, which implements the CompareTo method of the IComparable interface. The list automatically is sorted on the key, from the lowest stepID to the highest.

Reading shape properties

To read the properties for each shape, the Flowchart sample calls the following code:

rowIndex = EXL_ROW_START
stepID = GetExcelCellValue(excelSheet, rowIndex, EXL_COL_STEP_ID)

We start reading cell values on the second row (the first row contains the column headers and does not specify a shape itself). Each column is defined by a constant, such as EXL_COL_STEP_ID to indicate the stepID, representing the column number for a particular shape property. This means the Flowchart sample expects the column order to be the same, with the stepID always being the first column.

The GetExcelCellValue function retrieves a cell value, specified by row and column, using the following code:

Private Function GetExcelCellValue( _
    excelSheet As Excel.Worksheet, _
    rowIndex As Integer, _
    columnIndex As Integer) As String
    Dim excelCell As Excel.Range
    Dim cellValue As Object
    Dim returnValue As String
    excelCell = CType(excelSheet.Cells.Item( _
        rowIndex, columnIndex), Excel.Range)
    cellValue = excelCell.Value
    If (cellValue is Nothing) Then
        returnValue = ""
        returnValue = CStr(cellValue)
    End If
End Function

Note that the GetExcelCellValue function returns an empty string when no value is found. When the Flowchart sample looks for a stepID and receives an empty string, there are no remaining shapes defined in the worksheet.

Setting shape properties

The first task in actually drawing each flowchart shape is to determine the shape type and create the shape, as shown in the following code:

stepType = GetExcelCellValue(excelSheet, rowIndex, _
shape = DropMasterOnPage(page, _
    GetMasterNameFromStepType(stepType), _

The master to use for drawing the shape is defined in the column represented by EXL_COL_STEP_TYPE. To draw the shape, call the DropMasterOnPage function (see the topic Drawing the flowchart shapes).

Each shape property is mapped to a worksheet column. For example, to set the shape text, we do the following:

shape.Characters.Text = GetExcelCellValue(excelSheet, _
    rowIndex, EXL_COL_TASK)

Setting the shape�s fill color is handled a little differently. The color corresponds to the owner of the flowchart step. This requires each owner to be mapped to a color as follows:

Select Case UCase(owner)
Connecting the flowchart shapes

The code that connects one flowchart shape is more complex than in the TutorialAddin sample, in that it does not limit shape connections to the step immediately following the preceding step. It even allows one step to be connected to multiple steps.

After all the shapes are created, we must read the value of the Next Steps column to connect steps as follows:

nextSteps = GetExcelCellValue( _
    excelSheet, rowIndex, EXL_COL_NEXT_STEPS)

Now we call a helper function named ParseNextSteps:

If ParseNextSteps(nextSteps, nextStepID, nextStepCount) Then

The parameters for ParseNextSteps are as follows:

  • nextSteps is a String
  • nextStepID is an Integer array
  • nextStepCount is the array size

The value of the nextSteps parameter is read from the Next Steps column as shown above and is a string representing the comma-separated list of steps that the shape is connected to (for example, "2,3,4"). We read these values into the nextStepID array.

To connect the steps, we use the key (the shapeID) to get a reference to the shape using the following code:

shape = page.Shapes.ItemFromID( _

Now we can use the keys stored in the nextStepID array to get each shape to connect to:

nextShape = page.Shapes.ItemFromID( _
    CInt(shapeIDs.Item( _

Now that we have a reference to the two shapes to connect, we call the ConnectWithDynamicGlueAndConnector procedure to connect the shapes.

ConnectWithDynamicGlueAndConnector( _
    shape, nextShape)

This is the same procedure called by the TutorialAddin sample. For more details, see the topic Connecting the flowchart shapes.

Summary: Data Input

Both the TutorialAddin sample and the example that reads from an Excel spreadsheet provide implementations of the DocumentCreator class to gather the shape properties for a flowchart. Both show quick ways of jump-starting the design of a flowchart.

The two samples differ in these ways:

  • The TutorialAddin sample provides a more interactive way of designing a flowchart by implementing a wizard.
  • The implementation that reads from an Excel spreadsheet provides a more automated method by defining a custom spreadsheet for a flowchart�s appearance.

The TutorialAddin sample developed in this lesson has a rich degree of functionality and can be customized easily for your applications. We will make one last change to the sample, but not a functional one.

Rather, in Lesson 4: Performance, we will improve the performance of the drawing code.

© 2015 Microsoft