GroupBy Excel function

Aspose.Cells Product Family
1

Is it on the roadmap to support the Excel groupby function?
Please add.

Thanks,
Dean

2

@Moonglum

Cause

Currently, the Aspose.Cells library does not support the Excel GROUPBY function directly. However, you can achieve similar functionality using smart markers for grouping data in your Excel reports.

Solution

To group data in Aspose.Cells, you can utilize smart markers with specific grouping parameters. This allows you to create summary rows for each group of records, similar to what the GROUPBY function does in Excel.

Code

Here’s a simple example of how to implement grouping using smart markers:

// Create a connection object, specify the provider info and set the data source.
OleDbConnection con = new OleDbConnection("provider=microsoft.jet.oledb.4.0;data source=Northwind.mdb");

// Open the connection object.
con.Open();

// Create a command object and specify the SQL query.
OleDbCommand cmd = new OleDbCommand("Select * from [Order Details]", con);

// Create a data adapter object.
OleDbDataAdapter da = new OleDbDataAdapter();
da.SelectCommand = cmd;

// Create a dataset object.
DataSet ds = new DataSet();
da.Fill(ds, "Order Details");

// Create a datatable with respect to dataset table.
DataTable dt = ds.Tables["Order Details"];

// Create WorkbookDesigner object.
WorkbookDesigner wd = new WorkbookDesigner();
wd.Workbook = new Workbook("SmartMarkerDesigner.xls");

// Set the datatable as the data source.
wd.SetDataSource(dt);

// Process the smart markers to fill the data into the worksheets.
wd.Process(true);

// Save the excel file.
wd.Workbook.Save("outSmartMarker_Designer.xls");

In this example, you can use smart markers like &=Order Details.OrderID(group:merge,skip:1) to group your data effectively.

Next Steps

If you would like to see the GROUPBY function added to Aspose.Cells, I recommend submitting a feature request through our support channels. Meanwhile, you can explore the smart markers feature for grouping data in your reports.

Please let me know if you need further assistance!

3

@Moonglum,

Aspose.Cells’ formula calculation engine have not yet supported GROUPBY formula/function.
We have already opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.

Issue ID(s): 
CELLSNET-57270: Support GROUPBY and PIVOTBY cell formulas/functions

You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.

Please note, we have not scheduled it yet, but we will look into the possibility of sharing a roadmap after analyzing it thoroughly. Rest assured, we will provide you with an update here as soon as we have more information or we make inroads.

4

@Moonglum,

To update you on your ticket, please note, the two functions are quite complex due to numerous options, varying logic, scenarios, and implementations they require. To address this, we need to implement them step by step. Currently, we are focusing on the GROUPBY function and aim to provide support for it by the first quarter of 2026. The PIVOTBY function, however, is even more complex than GROUPBY. That said, we anticipate that some implementations or algorithms developed for GROUPBY can be reused for PIVOTBY, which should help reduce investigation time. At this stage, we cannot provide a specific ETA for PIVOTBY, but rest assured, we are committed to working hard to deliver its support as soon as possible, especially since it has been prioritized through your Enterprise Support ticket.