Header row getting included in sorting after applying subtotal and giving wrong results

Aspose.Cells Product Family
1

Hi,

I am facing an issue while doing sorting on column after the subtotal functionality is applied.

  1. Applied sort on 1st column
  2. Applied subtotal on 2nd and 7th column
  3. Applied descending sort on 7th column
    When I try to do the step 3, the table header row is also getting included and showing the wrong results . While giving the cell range I excluded the row index of header row but still facing the issue. Can you please suggest me the solution for this.
    Attached the sample files please check and let me know the solution as soon as possible.

ThanksSortingIssue.zip (11.9 KB)

2

@Sam0309 please change the order of the sort, that will fix your issue. The method addSubTotals must look like this:

private void addSubTotals(Workbook workbook, Worksheet workSheet) {
    workbook.calculateFormula();

    //sort applied after subtotal calculation on column 7
    DataSorter sorter1 = workbook.getDataSorter();

    sorter1.setOrder1(SortOrder.DESCENDING);
    sorter1.setKey1(6);

    // Sort data in the specified data range
    CellArea cellArea1 = new CellArea();
    cellArea1.StartRow = 1;
    cellArea1.StartColumn = 0;
    cellArea1.EndRow = workSheet.getCells().getMaxDataRow() - 1;
    cellArea1.EndColumn = DATA_END_COL;
    sorter1.sort(workSheet.getCells(), cellArea1);

    workbook.calculateFormula();

    // Get the workbook datasorter object.
    DataSorter sorter = workbook.getDataSorter();

    sorter.setOrder1(SortOrder.DESCENDING);
    sorter.setKey1(0);

    // Sort data in the specified data range
    CellArea cellArea = new CellArea();
    cellArea.StartRow = ORDER_TABLE_START_ROW_INDEX + 1;
    cellArea.StartColumn = 0;
    cellArea.EndRow = DATA_END_ROW;
    cellArea.EndColumn = DATA_END_COL;
    sorter.sort(workSheet.getCells(), cellArea);

    workSheet.getCells().subtotal(cellArea, 0, ConsolidationFunction.SUM, new int[] {2, 6});

    //        int maxRow = workSheet.getCells().getMaxDataRow();

    //        for (int i = 0; i <= maxRow; i++)
    //        {
    //
    //            int rowOutlineLevel = workSheet.getCells().getGroupedRowOutlineLevel(i);
    //            if (rowOutlineLevel > 1)
    //            {
    //                workSheet.getCells().hideGroupDetail(true, i);
    //            }
    //        }
}

output.zip (7.5 KB)

3

Hi @eduardo.canal,

The above solution solves the issue for header not being in 1st row, but the ā€˜AllottedAmountā€™ column is not sorted in ā€˜descendingā€™ order. In ā€˜output.xlsxā€™ the ā€˜AllottedAmountā€™ is not in sorted order. Actually I want to follow the steps in this order:

  1. Apply sort on ā€˜StudentNameā€™ column
  2. Calculate SUM ā€˜subtotalā€™ on ā€˜Amountā€™ and ā€˜AllottedAmountā€™ columns
  3. Click number 2 so it will Collapse the group of cells
  4. Apply ā€˜descendingā€™ sort on ā€˜AllottedAmountā€™ column
  5. Click number 3 so it will Expand the group of cells
    and after applying the steps the header should be in 1st row.
    Can you please suggest me the solution for these scenarios, I tried different methods but when the ā€˜descendingā€™ sort was applied on ā€˜AllottedAmontā€™ column, the header row is causing issue other than that I got the desired result. In the sample excel sheet I have attached everything is fine except that header row, I just want that header row to be in correct place(i.e, in 1st row).

Thanks

4

@Sam0309,

Could you please share your current output Excel file (undesired results) by Aspose.Cells, and sample (updated) code that you are using. Also, share your expected Excel file which you want to achieve. We will check your issue soon. By the way, could you please perform you steps for the input dataset in MS Excel manually if you could get your desired results or not.

5

Hi @amjad.sahi,

I applied the below steps for the input dataset in MS Excel manually and I got the desired results, I am attaching the ā€˜ExpectedResultā€™ excel file that is generated with steps applied manually.
Steps applied:

  1. Apply sort on ā€˜StudentNameā€™ column
  2. Calculate SUM ā€˜subtotalā€™ on ā€˜Amountā€™ and ā€˜AllottedAmountā€™ columns
  3. Click number 2 so it will Collapse the group of cells
  4. Apply ā€˜descendingā€™ sort on ā€˜AllottedAmountā€™ column
  5. Click number 3 so it will Expand the group of cells

If I try to apply the same scenario through aspose Iā€™m not getting desired result instead the only issue Iā€™m facing is with header row, other than that every calculation is fine. Iā€™m attaching the ā€˜ActualResultā€™ excel file that is generated through Aspose, in that file I want the header row to be displayed in 1st row not in the 10th row. Except header row all the data is sorted properly.
Can you please check what is causing that and help me with the correct solution to achieve it.

ThanksSortingIssue.zip (20.2 KB)

6

@Sam0309,

Thanks for the artifacts and details.

We will look into it and get back to you soon.

7

@Sam0309
We have 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): CELLSJAVA-45402

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.

1 Like
8

Hi @John.He,

I see that the ticket(Issue ID(s): CELLSJAVA-45402) status was resolved, can I get the solution for that fix?

Thanks

9

@Sam0309,
We are pleased to inform you that your issue has been resolved. The fix will be included in our upcoming release (Aspose.Cells Java 23.6) that we plan to release in the first half of June 2023. You will be notified when the next version is released.

10

The issues you have found earlier (filed as CELLSJAVA-45402) have been fixed in Aspose.Cells for Java 23.6.