One question I read frequently on the forums is about how to get BOM data of a Pro/ENGINEER assembly into Excel. Typically the solutions involve saving files to disk, then some editing, and finally reading that data into Excel.

This example will demonstrate how to skip these extra steps and, using Pro/WebLink, send your BOM directly from Pro/ENGINEER into Excel.

The HTML Page

The starting point is this very simply HTML page. At the beginning, it pulls in two JavaScript libraries, pfcUtils.js and bom2excel.js. As mentioned in my previous Pro/WebLink article, pfcUtils.js is a small PTC provided library. bom2excel.js will contain the remaining JavaScript code mentioned in this article.

The HTML page also contains two buttons and two div fields. The two div fields are "buckets" used for output and status messages and will contain HTML code added programmatically. One button initiates the action and the other clears the div fields.

The GetData() function initializes the data structures, gets the BOM data using the recursive GetBOMData() function, and sends the data to Excel or the browser using the SendData() function.

Once we're sure that we're connected to a Pro/ENGINEER session properly and a model is active, the function sets up an object that will be used by the recursive GetBOMData() function. The properties of this object are "params", "comppath_seq" and "root".

The params property lists the columns that will appear in the output. Three of the columns ("LEVEL", "NAME", and "QTY") are special and have supporting code to populate their values. All others are presumed to be Pro/ENGINEER parameters and are treated as such.

The comppath_seq and root properties are used to transform component feature objects into model objects via the pfcComponentPath class.

When the appdata object has been setup, it is passed to GetBOMData, which returns an array of "model arrays". Each "model array" contains information about each part or assembly that was encountered in the BOM. This array of arrays is assigned to the "values" property of the appdata object.

The object is then passed to SendData(), which will attempt to put the data into Excel.

The GetBOMData() function recursively gathers the BOM data for an assembly. There are three main actions performed in this function: attribute gathering, recursing (for subassemblies), and quantity adjustments.

Before the current model attribute gathering, the parent name of the currently encountered component is stored. The logic used here flattens the tree structure of the assembly into a single array. In order to adjust the quantity, the parent needs to be tracked in order to adjust the quantity for the current level only.

In the attribute gathering code, you'll see code handling the three special attributes: level, name, and qty. Name is simply the model name. Qty is used here only for the top-level object, which always has a quantity of one. Level is calculated from the comppath_seq property. The ComponentPath is essentially an array of feature id's that let you walk through the assembly structure to a specific component. The length of the array indicates the component level in the assembly.

Any other items encountered in the params property of the appdata object is assumed to be a Pro/ENGINEER parameter and the GetParam() method is used to obtain its object. A try block handles the situation where there is no parameter of that name and a default value is used instead.

In the recursing section, which is skipped if the encountered model is a part, the code loops through all of the assembly components. The are four main actions performed in the loop. First addressed is building the ComponentPath, by appending the component's feature id, which gives the pfcModel object of the component. Second is determining whether to recurse, and handling the resulting arrays if it does. Components are not processed more than once at a given level. In the third action, the quantity count is initialized, if necessary, and incremented. Finally, the component id is removed from the comppath_seq.

The final task in GetBOMData() is to adjust the quantity. This is done by looking up component names in the qtyCount associative array. This is done only for components returned from recursive calls, which explains why the loop starts at index 1 not 0. A component cannot know how many times it is assembled. This can only be known from the subassembly level.

Finally, the array of model_arrays is returned back the previous level.

The SendData() function is used to send the data to Excel (Windows) or to the browser (Unix).

On Windows, the code gets an Excel session object, either from an existing session or by starting a new one, if necessary. Your IE security settings may cause a new session to be started every time. A new workbook is created, and the data is written to the cells, headers first, then data rows.

The column header values are pulled from the params property array of the appdata object. These values are used to look up values in each model_array from the values property. You'll note that Excel cell indexes start at 1 and not 0 as with the JavaScript arrays.

On Unix, the data is written to the "data" div field on the HTML page, also using the params property for the headers and values property for the parameter values.

The code is somewhat more complex than I had expected, but this is largely due to the quantity adjustment. Strip out this and the code is signifcantly more terse, but less functional of course. I have a enhanced version of this application that gets the attribute data from a list in a textfield. This is a bit more practical because it allows for changes at runtime without having to edit the code. If there is interest, I will discuss those changes.