Creating Predefined Excel Template Files(Windows Front-End)
Predefined template files are supplied with certain solutions.
Each predefined template file is associated with an Excel file. Management of this Excel file is assured by HOPEX Windows Front-End from system business documents.
For more details, see Using Business Documents.Creating Excel Template Files
To creating an Excel Template File:
1. From the HOPEX Windows Front-End desktop, select View > Navigation Windows > Utilities.
2. Right-click the Excel Templates folder and select New > Excel Template.
3. Open the properties dialog box of the Excel template you have just created and select the Characteristics tab.
4. Click the arrow at the right of the System Business Document field and select Create System Business Document.
The system business document creation dialog box opens.
5. Define the System Business Document name and the File Location of the Excel template you want to use.
6. The Document Template field shows Excel Import/Export Template.
The system business document must belong to document category Excel Import/Export Templates or to a sub-category. For more details, see Classification.7. Click OK to close the system Business Document creation window.
The system business document is created.
8. In the MetaClass section, click the Connect button to select the MetaClasses representing the main concepts managed by the template.
In the context of Excel export, MetaClasses supported by Excel worksheets serve in lists to identify templates to be used as priority. For more details, see Exporting an object list in Excel format.9. In the Excel Worksheet section, click New for each Excel worksheet you want to add to your file.
To fill characteristics of your template Excel sheets, see Creating Excel Template Worksheets.Creating Excel Template Worksheets
An Excel template worksheet should define information required for creation of an Excel import/export worksheet.
For more details, see Creating an Excel worksheet.Information defined in columns of the Excel worksheet is Excel template data referenced by the system business document.
To create an Excel template worksheet:
1. Open the properties dialog box of the Excel template for which you want to attach the sheet and select the Characteristics tab.
2. In the Excel Template worksheet, open the properties of the Excel Template worksheet of interest to you.
3. In the MetaClass field select the master MetaClass (for example "Application").
4. The fields MetaAssociationEnd and Slave Metaclass enable to fill in the column of an Excel worksheet with connected objects. For example, objects of the slave MetaClass “IT service” linked to the master MetaClass through the “Service defined” MetaAssociationEnd.
5. The Excel column macro enables to specify macros which will be run by the wizard to fill in each column of the Excel export worksheet.
For more details on used macros, see Using Macros in Excel Import/Export Files.6. Click OK to finish.
Using Macros in Excel Import/Export Files
A macro enables export to Excel and import to HOPEX of calculated attribute values, for example: the result of a sum of whole numbers.
For more details on configuration of notifications or e-mails, see Creating Excel Template Worksheets:To use the the VB macro creation wizard on an object of Business Document type:
1. Open the Characteristics of the Excel worksheet that interests you.
2. In the Excel column Macro section, click the New button.
The macro creation wizard opens.
3. Select the check box corresponding to the type of macro you want to use, for example:
• "(VB) Script macro"
• "macro based on component"
• "java macro":
• “macro c#”
• "existing macro": to use an existing macro, of which field Reusable is selected.
4. Click Next.
5. If the macro you have created can be reused for another Excel import/export, select the Reusable box.
6. Click Finish.
The constraint appears in the properties dialog box.
7. Modify the Name of the macro.
The macro created contains by default the code of functions enabling encoding of operation of:
• Export, with the “ColumnExport” function that has as parameters:
• The object being exported.
• The list of header columns in the form of megafields.
This parameter is a character string that should be broken down using character "#" as separator.• The index in the list of columns of the column being exported.
• Import, with the “ColumnImport” function that has parameters:
• The object being imported.
This object can be an object created by import or an existing object modified by import.• The value of the Excel cell being imported.
• The list of header columns in the form of megafields.
This parameter is a character string that should be broken down using character "#" as separator.• The list of values of cells of the row being imported.
This parameter is a character string that should be broken down using character "#" as separator.• The index in the list of columns of the column being imported.