Smartly importing and placing data with Smart markers

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 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 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 is laid out. They are appended to the end of the 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.
  • 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 and 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 commas, 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 - Offset to 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 B and C columns respectively which will be set for division operation, the skip parameter is one row. Moreover, we should specify the following char:

 "~"

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. Following example code shows on how to use dynamic formulas in Smart Markers. We load the template file and create test data, process the markers to fill data into the cells against the marker.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Create directory if it is not already present.
bool IsExists = System.IO.Directory.Exists(dataDir);
if (!IsExists)
System.IO.Directory.CreateDirectory(dataDir);
//set the file path of designer spreadsheet containing smart markers
string designerFile = "templateDynamicFormulas.xlsx";
//create your data set
DataSet dataset = new DataSet();
DataTable dt = new DataTable("Answer");
dataset.Tables.Add(dt);
DataColumn price = new DataColumn("Price", typeof(double));
DataColumn amount = new DataColumn("Amount", typeof(int));
dt.Columns.Add(price);
dt.Columns.Add(amount);
dt.Rows.Add(100.00, 2);
dt.Rows.Add(75.25, 3);
dt.Rows.Add(25.00, 5);
if (designerFile != null)
{
// 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();
}

Using Variable Arrays

Following example code shows on how to use variable arrays in Smart Markers. We place a variable array marker into A1 cell of the first worksheet of the workbook dynamically which contains string of values which we set for the marker, process the markers to fill data into the cells against the marker. Finally we save the Excel file.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Instantiate a new Workbook designer.
WorkbookDesigner report = new WorkbookDesigner();
// Get the first worksheet of the workbook.
Worksheet w = report.Workbook.Worksheets[0];
// Set the Variable Array marker to a cell.
// You may also place this Smart Marker into a template file manually in Ms Excel and then open this file via Workbook.
w.Cells["A1"].PutValue("&=$VariableArray");
// Set the DataSource for the marker(s).
report.SetDataSource("VariableArray", new string[] { "English", "Arabic", "Hindi", "Urdu", "French" });
// Process the markers.
report.Process(false);
// Save the Excel file.
report.Workbook.Save(dataDir + "output.xlsx");

Grouping Data

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 data by fields 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.

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 rows 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 the ProductID field in the Products table.
  • &=Tabx.Col3(subtotal9:Tabx.Col1) specifies summary function upon the 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.

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.

The first worksheet in the SmartMarker_Designer.xls file, complete with smart markers
todo:image_alt_text
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.
The second worksheet of the SmartMarker_Designer.xls file, showing mixed smart markers.
todo:image_alt_text
Here is the source code used in the example.
private void SmartMarkers_GroupingData()
{
//Examples-CSharp-SmartMarkers-GroupingData-1.cs
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// 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=" + dataDir + "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(dataDir + "Designer.xlsx");
// 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(dataDir + "output.xlsx");
}
class OleDbCommand
{
private string p;
private OleDbConnection con;
public OleDbCommand(string p, OleDbConnection con)
{
// TODO: Complete member initialization
this.p = p;
this.con = con;
}
}
class OleDbConnection
{
private string p;
public OleDbConnection(string p)
{
// TODO: Complete member initialization
this.p = p;
}
internal void Open()
{
}
}
class OleDbDataAdapter
{
public OleDbCommand SelectCommand { get; set; }
internal void Fill(System.Data.DataSet ds, string p)
{
}
}

Using Anonymous Types or Custom Objects

Aspose.Cells also supports anonymous types or custom objects in smart markers. The example that follows shows how this works.For importing data from dynamic objects using Smart Markers, visit the following article:

Importing from dynamic object as data source

private void SmartMarkers_UsingAnonymousTypes()
{
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Instantiate the workbookdesigner object.
WorkbookDesigner report = new WorkbookDesigner();
// Get the first worksheet(default sheet) in the workbook.
Aspose.Cells.Worksheet sheet = report.Workbook.Worksheets[0];
// Input some markers to the cells.
sheet.Cells["A1"].PutValue("Name");
sheet.Cells["B1"].PutValue("Age");
sheet.Cells["A2"].PutValue("&=MyProduct.Name");
sheet.Cells["B2"].PutValue("&=MyProduct.Age");
// Instantiate the list collection based on the custom class.
IList<Person> list = new List<Person>();
// Provide values for the markers using the custom class object.
list.Add(new Person("Simon", 30));
list.Add(new Person("Johnson", 33));
// Set the data source.
report.SetDataSource("MyProduct", list);
// Process the markers.
report.Process(false);
// Save the excel file.
report.Workbook.Save(dataDir + "Smart Marker Customobjects.xls");
}
// Definition of Custom class.
public class Person
{
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 Person(string name, int age)
{
this.m_Name = name;
this.m_Age = age;
}
}

Image Markers

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 specify Left and Top positions (in points).

Here is the source code used in the example.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Get the image data.
byte[] imageData = File.ReadAllBytes(dataDir+ "aspose-logo.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(dataDir+ "image2.jpg");
row = t.NewRow();
row[0] = imageData;
t.Rows.Add(row);
// Create WorkbookDesigner object.
WorkbookDesigner designer = new WorkbookDesigner();
// Open the template Excel file.
designer.Workbook = new Workbook(dataDir+ "TestSmartMarkers.xlsx");
// Set the datasource.
designer.SetDataSource(t);
// Process the markers.
designer.Process();
// Save the Excel file.
designer.Workbook.Save(dataDir+ "output.xls");

Using Nested Objects

Aspose.Cells supports nested objects in smart markers, the nested objects should be simple. We use a simple template file. See the designer spreadsheet that contains some nested smart markers.

The first worksheet of the SM_NestedObjects.xlsx file showing nested smart markers.
todo:image_alt_text
The example that follows shows how this works.
private void SmartMarkers_UsingNestedObjects()
{
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// ****** Program ******
// Initialize WorkbookDesigner object
WorkbookDesigner designer = new WorkbookDesigner();
// Load the template file
designer.Workbook = new Workbook(dataDir + "SM_NestedObjects.xlsx");
// Instantiate the List based on the class
System.Collections.Generic.ICollection<Individual> list = new System.Collections.Generic.List<Individual>();
// Create an object for the Individual class
Individual p1 = new Individual("Damian", 30);
// Create the relevant Wife class for the Individual
p1.Wife = new Wife("Dalya", 28);
// Create another object for the Individual class
Individual p2 = new Individual("Mack", 31);
// Create the relevant Wife class for the Individual
p2.Wife = new Wife("Maaria", 29);
// Add the objects to the list
list.Add(p1);
list.Add(p2);
// Specify the DataSource
designer.SetDataSource("Individual", list);
// Process the markers
designer.Process(false);
// Save the Excel file.
designer.Workbook.Save(dataDir + "output.xlsx");
}
class Individual
{
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 Individual(string name, int age)
{
this.Name = name;
this.Age = age;
}
private Wife m_Wife;
public Wife Wife
{
get { return m_Wife; }
set { m_Wife = value; }
}
}
public class Wife
{
public Wife(string name, int age)
{
this.m_name = name;
this.m_age = age;
}
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; }
}
}

Using Generic List as Nested Object

Aspose.Cells now also supports using generic list as a nested object. Please check the screenshot of the output excel file generated with the following code. As you can see in the screenshot a Teacher object contains multiple nested Student objects.

todo:image_alt_text
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
Workbook workbook = new Workbook();
// Create a designer workbook
// Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];
worksheet.Cells["A1"].PutValue("Teacher Name");
worksheet.Cells["A2"].PutValue("&=Teacher.Name");
worksheet.Cells["B1"].PutValue("Teacher Age");
worksheet.Cells["B2"].PutValue("&=Teacher.Age");
worksheet.Cells["C1"].PutValue("Student Name");
worksheet.Cells["C2"].PutValue("&=Teacher.Students.Name");
worksheet.Cells["D1"].PutValue("Student Age");
worksheet.Cells["D2"].PutValue("&=Teacher.Students.Age");
// Apply Style to A1:D1
Range range = worksheet.Cells.CreateRange("A1:D1");
Style style = workbook.CreateStyle();
style.Font.IsBold = true;
style.ForegroundColor = Color.Yellow;
style.Pattern = BackgroundType.Solid;
StyleFlag flag = new StyleFlag();
flag.All = true;
range.ApplyStyle(style, flag);
// Initialize WorkbookDesigner object
WorkbookDesigner designer = new WorkbookDesigner();
// Load the template file
designer.Workbook = workbook;
System.Collections.Generic.List<Teacher> list = new System.Collections.Generic.List<Teacher>();
// Create an object for the Teacher class
Teacher h1 = new Teacher("Mark John", 30);
// Create the relevant student objects for the Teacher object
h1.Students = new List<Person>();
h1.Students.Add(new Person("Chen Zhao", 14));
h1.Students.Add(new Person("Jamima Winfrey", 18));
h1.Students.Add(new Person("Reham Smith", 15));
// Create another object for the Teacher class
Teacher h2 = new Teacher("Masood Shankar", 40);
// Create the relevant student objects for the Teacher object
h2.Students = new List<Person>();
h2.Students.Add(new Person("Karishma Jathool", 16));
h2.Students.Add(new Person("Angela Rose", 13));
h2.Students.Add(new Person("Hina Khanna", 15));
// Add the objects to the list
list.Add(h1);
list.Add(h2);
// Specify the DataSource
designer.SetDataSource("Teacher", list);
// Process the markers
designer.Process();
// Autofit columns
worksheet.AutoFitColumns();
// Save the Excel file.
designer.Workbook.Save(dataDir + "output.xlsx");

Using HTML property of Smart Markers

The following sample code explains the use of HTML property of the Smart Markers. When it will be processed, it will show “World” in “Hello World” as bold because of HTML tag.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
Workbook workbook = new Workbook();
WorkbookDesigner designer = new WorkbookDesigner();
designer.Workbook = workbook;
workbook.Worksheets[0].Cells["A1"].PutValue("&=$VariableArray(HTML)");
designer.SetDataSource("VariableArray", new String[] { "Hello <b>World</b>", "Arabic", "Hindi", "Urdu", "French" });
designer.Process();
workbook.Save(dataDir + "output.xls");

Not line by line

The current default processing method is to process smartmaker line by line. But sometimes the smart markers of the same data table needs to be processed together, no matter if they are in the same row or not, then you have to specify a named range “_CellsSmartMarkers” and specify WorkbookDesigner.LineByLine as false before calling the processing.

|todo:image_alt_text|

string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
Workbook workbook = new Workbook();
Style style = workbook.CreateStyle();
style.Pattern = BackgroundType.Solid;
style.ForegroundColor = Color.Black;
style.Font.Color = Color.White;
// Create a designer workbook
// Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];
worksheet.Cells["A1"].PutValue("Teacher Name");
worksheet.Cells["A1"].SetStyle(style);
worksheet.Cells["A2"].PutValue("&=Teacher.Name");
worksheet.Cells["B1"].PutValue("Teacher Age");
worksheet.Cells["B1"].SetStyle(style);
worksheet.Cells["B2"].PutValue("&=Teacher.Age");
worksheet.Cells["A3"].PutValue("Student Name");
worksheet.Cells["A3"].SetStyle(style);
worksheet.Cells["A4"].PutValue("&=Teacher.Students.Name");
worksheet.Cells["B3"].PutValue("Student Age");
worksheet.Cells["B3"].SetStyle(style);
worksheet.Cells["B4"].PutValue("&=Teacher.Students.Age");
worksheet.AutoFitColumns();
//A named range "_CellsSmartMarkers" must be added for checking which range contains all smart markers about a table.
worksheet.Cells.CreateRange("A1:B4").Name = "_CellsSmartMarkers";
// Initialize WorkbookDesigner object
WorkbookDesigner designer = new WorkbookDesigner();
// Load the template file
designer.Workbook = workbook;
System.Collections.Generic.List<Teacher> list = new System.Collections.Generic.List<Teacher>();
// Create an object for the Teacher class
Teacher h1 = new Teacher("Mark John", 30);
// Create the relevant student objects for the Teacher object
h1.Students = new List<Person>();
h1.Students.Add(new Person("Chen Zhao", 14));
h1.Students.Add(new Person("Jamima Winfrey", 18));
h1.Students.Add(new Person("Reham Smith", 15));
// Create another object for the Teacher class
Teacher h2 = new Teacher("Masood Shankar", 40);
// Create the relevant student objects for the Teacher object
h2.Students = new List<Person>();
h2.Students.Add(new Person("Karishma Jathool", 16));
h2.Students.Add(new Person("Angela Rose", 13));
h2.Students.Add(new Person("Hina Khanna", 15));
// Add the objects to the list
list.Add(h1);
list.Add(h2);
// Specify the DataSource
designer.SetDataSource("Teacher", list);
designer.LineByLine = false;
// Process the markers
designer.Process();
// Autofit columns
worksheet.AutoFitColumns();
// Save the Excel file.
designer.Workbook.Save(dataDir + "output.xlsx");
public class Person
{
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 Person(string name, int age)
{
this.m_Name = name;
this.m_Age = age;
}
}
public class Teacher
{
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; }
}
private List<Person> mStudents;
public List<Person> Students
{
get { return mStudents; }
set { mStudents = value; }
}
public Teacher(string name, int age)
{
this.Name = name;
this.Age = age;
}
}

Getting Notifications while Merging Data with Smart Markers

Sometimes, it may be required to get the notifications about the cell reference or the particular Smart Marker being processed before the completion. This can be achieved using the WorkbookDesigner.CallBack property and ISmartMarkerCallBack

Advance topics