Home Nodes Excel Workbook

Excel Workbook

Permits use of an Excel spreadsheet as a calculation engine by setting and getting values of sets of ranges in an Excel workbook.

Properties

Connection

Type: Connection Input

FilePath
Type: String
The path to the Excel workbook.

ReuseExcelApplication
Type: Boolean
When true, the Excel application object will be kept open for the lifetime of the calling workflow. This provides a significant performance benefit.

ReuseExcelWorkbook
Type: Boolean
When true, the Excel workbook object will be kept open for the lifetime of the calling workflow unless the FilePath property is changed. This provides a significant performance benefit. When set to true, take care that setting range values does not affect the workbook state in a way that makes it unreliable for subsequent calls.

The Connection Property

Worksheet

Type: String Input
The name of the Worksheet within the Workbook in which to set ranges

InputRangeXml

Type: Xml Input
An XML representation of a set of values for ranges in the form rangevalue

OutputRangeXml

Type: Xml Output
An XML representation of a set of the range values that were not set by InputRangeXml (ie. output ranges) in the form rangevalue

Remarks

This Node permits re-use of Excel as a calculation engine and is particularly useful when applying custom business logic that can be owned by the end-user. This Node must be run at a DropPoint and FilePath should be set to a location that is accessible to the DropPoint. As a result, note that Excel needs to be installed on the same server as the DropPoint.

For greatest efficiency, set ReuseExcelApplication and ReuseExcelWorkbook set to True. ReuseExcelApplication causes the Connector to create an Excel application instance only once for the lifetime of the workflow. ReuseExcelWorkbook causes the Connector to load the Workbook only one for the lifetime of the workflow.

Setting up a connection

In order for the DropPoint to have permission to use the Excel libraries, it must be running under a user account and not the System account. Change this setting from Services by right-clicking the service, choosing Properties and clicking the Log On tab. Additionally, the user specified must be able to open the Excel document specified in the Connection.

If you receive an error accessing the document, ensure that both of these folders exist:

  • C:\Windows\SysWOW64\config\systemprofile\Desktop
  • C:\Windows\System32\config\systemprofile\Desktop (this folder is required for Windows 8)

Additionally, ensure that the Desktop folders have full read/write permissions for the relevant user.

Troubleshooting Error Codes

  • ErrDiv0 = -2146826281: Divide by 0 error
  • ErrNA = -2146826246: Not available error
  • ErrName = -2146826259: An identifier could not be evaluated
  • ErrNull = -2146826288: Two or more cell references are not separated correctly in a formula
  • ErrNum = -2146826252: Division by 0, or another calculation that results in an overflow of the defined value range (a value too big or too small)
  • ErrRef = -2146826265: A column or row description name could not be resolved, or the column, row, or sheet that contains a referenced cell is missing
  • ErrValue = -2146826273: Formula yields a value that does not correspond to the definition, or a cell that is referenced in the formula contains text instead of a number

Further reading can be found here at StackOverFlow .

Examples

See Sample Workflow for an example.

In order to use this example, copy it in to your Site, then set the path to a spreadsheet in the Connection Property. The spreadsheet for the example should look like this:

Note that the fields in column C are named ranges. For example, C2 has a range name of "Flowgear" as well as a value of "Flowgear".