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.
<HTML>
<SCRIPT LANGUAGE="JavaScript" type=text/javascript src="pfcUtils.js"></SCRIPT>
<SCRIPT LANGUAGE="JavaScript" type=text/javascript src="bom2excel.js"></SCRIPT>
<BODY>
<form name="f">
<br><INPUT id="get_btn" type=button value="Get BOM" onclick="GetData()">
<INPUT id="clr_btn" type=button value="Clear" onclick="Clear()">
<br><div id="data"></div><br>
<br><div id="status"></div><br>
</form>
</BODY>
</HTML>
The Initialization Function
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.
function GetData () {
if (!pfcIsWindows())
netscape.security.PrivilegeManager.enablePrivilege("UniversalXPConnect");
var data_elem = document.getElementById("data");
var session = null;
var model = null;
// Get session object
try { session = pfcGetProESession(); }
catch (e) {
data_elem.innerHTML = "ERROR: Cannot connect to Pro/Engineer session!";
return;
}
// Make sure there is a model active
try { model = session.CurrentModel; }
catch (e) {
// probably no model
data_elem.innerHTML = "Problem getting current model info.";
return;
}
data_elem.innerHTML = "<br>" + "Top Level Model: " + model.FileName;
// Setup appdata object for bom data
var appdata = new Object();
appdata.params = new Array( "LEVEL", "NAME", "QTY", "DESC", "PROI_CREATED_ON" );
appdata.comppath_seq = new pfcCreate("intseq");
appdata.root = model;
// get bom data as an array of arrays
appdata.values = GetBOMData(model, appdata);
// send bom data
SendData(appdata);
}
The Recursive Function
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.
function GetBOMData ( model, appdata ) {
var data_elem = document.getElementById("data");
var status_elem = document.getElementById("status");
var model_array = new Array(); // data for this model
var return_array = new Array(); // array to store model_array's
// Assign parent attribute for qty count
//
try {
model_array["PARENT"] = appdata.parent.FileName;
}
catch (e) {
// ignore exception, probably top-level asm
model_array["PARENT"] = "";
}
// Get params of current model
//
for (var i = 0; i < appdata.params.length; i++) {
if (appdata.params[i] == "LEVEL") {
model_array["LEVEL"] = appdata.comppath_seq.Count+1;
}
else if (appdata.params[i] == "NAME") {
model_array["NAME"] = model.FileName;
}
else if (appdata.params[i] == "QTY" && model == appdata.root) {
model_array["QTY"] = 1;
}
else {
var param = null;
var paramvalue = " -- n/a -- ";
try {
// get parameter object
param = model.GetParam(appdata.params[i]);
// get parameter value
switch (param.Value.discr) {
case pfcCreate("pfcParamValueType").PARAM_STRING:
paramvalue = param.Value.StringValue;
break;
case pfcCreate("pfcParamValueType").PARAM_INTEGER:
paramvalue = param.Value.IntValue;
break;
case pfcCreate("pfcParamValueType").PARAM_BOOLEAN:
if (param.Value.BoolValue)
paramvalue = true;
else
paramvalue = false;
break;
case pfcCreate("pfcParamValueType").PARAM_DOUBLE:
paramvalue = param.Value.DoubleValue;
break;
}
}
catch (e) {
// param probably doesn't exist, ignore
}
// store param value in model array
model_array[appdata.params[i]] = paramvalue;
}
}
// store model array in return array
return_array.push(model_array);
// Recurse into components, if model is an assembly
//
if ( model.Type == pfcCreate("pfcModelType").MDL_ASSEMBLY ) {
var compMdl = null;
var qtyIndexName = null;
var qtyCount = new Array();
// get component sequence of current subasm
var components = model.ListFeaturesByType( false, pfcCreate("pfcFeatureType").FEATTYPE_COMPONENT );
// loop through components
for (var i = 0; i < components.Count; i++) {
var compFeat = components.Item(i);
if (compFeat.Status != pfcCreate("pfcFeatureStatus").FEAT_ACTIVE) {
continue; // skip inactive components
}
// Append component id to sequence (for building ComponentPath)
appdata.comppath_seq.Append(compFeat.Id);
// get model object of component
try {
// have to create ComponentPath object first, then use "Leaf" property
var cp = pfcCreate("MpfcAssembly").CreateComponentPath( appdata.root, appdata.comppath_seq );
compMdl = cp.Leaf;
} catch (e) {
status_elem.innerHTML += "<br> CreateComponentPath() exception: " + pfcGetExceptionType(e);
}
// using a unique index (subasm & comp names) for the qty count array
qtyIndexName = model.FileName+"/"+compMdl.FileName
appdata.parent = model;
// Descend into subassembly, if model has not been processed in this subasm
if ( !(qtyIndexName in qtyCount) && compMdl != model ) {
// concatenated arr into return_array (concat doesn't seem to work)
var arr = GetBOMData(compMdl, appdata);
for (var j=0; j<arr.length; j++) {
return_array.push(arr[j]);
}
arr = null;
}
// initialize and increment qty count for this subasm/component
if ( ! (qtyIndexName in qtyCount) ) {
qtyCount[qtyIndexName] = 0;
}
qtyCount[qtyIndexName]++;
// Remove last id in sequence, not needed anymore
try {
appdata.comppath_seq.Remove( (appdata.comppath_seq.Count-1), (appdata.comppath_seq.Count) );
} catch (e) {
status_elem.innerHTML += "<br> comppath_seq.Remove exception: " + pfcGetExceptionType(e);
}
} // Loop: components
// process arrays (for qty adjust) returned from GetBOMData() call
for (var i = 1; i < return_array.length; i++) {
var compName = return_array[i]["NAME"];
qtyIndexName = model.FileName+"/"+compName;
// Adjust qty for current level objects
if (return_array[i]["PARENT"] == model.FileName) {
for (var j = 0; j < appdata.params.length; j++) {
// make sure qty was requested
if (appdata.params[j] == "QTY") {
if (qtyIndexName in qtyCount) {
return_array[i]["QTY"] = qtyCount[qtyIndexName];
}
else {
return_array[i]["QTY"] = 1;
}
}
}
}
}
qtyCount = null;
} // model.Type
return return_array;
}
The Sending Function
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.
function SendData ( appdata ) {
var oXL = null;
var data_elem = document.getElementById("data");
if (appdata.values.length == 0) {
data_elem.innerHTML = "No data to send!";
return;
}
if (pfcIsWindows()) {
// Get/Create Excel Object Reference
try {
oXL = GetObject("","Excel.Application"); // Use current Excel session
}
catch (e) {
// couldn't get an excel session, try starting a new one
try {
oXL = new ActiveXObject("Excel.Application"); // Open new Excel session
}
catch (e) {
// couldn't start a new excel session either
}
}
if (oXL == null) {
data_elem.innerHTML = "Could not get or start Excel session!";
return;
}
// Create new workbook
try {
oXL.Visible = true;
var oWB = oXL.Workbooks.Add();
var oSheet = oWB.ActiveSheet;
}
catch (e) {
data_elem.innerHTML = "Problem creating new workbook.";
return;
}
// Write header cells
for (var i=0; i < appdata.params.length; i++ ) {
oSheet.Cells(1, i+1).Value = appdata.params[i];
}
// Write data cells
for (var i=0; i < appdata.values.length; i++ ) {
for (var j=0; j < appdata.params.length; j++ ) {
oSheet.Cells(i+2, j+1).Value = appdata.values[i][appdata.params[j]];
}
}
}
else {
// Not a windows platform, write data to browser
// Write header cells
data_elem.innerHTML += appdata.params.join(" / ");
// Write data cells
for (var i=0; i < appdata.values.length; i++ ) {
data_elem.innerHTML += "<br>";
for (var j=0; j < appdata.params.length; j++ ) {
if (j > 0) { data_elem.innerHTML += " / "; }
data_elem.innerHTML += appdata.values[i][appdata.params[j]];
}
}
}
}
The Cleanup Function
The Clear() function is very simple. It just blanks the content div fields.
function Clear() {
var data_elem = document.getElementById("data");
var status_elem = document.getElementById("status");
data_elem.innerHTML = "";
status_elem.innerHTML = "";
}
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.
Questions and comments are always welcome, either here on my blog or at MarcMettes@InversionConsulting.com.
2 comments:
Gr8 Article, nicely put. thanks
It's really helpful, thank you for your sharing!
Post a Comment