Interface ITemplateMarkersProcessor

Represents the processing of a special marker symbol, that appends multiple records from data source into a worksheet.

Namespace: Syncfusion.XlsIO

Assembly: Syncfusion.XlsIO.NET.dll

Syntax

public interface ITemplateMarkersProcessor

Examples

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");

ITemplateMarkersProcessor marker = workbook.CreateTemplateMarkersProcessor();
System.Data.DataTable reports = new System.Data.DataTable();
reports.Columns.Add("SalesPerson");
reports.Columns.Add("FromDate", typeof(DateTime));
reports.Columns.Add("ToDate", typeof(DateTime));
reports.Rows.Add("Andy Bernard", new DateTime(2014, 09, 08), new DateTime(2014, 09, 11));
reports.Rows.Add("Jim Halpert", new DateTime(2014, 09, 11), new DateTime(2014, 09, 15));
reports.Rows.Add("Karen Fillippelli", new DateTime(2014, 09, 15), new DateTime(2014, 09, 20));
reports.Rows.Add("Phyllis Lapin", new DateTime(2014, 09, 21), new DateTime(2014, 09, 25));
reports.Rows.Add("Stanley Hudson", new DateTime(2014, 09, 26), new DateTime(2014, 09, 30));
marker.AddVariable("Reports", reports);
marker.ApplyMarkers();

Properties

ArgumentSeparator

Gets or sets the arguments separator. Default argument separator is ';'.

Declaration

char ArgumentSeparator { get; set; }

Property Value

Type
System.Char

Remarks

To know more about template marker variable syntax refer this link.

Examples

The default argument separator for template marker is ';'. We can change this by using ArgumentSeparator Property. Here for example, we change the default separator to ':' and used this separator in our template marker variables. To know about customizing marker prefix refer MarkerPrefix property.

        using (ExcelEngine excelEngine = new ExcelEngine())
        {
            //Create a worksheet.
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(1);
            IWorksheet worksheet = workbook.Worksheets[0];

            //Data to be used for Template Marker.
            string[] names = new string[] { "SalesPerson", "SalesJanJun", "SalesJulDec", "Change" };

            //Add marker variables in the worksheet.
            worksheet["A1"].Text = "%Names:horizontal";
            worksheet["A2"].Text = "%Reports.SalesPerson:vertical";
            worksheet["B2"].Text = "%Reports.SalesJanJun:vertical";
            worksheet["C2"].Text = "%Reports.SalesJulDec:vertical";
            worksheet["D2"].Text = "%Reports.Change:vertical";

            //Create a Template Marker Instance.
            ITemplateMarkersProcessor marker = workbook.CreateTemplateMarkersProcessor();

            //Change the default separator to ':'.
            marker.ArgumentSeparator = ':';

            //Add variable names from the binding context to Template Marker.
            marker.AddVariable("Names", names);
            marker.AddVariable("Reports", GetDataTable());

            //Apply Marker.
            marker.ApplyMarkers();

            //Save and Dispose.
            workbook.SaveAs("TemplateMarker.xlsx");
            workbook.Close();
        }

    //GetDataTable method
    System.Data.DataTable GetDataTable()
    {
        System.Data.DataTable reports = new System.Data.DataTable();
        reports.Columns.Add("SalesPerson");
        reports.Columns.Add("SalesJanJun", typeof(int));
        reports.Columns.Add("SalesJulDec", typeof(int));
        reports.Columns.Add("Change", typeof(int));
        reports.Rows.Add("Andy Bernard", 45000, 58000, 29);
        reports.Rows.Add("Jim Halpert", 34000, 65000, 91);
        reports.Rows.Add("Karen Fillippelli", 75000, 64000, 15);
        reports.Rows.Add("Phyllis Lapin", 56500, 33600, 40);
        reports.Rows.Add("Stanley Hudson", 46500, 52000, 12);
        return reports;
    }

MarkerPrefix

Gets or sets the marker prefix. String indicates that the cells contains marker.

Declaration

string MarkerPrefix { get; set; }

Property Value

Type
System.String

Remarks

To know more about template marker variable syntax refer this link.

Examples

The default template marker prefix is "%". This can be changed by using MarkerPrefix property. Here for example, we change the marker prefix to "*" and use this prefix in our template marker variables. To know about customizing the argument separator refer ArgumentSeparator Property.

        using (ExcelEngine excelEngine = new ExcelEngine())
        {
            //Create a worksheet.
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(1);
            IWorksheet worksheet = workbook.Worksheets[0];

            //Data to be used for Template Marker.
            string[] names = new string[] { "SalesPerson", "SalesJanJun", "SalesJulDec", "Change" };

            //Add marker variables in the worksheet.
            worksheet["A1"].Text = "*Names;horizontal";
            worksheet["A2"].Text = "*Reports.SalesPerson;vertical";
            worksheet["B2"].Text = "*Reports.SalesJanJun;vertical";
            worksheet["C2"].Text = "*Reports.SalesJulDec;vertical";
            worksheet["D2"].Text = "*Reports.Change;vertical";

            //Create a Template Marker Instance.
            ITemplateMarkersProcessor marker = workbook.CreateTemplateMarkersProcessor();

            //Change the default prefix to "*".
            marker.MarkerPrefix = "*";

            //Add variable names from the binding context to Template Marker.
            marker.AddVariable("Names", names);
            marker.AddVariable("Reports", GetDataTable());

            //Apply Marker.
            marker.ApplyMarkers();

            //Save and Dispose.
            workbook.SaveAs("TemplateMarker.xlsx");
            workbook.Close();
        }

    //GetDataTable method
    System.Data.DataTable GetDataTable()
    {
        System.Data.DataTable reports = new System.Data.DataTable();
        reports.Columns.Add("SalesPerson");
        reports.Columns.Add("SalesJanJun", typeof(int));
        reports.Columns.Add("SalesJulDec", typeof(int));
        reports.Columns.Add("Change", typeof(int));
        reports.Rows.Add("Andy Bernard", 45000, 58000, 29);
        reports.Rows.Add("Jim Halpert", 34000, 65000, 91);
        reports.Rows.Add("Karen Fillippelli", 75000, 64000, 15);
        reports.Rows.Add("Phyllis Lapin", 56500, 33600, 40);
        reports.Rows.Add("Stanley Hudson", 46500, 52000, 12);
        return reports;
    }

Methods

AddVariable(String, Object)

Adds a variable to the collection with the specified variable name and value.

Declaration

void AddVariable(string strName, object variable)

Parameters

Type	Name	Description
System.String	strName	Name of the variable.
System.Object	variable	Value of the variable.

Remarks

To know more about Template Markers refer this link

Examples

To load the marker from arrays we can use the marker name and source array name in the AddVariable(String, Object) method. For example, here we load data from names array to the marker variable %Names. Other than arrays, we can import data from System.Data.DataTable. Refer this link to know how to import data from System.Data.DataTable using template markers.

        using (ExcelEngine excelEngine = new ExcelEngine())
        {    
            //Create a worksheet.
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(1);
            IWorksheet worksheet = workbook.Worksheets[0];

            //Data to be used for Template Marker.
            string[] names = new string[] { "A", "B", "C", "D" };
            string[] numbers = new string[] { "1", "2", "3", "4" };

            //Add marker variables in the worksheet.
            worksheet["A1"].Text = "Names";
            worksheet["B1"].Text = "Numbers";
            worksheet["A2"].Text = "%Names";
            worksheet["B2"].Text = "%Numbers";

            //Create a Template Marker Instance.
            ITemplateMarkersProcessor marker = workbook.CreateTemplateMarkersProcessor();

            //Add variable names from the binding context to Template Marker.
            marker.AddVariable("Names", names);
            marker.AddVariable("Numbers", numbers);

            //Apply Marker.
            marker.ApplyMarkers();

            //Save and Dispose.
            workbook.SaveAs("TemplateMarker.xlsx");
            workbook.Close();
        }

AddVariable(String, Object, VariableTypeAction)

Adds a variable to the collection with the specified name, value and VariableTypeAction.

Declaration

void AddVariable(string strName, object variable, VariableTypeAction variableTypeAction)

Parameters

Type	Name	Description
System.String	strName	Name of the variable.
System.Object	variable	Value of the variable.
VariableTypeAction	variableTypeAction	Marker variable type.

Examples

Using VariableTypeAction we can enable template marker to detect the "Number Formats" and "Data Types" of objects and apply formatting depending on that. For example, Here we use FromDate and ToDate as objects of DateTime. Since we used DetectNumberFormat default date formatting will be applied for the data for those marker variables.

        using (ExcelEngine excelEngine = new ExcelEngine())
        {    
            //Create a worksheet.
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(1);
            IWorksheet worksheet = workbook.Worksheets[0];

            //Data to be used for Template Marker.
            string[] names = new string[] { "A", "B", "C", "D" };
            string[] numbers = new string[] { "1", "2", "3", "4" };

            //Add marker variables in the worksheet.
            worksheet["A1"].Text = "SalesPerson";
            worksheet["B1"].Text = "FromDate";
            worksheet["C1"].Text = "ToDate";
            worksheet["A2"].Text = "%Reports.SalesPerson";
            worksheet["B2"].Text = "%Reports.FromDate";
            worksheet["C2"].Text = "%Reports.ToDate";

            //Create a Template Marker Instance.
            ITemplateMarkersProcessor marker = workbook.CreateTemplateMarkersProcessor();

            //Create DataTable for import using Template Marker.
            System.Data.DataTable reports = new System.Data.DataTable();
            reports.Columns.Add("SalesPerson");
            reports.Columns.Add("FromDate", typeof(DateTime));
            reports.Columns.Add("ToDate", typeof(DateTime));
            reports.Rows.Add("Andy Bernard", new DateTime(2014, 09, 08), new DateTime(2014, 09, 11));
            reports.Rows.Add("Jim Halpert", new DateTime(2014, 09, 11), new DateTime(2014, 09, 15));
            reports.Rows.Add("Karen Fillippelli", new DateTime(2014, 09, 15), new DateTime(2014, 09, 20));
            reports.Rows.Add("Phyllis Lapin", new DateTime(2014, 09, 21), new DateTime(2014, 09, 25));
            reports.Rows.Add("Stanley Hudson", new DateTime(2014, 09, 26), new DateTime(2014, 09, 30));

            //Add variable name from the binding context to Template Marker.
            marker.AddVariable("Reports", reports, VariableTypeAction.DetectNumberFormat);

            //Apply Marker.
            marker.ApplyMarkers();

            //Save and Dispose.
            workbook.SaveAs("TemplateMarker.xlsx");    
            workbook.Close();
        }

ApplyMarkers()

Applies markers to the parent object.

Declaration

void ApplyMarkers()

Remarks

To ignore marker variables in document that are not used in template marker we can use Skip in the ApplyMarkers(UnknownVariableAction) method.

Examples

To apply the marker with default option we can use ApplyMarkers() method. The default option will be Exception. If any text with marker variable syntax appears in the workbook this operation will throw exception. For example, Here we use marker variables %Names and %Numbers. If we didn't add any one of these two variables in template marker and try to run the code, it will throw exception. This behaviour can be changed by giving the UnknownVariableAction as a parameter in ApplyMarkers(UnknownVariableAction).

        using (ExcelEngine excelEngine = new ExcelEngine())
        {
            //Create a worksheet.
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(1);
            IWorksheet worksheet = workbook.Worksheets[0];

            //Data to be used for Template Marker.
            string[] names = new string[] { "A", "B", "C", "D" };
            string[] numbers = new string[] { "1", "2", "3", "4" };

            //Add marker variables in the worksheet.
            worksheet["A1"].Text = "Names";
            worksheet["B1"].Text = "Numbers";
            worksheet["A2"].Text = "%Names";
            worksheet["B2"].Text = "%Numbers";

            //Create a Template Marker Instance.
            ITemplateMarkersProcessor marker = workbook.CreateTemplateMarkersProcessor();

            //Add variable names from the binding context to Template Marker.
            marker.AddVariable("Names", names);
            marker.AddVariable("Numbers", numbers);

            //Apply Marker.
            marker.ApplyMarkers();

            //Save and Dispose.
            workbook.SaveAs("TemplateMarker.xlsx");
            workbook.Close();
        }

ApplyMarkers(UnknownVariableAction)

Applies markers to the parent object with the specified UnknownVariableAction.

Declaration

void ApplyMarkers(UnknownVariableAction action)

Parameters

Type	Name	Description
UnknownVariableAction	action	Marker unknown variable.

Remarks

To preserve the document that has marker variables that are not added to template marker we use ApplyMarkers() method.

Examples

To avoid the marker throwing exception with default option of ApplyMarkers() method we can use Skip or ReplaceBlank. The "Skip" just ignores the marker variables that are not added in Template Marker and the "ReplaceBlank" can be used to clear the marker variable from the document.

        using (ExcelEngine excelEngine = new ExcelEngine())
        {    
            //Create a worksheet.        
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(1);
            IWorksheet worksheet = workbook.Worksheets[0];

            //Data to be used for Template Marker.
            string[] names = new string[] { "A", "B", "C", "D" };

            //Add marker variables in the worksheet.
            worksheet["A1"].Text = "Names";
            worksheet["B1"].Text = "Numbers";
            worksheet["A2"].Text = "%Names";
            worksheet["B2"].Text = "%Numbers";

            //Create a Template Marker Instance.
            ITemplateMarkersProcessor marker = workbook.CreateTemplateMarkersProcessor();

            //Add variable names from the binding context to Template Marker.
            marker.AddVariable("Names", names);

            //Apply Marker.
            marker.ApplyMarkers(UnknownVariableAction.Skip);

            //Save and Dispose.
            workbook.SaveAs("TemplateMarker.xlsx");
            workbook.Close();
        }

ContainsVariable(String)

Checks whether template markers object contains variable with the specified name.

Declaration

bool ContainsVariable(string strName)

Parameters

Type	Name	Description
System.String	strName	Name to locate.

Returns

Type	Description
System.Boolean	Indicates whether the markers contains the variable.

Examples

To check whether a marker variable is present in the marker object we can use this method. For example, here we add marker variable %Names then we check whether the marker object contains the marker variable. If marker has the variable then it returns "true".

        using (ExcelEngine excelEngine = new ExcelEngine())
        {    
            //Create a worksheet.
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(1);
            IWorksheet worksheet = workbook.Worksheets[0];

            //Data to be used for Template Marker.
            string[] names = new string[] { "A", "B", "C", "D" };
            string[] numbers = new string[] { "1", "2", "3", "4" };

            //Add marker variables in the worksheet.
            worksheet["A1"].Text = "Names";
            worksheet["B1"].Text = "Numbers";
            worksheet["A2"].Text = "%Names";
            worksheet["B2"].Text = "%Numbers";

            //Create a Template Marker Instance.
            ITemplateMarkersProcessor marker = workbook.CreateTemplateMarkersProcessor();

            //Add variable names from the binding context to Template Marker.
            marker.AddVariable("Names", names);

            //Check whether marker contains the variables.
            Console.WriteLine(marker.ContainsVariable("Names"));
            Console.WriteLine(marker.ContainsVariable("Numbers"));

            //Dispose.
            workbook.Close();
        }
//Output for the code will be
//True
//False

CreateConditionalFormats(IRange)

Adds a conditional format to the Template Marker.

Declaration

IConditionalFormats CreateConditionalFormats(IRange range)

Parameters

Type	Name	Description
IRange	range	Represents the range where the conditional format to be applied.

Returns

Type	Description
IConditionalFormats	Newly added conditional formats.

Remarks

To know more about using conditional formats with Template Markers refer this link. To get some additional information about conditional formats and applicable format types check this link.

Examples

Conditional Formats can be applied to the data imported using template marker. In order to do that we have to create conditonal formats within the template marker range. Here we import data from System.Data.DataTable and apply conditional format for each column. For this example the marker variables are in cells "A2", "B2" and "C2" so the conditional formats should use these ranges.

        using (ExcelEngine excelEngine = new ExcelEngine())
        {
            //Create a worksheet.
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(1);
            IWorksheet worksheet = workbook.Worksheets[0];

            //Add marker variables in the worksheet.
            worksheet["A1"].Text = "SalesPerson";         
            worksheet["B1"].Text = "SalesJanJun";
            worksheet["C1"].Text = "SalesJulDec";
            worksheet["D1"].Text = "Change";
            worksheet["A2"].Text = "%Reports.SalesPerson";
            worksheet["B2"].Text = "%Reports.SalesJanJun";
            worksheet["C2"].Text = "%Reports.SalesJulDec";
            worksheet["D2"].Text = "%Reports.Change";

            //Create Template Marker Processor
            ITemplateMarkersProcessor marker = workbook.CreateTemplateMarkersProcessor();

            //Create Conditional Formats within the Template Marker range.
            IConditionalFormats conditionalFormats = marker.CreateConditionalFormats(worksheet["B2"]);
            IConditionalFormat condition = conditionalFormats.AddCondition();


            //Set the format type
            condition.FormatType = ExcelCFType.DataBar;
            IDataBar dataBar = condition.DataBar;

            //Set the constraint
            dataBar.MinPoint.Type = ConditionValueType.LowestValue;
            dataBar.MinPoint.Value = "0";
            dataBar.MaxPoint.Type = ConditionValueType.HighestValue;
            dataBar.MaxPoint.Value = "0";

            //Set color for Bar
            dataBar.BarColor = System.Drawing.Color.FromArgb(156, 208, 243);
            //Hide the value in data bar
            dataBar.ShowValue = false;

            //Add IconSet as second conditional format for the existing databar.
            condition = conditionalFormats.AddCondition();
            condition.FormatType = ExcelCFType.IconSet;
            IIconSet iconSet = condition.IconSet;
            iconSet.IconSet = ExcelIconSetType.FourRating;
            iconSet.IconCriteria[0].Type = ConditionValueType.LowestValue;
            iconSet.IconCriteria[0].Value = "0";
            iconSet.IconCriteria[1].Type = ConditionValueType.HighestValue;
            iconSet.IconCriteria[1].Value = "0";
            iconSet.ShowIconOnly = true;

            //Create Conditional Formats within the Template Marker range.
            conditionalFormats = marker.CreateConditionalFormats(worksheet["C2"]);

            //Color Scale
            condition = conditionalFormats.AddCondition();
            condition.FormatType = ExcelCFType.ColorScale;
            IColorScale colorScale = condition.ColorScale;

            //Sets 3 - color scale.
            colorScale.SetConditionCount(3);
            colorScale.Criteria[0].FormatColorRGB = System.Drawing.Color.FromArgb(230, 197, 218);
            colorScale.Criteria[0].Type = ConditionValueType.LowestValue;
            colorScale.Criteria[0].Value = "0";
            colorScale.Criteria[1].FormatColorRGB = System.Drawing.Color.FromArgb(244, 210, 178);
            colorScale.Criteria[1].Type = ConditionValueType.Percentile;
            colorScale.Criteria[1].Value = "50";
            colorScale.Criteria[2].FormatColorRGB = System.Drawing.Color.FromArgb(245, 247, 171);
            colorScale.Criteria[2].Type = ConditionValueType.HighestValue;
            colorScale.Criteria[2].Value = "0";

            //Create Conditional Formats within the Template Marker range. 
            conditionalFormats = marker.CreateConditionalFormats(worksheet["D2"]);

            //Icon set
            condition = conditionalFormats.AddCondition();
            condition.FormatType = ExcelCFType.IconSet;
            iconSet = condition.IconSet;
            iconSet.IconSet = ExcelIconSetType.ThreeSymbols;
            iconSet.IconCriteria[0].Type = ConditionValueType.LowestValue;
            iconSet.IconCriteria[0].Value = "0";
            iconSet.IconCriteria[1].Type = ConditionValueType.HighestValue;
            iconSet.IconCriteria[1].Value = "0";
            iconSet.ShowIconOnly = false;

            //Add datatable from GetDataTable method as Template Marker variable "Reports".
            marker.AddVariable("Reports", GetDataTable());

            //Process the markers in the template.
            marker.ApplyMarkers();

            //Save and Dispose.
            workbook.SaveAs("TemplateMarker.xlsx");
            workbook.Close();
        }

    //GetDataTable method
    System.Data.DataTable GetDataTable()
    {
        System.Data.DataTable reports = new System.Data.DataTable();
        reports.Columns.Add("SalesPerson");
        reports.Columns.Add("SalesJanJun", typeof(int));
        reports.Columns.Add("SalesJulDec", typeof(int));
        reports.Columns.Add("Change", typeof(int));
        reports.Rows.Add("Andy Bernard", 45000, 58000, 29);
        reports.Rows.Add("Jim Halpert", 34000, 65000, 91);
        reports.Rows.Add("Karen Fillippelli", 75000, 64000, 15);
        reports.Rows.Add("Phyllis Lapin", 56500, 33600, 40);
        reports.Rows.Add("Stanley Hudson", 46500, 52000, 12);
        return reports;
    }

RemoveVariable(String)

Removes a variable with specified name from the collection.

Declaration

void RemoveVariable(string strName)

Parameters

Type	Name	Description
System.String	strName	Name of the variable.

Remarks

If we remove the marker variable from marker and keep the marker variable in document then using ApplyMarkers() will cause exception. Since the default argument is Exception. To avoid this we can use Skip or ReplaceBlank as arguments in ApplyMarkers(UnknownVariableAction) method.

Examples

The variables added in marker can be removed using RemoveVariable(String) method. Removing marker variable avoids loading data that is not required. Here for example, we add marker variables "%Names" and "%Numbers", then we remove "%Numbers" from the marker object. So the ApplyMarkers(UnknownVariableAction) method will import data for the marker "%Names" only and it simply ignores "%Numbers".

        using (ExcelEngine excelEngine = new ExcelEngine())
        {
            //Create a worksheet.
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(1);
            IWorksheet worksheet = workbook.Worksheets[0];

            //Data to be used for Template Marker.
            string[] names = new string[] { "A", "B", "C", "D" };
            string[] numbers = new string[] { "1", "2", "3", "4" };

            //Add marker variables in the worksheet.
            worksheet["A1"].Text = "Names";
            worksheet["B1"].Text = "Numbers";
            worksheet["A2"].Text = "%Names";
            worksheet["B2"].Text = "%Numbers";

            //Create a Template Marker Instance.
            ITemplateMarkersProcessor marker = workbook.CreateTemplateMarkersProcessor();

            //Add variable names from the binding context to Template Marker.
            marker.AddVariable("Names", names);
            marker.AddVariable("Numbers", numbers);

            //Remove variable from marker object
            marker.RemoveVariable("Numbers");

            //Apply Marker.
            marker.ApplyMarkers(UnknownVariableAction.Skip);

            //Save and Dispose.
            workbook.SaveAs("TemplateMarker.xlsx");
            workbook.Close();
        }