Converting Input Data

 

An Absyntax input data project has an entry-point that will accept a single object of a specific type.  Every time a project invocation rule is invoked that defines a requirement for input data, the Absyntax Excel Add-in is faced with the task of fetching and packaging this data into a form that will be accepted by the target project.

 

In Microsoft Excel, the value of any non-empty cell in a project invocation rule's input data range that is not a character string or a Boolean will be passed by the add-in to the target project as a double-precision floating-point number, irrespective of any cell formatting.  If your project needs to work with other types of numeric value (e.g. integers) or date/time values then it must have an entry-point that supports either a double-precision floating-point number (System.Double) or a collection of such numbers and it must convert these numbers as necessary.

 

Furthermore, while it is sometimes possible to convert single values from one type to another, the same cannot be said for converting a collection of such values.  If you do not wish to be concerned with the nuances of packaging multiple cell values from Microsoft Excel into an object that can be converted to the data type of an Absyntax project's entry-point, you can define your project as having an entry-point data type equal to any of the following:

 

 

Such projects guarantee that they will be able to receive one or more values of different types from the Absyntax Excel Add-in.  The disadvantage of this approach is that your project will need to perform conversions so that it can then process each item correctly.  However, this is the only choice when you need to input different types of data to a project.

 

If all cell values in the input data range are of the same type (i.e. either Boolean, text or numeric), you can define your project as having a "strongly typed" enumerable entry-point such as:

 

 

Empty Cells

 

An empty Microsoft Excel cell is a cell for which no value has been specified.  The way in which empty cells in a project invocation rule's input data range are handled by the Absyntax Excel Add-in depends on whether there are multiple cells in the range and the types of the non-empty cell values.

 

 

Single-cell range?	Non-empty cells contain text?	Non-empty cells contain other data?	Action
Yes	-	-	The range contains one empty cell.  An attempt will be made to pass a single null value to the target project.  This currently results in an error (missing startup data).
No	No	No	All cells are empty.  An attempt will be made to pass an object array of null references to the target project.
No	Yes	No	Cells are either empty or contain only text.  An attempt will be made to pass a string array of all cell values, null or otherwise, to the target project.
No	No	Yes	Cells are either empty or contain only non-text values.  If all non-text values are of the same type (i.e. they are all either numeric or Boolean) then an attempt will be made to pass an array of that type, excluding empty values, to the target project.  If the non-text values include both Boolean and numeric types then an attempt will be made to pass an object array of all cell values, null or otherwise, to the target project.
No	Yes	Yes	An attempt will be made to pass an object array of all cell values, null or otherwise, to the target project.

 

 

Conversion Strategy

 

The Absyntax Excel Add-in does not know what a project's entry-point data type is (or even if a project requires input data at all).  This means that, initially, it must guess before packaging worksheet data and invoking a project with it.  If Absyntax is unable to convert the presented data, the add-in will try packaging the data in up to two alternative ways, re-invoking the project each time until the project accepts the data.  This sequence of attempts is referred to as the conversion strategy.  The add-in remembers the form of the data for the successful conversion and uses it the next time the same rule is invoked.  If Absyntax rejects all attempts to convert the data, this is considered an error.

 

The following table defines the various strategies that the add-in can employ when attempting to package one or more cell values into an object that will be accepted by the target project's entry-point.

 

 

Last Form of Data	Single-cell range?	Conversion Strategy
None	Yes	Single value Strongly-typed array Object array
None	No	Strongly-typed array Object array
Single value	Yes	Single value Strongly-typed array Object array
Single value	No	Strongly-typed array Object array
Strongly-typed array	Yes	Strongly-typed array Single value Object array
Strongly-typed array	No	Strongly-typed array Object array
Object array	Yes	Object array Single value Strongly-typed array
Object array	No	Object array Strongly-typed array

 

 

Note that if a project's entry-point data type is changed and the associated project invocation rule is configured to reload the project, there is no guarantee that the last successful form of data will still work and the add-in may have to cycle through the relevant strategy when it next invokes the rule.  It is also worth noting that the same rule may be changed to target a different project, for which there is also no guarantee.

 

Single-string Input Data Projects

 

Absyntax projects with entry-point data types equal to System.String can sometimes appear to behave strangely when they are supplied with data from Microsoft Excel.  If you create a project invocation rule for such a project and said rule defines an input data range consisting of multiple cells, upon invocation the target project will receive a string value equal to one of the following:

 

 

With reference to the conversion strategy described above, this is because the Absyntax Excel Add-in attempts to pass to Absyntax either a strongly-typed array or an object array (depending on the last form of data and the data types of the various cell values).  Because any value is convertible to a string, Absyntax accepts the incoming array and presents to the project the name of the array's data type (because that is how arrays are converted to strings).

 

This kind of behaviour is rarely required, so ensure that your project invocation rule's input data range extends only to a single cell when targeting single-string input data projects.

 

Receiving Output Data

 

Microsoft Excel will reject attempts to write data items to worksheet cells if any item is of a type that Microsoft Excel does not recognise.  However, Microsoft Excel is proficient at receiving string values and treating them flexibly.  For example, if the string representation of the Boolean value "True" is written to a cell, it will be stored as a Boolean value.  Likewise if the string representation of a numeric value is written to a cell, it will be stored as a numeric value.  For this reason, when requiring an Absyntax project to output data, it is advisable to use a project that defines its exit-point data type as either of the following:

 

 

The latter form is recommended as it is more versatile from the perspective of Absyntax project development and maintenance.

 

Outputting project data as strings has an added bonus in that the string representations of date/time values are automatically formatted when written to worksheet cells, meaning that a date/time appears as it is meant to rather than as a double-precision floating-point number that needs subsequent, manual formatting.