Multi Dimensional Data
- External Procedure axll::ReadTable
This function reads a table from the active excel sheet into an identifier reference.
The number of columns in the
RowHeaderRange
plus the number of rows in theColumnHeaderRange
determines the expected dimension of the identifier that will be written.Examples:
2-dimensional with one index in rows and one index in colums:
ReadTable( P2(i,j), "A2:A12", "B1:H2", "B2:H12" )
1-dimensional with the single index as rows:
ReadTable( P1(i), "A1:A10", "", "B1:B10" )
1-dimensional with the single index as columns:
ReadTable( P1(i), "", "A1:H1", "A2:H2" )
5-dimensional with first 3 indices as row tuples and the last 2 indices as column tuples:
ReadTable( P5(i,j,k,l,m), "A3:C10", "D1:M2", "D3:M10" )
- Handle ReadTable::IdentifierReference
- Attributes
Property – InOut
The (non scalar) identifier to which the data from the sheet will be written.
You can fix a domain index of the identifier to a specific element, such that only a specific slice of the identifier will be written.
- String Parameter ReadTable::RowHeaderRange
- Attributes
Property – Input
The excel range where the starting indices reside.
It may be left empty (“”), which means that all indices are in the
ColumnHeaderRange
.Examples:
“B1:B10” (covering only one domain index), or
“B1:C10” (representing tuples of size 2, and thus covering two domain indices).
- String Parameter ReadTable::ColumnHeaderRange
- Attributes
Property – Input
The excel range where the ending indices reside.
It may be left empty (“”), which means that all indices are in the
RowHeaderRange
.Examples:
“A1:H1” (covering only one domain index), or
“A1:H2” (representing tuples of size 2, and thus covering two domain indices).
- String Parameter ReadTable::DataRange
- Attributes
Property – Optional
(optional) Representing the range where the data of the table is. This range should match with the number of rows in the
RowHeaderRange
and the number of columns in theColumnHeaderRange
.If not specified, the range is automatically determined using the locations of the
RowHeaderRange
and theColumnHeaderRange
.
- Parameter ReadTable::ModeForUnknownElements
- Attributes
Property – Optional
(optional) Default = 0. This argument specified what to do with elements in the rows or columns that do not exist in the corresponding domain set.
Valid values are:
0 : unknown elements are treated as an error, and reading stops.
1 : unknown elements are added to the corresponding set, and an error is given if this fails.
2 : rows and columns with unknown elements are just silently skipped.
3 : rows and columns with unknown elements are skipped, but do raise a warning.
- Parameter ReadTable::MergeWithExistingData
- Attributes
Property – Optional
(optional) Default is 0.
If set to 0, the identifier(slice) to write to is first emptied before reading any values. If set to 1, then only the non-blank values in the table will be written to the identifier(slice), and any other existing data in the identifier will remain unmodified.
- External Procedure axll::WriteTable
This function writes an identifier in table format to the active excel sheet.
Other than the function
FillTable
this function overwrites all cells in the given ranges, including theRowHeaderRange
andColumnHeaderRange
.Note
If you do not need full control over where each part of the table is written, you can also use the function
WriteTableQuick
.Examples:
2-dimensional with one index in rows and one index in colums:
WriteTable( P2(i,j), "A2:A12", "B1:H2", "B2:H12" )
1-dimensional with the single index as rows:
WriteTable( P1(i), "A1:A10", "", "B1:B10" )
1-dimensional with the single index as columns:
WriteTable( P1(i), "", "A1:H1", "A2:H2" )
5-dimensional with first 3 indices as row tuples and the last 2 indices as column tuples:
WriteTable( P5(i,j,k,l,m), "A3:C10", "D1:M2", "D3:M10" )
- Handle WriteTable::IdentifierReference
- Attributes
Property – Input
The (non scalar) identifier of which the data will be written to the table in the active sheet.
You can fix a domain index of the identifier to a specific element, such that only a specific slice of the identifier will be written. A specification like A(i,’fixed-j’,k) can in this way be written in a 2-dimensional table.
- String Parameter WriteTable::RowHeaderRange
- Attributes
Property – Input
The excel range where the starting indices should be written.
It may be left empty (“”), which means that all indices will be in the
ColumnHeaderRange
.Examples:
“B1:B10” (covering only one domain index), or
“B1:C10” (representing tuples of size 2, and thus covering two domain indices).
- String Parameter WriteTable::ColumnHeaderRange
- Attributes
Property – Input
The excel range where the ending indices are written.
It may be left empty (“”), which means that all indices will be in the
RowHeaderRange
.Examples:
“A1:H1” (covering only one domain index), or
“A1:H2” (representing tuples of size 2, and thus covering two domain indices).
- String Parameter WriteTable::DataRange
- Attributes
Property – Optional
(optional) Representing the range where the data of the table is written. This range should match with the number of rows in the
RowHeaderRange
and the number of columns in theColumnHeaderRange
.If not specified, the range is automatically determined using the locations of the
RowHeaderRange
and theColumnHeaderRange
.
- Parameter WriteTable::WriteZeros
- Attributes
Property – Optional
(optional) Default is 0. If set to 0 a value of 0.0 will appear as an empty cell, otherwise it will be written as an explicit 0.
- Parameter WriteTable::AllowRangeOverflow
- Attributes
Property – Optional
(optional) Default is 0. If set to 1 and there are more rows and/or columns in the data than can be contained in the specified row header and/or column header ranges, then these ranges are automatically extended.
- Parameter WriteTable::IncludeEmptyRowsColumns
- Attributes
Property – Optional
(optional) Deprecated. Use the arguments
IncludeEmptyRows
and/orIncludeEmptyColumns
instead.
- Parameter WriteTable::IncludeEmptyRows
- Attributes
Property – Optional
(optional) Default is 0, only applicable if the row range is over a single index
If set to 1, a row in which each values equals 0 will be included. If set to 0, such a row will not be written at all.
- Parameter WriteTable::IncludeEmptyColumns
- Attributes
Property – Optional
(optional) Default is 0, only applicable if the column range is over a single index
If set to 1, a column in which each values equals 0 will be included. If set to 0, such a column will not be written at all.
- External Procedure axll::FillTable(IdentifierReference, RowHeaderRange, ColumnHeaderRange, DataRange, writeZeros, clearExistingContent)
This function writes an identifier to a table in an excel sheet where the row and columns are already present. So it reads the existing row and column ranges from the sheet and then writes the proper values to the cells of the
DataRange
. This means that the content of theRowHeaderRange
andColumnHeaderRange
remains unchanged and only the cells in theDataRange
will be written.Note
If you need to fill a table where there is only a row header or only a column header, use the function
FillList
instead.- Handle FillTable::IdentifierReference
- Attributes
Property – Input
The (non scalar) identifier of which the data will be written to the table in the active sheet.
You can fix a domain index of the identifier to a specific element, such that only a specific slice of the identifier will be written. A specification like A(i,’fixed-j’,k) can in this way be written in a 2-dimensional table.
- String Parameter FillTable::RowHeaderRange
- Attributes
Property – Input
The excel range where the starting indices reside.
Examples:
“B1:B10” (covering only one domain index), or
“B1:C10” (representing tuples of size 2, and thus covering two domain indices).
- String Parameter FillTable::ColumnHeaderRange
- Attributes
Property – Input
The excel range where the ending indices reside.
Examples:
“A1:H1” (covering only one domain index), or
“A1:H2” (representing tuples of size 2, and thus covering two domain indices).
- String Parameter FillTable::DataRange
- Attributes
Property – Optional
(optional) Representing the range where the data of the table is written. This range should match with the number of rows in the
RowHeaderRange
and the number of columns in theColumnHeaderRange
.If not specified, the range is automatically determined using the locations of the
RowHeaderRange
and theColumnHeaderRange
.
- Parameter FillTable::WriteZeros
- Attributes
Property – Optional
(optional) Default is 0. If set to 0 a value of 0.0 will appear as an empty cell, otherwise it will be written as an explicit 0.
- Parameter FillTable::clearExistingContent
- Attributes
Default – 1
Property – Optional
(optional) Default is 1. If set to 0 any existing cell content will not be overwritten by an empty value if the corresponding data in the identifier does not exist (or is 0.0)
- External Procedure axll::FillList(IdentifierReference, RowHeaderRange, DataRange, writeZeros, clearExistingContent)
This function writes an identifier to a list format in an excel sheet where the row headers are already present. So it reads the existing row range from the sheet and then writes the proper values to the cells of the
DataRange
.The
DataRange
should have either a width of 1 (vertical oriented), or it should have a height of 1 (horizontally oriented).If the
DataRange
is a horizontally oriented, theRowHeaderRange
should also be oriented horizontally and the number of columns in theRowHeaderRange
should match the number of columns in theDataRange
. In other words, theRowHeaderRange
is than treated as a column header.Examples:
1-dimensional, vertically oriented:
FillList( P1(i), "A1:A10", "B1:B10" )
1-dimensional, horizontally oriented:
FillList( P1(i), "A1:J1", "A2:J2" )
2-dimensional, vertically oriented:
FillList( P2(i,j), "A1:B20", "C1:C20" )
2-dimensional, horizontally oriented:
FillList( P2(i,j), "A1:Z2", "A3:Z3" )
- Handle FillList::IdentifierReference
- Attributes
Property – Input
The (non scalar) identifier of which the data will be written as a list in the active sheet.
- String Parameter FillList::RowHeaderRange
- Attributes
Property – Input
The excel range where the indices reside (either horizontally or vertically oriented)
- String Parameter FillList::DataRange
- Attributes
Property – Input
The excel range where the data should be written.
- Parameter FillList::WriteZeros
- Attributes
Property – Optional
(optional) Default is 0. If set to 0 a value of 0.0 will appear as an empty cell, otherwise it will be written as an explicit 0.
- Parameter FillList::clearExistingContent
- Attributes
Default – 1
Property – Optional
(optional) Default is 1. If set to 0 any existing cell content will not be overwritten by an empty value if the corresponding data in the identifier does not exist (or is 0.0)
- External Procedure axll::WriteTableQuick(IdentifierReference, TopLeftCell, RowDimension, writeZeros, IncludeEmptyRows, IncludeEmptyColumns, IncludeEmptyRowsColumns)
This function writes an identifier in table (or list) format to the active excel sheet. It only needs the top-left cell where the table to start and the number of indices that should be used as row indices.
The resulting table in the sheet will have a ‘natural’ layout without any empty rows or columns to separate the headers from the actual data.
This is a utility function that is easier to use than
WriteTable
. If you need more control over where row and column headers should appear, you should use theWriteTable
function instead.Examples:
WriteTableQuick(P(i,j,k), "A1", 2) ! produces the same result as WriteTable(P(i,j,k), "A2:B10", "C1:D1", AllowRangeOverflow:1) !or WriteTableQuick(P(i,j,k), "A1", 1) ! produces the same result as WriteTable(P(i,j,k), "A3:A10", "B1:H2", AllowRangeOverflow:1)
Example:
WriteTable( P(i,j,k,'l1'), "A1", 2 )
writes the tuples (i,j) to the range “A2:B[n]” (where n depends on the amount of data written)
writes the tuples (k) to the range “C1:[N]1” (where N depends on the amount of data written)
writes the value to the range with the left top corner in C2
- Handle WriteTableQuick::IdentifierReference
- Attributes
Property – Input
The (non scalar) identifier of which the data will be written to the table in the active sheet.
You can fix a domain index of the identifier to a specific element, such that only a specific slice of the identifier will be written. A specification like A(i,’fixed-j’,k) can in this way be written in a 2-dimensional table.
- String Parameter WriteTableQuick::TopLeftCell
- Attributes
Property – Input
The top-left excel cell where the table should start.
- Parameter WriteTableQuick::RowDimension
- Attributes
Property – Input
The number of indices in the domain of the identifier that should be written as rows of the table. The remaining indices will appear as columns. The value should be in the range [0 .. dimension of identifier].
- Parameter WriteTableQuick::WriteZeros
- Attributes
Property – Optional
(optional) Default is 0. If set to 0 a value of 0.0 will appear as an empty cell, otherwise it will be written as an explicit 0.
- Parameter WriteTableQuick::IncludeEmptyRowsColumns
- Attributes
Property – Optional
(optional) Deprecated. Use the arguments
IncludeEmptyRows
and/orIncludeEmptyColumns
instead.
- Parameter WriteTableQuick::IncludeEmptyRows
- Attributes
Property – Optional
(optional) Default is 0, only applicable if
RowDimension
is 1.If set to 1, a row in which each values equals 0 will be included.
If set to 0, such a row will not be written at all.
- Parameter WriteTableQuick::IncludeEmptyColumns
- Attributes
Property – Optional
(optional) Default is 0, only applicable if (dimension-of-identifier -
RowDimension
) equals 1.If set to 1, a column in which each values equals 0 will be included.
If set to 0, such a column will not be written at all.
- External Procedure axll::WriteCompositeTable(IdentifierReference, TopLeftCell, WriteZeros, WriteIndexNames)
This function writes multiple identifiers to a composite table format in the active excel sheet
Example:
Assume identifiers P(i,j) and Q(i,j), and set Contents = { P, Q }, then
WriteCompositeTable( Contents, "A1", 1 )
writes all tuples (i,j) for which either P or Q has a non default value to the range “A2:B<n>”
writes the string “P” in the cell “C1” (the title of that column)
writes the corresponding P values to the range “C2:C<n>”
writes the string “Q” in the cell “D1” (the title of that column)
writes the corresponding Q values to the range “D2:D<n>” (where <n> depends on the amount of data)
Values equal to 0.0 are written as explicit 0 values.
- External Procedure axll::ReadList(IdentifierReference, RowHeaderRange, DataRange, ModeForUnknownElements, MergeWithExistingData)
This function reads a list of data from the active excel sheet into an identifier reference.
The function is similar to
ReadTable
where either theReadTable::ColumnHeaderRange
or theReadTable::RowHeaderRange
is left empty.- Handle ReadList::IdentifierReference
- Attributes
Property – InOut
The (non scalar) identifier to which the data from the sheet will be written.
You can fix a domain index of the identifier to a specific element, such that only a specific slice of the identifier will be written.
- String Parameter ReadList::RowHeaderRange
- Attributes
Property – Input
The excel range where the indices reside (either horizontally or vertically oriented)
- String Parameter ReadList::DataRange
- Attributes
Property – Input
Representing the range from which to read the data. This range should be either one row high, or one column wide.
- Parameter ReadList::ModeForUnknownElements
- Attributes
Property – Optional
(optional) Default = 0. This argument specified what to do with elements in the rows or columns that do not exist in the corresponding domain set.
Valid values are:
0 : unknown elements are treated as an error, and reading stops.
1 : unknown elements are added to the corresponding set, and an error is given if this fails.
2 : rows and columns with unknown elements are just silently skipped.
3 : rows and columns with unknown elements are skipped, but do raise a warning.
- Parameter ReadList::MergeWithExistingData
- Attributes
Property – Optional
(optional) Default is 0.
If set to 0, the identifier(slice) to write to is first emptied before reading any values.
If set to 1, then only the non-blank values in the table will be written to the identifier(slice), and any other existing data in the identifier will remain unmodified.
- External Procedure axll::ReadRawValues(IdentifierReference, DataRange, MergeWithExistingData)
This function reads a block of values from the active excel sheet into an identifier reference without an explicit matching on element names. Rows (and columns) in the range are mapped to element in the domain sets based on the ordinal position.
Please note that the result is unpredictable if the domain sets of the identifier do not have an explicit or implicit ordering.
Example:
If i references an (ordered) set with elements { i1 .. i10 }, and j references an (ordered) set with elements { j1 .. j10 }, then
ReadRawValues( P(i,j), "E2:G5" )
assigns E3 to P(‘i2’,’j1’) and F5 to P(‘i4’,’j2’) here E3 stands for the content of cell E3 in the excel sheet (etc.)
- Handle ReadRawValues::IdentifierReference
- Attributes
Property – InOut
A one or two dimensional identifier to write to.
You can fix a domain index of the identifier to a specific element, such that only a specific slice of the identifier will be written.
- String Parameter ReadRawValues::DataRange
- Attributes
Property – Optional
Representing the range from which to read the data. If the identifier is one-dimensional, this range should be either one row high, or one column wide.
- Parameter ReadRawValues::MergeWithExistingData
- Attributes
Property – Optional
(optional) Default is 0.
If set to 0, the identifier(slice) to write to is first emptied before reading any values. If set to 1, then only the non-blank values in the table will be written to the identifier(slice), and any other existing data in the identifier will remain unmodified.