Introduction

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

Version – Validated on 2021x FD09 (FP2140), 2022x FD01 (FP2205), 2023x FD02 (FP2314), 2024x FD02 (FP2414), 2025x FD03 (FD2524)

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, 2024x & 2025x documentation.

Logical Flow

Below is the logical flow for Export Resource Structure to Excel

Example

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 show to make it easier to understand. In case there is data is 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”

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

Get list which includes root resource and its children’s in same sequence as they appear in tree.

Below lines of code is responsible to get list of all “ResourceOccurrence” including root resource  in a same sequence as they appear in the tree.

//Find all the objects of type "ResourceOccurrence" inside Root Resource Occ
listOfResOcc->AddItem(oRootResourceOcc, 1)
listOfChildResOcc  = oRootResourceOcc->Query("ResourceOccurrence", "") // This list will be the entire list of ResourceOccurrence objects available under Root Resource node
listOfResOcc = listOfResOcc + listOfChildResOcc

Get Type of Resource Object:

The internal object type for all resource objects is “VPMReference”. To get the type as Display Name, for example, Robot, Worker. User need to read the “V_dicipline” attribute on the reference of Resource object as given below:

set strObjType = oResRef.V_discipline //Get Resource Object Type

Get level of Current Product from tree:

To get level of resource item in tree, another EKL script is called by passing Resource occurrence and a variable to store Level. The script returns the level of the passed resource as output.

The below lines of code calls the external action file

oFindLevelAction.Run(oResOcc, i) 
//A action script which finds the level of occurrence in the tree

intLevel = i  //set Level as i,which is output of running the action file

The Action file called above is attached in this page in Example section.

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 variable of string type specifies the type of excel file to be created for Export Excel. Modify the below lines of code to support .csv format

//For .xlsx
strExportFileName   = strExportFolder + "_" + strName + "_ ResourceStructure _" + strTemp3 + ".xlsx"  //define the file path

//For .csv
strExportFileName   = strExportFolder + "_" + strName + "_ ResourceStructure _" + strTemp3 + ".csv"  //define the file path

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.

//Decide the Export Excel File Path
strExportFolder = GetEnvVariableValue("EXPORT_FOLDER_PATH") //Get folder path from environmental variablles
if strExportFolder == ""
        strExportFolder = GetEnvVariableValue("CATTemp")
strExportFileName = strExportFolder + "_" +strName + "_ ResourceStructure _" + strTemp3 + ".xlsx"//define the file path

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 before attribute 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.

strAttrName = 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

<>

Author

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

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