Working with Template Markers
28 Oct 202424 minutes to read
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
- Collection objects
- Nested collection objects
- Arrays
To quickly export data to a predefined Excel template with the .NET Excel (XlsIO) Library, please check out this video:
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
default-This argument adds the property value once per object for the corresponding records in the column while importing nested collection objects.
Syntax: %<MarkerVariable>.<Property>default
merge-This argument merges the cells in the column for each object set while importing nested collection objects.
Syntax: %<MarkerVariable>.<Property>merge
repeat-This argument repeats the property value for the corresponding records in the column while importing nested collection objects.
Syntax: %<MarkerVariable>.<Property>repeat
collapsegroup-This argument groups the cells in the column for each object set and collapse the data while importing nested collection objects.
Syntax: %<MarkerVariable>.<Property>collapsegroup
expandgroup-This argument groups the cells in the column for each object set and expand the data while importing nested collection objects.
Syntax: %<MarkerVariable>.<Property>expandgroup
NOTE
XlsIO supports only one marker for each cell while importing data from data sources to Excel templates. This is the behavior of template marker in XlsIO.
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.
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Xlsx;
FileStream inputStream = new FileStream(Path.GetFullPath(@"Data/InputTemplate.xlsx"), FileMode.Open, FileAccess.Read);
IWorkbook workbook = application.Workbooks.Open(inputStream, ExcelOpenType.Automatic);
IWorksheet worksheet = workbook.Worksheets[0];
//Create Template Marker Processor
ITemplateMarkersProcessor marker = workbook.CreateTemplateMarkersProcessor();
//Create an instance for Data table
DataTable reports = new DataTable();
//Add value to data table
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 marker variable
marker.AddVariable("Reports", reports, VariableTypeAction.DetectNumberFormat);
//Process the markers in the template
marker.ApplyMarkers();
#region Save
//Saving the workbook
FileStream outputStream = new FileStream(Path.GetFullPath("Output/ImportDataTable.xlsx"), FileMode.Create, FileAccess.Write);
workbook.SaveAs(outputStream);
#endregion
//Dispose streams
outputStream.Dispose();
inputStream.Dispose();
}
using (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");
}
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
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.
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();
}
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");
}
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
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.
The following screenshot represents the input template which has a template marker.
NOTE
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 code snippet illustrates how to detect data type and apply number format with template marker.
//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();
}
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");
}
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
A complete working example to bind data from data table to template marker in C# is present on this GitHub page.
The following screenshot represents an Excel file in which the data type is detected and then number format is applied.
Bind from Collection 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 (or) %Reports.Image;size:60,auto (or) %Reports.Image;size:auto,60 (or) %Reports.Image;size:auto,auto |
Image is applied to the specified size (width, height). Height parameter is optional. Value of width is applied when height is not specified. You can specify either width or height value as "auto" to set ratio value of other size value. If both width and height value is set as "auto" then the Image is applied to the original size |
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 collection objects.
Different positions of the image are maintained internally in the ImageVerticalPosition and ImageHorizontalPosition enumerators.
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.
Bind from Nested Collection Objects with import data and group options
You can bind the data from Nested collection objects with import data options and group options.
The import data options are:
- Default
- Merge
- Repeat
The import data group options are:
- Collapse Group
- Expand Group
The following code snippet illustrates how to import data from nested collection objects with template marker.
//Binding data from data table is supported only from ASP.NET Core 2.0
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Xlsx;
FileStream inputStream = new FileStream(Path.GetFullPath(@"Data/InputTemplate.xlsx"), FileMode.Open, FileAccess.Read);
IWorkbook workbook = application.Workbooks.Open(inputStream, ExcelOpenType.Automatic);
IWorksheet worksheet = workbook.Worksheets[0];
//Create Template Marker Processor
ITemplateMarkersProcessor marker = workbook.CreateTemplateMarkersProcessor();
//Add collection to the marker variables where the name should match with input template
marker.AddVariable("Customer", GetSalesReports());
//Process the markers in the template
marker.ApplyMarkers();
#region Save
//Saving the workbook
FileStream outputStream = new FileStream(Path.GetFullPath("Output/ImportNestedCollection.xlsx"), FileMode.Create, FileAccess.Write);
workbook.SaveAs(outputStream);
#endregion
//Dispose streams
outputStream.Dispose();
inputStream.Dispose();
}
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("Customer", GetSalesReports());
//Process the markers in the template
marker.ApplyMarkers();
workbook.SaveAs("TemplateMarkerNestedCollection.xlsx");
}
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("Customer", GetSalesReports())
'Process the markers and detect the number format along with the data type in the template
marker.ApplyMarkers()
workbook.SaveAs("TemplateMarkerNestedCollection.xlsx");
End Using
The following code snippet provides supporting methods and classes for the previous code.
//Gets a list of sales reports
public static List<Customer> GetSalesReports()
{
List<Customer> reports = new List<Customer>();
List<Order> orders = new List<Order>();
orders.Add(new Order(1408, 451.75));
orders.Add(new Order(1278, 340.00));
orders.Add(new Order(1123, 290.50));
Customer c1 = new Customer(002107, "Andy Bernard", 45);
c1.Orders = orders;
Customer c2 = new Customer(011564, "Jim Halpert", 34);
c2.Orders = orders;
Customer c3 = new Customer(002097, "Karen Fillippelli", 35);
c3.Orders = orders;
Customer c4 = new Customer(001846, "Phyllis Lapin", 37);
c4.Orders = orders;
Customer c5 = new Customer(012167, "Stanley Hudson", 41);
c5.Orders = orders;
reports.Add(c1);
reports.Add(c2);
reports.Add(c3);
reports.Add(c4);
reports.Add(c5);
return reports;
}
//Customer details
public partial class Customer
{
public int Id { get; set; }
public string Name { get; set; }
public int Age { get; set; }
public IList<Order> Orders { get; set; }
public Customer(int id, string name, int age)
{
Id = id;
Name = name;
Age = age;
}
}
//Order details
public partial class Order
{
public int Order_Id { get; set; }
public double Price { get; set; }
public Order(int id, double price)
{
Order_Id = id;
Price = price;
}
}
//Gets a list of sales reports
public static List<Customer> GetSalesReports()
{
List<Customer> reports = new List<Customer>();
List<Order> orders = new List<Order>();
orders.Add(new Order(1408, 451.75));
orders.Add(new Order(1278, 340.00));
orders.Add(new Order(1123, 290.50));
Customer c1 = new Customer(002107, "Andy Bernard", 45);
c1.Orders = orders;
Customer c2 = new Customer(011564, "Jim Halpert", 34);
c2.Orders = orders;
Customer c3 = new Customer(002097, "Karen Fillippelli", 35);
c3.Orders = orders;
Customer c4 = new Customer(001846, "Phyllis Lapin", 37);
c4.Orders = orders;
Customer c5 = new Customer(012167, "Stanley Hudson", 41);
c5.Orders = orders;
reports.Add(c1);
reports.Add(c2);
reports.Add(c3);
reports.Add(c4);
reports.Add(c5);
return reports;
}
//Customer details
public partial class Customer
{
public int Id { get; set; }
public string Name { get; set; }
public int Age { get; set; }
public IList<Order> Orders { get; set; }
public Customer(int id, string name, int age)
{
Id = id;
Name = name;
Age = age;
}
}
//Order details
public partial class Order
{
public int Order_Id { get; set; }
public double Price { get; set; }
public Order(int id, double price)
{
Order_Id = id;
Price = price;
}
}
'Gets a list of sales reports
Public Shared Function GetSalesReports() As List(Of Customer)
Dim reports As List(Of Customer) = New List(Of Customer)()
Dim orders As List(Of Order) = New List(Of Order)()
orders.Add(New Order(1408, 451.75))
orders.Add(New Order(1278, 340.00))
orders.Add(New Order(1123, 290.50))
Dim c1 As Customer = New Customer(002107, "Andy Bernard", 45)
c1.Orders = orders
Dim c2 As Customer = New Customer(011564, "Jim Halpert", 34)
c2.Orders = orders
Dim c3 As Customer = New Customer(002097, "Karen Fillippelli", 35)
c3.Orders = orders
Dim c4 As Customer = New Customer(001846, "Phyllis Lapin", 37)
c4.Orders = orders
Dim c5 As Customer = New Customer(012167, "Stanley Hudson", 41)
c5.Orders = orders
reports.Add(c1)
reports.Add(c2)
reports.Add(c3)
reports.Add(c4)
reports.Add(c5)
Return reports
End Function
'Customer details
Public Class Customer
Public Property Id As Integer
Public Property Name As String
Public Property Age As Integer
Public Property Orders As IList(Of Order)
Public Sub New(ByVal id As Integer, ByVal name As String, ByVal age As Integer)
Id = id
Name = name
Age = age
End Sub
End Class
'Order details
Public Class Order
Public Property Order_Id As Integer
Public Property Price As Double
Public Sub New(ByVal id As Integer, ByVal price As Double)
Order_Id = id
Price = price
End Sub
End Class
A complete working example to bind data from nested collections to template marker in C# is present on this GitHub page.
The output of all the import data and group options with input templates are as follows.
Default option input and output
Input template
Generated output
Merge option input and output
Input template
Generated output
Repeat option input and output
Input template
Generated output
Collapse group option input and output
Input template
Generated output
Expand group option input and output
Input template
Generated output
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.
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Xlsx;
FileStream inputStream = new FileStream(Path.GetFullPath(@"Data/InputTemplate.xlsx"), FileMode.Open, FileAccess.Read);
IWorkbook workbook = application.Workbooks.Open(inputStream, ExcelOpenType.Automatic);
IWorksheet worksheet = workbook.Worksheets[0];
//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
//Declaring Icon set for the condition
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
//Applying color for Conditional formatting
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
//Apply Icons for conditional formatting
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();
#region Save
//Saving the workbook
FileStream outputStream = new FileStream(Path.GetFullPath("Output/ConditionalFormatting.xlsx"), FileMode.Create, FileAccess.Write);
workbook.SaveAs(outputStream);
#endregion
//Dispose streams
outputStream.Dispose();
inputStream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
IWorkbook workbook = excelEngine.Excel.Workbooks.Open("TemplateMarker.xlsx");
IWorksheet worksheet = workbook.Worksheets[0];
//Create Template Marker Processor
ITemplateMarkersProcessor marker = workbook.CreateTemplateMarkersProcessor();
IConditionalFormats conditionalFormats = marker.CreateConditionalFormats(worksheet["C5"]);
//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;
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["D5"]);
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";
conditionalFormats = marker.CreateConditionalFormats(worksheet["E5"]);
condition = conditionalFormats.AddCondition();
condition.FormatType = ExcelCFType.IconSet;
iconSet = condition.IconSet;
iconSet.IconSet = ExcelIconSetType.ThreeSymbols;
iconSet.IconCriteria[0].Type = ConditionValueType.LowestValue;
iconSet.IconCriteria[0].Value = "0";
iconSet.IconCriteria[1].Type = ConditionValueType.HighestValue;
iconSet.IconCriteria[1].Value = "0";
iconSet.ShowIconOnly = false;
//Add 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();
workbook.Version = ExcelVersion.Excel2013;
workbook.SaveAs("TemplateMarkerCF.xlsx");
}
using excelEngine As ExcelEngine = new ExcelEngine()
Dim workbook As IWorkbook = excelEngine.Excel.Workbooks.Open("TemplateMarker.xlsx")
Dim worksheet As IWorksheet = workbook.Worksheets(0)
'Create Template Marker Processor
Dim marker As ITemplateMarkersProcessor = workbook.CreateTemplateMarkersProcessor()
Dim conditionalFormats As IConditionalFormats = marker.CreateConditionalFormats(worksheet("C5"))
'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
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
conditionalFormats = marker.CreateConditionalFormats(worksheet("D5"))
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"
conditionalFormats = marker.CreateConditionalFormats(worksheet("E5"))
condition = conditionalFormats.AddCondition()
condition.FormatType = ExcelCFType.IconSet
iconSet = condition.IconSet
iconSet.IconSet = ExcelIconSetType.ThreeSymbols
iconSet.IconCriteria(0).Type = ConditionValueType.LowestValue
iconSet.IconCriteria(0).Value = "0"
iconSet.IconCriteria(1).Type = ConditionValueType.HighestValue
iconSet.IconCriteria(1).Value = "0"
iconSet.ShowIconOnly = False
'Add 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()
workbook.Version = ExcelVersion.Excel2013
workbook.SaveAs("TemplateMarkerCF.xlsx")
End Using
The following code snippet provides supporting method and class for the previous code.
GetSalesReports Method:
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;
}
}
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;
}
}
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
A complete working example to import data to template marker with conditional formatting in C# is present on this GitHub page.
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.
//Binding data from data table is supported only from ASP.NET Core 2.0
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Xlsx;
FileStream inputStream = new FileStream(Path.GetFullPath(@"Data/InputTemplate.xlsx"), FileMode.Open, FileAccess.Read);
IWorkbook workbook = application.Workbooks.Open(inputStream, ExcelOpenType.Automatic);
IWorksheet worksheet = workbook.Worksheets[0];
//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();
#region Save
//Saving the workbook
FileStream outputStream = new FileStream(Path.GetFullPath("Output/HyperlinkWithMarker.xlsx"), FileMode.Create, FileAccess.Write);
workbook.SaveAs(outputStream);
#endregion
//Dispose streams
outputStream.Dispose();
inputStream.Dispose();
}
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");
}
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
The following code snippet provides supporting methods and classes for the previous code.
//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; }
}
//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; }
}
'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
A complete working example to import data to template marker with hyperlink in C# is present on this GitHub page.
The following screenshot represents generated Excel file in which the hyperlink is added.
NOTE
Explore our .NET Excel Library Feature Tour page and .Net Excel Framework demo that shows how to create and modify Excel files from C# with 5 lines of code on different platforms.