Is it on the roadmap to support the Excel groupby function?
Please add.
Thanks,
Dean
Is it on the roadmap to support the Excel groupby function?
Please add.
Thanks,
Dean
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!
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.