Integrating Visio 2007 and Excel 20...

Switch on low bandwidth view

Community Content

In this section

Statistics Annotations (0)

Click here to expand

Language Filter

Integrating Visio 2007 and Excel 2007

Summary: Visio 2007 and Excel 2007 provide built-in integration capabilities. See an example of linking data in Visio to Excel by using the Visio Data Selector and an example of creating and populating an Excel Bill of Materials by using Automation code. (10 printed pages)

Visimation, Inc.

October 2006

Applies to: Microsoft Office Visio 2007, Microsoft Office Excel 2007

Contents

Generate an Excel Bill of Materials from Data Stored in Shapes by Using the Data Reports Tool
Generate an Custom Excel Bill of Materials from Data Stored in Shapes by Using Automation Code
The Visio Drawing
The Excel Bill of Materials
Code Example to Extract Data from the Visio Drawing and Fill in the BOM
About the Author
Additional Resources

Generate an Excel Bill of Materials from Data Stored in Shapes by Using the Data Reports Tool

From within Microsoft Office Visio 2007, you can report your data out in a variety of ways. One example is reporting data from Visio 2007 to Microsoft Office Excel 2007. If you create a drawing and add data manually to the shape or to your shape data fields, you can generate an Excel 2007 report summarizing that data. In Figure 1, data was associated with the equipment shapes in the diagram by using shape data fields and displayed by using data graphics.

Figure 1. A network diagram with associated shape data

To report on the data contained in the diagram

On the Data menu, click Reports.
In the Reports dialog box, select the report to run. You can modify an existing report or create and save a new report.

Figure 2. The Reports dialog box enables you to run or modify existing reports or create a new report
Select Modify or New to choose the fields on which you want to report.
In the Report Definition Wizard, choose which shapes you want to include in your report—Shapes on all pages, Shapes on the current page, or Selected shapes.

Figure 3. Specify the shapes in your report
On the next screen of the Report Definition Wizard, specify which fields you want to export to your Excel 2007 report.
On the next screen, type a title for your report and indicate how you want the data to be sorted and formatted.
Run the report; choose Excel as the reporting format.

Figure 4. Data reported to Excel from the Visio 2007 network diagram

Generate a Custom Excel Bill of Materials from Data Stored in Shapes by Using Automation Code

The following example code generates an Excel Bill of Materials (BOM) from data that is extracted from a Visio drawing. Visio drawings are more than just pictures. Visio shapes can have custom data, called Shape Data, defined for any shape. Data can be stored within Shape Data. You can use Automation code to extract and organize this data before writing it into the Excel workbook.

The Visio Drawing

In this example the data to be captured in the BOM is a count of the number of shapes, by type, within the drawing. We are interested only in a specific kind of shape, those that are of types Part 1, Part 2, Part 3, and Part 4. We are not interested in other kinds of shapes, such as the connectors or the text on the page. To enable us to detect this condition easily, we have assigned to these shapes a user-defined cell named User.PartName. An example can be seen in Figure 5, which shows the Visio ShapeSheet spreadsheet for the selected shape. This user cell simplifies the search criteria. Instead of searching through the shapes looking for a list of shapes by part names, we can make a single comparison to look for the existence of the user cell. Shapes that do not have this cell, such as the connectors in this drawing, are ignored.

Figure 5. The Visio drawing from which the BOM material will be extracted

Visio drawing from which the BOM is extracted

The Excel Bill of Materials

The Excel BOM file has been preconfigured with layout and formulas already in the file. Figure 6 shows the file as it looks in its preconfigured state. After the example code runs, the count for each part that has the cell User.PartName is inserted into the column labeled Quantity. The Document Name and Date are also added to the BOM. The final result is shown in Figure 7.

Figure 6. The Excel BOM template

Figure 7. The completed BOM

Code Example to Extract Data from the Visio Drawing and Fill in the BOM

using System;
using System.Data;
using System.Data.OleDb;
using System.Collections;
using System.Windows.Forms;

using Visio = Microsoft.Office.Interop.Visio;
using Excel = Microsoft.Office.Interop.Excel;

BOM creation is initiated by an external event, such as a user clicking a button on a form (not shown in this example), which calls the GenerateBOM method. This is a high-level entry point that opens a Visio drawing, extracts information from the drawing, and then writes the data to the BOM file. The readVisioDrawing method reads the data from the Visio drawing. The createExcelFile method opens the preconfigured BOM shown previously and updates the part data.

private static string _DocumentName = string.Empty;

internal static void GenerateBOM(
    string drawingFileName,
    string excelFileName)
{
    // Get the name of the Visio document to be opened.
    _DocumentName =
        System.IO.Path.GetFileNameWithoutExtension(drawingFileName);

    // Read the information from the drawing.
    readVisioDrawing(drawingFileName);

    // Create the Excel file.
    createExcelFile(excelFileName);
}

The readVisioDrawing method opens the Visio drawing and then searches all shapes on all pages to find every shape that has the User.PartName cell. Individual shapes are processed within the processDrawingShape method. The information from the Visio drawing is accumulated in a hash table.

private static Hashtable _PartNameQuantity = new Hashtable();
 
private static void readVisioDrawing(
    string drawingFileName)
{
    // Open the drawing in Visio.
    Visio.Application visioApplication = new Visio.Application();
    Visio.Document partsDocument = 
        visioApplication.Documents.Open(drawingFileName);

    // Gather the information from the drawing.
    _PartNameQuantity.Clear();
    foreach (Visio.Page thisPage in partsDocument.Pages)
    {
        foreach (Visio.Shape thisShape in thisPage.Shapes)
        {
            processDrawingShape(thisShape);
        }
    }

    // Close the drawing.
    visioApplication.Quit();
}

The processDrawingShape method reads the part identifier and updates the count in the hash table. It checks for the User.PartName cell in each shape. If the cell exists, it gets the part name data from the cell. If the cell does not exist, an error is generated and no counts are incremented for this shape. As a last step, the method checks to see if this shape has subshapes, to determine if it is a group shape. If the shape does have subshapes, the processDrawingShape method makes a recursive call to itself to process the subshapes of the group.

private const string _VisioPartNameCell = "User.PartName";
private const string _VisioPropCellPrefix = "Prop.";
private static Hashtable _PartNameQuantity = new Hashtable();

private static void processDrawingShape(
    Visio.Shape thisShape)
{
    try
    {
        // Get the part name from the shape.
        string partName =
            thisShape.get_Cells(_VisioPartNameCell).
            get_ResultStr((short)Visio.VisUnitCodes.visNoCast).ToUpper();

        // Reflect this shape in the part name / quantity table.
        if (_PartNameQuantity.Contains(partName))
            _PartNameQuantity[partName] =
                (int)_PartNameQuantity[partName] + 1;
        else
            _PartNameQuantity.Add(partName, 1);
    }
    catch
    {
        // Ignore shapes without a part name.
    }

    // Process any subshapes within a group shape.
    foreach (Visio.Shape thisSubShape in thisShape.Shapes)
    {
        processDrawingShape(thisSubShape);
    }
}

The createExcelFile method creates the new Excel file based on the preconfigured BOM template file _ExcelTemplateName. This file is first copied to excelFileName so that the original template file is not changed. The updatePartsTable method fills in the quantity values in the BOM with the information extracted from the Visio drawing. The updateDocumentInformation method adds the document name and date to the BOM.

private const string _ExcelTemplateName = "ExcelBOM.xls";

private static void createExcelFile(
    string excelFileName)
{
    // Copy the Excel file template to the new file name.
    System.IO.File.Copy(
        Application.StartupPath + "\\" + _ExcelTemplateName, 
        excelFileName, true);

    // Update the parts table information.
    updatePartsTable(excelFileName);

    // Update the document information.
    updateDocumentInformation(excelFileName);
}

The updatePartsTable method updates the parts table information in the worksheet. Because this information is in a worksheet that has a header row, we can use ADO.NET to access the data through a dataset. The parts table is a named range of the worksheet that contains the part name and corresponding quantity. It has a header row as the first row in the named range.

private const string _PartsTableConnectionString =
    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=Yes\"";
private const string _PartsTableName = "PartNameQuantity";
private const string _PartsTableNameColumn = "Part Name";
private const string _PartsTableQuantityColumn = "Quantity";
private const string _PartsTableSelectString = "Select * From [{0}]";
private const string _PartsTableUpdateString = 
    "UPDATE [{0}] SET [{1}] = ? WHERE [{2}] = ?";

private static void updatePartsTable(
    string excelFileName)
{
    // Open the data portion of the Excel file as a dataset.
    OleDbConnection connection = new OleDbConnection(
        string.Format(_PartsTableConnectionString, excelFileName));
    connection.Open();

    // Access the part quantity table.
    OleDbDataAdapter partQuantityAdapter = new OleDbDataAdapter(
        string.Format(_PartsTableSelectString, _PartsTableName),
        connection);
    DataSet partQuantityDataset = new DataSet();
    partQuantityAdapter.Fill(partQuantityDataset, _PartsTableName);
    if (partQuantityDataset.Tables.Count > 0)
    {
        // Update the records with the quantities from the drawing.
        foreach (DataRow thisRow in partQuantityDataset.Tables[0].Rows)
        {
            string partName = thisRow[_PartsTableNameColumn].ToString().ToUpper();
            if (_PartNameQuantity.Contains(partName))
                thisRow[_PartsTableQuantityColumn] = (int)_PartNameQuantity[partName];
            else
                thisRow[_PartsTableQuantityColumn] = 0;
        }

        // Generate the update command.
        partQuantityAdapter.UpdateCommand = new OleDbCommand(
            string.Format(_PartsTableUpdateString, _PartsTableName,
            _PartsTableQuantityColumn, _PartsTableNameColumn), connection);
        partQuantityAdapter.UpdateCommand.Parameters.Add(
            "@" + _PartsTableQuantityColumn, OleDbType.Numeric).
            SourceColumn = _PartsTableQuantityColumn;
        partQuantityAdapter.UpdateCommand.Parameters.Add(
            "@" + _PartsTableNameColumn, OleDbType.VarChar, 255).
            SourceColumn = _PartsTableNameColumn;

        // Push the updated data back to Excel.
        partQuantityAdapter.Update(partQuantityDataset, _PartsTableName);
    }

    // Close the connection.
    connection.Close();
}

The updateDocumentInformation method updates the document name and data into the worksheet. Because the cells in the worksheet are not in a table that has a header row, the Excel application programming interface (API) is used to change the cells directly.

private const string _DocumentNameCell = "DocumentName";
private const string _DocumentDateCell = "DocumentDate";

private static void updateDocumentInformation(
    string excelFileName)
{
    object optional = System.Reflection.Missing.Value;
    
    try
    {
        // Open the Excel file and use the Excel API.
        // Excel stays hidden during this process.
        Excel.Application excelApplication = new Excel.ApplicationClass();

        Excel.Workbook excelWorkbook =
            excelApplication.Workbooks.Open(excelFileName,
            optional, optional, optional, optional, optional,
            optional, optional, optional, optional, optional,
            optional, optional, optional, optional);

        // Update the records with the values from the drawing.
        Excel.Worksheet excelWorksheet =
            (Excel.Worksheet)excelWorkbook.Worksheets[1];
        excelWorksheet.get_Range(_DocumentNameCell, optional).
            Value2 = _DocumentName;
        excelWorksheet.get_Range(_DocumentDateCell, optional).
            Value2 = System.DateTime.Today;

        // Save and close.
        excelWorkbook.Save();
        excelWorkbook.Close(false, optional, optional);
        excelApplication.Quit();
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
}

About the Author

Since 1997, Visimation has helped companies improve their productivity by providing visual software tools to ease business and technical tasks and by offering a broad range of consulting services focusing on Microsoft Visio as a platform for rapid development of efficient Automation programs.

Additional Resources

For more information about automating Excel and using the Visio object model to traverse shapes in a drawing, see the following resources:

For more information about Visio 2007 integration, see the following articles: