Smartly importing and placing data with Smart markers in Python via Java

Introduction

Smart markers are used to let Aspose.Cells know what information to place in a Microsoft Excel designer spreadsheet. Smart markers allow you to create templates that contain only specific information and formatting.

Designer Spreadsheet & Smart Markers

Designer spreadsheets are standard Excel files that contain visual formatting, formulas and smart markers. They can contain smart markers that reference one or more data sources, such as information from a project and information for related contacts. Smart markers are written into the cells where you want the information.

All smart markers start with &=. An example of a data marker is &=Party.FullName. If the data marker results in more than one item, for example, a complete row, then the following rows are moved down automatically to make room for the new information. Thus sub‑totals and totals can be placed on the row immediately after the data marker to make calculations based on the inserted data. To make calculations on the inserted rows, use dynamic formulas.

Smart markers consist of the data source and field name parts for most information. Special information may also be passed with variables and variable arrays. Variables always fill only one cell, whereas variable arrays may fill several. Only one data marker should be used per cell. Unused smart markers are removed.

Smart markers may also contain parameters. Parameters allow you to modify how the information is laid out. They are appended to the end of the smart marker in parentheses as a comma‑separated list.

Smart Marker Options

&=DataSource.FieldName
&=[Data Source].[Field Name]
&=$VariableName
&=$VariableArray
&==DynamicFormula
&=&=RepeatDynamicFormula

Parameters

The following parameters are allowed:

  • noadd – Do not add extra rows to fit data.
  • skip:n – Skip n number of rows for each row of data.
  • ascending:n or descending:n – Sort data in smart markers. If n is 1, then the column is the first key of the sorter. The data is sorted after processing the data source. For example: &=Table1.Field3(ascending:1).
  • horizontal – Write data left‑to‑right instead of top‑to‑bottom.
  • numeric – Convert text to number if possible.
  • shift – Shift down or right, creating extra rows or columns to fit data. The shift parameter works the same way as in Microsoft Excel. For example, in Microsoft Excel, when you select a range of cells, right‑click and select Insert, then specify Shift cells down, Shift cells right, and other options. In short, the shift parameter fills the same function for vertical/normal (top‑to‑bottom) or horizontal (left‑to‑right) smart markers.
  • copystyle – Copy the base cell’s style to all the cells in that column.

The parameters noadd and skip can be combined to insert data on alternating rows. Because the template is processed from bottom to top, you should add noadd on the first row to avoid extra rows from being inserted before the alternate row.

If you have multiple parameters, separate them with a comma, but no space: parameterA,parameterB,parameterC.

The following screenshots show how to insert data on every other row.

Template File Output File
todo:image_alt_text todo:image_alt_text

Dynamic Formulas

Dynamic formulas allow you to insert Excel formulas into cells even when the formula references rows that will be inserted during the export process. Dynamic formulas can repeat for each inserted row or use only the cell where the data marker is placed.

Dynamic formulas allow the following additional options:

  • r – Current row number.
  • 2, -1 – Offsets to the current row number.

For example:

&=B{-1}/C{-1}~(skip:1)

In the dynamic formula marker, “-1” denotes the offset to the current row in the B and C columns respectively, which will be used for the division operation. The skip parameter is one row. Moreover, we should specify the following character:

~

as a separator character to apply further parameters in dynamic formulas.

The following screenshots illustrate a repeating dynamic formula and the resulting Excel worksheet.

Template File Output File
todo:image_alt_text todo:image_alt_text

Cell C1 contains the formula =A1*B1, cell C2 contains =A2*B2, and cell C3 contains =A3*B3.

It’s very easy to process the smart markers. What follows is a code snippet in Python via Java that shows how it is done.