Import and Export actions on Spreadsheet

June 22nd, 2012
This page walks through how to configure the actors to import data to a spreadsheet and export data out from a spreadsheet.

SpreadsheetImporter Actor

This is the interface of a SpreadsheetImporter Actor:

Spreadsheet Template

There should be a spreadsheet that the actor will interact with. In this example, a spreadsheet is prepared like this:

It has two columns: name and email. Data in such format, an array of records, for example, {{email = “”, name = “Tom”},
{email = “”, name = “Eric”}} can be imported through “data” input port through SpreadsheetImporter actor to the spreadsheet.

After the data is imported, the spreadsheet is like this:

SpreadsheetExporter Actor


Next, let’s take a look at how to export data out from the spreadsheet. This is the interface of the SpreadsheetRowsExporter actor.

It will connect to the spreadsheet according to the configuration, then export data out of the spreadsheet. The output data format is an array of records. Remember to configure the data type of the “output” port. It could be “general” or “arrayType({email = string, name = string})” in this simple example. But in most cases, it should be set as the specific “arrayType”, like “arrayType({email = string, name = string})”.
(note: SpreadsheetRowsExporter actor can be used in COMAD system)

This is how to use SpreadsheetRowsExporter in a workflow:

After the workflow get the authentication, it will export data from the designated spreadsheet. This is the output:


In order to provide a more flexible way of how to use the data exported from the spreadsheet, a SpreadsheetColExporter is developed, so that data can be exported according to their column names. In this way, the output data can be more easily accepted by down stream actors, like RExpression actor to perform some computing on them.

This is the SpreadsheetColsExporter actor before being configured with the output ports:

After two ports were added:

This is how the SpreadsheetColsExporter is used in a workflow:

Remember to set the data type of ports – “name” and “email”. They should be “arrayType(string)” or “general” in this example.

This is the output from the workflow with SpreadsheetColsExporter, so the down stream actor can operate different tasks on different data:

There is a more complicated example of showing how to use these actors to resolve a environmental question:
Evapotranspiration Workflow