Pivot Drag and Drop not working with calculate fields in 24.x version

jbures · December 17, 2024, 2:54pm

Hi,

we are using Aspose Cells to build custom pivots in our app. Due to another bug, we wanted to update to 24.X version, but we are not able to since our pivots use calculated fields and the latest version breaks drag & drop pivot functionality when calculated field is added and this is essential for our users.

Replication steps:

When using version 24.12 and running attached test, the result is that the working.xlsx Excel allows user to drag and drop fields in pivot view while the not_working.xlsx does not allow this
When using version 21.9, both tests produce pivot where drag & drop works just fine (I did not test interim versions)

I have dug a bit and this can be fixed manually (by deselecting and selecting the “Classic PivotTable layout” option in PivotTable Options in Excel and in the XML (by adding compact=“0” attribute on the calculated pivot field in pivotTable1.xml). Neither of those is something we want to do and I did not manage to influence the value of this via any code I could think of so I turn to you.

In the next post, I will attach the source code and example Excels.

Best,
Jan B.

jbures · December 17, 2024, 2:56pm

package com.test;

import com.aspose.cells.*;
import org.testng.annotations.Test;

public class TestTest {
    @Test
    public void working() throws Exception {
        // New file
        Workbook workbook = new Workbook();

        // put in data
        Worksheet data = workbook.getWorksheets().add("data");
        data.getCells().get("A1").putValue("Country");
        data.getCells().get("B1").putValue("Product");
        data.getCells().get("C1").putValue("Vendor");
        data.getCells().get("D1").putValue("Value");
        data.getCells().get("E1").putValue("Units");
        data.getCells().get("A2").putValue("USA");
        data.getCells().get("B2").putValue("Car");
        data.getCells().get("C2").putValue("Ford");
        data.getCells().get("D2").putValue(1200.987654321);
        data.getCells().get("E2").putValue(3);
        data.getCells().get("A3").putValue("Japan");
        data.getCells().get("B3").putValue("Bike");
        data.getCells().get("C3").putValue("Yamaha");
        data.getCells().get("D3").putValue(985.123456789);
        data.getCells().get("E3").putValue(2);

        // create pivot
        Worksheet pivot = workbook.getWorksheets().add("pivot");
        PivotTableCollection pivotTables = pivot.getPivotTables();
        int index = pivotTables.add("=data!A1:E3", "A1", "pivotTable1");
        PivotTable pivotTable = pivotTables.get(index);
        pivotTable.setRowGrand(false);
        pivotTable.setColumnGrand(true);
        pivotTable.setGridDropZones(true);
        pivotTable.setRefreshDataOnOpeningFile(false);
        pivotTable.addFieldToArea(PivotFieldType.ROW, "Country");
        pivotTable.addFieldToArea(PivotFieldType.COLUMN, "Product");
        pivotTable.addFieldToArea(PivotFieldType.PAGE, "Vendor");
        pivotTable.addFieldToArea(PivotFieldType.DATA, "Value");
        pivotTable.addFieldToArea(PivotFieldType.DATA, "Units");

        PivotField pivotField = pivotTable.getDataFields().get("Value");
        pivotField.setNumberFormat("\"$\"#,##0.###");

        pivotField = pivotTable.getDataFields().get("Units");
        pivotField.setNumberFormat("#,##0");

        // recalculate data in pivot according to new setup
        pivotTable.calculateRange();
        pivotTable.calculateData();
        pivotTable.setPageFieldWrapCount(0);
        pivot.autoFitColumns();

        // Write
        workbook.save("working.xlsx");
    }

    @Test
    public void not_working() throws Exception {
        // New file
        Workbook workbook = new Workbook();

        // put in data
        Worksheet data = workbook.getWorksheets().add("data");
        data.getCells().get("A1").putValue("Country");
        data.getCells().get("B1").putValue("Product");
        data.getCells().get("C1").putValue("Vendor");
        data.getCells().get("D1").putValue("Value");
        data.getCells().get("E1").putValue("Units");
        data.getCells().get("A2").putValue("USA");
        data.getCells().get("B2").putValue("Car");
        data.getCells().get("C2").putValue("Ford");
        data.getCells().get("D2").putValue(1200.987654321);
        data.getCells().get("E2").putValue(3);
        data.getCells().get("A3").putValue("Japan");
        data.getCells().get("B3").putValue("Bike");
        data.getCells().get("C3").putValue("Yamaha");
        data.getCells().get("D3").putValue(985.123456789);
        data.getCells().get("E3").putValue(2);

        // create pivot
        Worksheet pivot = workbook.getWorksheets().add("pivot");
        PivotTableCollection pivotTables = pivot.getPivotTables();
        int index = pivotTables.add("=data!A1:E3", "A1", "pivotTable1");
        PivotTable pivotTable = pivotTables.get(index);
        pivotTable.setRowGrand(false);
        pivotTable.setColumnGrand(true);
        pivotTable.setGridDropZones(true);
        pivotTable.setRefreshDataOnOpeningFile(false);
        pivotTable.addFieldToArea(PivotFieldType.ROW, "Country");
        pivotTable.addFieldToArea(PivotFieldType.COLUMN, "Product");
        pivotTable.addFieldToArea(PivotFieldType.PAGE, "Vendor");
        pivotTable.addFieldToArea(PivotFieldType.DATA, "Value");
        pivotTable.addFieldToArea(PivotFieldType.DATA, "Units");

        PivotField pivotField = pivotTable.getDataFields().get("Value");
        pivotField.setNumberFormat("\"$\"#,##0.###");

        pivotField = pivotTable.getDataFields().get("Units");
        pivotField.setNumberFormat("#,##0");

        pivotTable.addCalculatedField("Price", "'Value' / 'Units'");

        // recalculate data in pivot according to new setup
        pivotTable.calculateRange();
        pivotTable.calculateData();
        pivotTable.setPageFieldWrapCount(0);
        pivot.autoFitColumns();

        // Write
        workbook.save("not_working.xlsx");
    }
}

amjad.sahi · December 17, 2024, 6:03pm

@jbures,

Thanks for the details and sample code snippets.

I tested your scenario and tried both cases using your sample code snippets. I am sorry but I could not find any issue with the output “not_working.xlsx” file. In the pivot table (of the output file), I can drag and drop fields in MS Excel manually in Field List. Please find attached the output file and let me know on how could I reproduce and spot the issue.
not_working.zip (10.7 KB)

You may also capture some screens to demonstrate the issue. This will help us to evaluate and reproduce the issue and consequently we can figure it out soon.

jbures · December 17, 2024, 8:35pm

Hi Amjad,

thanks for trying to reproduce the issue. I tried the output you uploaded and can reproduce the issue. The drag and drop does indeed work in the field list, but it does not work directly on the pivot.

In the working.xlsx, you can drag and drop the Product label from columns to rows or filter (indicated by the “moving cross” cursor when you hover over it. In the not-working one, you cannot do this, instead getting the standard “plus” cursor when you hover over it.

I have attached two short videos - a working one showing the drag and drop in action and not_working one showing the same approach on the broken pivot.

capture.zip (736.0 KB)

Let me know if I can help you further and thanks again for looking into this issue!

Best,
Jan B.

John.He · December 18, 2024, 1:39am

@jbures
By testing with sample code on the latest version v24.12, we can reproduce the issue. After adding some calculation fields to the pivot table, the drag and drop operation on the pivot table does not work.

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-46206

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.

simon.zhao · December 18, 2024, 10:48am

@jbures

If we add a pivot table in MS Excel newer version , the pivot table show as compact form by default.
So we did some improvement to add compact pivot table with the new method: public int Add(string sourceData, string cell, string tableName, bool useSameSource, bool isXlsClassic).
When isXlsClassic is true and add calculated field, we forgot to set compact as false.
We have fixed this issue. And we will include it into the next version 25.1
24.12.2.zip (10.6 KB)

jbures · December 18, 2024, 11:02am

Hi Simon,

thank you for the info and the prompt fix. I will be on the lookout for 25.1 and the included fix!

Best,
Jan B.

amjad.sahi · December 18, 2024, 11:09am

@jbures,

You are welcome. We will keep you posted with updates on the next version (once available).