...
REPLINS$(Telitab$, ",", ".", 0)
in which Telitab$ is the result of the EXCEL#() function.
5. The scripts (both input and output) for EXCEL#() follows the TeLiTab rules for defining objects/telitabs etc. So, when you want to write something to a sheet named "input" from an object (or TeLiTab) named "DATA" containing parameter "A" and "B" as part of a TeLiTab also containing parameter "C". you can write:
TEXTITEM1=|
2
"C" "input.c(1)"
"DATA"
{
2
"A" "input.A(1)"
"B" "input.B(2)"
}
|
C and DATA are list values in the TeLiTab and therefore TEXTITEM1 starts with 2. DATA is an object or TeLiTab and you C and DATA are list values in the TeLiTab and therefore TEXTITEM1 starts with 2. DATA is an object or TeLiTab and you want to get A and B from this TeLiTab. This can be written as a list of 2 values.
Please make sure the list number is placed below the bracket "{".
Furthermore, special characters (like "{" or "[") in names of the sheet items you refer to are a risk. It might work sometimes but we did not test all use cases.
...
Our TelitabInput# consists of a single-case parameter "No_cases", and a large table of multi-case data:
Let the input script in the dataslot of the EXCEL relation be:
This means:
Preparation of the Excel sheet
The EXCEL sheet could look like this:
NOTE:
The described approach requires that the parameters (such as Lpp) have to be available in the indicated columns (1 for Lpp).
Moreover, if a parameter is available more than once in an indicated column, the first case will be taken into account as starting point to write the values.
Furthermore, you could place parameters in the same column. However, when the value represented the name of the parameter is overwritten during the process of writing the values of the other parameter in the column, Quaestor will not be able to find the overwritten value and an error will be the result.
If data from excel should be imported to the knowledgebase, you should define an output script. (Note that in the above example no output script was defined). The output script is defined similar to the input script, for example:
By using a comparable relation as example 1a, but now with an output script added a TeLiTab with Lpp, V_eco, Rt and PE is received as result:
TelitabOutput$ = EXCEL#(1, "Calc_sheet.xls", "Result_sheet.xls", TelitabInput$, TEXTITEM$(1), TEXTITEM$(2))
with the output script defined by
TelitabOutput$ will contain the table with all ships with length (Lpp, which was inserted to the sheet as input and is now read to be present in the TeLiTab), economical speed V_eco, resistance Rt and power PE. These values are read from the corresponding column indicated in the output script (1 for Lpp, 10 for V_eco etc.) and the rows below the cell with the value indicated in the output script (Lpp for Lpp, V_eco_knots for V_eco etc.).
Sometimes you want to write datasets in blocks of similar cases to an Excel sheet.
For example:
In this example TestNo indicates the blocks of accompanying data for Vs and Ps.
You can imagine that you want to place these sets of Vs and Ps in separate columns for each TestNo. With the above discussed examples, this is not possible. Therefore, an alternative syntax for the input script is created.
Let the input script in the dataslot of the relation be:
This means:
...
However, the expression between the brackets is a relation determining the column number. The following rules will be used:
...
1
"No_cases" 2000
4 "Lpp" "B" "T" "Cb"
"1" 100 15 6 0.55
"2" 110 16 7 0.60
"3" 120 17 8 0.65
"4" 130 18 7 0.60
"." ... .. . ....
"2000" 140 19 6 0.55
Let the input script in the dataslot of the EXCEL relation be:
TEXTITEM1=|
5
"No_cases" "main_dim.No_cases"
"Lpp" "main_dim.Lpp(1)"
"B" "main_dim.B(2)"
"T" "main_dim.T(3)"
"Cb" "main_dim.Cb(4)"
|
This means:
Preparation of the Excel sheet
The EXCEL sheet could look like this:
NOTE:
The described approach requires that the parameters (such as Lpp) have to be available in the indicated columns (1 for Lpp).
Moreover, if a parameter is available more than once in an indicated column, the first case will be taken into account as starting point to write the values.
Furthermore, you could place parameters in the same column. However, when the value represented the name of the parameter is overwritten during the process of writing the values of the other parameter in the column, Quaestor will not be able to find the overwritten value and an error will be the result.
If data from excel should be imported to the knowledgebase, you should define an output script. (Note that in the above example no output script was defined). The output script is defined similar to the input script, for example:
By using a comparable relation as example 1a, but now with an output script added a TeLiTab with Lpp, V_eco, Rt and PE is received as result:
TelitabOutput$ = EXCEL#(1, "Calc_sheet.xls", "Result_sheet.xls", TelitabInput$, TEXTITEM$(1), TEXTITEM$(2))
with the output script defined by
TEXTITEM2=
|3
"Lpp" "main_dim.Lpp(1)"
"V_eco" "main_dim.V_eco_knots(10)"
"Rt" "main_dim.Rt(17)"
"PE" "main_dim.PE(18)"|
TelitabOutput$ will contain the table with all ships with length (Lpp, which was inserted to the sheet as input and is now read to be present in the TeLiTab), economical speed V_eco, resistance Rt and power PE. These values are read from the corresponding column indicated in the output script (1 for Lpp, 10 for V_eco etc.) and the rows below the cell with the value indicated in the output script (Lpp for Lpp, V_eco_knots for V_eco etc.).
Sometimes you want to write datasets in blocks of similar cases to an Excel sheet.
For example:
1
"No_cases" 6
3 "TestNo" "Vs" "Ps"
"1" 10001 15 6500
"2" 10001 16 7000
"3" 10001 17 8000
"4" 10002 15 7000
"5" 10002 16 8500
"6" 10002 17 10000
"." ... .. . ....
In this example TestNo indicates the blocks of accompanying data for Vs and Ps.
You can imagine that you want to place these sets of Vs and Ps in separate columns for each TestNo. With the above discussed examples, this is not possible. Therefore, an alternative syntax for the input script is created.
Let the input script in the dataslot of the relation be:
TEXTITEM1=
|
4
"No_cases" "main.No_cases"
"TestNo" "main.TestNo(0+CASENO(TestNo)).1"
"Vs" "main.Vs(0+CASENO(TestNo))"
"Ps" "main.Ps(4+CASENO(TestNo))"
|
This means:
"main.TestNo(0+CASENO(TestNo)).1"
is comparable to example 1.
However, the expression between the brackets is a relation determining the column number. The following rules will be used:
The difference in syntax between
and
is that, by placing ".1" behind "main.TestNo(CASENO(TestNo))", you indicated that only one value should be written, while using the method to jump columns. (for the moment only .1 is supported and only in combination with the CASENO syntax)
While keeping the above rules in mind, this method will enable you to use a dynamic change of column numbers. However please note the following:
In some cases it is more convenient to keep the sheet inside the knowledge base (when distributing the knowledge base as one file for instance).
An Excel sheet can be included in the frame of the expression that uses this sheet. Right click on the relation (not the parameter) in the knowledge browser, and select: Include Binary in frame... and select the Excel sheet you want to include in the frame.
The syntax of the expression using the embedded workbook is almost the same as in the previous examples, except you don't have to address the source sheet anymore. The expression becomes, for instance:
TelitabOutput$ = EXCEL#(1, "", "", TelitabInput$, TEXTITEM$(1),TEXTITEM$(2))
Use as the syntax:
TelitabOutput$ = EXCEL#(1, "Calc_sheet.xls", "Result_sheet.xls", TelitabInput$, TEXTITEM$(1), "NullString")
This means that:
An example TelitabInput$ would be (see TELITAB for a description of the TeLiTab format):
1
"No_cases" 4
4 "Lpp" "B" "T" "Cb"
"1" 100 15 6 0.55
"2" 110 16 7 0.60
"3" 120 17 8 0.65
"4" 130 18 7 0.60
An example of the input script in the dataslot of the relation would be:
TEXTITEM1=
|
5
"No_cases" "main_dim.No_cases"
"Lpp" "main_dim.Lpp Ref*"
"B" "main_dim.B Ref*"
"T" "main_dim.T Ref*"
"Cb" "main_dim.Cb Ref*"
|
...
While keeping the above rules in mind, this method will enable you to use a dynamic change of column numbers. However please note the following:
In some cases it is more convenient to keep the sheet inside the knowledge base (when distributing the knowledge base as one file for instance).
An Excel sheet can be included in the frame of the expression that uses this sheet. Right click on the relation (not the parameter) in the knowledge browser, and select: Include Binary in frame... and select the Excel sheet you want to include in the frame.
The syntax of the expression using the embedded workbook is almost the same as in the previous examples, except you don't have to address the source sheet anymore. The expression becomes, for instance:
TelitabOutput$ = EXCEL#(1, "", "", TelitabInput$, TEXTITEM$(1),TEXTITEM$(2))
Use as the syntax:
TelitabOutput$ = EXCEL#(1, "Calc_sheet.xls", "Result_sheet.xls", TelitabInput$, TEXTITEM$(1), "NullString")
This means that:
An example TelitabInput$ would be (see TELITAB for a description of the TeLiTab format):
An example of the input script in the dataslot of the relation would be:
This means:
...
Knowledge engineer tutorials covers the use of MS Excel as a satellite (using example 1a and 1b).
Quick links: Functions overview | Attribute overview | Constants overview | Dimensions overview