Excel Connector

Version 21.0.7884


Excel Connector


The Excel Connector uses scripting templates to generate Excel sheets out of XML files.

Overview

Excel Connectors are configured primarily through the Template File. This template file is an Excel sheet that includes a combination of static column headers and ArcScript. The scripting dynamically populates the Excel document with data from input XML files processed by the connector. The Excel Connector includes a default Template File that can be used as a reference, and for more information please see the Templates section.

Connector Configuration

This section contains all of the configurable connector properties.

Settings Tab

Configuration

Settings related to the core configuration of the connector.

  • Connector Id The static name of the connector. All connector-specific files are held in a folder by the same name within the Data Directory.
  • Connector Description An optional field to provide free-form description of the connector and its role in the flow.
  • Template File The Excel file that functions as the output template. Data is dynamically added to the file based on the scripting within the template. More information can be found in the Templates section.

Local Folders

Settings that determine the folder on disk that files will be processed from, and where they will be placed after processing.

  • Input Folder (Send) The connector can process files placed in this folder. If Send Automation is enabled, the connector will automatically poll this location for files to process.
  • Output Folder (Receive) After the connector finishes processing a file, the result will be placed in this folder. If the connector is connected to another connector in the flow, files will not remain here and will instead be passed along to the Input/Send folder for the connected connector.
  • Processed Folder (Sent) After processing a file, the connector will place a copy of the processed file in this folder if Save to Sent Folder is enabled. This copy of the file will not be passed along to the next connector in the flow.

Performance

Settings related to the allocation of resources to the connector.

  • Max Workers The maximum number of worker threads that will be consumed from the threadpool to process files on this connector. If set, overrides the default setting from the Profile tab.
  • Max Files The maximum number of files that will be processed by the connector each time worker threads are assigned to the connector. If set, overrides the default setting from the Profile tab.

Other Settings

Settings not included in the previous categories.

  • Send Filter A glob pattern filter to determine which files in the Send folder will be uploaded by the connector (e.g. *.txt). Negative patterns may be used to indicate files that should not be uploaded (e.g. -*.tmp). Multiple patterns may be separated by commas, with later filters taking priority except when an exact match is found.
  • Local File Scheme A filemask for determining local file names as they are downloaded by the connector. The following macros may be used to reference contextual information:
    %ConnectorId%, %Filename%, %FilenameNoExt%, %Ext%, %ShortDate%, %LongDate%, %RegexFilename:%, %DateFormat:%.
    As an example: %FilenameNoExt%_%ShortDate%%Ext%
  • Log Subfolder Scheme By default, logs for transactions processed by the connector will be stored in the Logs subfolder for the connector. For connectors that process many transactions, it may be desirable to further divide the logs based on the datetime they were generated. When this setting is set to Daily, logs generated on the same day will be grouped in a subfolder;; when this setting is set to Weekly, logs generated in the same week will be grouped in a subfolder; and so on.

  • Log Messages Whether the log entry for a processed file will include a copy of the file itself.
  • Save to Sent Folder Whether files processed by the connector should be copied to the Sent folder for the connector.

Automation

Settings related to the automatic processing of files by the connector.

  • Send Whether messages arriving at the connector will automatically be processed.

Templates

Excel templates contain static column headers, scripting within Excel comments, and scripting within Excel cells. The scripting within Excel comments should use the xmlDOMSearch operation to loop through the input XML at a specified XPath. The scripting within Excel cells should use the xpath formatter to read values from the XML at a given xpath (this xpath is relative to the xpath specified in the xmlDOMSearch operation).

The following is a simple example a template that combines xmlDOMSearch and xpath:

Simple Template

The details of the xmlDOMSearch operation and xpath formatters in this example are described below.

xmlDOMSearch

The xmlDOMSearch requires two parameters:

  • uri

The URI is the resource path to the XML file to parse. The \[filepath\] attribute resolves to the input XML file to the connector, and the URI should almost always be set to this value. In the above example, the filepath is URL-encoded with the urlencode formatter to ensure that special characters in the filepath do not prevent the connector from reading the file: \[filepath | urlencode\]

  • xpath

The xpath is the XML path to loop over in the document. The operation will loop for each occurrence of the specified xpath: /Items/Orders. This means that each Orders element that is a child of the root Items element will cause a new set of output in the resulting Excel file.

xpath

The Excel comments with the xmlDOMSearch operation surround a block of cells that reference the input XML via the xpath formatter. The xpath formatter reads values from the input XML at the specified xpath. Note that this xpath is relative to the path provided as a parameter to the xmlDOMSearch operation.

In the above example, the first cell is populated with: \[xpath("OrderNo")\]. Since this xpath is relative, the cell will be populated with the value from the following path in the input XML: /Items/Orders/OrderNo.

If the xmlDOMSearch operation loops more than once (i.e. if more than one instance of the operation’s ‘xpath’ parameter is found), then the block of cells between the Excel comments will be repeated. The new cells are added vertically, so in the above example the second OrderNo cell would be directly below the first Comments cell.

Excel Operations

In addition to the Operations provided with ArcESB, connectors may provide operations that extend functionality into ArcScript. Operations specific to the functionality of the Excel Connector are listed below.

excelClose

Close an Excel connection.

Optional Parameters

  • handle: The handle for the Excel file.

Output Attributes

  • success: True if the connection is closed successfully.

excelCreate

Create a new Excel worksheet.

Required Parameters

  • sheet: The name of the Excel worksheet to create inside the workbook. The default value is ‘sheet1’.

Optional Parameters

  • file: The path to the Excel workbook.
  • version: The version of Excel you are using. The allowed values are ‘AUTO, 95, 97-2003, 2007’. The default value is ‘AUTO’.
  • overwrite: Indicates whether to overwrite the file if it already exists. The allowed values are ‘true, false’. The default value is ‘false’.
  • columnnames: Comma-separated list of the column names of the new spreadsheet. Column names cannot contain white space.

Output Attributes

  • file: The name of the Excel workbook updated.
  • sheet: The name of the Excel worksheet to be created inside the workbook. May be different from the sheet specified if the name conflicts with one already existing.

excelDelete

Delete the row by RowId in an Excel worksheet.

Required Parameters

  • sheet: The name of the Excel worksheet.
  • RowId: The start row index to be deleted from the document. Starts from 1.
  • count: The count of rows to be deleted from the document.

Optional Parameters

  • version: The version of Excel you are using. The allowed values are ‘AUTO, 95, 97-2003, 2007’. The default value is ‘AUTO’.
  • file: The path to the Excel workbook.
  • handle: The handle for the Excel file.
  • headerlocation: Location of the column headers. The default value is ‘NONE’.
  • recalculate: If true, the cell formulas that depend on the changed cells will be recalculated. If not, they will be calculated on the first opening in Excel. The default value is ‘true’.
  • Ignorecalcerror: If IgnoreCalcError is set to True any errors that occur due to formula calculation will be ignored. If this happens the formula result may be unreliable but other data will be accurate. The default value is ‘false’.
  • logfile: The log file used to log any errors that occurred during the calculation.
  • CloseFile: Indicates whether to close the file now or wait until the connection is closed.

Output Attributes

  • file: The name of the Excel workbook updated.
  • sheet: The name of the Excel worksheet updated.

excelGet

Queries the specified Excel worksheet.

Required Parameters

  • sheet: The name of the Excel worksheet.

Optional Parameters

  • version: The version of Excel you are using. The allowed values are ‘AUTO, 95, 97-2003, 2007’. The default value is ‘AUTO’.
  • file: The path to the Excel workbook.
  • handle: The handle for the Excel file.
  • map:*: This set of inputs contains a mapping of the attribute name and the name of the cell whose value is to be retrieved from the spreadsheet. For example, the attribute name map:MyValue which has a value of C1 will push an attribute named MyValue with the value found in the cell at C1 in the sheet. A range of cell names can also be specified to retrieve a range of cell values.

Output Attributes

  • *: Dependent on the content of the sheet and the query specified. If column headers are present they will be used to name the output attributes.

excelGetHeader

Describe the specified Excel worksheet.

Required Parameters

  • sheet: The name of the Excel worksheet.

Optional Parameters

  • version: The version of Excel you are using. The allowed values are ‘AUTO, 95, 97-2003, 2007’. The default value is ‘AUTO’.
  • file: The path to the Excel workbook.
  • handle: The handle for the Excel file.
  • headerlocation: Location of the column headers. The default value is ‘NONE’.
  • headernames: Comma-delimited list of column or row headers that you want to use (for example, firstName, lastName).
  • recalculate: If true, recalculate the formulas and save the results before returning values. The default value is ‘true’.
  • Ignorecalcerror: If ignorecalcerror is set to True any errors that occur due to formula calculation will be ignored. If this happens the formula result may be unreliable but other data will be accurate. The default value is ‘false’.
  • logfile: The log file used to log any errors that occurred during the calculation.
  • typedetectionscheme: The scheme to detect the data type.
  • rowScandepth: The row scan depth.

Output Attributes

  • *: Dependent on the content of the sheet and the range specified.
  • meta:row: The index of the row in the Excel document.

excelInsert

Append a record to an Excel sheet.

Required Parameters

  • sheet: The name of the Excel worksheet.
  • cell#: The cells you want to update.
  • value#: The values you want to set to the range.

Optional Parameters

  • version: The version of Excel you are using. The allowed values are ‘AUTO, 95, 97-2003, 2007’. The default value is ‘AUTO’.
  • file: The path to the Excel workbook.
  • handle: The handle for the Excel file.
  • headerlocation: Location of the column headers. The default value is ‘NONE’.
  • RowId: The index of the row appended to the document.
  • dataType#: The data types you want to set to the range.
  • allowformula: If true, the cell value that starts with an equals sign (=) will be treated as a formula. The default value is ‘true’.
  • recalculate: If true, the cell formulas that depend on the changed cells will be recalculated. If not, they will be calculated on the first opening in Excel. The default value is ‘true’.
  • ignorecalcerror: If IgnoreCalcError is set to True any errors that occur due to formula calculation will be ignored. If this happens the formula result may be unreliable but other data will be accurate. The default value is ‘false’.
  • logfile: The log file used to log any errors that occurred during the calculation.
  • CloseFile: Indicates whether to close the file now or wait until the connection is closed.

Output Attributes

  • sheet: The name of the Excel worksheet updated.
  • RowId: The index of the row appended to the document.

excelListSheets

Lists the worksheets in a specified Excel workbook.

Optional Parameters

  • version: The version of Excel you are using. The allowed values are ‘AUTO, 95, 97-2003, 2007’. The default value is ‘AUTO’.
  • file: The path to the Excel workbook.
  • handle: The handle for the Excel file.

Output Attributes

  • sheet: The name of the Excel worksheet. Note that the name ends with $. This is not required when specifying worksheet names to other operations.

excelOpen

Open an existing Excel workbook.

Required Parameters

  • file: The path to the Excel workbook.

Optional Parameters

  • version: The version of Excel you are using. The allowed values are ‘AUTO, 95, 97-2003, 2007’. The default value is ‘AUTO’.

Output Attributes

  • handle: The handle which is used to execute other ops.

excelUpdate

Update cells in an Excel worksheet.

Required Parameters

  • sheet: The name of the Excel worksheet.
  • RowId: The index of the row updated to the document.
  • cell#: The cells you want to update.
  • value#: The values you want to set to the range.

Optional Parameters

  • version: The version of Excel you are using. The allowed values are ‘AUTO, 95, 97-2003, 2007’. The default value is ‘AUTO’.
  • file: The path to the Excel workbook.
  • handle: The handle for the Excel file.
  • headerlocation: Location of the column headers. The default value is ‘NONE’.
  • allowformula: If true, the cell value that starts with the equals sign (=) will be treated as formula. The default value is ‘true’.
  • recalculate: If true, the cell formulas that depend on the changed cells will be recalculated. If not, they will be calculated on the first opening in Excel. The default value is ‘true’.
  • Ignorecalcerror: If IgnoreCalcError is set to True any errors that occur due to formula calculation will be ignored. If this happens the formula result may be unreliable but other data will be accurate. The default value is ‘false’.
  • logfile: The log file used to log any errors that occurred during the calculation.
  • CloseFile: Indicates whether to close the file now or wait until the connection is closed.

Output Attributes

  • file: The name of the Excel workbook updated.
  • sheet: The name of the Excel worksheet updated.
  • range: The cell range updated.