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.
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);
}
}