Free Support Forum - aspose.com

Smart markers and multiple datasets

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 :-1:
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)

@neharai,
We have noted your requirement and will share our feedback soon.

@neharai,

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 :-1:

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.

@neharai,
Please download it from this dropbox link and let us know your feedback.

Thanks a lot , you guys are amazing, solution worked for me :slight_smile:

@neharai,

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.