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.
|
| @Format | Specifies the Excel-file format to write. The format can be set to either: xls or xlsx |
| @OutputFileAs |
Allows to retrieve the output of an action and write it to a file path, a stream, or the application server jobs log. Possible values:
|
| @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.
Comments
0 comments
Please sign in to leave a comment.