Generate Excel

You need to generate one or multiple .xlsx file. The .xlsx file can be generated with formatting and images. A template file with the extension .xslx must be created with a software like Microsoft Excel or Google Sheets.

Json

Example

{
  "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	Description
request	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}.
templates	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.

Input request

Example

<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>

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
files		The files generated.
listing		An xml file representing the list of the temporary files.
allFilesGenerated		YES | NO. YES: all files have been generated successfully. NO: at least one file has not been generated.

Output listing

Example

<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.

FAQ

Can I use Excel formulas?

Yes. The formulas will be executed when the file will be opened by the user in Excel.