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 the ColumnHeaderRange determines the expected dimension of the identifier that will be written.

Examples:

2-dimensional with one index in rows and one index in columns:
```
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 the ColumnHeaderRange.

If not specified, the range is automatically determined using the locations of the RowHeaderRange and the ColumnHeaderRange.

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 the RowHeaderRange and ColumnHeaderRange.

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 columns:
```
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 the ColumnHeaderRange.

If not specified, the range is automatically determined using the locations of the RowHeaderRange and the ColumnHeaderRange.

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/or IncludeEmptyColumns 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 the RowHeaderRange and ColumnHeaderRange remains unchanged and only the cells in the DataRange 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 the ColumnHeaderRange.

If not specified, the range is automatically determined using the locations of the RowHeaderRange and the ColumnHeaderRange.

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, the RowHeaderRange should also be oriented horizontally and the number of columns in the RowHeaderRange should match the number of columns in the DataRange. In other words, the RowHeaderRange 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 the WriteTable 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/or IncludeEmptyColumns 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 the ReadTable::ColumnHeaderRange or the ReadTable::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.

Multi Dimensional Data

Docs

Product

Support