Setting Pivot Table Option - For Empty Cells Show

Introduction

You can set different pivot table options using Aspose.Cells. One such option is “For empty cells show”. Setting this option means that all empty cells in the pivot table are displayed with a specified string.

To find and set this option in Microsoft Excel:

1. Select a pivot table and right-click it.
2. Select PivotTable Options.
3. Select the Layout & Format tab.
4. Select the For empty cells show option and specify a string.

How to Setting Pivot Table Option - For Empty Cells Show

Aspose.Cells provides the PivotTable.setDisplayNullString() and PivotTable.setNullString() properties for setting the “For empty cells show” pivot table option.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getDataDir(SettingPivotTableOption.class);
Workbook wb = new Workbook(dataDir + "input.xlsx");
PivotTable pt = wb.getWorksheets().get(0).getPivotTables().get(0);
// Indicating if or not display the empty cell value
pt.setDisplayNullString(true);
// Indicating the null string
pt.setNullString("null");
pt.calculateData();
pt.setRefreshDataOnOpeningFile(false);
wb.save(dataDir + "output.xlsx");