Grouped data display in excel

Aspose.Cells Product Family
1

Hi Team,


I have data in the below format in .net data table.
Data Table 1
Col1 Col2 Col3 Col4 Col5 Col6
Parent1 Child1 SubChild1 100 20 10
Parent1 Child1 SubChild2 45 4 1
Parent1 Child1 SubChild3 34 3 67
Parent1 Child1 SubChild4 56 45 99
Parent1 Child2 SubChild5 44 65 34
Parent1 Child2 SubChild6 78 2 54

In the above data table, I need to group data by parent and child for sub Child data. I.e. the above data table should display in the below format.

Data Table 2
Col1 Col2 Col3 Col4 Col5 Col6
Parent1 Child1 SubChild1 100 20 10
SubChild2 45 4 1
SubChild3 34 3 67
SubChild4 56 45 99
Child2 SubChild5 44 65 34
SubChild6 78 2 54

How can i display the Data Table 1 data in the Data table 2 format using Aspose.Cells.

Thanks,
Jithendra.
2

Hi Jithendra,


Thank you for contacting Aspose support.

You can accomplish this goal while using the grouping feature of Smart Markers. Please check the following piece of code as well as attached designer & resultant spreadsheets for your reference.

C#

//Create sample DataTable
var table = new DataTable(“data”);
//Add columns to the newly created DataTable while specifying the column type
table.Columns.Add(“Col1”, typeof(string));
table.Columns.Add(“Col2”, typeof(string));
table.Columns.Add(“Col3”, typeof(string));
table.Columns.Add(“Col4”, typeof(int));
table.Columns.Add(“Col5”, typeof(int));
table.Columns.Add(“Col6”, typeof(int));

//Add some rows with data to the DataTable
table.Rows.Add(“Parent 1”, “Child 1”, “SubChild 1”, 10, 15, 20);
table.Rows.Add(“Parent 1”, “Child 1”, “SubChild 2”, 20, 15, 20);
table.Rows.Add(“Parent 1”, “Child 2”, “SubChild 3”, 30, 15, 20);
table.Rows.Add(“Parent 1”, “Child 2”, “SubChild 4”, 40, 15, 20);
table.Rows.Add(“Parent 2”, “Child 1”, “SubChild 5”, 50, 15, 20);
table.Rows.Add(“Parent 2”, “Child 1”, “SubChild 6”, 60, 15, 20);
table.Rows.Add(“Parent 2”, “Child 2”, “SubChild 7”, 70, 15, 20);
table.Rows.Add(“Parent 2”, “Child 2”, “SubChild 8”, 80, 15, 20);

//Create an instance of WorkbookDesigner class
var designer = new WorkbookDesigner();

//Assign the Workbook property to the instance of Workbook created in first step
designer.Workbook = new Workbook(dir + “template.xlsx”);

//Set the data source
designer.SetDataSource(table);

//Call Process method to populate data
designer.Process();

//Save result on disc
designer.Workbook.Save(dir + “output.xlsx”);

3

Hi Babar Raza,


Thank you for your quick response. I have one more question. I would like to apply this styling for different work sheets of a work book and each work sheet will have different columns. Then how to proceed with this.

All the columns in my work sheets are dynamic. I.e. the column names are not fixed, then how to proceed with this. What is the best way to create the template.xlsx. It seems like we can’t skip any column name in the template.xlsx. Then how can I apply the format for different work sheets with different columns.

But in my all work sheets the column name for which I want to apply the grouping is same but remaining columns are dynamic.

Please suggest me the the better approach for this.

Thanks,
Jithendra.
4

Hi Babar Raza,


Please find the below additional information for the previous question.

If I am creating work book with 2 work sheets. For example the data source of the 1st work sheet contains the columns like Colum1, Colum2, Colum3, AAA1,BBB1,CCC1,DDD1,EEE1. The data source of the 2nd work sheet contains the columns like Colum1, Colum2, Colum3, XXX1,YYY1,ZZZ1,

In this work book I have two work sheets which contains the different columns count with different column names. Only the common column names are Column1, Column2 and Column3. Remaining columns are dynamic columns whose names will be changed dynamically in the data source.

Now I would like to apply the grouping on column1, Column2 and Column3. But the issue here is the smart marker template xlsx should be created with all column names, But here different work sheets has different column names, Then how to apply the grouping.

Is there any way in Smart Markers, where we can apply the grouping only for specific column and ignore the remaining columns.

Please suggest us.

Thanks,
Jithendra.
5

Hi Jithendra,


Based on your recent comments, I would suggest you to create the template dynamically in your code. This way, you can have the same number of columns as per your data source as well as the Smart Markers will correspond to the columns names. Regarding the formatting/styling, you can apply the style to your template as well as the final Workbook. Please check the following piece of code which tries to accomplish your goal of generating dynamic template as well as a few useful article links at the bottom of this post.

Please note, the code is for demonstration purposes, and you should amend it according to you application requirements.

C#

// Create two objects of DataTable for dummy data
DataTable table1 = new DataTable(“patients”);
table1.Columns.Add(“name”);
table1.Columns.Add(“id”);
table1.Rows.Add(“Sam”, 1);
table1.Rows.Add(“Mark”, 2);
table1.Rows.Add(“David”, 3);
table1.Rows.Add(“John”, 4);
table1.Rows.Add(“Abraham”, 5);

DataTable table2 = new DataTable(“medications”);
table2.Columns.Add(“id”);
table2.Columns.Add(“medication”);
table2.Rows.Add(1, “Atenolol”);
table2.Rows.Add(2, “Amoxicillin”);

// Create a DataSet and put both tables in it.
DataSet set = new DataSet(“records”);
set.Tables.Add(table1);
set.Tables.Add(table2);

// Create Template
Workbook book = new Workbook();
Worksheet sheet = book.Worksheets[0];
sheet.Name = “default”;

// Create Style
Style style = book.CreateStyle();
style.VerticalAlignment = TextAlignmentType.Center;
style.HorizontalAlignment = TextAlignmentType.Center;
style.Font.Color = Color.Red;
style.Font.Size = 10;

// Insert Smart Markers
for (int tab = 0; tab < set.Tables.Count; tab++)
{
DataTable table = set.Tables[tab];
sheet = book.Worksheets.Add(table.TableName);

for (int col = 0; col < table.Columns.Count; col++)
{
DataColumn column = table.Columns[col];
sheet.Cells[0, col].PutValue(“&=” + table.TableName + “.” + column.ColumnName);
}

// Apply style
sheet.Cells.CreateRange(0, 0, sheet.Cells.MaxDataRow + 1, sheet.Cells.MaxDataColumn + 1).ApplyStyle(style, new StyleFlag() { All = true });
}
// Remove default worksheet
book.Worksheets.RemoveAt(“default”);

// Save the Template on disc just to review it
book.Save(dir + “output-template.xlsx”);

// Process the designer against data source
// Create an instance of WorkbookDesigner class
WorkbookDesigner designer = new WorkbookDesigner();

// Assign the Workbook property to the instance of Workbook created in first step
designer.Workbook = book;

// Set the data source
designer.SetDataSource(set);

// Call Process method to populate data
designer.Process();

// Autofit columns
foreach (Worksheet processedSheet in designer.Workbook.Worksheets)
{
processedSheet.AutoFitColumns();
}

// Save result on disc
designer.Workbook.Save(dir + “output-final.xlsx”);


Please go through the following articles on how to format the data.

6

Is there any way to do this without using ADO Datasets / DataTables, i.e. with custom objects?

7

Hi John,


First of all, we humbly request you to always create new threads with proper problem summary and description. Moreover, the automated notifications regarding a problem are sent to the registered email address of the thread owner only.

Regarding your concerns, yes, you can use the custom objects with Smart Markers. Please check the following snippet that makes use of a list of objects from the structure Person defined at the bottom of this post.

Note: The code has been provided for demonstration purposes only therefore you should be able to amend it as per the application requirements.

C#

WorkbookDesigner myWorkbook = new WorkbookDesigner();
Worksheet curentWorksheet = myWorkbook.Workbook.Worksheets[“Sheet1”];

// Create temporary data
IList myList = new List();
myList.Add(new Person() { Name = “X”, Age = 26 });
myList.Add(new Person() { Name = “X”, Age = 32 });
myList.Add(new Person() { Name = “Y”, Age = 19 });

// Set the headers and smart markers to the XL file
curentWorksheet.Cells[“A1”].PutValue(“Name”);
curentWorksheet.Cells[“B1”].PutValue(“Age”);
curentWorksheet.Cells[“A2”].PutValue("&=Person.Name(group:merge,skip:1)");
curentWorksheet.Cells[“B2”].PutValue("&=Person.Age");

// Set the data source
myWorkbook.SetDataSource(“Person”, myList);

myWorkbook.Process();
myWorkbook.Workbook.Save(dir + “output.xls”);


struct Person
{
private String _name;
private Int32 _age;

public String Name
{
get { return _name; }
set { _name = value; }
}

public Int32 Age
{
get { return _age; }
set { _age = value; }
}
}