Setting Pivot Table Option - For Empty Cells Show
Contents
[
Hide
]
You can set different pivot table options using Aspose.Cells. One such option is “For empty cells show”. By setting this option, all empty cells in a pivot table are displayed as a specified string.
Setting Pivot Table Option in Microsoft Excel
To find and set this option in Microsoft Excel:
- Select a pivot table and right-click.
- Select PivotTable Options.
- Select the Layout & Format tab.
- Select the For empty cells show option and specify a string.
Setting Pivot Table Option Using Aspose.Cells
Aspose.Cells provides the PivotTable.DisplayNullString and PivotTable.NullString properties for setting the “For empty cells show” pivot table option.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET | |
// The path to the documents directory. | |
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType); | |
Workbook wb = new Workbook(dataDir + "input.xlsx"); | |
PivotTable pt = wb.Worksheets[0].PivotTables[0]; | |
// Indicating if or not display the empty cell value | |
pt.DisplayNullString = true; | |
// Indicating the null string | |
pt.NullString = "null"; | |
pt.CalculateData(); | |
pt.RefreshDataOnOpeningFile = false; | |
wb.Save(dataDir+ "output_out.xlsx"); |