Expand pivot table fields Java

Aspose.Cells Product Family
1

I wrote this code and use methods “setHide” for expanding but it doesn’t work for.
package com.epam.organizer;

import com.aspose.cells.*;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

public class ChartExpand {

public static void main(String[] args) {
    new ChartExpand().method("PivotFields.xlsm");
}

public void method(String file) {

    ArrayList<List<String>> list = new ArrayList<>();
    Workbook workbook = null;
    try {
        workbook = new Workbook(file);
    } catch (Exception e) {
        e.printStackTrace();
    }
    Worksheet sheet = workbook.getWorksheets().get("Pivot Table");
    PivotTable pivotTable = sheet.getPivotTables().get(0);

    int rowFieldCount = pivotTable.getRowFields().getCount();

// PivotTable
for (int i = 0; i < rowFieldCount; i++) {
ArrayList list1 = new ArrayList<>();
PivotFieldCollection pivotFields = pivotTable.getRowFields();
// PivotFields
PivotField pivotField = pivotFields.get(i);
boolean isHidden1 = pivotField. isHiddenItem(i);
if (isHidden1) {
pivotField.hideDetail(false);
}

        List<String> list2 = Arrays.asList(pivotField.getItems());

        for (int k = 1; k < pivotField.getPivotItems().getCount(); k++) {
            PivotItem pivotItem = pivotField.getPivotItems().get(k);

            String string = pivotItem.getStringValue();
            list1.add(string);
            boolean isHidden = pivotItem.isHidden();
            if (isHidden) {
                pivotItem.setHidden(false);
            }
        }
            list.add(list1);
    }

    try {
        workbook.save(file);
    } catch (Exception e) {
        e.printStackTrace();
    }
}

}

2

@naz1719

Thanks for using Aspose APIs.

Please try one of the following methods and it should fix your issue.

  • pivotField.hideDetail(isHiddenDetail);
  • pivotField.hideItem(index, isHidden);
  • pivotField.hideItemDetail(index, isHiddenDetail);

Let us know your feedback.

3

It’s also doesn’t work.

4

@naz1719

Thanks for using Aspose APIs.

Please provide us your sample Excel file e.g. PivotFields.xlsm, your actual output Excel file and your expected output Excel file for our investigation. You can create your expected Excel file by opening your actual Excel file in Microsoft Excel and fix it manually.

5

https://drive.google.com/drive/u/0/folders/1HRuUDvgbtLJtO_flCFY0i7lmqkWKXVv9
this is an sample of excel, but for my task i use another but so similar.

I tryed to represent like a macros in java fromat, as wrote in this Expand and Collapse Entire Pivot Table Fields - VBA Macro - Excel Campus

What i wold like.
Actual file
image.png (3.3 KB)
After execution of the program i would like to see all fields expanded.
image.png (20.5 KB)

6

@naz1719

Thanks for using Aspose APIs.

We were able to observe this issue as per your description. Probably, the issue is occurring because of grouping. We have logged the issue in our database for investigation and for a fix. Once, the issue is resolved or we have some other news for you, we will update you asap.

This issue has been logged as

  • CELLSJAVA-42629 - Expand or Collapse Pivot Fields when it contains grouping
7

@naz1719

Please see the following code which is correct. You should read this comment inside the code for better understanding. The comment explains the root cause of this issue.

You need to comment this code, because the source file does not contain the data source of PivotTable, so you cannot refresh data.

C#

Workbook wb = new Workbook(filePath + "PivotFields.xlsm");

Worksheet ws = wb.Worksheets[0];

PivotTable pt = ws.PivotTables[0];

int rowFieldsCount = pt.RowFields.Count;
for (int i = 0; i < rowFieldsCount; i++)
{
    //Expand PivotField 
    pt.RowFields[i].HideDetail(false);
}

pt.RefreshDataFlag = false;
//You need to comment this code, because the source file does not contain the data source of PivotTable, so you cannot refresh data.
//pt.RefreshData(); 
pt.CalculateData();
pt.RefreshDataFlag = true;

wb.Save(filePath + "out.xlsm");