In my pivot table (see the attached screenshot) how to do via API:
1. Read that there are 2 report filter fields, their names and current values
2. Read all possible values for a filter field
3. Update a filter field value/values (if multiple values are supported). After the values is changed, do I need to refresh/recalc the pivot table before saving to PDF/HTML so the change is reflected properly?
I didn’t find this topic covered in the docs.
Thanks
I’m facing the following issue with a pivot table built against data in a table within the same workbook.
The pivot table has two report filter fields as in my screenshot. Initial state of the workbook is that Is UST = TRUE,
Execution Type=Electronic.
I run my code that opens the workbook, updates table data, CalculateFormula, RefreshData, CalculateDate, saves a XLSX, saves a PDF.
When I open the XLSX file in Excel, I see filters
Is UST = FALSE,
Execution Type=Voice
and the pivot table is displayed according to the filter values.
In PDF I see report filters displayed with original values
Is UST = TRUE,
Execution Type=Electronic,
but the pivot table is displayed as if filter values are:
Is UST = FALSE,
Execution Type=Electronic.
It seems that Aspose doesn’t preserve report filters, although Excel doesn’t seem to preserve them either. If this is expected behavior, I’d like to read, save and restore report filter values via Aspose API. But then Aspose doesn’t reflect changed values. I expect pivottable.CalculateDate() to update cells that display report filters, or should I use another method.
Hi Dmitry,
Thanks for your posting and using Aspose.Cells.
To answer your questions, I have written the following sample code. Please run it with the attached source Excel file. I have also attached the output Excel file generated by the code for your reference. The code is fully commented so you will be able to understand it easily. If you still have confusion, then please feel free to ask, we will be glad to look into your issues and help you further.
I have also shown the Debug Output of the code for your reference. Let us know your feedback.
C#
string filePath = @“F:\Shak-Data-RW\Downloads\source.xlsx”;
Workbook workbook = new Workbook(filePath);
Worksheet worksheet = workbook.Worksheets[0];
PivotTable pivotTable = worksheet.PivotTables[0];
//Reading first report filter
PivotField pf = pivotTable.PageFields[0];
//Printing its name
Debug.WriteLine("Name: " + pf.Name);
//Finding its selected item
Debug.WriteLine("Selected Item: " + pf.Items[pf.CurrentPageItem]);
//Checking if select multiple items selected
Debug.WriteLine("Is Multiple Selection Allowed: " + pf.IsMultipleItemSelectionAllowed);
//Reading second report filter
pf = pivotTable.PageFields[1];
//Printing its name
Debug.WriteLine("Name: " + pf.Name);
//Check which of the items of this pivot field are unchecked
Debug.WriteLine(“Which of the items of this pivot field are unchecked”);
for (int i = 0; i < pf.Items.Length; i++)
{
Debug.WriteLine(pf.Items[i] + ": " + pf.IsHiddenItem(i));
}
//Now check the 3rd item in the first report filter field and refresh the pivot table
pf = pivotTable.PageFields[0];
pf.CurrentPageItem = 2;
pivotTable.RefreshDataOnOpeningFile = true;
workbook.Save(“output.xlsx”);
Debug Output:Name: T2
Selected Item: 8
Is Multiple Selection Allowed: False
Name: T4
Which of the items of this pivot field are unchecked
0: True
1: True
2: True
3: False
4: False
5: False
6: True
7: True
8: True
9: True
10: True
How to implement a report based on a pivot table, say I want to generate multiple PDFs from one privot table applying different values to page pivot fields?
Do I need to reset all CurrentPageItem to 32765 first (is there a better way to set ALL), call pivottable.RefreshData()?
So then I can set new values like this:
short item=32765;
for (var i = 0 ; i < pivotField.PivotItems.Count; i++) {
if (pivotField.PivotItems[i].Name == value) {
item = i;
break;
}
}
pivotField.CurrentPageItem = item;
After I update page pivot fields, do I need run any refresh method (e.g. pivottable.RefreshData()), so the pivot itself is updated before I can save to PDF or HTML?
In the code above I switch to (All) if there is not pivot item that matches a given value. How to implement that the page pivot fields is set to the value anyway (which makes the pivot table to render empty)?
Hi Dmitry,
Thanks for your posting and using Aspose.Cells.
Yes, you got it right. You will have to call RefreshData and CalculateData respectively before saving to PDF or HTML.
Unfortunately, there is not any way other than a loop to hide/unhide (select/unselect) pivot field items. If you need it, we may consider adding such a method for your convenience.
Please see the following sample code and its source and output Excel and PDF files for your reference.
C#
string filePath = @“F:\Shak-Data-RW\Downloads\test.xlsx”;
Workbook workbook = new Workbook(filePath);
Worksheet worksheet = workbook.Worksheets[0];
PivotTable pt = worksheet.PivotTables[0];
//Select all items
pt.PageFields[0].CurrentPageItem = 32765;
//Select all items by unhiding them
for (int i = 0; i < pt.PageFields[1].PivotItems.Count; i++)
{
pt.PageFields[1].PivotItems[i].IsHidden = false;
}
pt.RefreshDataOnOpeningFile = true;
//You need to call both these APIs to save to PDF or HTML
pt.RefreshData();
pt.CalculateData();
workbook.Save(“output.pdf”);
workbook.Save(“output.xlsx”);