WRITE.EXCEL

Owned by Morten Lindequist Køhler
Feb 14, 2024
5 min read

Write content to a destination data source in the Microsoft Excel format.

Properties

From

Name of the Input Data Source.
The input-source must always be from the neutral Table-format.

To

Name of the Output Data Source.
The output-source can contain either a File-path, a PBinaryStream object or a .Net Stream object. See WRITE.* Property "To" for more information's and examples.
The output format can be either .xls or .xlsx

Parameters

@Template

Path to an Excel-file to use as a template.If a template is specified, this is used as an input-file, and can be used to specify where to output data in the document, and also what styles and fonts to use etc.

@Context

Allows more control over how values are output.
This entire value is optional, and all sub-parts of it is also optional (i.e. can be left blank) The context is specified as follows: 'Sheet/StartCell/Mode/Direction'

  • Sheet Specifies the name or index of the sheet to output data to.
    Empty The first sheet is used
    If a template is used, this is the sheet that will be targeted. If the sheet does not exist, it will be created.
    If no template is used, this will simply define what the name the single sheet will have.

  • StartCell Specifies the upper left cell where writing of the output needs to start. If template is specified this parameter will not be used if it is possible to find a cell containing a code e.g. {ProductName}.
    Empty If a template is used, it will be searched automatically to find the code-cell. If no code-cell is found it will default to the most upper-left cell A1.

  • Mode Specifies if the data should be overwritten or inserted
    O overwrites rows/cols depending on chosen direction
    I Inserts rows/cols depending on chosen direction

  • Direction Specifies the data-flow direction, e.g., if data-rows are flowing down or right.
    This value can be specified as either D or R (where D=Down and R=Right).
    Empty If the StartCell is a code-cell, then it will automatically try to find other code-cells either below or to the right of the StartCell. From this it tries to automatically find out whether data-rows should flow down or right. If it's not possible to determine the data-flow direction, it will default to D=Down.
    The direction is only necessary to specify if only a single code-cell is included, and the data-source delivers multiple records (in this situation it's not possible to find out in what direction the data should flow.

@Format

Specifies the Excel-file format to write.
The format can be set to either: xls or xlsx

@WriteFileIfInputIsEmpty

If the number of rows in the Data Source is zero, it is possible to control if the command should write a empty file or not. The value can be true or false and the default value is true. (Introduced from Perfion 2023 R1)

Map

No mapping can be done with this command. This Command is only meant to serve the final step of converting data to be saved in the output-format, to a File-path or send it to a Stream.
If you need to map data use one of the many other Commands such as e.g. SELECT or SELECT.UNIQUE before using this command.

Example 1 – No Template:

Setting Output as a variable programmatically.

Example 2 – Using a Template (Simple):

Below is an example of a simple template.

Below is the Action to use this template.
Notice that it is not necessary to specify the @context, as everything can be determined automatically from the template.

When the template is used by the command, all the cells that contain code-values are replaced with values delivered by the Action, and if the Action delivers multiple records, these will be added either underneath or to the right of the code-cells depending upon the specified direction.
Furthermore, all the design properties of the code-cells will persist for all additional records, including font-types, font-sizes, color of the values, color of the cell backgrounds etc.
This makes it possible to design templates exactly as you want. Any static content will also stay in the document, as long as it is added in areas that will not be overwritten by e.g. repeating code-cells due to multiple records.

Example 3 – Using a Template (Advanced):

Below is an example of a more complex template. This template uses data from 2 different data-sources, and the document title comes from a global parameter value.

Since there are multiple regions where data needs to be included, it is not possible to determine the entire context automatically.
The Action to use the above advanced template is shown below.

The above Action starts by setting the global parameter value $DocumentTitle, and then it establishes the 2 different data-sources to be used.
Then it calls the WRITE.EXCEL command 2 times.
The first time, it uses the template as it is defined above. It defines D3 as the start-cell. During this step, the {=$DocumentTitle} from the global parameter, as well as the {CustomerName} and {ContactName} from the CUSTOMERINFO data-source will be filled out.
The second WRITE.EXCEL command uses the output-file generated from the first command as template and adds all the data (and records) from the PRODUCTINFO data-source.
By calling WRITE.EXCEL multiple times, it will be possible to create even more complex templates with different data that needs to be output to multiple sheets etc.
In the above example the entire @CONTEXT is specified for both commands, however, if the name of the sheets was irrelevant, it would only be necessary to specify the StartCell, as the direction could be determined automatically by the command.