PivotTable. Missing properties


I can’t find these properties in Aspose.Cells. Could you please fix it or show me they, if such properties already exist?

Ribbon -> PivotTable Tools -> Options -> PivotTable -> Options:
Layout & Format
When in compact form indent row labels: xx character(s)

Totals & Filters
Allow multiple filters per field

Show contextual tooltips
Show properties in tooltips

Show the Values row

Show items with no data on rows
Show items with no data on columns

Field list: Sort A to Z <<–>> Sort in data source order

Print expand/collapse buttons when displayed on PivotTable

Number of items to retain per field:
Enable cell editing in the values area

Alt Text

I am using Excel 2010 and Aspose.Cells

Thank you,
Alex Shloma


Thanks for your post.

Yes, there are some properties that are not yet supported for PivotTable. I have logged a ticket for it with an id: CELLSNET-40179. We will soon look into it to support all of them for your needs. We will also provide complete details/sample code about your mentioned attributes soon.

Thank you.

This issue is supported / fixed in the latest Aspose.Cells version

Please use this sample code:


Thank you.


I have several questions:
1. Console.WriteLine(table.ShowDrill); - It’s property from tab ‘Display’ -> ‘Show expand/collapse buttons’, but I search for ‘Printing’ -> ‘Print expand/collapse buttons when displayed on PivotTable’.

2. These properties do not work:
IsMultipleFieldFilters - PivotTableOptions.xlsx, PivotTableOptions.xls
ShowDataTips - PivotTableOptions.xlsx, PivotTableOptions.xls
ShowMemberPropertyTips - PivotTableOptions2.xlsx, PivotTableOptions2.xls
EnableDataValueEditing - PivotTableOptions2.xlsx, PivotTableOptions2.xls

3. Properties that do not work in *.xls:
Indent - PivotTableOptions.xls (0 instead of 13)
FieldListSortAscending - PivotTableOptions.xls
MissingItemsLimit - PivotTableOptions.xls (Automatic instead of Max)
ShowEmptyRow - PivotTableOptions2.xls
ShowEmptyCol - PivotTableOptions2.xls

4. I found more missing properties:
Ribbon -> PivotTable Tools -> Options -> PivotTable -> Options:
Totals & Filters
Include filtered items in totals
Mark totals with *
Evaluate calculated members from OLAP server in filters
Show calculated members from OLAP server

5. These properties raise an exception while reading from some *.xls files:
RefreshDataOnOpeningFile - PivotTableOptions2.xls
MissingItemsLimit - PivotTableOptions2.xls
I created a few simple documents with Pivot Table. The properties listed above were set to true.

Workbook wb = new Workbook(fName);
foreach(Worksheet ws in wb.Worksheets)
printLine(ws.Name, string.Empty, SPACE0); // my inner printing method
foreach(PivotTable pt in ws.PivotTables)
try { pt.RefreshData(); }
catch { }
try { pt.CalculateData(); }
catch { }
try { pt.CalculateRange(); }
catch { }
printLine(“IsMultipleFieldFilters”, pt.IsMultipleFieldFilters.ToString(), SPACE2);
printLine(“ShowDataTips”, pt.ShowDataTips.ToString(), SPACE2);
printLine(“ShowMemberPropertyTips”, pt.ShowMemberPropertyTips.ToString(), SPACE2);
printLine(“EnableDataValueEditing”, pt.EnableDataValueEditing.ToString(), SPACE2);
printLine(“Indent”, pt.Indent.ToString(), SPACE2);
printLine(“FieldListSortAscending”, pt.FieldListSortAscending.ToString(), SPACE2);
try { printLine(“MissingItemsLimit”, pt.MissingItemsLimit.ToString(), SPACE2); }
catch { printLine(“MissingItemsLimit”, " ============== ERROR ============== ", SPACE2); }
printLine(“ShowEmptyRow”, pt.ShowEmptyRow.ToString(), SPACE2);
printLine(“ShowEmptyCol”, pt.ShowEmptyCol.ToString(), SPACE2);
try { printLine(“RefreshDataOnOpeningFile”, pt.RefreshDataOnOpeningFile.ToString(), SPACE2); }
catch { printLine(“RefreshDataOnOpeningFile”, " ============== ERROR ============== ", SPACE2); }
I am using Excel 2010 and Aspose.Cells

Thank you,
Alex Shloma

Hi Alex,

Apparently, I can notice the issues as you mentioned here. I have logged all of them and we will soon look into them. I have reopened your ticket again. Once we have any update on it, we will let you know here.

Thank you.


Please download: Aspose.Cells for .NET v7.0.4.3

Please use pivottable.PrintDrill

2) 3)
We cannot support these properties in Excel 2003 which are saved by Excel2007 now. We will support it later.

We cannot support the pivottable that is using OLAP now, we will support it later.

We have fixed it in the latest version: Aspose.Cells for .NET v7.0.4.3.


1. It’s always a false in *.xls (please see PivotTableOptions.xls).
2,3,4. Thank for answer. I will wait for this update.

Thank you,
Alex Shloma


Thanks for your xls file.

We have forwarded your issue to our development team and logged it. Once, we will get some update relating to it, we will let you know asap.

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

This message was posted using Notification2Forum from Downloads module by aspose.notifier.

I've checked issue with new 7.1.0 version, but this issue is still present.

Thank you,
Alex Shloma
We fixed the issue # 5) in the new release v7.1.0.
For Issues: e.g 1, 2,3,4
We could not support these properties in Excel 2003 which are saved by Excel2007 now. When we fix them, we will inform you.

Sorry for any inconvenience caused!
Hi Amjad,

Thank you! Looking forward for new update!

Best regards,
Alex Shloma

Please try our latest version/fix: Aspose.Cells for .NET v21.6.3 (attached)
Aspose.Cells21.6.3 For .Net2_AuthenticodeSigned.Zip (5.5 MB)
Aspose.Cells21.6.3 For .Net4.0.Zip (5.5 MB)
Aspose.Cells21.6.3 For .NetStandard20.Zip (5.5 MB)

Your issue should be fixed in it.
Let us know your feedback.