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 source, 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 all of 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 use one data marker per cell. Unused smart markers are 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.

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

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. E.g &=Table1.Field3(ascending:1).
• 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, creating extra rows or columns to fit data.

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 commas, but no space: parameterA,parameterB,parameterC

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

becomes…

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 - Offset to current row number.

The following screenshots illustrate 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. What follows are two code snippets, one in C# and one in BB, that shows how it is done.

//Instantiating a WorkbookDesigner object
WorkbookDesigner designer = new WorkbookDesigner();

//Open a designer spreadsheet containing smart markers
designer.Workbook = new Workbook(designerFile);

//Set the data source for the designer spreadsheet
designer.SetDataSource(dataset);

//Process the smart markers
designer.Process();
 

'Instantiating a WorkbookDesigner object
Dim designer As WorkbookDesigner = New WorkbookDesigner()

'Open a designer spreadsheet containing smart markers
designer.Workbook = New Workbook(designerFile)

'Set the data source for the designer spreadsheet
designer.SetDataSource(DataSet)

'Process the smart markers
designer.Process()
 

In some Excel reports you might need to break the data into groups to make it easier to read and 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 field(s) and place summary rows in between data sets or data groups. For example, if grouping data by Customers.CustomerID, you can add a summary record every time the group changes.

The example code snippets that follow shows how to group data in an Excel report using smart markers.

Parameters

Following are some of the smart marker parameters used for grouping data.

group:normal/merge/repeat

We support three types of group that you can choose between.

• normal - The group by field(s) value is not be repeated for the corresponding records in the column; instead they are printed once per data group.
• merge - The same behavior as for the normal parameter, except that it merges the cells in the group by field(s) for each group set.
• repeat - The group by field(s) value is repeated for the corresponding records.

For example: &=Customers.CustomerID(group:merge)

skip

Skips a specified number of row after each group.

For example, &=Employees.EmployeeID(group:normal,skip:1)

subtotalN*

Performs a summary operation for a specified field data related to a group by field. The N represents numbers between 1 and 11 which specify the function used when calculating subtotals within a list of data. (1=AVERAGE, 2=COUNT, 3=COUNTA, 4=MAX, 5=MIN,...9=SUM etc.) Refer to the Subtotal reference in Microsoft Excel's help for further details.

The format actually states as:
subtotalN:Ref where Ref refers to the group by column.

For example,

• &=Products.Units(subtotal9:Products.ProductID) specifies summary function upon Units field with respect to ProductID field in the Products table.
• &=Tabx.Col3(subtotal9:Tabx.Col1) specifies summary function upon Col3 field group by Col1  in the table Tabx.
• &=Table1.ColumnD(subtotal9:Table1.ColumnA&Table1.ColumnB) specifies summary function upon ColumnD field group by ColumnA and ColumnB in the table Table1.

Example

This example shows some of the grouping parameters in action. It uses the Northwind.mdb Microsoft Access database and extract data from the table named "Order Details". We create a designer file called SmartMarker_Designer.xls in Microsoft Excel and put smart markers into various cells in worksheets. The markers are processed to fill the worksheets. The data is placed and organized by a group field.

The designer file has two worksheets. In the first we put smart markers with grouping parameters as shown in the screenshot below. Three smart markers (with grouping parameters) are placed:
&=[Order Details].OrderID(group:merge,skip:1),
&=[Order Details].Quantity(subtotal9:Order Details.OrderID), and
&=[Order Details].UnitPrice(subtotal9:Order Details.OrderID) go into A5, B5 and C5 respectively.

Figure: The first worksheet in the SmartMarker_Designer.xls file, complete with smart markers

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

Figure: The second worksheet of the SmartMarker_Designer.xls file, showing mixed smart markers.

Source Code

Here is the source code used in the example.

//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.Workbook = new Workbook("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.Workbook.Save("D:\\test\\outSmartMarker_Designer.xls");
 

'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.Workbook = New Workbook("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.Workbook.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: The 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: The second worksheet of the output file is filled with data after processing the smart markers.

Aspose.Cells also supports anonymous types or custom objects in smart markers. The example that follows shows how this works.

Example Code

     //Definition of Custom class.
     public class MyProduct
    {
        private string m_Name;

        public string Name
        {
            get { return m_Name; }
            set { m_Name = value; }
        }
        private int m_Age;

        public int Age
        {
            get { return m_Age; }
            set { m_Age = value; }
        }
        internal MyProduct(string name, int age)
        {
            this.m_Name = name;
            this.m_Age = age;
        }

    }


               // ****** Program ******

            //Instantiate the workbookdesigner object.
            WorkbookDesigner report = new WorkbookDesigner();
            //Get the first worksheet(default sheet) in the workbook.
            Aspose.Cells.Worksheet w = report.Workbook.Worksheets[0];

            //Input some markers to the cells.
            w.Cells["A1"].PutValue("Test");
            w.Cells["A2"].PutValue("&=MyProduct.Name");
            w.Cells["B2"].PutValue("&=MyProduct.Age");

            //Instantiate the list collection based on the custom class.
            IList<MyProduct> list = new List<MyProduct>();
            //Provide values for the markers using the custom class object.
            list.Add(new MyProduct("Simon", 30));
            list.Add(new MyProduct("Johnson", 33));

            //Set the data source.
            report.SetDataSource("MyProduct", list);

            //Process the markers.
            report.Process(false);

            //Save the excel file.
            report.Workbook.Save("f:\\test\\out_customobjects.xls");
 

	 'Definition of Custom class.
	 Public Class MyProduct
		Private m_Name As String

		Public Property Name() As String
			Get
				Return m_Name
			End Get
			Set(ByVal value As String)
				m_Name = value
			End Set
		End Property
		Private m_Age As Integer

		Public Property Age() As Integer
			Get
				Return m_Age
			End Get
			Set(ByVal value As Integer)
				m_Age = value
			End Set
		End Property
		Friend Sub New(ByVal name As String, ByVal age As Integer)
			Me.m_Name = name
			Me.m_Age = age
		End Sub

	 End Class



                        ' ****** Program ******

			'Instantiate the workbookdesigner object.
			Dim report As New WorkbookDesigner()
			'Get the first worksheet(default sheet) in the workbook.
			Dim w As Aspose.Cells.Worksheet = report.Workbook.Worksheets(0)

			'Input some markers to the cells.
			w.Cells("A1").PutValue("Test")
			w.Cells("A2").PutValue("&=MyProduct.Name")
			w.Cells("B2").PutValue("&=MyProduct.Age")

			'Instantiate the list collection based on the custom class.
			Dim list As IList(Of MyProduct) = New List(Of MyProduct)()
			'Provide values for the markers using the custom class object.
			list.Add(New MyProduct("Simon", 30))
			list.Add(New MyProduct("Johnson", 33))

			'Set the data source.
			report.SetDataSource("MyProduct", list)

			'Process the markers.
			report.Process(False)

			'Save the excel file.
			report.Workbook.Save("f:\test\out_customobjects.xls")
 

Aspose.Cells smart markers support image markers too. This section shows you how to insert pictures using smart markers.

Image Parameters

Smart marker parameters for managing images.

• Picture:FitToCell - Auto-fit the image to the cell’s row height and column width.
• Picture:ScaleN - Scale height and width to N percent.
• Picture:Width:Nin&Height:Nin - Render the image N inches high and N inches wide. You can also sepecify Left and Top positions (in points).
  

Example Code

The designer file contains one worksheet into which we've added smart markers using some of the image parameters mentioned above.

Figure: The first worksheet of the SmartMarker_Designer2_.xls file showing image smart markers.

Here is the source code used in the example.

//Get the image data.
byte[] imageData = File.ReadAllBytes(@"c:\pic1.jpg");
//Create a datatable.
DataTable t = new DataTable("Table1");
//Add a column to save pictures.
DataColumn dc = t.Columns.Add("Picture");
//Set its data type.
dc.DataType = typeof(object);

//Add a new new record to it.
DataRow row = t.NewRow();
row[0] = imageData;
t.Rows.Add(row);

//Add another record (having picture) to it.
imageData = File.ReadAllBytes(@"e:\test\pic2.jpg");
row = t.NewRow();
row[0] = imageData;
t.Rows.Add(row);

//Create WorkbookDesigner object.
WorkbookDesigner designer = new WorkbookDesigner();
//Open the temple Excel file.
designer.Workbook = new Workbook(@"e:\test\SmartMarker_Designer2.xls");
//Set the datasource.
designer.SetDataSource(t);
//Process the markers.
designer.Process();
//Save the Excel file.
designer.Workbook.Save(@"e:\test\out_SmartBook.xls");
 

'Get the image data.
Dim imageData() As Byte = File.ReadAllBytes("c:\pic1.jpg")
'Create a datatable.
Dim t As New DataTable("Table1")
'Add a column to save pictures.
Dim dc As DataColumn = t.Columns.Add("Picture")
'Set its data type.
dc.DataType = GetType(Object)

'Add a new new record to it.
Dim row As DataRow = t.NewRow()
row(0) = imageData
t.Rows.Add(row)

'Add another record (having picture) to it.
imageData = File.ReadAllBytes("e:\test\pic2.jpg")
row = t.NewRow()
row(0) = imageData
t.Rows.Add(row)

'Create WorkbookDesigner object.
Dim designer As New WorkbookDesigner()
'Open the temple Excel file.
designer.Workbook = New Workbook("e:\test\SmartMarker_Designer2.xls")
'Set the datasource.
designer.SetDataSource(t)
'Process the markers.
designer.Process()
'Save the Excel file.
designer.Workbook.Save("e:\test\out_SmartBook.xls")