Excel

Using Documotor with Microsoft Excel

This section outlines the configuration of Documotor in Excel, describing how to bind transformed data to your Microsoft Excel workbooks.

Documotor can be used to insert transformed data using bindings in Excel. Bindings in Excel are placed into comments to named ranges, which can be both single cells and table-shapes ranges or cells. The Define Name option from the right-click menu after selecting a range and the Name Manager are used a lot while configuring bindings in Excel.

The ranges of the spreadsheet define exactly where the data is inserted, while the binding inserted into the comment has the format of a JSON object, whose keys and values define which pieces of data are inserted, just like in Word bindings. This is the general format:

{ "BindingType": "type", "BindingKey": "value" }.

The different binding types will be explained in-depth in the articles in this chapter, and binding keys are explained in another article.

Bindings

Three binding types are currently supported in Excel. The complexity of Excel files usually comes from intricate logic and formulas used to construct them, rather than the data itself. Here are the Excel bindings:

BindingDescription
FieldInsert text.
FormatSet background and text colors.
RepeatRepeat content within a range.

A detailed overview of each of the binding types follows.

Field

{ "BindingType": "Field", "BindingKey": "TextToInsert" }

Replaces the content in the cell with the string or number in the data. The content is removed if the path doesnโ€™t exist or is null. If there is no content in the cell, no new content will be inserted. Primarily to be used with single-cell ranges, in case of use with larger ranges, the content is only inserted in the top-left cell of the range.

Parameters

string BindingType

Always "Field" to invoke the Field binding.

JSON path TextToInsert

A path to a string or number to insert inside of the content control.

Field bindings in header and footer

Field bindings can be inserted into headers and footers as well. In that case, insertion isn't done via the name manager, but follows the PowerPoint format of

<<Field:BindingKey>>

and is inserted directly into the header.
Note that the header and footer are accessed from the Page Layout view. Also, the character limits of Excel apply when inserting content into the header and footer. If the length exceeds the limit, the file will still be generated, but will not open in Excel. In that case, you can generate it as a PDF or open it using LibreOffice.

๐Ÿšง

Currently, bindings in header and footer cannot be applied if there are no cell bindings in the file.

Format

{"BindingType": "Format", "BindingKey": "ColorsPath"}

Sets the background and text colors of the area inside the content control.

Parameters

JSON path BindingType

Always "Format" to invoke the Format binding.

JSON path ColorsPath

A path pointing to an object of the following format:

{
  "ForegroundColor": "FF7A00",
  "BackgroundColor": "0,122,255"
}

Remarks

The colors in the values of the ForegroundColor and BackgroundColor properties are strings that can be of a hex format (without #) or "R,G,B", where the values for red, green and blue are separated only by a comma.

Repeat

{ "BindingType": "Repeat", "BindingKey": "ArrayPath"}

Repeats content inside the named range once for each element of ArrayPath. Each new repetition is placed into the next row, naturally building tables from arrays. This is especially valuable when there are nested content controls inside the repeat content control. In the nth repetition, the scope is the nth element of ArrayPath.

Parameters

string BindingType

Always "Repeat" to invoke the Repeat binding.

JSON path ArrayPath

A path to an array defining the repeating of elements and setting the scope.