ФОРУМ за света на PTC CREO CAD/CAM/CAE софтуерните решения


www.soft.cadcamcae.bg - Софтуерни решения за машиностроене;

www.forum.cadcamcae.bg - Форум;

www.blog.cadcamcae.bg - Блог;

http://e-shop.cadcamcae.bg - Електронен CAD магазин;



3D инженерни мишки;


ФОРУМ за света на PTC CREO CAD/CAM/CAE софтуерните решения

ФОРУМ: Част от проекта на списание CAD ДАЙДЖЕСТ - www.cadcamcae.bg;

CAD ДАЙДЖЕСТ . Едно онлайн издание за CAD/CAM/CAE технологии и машиностроене

ИндексИндекс  PortalPortal  КалендарКалендар  ГалерияГалерия  Въпроси/ОтговориВъпроси/Отговори  ТърсенеТърсене  ПотребителиПотребители  Потребителски групиПотребителски групи  Регистрирайте сеРегистрирайте се  ВходВход  
Потребителско име:
Искам да влизам автоматично с всяко посещение: 
:: Забравих си паролата!
Social bookmarking
Social bookmarking digg  Social bookmarking delicious  Social bookmarking reddit  Social bookmarking stumbleupon  Social bookmarking slashdot  Social bookmarking yahoo  Social bookmarking google  Social bookmarking blogmarks  Social bookmarking live      

Bookmark and share the address of ФОРУМ за света на PTC CREO CAD/CAM/CAE софтуерните решения on your social bookmarking website
Новини от twitter.
Latest topics

Display results as :
Rechercher Advanced Search
Споделете с приятел.
Споделете форума В,,, с,,,в,,,е,,,т,,,а,,, н,,,а,,, CAD/CAM/CAE/PLM i Вашия форум за социално споделянеBookmark and Share
Май 2019


 Как да изпратим данни от Pro/E към Excel? част 2

Go down 

Male Брой мнения : 796
Местожителство : София, България
Job/hobbies : CAD/CAM специалист
Reputation : 3
Points : 6205
Registration date : 27.03.2008

Как да изпратим данни от Pro/E към Excel? част 2 Empty
ПисанеЗаглавие: Как да изпратим данни от Pro/E към Excel? част 2   Как да изпратим данни от Pro/E към Excel? част 2 Icon_minitimeВто 13 Май - 14:54:35

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(" &nbsp; / &nbsp; "); // 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.
Върнете се в началото Go down
Вижте профила на потребителя http://cadcamcae-bg.bulgarianforum.net
Как да изпратим данни от Pro/E към Excel? част 2
Върнете се в началото 
Страница 1 от 1

Права за този форум:Не Можете да отговаряте на темите
ФОРУМ за света на PTC CREO CAD/CAM/CAE софтуерните решения :: PTC CREO 4.0: СОФТУЕР ЗА МАШИНОСТРОЕНЕ :: CREO 4.0: Моделиер от висок клас (бивш Pro/ENGINEER Wildfire)-
Идете на: