Smart markers are used to let Aspose.Cells know what information to place in an 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 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.

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. 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 shift parameter works the same way as in Microsoft Excel. For example in MS 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.

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 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.

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

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.

//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=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("SmartMarkerDesigner.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("outSmartMarker_Designer.xls");

the resulted output will be:

Download

• Sample Code

Using Anonymous Types or Custom Objects

Aspose.Cells also supports anonymous types or custom objects in smart markers. In below example Product class is need to be defined before use.

//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("Smart Marker Customobjects.xls");

Download

• Sample Code

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

//Get the image data.
            byte[] imageData = File.ReadAllBytes("Thumbnail.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("Desert.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("ImageSmartBook.xls");
            //Set the datasource.
            designer.SetDataSource(t);
            //Process the markers.
            designer.Process();
            //Save the Excel file.
            designer.Workbook.Save("out_ImageSmartBook.xls");

Download

• Sample Code

Using Nested Objects

Aspose.Cells supports nested objects in smart markers, the nested objects should be simple.Individual class need to be defined by user for below code.

We use a simple template file. See the designer spreadsheet that contains some nested smart markers.

 //Initialize WorkbookDesigner object
            WorkbookDesigner designer = new WorkbookDesigner();
            //Load the template file
            designer.Workbook = new Workbook("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("out_SM_NestedObjects.xlsx");

Resulted Output

Download

• Sample Code