Introduction

Smart Marker is a way to let Aspose.Cells know that what information you wish to place in an Excel designer spreadsheet. Smart Markers allow you to create templates that contain only relevant information and are formatted to meet your needs.

Designer Spreadsheet & Smart Markers

Designer spreadsheets are standard Excel files that contain the 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 cells where you want information to be filled in.

All smart markers start with "&=", without the quotes. An example of a data marker is &=Party.FullName. If the data marker results in more than one item, i.e. row then following rows will be moved down automatically to make room for all of the new information. Thus sub-totals and totals can be placed on the following row after the data marker to make calculations based on inserted data. In order to make calculations on the rows that are inserted, you must 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 ones. You may only use one data marker per cell. Unused smart markers will be removed.

Smart marker may also contain parameters. Parameters allow you to modify how the information will be laid out. They are appended to the end of smart marker in parenthesis 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

horizontal

Write data left-to-right, instead of top-to-bottom.

numeric

Convert text to number if possible.Only supported in .NET version.

shift

Shift down or right extrs rows/columns to fit data.

bean

Indicates the data source is a simple POJO. Only supported in JAVA version.

The parameters noadd and skip may be combined to insert alternating data on every other row. Since the template is processed from the bottom to the top, you should add the 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.

The following illustrates inserting data on every other row.

 

becomes…

 

 

Dynamic Formulas

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

Dynamic Formulas allow following additional options:

{r} - Current row number

{2}, {-1} - Offset to current row number

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

 

becomes…

 

 

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

Sample Code

It's very easy to process the smart markers. Following is a sample code.

Example:

[C#]

 

 

//Instantiating a WorkbookDesigner object

WorkbookDesigner designer = new WorkbookDesigner();

 

 

//Open a designer spreadsheet containing smart markers

designer.Open(designerFile);

 

 

//Set the data source for the designer spreadsheet

designer.SetDataSource(dataset);

 

 

//Process the smart markers

designer.Process();

 

 

[VB.NET]

 

 

'Instantiating a WorkbookDesigner object

Dim designer As WorkbookDesigner = New WorkbookDesigner()

 

 

'Open a designer spreadsheet containing smart markers

designer.Open(designerFile)

 

 

'Set the data source for the designer spreadsheet

designer.SetDataSource(dataset)

 

 

'Process the smart markers

designer.Process()

 

 

[JAVA]

 

 

//Instantiating a WorkbookDesigner object

WorkbookDesigner designer = new WorkbookDesigner();

 

 

//Open a designer spreadsheet containing smart markers

designer.open(designerFile);

 

 

//Set the data source for the designer spreadsheet

designer.setDataSource(dataset);

 

 

//Process the smart markers

designer.process();

 

 

Grouping Data

 

In some excel reports you might require to break the data into groups in order to make it more easier to read and to analyze. One of the primary purposes for breaking data into groups is to run calculations (perform summary operations) on each group of records. Aspose.Cells Smart Markers allow you to group your data by your desired field(s) set and place summary rows in between your data sets or data groups. For example, if you group your data by {Customers.CustomerID}, at each change of the group you will see the corresponding summay record added. This section shows you how to group data in an excel report using Smart Markers feature.

Parameters

 

Following are some Smart Marker parameters involved for grouping data.

 

1). group:normal/merge/repeat

 

we support 3 types of group, so you may choose any one for your requirement. If you specify normal parameter, the group by field(s) value will not be repeated for the corresponding records in the column rather they would be printed once per data group. The merge parameter would show the same behavior as normal except it will merge the cells in the group by field(s) for each group set. If you specify repeat parameter, the group by field(s) value will be repeated for the corresponding records.

 

E.g., &=Customers.CustomerID(group:merge)

 

2). skip

 

It skips number of row(s) after each group.

 

E.g., &=Employees.EmployeeID(group:normal,skip:1)

 

 

3). subtotalN

 

It actually performs summary operation for a specified field data related a group by field. The "N" here, is actually the number b/w 1 to 11 that specifies which function (1=AVERAGE, 2=COUNT,

3=COUNTA, 4=MAX, 5=MIN,...9=SUM etc.) to use in calculating subtotals within a list of data. You may check Subtotal reference in MS Excel help for further details.

 

The format actually states as:

subtotalN:Ref where Ref refers to the group by column(s).

E.g.,

 

(i).  &=Products.Units(subtotal9:Products.ProductID) specifies summary function upon Units field with respect to ProductID field in the Products table.

 

(ii).  &=Tabx.Col3(subtotal9:Tabx.Col1) specifies summary function upon Col3 field group by Col1  in the table Tabx.

 

(iii). &=Table1.ColumnD(subtotal9:Table1.ColumnA&Table1.ColumnB) specifies summary function upon ColumnD field group by ColumnA and ColumnB in the table Table1.

 

 

Example

 

We utilize some of the grouping parameters in an example. In the example we make use of Northwind.mdb MS Access database and extract data from its table named "Order Details". We create a designer file SmartMarker_Designer.xls in MS Excel and input smart markers into different cells of its worksheets. We process the markers to fill data into the worksheets. The data is placed and organized by a group field.

 

The designer file has two worksheets. In the first worksheet, we put some smart markers with grouping parameters as shown in the figure below. We place three smart markers (having grouping parameters) i.e. &=[Order Details].OrderID(group:merge,skip:1), &=[Order Details].Quantity(subtotal9:Order Details.OrderID), &=[Order Details].UnitPrice(subtotal9:Order Details.OrderID) into A5, B5 and C5 cells respectively.

 

 

Figure: First Worksheet of SmartMarker_Designer.xls file Containing the Smart Markers

 

 

In the second worksheet of the designer file, we put some more smart markers as shown in the figure below. We place five smart markers i.e. &=[Order Details].OrderID(group:normal), &=[Order Details].Quantity, &=[Order Details].UnitPrice, &=&=B{r}*C{r}, &=subtotal9:Order Details.OrderID              into A5, B5, C5, D5 and C6 cells respectively.

 

 

Figure: Second Worksheet of SmartMarker_Designer.xls file Containing Mixed Smart Markers

 

 

Source Code:

 

Here is the source code utilized in the example.

 

 

[C#]

 

//Create a connection object, specify the provider info and set the data source.

OleDbConnection con = new OleDbConnection("provider=microsoft.jet.oledb.4.0;data source=d:\\test\\Northwind.mdb");

//Open the connection object.

con.Open();

//Create a command object and specify the SQL query.

OleDbCommand cmd = new OleDbCommand("Select * from [Order Details]",con);

//Create a data adapter object.

OleDbDataAdapter da = new OleDbDataAdapter();

//Specify the command.

da.SelectCommand = cmd;

//Create a dataset object.

DataSet ds = new DataSet();

//Fill the dataset with the table records.

da.Fill(ds,"Order Details");

//Create a datatable with respect to dataset table.

DataTable dt = ds.Tables["Order Details"];

//Create WorkbookDesigner object.

WorkbookDesigner wd = new  WorkbookDesigner();

//Open the template file (which contains smart markers).

wd.Open("D:\\test\\SmartMarker_Designer.xls");

//Set the datatable as the data source.

wd.SetDataSource(dt);

//Process the smart markers to fill the data into the worksheets.

wd.Process(true);

//Save the excel file.

wd.Save("D:\\test\\outSmartMarker_Designer.xls");

 

 

 

[VB]

 

'Create a connection object, specify the provider info and set the data source.

Dim con As OleDbConnection = New OleDbConnection("provider=microsoft.jet.oledb.4.0;data source=d:\test\Northwind.mdb")

'Open the connection object.

con.Open()

'Create a command object and specify the SQL query.

Dim cmd As OleDbCommand = New OleDbCommand("Select * from [Order Details]",con)

'Create a data adapter object.

Dim da As OleDbDataAdapter = New OleDbDataAdapter()

'Specify the command.

da.SelectCommand = cmd

'Create a dataset object.

Dim ds As DataSet = New DataSet()

'Fill the dataset with the table records.

da.Fill(ds,"Order Details")

'Create a datatable with respect to dataset table.

Dim dt As DataTable = ds.Tables("Order Details")

'Create WorkbookDesigner object.

Dim wd As WorkbookDesigner = New WorkbookDesigner()

'Open the template file (which contains smart markers).

wd.Open("D:\test\SmartMarker_Designer.xls")

'Set the datatable as the data source.

wd.SetDataSource(dt)

'Process the smart markers to fill the data into the worksheets.

wd.Process(True)

'Save the excel file.

wd.Save("D:\test\outSmartMarker_Designer.xls")

 

 

 

After running the above code, the first sheet of the excel file is filled with data as shown below. You may see an extra summary row is added for the field(s) in each group set.

 

 

Figure: First Worksheet of the Output File is Filled with Data after Processing the Smart Markers

 

 

The second worksheet of the excel file is filled with data as shown in the figure below.

 

 

Figure: Second Worksheet of the Output File is Filled with Data after Processing the Smart Markers