Excel(Output)

The Excel(Output) component updates the specified ExcelBuilder defined content in the specified Excel file, and then outputs the data.

To use this component, it is necessary to define the Excel cells to which the input data is to be written and the format that this data will take. This can be done in the following steps:

1. open the ExcelBuilder
2. specify the Excel template file to read
3. specify work book cells on which this component is to operate
4. define the ASTERIA field's data type for each cell

Stream Information

InputFormatRecord
Number of Inputs1
Description

The record to write to the Excel file.

Writes the data to the area specified by the ExcelBuilder. It is possible to define the access details using the Mode property.

Fields are defined in the ExcelBuilder, not ASTERIA Designer.

If all the input records cannot be written to the specified area, an Exception is thrown.

OutputFormatBinary
Description

The modified Excel file image data.

Component Properties

NameData Type/SelectionMappingDescription
CellcategoryInput Only

Specifies the Excel cells, as defined by the ExcelBuilder, to which the input record data is to be written.

When a flow is executed, these cells' values are written to the Excel file's respective cells. Fields are defined in the ExcelBuilder, not ASTERIA Designer.

FilePathstringYes

Specifies the file location of the Excel file.

If this file does not exist, an empty file is created and the appropriate cell data updated. The new file is then output.

If UpdateFile=False, the updated file data is not saved.

When this property uses a relative path, the HomeDirectory property defines the base directory.

HomeDirectorychoiceNone

Specifies the base directory used by the FilePath.

ProjectOwner sets the base path as the project owner's HOME directory. ExecuteUser sets the base path as the execute user's HOME directory.

ModechoiceYes

Specifies the way data is written to the ExcelBuilder specified cells and records.

  • Override clears the record area and writes the new data.
  • Append writes the data to the record following the record that matches the key.
    If a key is not defined or not matched, the data is written to the next empty line.
  • Insert writes the data to the record before the record that matches the key.
    If a key is not defined or not matched, the data is written to the first row in the sheet.
  • Update writes the data to the record that matches the key.
    If a key is not defined or not matched, the data is written to the last empty row.
  • Delete deletes the record that matches the key.
    If a key is not defined, all records are deleted.
    If a match is not found, nothing is done.

If all the records cannot be written to the record area an Exception will occur. In the case of Append or Insert, if there are no more empty lines to write data to, new lines will not be created.
UpdateFilebooleanYes

True updates the Excel file defined by the FilePath. False does not update the Excel file defined by the FilePath.

AllowBlankRecordbooleanYes

Specifies whether an empty record, which is defined as a record in which all its fields are empty, is allowed in the output record area or not. This property is used to effect how empty records are handled when the Mode property is set to Append, Insert, or Update.

True allows empty records. Cells that have defined cell formatting such as fonts or line settings are not treated as empty. Furthermore, if doing cell formatting within the record area, the record will be determined to be 'full' and an Exception will occur. If doing cell formatting within the record area is necessary, specify this property as False.

False prohibits an empty record.

OutputSheetNamestringYes

Specifies the name of the Excel sheet to which the ExcelBuilder defined cells and records are to be updated in the Excel file. If the defined work sheet does not exist, a new work sheet is created. This property can be defined in a preceding Mapper component.

OutputSheetCreateModechoiceYes

If the OutputSheetName is defined, and the specified sheet does not exist in the work book, this property specifies whether a new empty sheet is created, or if a copy of a specified work sheet is made.
If the OutputSheetName is not defined, this property is ignored.

New creates a new, empty worksheet.
Copy makes a copy of the sheet defined in the ExcelBuilder.

ConvertNullchoiceYes

The value to be written to a sheet's number cell if the value being written is NULL.
Zero - "0" is written to the cell.
Empty - An empty cell is written.

Loop Management

This component cannot be the starting point for a loop.

Transaction Management

Commit

Do Nothing

Rollback

Do Nothing

Exceptions

TypeParametersFlow Input StreamError CodeCause
Exception
No.NameDescription
1FilePathThe FilePath property value at the time of the Exception.

This component's input stream.

none Cannot write to the specified record area.
This Exception is generated even if AllowBlankRecord is set to True, but the record area's formatting is different than specified.
none Cannot write to the FilePath.
none If HomeDirectory=ExecuteUser but the execute user for this flow wasn't set at runtime.
none Opening an Excel file of an unsupported format.

Excel files formatted prior to Excel 97, such as Excel95 or older versions, must be saved as an Excel97 formatted file to be used by this component.

Limitations

This component can read Excel formatted files since Excel97. However, this component does not support the reading of Excel files formatted prior to Excel 97, such as Excel95 or older.

This component can write a file as large as 5MB. Only up to 8K of character data (4096 full length characters) per cell is processed.

CategoryMaximum Cell Limit
Number300,000 cells
Text200,000 cells of 4 bytes (2 full length characters)
50,000 cells of 80 bytes (40 full length characters)
600 cells of 8K (4096 full length characters)
The amount of data that can be input decreases as the number of cells increases. This occurs because the Excel format stores an additional 10 to 20 bytes of cell formatting data for each cell.