Class TemplateMarkersImpl
Represents the processing of a special marker symbol, that appends multiple records from data source into a worksheet.
Inherited Members
Namespace: Syncfusion.XlsIO.Implementation.TemplateMarkers
Assembly: Syncfusion.XlsIO.UWP.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. |