Set default selected option on PivotTable PageField

Aspose.Cells Product Family
1

Hello,


In the attached sample, I am trying to build the “Renewals” tab using Aspose.Cells. The only piece that I am hoping you can help me with is getting the PageField “LossRatio>=40%” to have its default option set to “N”. For some reason I can’t get my code to set the default value, and I’m wondering if it is due to the field being a formula field on the “Detail” tab.

Here is the code I’m using that isn’t working:

//Filter the LossRatio>=40% field

pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Page, “LossRatio>=40%”);

pivotField = pivotTable.PageFields[“LossRatio>=40%”];

pivotField.IsAutoSort = true;

pivotField.IsAscendSort = true;


pivotItems = pivotField.PivotItems;


for (int i = 0; i < pivotItems.Count; i++)

{

pivotItem = pivotItems[i];

if (pivotItem.Name != null)

{

if (pivotItem.Name.Equals(“N”))

{

pivotField.CurrentPageItem = (short)i;

}

}

}
Every time it gets to the for loop, there is only a single “null” item in the pivotItems collection.

Do you have any suggestions on how to get this working?

Thank you!!

2

Hi,


Thanks for your posting and using Aspose.Cells.

Your code looks OK. Please refresh your pivot table and it should fix your issue. Please add the following two lines at the end of your code.

pivotTable.RefreshData();
pivotTable.CalculateData();

Let us know your feedback.

3

Thank you for your response. I do already have those 2 lines of code at the end of the code, but it still isn’t working.


Whenever I reach this line of code:
pivotItems = pivotField.PivotItems;
The pivotItems.Count is always 1, but it should be at least 2. There is never anything in the collection for this PageField.

Do you have any other suggestions of what I can try?

Thank you,

–Eric

4

Hi,


Thanks for your posting and using Aspose.Cells.

Please provide us your complete runnable sample code (or sample project) that we could execute at our end as well as the expected result file which you could generate manually with MS-Excel.

We will look into this issue and help you asap. Thanks for your cooperation in this regard and have a good day.

5

I’m having trouble duplicating the issue in a sample project, so I’m wondering if something is wrong somewhere within my project. I can’t send my actual project since it is larger and does quite a bit, so I tried building a sample project which simply copies an existing spreadsheet into a new one, then adds the pivot table. When I try this, the LossRatio>=40% field is filtered as expected.


6

Can you please tell me if this method is recommended for using Aspose.Cells:


1. Open existing Excel file
2. Add a few autofilters to existing tabs
3. Add 3 or more new PivotTable tabs
4. Save the Excel file

Or, do you recommend that I save and re-open the Excel file after I create each PivotTable tab?

Thank you!
7

Hi,


Thanks for your posting and considering Aspose.Cells.

You do not need to save and re-open the excel file to resolve this issue. If you have to do this, then please provide us your sample code, we will look into it and check, if it is a bug or not.

If your workbook has formulas that your pivot table data depends on, then please also call Workbook.CalculateFormula() method before creating pivot table.

8

Excellent, that was exactly what I was missing, adding that line fixed it - thank you very much!!

9

Hi,


Good to know that your issue is sorted out by the suggested line of code. Feel free to contact us any time if you need further help or have some other issue or queries, we will be happy to assist you soon.

Thank you.
10

I actually do have 1 more question, I know that I can use the following to set the Active Sheet when opening the Excel file:

workbook.Worksheets.ActiveSheetIndex = 0;

Instead of using the integer 0, is there a method I can use to set the Active Sheet by it’s tab name?
11

Hi,


Thanks for your posting and considering Aspose.Cells.

This code should fulfill your requirement.

workbook.Worksheets.ActiveSheetIndex = workbook.Worksheets[“MySheet”].Index;

Please see the following sample code, its source Excel file and its output Excel file for your reference.

C#
Workbook workbook = new Workbook(“sample.xlsx”);

workbook.Worksheets.ActiveSheetIndex = workbook.Worksheets[“Sheet3”].Index;

workbook.Save(“output.xlsx”);

12

Perfect thank you!