Skip to main content
BOOK A DEMO

Generate Excel workbook introduction

skybow Support
  • Article Updated
TL;DR: Generate Excel workbook lets you produce Excel files populated with SharePoint list data, based on an .xlsx template you upload. Configure placeholders directly in the template cells and format them as needed — the generator preserves cell formatting and outputs a ready-to-use workbook.

This article describes how to use the Generate Excel workbook action.

Generate Excel workbook allows you to generate Excel files based on a standard Microsoft Excel file filled with SharePoint list data. This can be triggered in actions used in Scheduled & Triggered actions. The generated workbook can be stored in a SharePoint library.

Main advantages:

  • The Excel workbook is generated on a fully customizable template
  • Creating a template is as simple as editing an Excel file and can be done by power users
  • Metadata of a single item, a list, or even connected sublists with all their data can be added into the workbook
  • Reports across a dossier (master-slave relation) can be created

The Generate Excel workbook action can produce different types of workbooks, such as:

  • Invoice
  • Factsheet
  • Project Report
  • Work Report
  • Order Confirmation
  • Inventory List
  • ... and many more

Table of Contents

Configuration UI

Configuration for a single item:

Configuration for multiple items:

Description of the configuration parameters:

Template

Provided Excel template (.xlsx) that contains placeholders can be uploaded here with "Upload new template" (templates are stored in a hidden library "sbDocumentGenerator")

The currently used template can be edited by clicking the Edit current template link.

Data

Choose the data scope for the generated workbook:

  • For single item / For multiple items
  • List — list from the current site
  • Item ID (only if For single item is selected) — ID or expression to be evaluated as the ID of the item to use as context in the workbook
  • List Query (only if For multiple items is selected) — CAML query used to filter list items
  • List View (only if For multiple items is selected) — defined filters in list views are merged with the query above
Target

Choose where the generated workbook is sent:

  • Target library — target document library where the generated workbook should be stored
  • Subfolder — fixed subfolder path or dynamically calculated using an expression, e.g. with placeholders
  • Filename — file name for the generated workbook; fixed name or dynamically calculated using an expression, e.g. with placeholders
  • Overwrite File — if checked and the file already exists in the configured library, the old file will be overwritten

Back to top

Excel Templates

The supported template format is .xlsx. The template is created in Microsoft Excel or other software that supports the .xlsx format.

Generate Excel workbook supports inserting skybow placeholders, conditions, loops, nested loops, and loops over rows, images, links, HTML directly in the Excel cells.

The template can contain several worksheets, and each of them can have its own placeholders, conditions and loops. All worksheets are filled with data from the same configured list and item. Formulas in the template are recalculated after the data is filled in.

Cell formatting in the template

If you want the generated workbook to display values in a specific format — such as numbers, dates, currencies or percentages — you must configure the cell format directly in the Excel template before uploading it.

To set cell formatting in Excel:

  1. Select the cell or range that contains the placeholder.
  2. Right-click and choose Format Cells.
  3. Select the desired category, for example Number, Date, Currency or Percentage.
  4. Click OK and save the template.

Note: Make sure the SharePoint column value passed to the placeholder is compatible with the cell format you choose. For example, a Date format requires the placeholder to return a valid date value.

Example of a template (download)

You can check out a sample template and explore the placeholder syntax by downloading an example from the skybow Portal.

Excel Generator Placeholder Expressions

You can use different kinds of expressions to show the metadata in an Excel workbook.

Expression syntax

Type Description Expression
Template Expressions Any kind of string Template Expression [[Title]] [[Firstname]]
Assignment Expressions Start with [[= and any kind of string Expression [[= [[Title]] + ", " + [[Firstname]] ]]
Function Code Expressions Start with [[{ in the Tag property. Write your complex expression as content in the placeholder [[{ var arr = [[@Web.GetValuesForQuery('OtherList', '<View></View>', 'Total')]]; var sum = 0; arr.forEach(n=>sum+=n); return sum; }]]
Lookup Values Use the [[Lookup.FieldInternalName]] notation to get any value from the target list of a lookup. Note: the lookup target list must be on the current site. [[Department.Code]]
[[Department.Author]]
[[Department.Key]] → item ID of the department
If Conditions Start with [[if …]] and any kind of boolean Expression (Condition). End with [[end if]]. Everything between the opening and closing tag is visible when the condition is true. [[if [[Budget]] > [[Outgoings]] ]] [[Title]] [[end if]]

[[if {return ([[Budget]] > [[Outgoings]]);} ]] [[Title]] [[end if]]

[[if [[Status]]!="Completed" ]] [[Title]] [[end if]]
Loops

Start with [[loop]] for looping over multiple items of the selected list. If a CAML Query is configured, looping is filtered accordingly. Use [[loop SublistTitle]] to loop over sublist items filtered by the lookup to the parent list. End with [[end loop]].

Use filterlookupfield('field_internal_name') to specify which lookup field to use when several lookups on the same list exist. Use rowlimit(number) to override the default limit of 200.

Note: a loop must start and end on the same worksheet; each worksheet can have its own loops.

 [[loop]] [[Title]] [[end loop]]

[[loop SubListTitle filterlookupfield('field_internal_name') rowlimit(number)]] [[Title]] [[Firstname]] [[end loop]]
Filtered Loops Where SomeView is a view .aspx page title. The order and filter of the view are applied. If a CAML query is added (optional), it is united with the view query using an and operator. [[loop SublistTitle view('SomeView') query('<Where><BeginsWith><FieldRef Name="Title" /><Value Type="Text">[[{return [[Title]];}]]</Value></BeginsWith></Where>')]] [[Title]] [[end loop]]
Loops over Documents in Sublibraries Use the Scope="Recursive" property on the query to get documents in a folder when used as a sublibrary of a dossier. Combine recursive scope and CAML query filters to filter listed document metadata. [[loop SublibraryName query('<View Scope="Recursive"></View>')]] [[FileLeafRef]] [[Title]] [[OtherField]] [[end loop]]

[[loop SublibraryName query('<View Scope="Recursive"><Query><Where><Eq><FieldRef Name="IsActive" /><Value Type="Boolean">1</Value></Eq></Where></Query></View>')]] [[FileLeafRef]] [[Title]] [[end loop]]
Nested Loops Looping over a sublist inside a loop over multiple items of the parent list is supported. Use [[@Loop.Parent.Field]] to retrieve parent loop data inside a nested loop. [[@Loop.Parent.Field1]]
[[@Loop.Parent.LookupField1.Field2]]
[[@Loop.Parent.Parent.Field3]]
[[@Loop.CurrentIndex]]
[[@Loop.Count]]

[[loop]] [[Title]] [[loop SubList1]] [[Title]] [[Firstname]] [[end loop]] [[end loop]]
Loops over Rows Loop over rows by placing the loop expression inside a row range in the Excel template. In nested loops, if the parent loop is placed in an outer row range, a separate row block is rendered for each parent item. [[loop SubListTitle]] [[Title]] [[Firstname]] [[Lastname]] [[end loop]]
Image

Show an image inside the workbook.

By default the image is placed in the cell (like Excel's "Place in Cell"), so it moves and sorts together with its row. Use placeovercell=true to place the image over the cell instead.

You can optionally specify the width and height of the image and also max/min width and height (in pixels). Width/height parameters are not mandatory, can be empty '' or null. Recommended to use .jpg format when specifying dimensions. Works only with images on the current site.

 [[image([[Picture]])]]
[[image([[Picture]], 50, 50)]]
[[image([[Picture]], 20)]]
[[image([[Picture]],,150)]]
[[image([[Picture]], maxwidth=100, maxheight=300)]]
[[image(url=[[Picture]], maxwidth=100, maxheight=300)]]
[[image(url=[[Picture]], width=100, placeovercell=true)]]
Link Create a hyperlink in the workbook. [[link([[Hyperlink]])]]
[[link([[Hyperlink.Url]], [[Hyperlink.Description]])]]
[[link(url=[[Hyperlink.Url]], text='linktext')]]
HTML Allow inserting HTML content inside the workbook. Supported with [[html placeholder]] and placeholders of Multiple lines of text fields with options: Rich text and Enhanced rich text. [[html [[MultiLineRichTextField]] ]]

Back to top

Placeholders Usage Details

The following placeholders from the Expression Builder can be used in the Excel template:

  • Item field placeholders (except [[..IsChanged]] and [[..OriginalValue]])
  • Web placeholders, including filtering features with CAML Query
  • Site placeholders, including functions for filtering with CAML Query
  • Output of variables and actions ([[@Variables..]] and [[@Actions..]])

Not supported for use in workbook generation:

  • Form fields [[..IsChanged]] and [[..OriginalValue]]
  • Functions placeholders

Back to top

Related articles