Excel Populate Template Syntax
PDF4me Excel Populate uses the Aspose Smart Markers syntax to merge JSON data into a designed Excel workbook. You type marker fields like &=Items.ItemName directly into the cells of a normal .xlsx file, then pass the workbook plus a JSON payload to the engine. This page is the canonical reference for the syntax: marker format, modifier parameters, multi-sheet behaviour, formula handling, culture formatting, and downloadable sample template + JSON files you can run end-to-end.
External reference: the Excel Populate engine implements the Aspose.Cells Smart Markers feature. The Aspose documentation is the authoritative grammar source; this page distils it for PDF4me users and adds the JSON-payload conventions specific to the PDF4me API.
Sample files
Download the sample templates and matching JSON data files used throughout this page. Drop them straight into the PDF4me Excel Populate REST endpoint, Power Automate action, or any of the integration platforms.
&=Items.* markers used in the Sheet1 sample.RootData.Items[] with description, qty, unit price, discount, and price.Items[] with string values to exercise the Strict JSON Strings setting.Products[]) for the third sheet.Employees[]) to populate the staff roster sheet.Marker format
The basic Smart Marker is a single cell value that begins with &= (ampersand-equals) and is followed by the data source name, a dot, and the field name.
&=DataSource.FieldName
Place the marker in the cell where the first record should land. When you supply a JSON array under DataSource, the engine writes the first record into that cell and inserts new rows below for every additional record. Adjacent cells holding markers on the same row are expanded together so each record stays aligned.
Minimal example
Template (Sheet1 of template.xlsx) is exactly this:
| A | B | C |
|---|---|---|
| Item Name | Quantity | Unit Price |
&=Items.ItemName | &=Items.Qty | &=Items.UnitPrice |
JSON payload (sheet23-data.json, the matching sample):
{
"Items": [
{ "ItemName": "A123", "Qty": "55", "UnitPrice": "3.05" },
{ "ItemName": "B456", "Qty": "20", "UnitPrice": "5.50" },
{ "ItemName": "C789", "Qty": "10", "UnitPrice": "12.99" }
]
}
Populated output: the engine writes the first record into row 2, inserts two additional rows below, and aligns columns by adjacent marker position. The header row in row 1 stays in place.
Modifier parameters
Smart Markers accept a comma-separated parameter list in parentheses immediately after the field name to control layout and behaviour.
&=DataSource.FieldName(parameter1, parameter2, ...)
The most useful parameters:
| Parameter | What it does | Example |
|---|---|---|
dynamic | Creates a dynamic range (and named formulas) around the populated cells. Use for output that feeds PivotTables, charts, or named-range formulas downstream. | &=Items.ItemName(dynamic) |
horizontal | Lays the records out left-to-right across columns instead of top-to-bottom down rows. Use for compact summaries or single-row dashboards. | &=Items.ItemName(horizontal) |
noadd | Replaces values in place without adding any new rows. Use when the template already contains enough pre-formatted rows and you do not want the layout to grow. | &=Items.ItemName(noadd) |
skip:N | Inserts N blank rows between each record. Useful for spaced-out reports such as packing slips or labels. | &=Items.ItemName(skip:1) |
copystyle | Copies the formatting of the marker cell down onto every populated row. Without it the first row keeps its style and the inserted rows pick up the default. | &=Items.ItemName(copystyle) |
shift | Shifts cells (right or down) when inserting populated rows so existing content below is preserved instead of overwritten. | &=Items.ItemName(shift) |
repeat | Repeats a header or section for each grouping. Used together with subtotal-style templates. | &=Items.ItemName(repeat) |
Parameters can be combined: &=Items.ItemName(dynamic, copystyle) creates a dynamic range AND copies the row style to every inserted record.
Nested data sources
When the JSON wraps your array under a parent object, reach into it with a dotted path. The Invoice sheet of template.xlsx is the canonical example. Its markers in row 2 read through RootData into Items:
B2: &=RootData.Items.ItemName
C2: &=RootData.Items.Description
D2: &=RootData.Items.Qty
E2: &=RootData.Items.UnitPrice
F2: &=RootData.Items.Discount
G2: &=RootData.Items.Price
The matching payload is sheet1-data.json:
{
"RootData": {
"Items": [
{ "ItemName": "Laptop", "Description": "Dell Inspiron 15", "Qty": 2, "UnitPrice": 750, "Discount": 50, "Price": 1450 },
{ "ItemName": "Mouse's", "Description": "Wireless Mouse", "Qty": 3, "UnitPrice": 25, "Discount": 0, "Price": 75 },
{ "ItemName": "Keyboard", "Description": "Mechanical Keyboard", "Qty": 1, "UnitPrice": 120, "Discount": 10, "Price": 110 }
]
}
}
Note the embedded apostrophe in "Mouse's". Smart Markers preserve quoted string primitives by default (the StrictJsonStrings setting defaults to true). Set it to false when you want the engine to coerce quoted numeric or date strings into typed Excel cell values during the merge.
Excel Tables and structured references
The Invoice sheet of template.xlsx demonstrates a powerful Smart Markers pattern: the marker row lives inside an Excel Table (named SimpleInvoice). Formula cells reference Table columns by structured reference instead of A1 addresses, and the Table range grows automatically as Smart Markers insert new rows.
A representative formula cell from the Invoice sheet:
=IFERROR(IF(SimpleInvoice[[#This Row],[Unit price]]="","",
(SimpleInvoice[[#This Row],[Qty]] * SimpleInvoice[[#This Row],[Unit price]])
- SimpleInvoice[[#This Row],[Discount]]
), "")
Aggregation cells outside the Table reference whole columns:
G7: =SUM(SimpleInvoice[Price]) // Invoice subtotal
G9: =IFERROR(G7*G8,"") // Sales tax (G8 is rate)
G11: =SUM(G2:G4)-G10 // Total (G10 is deposit)
Why this matters: when the engine expands the marker row from 1 record to N records, the SimpleInvoice table range expands automatically, SUM(SimpleInvoice[Price]) keeps summing the right column, and per-row formulas propagate down without you having to author them per record.
Multi-sheet templates
Excel Populate can populate every sheet in a workbook in a single call, or restrict the merge to a subset using the Worksheet Indexes parameter (1-based, comma-separated). The template-3-sheets.xlsx sample contains three sheets, in this exact order:
| Position | Sheet name | Markers used | Sample JSON |
|---|---|---|---|
| 1 | Items | &=Items.ItemName, &=Items.Qty, &=Items.UnitPrice | items-data.json |
| 2 | Employees | &=Employees.EmployeeName, &=Employees.Department, &=Employees.Salary | employees-data.json |
| 3 | Products | &=Products.ProductName, &=Products.Category, &=Products.Stock | products-data.json |
To populate all three sheets in one call, merge the three JSON payloads into a single object whose top-level keys match the data-source names from the markers:
{
"Items": [ { "ItemName": "A123", "Qty": "55", "UnitPrice": "3.05" }, ... ],
"Employees": [ { "EmployeeName": "John Doe", "Department": "IT", "Salary": "5000" }, ... ],
"Products": [ { "ProductName": "Laptop", "Category": "Electronics", "Stock": "15" }, ... ]
}
To populate only the Employees sheet, pass Worksheet Indexes = 2 in the action call. Positions 1 and 3 are left untouched. To populate Items + Products only, pass 1,3.
Formula handling
Smart Markers cooperate with native Excel formulas. Two important rules:
- Static formulas survive expansion. A formula like
=B2*C2in a row that gets expanded copies down to every populated row with the cell references adjusted in the usual Excel way. You do not need a marker for the formula cell. - Recalculation is on by default. The
Calculate Formulassetting controls whether the workbook is recalculated after population. Leave it true to receive a workbook whose totals and aggregates are accurate the moment you open it. Set it to false when you plan to recalculate later in your own pipeline and want to skip the cost.
Culture and locale formatting
The Culture & Language Settings parameter accepts a standard culture name (en-US, fr-FR, de-DE, ja-JP, etc.) and controls how numbers, dates, and currency are formatted when the engine writes JSON values into Excel cells. Set it to match the locale your recipients expect. The default is en-US.
The related Quote Prefix To Style setting carries the Excel quote-prefix flag onto styled values when present, so values that look like numbers but should display as text (account numbers, ZIP codes with leading zeros) stay text.
Common patterns
Invoice line items with totals row
A1: Invoice number B1: &=Invoice.Number
A2: Date B2: &=Invoice.Date
A4: Item B4: Qty C4: Unit price D4: Total
A5: &=Items.ItemName B5: &=Items.Qty C5: &=Items.UnitPrice D5: =B5*C5
A6: Grand total D6: =SUM(D5:D5)
When the engine expands row 5 for every item, the SUM range in row 6 expands with it because it sits below the marker rows.
Side-by-side comparison table (horizontal)
A1: Metric B1: &=Months.Name(horizontal)
A2: Revenue B2: &=Months.Revenue(horizontal)
A3: Expenses B3: &=Months.Expenses(horizontal)
Each month becomes a new column to the right of B instead of a new row below.
Pre-printed labels (noadd)
When you have 24 pre-formatted label cells on a sheet and you want the data to flow into them without adding rows:
&=Labels.Name(noadd)
&=Labels.Address(noadd)
The engine fills the existing 24 rows in order and silently truncates any records beyond what fits.
Quick checklist
&=&= in cell A1 (or any cell). Anything else is treated as a normal cell value.ItemName in the template does NOT match itemName in the JSON.