I am currently passing a List to WorkBookDesigner.SetDataSource and assigning it a smart marker name as follows workBookDesigner.SetDataSource(“Results”, dataSource);
I then reference the fields in an Excel template using smart markers as follows: &=[Results].FieldName
Now I want to pass more than one list of classes, say List and List. My questions are as follows:
1. How will I pass more than one list through WorkBookDesigner.SetDataSource?
2. How can I reference the two distinct lists using smart markers? Something like &=Results[0].FieldNameOfClass1 and &=Results[1].FieldNameOfClass2?
Hi,
Yes, you may specify multiple data sources for different smart markers. See the Example below for your reference:
Example code:
//…
//Instantiate the workbookdesigner object.
WorkbookDesigner report = new WorkbookDesigner();
//Get the first worksheet(default sheet) in the workbook.
Aspose.Cells.Worksheet w = report.Workbook.Worksheets[0];
//Input some markers to the cells.
w.Cells[“A1”].PutValue(“Test”);
w.Cells[“A2”].PutValue("&=Person1.Name");
w.Cells[“B2”].PutValue("&=Person1.Age");
w.Cells[“C2”].PutValue("&=Product.Quantity");
//Instantiate the list collection based on the custom class.
IList list = new List();
//Provide values for the markers using the custom class object.
list.Add(new Person1(“Jordon”, 30));
list.Add(new Person1(“Stehphen”, 33));
//Instantiate another list collection based on the custom class.
IList list2 = new List();
//Provide values for the markers using the custom class object.
list2.Add(new Product(50));
//Set the data source.
report.SetDataSource(“Person1”, list);
//Set the data source.
report.SetDataSource(“Product”, list2);
//Process the markers.
report.Process(true);
//Save the excel file.
report.Workbook.Save(“e:\test2\customobjects.xls”);
//…
//Custom class.
public class Person1
{
private string m_Name;
public string Name
{
get { return m_Name; }
set { m_Name = value; }
}
private int m_Age;
public int Age
{
get { return m_Age; }
set { m_Age = value; }
}
internal Person1(string name, int age)
{
this.m_Name = name;
this.m_Age = age;
}
}
//Custom class.
public class Product
{
private int p_quantity;
public int Quantity
{
get { return p_quantity; }
set { p_quantity = value; }
}
internal Product(int qty)
{
this.p_quantity = qty;
}
}
//…
I have the same need as above just I need data in below template ,help me to write code for this need because I am getting duplicate key for data source issue when trying to run it in foreach 
code snippet;
foreach (var department in Departments)
{
wb.SetDataSource(“Department”, string.IsNullOrEmpty(purchaseOrder?.PurchaseOrderNumber) ? string.Empty : purchaseOrder.PurchaseOrderNumber);
wb.SetDataSource(“Subject”, string.IsNullOrEmpty(purchaseOrder?.PurchaseOrderTypeCode) ? string.Empty : purchaseOrder.PurchaseOrderTypeCode);
wb.SetDataSource(“ageGroup”, string.IsNullOrEmpty(purchaseOrder?.PurchaseOrderTypeCode) ? string.Empty : purchaseOrder.PurchaseOrderTypeCode);
wb.SetDataSource(“ProfessorName”, string.IsNullOrEmpty(purchaseOrder?.PurchaseOrderTypeCode) ? string.Empty : purchaseOrder.PurchaseOrderTypeCode);
wb.SetDataSource(“StudentList”, department.Students);
}
image.png (27.5 KB)
Please share your runnable sample code (similar to what we provided in the post) and template Excel file (if any) to show the issue, we will check it soon.
PS. please zip the file(s) prior attaching.
Below is the code:-
class Program
{
static void Main(string[] args)
{
Console.WriteLine(“Hello World!”);
List departments = new List();
List students = new List();
Student student1 = new Student();
student1.EmailId = "ewe@kkk.com";
student1.Name = “Jimit”;
student1.PhoneNumber = “1213123213”;
Student student2 = new Student();
student2.EmailId = "ewe@kkk.com";
student2.Name = “Jimit”;
student2.PhoneNumber = “1213123213”;
Student student3 = new Student();
student3.EmailId = "ewe@kkk.com";
student3.Name = “Jimit”;
student3.PhoneNumber = “1213123213”;
students.Add(student1);
students.Add(student2);
students.Add(student3);
Department department1 = new Department();
department1.Name = "Science";
department1.ProfessorName = "C.V";
department1.Students = students;
Department department2 = new Department();
department2.Name = "History";
department2.ProfessorName = "CR.V";
department2.Students = students;
departments.Add(department1);
departments.Add(department2);
WorkbookDesigner designer = PrepareWorkBook(departments);
//then use this designer to generate the excel file
}
public static WorkbookDesigner MapDepartment(WorkbookDesigner wb, Department department)
{
wb.SetDataSource("Name", string.IsNullOrEmpty(department?.Name) ? string.Empty : department.Name);
wb.SetDataSource("ProfessorName", string.IsNullOrEmpty(department?.ProfessorName) ? string.Empty : department.ProfessorName);
wb.SetDataSource("EmailId", string.IsNullOrEmpty(department?.EmailId) ? string.Empty : department.EmailId);
wb.SetDataSource("Subject", string.IsNullOrEmpty(department?.Subject) ? string.Empty : department.Subject);
wb.SetDataSource("Students", department?.Students);
return wb;
}
public static WorkbookDesigner PrepareWorkBook(List<Department> departments)
{
// Create workbook and fill it with the data
WorkbookDesigner designer = new WorkbookDesigner();
//WorkbookDesigner result = new WorkbookDesigner();
designer.Workbook =
new Workbook(AppDomain.CurrentDomain.BaseDirectory + @"\" + "" );
foreach (var department in departments)
{
MapDepartment(designer, department);
}
designer.Process();
return designer;
}
}The above code gives error
Item has already been added. Key in dictionary: ‘Name’ Key being added: ‘Name’
@neharai,
Could you please share a runnable console application with template file because shared code is missing your custom class code like “Student”. We will reproduce the problem and provide our feedback after analysis.
Now the problem is its just pushing the last department to the excel:-
DepartmentTemplate.zip (29.9 KB)
TestAspose1.zip (5.9 MB)
Now the problem its just updating the excel with last department value and I need the template to get repeated for each department .
@neharai,
We have noted your requirement and have logged a ticket for it. We will write back here once any update is ready for sharing.
This issue is logged as:
CELLSNET-47888 - Appropriate SmartMarkers required to achieve the desired output
@neharai,
We only can process multiple times to support your need. See modified template file and codes:
WorkbookDesigner designer = new WorkbookDesigner();
//WorkbookDesigner result = new WorkbookDesigner();
designer.Workbook = new Workbook(dir + "DepartmentTemplate.xlsx");
Worksheet hiddenTemplate = designer.Workbook.Worksheets["HiddenBatch"];
Range sourceRange = hiddenTemplate.Cells.CreateRange("B1:I5");
Worksheet template = designer.Workbook.Worksheets["Batch PO"];
foreach (var department in departments)
{
int row = template.Cells.MaxDataRow;
if (row != 0)
{
row += 1;
}
Range targeRange = template.Cells.CreateRange(row, 0, sourceRange.RowCount, sourceRange.ColumnCount);
targeRange.Copy(sourceRange);
MapDepartment(designer, department);
designer.Process(template.Index, false);
}
designer.Workbook.Save(dir + "dest.xlsx", SaveFormat.Xlsx);
DepartmentTemplate_m.zip (35.6 KB)
Hi Team unbale to download the updated template file.
Thanks a lot , you guys are amazing, solution worked for me 
Good to know that the suggested code segment with updated template file work for your needs. Feel free to write us back if you have further queries or comments.
Hi Sir,
There is one more need with same excel that I want to add font color red for &=Students.PhoneNumber if phone number is invalid (greater than 10 chars means its invalid).
Second need is to delete the hidden template file after processing all this excel.
Thanks
This is not supported directly in Smart Markers. But you may easily cope with it using conditional formatting. Once all the markers are processed and data/value is filled into the cells, then you may simply add conditional formatting for those invalid numbers (e.g. you may use FormatConditionType.CellValue where operator type is “GreaterThan” and specifying “9999999999” as formula1, etc.). Also, see the document on how to apply conditional formattings for your reference.
You mean hidden sheet in the workbook? If true, you may use Worksheet.IsVisible attribute to get to know if a worksheet is hidden, then you may remove that sheet easily. See the document on how to remove worksheet for your reference.
Hope, this helps a bit.
hi Team,
How can I apply cell formatting on cell if the value of the cell is “some particular text” ?
e.g. in departmentTemplate excel if the emailID column contains value “%@gmail.com%” then color its font with red.