How to hide (blank) data in a pivot table row

Aspose.Cells Product Family
1

Is it possible to set "(blank)" data to be hidden in a pivot table row?

If so, how?

2
Hi,

Well, there is no direct way to hide a few specific items but you can hide pivot field's items.
Please try PivotField.HideItem()

Sample code:
workbook.Worksheets[0].PivotTables[0].RowFields[0].HideItem(2,true);

Thank you.
3

I did this following line trying to hide the data section of my pivot table, it got error, saying " You can’t hide/unhide one of the…" . Does it mean HideItem doesn’t apply to Datafield? if so, why has such property available?


The line of code got error:

pivotTable.DataFields[0].HideItem(0, true);
4

Hi,


Thank you for contacting Aspose support.

The exception (CellsException: “You can’t hide/unhide one of the…”) is caused due to the reason that you cannot hide the DataField from the Pivot Table. You can manually check this in Excel as well because even Excel does not allow this action. Moreover, the DataField class is derived from PivotField class therefore it inherits all attributes of the base class including the HideItem method.

It would be appropriate that you create a new thread with your exact requirements & sample spreadsheets so we could investigate the matter to provide an alternative solution. We appreciate if you can also provide your desired results that you may create manually in Excel application to show what you exactly require.
5

Hi!

I’m facing the same problem with Aspose.Cells for Java version 18.7 and the last one 21.5.

I can reproduce the problem with the following code:

@Test
    void hideBlankTest() throws Exception {

        Workbook wb = new Workbook(getClass().getResource("/templates/hide-blank-test.xlsx").getPath());
        Worksheet sheet = wb.getWorksheets().get("Sheet2");
        PivotTable pivot = sheet.getPivotTables().get("PivotTable1");
        pivot.refreshData();
        pivot.calculateData();

        PivotFieldCollection collection = pivot.getRowFields();
        for (int i = 0; i < collection.getCount(); i++) {
            PivotField field = collection.get(i);
            for (String item : field.getItems()) {
                if (item.equals("(blank)")) {
                    field.hideItem(item, true);
                }
            }
        }
        wb.save("hide-blank-test-result.xlsx", com.aspose.cells.SaveFormat.XLSX);
    }

If I debug, it correctly hit the line field.hideItem(item, true); but in the resulting Excel file the “(blank)” row is still there.

Here there are input and output file:
hide-blank-test.zip (21.3 KB)

So is there a way to hide “(blank)” row in pivot table?

Thank you.

6

@kylanee,
We have observed this issue and logged it in our database for further investigation. You will be notified here once any update is ready for sharing.

This issue is logged as:
CELLSJAVA-43464 - PivotField.hideItem() does not take effect in the output file

1 Like
7

@kylanee,
Please try our latest version/fix: Aspose.Cells for Java v21.5.2 (attached)
aspose-cells-21.5.2-java.zip (7.3 MB)
The sample code in Java:

Workbook wb = new Workbook(filePath + "hide-blank-test.xlsx");
    	Worksheet sheet = wb.getWorksheets().get("Sheet2");
    	PivotTable pivot = sheet.getPivotTables().get("PivotTable1");
    	pivot.refreshData();
    	pivot.calculateData();
    	PivotFieldCollection collection = pivot.getRowFields();
    	for (int i = 0; i < collection.getCount(); i++)
    	{
    	    PivotField field = collection.get(i);
    	    for (String item : field.getItems())
    	    {
    	        if (item.equals("(blank)"))
    	        {
    	            field.hideItem(item, true);
    	        }
    	    }
    	}    	
    	//After hiding the data, you need to refresh and calculate the PivotTable again
        pivot.refreshData();
        pivot.calculateData();
        //Or refresh and calculate the PivotTable automatically
        //pivot.setRefreshDataOnOpeningFile(true);        
    	wb.save(filePath + "out_java.xlsx", com.aspose.cells.SaveFormat.XLSX);

Your issue should be fixed now.
Let us know your feedback.

8

The issues you have found earlier (filed as CELLSJAVA-43464) have been fixed in this update.