Working with Template Markers

A template marker is a special marker symbol created in an Excel template that appends multiple records from a data source into a worksheet. This marker automatically maps the column name in the data source and names of the marker fields in the template Excel document and fills the data (text or image). Essential XlsIO allows you to bind the template markers to data from various sources, such as

• DataTable
• CLR objects
• Arrays

Template marker Syntax

Each marker starts with a prefix character (by default it is “%” character). The marker is followed by the variable name and properties which are delimited by a character (by default it is semicolon “;”.)

%<MarkerVariable>.<Property> For example: %Customers.CompanyName Where, “Customers” is marker variable name and CompanyName is the property name

You can change the marker prefix and delimiter characters by the MarkerPrefix and ArgumentSeparator properties of the ITemplateMarkersProcessor instance respectively.

Arguments

You can specify the following arguments with the marker to customize the worksheet.

Horizontal-This argument specifies the horizontal direction of the data filling for variables.

Syntax: %<MarkerVariable>.<Property>;horizontal

Vertical-This argument specifies the vertical direction of the data filling for variables. By default, data will be filled in vertical direction

Syntax: %<MarkerVariable>.<Property>;vertical

insert-This argument inserts new row or column, depending on the direction argument for each new cell. Note that by default, the rows will not be inserted.

Syntax: %<MarkerVariable>.<Property>;insert

insert:copystyles-This argument copies styles from the above row or left column.

Syntax: %<MarkerVariable>.<Property>;insert:copystyles

jump:[cell reference in R1C1 notation]-This argument binds the data to the cell at the specified reference. Cell reference addresses can be relative or absolute.

Syntax: %<MarkerVariable>.<Property>;jump:R2C2

copyrange:[top-left cell reference in R1C1]:[bottom-right cell reference in R1C1]-Copies the specified cells after each cell import.

Syntax: %<MarkerVariable>.<Property>;copyrange:R2C2:R4C4

Bind from Array

An array of data can be binded to the marker in the template document.

Syntax: %VariableArray

The following screenshot represents the input template which has a template marker.

Following code example illustrates how to bind the data from an array to a marker.

c#

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

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

  //Insert Array Horizontally
  string[] names = new string[] { "Mickey", "Donald", "Tom", "Jerry" };
  string[] descriptions = new string[] { "Mouse", "Duck", "Cat", "Mouse" };
  
  //Add collections to the marker variables where the name should match with input template
  marker.AddVariable("Names", names);
  marker.AddVariable("Descriptions", descriptions);

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

  workbook.Version = ExcelVersion.Excel2013;
  workbook.SaveAs("TemplateMarker.xlsx");
}

vb

Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim workbook As IWorkbook = excelEngine.Excel.Workbooks.Open("Sample.xlsx")
  Dim sheet As IWorksheet = workbook.Worksheets(0)

  'Create Template Marker Processor
  Dim marker As ITemplateMarkersProcessor = workbook.CreateTemplateMarkersProcessor()

  'Insert Array Horizontally
  Dim names As String() = New String() {"Mickey", "Donald", "Tom", "Jerry"}
  Dim descriptions As String() = New String() {"Mouse", "Duck", "Cat", "Mouse"}

  'Add collections to the marker variables where the name should match with input template
  marker.AddVariable("Names", names)
  marker.AddVariable("Descriptions", descriptions)

  'Process the markers in the template
  marker.ApplyMarkers()

  workbook.Version = ExcelVersion.Excel2013
  workbook.SaveAs("TemplateMarker.xlsx")
End Using

uwp

using (ExcelEngine excelEngine = new ExcelEngine())
{
  //Gets assembly
  Assembly assembly = typeof(App).GetTypeInfo().Assembly;

  //Gets input Excel document from embedded resource collection
  Stream inputStream = assembly.GetManifestResourceStream("Sample.xlsx");

  IWorkbook workbook = await excelEngine.Excel.Workbooks.OpenAsync(inputStream);
  IWorksheet worksheet = workbook.Worksheets[0];

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

  //Insert Array Horizontally
  string[] names = new string[] { "Mickey", "Donald", "Tom", "Jerry" };
  string[] descriptions = new string[] { "Mouse", "Duck", "Cat", "Mouse" };

  //Add collections to the marker variables where the name should match with input template
  marker.AddVariable("Names", names);
  marker.AddVariable("Descriptions", descriptions);

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

  //Initializes FileSavePicker
  FileSavePicker savePicker = new FileSavePicker();
  savePicker.SuggestedStartLocation = PickerLocationId.Desktop;
  savePicker.SuggestedFileName = "TemplateMarker";
  savePicker.FileTypeChoices.Add("Excel Files", new List<string>() { ".xlsx" });

  //Creates a storage file from FileSavePicker
  StorageFile storageFile = await savePicker.PickSaveFileAsync();

  //Saves changes to the specified storage file
  await workbook.SaveAsAsync(storageFile);
}

asp.net core

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;

  FileStream fileStream = new FileStream("Sample.xlsx", FileMode.Open, FileAccess.Read);

  IWorkbook workbook = application.Workbooks.Open(fileStream);
  IWorksheet worksheet = workbook.Worksheets[0];

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

  //Insert Array Horizontally
  string[] names = new string[] { "Mickey", "Donald", "Tom", "Jerry" };
  string[] descriptions = new string[] { "Mouse", "Duck", "Cat", "Mouse" };

  //Add collections to the marker variables where the name should match with input template
  marker.AddVariable("Names", names);
  marker.AddVariable("Descriptions", descriptions);

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

  //Saving the workbook as stream
  workbook.Version = ExcelVersion.Excel2013;
  FileStream stream = new FileStream("TemplateMarker.xlsx", FileMode.Create, FileAccess.ReadWrite);
  workbook.SaveAs(stream);
  stream.Dispose();
}

xamarin

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  
  //Gets assembly
  Assembly assembly = typeof(App).GetTypeInfo().Assembly;
           
  //Gets input Excel document from embedded resource collection
  Stream inputStream = assembly.GetManifestResourceStream("Sample.xlsx");
  
  IWorkbook workbook = application.Workbooks.Open(inputStream);
  IWorksheet worksheet = workbook.Worksheets[0];

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

  //Insert Array Horizontally
  string[] names = new string[] { "Mickey", "Donald", "Tom", "Jerry" };
  string[] descriptions = new string[] { "Mouse", "Duck", "Cat", "Mouse" };

  //Add collections to the marker variables where the name should match with input template
  marker.AddVariable("Names", names);
  marker.AddVariable("Descriptions", descriptions);

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

  //Saving the workbook as stream
  workbook.Version = ExcelVersion.Excel2013;
  MemoryStream outputStream = new MemoryStream();
  workbook.SaveAs(outputStream);

  //Save the stream as Excel document and view the saved document
  
  //The operation in SaveAndView under Xamarin varies between Windows Phone, Android and iOS platforms. Please refer xlsio/xamarin section for respective code samples.
  if (Device.OS == TargetPlatform.WinPhone || Device.OS == TargetPlatform.Windows)
    await DependencyService.Get<ISaveWindowsPhone>().SaveAndView("TemplateMarker.xlsx", "application/msexcel", outputStream);
  else
    DependencyService.Get<ISave>().SaveAndView("TemplateMarker.xlsx", "application/msexcel", outputStream);

  //Dispose the input and output stream instances
  inputStream.Dispose();
  outputStream.Dispose();
}

The following screenshot represents generated Excel file in which the array of data is bounded.

You can also add or insert template markers using XlsIO APIs as follows.

c#

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IWorkbook workbook = excelEngine.Excel.Workbooks.Open("Sample.xlsx");
  IWorksheet sheet = workbook.Worksheets[0];

  //Insert Simple marker
  sheet.Range["B2"].Text = "%Marker";

  //Insert marker which gets value of Author name
  sheet.Range["C2"].Text = "%Marker2.Worksheet.Workbook.Author";

  //Insert marker which gets cell address
  sheet.Range["H2"].Text = "%ArrayProperty.Cells.Address";

  //Create Template Marker Processor
  ITemplateMarkersProcessor marker = workbook.CreateTemplateMarkersProcessor();
  
  //Add collections to the marker variables where the name should match with input template
  marker.AddVariable("Marker", "First test of markers");
  marker.AddVariable("Marker2", sheet.Range["B2"]);
  marker.AddVariable("ArrayProperty", sheet.Range["B2:G2"]);

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

  workbook.Version = ExcelVersion.Excel2013;
  workbook.SaveAs("TemplateMarker.xlsx");
}

vb

Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim workbook As IWorkbook = excelEngine.Excel.Workbooks.Open("Sample.xlsx")
  IWorksheet sheet = workbook.Worksheets(0)

  'Insert Simple marker
  sheet.Range("B2").Text = "%Marker"

  'Insert marker which gets value of Author name
  sheet.Range("C2").Text = "%Marker2.Worksheet.Workbook.Author"

  'Insert marker which gets cell address
  sheet.Range("H2").Text = "%ArrayProperty.Cells.Address"

  'Create Template Marker Processor
  Dim marker As ITemplateMarkersProcessor = workbook.CreateTemplateMarkersProcessor()
  
  'Add collections to the marker variables where the name should match with input template
  marker.AddVariable("Marker", "First test of markers")
  marker.AddVariable("Marker2", sheet.Range("B2"))
  marker.AddVariable("ArrayProperty", sheet.Range("B2:G2"))

  'Process the markers in the template
  marker.ApplyMarkers()

  workbook.Version = ExcelVersion.Excel2013
  workbook.SaveAs("TemplateMarker.xlsx")
End Using

uwp

using (ExcelEngine excelEngine = new ExcelEngine())
{
  //Gets assembly
  Assembly assembly = typeof(App).GetTypeInfo().Assembly;

  //Gets input Excel document from embedded resource collection
  Stream inputStream = assembly.GetManifestResourceStream("Sample.xlsx");

  IWorkbook workbook = await excelEngine.Excel.Workbooks.OpenAsync(inputStream);
  IWorksheet sheet = workbook.Worksheets[0];

  //Insert Simple marker
  sheet.Range["B2"].Text = "%Marker";

  //Insert marker which gets value of Author name
  sheet.Range["C2"].Text = "%Marker2.Worksheet.Workbook.Author";

  //Insert marker which gets cell address
  sheet.Range["H2"].Text = "%ArrayProperty.Cells.Address";

  //Create Template Marker Processor
  ITemplateMarkersProcessor marker = workbook.CreateTemplateMarkersProcessor();
  
  //Add collections to the marker variables where the name should match with input template
  marker.AddVariable("Marker", "First test of markers");
  marker.AddVariable("Marker2", sheet.Range["B2"]);
  marker.AddVariable("ArrayProperty", sheet.Range["B2:G2"]);

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

  workbook.Version = ExcelVersion.Excel2013;

  //Initializes FileSavePicker
  FileSavePicker savePicker = new FileSavePicker();
  savePicker.SuggestedStartLocation = PickerLocationId.Desktop;
  savePicker.SuggestedFileName = "TemplateMarker";
  savePicker.FileTypeChoices.Add("Excel Files", new List<string>() { ".xlsx" });

  //Creates a storage file from FileSavePicker
  StorageFile storageFile = await savePicker.PickSaveFileAsync();

  //Saves changes to the specified storage file
  await workbook.SaveAsAsync(storageFile);
}

asp.net core

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;

  FileStream fileStream = new FileStream("Sample.xlsx", FileMode.Open, FileAccess.Read);

  IWorkbook workbook = application.Workbooks.Open(fileStream);
  IWorksheet sheet = workbook.Worksheets[0];

  //Insert Simple marker
  sheet.Range["B2"].Text = "%Marker";

  //Insert marker which gets value of Author name
  sheet.Range["C2"].Text = "%Marker2.Worksheet.Workbook.Author";

  //Insert marker which gets cell address
  sheet.Range["H2"].Text = "%ArrayProperty.Cells.Address";

  //Create Template Marker Processor
  ITemplateMarkersProcessor marker = workbook.CreateTemplateMarkersProcessor();
  
  //Add collections to the marker variables where the name should match with input template
  marker.AddVariable("Marker", "First test of markers");
  marker.AddVariable("Marker2", sheet.Range["B2"]);
  marker.AddVariable("ArrayProperty", sheet.Range["B2:G2"]);

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

  //Saving the workbook as stream
  workbook.Version = ExcelVersion.Excel2013;
  FileStream stream = new FileStream("TemplateMarker.xlsx", FileMode.Create, FileAccess.ReadWrite);
  workbook.SaveAs(stream);
  stream.Dispose();
}

xamarin

using(ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  
  //Gets assembly
  Assembly assembly = typeof(App).GetTypeInfo().Assembly;
             
  //Gets input Excel document from embedded resource collection
  Stream inputStream = assembly.GetManifestResourceStream("Sample.xlsx");
  
  IWorkbook workbook = application.Workbooks.Open(inputStream);
  IWorksheet sheet = workbook.Worksheets[0];

  //Insert Simple marker
  sheet.Range["B2"].Text = "%Marker";

  //Insert marker which gets value of Author name
  sheet.Range["C2"].Text = "%Marker2.Worksheet.Workbook.Author";

  //Insert marker which gets cell address
  sheet.Range["H2"].Text = "%ArrayProperty.Cells.Address";

  //Create Template Marker Processor
  ITemplateMarkersProcessor marker = workbook.CreateTemplateMarkersProcessor();
  
  //Add collections to the marker variables where the name should match with input template
  marker.AddVariable("Marker", "First test of markers");
  marker.AddVariable("Marker2", sheet.Range["B2"]);
  marker.AddVariable("ArrayProperty", sheet.Range["B2:G2"]);

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

  //Saving the workbook as stream
  workbook.Version = ExcelVersion.Excel2013;
  MemoryStream outputStream = new MemoryStream();
  workbook.SaveAs(outputStream);

  //Save the stream as Excel document and view the saved document  
  
  //The operation in SaveAndView under Xamarin varies between Windows Phone, Android and iOS platforms. Please refer xlsio/xamarin section for respective code samples.
  if (Device.OS == TargetPlatform.WinPhone || Device.OS == TargetPlatform.Windows)
      await DependencyService.Get<ISaveWindowsPhone>().SaveAndView("TemplateMarker.xlsx", "application/msexcel", outputStream);
  else
      DependencyService.Get<ISave>().SaveAndView("TemplateMarker.xlsx", "application/msexcel", outputStream);

  //Dispose the input and output stream instances
  inputStream.Dispose();
  outputStream.Dispose();
}

Bind from DataTable

Syntax:

%<DataSource>.<FieldName> For example: %Products.ProductName Where, “Products” is a data source which can be data tables, datasets, data readers and data views and ProductName is the field name or column name

By default, DataTable values will be filled in the worksheet as a string format. You can detect data type and number format of DataTable values by using VariableTypeAction enumerator. To know more about the VariableTypeAction enumerator, please refer VariableTypeAction in API section.

The following screenshot represents the input template which has a template marker.

The following code snippet illustrates how to detect data type and apply number format with template marker.

c#

using(ExcelEngine excelEngine = new ExcelEngine())
{
  IWorkbook workbook = excelEngine.Excel.Workbooks.Open("TemplateMarker.xlsx");

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

  DataTable reports = new 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 collection to the marker variables where the name should match with input template
  //Detects number format in DateTable values
  marker.AddVariable("Reports", reports,VariableTypeAction.DetectNumberFormat);

  //Process the markers and detect the number format along with the data type in the template
  marker.ApplyMarkers();

  workbook.Version = ExcelVersion.Excel2013;
  workbook.SaveAs("TemplateMarkerWithFormat.xlsx");
}

vb

using excelEngine As ExcelEngine = new ExcelEngine()
  Dim workbook As IWorkbook = excelEngine.Excel.Workbooks.Open("TemplateMarker.xlsx")

  'Create Template Marker Processor
  Dim marker As ITemplateMarkersProcessor = workbook.CreateTemplateMarkersProcessor()

  Dim reports As New DataTable()
  reports.Columns.Add("SalesPerson")
  reports.Columns.Add("FromDate", GetType(DateTime))
  reports.Columns.Add("ToDate", GetType(DateTime))
  reports.Rows.Add("Andy Bernard", New DateTime(2014, 9, 8), New DateTime(2014, 9, 11))
  reports.Rows.Add("Jim Halpert", New DateTime(2014, 9, 11), New DateTime(2014, 9, 15))
  reports.Rows.Add("Karen Fillippelli", New DateTime(2014, 9, 15), New DateTime(2014, 9, 20))
  reports.Rows.Add("Phyllis Lapin", New DateTime(2014, 9, 21), New DateTime(2014, 9, 25))
  reports.Rows.Add("Stanley Hudson", New DateTime(2014, 9, 26), New DateTime(2014, 9, 30))

  'Add collection to the marker variables where the name should match with input template
  'Detects number format in DateTable values
  marker.AddVariable("Reports", reports, VariableTypeAction.DetectNumberFormat)

  'Process the markers and detect the number format along with the data type in the template
  marker.ApplyMarkers()

  workbook.Version = ExcelVersion.Excel2013
  workbook.SaveAs("TemplateMarkerWithFormat.xlsx")
End Using

uwp

//XlsIO supports binding data from data table using template markers in Windows Forms, WPF, ASP.NET, ASP.NET MVC, and ASP.NET Core (2.0 onwards) platforms alone.

asp.net core

//Binding data from data table is supported only from ASP.NET Core 2.0
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  FileStream fileStream = new FileStream("TemplateMarker.xlsx", FileMode.Open, FileAccess.Read);
  IWorkbook workbook = application.Workbooks.Open(fileStream);

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

  DataTable reports = new 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 collection to the marker variables where the name should match with input template  
  //Detects number format in DateTable values
  marker.AddVariable("Reports", reports, VariableTypeAction.DetectNumberFormat);

  //Process the markers and detect the number format along with the data type in the template
  marker.ApplyMarkers();

  //Saving the workbook as stream
  workbook.Version = ExcelVersion.Excel2013;

  FileStream stream = new FileStream("TemplateMarkerWithFormat.xlsx", FileMode.Create, FileAccess.ReadWrite);
  workbook.SaveAs(stream);
  stream.Dispose();
}

xamarin

//XlsIO supports binding data from data table using template markers in Windows Forms, WPF, ASP.NET, ASP.NET MVC, and ASP.NET Core (2.0 onwards) platforms alone.

The following screenshot represents an Excel file in which the data type is detected and then number format is applied.

Bind from CLR objects with images

You can generate reports more appealingly with image support in template markers. The possible image formats are as follows:

• GIF
• JPEG
• PNG
• BMP
• TIFF

XlsIO detects the property as image when its type is System.Drawing.Image or byte []. The image can be formatted using the following arguments.

No	Image arguments	Description
1	No argument Ex: %Reports.Image;	Image is applied with a default size (50x50 pixels) and position (Top-Left).
2	fittocell Ex: %Reports.Image;fittocell	The image is applied to cell width and height.
3	size:width,height Ex: %Reports.Image;size:60 (or) %Reports.Image;size:60,60	Image is applied to the specified size (width, height). Height parameter is optional. Value of width is applied when height is not specified.
4	position:position Ex: %Reports.Image;position:middle-center (or) %Reports.Image;position:right	Image is positioned (top-left, top-center, etc.,) within the cell.

In the following example, a marker is added for merging images. Data source and property name is specified (%Reports.Image;) for image also.

Marker added for merging images

NOTE

Image can be used in array, DataTable, and CLR objects.

Different positions of the image are maintained internally in the ImageVerticalPosition and ImageHorizontalPosition enumerators. To learn more about this, refer to the ImageVerticalPosition and ImageHorizontalPosition enumerators respectively in API section.

The output of all the image insertion options with input templates are as follows.

Default image input and output

Input template

Generated output

Image with FitToCell attribute

Input template

Generated output

Image with Size

Input template

Generated output

Image with Position

Input template

Generated output

Image with position and size

Input template

Generated output

You can also refer to the Template based data filling using Template Markers section in Getting Started for the sample regarding template marker with images.

Template marker with conditional formatting

You can create or apply conditional format to the template marker range.

The following screenshot represents the input template, which has a template marker.

The following code sample illustrates how to create or apply conditional format to the marker.

c#

using (ExcelEngine excelEngine = new ExcelEngine())
{
  #region Initialize Workbook

  IWorkbook workbook = excelEngine.Excel.Workbooks.Open("TemplateMarker.xlsx");
  IWorksheet worksheet = workbook.Worksheets[0];

  #endregion

  #region Create Template Marker

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

  IConditionalFormats conditionalFormats = marker.CreateConditionalFormats(worksheet["C5"]);

  #region 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 = Color.FromArgb(156, 208, 243);

  //Hide the value in data bar
  dataBar.ShowValue = false;

  #endregion

  #region 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;

  #endregion

  conditionalFormats = marker.CreateConditionalFormats(worksheet["D5"]);

  #region Color Scale

  condition = conditionalFormats.AddCondition();
  condition.FormatType = ExcelCFType.ColorScale;

  IColorScale colorScale = condition.ColorScale;

  //Sets 3 - color scale
  colorScale.SetConditionCount(3);

  colorScale.Criteria[0].FormatColorRGB = Color.FromArgb(230, 197, 218);
  colorScale.Criteria[0].Type = ConditionValueType.LowestValue;
  colorScale.Criteria[0].Value = "0";

  colorScale.Criteria[1].FormatColorRGB = Color.FromArgb(244, 210, 178);
  colorScale.Criteria[1].Type = ConditionValueType.Percentile;
  colorScale.Criteria[1].Value = "50";

  colorScale.Criteria[2].FormatColorRGB = Color.FromArgb(245, 247, 171);
  colorScale.Criteria[2].Type = ConditionValueType.HighestValue;
  colorScale.Criteria[2].Value = "0";

  #endregion

  conditionalFormats = marker.CreateConditionalFormats(worksheet["E5"]);

  #region IconSet

  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;

  #endregion

  //Add collection to the marker variables where the name should match with input template
  marker.AddVariable("Reports", GetSalesReports());

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

  #endregion

  #region Save the Workbook

  workbook.Version = ExcelVersion.Excel2013;
  workbook.SaveAs("TemplateMarkerCF.xlsx");

  #endregion
}

vb

'Region "Initialize Workbook"

using excelEngine As ExcelEngine = new ExcelEngine()
  Dim workbook As IWorkbook = excelEngine.Excel.Workbooks.Open("TemplateMarker.xlsx")
  Dim worksheet As IWorksheet = workbook.Worksheets(0)

  'End Region

  'Region "Create Template Marker"

  'Create Template Marker Processor
  Dim marker As ITemplateMarkersProcessor = workbook.CreateTemplateMarkersProcessor()

  Dim conditionalFormats As IConditionalFormats = marker.CreateConditionalFormats(worksheet("C5"))

  'Region "Data Bar"

  'Apply markers using Formula
  Dim condition As IConditionalFormat = conditionalFormats.AddCondition()

  'Set Data bar and icon set for the same cell
  'Set the format type
  condition.FormatType = ExcelCFType.DataBar

  Dim dataBar As IDataBar = 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 = Color.FromArgb(156, 208, 243)

  'Hide the value in data bar
  dataBar.ShowValue = False

  'End Region

  'Region "IconSet"

  condition = conditionalFormats.AddCondition()
  condition.FormatType = ExcelCFType.IconSet

  Dim iconSet As IIconSet = 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

  'End Region

  conditionalFormats = marker.CreateConditionalFormats(worksheet("D5"))

  'Region "Color Scale"

  condition = conditionalFormats.AddCondition()
  condition.FormatType = ExcelCFType.ColorScale

  Dim colorScale As IColorScale = condition.ColorScale

  'Sets 3 - color scale
  colorScale.SetConditionCount(3)

  colorScale.Criteria(0).FormatColorRGB = Color.FromArgb(230, 197, 218)
  colorScale.Criteria(0).Type = ConditionValueType.LowestValue
  colorScale.Criteria(0).Value = "0"

  colorScale.Criteria(1).FormatColorRGB = Color.FromArgb(244, 210, 178)
  colorScale.Criteria(1).Type = ConditionValueType.Percentile
  colorScale.Criteria(1).Value = "50"

  colorScale.Criteria(2).FormatColorRGB = Color.FromArgb(245, 247, 171)
  colorScale.Criteria(2).Type = ConditionValueType.HighestValue
  colorScale.Criteria(2).Value = "0"

  'End Region

  conditionalFormats = marker.CreateConditionalFormats(worksheet("E5"))

  'Region "IconSet"

  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

  'End Region

  'Add collection to the marker variables where the name should match with input template
  marker.AddVariable("Reports", GetSalesReports())

  'Process the markers in the template
  marker.ApplyMarkers()

  'End Region

  'Region "Save the Workbook"

  workbook.Version = ExcelVersion.Excel2013
  workbook.SaveAs("TemplateMarkerCF.xlsx")

  'End Region
End Using

uwp

using (ExcelEngine excelEngine = new ExcelEngine())
{
  //Gets assembly
  Assembly assembly = typeof(App).GetTypeInfo().Assembly;

  //Gets input Excel document from embedded resource collection
  Stream inputStream = assembly.GetManifestResourceStream("TemplateMarker.TemplateMarker.xlsx");

  IWorkbook workbook = await excelEngine.Excel.Workbooks.OpenAsync(inputStream);
  IWorksheet worksheet = workbook.Worksheets[0];

  #endregion

  #region Create Template Marker

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

  IConditionalFormats conditionalFormats = marker.CreateConditionalFormats(worksheet["C5"]);

  #region 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 = Color.FromArgb(156, 208, 243, 255);

  //Hide the value in data bar
  dataBar.ShowValue = false;

  #endregion

  #region 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;

  #endregion

  conditionalFormats = marker.CreateConditionalFormats(worksheet["D5"]);

  #region Color Scale

  condition = conditionalFormats.AddCondition();
  condition.FormatType = ExcelCFType.ColorScale;

  IColorScale colorScale = condition.ColorScale;

  //Sets 3 - color scale
  colorScale.SetConditionCount(3);

  colorScale.Criteria[0].FormatColorRGB = Color.FromArgb(230, 197, 218, 255);
  colorScale.Criteria[0].Type = ConditionValueType.LowestValue;
  colorScale.Criteria[0].Value = "0";

  colorScale.Criteria[1].FormatColorRGB = Color.FromArgb(244, 210, 178, 255);
  colorScale.Criteria[1].Type = ConditionValueType.Percentile;
  colorScale.Criteria[1].Value = "50";

  colorScale.Criteria[2].FormatColorRGB = Color.FromArgb(245, 247, 171, 255);
  colorScale.Criteria[2].Type = ConditionValueType.HighestValue;
  colorScale.Criteria[2].Value = "0";

  #endregion

  conditionalFormats = marker.CreateConditionalFormats(worksheet["E5"]);

  #region IconSet

  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;

  #endregion

  //Add collection to the marker variables where the name should match with input template
  marker.AddVariable("Reports", GetSalesReports());

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

  #endregion

  #region Save the Workbook

  workbook.Version = ExcelVersion.Excel2013;

  //Initializes FileSavePicker
  FileSavePicker savePicker = new FileSavePicker();
  savePicker.SuggestedStartLocation = PickerLocationId.Desktop;
  savePicker.SuggestedFileName = "TemplateMarkerCF";
  savePicker.FileTypeChoices.Add("Excel Files", new List<string>() { ".xlsx" });

  //Creates a storage file from FileSavePicker
  StorageFile storageFile = await savePicker.PickSaveFileAsync();

  //Saves changes to the specified storage file
  await workbook.SaveAsAsync(storageFile);

  #endregion
}

asp.net core

using (ExcelEngine excelEngine = new ExcelEngine())
{
  #region Initialize Workbook

  FileStream fileStream = new FileStream("TemplateMarker.xlsx", FileMode.Open, FileAccess.Read);

  IWorkbook workbook = excelEngine.Excel.Workbooks.Open(fileStream);
  IWorksheet worksheet = workbook.Worksheets[0];

  #endregion

  #region Create Template Marker

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

  IConditionalFormats conditionalFormats = marker.CreateConditionalFormats(worksheet["C5"]);

  #endregion

  #region 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 = Color.FromArgb(156, 208, 243);

  //Hide the value in data bar
  dataBar.ShowValue = false;

  #endregion

  #region 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;

  #endregion

  conditionalFormats = marker.CreateConditionalFormats(worksheet["D5"]);

  #region Color Scale

  condition = conditionalFormats.AddCondition();
  condition.FormatType = ExcelCFType.ColorScale;

  IColorScale colorScale = condition.ColorScale;

  //Sets 3 - color scale
  colorScale.SetConditionCount(3);

  colorScale.Criteria[0].FormatColorRGB = Color.FromArgb(230, 197, 218);
  colorScale.Criteria[0].Type = ConditionValueType.LowestValue;
  colorScale.Criteria[0].Value = "0";

  colorScale.Criteria[1].FormatColorRGB = Color.FromArgb(244, 210, 178);
  colorScale.Criteria[1].Type = ConditionValueType.Percentile;
  colorScale.Criteria[1].Value = "50";

  colorScale.Criteria[2].FormatColorRGB = Color.FromArgb(245, 247, 171);
  colorScale.Criteria[2].Type = ConditionValueType.HighestValue;
  colorScale.Criteria[2].Value = "0";

  #endregion

  conditionalFormats = marker.CreateConditionalFormats(worksheet["E5"]);

  #region IconSet

  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;

  #endregion

  //Add collection to the marker variables where the name should match with input template
  marker.AddVariable("Reports", GetSalesReports());

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

  //Saving the workbook as stream
  workbook.Version = ExcelVersion.Excel2013;
  FileStream stream = new FileStream("TemplateMarkerCF.xlsx", FileMode.Create, FileAccess.ReadWrite);
  workbook.SaveAs(stream);
  stream.Dispose();
}

xamarin

using (ExcelEngine excelEngine = new ExcelEngine())
{
  #region Initialize Workbook

  //Gets assembly
  Assembly assembly = typeof(App).GetTypeInfo().Assembly;

  //Gets input Excel document from embedded resource collection
  Stream inputStream = assembly.GetManifestResourceStream("TemplateMarker.xlsx");

  IWorkbook workbook = excelEngine.Excel.Workbooks.Open(inputStream);
  IWorksheet worksheet = workbook.Worksheets[0];

  #endregion

  #region Create Template Marker

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

  IConditionalFormats conditionalFormats = marker.CreateConditionalFormats(worksheet["C5"]);

  #endregion

  #region 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";

  //Hide the value in data bar
  dataBar.ShowValue = false;

  #endregion

  #region 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;

  #endregion

  conditionalFormats = marker.CreateConditionalFormats(worksheet["D5"]);

  #region Color Scale

  condition = conditionalFormats.AddCondition();
  condition.FormatType = ExcelCFType.ColorScale;

  IColorScale colorScale = condition.ColorScale;

  //Sets 3 - color scale
  colorScale.SetConditionCount(3);

  colorScale.Criteria[0].FormatColorRGB = Syncfusion.Drawing.Color.FromArgb(230, 197, 218);
  colorScale.Criteria[0].Type = ConditionValueType.LowestValue;
  colorScale.Criteria[0].Value = "0";

  colorScale.Criteria[1].FormatColorRGB = Syncfusion.Drawing.Color.FromArgb(244, 210, 178);
  colorScale.Criteria[1].Type = ConditionValueType.Percentile;
  colorScale.Criteria[1].Value = "50";

  colorScale.Criteria[2].FormatColorRGB = Syncfusion.Drawing.Color.FromArgb(245, 247, 171);
  colorScale.Criteria[2].Type = ConditionValueType.HighestValue;
  colorScale.Criteria[2].Value = "0";

  #endregion

  conditionalFormats = marker.CreateConditionalFormats(worksheet["E5"]);

  #region IconSet

  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;

  #endregion

  //Add collection to the marker variables where the name should match with input template
  marker.AddVariable("Reports", GetSalesReports());

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

  //Saving the workbook as stream
  workbook.Version = ExcelVersion.Excel2013;
  MemoryStream outputStream = new MemoryStream();
  workbook.SaveAs(outputStream);

  //Save the stream as Excel document and view the saved document
  
  //The operation in SaveAndView under Xamarin varies between Windows Phone, Android and iOS platforms. Please refer xlsio/xamarin section for respective code samples.
  if (Device.OS == TargetPlatform.WinPhone || Device.OS == TargetPlatform.Windows)
      await DependencyService.Get<ISaveWindowsPhone>().SaveAndView("TemplateMarker.xlsx", "application/msexcel", outputStream);
  else
      DependencyService.Get<ISave>().SaveAndView("TemplateMarker.xlsx", "application/msexcel", outputStream);

  //Dispose the input and output stream instances
  inputStream.Dispose();
  outputStream.Dispose();
}

The following code snippet provides supporting method and class for the previous code.

GetSalesReports Method:

c#

public IList<Sales> GetSalesReports()
{
    IList<Sales> sales = new List<Sales>();

    sales.Add(new Sales("Andy Bernard", 45000, 58000, 29));

    sales.Add(new Sales("Jim Halpert", 34000, 65000, 91));

    sales.Add(new Sales("Karen Fillippelli", 75000, 64000, -15));

    sales.Add(new Sales("Phyllis Lapin", 56500, 33600, -40));

    sales.Add(new Sales("Stanley Hudson", 46500, 52000, 12));

    return sales;
}
public class Sales
{
    public string SalesPerson { get; set; }
    public int SalesJanJun { get; set; }
    public int SalesJulDec { get; set; }
    public int Change { get; set; }

    public Sales(string name, int salesJanJun, int salesJulDec, int change)
    {
        SalesPerson = name;
        SalesJanJun = salesJanJun;
        SalesJulDec = salesJulDec;
        Change = change;
    }
}

vb

Public Function GetSalesReports() As IList(Of Sales)
  Dim sales As IList(Of Sales) = New List(Of Sales)()
  sales.Add(New Sales("Andy Bernard", 45000, 58000, 29))
  sales.Add(New Sales("Jim Halpert", 34000, 65000, 91))
  sales.Add(New Sales("Karen Fillippelli", 75000, 64000, -15))
  sales.Add(New Sales("Phyllis Lapin", 56500, 33600, -40))
  sales.Add(New Sales("Stanley Hudson", 46500, 52000, 12))
  Return sales
End Function
Public Class Sales
  Public Property SalesPerson As String
  Public Property SalesJanJun As Integer
  Public Property SalesJulDec As Integer
  Public Property Change As Integer

  Public Sub New(ByVal name As String, ByVal salesJanJun As Integer, ByVal salesJulDec As Integer, ByVal change As Integer)
    SalesPerson = name
    salesJanJun = salesJanJun
    salesJulDec = salesJulDec
    change = change
  End Sub
End Class

uwp

public IList<Sales> GetSalesReports()
{
    IList<Sales> sales = new List<Sales>();

    sales.Add(new Sales("Andy Bernard", 45000, 58000, 29));

    sales.Add(new Sales("Jim Halpert", 34000, 65000, 91));

    sales.Add(new Sales("Karen Fillippelli", 75000, 64000, -15));

    sales.Add(new Sales("Phyllis Lapin", 56500, 33600, -40));

    sales.Add(new Sales("Stanley Hudson", 46500, 52000, 12));

    return sales;
}
public class Sales
{
    public string SalesPerson { get; set; }
    public int SalesJanJun { get; set; }
    public int SalesJulDec { get; set; }
    public int Change { get; set; }

    public Sales(string name, int salesJanJun, int salesJulDec, int change)
    {
        SalesPerson = name;
        SalesJanJun = salesJanJun;
        SalesJulDec = salesJulDec;
        Change = change;
    }
}

asp.net core

public IList<Sales> GetSalesReports()
{
    IList<Sales> sales = new List<Sales>();

    sales.Add(new Sales("Andy Bernard", 45000, 58000, 29));

    sales.Add(new Sales("Jim Halpert", 34000, 65000, 91));

    sales.Add(new Sales("Karen Fillippelli", 75000, 64000, -15));

    sales.Add(new Sales("Phyllis Lapin", 56500, 33600, -40));

    sales.Add(new Sales("Stanley Hudson", 46500, 52000, 12));

    return sales;
}
public class Sales
{
    public string SalesPerson { get; set; }
    public int SalesJanJun { get; set; }
    public int SalesJulDec { get; set; }
    public int Change { get; set; }

    public Sales(string name, int salesJanJun, int salesJulDec, int change)
    {
        SalesPerson = name;
        SalesJanJun = salesJanJun;
        SalesJulDec = salesJulDec;
        Change = change;
    }
}

xamarin

public IList<Sales> GetSalesReports()
{
    IList<Sales> sales = new List<Sales>();

    sales.Add(new Sales("Andy Bernard", 45000, 58000, 29));

    sales.Add(new Sales("Jim Halpert", 34000, 65000, 91));

    sales.Add(new Sales("Karen Fillippelli", 75000, 64000, -15));

    sales.Add(new Sales("Phyllis Lapin", 56500, 33600, -40));

    sales.Add(new Sales("Stanley Hudson", 46500, 52000, 12));

    return sales;
}
public class Sales
{
    public string SalesPerson { get; set; }
    public int SalesJanJun { get; set; }
    public int SalesJulDec { get; set; }
    public int Change { get; set; }

    public Sales(string name, int salesJanJun, int salesJulDec, int change)
    {
        SalesPerson = name;
        SalesJanJun = salesJanJun;
        SalesJulDec = salesJulDec;
        Change = change;
    }
}

The following screenshot represents generated Excel file in which the conditional format is applied.

Template marker with Hyperlink

You can add hyperlink to the template marker range.

The following screenshot represents the input template, which has a template marker.

The following code snippet illustrates how to detect data type and apply number format with template marker.

c#

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

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

    //Add collection to the marker variables where the name should match with input template
    marker.AddVariable("Company", GetCompanyDetails());

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

    workbook.SaveAs("TemplateMarkerHyperlink.xlsx");
}

vb

using excelEngine As ExcelEngine = new ExcelEngine()
  Dim workbook As IWorkbook = excelEngine.Excel.Workbooks.Open("TemplateMarker.xlsx")

  'Create Template Marker Processor
  Dim marker As ITemplateMarkersProcessor = workbook.CreateTemplateMarkersProcessor()
 
  'Add collection to the marker variables where the name should match with input template
   marker.AddVariable("Company", GetCompanyDetails());

  'Process the markers and detect the number format along with the data type in the template
  marker.ApplyMarkers()

  workbook.SaveAs("TemplateMarkerHyperlink.xlsx");
End Using

uwp

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;

  //Instantiates the File Picker
  FileOpenPicker openPicker = new FileOpenPicker();
  openPicker.SuggestedStartLocation = PickerLocationId.Desktop;
  openPicker.FileTypeFilter.Add(".xlsx");
  openPicker.FileTypeFilter.Add(".xls");
  StorageFile openFile = await openPicker.PickSingleFileAsync();

  //Opens the workbook
  IWorkbook workbook = await application.Workbooks.OpenAsync(openFile);
  
  //Create Template Marker Processor
  ITemplateMarkersProcessor marker = workbook.CreateTemplateMarkersProcessor();

  //Add collection to the marker variables where the name should match with input template
  marker.AddVariable("Company", GetCompanyDetails());

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

  //Initializes FileSavePicker
  FileSavePicker savePicker = new FileSavePicker();
  savePicker.SuggestedStartLocation = PickerLocationId.Desktop;
  savePicker.SuggestedFileName = "TemplateMarkerHyperlink";
  savePicker.FileTypeChoices.Add("Excel Files", new List<string>() { ".xlsx" });

  //Creates a storage file from FileSavePicker
  StorageFile storageFile = await savePicker.PickSaveFileAsync();

  //Saves changes to the specified storage file
  await workbook.SaveAsAsync(storageFile);
}

asp.net core

//Binding data from data table is supported only from ASP.NET Core 2.0
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  FileStream fileStream = new FileStream("TemplateMarker.xlsx", FileMode.Open, FileAccess.Read);
  IWorkbook workbook = application.Workbooks.Open(fileStream);

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

  //Add collection to the marker variables where the name should match with input template
  marker.AddVariable("Company", GetCompanyDetails());

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

  //Saving the workbook as stream

  FileStream stream = new FileStream("TemplateMarkerHyperlink.xlsx", FileMode.Create, FileAccess.ReadWrite);
  workbook.SaveAs(stream);
  stream.Dispose();
}

xamarin

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Excel2013;
  Assembly assembly = typeof(App).GetTypeInfo().Assembly;
  Stream inputStream = assembly.GetManifestResourceStream("SampleBrowser.XlsIO.Samples.Template.TemplateMarker.xlsx");
  IWorkbook workbook = application.Workbooks.Open(inputStream);

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

  //Add collection to the marker variables where the name should match with input template
  marker.AddVariable("Company", GetCompanyDetails());

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

  //Saving the workbook as stream
  MemoryStream stream = new MemoryStream();
  workbook.SaveAs(stream);

  stream.Position = 0;

  //Save the document as file and view the saved document

  //The operation in SaveAndView under Xamarin varies between Windows Phone, Android and iOS platforms. Please refer xlsio/xamarin section for respective code samples.

  if (Device.OS == TargetPlatform.WinPhone || Device.OS == TargetPlatform.Windows)
  {
	Xamarin.Forms.DependencyService.Get<ISaveWindowsPhone>().SaveAndView("TemplateMarkerHyperlink.xlsx", "application/msexcel", stream);
  }
  else
  {
	Xamarin.Forms.DependencyService.Get<ISave>().SaveAndView("TemplateMarkerHyperlink.xlsx", "application/msexcel", stream);
  }
}

The following code snippet provides supporting methods and classes for the previous code.

c#

//Gets a list of company details
private List<Company> GetCompanyDetails()
{
    List<Company> companyList = new List<Company>();

    Company company = new Company();
    company.Name = "Syncfusion";
    Hyperlink link = new Hyperlink("https://www.syncfusion.com", "", "", "Syncfusion", ExcelHyperLinkType.Url, null);
    company.Link = link;
    companyList.Add(company);

    company = new Company();
    company.Name = "Microsoft";
    link = new Hyperlink("https://www.microsoft.com", "", "", "Microsoft", ExcelHyperLinkType.Url, null);
    company.Link = link;
    companyList.Add(company);

    company = new Company();
    company.Name = "Google";
    link = new Hyperlink("https://www.google.com", "", "", "Google", ExcelHyperLinkType.Url, null);
    company.Link = link;
    companyList.Add(company);

    return companyList;
}    
public class Hyperlink : IHyperLink
{
    public IApplication Application { get; }
    public object Parent { get;}
    public string Address { get; set; }
    public string Name { get; }
    public IRange Range { get; }
    public string ScreenTip { get; set; }
    public string SubAddress { get; set; }
    public string TextToDisplay { get; set; }
    public ExcelHyperLinkType Type { get; set; }
    public IShape Shape { get; }
    public ExcelHyperlinkAttachedType AttachedType { get; }
    public byte[] Image { get; set; }

    public Hyperlink(string address, string subAddress, string screenTip, string textToDisplay, ExcelHyperLinkType type, byte[] image)
    {
        Address = address;
        ScreenTip = screenTip;
        SubAddress = subAddress;            
        TextToDisplay = textToDisplay;
        Type = type;
        Image = image;
    }
}

public class Company
{
    public string Name { get; set; }
    public Hyperlink Link { get; set; }
}

vb

'Gets a list of company details
Private Function GetCompanyDetails() As List(Of Company)
    Dim companyList As List(Of Company) = New List(Of Company)()
    Dim company As Company = New Company()
    company.Name = "Syncfusion"
    Dim link As Hyperlink = New Hyperlink("https://www.syncfusion.com", "", "", "Syncfusion", ExcelHyperLinkType.Url, Nothing)
    company.Link = link
    companyList.Add(company)
    company = New Company()
    company.Name = "Microsoft"
    link = New Hyperlink("https://www.microsoft.com", "", "", "Microsoft", ExcelHyperLinkType.Url, Nothing)
    company.Link = link
    companyList.Add(company)
    company = New Company()
    company.Name = "Google"
    link = New Hyperlink("https://www.google.com", "", "", "Google", ExcelHyperLinkType.Url, Nothing)
    company.Link = link
    companyList.Add(company)
    Return companyList
End Function   
Public Class Hyperlink
    Inherits IHyperLink

    Public ReadOnly Property Application As IApplication
    Public ReadOnly Property Parent As Object
    Public Property Address As String
    Public ReadOnly Property Name As String
    Public ReadOnly Property Range As IRange
    Public Property ScreenTip As String
    Public Property SubAddress As String
    Public Property TextToDisplay As String
    Public Property Type As ExcelHyperLinkType
    Public ReadOnly Property Shape As IShape
    Public ReadOnly Property AttachedType As ExcelHyperlinkAttachedType
    Public Property Image As Byte()

    Public Sub New(ByVal address As String, ByVal subAddress As String, ByVal screenTip As String, ByVal textToDisplay As String, ByVal type As ExcelHyperLinkType, ByVal image As Byte())
        Address = address
        ScreenTip = screenTip
        SubAddress = subAddress
        TextToDisplay = textToDisplay
        Type = type
        Image = image
    End Sub
End Class

Public Class Company
    Public Property Name As String
    Public Property Link As Hyperlink
End Class

uwp

//Gets a list of company details
private List<Company> GetCompanyDetails()
{
    List<Company> companyList = new List<Company>();

    Company company = new Company();
    company.Name = "Syncfusion";
    Hyperlink link = new Hyperlink("https://www.syncfusion.com", "", "", "Syncfusion", ExcelHyperLinkType.Url, null);
    company.Link = link;
    companyList.Add(company);

    company = new Company();
    company.Name = "Microsoft";
    link = new Hyperlink("https://www.microsoft.com", "", "", "Microsoft", ExcelHyperLinkType.Url, null);
    company.Link = link;
    companyList.Add(company);

    company = new Company();
    company.Name = "Google";
    link = new Hyperlink("https://www.google.com", "", "", "Google", ExcelHyperLinkType.Url, null);
    company.Link = link;
    companyList.Add(company);

    return companyList;
}    
public class Hyperlink : IHyperLink
{
    public IApplication Application { get; }
    public object Parent { get;}
    public string Address { get; set; }
    public string Name { get; }
    public IRange Range { get; }
    public string ScreenTip { get; set; }
    public string SubAddress { get; set; }
    public string TextToDisplay { get; set; }
    public ExcelHyperLinkType Type { get; set; }
    public IShape Shape { get; }
    public ExcelHyperlinkAttachedType AttachedType { get; }
    public byte[] Image { get; set; }

    public Hyperlink(string address, string subAddress, string screenTip, string textToDisplay, ExcelHyperLinkType type, byte[] image)
    {
        Address = address;
        ScreenTip = screenTip;
        SubAddress = subAddress;            
        TextToDisplay = textToDisplay;
        Type = type;
        Image = image;
    }
}

public class Company
{
    public string Name { get; set; }
    public Hyperlink Link { get; set; }
}

asp.net core

//Gets a list of company details
private List<Company> GetCompanyDetails()
{
    List<Company> companyList = new List<Company>();

    Company company = new Company();
    company.Name = "Syncfusion";
    Hyperlink link = new Hyperlink("https://www.syncfusion.com", "", "", "Syncfusion", ExcelHyperLinkType.Url, null);
    company.Link = link;
    companyList.Add(company);

    company = new Company();
    company.Name = "Microsoft";
    link = new Hyperlink("https://www.microsoft.com", "", "", "Microsoft", ExcelHyperLinkType.Url, null);
    company.Link = link;
    companyList.Add(company);

    company = new Company();
    company.Name = "Google";
    link = new Hyperlink("https://www.google.com", "", "", "Google", ExcelHyperLinkType.Url, null);
    company.Link = link;
    companyList.Add(company);

    return companyList;
}    
public class Hyperlink : IHyperLink
{
    public IApplication Application { get; }
    public object Parent { get;}
    public string Address { get; set; }
    public string Name { get; }
    public IRange Range { get; }
    public string ScreenTip { get; set; }
    public string SubAddress { get; set; }
    public string TextToDisplay { get; set; }
    public ExcelHyperLinkType Type { get; set; }
    public IShape Shape { get; }
    public ExcelHyperlinkAttachedType AttachedType { get; }
    public byte[] Image { get; set; }

    public Hyperlink(string address, string subAddress, string screenTip, string textToDisplay, ExcelHyperLinkType type, byte[] image)
    {
        Address = address;
        ScreenTip = screenTip;
        SubAddress = subAddress;            
        TextToDisplay = textToDisplay;
        Type = type;
        Image = image;
    }
}

public class Company
{
    public string Name { get; set; }
    public Hyperlink Link { get; set; }
}

xamarin

//Gets a list of company details
private List<Company> GetCompanyDetails()
{
    List<Company> companyList = new List<Company>();

    Company company = new Company();
    company.Name = "Syncfusion";
    Hyperlink link = new Hyperlink("https://www.syncfusion.com", "", "", "Syncfusion", ExcelHyperLinkType.Url, null);
    company.Link = link;
    companyList.Add(company);

    company = new Company();
    company.Name = "Microsoft";
    link = new Hyperlink("https://www.microsoft.com", "", "", "Microsoft", ExcelHyperLinkType.Url, null);
    company.Link = link;
    companyList.Add(company);

    company = new Company();
    company.Name = "Google";
    link = new Hyperlink("https://www.google.com", "", "", "Google", ExcelHyperLinkType.Url, null);
    company.Link = link;
    companyList.Add(company);

    return companyList;
}    
public class Hyperlink : IHyperLink
{
    public IApplication Application { get; }
    public object Parent { get;}
    public string Address { get; set; }
    public string Name { get; }
    public IRange Range { get; }
    public string ScreenTip { get; set; }
    public string SubAddress { get; set; }
    public string TextToDisplay { get; set; }
    public ExcelHyperLinkType Type { get; set; }
    public IShape Shape { get; }
    public ExcelHyperlinkAttachedType AttachedType { get; }
    public byte[] Image { get; set; }

    public Hyperlink(string address, string subAddress, string screenTip, string textToDisplay, ExcelHyperLinkType type, byte[] image)
    {
        Address = address;
        ScreenTip = screenTip;
        SubAddress = subAddress;            
        TextToDisplay = textToDisplay;
        Type = type;
        Image = image;
    }
}

public class Company
{
    public string Name { get; set; }
    public Hyperlink Link { get; set; }
}

The following screenshot represents generated Excel file in which the hyperlink is added.