Generate Excel documents

This task allows to generate one or more Excel documents.

Examples

Generate a single Excel document from a template

json

{
  "name": "file-generation-xlsx",
  "taskReferenceName": "generate_excel",
  "description": "The business description of the task",
  "type": "SUB_WORKFLOW",
  "optional": false,
  "inputParameters": {
    "request": "${previous_taskReferenceName.output.file}",
    "templates": [
      {
        "key": "shoes",
        "file": "file://assets/shoes.xlsx"
      }
    ]
  }
}

Definition

Property	Type	Required	Description
			The name (or type) of the task
			The unique name of the task in your job. It is used to reference this task in the workflow.
			The functional description of this task in your job.
			The type of the task. It must be `SUB_WORKFLOW`.
			`true`: the job continues if there is an error on this task. `false`: the job fails.
			Input parameters of the task. See below

Inputs

Property	Type	Description
		A valid input. See the complete definition below. It can reference the output of a previous task, example: `${previous_taskReferenceName.output.file}` , or a static file, example: `file://assets/file.xml`, or a user input, example: `${workflow.input.input_key}`.
		An array of objects, where each object has a key property and a file property. The key is the reference used in the xml request, in the `<Template-Key>` element. The file must be a .xlsx file in the assets folder of the job.
		The key of the template to use.
		The file to use as a template. The following formats are currently supported: *Excel Workbook* (extension: .xlsx): `The default XML-based file format for Excel 2010 and Excel 2007. Cannot store Microsoft Visual Basic for Applications (VBA) macro code or Microsoft Office Excel 4.0 macro sheets (.xlm).` (source) *Excel Macro-Enabled Workbook (code)* (extensionn: .xlsm): `The XML-based and macro-enabled file format for Excel 2016, Excel 2013, Excel 2010, and Excel 2007. Stores VBA macro code or Excel 4.0 macro sheets (.xlm).` (source)
	:	Default: `false` When set to `true`, the report generation is skipped and thus the report is not available in the output parameters.

Input request

Example

xml

<Generate-Excel>
  <File>
    <File-Name>products.xlsx</File-Name>
    <Template-Key>shoes</Template-Key>
    <Sheets>
        <Sheet>
            <Sheet-Name>products</Sheet-Name>
            <Cells>
                <Cell-Text line="1" column="1">EAN</Cell-Text>
                <Cell-Text line="1" column="2">Price</Cell-Text>
                <Cell-Text line="1" column="3">Image</Cell-Text>
                <Cell-Text line="1" column="4">Link</Cell-Text>
                <Cell-Text line="2" column="1">0123456789012</Cell-Text>
                <Cell-Number line="2" column="2">12.3</Cell-Number>
                <Cell-Image line="2" column="3">https://host.com/28a01a76849106eb.jpg?preset=small</Cell-Image>
                <Cell-Link line="2" column="4" type="URL" url="http://product-live.com">Link to product-live website</Cell-Link>
            </Cells>
        </Sheet>
    </Sheets>
  </File>
</Generate-Excel>

Notes on the style of the cells

The action of inserting content in a cell (text content, number or image), does not alter the style present for this cell in the target template file.

Definition

You can use a static file stored in the asset folder, or you can generate this file from a Transform XSLT task.

XPath	Description	Occurs
`Generate-Excel`	Root of the document.	1
`./File`	For each file.	1..*
`./File/File-Name`	The output file name.	1
`./File/Template-Key`	The template key defined in the json.	1
`./File/Sheets`	Sheets.	1
`./File/Sheets/Sheet`	For each sheet.	1..*
`../Sheet/Sheet-Name`	The sheet name. If the sheet does exist it will be created.	1
`../Sheet/Cells`	Cells.	1
`../Cells/Cell-Text`	Cell with type text.	0..*
`../Cells/Cell-Text/@line`	Line position, starts at 1.	1
`../Cells/Cell-Text/@column`	Colum position, starts at 1.	1
`../Cells/Cell-Number`	Cell with type number. You must use . as separator.	0..*
`../Cells/Cell-Number/@line`	Line position, starts at 1.	1
`../Cells/Cell-Number/@column`	Colum position, starts at 1.	1
`../Cells/Cell-Image`	Cell with type image. The image will fit the space of the cell.	0..*
`../Cells/Cell-Image/@line`	Line position, starts at 1.	1
`../Cells/Cell-Image/@column`	Colum position, starts at 1.	1
`../Cells/Cell-Link`	Cell with type link. You can create different type of links, see below.	0..*
`../Cells/Cell-Link/@line`	Line position, starts at 1.	1
`../Cells/Cell-Link/@column`	Colum position, starts at 1.	1
`../Cells/Cell-Link/@type`	`URL` \| `FILE` \| `EMAIL` \| `DOCUMENT`. `URL` : open a valid url on your default web browser. `FILE`: open a file on your local system , the url attribute must be a valid path. `EMAIL`: open your default email program, the url attribute must be a valid email. `DOCUMENT`: reference another cell in the document, the url attribute must be like : 'Sheet1'!A1	1

Outputs

Property	Type	Description
		The files generated.
		An xml file representing the list of the temporary files.
		YES \| NO. YES: all files have been generated successfully. NO: at least one file has not been generated.
		An XML file containing a set of logs related to the execution of the task. This output is not available if the input `skipReportGeneration=true`.

Output `listing`

Example

xml

<Files>
  <File>
    <Url>https://app.product-live.com/files-data-factory/d05a74cf11788d8f3ae9bf0e0e028dde66g0c83005c5e0d1211b0069945c0c11</Url>
    <File-Name>products-1.xlsx</File-Name>
  </File>
  <File>
    <Url>https://app.product-live.com/files-data-factory/fb26911d77fe9a9dc45b111eef5b5db7ca2019c8038445662f29b20c54cb6f29</Url>
    <File-Name>products-2.xlsx</File-Name>
  </File>
</Files>

Definition

XPath	Description	Occurs
`Files`	Root of the document.	1
`./File`	For each file.	0..*
`./File/Url`	The url of the temporary file.	1
`./File/File-Name`	The file name.	1

Limits and additional notes

The limitation of files created is 50.
Excel has some constraints on the naming of sheets. If one of the constraints below is not respected, the Excel document cannot be generated and the task returns NO for the allFilesGenerated property.
- The maximum sheet naming size of the output Excel file is limited to 31 characters. All additional characters are truncated.
  - Ex: ENCASTRABLE_HOTTE_ASPIRANTE_HOTTE_CASQUETTE_1 (45 characters) becomes ENCASTRABLE_HOTTE_ASPIRANTE_HO (31 characters)
- The names of the sheets in the same target Excel file must be unique.
- The name of the sheets in the output Excel file should not contain the following characters / ? * [ ].
- The name of the sheets in the output Excel file must not be empty.

Generate Excel documents ​

Examples ​

Definition ​

Inputs ​

Input request ​

Example ​

Notes on the style of the cells ​

Definition ​

Outputs ​

Output listing ​

Example ​

Definition ​

Limits and additional notes ​

Generate Excel documents

Examples

Definition

Inputs

Input request

Example

Notes on the style of the cells

Definition

Outputs

Output `listing`

Example

Definition

Limits and additional notes