Back to OSCAM JSF v2 New Features
OSCAM JSF v2 Import/Export Tool - Enabling Macros
The Post Analysis Tool uses macros for the communication with OSCAM database, for configuration of the worksheets and for data validation. Macros must be enabled for the tool to function. On most PCs a warning message will be displayed that gives the option to enable macros.
Macro Warning Message
If the option to enable macros is not shown then it may be necessary to change the security settings. In Excel 2007 and 2010 this is down by selecting the "File" toolbar ribbon (this is the round Microsoft Office button in Excel 2007), then selecting "Options" near the bottom of the left hand pane. Select "Trust Center" on the dialog that appears and then click on the "Trust Center Settings". Select the radio button for "Show the Message Bar in all applications when active content, such as ActiveX controls and macros, has been blocked".
Excel 2007/2010 Trust Center Settings
For Microsoft Excel 2003, select the "Tools" menu and "Options" menu item. Click the "Security" tab and select "Macro Security" button. Then select the "Macro Security Level tab. The Medium settings will enable a warning to be displayed if a workbook contains macros and then give the option to enable them.
OSCAM JSF Import/Export Tool - Overview of Functionality
The OSCAM JSF v2 Import Export Tool allows one or more input data records to be extracted from an OSCAM JSF database, edited in Microsoft Excel and exported into the same or alternative OSCAM JSF database. Additional worksheets can be created for intermediary calculations and inputs values can be entered as numbers, formulae or cell references.
The OSCAM JSF v2 Import/Export Tool has versions for Microsoft Excel 2003 and for Microsoft Excel 2007 or 2010. The functionality of both versions is the same except that the Excel 2007/2010 version has support for the ribbon toolbar, while the Excel 2003 version uses custom toolbar buttons.
Import/Export Tool Excel 2007/2010
Ribbon Toolbar
Import/Export Tool Excel 2003 Custom
Toolbar
Data Record Worksheets
Each OSCAM JSF data record is represented by a worksheet in the Import/Export Tool spreadsheet. These sheets have a specific layout and can be created by:
-
Selecting the "New Data Sheet" toolbar item - this gives the option of starting with a default data sheet or to copy another data sheet in the workbook
-
Copying an existing data sheet by right-clicking on the worksheet tab and selecting the "Move or Copy..." option from the right-click menu
-
Importing a data record from an OSCAM JSF database
Each data record represented in the workbook must have a unique name. The data record names are displayed on the worksheet tabs, in a data field on the worksheet and on a Control worksheet that shows all the data record names and their validation status.
Import/Export Tool Control Sheet
A Data Record Worksheet is selected by choosing its worksheet tab or by double-clicking on the record name on the Control sheet.
A Data Record Worksheet contains every input that exists within OSCAM JSF. The sheet uses outlining that breaks the inputs into sectors and sub-sectors. This makes it easier to navigate round the sheet and find the inputs that you are looking for.
Import Export Tool Data Record
Worksheet - Outline Collapsed
Individual sectors and sub-sectors can be expanded or collapsed using the + and - buttons to the left. Alternatively the whole sheet can be expanded to a specified level using the Level 1 to 4 buttons at the top-left.
Import/Export Tool Data Record
Worksheet - Outline Expanded
Editing Inputs
All input names match those used in OSCAM. The long descriptions for inputs are represented in comment boxes and can be viewed by hovering the mouse pointer over the red triangles. The yellow, orange or gray cells can be edited. The color scheme is as follows:
-
Yellow - Currently contains an input value, but can be edited.
-
Orange - Cell is currently blank and a default value will be exported to an OSCAM database. A value can be entered.
-
Gray - The value in the cell will not be used in an OSCAM JSF simulation run due to switch settings, but the value can still be edited.
-
White - The value cannot be edited because it is a locked value or calculated from another input.
Some control inputs (which represent radio buttons or switches in OSCAM) have a limited choice of values. These can be selected from a drop-down list in the cell. Many switches will which affect inputs that are used in an OSCAM JSF simulation run. Inputs that will not used in a simulation run will have a gray background but will still be editable.
Import/Export Tool Switch Value
Drop-Down Selection
Table inputs are arranged in columns and will have a reference row above which in most cases shows the year. If a group of table inputs use the same reference values then only one reference row will be shown. Note that in some tables one of the cells will not be editable. This is because it contains a value that would not normally be seen in the OSCAM table but is required in the OSCAM database.
Import/Export Tool Table Inputs
Validating Input Values
Cell validation is used to check cell values as they are entered. If an invalid data type is entered (e.g. text for a numerical input) or the value is outside an acceptable range then a warning message will be shown. It will not be possible to exit the cell unless a valid value is entered or Cancel is selected.
Cell validation can be by-passed if data is pasted into a cell or a formula is used that is based on values in other cells. A second level of validation is used to check for valid values. An input with invalid cell values will be colored in red. The red background will be cascaded upwards so that any sector and sub-sector heading that contains inputs with invalid data will also be displayed with a red background so that problem inputs can be easily found even if the tree structure is collapsed.
Validation checking is performed when an input value is entered by editing or pasting. It will not immediately catch a problem if the input value is calculated from another cell value, but checking will be performed before an export to OSCAM.
Import/Export Tool Highlighting
Invalid Data
Copying Data Between Data Record Worksheets
Its is possible to copy data from one worksheet to another using standard Excel copy and paste. However, if the paste area contains non-editable cells then the paste will fail. An alternative method of copying input data is by right-clicking on sector and sub-sector headings.
Import/Export Tool Right-Clicking on
Sector and Sub-Sector Headings
The "OSCAM: Copy sector to another record" option will allow another record sheet to be selected for copying the data to. The "OSCAM: Copy sector from another record" option will allow pasting of data on this sheet from a selected record sheet. If the selected heading level has sub-sectors then it will be possible to select which sub-sector data to copy. Select the sheet to copy from or to and then click the "Copy Data" button.
Import/Export Tool Copy Sector Dialog
Importing OSCAM JSF Data Records
Data records can be imported from an OSCAM JSF v2 database. These databases will have a .USE or .HIS file extension. Individual or multiple records can be imported from the database. A record data sheet will be created for each record that is imported.
The "Import from OSCAM" toolbar item causes a dialog to be displayed that allows an OSCAM JSF database to be selected and then one or more records to be selected for import.
Import/Export Tool Import Dialog
The "Select a Database File" option displays a Windows Open dialog that allows an OSCAM .USE or .HIS file to be selected. A check will be made to ensure that the selected file is a valid OSCAM JSF v2 database. If it is not then a warning message will be displayed.
If an OSCAM JSF v2 database has previously been accessed then the most recent file will be available as a radio button option. This allows the database to be selected without having to use the Windows Open dialog.
Once a valid OSCAM JSF v2 database has been selected the available record names will be displayed. The records to be imported can be selected from the list. Click the "Import" button to import the data records.
The tool will check if a worksheet with the same name as a selected record already exists in the workbook. If so then a dialog will provide options to overwrite existing data in the worksheet, cancel importing the record, or to change the name of the record worksheet for the imported record.
Exporting OSCAM JSF Data Records
Data records can be exported to an existing OSCAM JSF v2 database. These databases will have a .USE file extension. Individual or multiple records can be exported to the database. Only valid record data sheets can be exported to a database. Invalid data records will be displayed on the Control sheet and will have invalid inputs highlighted in red on the record data sheet.
The "Export to OSCAM" toolbar item causes a dialog to be displayed that allows an OSCAM JSF database to be selected and then one or more record data sheets to be selected for export.
Import/Export Tool Export Dialog
The "Select a Database File" option displays a Windows Open dialog that allows an OSCAM .USE file to be selected. A check will be made to ensure that the selected file is a valid OSCAM JSF v2 database. If it is not then a warning message will be displayed.
If an OSCAM JSF v2 database has already been accessed during the session then the last file will be available as a radio button option. This allows the database to be selected without having to use the Windows Open dialog.
Once a valid OSCAM JSF v2 database has been selected the valid record data sheet names will be displayed. The records to be exported can be selected from the list. Click the "Export" button to export the data records.
The tool will check if a record with the same name as a record data sheet already exists in the database. If so then a dialog will provide options to overwrite existing data in the database, cancel exporting the record, or to change the name of the record when it is exported.