Append Excel

Outputs data to an Excel workbook (.xlsx, .xls or .xlsm), with the ability to append data to different worksheets or add new worksheets. If the file does not exist, a new file will be created.

By default, only the actual data and some of the cell styles are preserved if you are appending to an existing file. Formulas will not be preserved as formulas, rather the values will be stored. Images, charts, and other formatting information will not be preserved.

If you want to use an existing Excel file (with charts, formulas, pictures etc) as a template and simply populate the data via a node, the Output Excel node should be used with the TemplateInputFile property specified.

If you want to append to an existing Excel file with charts, formulas, pictures etc and you want to use this node then you can change the ExistingDataPreservation property and set it to preserve all. Please be aware that doing so will cause all information in the existing Excel workbook to be loaded into memory, and the node may run out of memory while executing depending on the size of the workbook.

Properties

File

Click the folder icon and browse to the Excel file to which you want to export the data, then click Choose.

To create a new file, click the folder icon and navigate to the folder where you want to output the file, then enter a name for the file in the Filename field and click Choose.

A value is required for this property.

Note: If you are using the server edition, the output file will be created on the Data360 Analyze server. If you wish to open the file, and you don't have access to the file system on the server, you will need to download the file onto your local machine. Please see the topic Downloading data from the server for more details.

SheetMatchMethod

Optionally specify how to determine which input to write to which worksheet:

  • Index - use the index of the input pin and try to locate an existing worksheet.
  • Name - use the name of the input pin and try to locate an existing worksheet.
  • None - don't match the input pin to any existing worksheet.

The default value is Index.

DataAppendMethod

Optionally specify how the data from an input pin should be written to file when the input pin matches an existing worksheet:

  • Append - all data will be written after the last row in the existing sheet.
  • Clear Worksheet - any existing data in the worksheet will be cleared, and the new data will be written at the top of the worksheet.
  • Overwrite Data - the data in the existing worksheet will be left in place, However, if the new data is to be written to the same location as an existing cell, then that data cell will be overwritten. By default the new data will be written to the top of the worksheet.

The default value is Append.

ExistingDataPreservation

Optionally specify how to load the existing Excel file:

  • Data Only - only the data will be read and is able to be preserved.
  • Preserve All - everything from the existing file will be read and is able to be preserved. This can lead to memory issues as all charts, images, data, etc. need to be loaded into memory.

The default value is Data Only.

HeaderBold

Optionally specify whether to format the header line in the output spreadsheet in bold.

The default value is True.

FormatOutput

Optionally specify the source for the Excel spreadsheet cell formatting:

  • From BRD - the data in the output Excel spreadsheet is formatted to match the data types in the input BRD.
  • From Template - the data in the output Excel spreadsheet uses the same format as specified in the template file.
  • No Formatting - all data is output as text.
  • true - same as From BRD - provided for backward compatibility.
  • false -same as No Formatting - provided for backward compatibility.

The default value is From BRD.

OutputSpec

To define which input pin is written to which Excel worksheet, and where within the worksheet the data is to be output, add XML code to the OutputSpec property.

All indexes are 1-based (first is 1, second is 2). All attributes must have values surrounded by double quotes " even if they are numeric. Use the following key words when adding the XML code:

<workbook> Top level container. Contains <sheet> elements.

<sheet> Use this tag to refer to individual worksheets within an Excel file. There are various optional and required attributes that can be used within the <sheet> tag to describe which input is mapped to which worksheet, and how the data is to be output:

  • inputIndex - Specifies which input pin the data is coming from (required).
  • outputIndex - Specifies which worksheet of the output Excel file the data is to be output to (required).
  • dataStartRow - Specifies which row in the worksheet will contain the first record of the input (required).
  • dataStartColumn - Specifies which column in the worksheet will contain the first input field (required).
  • headerRow - Specifies which row within the worksheet will contain the input metadata (optional). This attribute only takes effect when the a header row is output. If headerRow is not set, and a header row is included in the output, the header row is output to the row prior to that specified in dataStartRow. The first field in the header row is always output to the worksheet column specified by dataStartColumn.
  • outputHeaderRow - Specifies whether or not to include the input metadata in the output. If set to false, the headerRow attribute is ignored (optional).

For any worksheets that are referenced within the OutputSpec property, the attribute provided overrides the behavior specified by other properties. The output behavior of any inputs that are not referenced in the OutputSpec property is defined by the other node properties.

The OutputSpecExample property gives an example of the XML code that you can use in the OutputSpec property.

TrustedSource

Specify whether or not to trust any Excel file source used by the node.

XLSX and XLSM files are stored in a zip format. Malicious zip files can contain "zip bombs".

To prevent such attacks, this node is configured to error if it detects that the file is decompressing to a much larger size than the compressed version. This can lead to false positives where non-malicious files are detected to contain zip bombs simply due to the way that they are decompressed.

If you receive such errors while attempting to process an Excel file that you know is from a trusted source and cannot contain a zip bomb, set this property to True which will inform the node that the file is safe to process.

The default value is False.

NoSheetForInputAction

Optionally specify an action to perform when no worksheet exists for a given input:

  • Error - the node will error.
  • Ignore - the data in the input will not be written to the worksheet.
  • Log - the data in the input will not be written to the worksheet, but a message will be logged.
  • Create Sheet - a new worksheet will be created.

The default value is Create Sheet.

NoInputForSheetAction

Optionally specify an action to perform when there are worksheets in the file that do not match any of the input pins:

  • Error - the node will error.
  • Remove Sheet - the non-matching worksheet will be removed from the spreadsheet.
  • Leave Sheet - the non-matching worksheet will be left in the spreadsheet.

The default value is Leave Sheet.

Inputs and outputs

Inputs: in1, multiple optional.

Outputs: Multiple optional.