Class TemplateMarkersImpl

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

Inheritance

System.Object

CommonObject

TemplateMarkersImpl

Implements

IParentApplication

System.IDisposable

ITemplateMarkersProcessor

Inherited Members

CommonObject.AddReference()

CommonObject.AppImplementation

CommonObject.Application

CommonObject.CheckDisposed()

CommonObject.Dispose()

CommonObject.Finalize()

CommonObject.FindParent(Object, Type)

CommonObject.FindParent(Object, Type, Boolean)

CommonObject.FindParent(Type)

CommonObject.FindParent(Type, Boolean)

CommonObject.FindParent(Type[])

CommonObject.FindParents(Type[])

CommonObject.m_bIsDisposed

CommonObject.OnDispose()

CommonObject.Parent

CommonObject.ReferenceCount

CommonObject.ReleaseReference()

CommonObject.SetParent(Object)

Namespace: Syncfusion.XlsIO.Implementation.TemplateMarkers

Assembly: Syncfusion.XlsIO.NET.dll

Syntax

public class TemplateMarkersImpl : CommonObject, IParentApplication, IDisposable, ITemplateMarkersProcessor

Remarks

A template marker is a special marker symbol created in an Excel template that appends multiple records from a data source into a worksheet.

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();

Constructors

TemplateMarkersImpl(IApplication, Object)

Initializes a new instance of the TemplateMarkersImpl class.

Declaration

public TemplateMarkersImpl(IApplication application, object parent)

Parameters

Type	Name	Description
IApplication	application	Application object for the new object.
System.Object	parent	Parent object for the new object.

Properties

ArgumentSeparator

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

Declaration

public char ArgumentSeparator { get; set; }

Property Value

Type
System.Char

MarkerPrefix

Declaration

public string MarkerPrefix { get; set; }

Property Value

Type
System.String

Methods

AddVariable(String, Object)

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

Declaration

public void AddVariable(string strName, object variable)

Parameters

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

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);

AddVariable(String, Object, VariableTypeAction)

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

Declaration

public void AddVariable(string strName, object variable, VariableTypeAction makerType)

Parameters

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

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, VariableTypeAction.DetectNumberFormat);

ApplyMarkers()

Applies markers to the parent object.

Declaration

public void ApplyMarkers()

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));

//Detects number format in DateTable values.
marker.AddVariable("Reports", reports,VariableTypeAction.DetectNumberFormat);
marker.ApplyMarkers();

ApplyMarkers(IWorkbook)

Applies markers to the whole workbook.

Declaration

public void ApplyMarkers(IWorkbook book)

Parameters

Type	Name	Description
IWorkbook	book	Workbook to apply markers to.

ApplyMarkers(IWorkbook, UnknownVariableAction)

Applies markers to the parent object with the specified UnknownVariableAction.

Declaration

public void ApplyMarkers(IWorkbook book, UnknownVariableAction action)

Parameters

Type	Name	Description
IWorkbook	book	Workbook to apply markers to.
UnknownVariableAction	action	Marker Unknown Variable.

ApplyMarkers(IWorksheet)

Applies markers to the specified worksheet.

Declaration

public void ApplyMarkers(IWorksheet sheet)

Parameters

Type	Name	Description
IWorksheet	sheet	Worksheet to apply markers to.

ApplyMarkers(IWorksheet, UnknownVariableAction)

Applies markers to the specified worksheet.

Declaration

public void ApplyMarkers(IWorksheet sheet, UnknownVariableAction action)

Parameters

Type	Name	Description
IWorksheet	sheet	Worksheet to apply markers to.
UnknownVariableAction	action	Marker Unknown Variable.

ApplyMarkers(UnknownVariableAction)

Applies markers to the parent object with the specified UnknownVariableAction.

Declaration

public void ApplyMarkers(UnknownVariableAction action)

Parameters

Type	Name	Description
UnknownVariableAction	action	Marker Unknown Variable.

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));

//Detects number format in DateTable values.
marker.AddVariable("Reports", reports,VariableTypeAction.DetectNumberFormat);
marker.ApplyMarkers(UnknownVariableAction.Exception);

CheckAndApplyHeaders(Object, IWorksheet, UnknownVariableAction, IList<Int64>, IMigrantRange)

Checks and applies headers.

Declaration

public bool CheckAndApplyHeaders(object obj, IWorksheet sheet, UnknownVariableAction action, IList<long> arrCells, IMigrantRange migrantRange)

Parameters

Type	Name	Description
System.Object	obj	The obj to get the headers.
IWorksheet	sheet	The current sheet object.
UnknownVariableAction	action	The unknown variable action.
System.Collections.Generic.IList<System.Int64>	arrCells	The array of cells indexes.
IMigrantRange	migrantRange	The migrant range to set the header names.

Returns

Type	Description
System.Boolean	Indicates whether the markers are applied.

CheckClassMarker(IWorksheet, UnknownVariableAction, IList<Int64>, IMigrantRange)

Checks the class marker in the worksheet.

Declaration

public bool CheckClassMarker(IWorksheet sheet, UnknownVariableAction action, IList<long> arrCells, IMigrantRange migrantRange)

Parameters

Type	Name	Description
IWorksheet	sheet	The current sheet object.
UnknownVariableAction	action	The unknown variable action.
System.Collections.Generic.IList<System.Int64>	arrCells	The array of cell indexes.
IMigrantRange	migrantRange	The migrant range to apply the indexes.

Returns

Type	Description
System.Boolean	Indicates whether the sheet contains the class marker.

ContainsVariable(String)

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

Declaration

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

CreateConditionalFormats(IRange)

Adds a conditional format to the Template Marker.

Declaration

public 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

The conditional format range should be within the template marker range.

Examples

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IWorksheet worksheet = workbook.Worksheets[0];

//Create Template Marker Processor
ITemplateMarkersProcessor marker = workbook.CreateTemplateMarkersProcessor();
IConditionalFormats conditionalFormats = marker.CreateConditionalFormats(worksheet["C6"]);

//Data Bar
//Apply markers using Formula
IConditionalFormat condition = conditionalFormats.AddCondition();

//Set Data bar and icon set for the same cell
//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;

//IconSet
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;
conditionalFormats = marker.CreateConditionalFormats(worksheet["D6"]);

//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";
conditionalFormats = marker.CreateConditionalFormats(worksheet["E6"]);

//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;

marker.AddVariable("Reports", GetDataTable());

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


//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;
}

GetColumnType(String[], out List<String>, out List<Type>, VariableTypeAction)

Detects and get the value type of the each column.

Declaration

public void GetColumnType(string[] values, out List<string> numberFormats, out List<Type> valueTypes, VariableTypeAction variableTypeAction)

Parameters

Type	Name	Description
System.String[]	values	values of the data columns.
System.Collections.Generic.List<System.String>	numberFormats	detected number formats.
System.Collections.Generic.List<System.Type>	valueTypes	Detected values Types.
VariableTypeAction	variableTypeAction	Format Type.

GetValue(String, ref String, ref Type, Boolean)

Get value, value type and the number format.

Declaration

public object GetValue(string value, ref string numberFormat, ref Type valueType, bool detectNumberFormat)

Parameters

Type	Name	Description
System.String	value	string value.
System.String	numberFormat	number format of the value.
System.Type	valueType	Type of the value.
System.Boolean	detectNumberFormat	Indicates, whether to detect Number format.

Returns

Type	Description
System.Object	value as object.

GetValue(String, Type)

Get the value based on the valueType

Declaration

public object GetValue(string value, Type valueType)

Parameters

Type	Name	Description
System.String	value	string value.
System.Type	valueType	Type of the given value.

Returns

Type	Description
System.Object	value as object.

RemoveVariable(String)

Removes a variable with specified name from the collection.

Declaration

public void RemoveVariable(string strName)

Parameters

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

Implements

IParentApplication

System.IDisposable

ITemplateMarkersProcessor