I hope this is a simple question, and I’m just missing something obvious. I have a pivot table in a template file that I use for a report. The pivot table has a single “Rows” field, a single “Columns” field, and 7 “Values” (Data) fields. Furthermore, I have the saved excel file already using a Top 10 filter on the last [6] field of Data fields. All I need to do is change that to the [5] field of the Data fields, and change it to a Top 20. I can easily swap the number of items using the Filter.AutoFilter.FilterTop10(int, bool, bool, int) method, but I can’t seem to understand how to change what field to use. The first value of the FilterTop10 function is supposed to be the “FieldIndex” but the only value that works here is 0, even though I’m using the 6th ordinal field in the list. Where I am now:
PivotTable pivotTable = wb.Worksheets["Top 10 (Rolling)"].PivotTables["PivotTable12"];
PivotFilter filter = pivotTable.PivotFilters[0];
// something here to make filter.AutoFilter look at a different column?
filter.AutoFilter.FilterTop10(0, true, false, 20);
pivotTable.RefreshData();
pivotTable.CalculateData();
I also have the same question for the Excel-equivalent of the “More Sort Options” menu when trying to change the same pivot table to “Sort Descending (Z to A) By:” the same column. I’ve tried to specifically target that column and change the IsAutoSort and IsAscendSort but no matter what the sorting on the pivot table stays what it’s saved as in the template.
In your template Excel file, if you have filters set on row field, then the first pivot filter is obtained by using the line of code:
PivotFilter filter = pivotTable.PivotFilters[0];
You may check how many pivot filters are there in the pivot table for your template Excel file. For example, you may get count of the pivot filters using the line of code:
int count = pivotTable.PivotFilters.Count;
So, you may get the second pivot filter using the line of code:
PivotFilter filter = pivotTable.PivotFilters[1];
If you still could not evaluate your issue, kindly do provide a sample VS.NET console application (source code without compilation errors), zip the project and post us to show the issue. Also, zip and attach your template Excel file and current output Excel file (containing the pivot table). Moreover, provide a sample Excel file containing your desired (updated) pivot table, you may create the file (containing your desired updated pivot table) in MS Excel manually. We will check your issue soon.
Thank you for the reply. I have already verified that there is only one PivotFilter on the PivotTable via the property you mentioned, and it exists on the last DataField in the table, not on a RowField. The PivotFilter.AutoFilter.FilterColumns property is readonly, which is why I posted asking how to specify a different DataField instead of the one that I set up in Excel. Your documentation on the subject is not very specific when it comes to modifying existing filters.
Please note, “0” refers to first pivot filter set taken from left. Anyways, we need your sample project and template files to evaluate your issue. So, kindly do the needful and provide the artifacts and resource files. We will check your issue soon.
I apologize in advance for the lengthy explanation here, but I’ll do my best to summarize it quickly. So if you open my attached spreadsheet, and compare the two “Top 10” tabs, you’ll see one has 4 charts, and one has 3. By default, the pivot tables at the bottom of each tab are set up to match the sorting of the lower-left chart. On the “Rolling” tab, in certain conditions I won’t bother explaining, we do not use the lower-left chart, and I remove it with Aspose, stretch out the bottom right chart, and change it to 20 items (this all works fine). However, I then need to switch that bottom pivot table to be sorted by “Rolling CPU” instead of “Rolling Delta” in descending order, as well as to make it a Top 20 instead of a Top 10, by “Rolling CPU” instead of “Rolling Delta”. This is the original question, as I need to do this in the code when I am generating the reports. I use the template file to generate over 20 different spreadsheets with different data sources, and each may or may not use that extra chart and those need to be changed as I explained above.
The worksheet “Top 10 (Rolling)” is your desired sheet which you are trying to accomplish with your desired pivot table and charts in tact? If not, kindly also provide it for our reference. I also spotted some pivot tables/charts in the first two sheets have external references or data model connections as data source. Please note, currently Aspose.Cells does not support or manipulate pivot tables or charts with data model connection or having external reference/data source. The data source of the pivot table or chart should be in the same workbook. Moreover, we appreciate if you could provide your current (complete) sample code that you are using to try to accomplish your task but got undesired results via Aspose.Cells for .NET APIs. This will help us to evaluate your issue precisely and we can figure it out soon.
The external references have no bearing on the data source of the Top 10 (Rolling) tab, but I’ve cleaned up the file and removed all of the irrelevant parts. Small charts’ pivot tables are to the right, and the source of the bottom pivot table in question is on the tab “Rolling Disbursements”.
I cannot provide a complete sample code since it is well over 4000 lines of code, but the most pertinent part is this:
for (int u = 2; u < wb.Worksheets["Rolling Disbursements"].Cells.MaxDataRow; u++)
{
Cell c = wb.Worksheets["Rolling Disbursements"].Cells[$"AC{u}"];
String errorValue = (String)c.StringValue;
if (errorValue == "") {
Chart rsxChart = wb.Worksheets["Top 10 (Rolling)"].Charts["Chart 10"];
Chart dltChart = wb.Worksheets["Top 10 (Rolling)"].Charts["Chart 8"];
rsxChart.ChartObject.X = dltChart.ChartObject.X;
rsxChart.ChartObject.Y = dltChart.ChartObject.Y;
rsxChart.ChartObject.Width += dltChart.ChartObject.Width;
rsxChart.Title.Text = "Top 20 Rolling CPU by NPM";
// Remove the chart to begin with.
wb.Worksheets["Top 10 (Rolling)"].Charts.RemoveAt(wb.Worksheets["Top 10 (Rolling)"].Charts.IndexOf(dltChart));
PivotTable pivotTable = wb.Worksheets["Top 10 (Rolling)"].PivotTables["PivotTable3"];
PivotFilter filter = pivotTable.PivotFilters[0];
filter.AutoFilter.FilterTop10(0, true, false, 20);
pivotTable.RefreshData();
pivotTable.CalculateData();
// force the bottom table to match the new bottom chart
pivotTable = wb.Worksheets["Top 10 (Rolling)"].PivotTables["PivotTable12"];
// this works to change the item count to 20, but I need to change the filter from "Rolling Delta" to "Rolling CPU" column
filter = pivotTable.PivotFilters[0];
filter.AutoFilter.FilterTop10(0, true, false, 20);
// Right here I need to change the sort to "Rolling CPU", the same way you would open the "More Sort Options..." menu in Excel and change it to sort by "Rolling CPU" descending instead of "Rolling Delta" descending
// ...?
pivotTable.RefreshData();
pivotTable.CalculateData();
RefreshWorksheet(wb.Worksheets["Top 10 (Rolling)"]);
break;
}
}
The function “RefreshWorksheet()” is as follows:
private static void RefreshWorksheet(Worksheet sheet)
{
sheet.RefreshPivotTables();
for (int i = sheet.PivotTables.Count - 1; i >= 0; i--)
{
PivotTable pt = sheet.PivotTables[i];
try
{
PivotField pivotField = pt.RowFields[0];
//Setting the field auto sort.
pivotField.IsAutoSort = false;
//Setting the field sorting in ascending order.
pivotField.IsAscendSort = true;
//Setting the field auto sort using the field itself.
pivotField.AutoSortField = -1;
pivotField.IsAscendShow = true;
pt.FieldListSortAscending = true;
pt.CustomListSort = true;
pt.RefreshDataFlag = true;
pt.RefreshData();
pt.CalculateData();
pt.RefreshDataFlag = false;
}
catch (Exception x)
{
Environment.Exit(1);
}
pt.RowFields[0].HideDetail(true);
pt.RefreshDataOnOpeningFile = true;
}
}
At a high level overview, I am simply grabbing data from a database and loading it into the Rolling Disbursements tab data then refreshing the data connections in the workbook. Afterwards, in this code, I am scanning a specific column to see if there is no data (errorValue == “”), which indicates that we need to remove the one chart I mentioned and make these changes. I resize the chart object based on the size of the other charts, change the title to say 20 instead of 10, adjust the items with FilterTop10(), and that all works. The problem is where I’ve noted before, I now need to change PivotTable12 to filter by a different column, and sort by that same column in descending order. However, whenever I try to change the filters, either the whole thing breaks, or nothing changes.
We need to investigate your issue in details. I have logged a ticket with an id “CELLSNET-51726” for your issue. We will look into the details by evaluating your code segment and sample file and try to figure it out.
Once we have an update on it, we will let you know.
Please use the following code to change the PivotFilter to sort by the [5] field of the Data fields.
Workbook wb = new Workbook("KTP Spray Bore - July 17 2022_1_Example_2.xlsx");
PivotTable pivotTable = wb.Worksheets["Top 10 (Rolling)"].PivotTables["PivotTable12"];
PivotFilter filter = pivotTable.PivotFilters[0];
Console.WriteLine(filter.MeasureFldIndex);
//change to the [5] field of the Data fields
filter.MeasureFldIndex = 5;
wb.Save("out.xlsx");
Can you try it and let us know if it works for your needs?
In this case, yes, changing the MeasureFldIndex to 5 changes the Top10Filter to the correct column. Now I just need to change the sort to match the same column. My guess is that I am somehow confusing the correct values for the pivot table filed IsAutoSort, IsAscendSort, etc. In the same example, could you demonstrate how to change the sort on that same pivot table from Rolling Delta descending to Rolling CPU descending?
Please use the following code to change the PivotFilter to sort by the [5] field of the Data fields and sort PivotField in ROWS area via the [5] field of the Data fields.
e.g. Sample code:
Workbook wb = new Workbook("KTP Spray Bore - July 17 2022_1_Example_2.xlsx");
PivotTable pivotTable = wb.Worksheets["Top 10 (Rolling)"].PivotTables["PivotTable12"];
PivotFilter filter = pivotTable.PivotFilters[0];
Console.WriteLine(filter.MeasureFldIndex);
//change to the [5] field of the Data fields
filter.MeasureFldIndex = 5;
PivotField rowField = pivotTable.RowFields[0];
Console.WriteLine(rowField.AutoSortField);
//sort by the [5] field of the Data fields
rowField.AutoSortField = 5;
wb.Save("out.xlsx");
Please try it and let us know if you still find any issue.
Unfortunately, the rowField code doesn’t seem to make any difference. The PivotFilter for Top 10 changes to Rolling CPU as expected, but implementing your code to the letter does not change the Sort to Rolling CPU.You can see in this image, after I run the code and the workbook is saved, the Sort remains Rolling Delta instead. NoChange.PNG (28.8 KB)
The code I am using:
pivotTable = wb.Worksheets["Top 10 (Rolling)"].PivotTables["PivotTable12"];
filter = pivotTable.PivotFilters[0];
//change to the [5] field of the Data fields
filter.MeasureFldIndex = 5;
PivotField rowField = pivotTable.RowFields[0];
//sort by the [5] field of the Data fields
rowField.AutoSortField = 5;
pivotTable.RefreshData();
pivotTable.CalculateData();
Just for further detail, I am checking the RowField.AutoSortField before and after saving the file. If I change the field to [5], then refresh the pivot, and immediately check, the SortField is still [5], but if I save the file, then open the file in a new workbook variable and check the same RowField.AutoSortField, it is set back to [6] (the default from my template file), even if I don’t open the file or manipulate it in any way between saving and then reopening it.
Can you use the following code to test if it gives you correct results. We tested and got the correct result:
e.g. Sample code:
Workbook wb = new Workbook("KTP Spray Bore - July 17 2022_1_Example_2.xlsx");
PivotTable pivotTable = wb.Worksheets["Top 10 (Rolling)"].PivotTables["PivotTable12"];
PivotFilter filter = pivotTable.PivotFilters[0];
Console.WriteLine(filter.MeasureFldIndex);
//change to the [5] field of the Data fields
filter.MeasureFldIndex = 5;
PivotField rowField = pivotTable.RowFields[0];
Console.WriteLine(rowField.AutoSortField);
//sort by the [5] field of the Data fields
rowField.AutoSortField = 5;
Console.WriteLine("after change: ");
Console.WriteLine(filter.MeasureFldIndex);
Console.WriteLine(rowField.AutoSortField);
wb.Save("out.xlsx");
Console.WriteLine("\noutput file: ");
Workbook book = new Workbook("out.xlsx");
PivotTable pivot = book.Worksheets["Top 10 (Rolling)"].PivotTables["PivotTable12"];
Console.WriteLine(pivot.PivotFilters[0].MeasureFldIndex);
Console.WriteLine(pivot.RowFields[0].AutoSortField);
See the output console (in the attached screenshot) for your reference. output.png (3.2 KB)
Unfortunately, it just doesn’t change the sort for me. I ran your code with the current output of my own code, and the Sort stayed the same, even though the Top10 worked.
I have thoroughly commented the majority of my project and uploaded the entire section where I generate a report, with all the other adjustments included, as well as attached the template file for your review. For obvious reasons, I have censored some of the SQL calls which I use to databind my template spreadsheet, but the core of the project is there.
It is hard to evaluate your issue based on your provided code segment. Could you please provide a runnable console program or application (source code without compilation errors that should be executed standalone). Also, kindly attach your resultant file by you generated on your end. This will help us evaluate your issue precisely to consequently figure it out soon.