Disabling pivot table ribbon

Aspose.Cells Product Family
1

In VBA, there is a way to disable the pivot table UI options and make the ribbon tabs disappear. I tried to use what I thought was the same functionality in Aspose Cells:

    pt.setEnableFieldList(false);
    pt.setEnableFieldDialog(false);
    pt.setEnableDrilldown(false);
    pt.setEnableWizard(false);
    pt.setEnableDataValueEditing(false);

This does seem to disable the features mentioned, but the tabs still appear in the ribbon. Does Aspose support disabling these ribbon tabs?

2

@wtchang,

Thanks for the details.

Please provide us your template files (input (if any) and output file) and paste your complete Java program (runnable) to reproduce the issue, we will check it soon. Also, provide a sample file (containing your desired PivotTable) where its underlying UI options are disabled, you may create the file manually in MS Excel. Moreover, share some screenshots to highlight the issue.

3

Attached the files. pivot_table_test.xlsx is the template file to open, asposequery.xlsx is the output of the program, and pivot_table_test_ui_disabled.xlsx is the desired output.
Screen Shot 2018-07-10 at 3.31.54 PM.png (29.4 KB)
Screen Shot 2018-07-10 at 3.31.35 PM.png (48.9 KB)

4

@wtchang

Thanks for using Aspose APIs.

We have looked into your issue and found that you have not provided us the sample Excel files and only provided their screenshots. Please provide those Excel files instead of screenshots so that we could look into it further.

5

aspose query.zip (65.0 KB)
Sorry about that, here they are

6

@wtchang,

Thank you for sharing the sample files. We are analyzing these files and require more information. Please provide the detailed steps to enable/disable this pivot table ribbon as these steps are important for our analysis. We want to enable/disable this ribbon ourselves here for our tesing.

7

I found a VBA macro online to do it here. To enable, I assume you would set everything to true instead of false.

8

@wtchang

Thanks for using Aspose API.

We were able to observe your requirement and logged it in our database for investigation and implementation if possible. Once, we will have some fix or other news for you, we will update you asap.

This issue has been logged as

  • CELLSJAVA-42680 - Disabling Pivot Table Ribbon
9

@wtchang

Please try the following code for your needs. It will fix your issue as we have tested it. Let us know your feedback.

Java

Workbook wb = new Workbook(dirPath + "book1.xlsx");
PivotTable pt = wb.getWorksheets().get(0).getPivotTables().get(0);
pt.setEnableFieldList(false);
pt.setShowDrill(false);
wb.save(dirPath + "dest.xlsx");
10

I tried this and it is not disabling the ribbon, but I am also not using the most recent version of Aspose Cells. Is it possible that that will change its behavior?

11

@wtchang,

Please try our latest version/fix: Aspose.Cells for Java v18.7.x.

If you still find any issue using v18.7.x, please share the sample code and template file to reproduce the issue, we will check it soon.

Please note, we cannot evaluate issues in older versions, the fixes are based on latest APIs set only. It is quite possible that in older version the feature might not be supported or might have an issue. In newer versions, we have included more enhancements regarding PivotTables. So we recommend you to kindly try using latest version of the product.

12

@wtchang

After further evaluation, we did find the issue (logged earlier as “CELLSJAVA-42680”) and fixed it. We will soon provide you the fixed version after performing QA and incorporating other enhancements and fixes. Using the new fix (upcoming) you will use the following sample code (The code is in .NET, you may use relevant APIs in Java though):
e.g
Sample code:

    Workbook wb = new Workbook(filePath + "pivot_table_test.xlsx"); 
    PivotTable pt = wb.Worksheets[0].PivotTables[0]; 
    pt.EnableWizard = false; 
    wb.Save(filePath + "out.xlsx");

Keep in touch.

13

@wtchang,

Please try our latest version/fix: Aspose.Cells for Java v18.7.3 (attached).

Please use the following sample code to test it:
e.g
Sample code:

Workbook wb = new Workbook(filePath + "pivot_table_test.xlsx");
PivotTable pt = wb.getWorksheets().get(0).getPivotTables().get(0);
pt.setEnableWizard(false);
wb.save(filePath + "out_java.xlsx");

Let us know your feedback.
Aspose.Cells for Java v18.7.3.zip (6.3 MB)

14

The issues you have found earlier (filed as CELLSJAVA-42680) have been fixed in Aspose.Cells for Java 18.8. You can also get the latest Aspose.Cells for Java version from Maven repos. with simple configurations. Please see the document for your reference: Installation|Documentation

This message was posted using BugNotificationTool from Downloads module by Amjad_Sahi