We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Grouping data on particular column with group header

Aspose.Cells Product Family
#1

I went through the smarmarkers article:

http://www.aspose.com/docs/display/cellsnet/Smart+Markers

Is it possible to group the columns in run-time(instead of specifying in the template during design time)? Secondly, can we print the group headers for each group before the data for that particular group. Lets say we are grouping on “Age” column. Then the final output should look like,

Age 20
<Age = 20>
<Age = 20>
Age 21
<Age = 21>
and so on…
#2

Hi,


Sure, you may insert the grouping smart markers at runtime instead of having it in the template file. All you need to do is to put the markers using Cell.PutValue() method at your appropriate location in the Excel Worksheet’s cell(s):
e.g
Sample code:

//…
cell1.PutValue("&=[Order Details].OrderID(group:merge,skip:1)");
cell2.PutValue("&=[Order Details].UnitPrice(subtotal9:Order Details.OrderID)");
//etc.

But make sure to insert all the markers before specifying/setting and processing them, e.g before the lines such as:
//Set the datasource.

workbookdesigner.SetDataSource(t);

//Process the markers.

workbookdesigner.Process();


Regarding your other query to print Summary or grouping row at the start before its details, I am afraid, such a feature is not supported.

Thank you.


#3

Hi,


I’m using an evaluation version of Aspose.Cells for Java.
I just want to confirm that grouping rows (ie. header fields) at start before each group of details is still not supported.

For example:
I am setting up Excel template with smart markers. And I use an ICellsDataTable that has four fields: [Region, Address, Value 1, Value 2]

I want to group by region, with a header row per group as follows:
New York
123 Park Value1 = 100 Value2 = 500
456 Madison Value1 = 500 Value2 = 600

Los Angeles
123 Burbank Value1 = 500 Value2 = 900
456 Hollywood Value1 = 400 Value2 = 700

This must be a common use case, is there any ideal way to accomplish this?


#4

Hi Syed Huda,


Thanks for your query and providing some details.

Yes, your understanding is correct. Aspose.Cells’s Smart markers feature has its own format and you have to abide by certain rules/ things when specifying Smart Markers. You can add custom labels but those can be used with summary rows only, see the document for your reference here:
http://www.aspose.com/docs/display/cellsnet/Add+Custom+Labels+to+Concatenate+with+the+Subtotal+Rows+in+Smart+Markers
Please see the document on Smart Markers to know every thing about Smart Markers and different options and subsets used in it for your reference (the topic is written with .NET examples but this can be used with JAVA APIs using Aspose.Cells for Java):
http://www.aspose.com/docs/display/cellsnet/Smart+Markers

I also think that you may try to use SubTotal feature of Ms Excel if it is useful for your requirements, see the document here:
http://www.aspose.com/docs/display/cellsjava/Creating+Subtotals
(Note: you may simply place the Summary rows above the details by using the line, e.g worksheet.getOutline().SummaryRowBelow = false;)

Alternatively, you have to insert and place certain data set manually using Aspose.Cells APIs, so each header should be placed before inputting the relevant details (data) into the cells in the worksheet.

Thank you.

#5

Ok, one follow up question.


As mentioned I’m using ICellsDataTable which has an ArrayList. With the following array of strings for columns: [Region, Address, Value 1, Value 2]

When I try to group by columns with markers, it seems to not group them logically, rather it does so with the order of objects in the ArrayList:

New York 111 Park 97 26
Virginia 222 Tower 23 86
333 Ballston 29 99
Los Angeles 444 Holly 42 19
Virginia 555 Wilson 44 22
New York 666 Madison 12 73

As you can see, the New York and Virginia items are not grouped appropriately, because they are not ordered properly in the ArrayList. Do I just need to keep the ArrayList sorted properly for this to work?
#6

Hi Syed Huda,


Please make sure that you have inserted Smart Markers fine (as per the example in the docs) in the template file. Also you should input each hash map data properly while adding every row/ record to the List. If you still find the issue, kindly paste your complete Java Program (runnable) or provide .java file(s) (e.g your main Java file and other class file where you have implemented ICellsDataTable interface) and your template Excel file, so we could evaluate your issue on our end.

Thank you.