Introduction

This document intended to provide a detailed guide and examples on how to write EKL script to Export Mfg. Item Structure to Excel.

Version – Validated on 2021x FD09 (FP2140), 2022x FD01 (FP2205), 2023x FD02 (FP2314), 2026x GA

Pre-requisites:

Licenses –

• CSV(Collaborative Industry Innovator)
• MDG(Mechanical Designer)
• PPL(Process Engineer)
• KDI(Template Designer)

Disclaimer: Licenses mentioned above are as per 2021x, 2022x & 2023x documentation.

Logical Flow

Below is the logical flow to Export Mfg Item Structure to Excel

Example

Export Mfg. Item Structure to Excel EKL Script

Get Level of Mfg Item EKL Script

VB Script for Template Excel Selector

Template Excel for Mfg Item Export

Detail Explanation

Template Excel format

The template excel is used to decide the column names or attributes to be exported. User can add as much as attributes or column names which needs to be exported. The order of columns can be changed as per user’s requirement. The EKL script is able to support any kind of sequence of columns.

Here is an example template:

The template excel is usually contains only column names. But in above example, content is displayed to make it easier to understand. In case there is data  present in the template excel, then also the script works. Because, from template excel, only 1st row gets read.

Though the sequence does not matter here, for some attributes, column names are as follows:

Note: If instance attributes value needs to be exported then add “Inst_” keyword before attribute name. For example for instance V_description, it will be “Inst_V_description”

How to decide whether to export Full MBOM, RollUp MBOM or Both

The EKL script attached above can export both Full MBOM and RollUp MBOM. User need to set some conditions in the EKL script.

Modify below code for the desired output:

/*Set values for below two variables for the desired type of MBOM. By default, both types of MBOMS will be exported */

Set Export_Full_MBOM = true //To export Flat MBOM or entire MBOM set this to true otherwise set to false

Set Export_RollUp_MBOM = true //To export RolledUp MBOM  set this to true otherwise set to false

For example, if user don’t want to export Full MBOM, "Set Export_Full_MBOM = false"

Copy column names from template excel to export excel

In this EKL Script, template excel is used just for deciding attributes to export and column sequence. The structure is exported in another excel file. The below code is responsible to copy column names from template excel to export excel.

For refColIdx =1 while refColIdx <= oNoOfRefCols
{
      strRefAttr = oXLSheet.CellAsString(0, refColIdx)
      oExportSheet.SetCell(1, refColIdx,strRefAttr)      
}

Same logic is used for Flat MBOM also.

Get list which includes Root Mfg Item and its children’s in same sequence as they appear in tree.

Below lines of code is responsible to get list of all “MfgProcessOccurrence” including Root MID in a same sequence as they appear in the tree.

// Get all children inside selected mfg item
listOfMfgItemOcc.AddItem(oRootMfgProcOcc, 1)
listOfMfgItemChildOcc = oRootMfgProcOcc->Query("MfgProcessOccurrence", "") // This list will be the entire list of oRootMfgProcOcc objects available under selected Root Mfg Item Node
listOfMfgItemOcc = listOfMfgItemOcc + listOfMfgItemChildOcc

Explanation for key points in  RollUp MBOM Structure code:

The code between below comments is responsible for Rolled Up MBOM generation:

//Code of script to export rolled up MBOM
.
.
//Code to export rolled up MBOM Ends

How to get RolledUp quantity

In RolledUp quantity, only Reference objects are considered. If same object is instantiated multiple times, that object will appear only once in MBOM and its quantity will be count of that objects instances. The object will be listed with its reference’s V_Name

The below code is responsible for RolledUp Quantity:

Here three Dictionaries (Volatile Instances) are maintained. One is to store existing objects titles (dictMfgRefTitles), One is to store Mfg Items RolledUp quantity (dictMfgRefQty)  and the third is to store Occurrence of Mfg Items.

If the title of object currently in loop is available in dictionary of existing objects or was found previously, its old quantity is increase by one.

if( (dictMfgRefTitles.HasAttribute( strCurrMfgItemTitle ) == true )) 
// We have already consumed this item at least once, just increment related quantity
{
    thisMfgItemQty    = dictMfgRefQty.GetAttributeInteger( strCurrMfgItemTitle )
    thisMfgItemQty    = thisMfgItemQty + 1 //increase the MfgItemQty by 1
    intTotalPartCount = intTotalPartCount + 1 
    //increase the Total Part Quantity by 1
    dictMfgRefQty.SetAttributeInteger(strCurrMfgItemTitle, thisMfgItemQty)                                                          
    //Reset the quantity value in list/dictionary
}

If the object is not available in dictionary of existing objects, its title is added in existing objects dictionary and a new Value or element for its quantity is created in dictionary of Mfg items quantity with value 1. Also its Occurrence is added in the dictionary of Occurrences.

else 
// First time encountering this consumed item - add it to the list and assign quantity = 1
{
      dictMfgRefTitles.SetAttributeString( strCurrMfgItemTitle, strCurrMfgItemTitle )
      thisMfgItemQty  = 1 //Set  the MfgItemQty to 1
      intUniquePartNums = intUniquePartNums + 1 
      //increase the Unique Part Quantity by 1
      intTotalPartCount    = intTotalPartCount + 1 
      //increase the Total Part Quantity by 1
      dictMfgRefQty.SetAttributeInteger( strCurrMfgItemTitle, thisMfgItemQty) 
      //Set the quantity value in list/dictionary
      dictMfgItemOcc.SetAttributeObject(strCurrMfgItemTitle, itrMfgItemOcc)
}

While processing the objects, for every object, total object count is increased by 1, so that we will have total Mfg Item count at end.

Note: here, title means reference objects V_name.

How to Write RolledUp MBOM in Excel

For rolled up MBOM, no any item will appear again and again. For this, we had created one dictionary of all Mfg item titles (dictMfgRefTitles). In this dictionary, each reference object title is listed only once.

A list is generated from this dictionary of titles using below code:

Let contentTitles(List)
// Dump unique item #'s and corresponding quantities
contentTitles = dictMfgRefTitles.ListAttributeNames("", True )

The section of code between below two comments will iterate on each title in list of titles. The Mfg item Occurrence with current title will be extracted from dictionary of occurrences and its attributes will be written in the excel one by one as per template excel.

/*Section of script to write the attributes to excel */
.
.
/*Section of script to write the attributes to excel ends */

Explanation for key points in Full MBOM Structure code:

In full MBOM, instances are consider for MBOM generation. For every instance, a entry will be there in MBOM. Here, quantity does not matter, as it will remain 1 for every instance.

The code between below comments is responsible for Full MBOM generation:

//Code to export Full MBOM Starts.
.
.
//Code to export Full MBOM ends

Get Level of Mfg Item

A separate excel file is called to get level of mfg item in list. The action file/EKL Script for getting level is attached above.  The Mfg items occurrence will and a variable to store level will be passed as input and the output will be stored in “lvlMfgItem”

scrGetMfgLvl.Run(currMfgItemOcc, lvlMfgItem) // Get its level and set inside the excel sheet

"lvlMfgItem" will have value of level after execution of action file.

How to export Linked Product String:

To generate the Linked Product String, below steps are followed:

1. Get list of assigned product on current mfg item
2. Start for loop on each item of list
3. Get the instance name of product in current iteration.
4. If the string is blank, put the instance name in the string
5. If the string has some value, add instance name in the previous value by separating by semicolon.

Below is the code responsible to export linked products:

// If column name is Linked product then get assigned prod of current mfg item
set listOfAssgPrd = currMfgItemOcc.AssignedProduct
strLinkedObjs=""  //make the string empty
For pAssgnProdOcc inside listOfAssgPrd
{                                                           
               Set oPrdInstance = pAssgnProdOcc.Instance //Get the instance of ProdOccurrence
               // If multiple prod linked to one mfg item then we will add ; to separate them
               If strLinkedObjs == ""
                              strLinkedObjs = oPrdInstance.Name
               else
                              strLinkedObjs = strLinkedObjs + ";" + oPrdInstance.Name
}                                            
oFlatMbomSheet.SetCell(oRowNum, indxOnCol,strLinkedObjs)

Here under some Use Case about possible adaptation of the script

1. Requirement 1: Support CSV file type:

Most of the time external system generates the input file to create structure and commonly used format is Excel / CSV. So to use CSV as input/export file there are few modifications required in the code to export product structure.

• Modify below string in the VBScript:

In the script, strFileName string specifies the input excel file type. Modify the extension of the file name to .csv

'For .xlsx
strFileName = CATIA.FileSelectionBox("Select the input Excel File", "*.xlsx", CatFileSelectionModeOpen)

'For .csv
strFileName = CATIA.FileSelectionBox("Select the input Excel File", "*.csv", CatFileSelectionModeOpen)

• In the EKL script, make following changes:

In the EKL script, strTempFileName variable of string type specifies the type of excel file to be created for Template Excel. Modify the below lines of code to support .csv format

//For .xlsx
strTempFileName = " ResourceStructure _" + strTemp3 + ".xlsx"

//For .csv
strTempFileName = " ResourceStructure _" + strTemp3 + ".csv"

In the EKL script, strExportFileName and strFlatMbomExpFileName variable of string type specifies the type of excel file to be created for Export Excel for RollUp and Flat MBOM respectively.. Modify the below lines of code to support .csv format

//For .xlsx
strExpFileName = "RollUp_MID_Structure_" + strRootMfgItemTitle + "_" + strTemp3 + ".xlsx"  //file name for Quantity MBOM
strFlatMbomExpFileName = "Full_MID_Structure_" + strRootMfgItemTitle + "_" + strTemp3 + ".xlsx"  //file name for Flat MBOM

//For .csv
strExpFileName = "RollUp_MID_Structure_" + strRootMfgItemTitle + "_" + strTemp3 + ".csv"  //file name for Quantity MBOM
strFlatMbomExpFileName = "Full_MID_Structure_" + strRootMfgItemTitle + "_" + strTemp3 + ".csv"  //file name for Flat MBOM

2. Requirement 2: Use Environmental Variable to get Export File Path

User can define an environment variable “EXPORT_FOLDER_PATH” for the Export Excel file path in “env.txt” file. This environment variable can be used to generate Export Excel file path.

For example:

EXPORT_FOLDER_PATH = D:/work_dsi/PPR/ExportExcel

The below section of code is responsible to manage this export path. If the EXPORT_FOLDER_PATH environment variable is not set, then it will export the Excel in “CATTemp” folder.

// Get Export folder path if not set then use CATTemp path
strExpFolderPath = GetEnvVariableValue("EXPORT_FOLDER_PATH")
if strExpFolderPath == ""
               strExpFolderPath = GetEnvVariableValue("CATTemp")
strExpFileName = "RollUp_MID_Structure_" + strRootMfgItemTitle + "_" + strTemp3 + ".xlsx" 
//file name for Quantity MBOM

strFlatMbomExpFileName = "Full_MID_Structure_" + strRootMfgItemTitle + "_" + strTemp3 + ".xlsx"  //file name for Flat MBOM

strExpFilePath =  strExpFolderPath + "\\\\" + strExpFileName
strFlatMbomExpFilePath = strExpFolderPath + "\\\\" + strFlatMbomExpFileName

The detailed path where excel is exported, will be visible when the EKL script is finished execution.

3. Requirement 3: Export Additional Attributes (OOTB or Specialized)

User just needs to add extra columns in the template excel with column name as Internal Attribute name. The EKL script will export that attribute in excel.

The code in attached EKL script between below two comments is responsible to export Additional attributes.         

/*Exporting Additional OOTB or Custom Attribute */
.
.
/*Code to Export Additional OOTB or Custom Attribute Ends*/

Note: If instance attributes value needs to be exported then add “Inst_” keyword as prefix in attribute name/column name. For example for instance V_description, it will be “Inst_V_description”

4. Requirement 4: Use Hardcoded Export Excel Structure

If user don’t want to use Template Excel to decide Export Excel structure or User want to export fixed set of attributes using hardcoded Excel structure, follow below steps.

At the start of the script, where template file is selected, create a variable of type list as given below

Let ListOfAttr(List)

Instead of copying column names from Template Excel to Export Excel, set column names manually as given below.

oExportSheet.SetCell(oRow, 1,"Level")
oExportSheet.SetCell(oRow, 2,"Type")
oExportSheet.SetCell(oRow, 3,"Title")
oExportSheet.SetCell(oRow, 4,"Inst_Name")

Add the column names as elements in the list created above in same sequence as the column sequence in excel.

ListOfAttr->AddItem("Level", 1)
ListOfAttr->AddItem("Type", 2)
ListOfAttr->AddItem("Title", 3)
ListOfAttr->AddItem("Inst_Name", 4)

In the section of code, where attributes values are written in Excel file, attribute name to be exported is read from template excel. Now instead of reading it from Excel, read attribute name from the List of Attributes created above by passing column number as index.

strCurrAttrName= ListOfAttr->GetItem(IdxCol) //Get Attribute name from list

How to Deploy

This video describes: How to deploy the "Export Product Structure to Excel?" EKL Script. However, the process is same for following EKL Scripts:
   1. Export Resource Structure to Excel
   2. Export Mfg. Item Structure to Excel
   3. Export System Structure to Excel

(Note: 
1. Instead of creating a separate Physical Product for containing each script, you can create the action files for the above three EKL scripts in the same Physical Product that we are going to create for "Export Product Structure to Excel" EKL Script.
2. The Input Excel File Selector VBScript is same for all the above EKL Scripts. So, you can create it only once and use it in all the above EKL Scripts.
3. The input arguments are different for different type of structure script. The information is provided in the respective EKL script as a comment at the top section.
4. The "Get Level" EKL action is different for different structure. 
)​​​​​​​

Data Set to Test the Scripts

​​​​​​​Data Set for Test

Note: The above data set is prepared with 2021x.

References link

<< Reference Link Here>>

Author

Authors: @KA ​​​​​​​@RR ​​​​​​​@YB ​​​​​​​

For questions/improvements please refer to: @KA ​​​​​​​