Interfacing Excel with TestView

Add a HYPERSIM Excel Step in TestView

TestView provides a step called HYPERSIM Excel allowing users to import parameters from Excel worksheets, and to copy them to the corresponding power system components when executing a series of tests.

This step-item is commonly used in the Execution section of a test and extensively used in loops. It can also be inserted in the Initialization section for setting the initial states of the power system. It works much like the breaker and miscellaneous steps of TestView when used in multi-parameter mode.
Once the data has been imported, the list section and the component parameter sections are updated. After an import, you can add new components and parameters as in a multi-parameter miscellaneous block.
The user simply selects the name of the component, the parameter, and then the option New.
A new tab for multiple parameters is added. Users can then add the number of required values. Each line corresponds to a value.

Note: Users can add many parameters, but they must all have the same number of values (shown in parenthesis in the tab). A tab turns red when it does not contain a sufficient number of parameters.

See the section on Excel Parameter Definition for more help on this topic and learn how to define the table’s parameters.
HYPERSIM Excel Block Interface
See the table for a detailed description of each section.
FigImport Step Dialog
Field Name Description
Excel Workbook Information Section
Excel file path Path and name of the Excel file to use when importing parameter values
Named range
Name of the Excel region where parameters are located in the Excel file. See the section on the named region to define these region names.
Syntax example: MyTable
Sheet name and range
Excel range where parameters are located in the Excel file. The range must include the name of the sheet and all heading cells.
Syntax example: Sheet1!A1:G15
See section on Excel range to define a sheet name and range.
Import from Excel By clicking this button, TestView reads the Excel file and imports the parameters from the file
Auto import data on play When this option is checked, TestView automatically imports Excel data at the beginning of the test sequence
PARAMETERS SECTION
Category Category of the component to select to add new parameters
Component Component to add to the list
Parameter Parameter to add to the list
LIST SECTION
New Add a new subset to the list of subsets. The new subset uses the component and parameter defined in the parameter section
Subset item List of subset items used by TestView while iterating the loop
SUBSET SECTION
Index Iteration number
Component name Component that changes during the iteration
Parameter name Parameter for which the value will change during the iteration
Value New value for the iteration
Add Add a new iteration to the list
Remove Remove and iteration from the list
Excel Test Definition

The Excel Worksheet allows users to define parameters for power system components. These parameters are used by the TestView auto loop step.
This means that every iteration of an auto loop uses a different set of parameters. Many parameters from various components can be changed at the same time.
The user must specify component and parameter names and then specify the set of values to use in iterations.

Note: The user can enter parameter data using several formats explained in the following sections.

Data Import Options

Excel provides two different approaches to specify which part of the Excel file is imported by TestView: named range and sheet area.
Named Range

To specify data to be imported in TestView, named range is the suggested approach. It allows the user to create meaningful short names easier to use in TestView.
Each name refers to a part (an area) of the Excel document. The area contains data to be imported: parameter names are found in the first header row, and their values are found and on the subsequent lines.
To add a named range:
In Excel, select the cell, range of cells, or nonadjacent selections to be named.
Click the Name box at the left end of the formula bar.
Type the name to use for the selection and then Enter.
The following figure shows the Table called tableName. Its area is the range Sheet1:A2:D4

The Define Name toolbar can also be used to create a new area in the Excel document.
Sheet Range
Alternately, TestView can import data by using a sheet range. No extra step is required in Excel to use this approach, but the syntax is more complex to use in TestView since the user has to type the sheet range formula in TestView.
The syntax for specifying a range is:
SheetName!$UpperLeftCell:$LowerRightCell
For example, to read and import the table shown below, type Sheet13!$A$2:$D$4 in TestView step parameters.
Data Format Options
This section describes the various formats supported by TestView in the Excel file for defining which parameters to modify during tests. Each format offers different options: the first format allows only varying values of a single parameter for a single component during the test sequence and the other format allows users to vary the values of various parameters for a single component.
Fixed Format
This format defines the values for one parameter while iterating a TestView loop. The component and the parameter are fixed for all iterations, but values are modified.
In this format, only one column is required. The header of the column specifies the component name and parameter name. Component and parameter names must be separated by a period as in the following example: MyComponent.MyParameter.
Following the header, subsequent rows contain the iteration values.
The figure below shows an example of setting the operation time of the BR1 breaker. 7 iterations are performed. T1 parameter is set to 0.1 on the first iteration, 0.11 on the second iteration, and so on.
Variable Components Format
The second format simultaneously sets the values of various parameters for one component. For every iteration, the name of the component must be defined.
It implies that the user can set many parameters for a component during some iterations but then switch to another component while iterating.
Figure 8: (Columns C to F) shows the generic definition of this format.

In this format, many columns are required. The row headers specify the parameter name and the first column specifies the variable components.
Many columns can be used to specify various parameters. The first column must be named “$”.
In the first column (Column C), the user needs to define the name of the components for every loop iteration.
Note that components must be of the same type (ex: all components are breakers) and must have a value for all parameters defined in the following columns (Columns D to F).
Figure 9 shows an example of operating various breakers. The operation time and the phase (A, B, or C) to toggle are set at the same time. Again, in this example, 7 iterations are performed.
In the first iteration, the A and C phases of BR1 breaker are operated at 0.1 seconds, or toggled from their current state condition.
In the second iteration, the same breaker is operated, but now, only phase A is toggled. Note that iterations 3 and 4 are then performed on the BR2 breaker.
Variable components format example:
$ .T1 .T1A .T1B .T1C
BR1 0.1 1 0 1
BR1 0.11 1 0 0
BR2 0.12 1 1 1
BR2 0.11 0 0 0
BR3 0.121 0 0 1
BR4 0.111 0 0 0
BR4 0.12 1 0 1
Combining Multiple Formats
The user can combine and use both formats to control the changes of parameters for several components at the same time. The following figure presents one simple example where fixed format and variable components format are combined to set multiple parameters at the same time during loop iteration.
During the first iteration, the L inductance values are set on the RL_Shunt1 and RL_Shunt2 components to 50 and 120 Mvar respectively. At the same time, the operation time of the BR1 and BR11 breakers are set to 0.1 seconds, and phases A and C are selected for operation. The second iteration performs very similar operations but with different values. The third iteration performs the same type of operation but this time on BR2 and BR12 breakers. And so on.
Formulas
Any cell of the table can contain standard Excel formulas. When TestView reads the Excel worksheet, values are interpreted, and only resulting numbers and text are used. The following figure presents three cells where formulas are used to define the operating time of BR1 breaker.
In the second iteration, the random function is used to generate a new number every time the worksheet is read. A uniformly distributed number between 0 and 0.11 is automatically generated.
In the third iteration, standard math functions are used. The fourth iteration uses a cell reference to cell A1 to define the operating time of the breaker.
Formula Examples
Empty Cell
When the user leaves a cell empty, it indicates to the TestView sequencer to skip the change of the corresponding parameter value when iterating a loop. This can be useful when a change is not relevant for some iterations.
Import Variable
You can assign a value to a variable using Excel. In order to do this, the column header needs to use the syntax “%{myVariable}“. Variables can be used in TestView to change the execution sequence (ex: in If conditions) or in any step. See TestView documentation for more help on variables.
Reminder: variables can be accessed in TestView steps with the following syntax: %{myVariable}.
%{myVariable}
0.1
Toto
0.12
0.11
Import of a Variable

The column can be part of any table as in the following example.
Add Comments Column
You can add a comments column in the Excel table. To do this, the header of the column must start with the “#” character. The # signifies that the column is completely ignored during the import process.

The table shows the use of a comments column. Importing this table in TestView produces the same result.
Style and Descriptive Headers
The user can apply any style and layout to tables without affecting data to be imported in the TestView sequence. The user can also change cell content around the table.
This feature is useful if the user would like to add any description or units above the cells. The following figure shows an example of such modifications.
Note that the table can be located anywhere in the worksheet. The TestView Excel Parameters Import step provides a setting to specify a cell range to read.
Component and Parameter Name
Users can refer to the netlist to get the complete list of components and parameter names available in the current power system.

This page provides the exact component and parameter names to be used in the Excel table. For example, Figure 16: shows the parameters of the Ld1 RLC component.
Tableout Option
TestView provides a step called TableOut to create CVS report files. The following sections explain the options that allow users to generate Excel files instead of CVS file.
Table output generated in Excel can easily be formatted and templates can be used to create standard reports.
Export to Excel
In the TableOut step, an option called Export to Excel exports values to an Excel file. The file name and the table name where to export the data must be specified to export the data to Excel:

EXPORT TO A NEW FILE If the user exports to a non-existing file, then a new file is created and the data appears in a table.
EXPORT TO AN EXISTING FILE, TABLE NAME NOT FOUND If the user chooses an existing file but the table with the given name doesn’t exist, a new sheet is added to the workbook and a new table is created to hold the exported variable values.
EXPORT TO AN EXISTING FILE, TABLE NAME FOUND
If the user exports data to an existing file and the table already exists in the file, TestView tries, by default, to replace the old table data with the results of the new sequence.
TestView automatically manages the size of the table: if the table is not wide enough, columns are added to the table. It will not change if there’s enough room to add all columns.
If the table is not tall enough, new rows are added until all variable values can be stored in the table. Since rows below the table are simply shifted down, there’s no limitation on the number of rows that can be added.
When a table is too large, TestView also resizes it to accommodate the data.
FILTER OUT UNSAVED TEST This option outputs only tests that meet the criteria defined in the processing step of the TestView sequence. For example, assuming that only 20 out of 100 tests meet some given criteria, only those tests are exported to Excel.
APPEND TEST DATA
When the append data option is enabled, TestView searches inside the Excel file for the table and adds data to the existing columns.
This option is useful if many executions of the same sequence are done, and all data needs to be kept in the same Excel file. The new variable values are stored in rows appended to the end of the table.

Note: If the columns are not found, new columns are added to the right of the table to store these new variables. (See the preceding section about the rules for adding automatically new columns and resizing a table.)

In the figure above: a first sequence was executed and the values of the 3 tests were stored in rows 2 to 4. When the sequence was executed again, the values were appended at the end of the table, from row 4 to 6.

WARNING: Using this option too much can lead to an oversized Excel file that takes longer to open and navigate. This depends on the quantity of data acquired and the number of sequences.

Read-only Output File
To export the results to Excel using the TableOut step, the user must close the Excel output file before executing the sequence. If the file is still in use, Excel and the operating system will prevent TestView from writing to the file. If the user forgets to close the Excel file, TestView will write the results to a new file named “theoriginalfilename_i” (files will start at 0 and incremented until there’s no file inside the folder having the same name.
HYPERSIM Processing Block
The following sections build on a subset of information from the TestView reference manual about the processing step. Information on the Criteria section of the processing step is given.
HYPERSIM Processing Step Reminder

In this figure: A processing step was added into a loop where the operation mode is enabled. This option automatically sends a Trig command to the simulation to start the acquisition as explained in the TestView Tutorial.
In the processing tab, the maximum for the signal “EST7_Va” was assigned to a variable “a”, which means that for each iteration, the maximum value of the signal “EST7_Va” is appended to the variable “a”. At the end of all iterations, “a” is a vector with a size equal to the number of iterations.
HYPERSIM Processing Criteria
The Saving Mode tab saves all tests (Save All option), saves no tests (Save nothing option) or keep some of the tests where a value meets certain criteria (Use criteria option).
Criteria Descriptions
<none> ignore the criteria line, the criteria is never met
Min keep test with minimum value of expression
Max keep test with maximum value of expression
Minf keep test with finite minimum value of expression
Maxf keep test with finite maximum value of expression
First keep first test of loop sequence
Last keep last test of loop sequence
Save keep test if expression is true

In this figure, TestView keeps all tests that meet one or many of the following criteria:
The test with the finite minimum value of “a”
The test with the maximum value of “a”
The last test of the sequence.
All tests where “a” is greater than 616900

Field Name	Description
Excel Workbook Information Section
Excel file path	Path and name of the Excel file to use when importing parameter values
Named range	Name of the Excel region where parameters are located in the Excel file. See the section on the named region to define these region names. Syntax example: MyTable
Sheet name and range	Excel range where parameters are located in the Excel file. The range must include the name of the sheet and all heading cells. Syntax example: Sheet1!A1:G15 See section on Excel range to define a sheet name and range.
Import from Excel	By clicking this button, TestView reads the Excel file and imports the parameters from the file
Auto import data on play	When this option is checked, TestView automatically imports Excel data at the beginning of the test sequence
PARAMETERS SECTION
Category	Category of the component to select to add new parameters
Component	Component to add to the list
Parameter	Parameter to add to the list
LIST SECTION
New	Add a new subset to the list of subsets. The new subset uses the component and parameter defined in the parameter section
Subset item	List of subset items used by TestView while iterating the loop
SUBSET SECTION
Index	Iteration number
Component name	Component that changes during the iteration
Parameter name	Parameter for which the value will change during the iteration
Value	New value for the iteration
Add	Add a new iteration to the list
Remove	Remove and iteration from the list

$	.T1	.T1A	.T1B	.T1C
BR1	0.1	1	0	1
BR1	0.11	1	0	0
BR2	0.12	1	1	1
BR2	0.11	0	0	0
BR3	0.121	0	0	1
BR4	0.111	0	0	0
BR4	0.12	1	0	1

EXPORT TO A NEW FILE	If the user exports to a non-existing file, then a new file is created and the data appears in a table.
EXPORT TO AN EXISTING FILE, TABLE NAME NOT FOUND	If the user chooses an existing file but the table with the given name doesn’t exist, a new sheet is added to the workbook and a new table is created to hold the exported variable values.
EXPORT TO AN EXISTING FILE, TABLE NAME FOUND	If the user exports data to an existing file and the table already exists in the file, TestView tries, by default, to replace the old table data with the results of the new sequence. TestView automatically manages the size of the table: if the table is not wide enough, columns are added to the table. It will not change if there’s enough room to add all columns. If the table is not tall enough, new rows are added until all variable values can be stored in the table. Since rows below the table are simply shifted down, there’s no limitation on the number of rows that can be added. When a table is too large, TestView also resizes it to accommodate the data.
FILTER OUT UNSAVED TEST	This option outputs only tests that meet the criteria defined in the processing step of the TestView sequence. For example, assuming that only 20 out of 100 tests meet some given criteria, only those tests are exported to Excel.
APPEND TEST DATA	When the append data option is enabled, TestView searches inside the Excel file for the table and adds data to the existing columns. This option is useful if many executions of the same sequence are done, and all data needs to be kept in the same Excel file. The new variable values are stored in rows appended to the end of the table.

<none>	ignore the criteria line, the criteria is never met
Min	keep test with minimum value of expression
Max	keep test with maximum value of expression
Minf	keep test with finite minimum value of expression
Maxf	keep test with finite maximum value of expression
First	keep first test of loop sequence
Last	keep last test of loop sequence
Save	keep test if expression is true