Pivot Table Item pOSITION

Aspose.Cells Product Family
1

Hi

Can you advise how to set the position of pivot field data? For example it would look like this for Excel automation:

pvtTable.PivotFields("Cost").PivotItems("TypeA").Position = 1;
pvtTable.PivotFields("Cost").PivotItems("TypeB").Position = 2;
pvtTable.PivotFields("Cost").PivotItems("TypeC").Position = 3;

I can't seem to find a way of doing the above using Apose.Cells? Is this possible?

Thanks

2

Hi Brendan,


Thank you for contacting Aspose support.

You can change the position of the Pivot Items using the PivotItem.Move method. This method accepts an integer value and displaces the Pivot Item to new location based on the parameter passed. Please check the following code snippet & attached input/output spreadsheets for your reference.

C#

Workbook workbook = new Workbook(“D:/pivotTable_test.xls”);
Worksheet worksheet = workbook.Worksheets[1];
PivotTable table = worksheet.PivotTables[0];
table.ColumnFields[“Continent”].PivotItems[“Africa”].Move(-5);//move up
table.RowFields[“Product”].PivotItems[“Maxilaku”].Move(1);//move down
workbook.Save(“D:/output.xlsx”);

3

Hi

So it look as if "Move" shifts the item up or down the specified number of positions. How do I specify the exact position I want? For example in a large dynamically created data set I may want a specific item to move to say position 3 but I won't know its current position or how many places to move it up or down?

Thanks.

4

Hi Brendan,


Thank you for writing back.

I am afraid, you cannot specify the exact position of the Pivot Item with the current implementation of Aspose.Cells APIs. You have to use a combination of PivotItem.Index and PivotItem.Move members to the find the current position of the item and move it to any calculated position according to your application requirements.
5

Hi

It's a shame it is not possible to set the exact position you want. In any case I can't seem to get 'Move' to work properly. Depending upon which data item I try to move either nothing happens or it moves the wrong number of places. Consider the following code, in which I try to move "4H12" by 4 positions but it only moves 1 position:

Workbook wb = new Workbook(@"F:\Model_Outputs\Results\PivotTest.xlsx");

Worksheet wsPivot = wb.Worksheets.Add("pvtNew Hardware");

Worksheet wsData = wb.Worksheets["New Hardware - Yearly"];

// get the pivottables collection for the pivot sheet

PivotTableCollection pivotTables = wsPivot.PivotTables;

// add PivotTable to the worksheet

int index = pivotTables.Add("='New Hardware - Yearly'!A1:D621", "A3", "HWCounts_PivotTable");

// get the PivotTable object

PivotTable pvtTable = pivotTables[index];

// add vendor row field

pvtTable.AddFieldToArea(PivotFieldType.Row, "Vendor");

// add item row field

pvtTable.AddFieldToArea(PivotFieldType.Row, "Item");

// add data field

pvtTable.AddFieldToArea(PivotFieldType.Data, "2014");

// turn off the subtotals for the vendor row field

PivotField pivotField = pvtTable.RowFields["Vendor"];

pivotField.SetSubtotals(PivotFieldSubtotalType.None, true);

// turn off grand total

pvtTable.ColumnGrand = false;

// THIS ONLY SEEMS TO MOVE 4H12 DOWN BY 1 POSITION??

pvtTable.RowFields["Item"].PivotItems["4H12"].Move(4);

// save file

wb.Save(@"F:\Model_Outputs\Results\PivotTest2.xlsx");

I have attached a sample file, the first tab contains the data used in the pivot table, the second tab shows the pivot without calling the 'Move' method and the third tab shows the pivot when the 'Move' method is called - it only moves by 1 position.

6

Hi Brendan,


Thank you for writing back.

First of all, we have logged a Feature Request in our database under the ticket CELLSNET-43214 to analyze the feasibility of your required feature (Specifying Absolute Position for the Pivot Item). We will check if we can provide the feature equivalent to Office Automation code snippet shared in your original post.

Regarding the original problem, we have evaluated the presented scenario against the latest version of Aspose.Cells for .NET 8.3.0.3, and we are able to replicate the said problem. The Pivot Item “4H12” seems to displace only one position where as in code it has been set to move 4 places. We have logged this problem in our database under the ticket CELLSNET-43215 for further investigation & correction purposes. Please spare us little time to properly analyze the scenario to pinpoint the problem cause. In the meanwhile, we will keep you posted with updates in this regard.
7

Hi Brendan,


Thank you for your patience.

We have completed the preliminary investigation for the ticket CELLSNET-43215 logged earlier in our bug tracking system for the behavior of the PivotItem.Move method when used with your provided data source. This is actually the expected behavior because the aforesaid method changes the positions of all the PivotItems of a given PivotFiled, and not the position of PivotItems under the the same node. Please note, there are 2 nodes in the provided data source namely K11 & G14, whereas 10 PivotItems for the PivotField that are distributed among these nodes. When PivotItem.Move method is called to move the item “4H12”, it is placed correctly among it’s siblings.

Let me explain this in more detail.

  1. Please check the attachment for the “one.xlsx” that has two PivotTables in the Worksheet named “pvtNew Hardware”. The first PivotTable is original where as the second PivotTable is the result of calling PivotItems[“4H12”].Move(4). You will notice from the second PivotTable that the PivotItem “4H12” has been moved down four places. In this spreadsheet there are two PivotFields in the Rows area but the PivotField “Vendor” contains one one value; that is “K11”. Whereas the the PivotField “Item” contains ten values so all the PivotItems in the PivotField “Item” become 2nd-level nodes of “K11”.
  2. Please check the other attachment named as “two.xlsx”. This spreadsheet has two PivotTables in the worksheet “pvtNew Hardware”. The first PivotTable is original where as the second PivotTable is the result of the call PivotItems[“4H12”].Move(4). In the second PivotTable, the PivotItem “4H12” has moved down one place. Reason is the distribution of the. Please note, there are two PivotFields in the Rows area however, the PivotField “Vendor” contains two values (“K11”,”G14”). The PivotField named “Item” contains ten values so six PivotItems of the PivotField “Item” become 2nd-level nodes of “K11” and the remaining four PivotItems of the PivotField “Item” become 2nd-level nodes of “G14”.
  3. The position of PivotItem is zero based index in the PivotItems collection so the original position of “4H12” is 2. When PivotItems[“4H12”].Move(4) is called, the position of “4H12” becomes 6. The original sequence of PivotItems is “K11s”, ”G14s”, “4H12”, “5TDAA”, “CA32”, “KL32”, “AAA3”, “DIF400”, “FRE6”, “FRT 10A” that changes to “K11s”, ”G14s”, “5TDAA”, “CA32”, “KL32”, “AAA3”, “4H12”, “DIF400”, “FRE6”, “FRT 10A” because the Move method changes the positions of all the PivotItems in the PivotField “Item”. The above sequence is same for both attached spreadsheets whereas the difference is about the distribution of the items.

We have also completed the preliminary analysis of the feature request logged under the ticket CELLSNET-43214. Based on the above discussion, we can provide the means to set the absolute position of the PivotItems under a PivotField, and not the position among the siblings.
8

Hi, I can't see the attachments you refer to, did you upload them?

9

Hi Brendan,


Sorry, I have missed the attachments earlier. Now they are attached to my previous response. Please feel free to write back in case you have any questions or concerns.
10

Hi

Thanks for the explanation. I can see why the move operation in "one.xlsx" works the way it does. I'm still a little lost on how the move operation works when there is more than one node though for example in "two.xlsx". I have other examples where there is more than 2 nodes which will no doubt be even more complicated.

Ultimately I want to be able to position certain items within each vendor node independently. Is it possible for example (using a series of 'Move's or any other method) to set the following:

Set "4H12" to position 1 within the K11 node
Set "DIF400" to position 2 within the K11 node

Set "CA32" to position 1 within the G14 node
Set "AAA3" to position 2 within the G14 node

Thanks

11

Hi Brendan,


Sure, let me try your presented scenario on my end to provide you the solution. Please spare me little time to look into this matter.
12

Hi Brendan,


I am afraid, no. I have forwarded the request to the core team this morning, and discussion revealed that we may need to provide additional interface(s) to accomplish your requirement. The task is with core team now, and we will keep you posted with updates in this regard.
13

Hi Brendan,


Thank you for your patience with us.

Please check the latest release of Aspose.Cells for .NET 8.3.1.4. The API has exposed a few new properties and a method to achieve your requirement. Here are the details of these newly added APIs.

  • Added PivotItem.Position property that can be used to specify the position index in all the PivotItems regardless of the parent node.
  • Added PivotItem.PositionInSameParentNode property that can be used to specify the position index in the PivotItems under the same parent node.
  • Added PivotItem.Move(int count, bool isSameParent) method in order to move the item up or down based on the count value, where count is the number of position to move the PivotItem up or down. If the count value is less than zero, the item will be moved up where as if the count value is larger than zero, the PivotItem will move down, Boolean type isSameParent parameter specify whether the moving operation has to be performed in the same parent node or not.
  • Obsoleted the PivotItem.Move(int count) method therefore it is suggested to use the newly added method PivotItem.Move(int count, bool isSameParent) instead.

Please note, it is necessary to call the PivotTable.RefreshData and PivotTable.CalculateData methods before using PivotItem.Position, PivotItem.PositionInSameParentNode properties and PivotItem.Move(int count, bool isSameParent) method.

taylob:

Ultimately I want to be able to position certain items within each vendor node independently. Is it possible for example (using a series of 'Move's or any other method) to set the following:

Set "4H12" to position 1 within the K11 node
Set "DIF400" to position 2 within the K11 node

Set "CA32" to position 1 within the G14 node
Set "AAA3" to position 2 within the G14 node

In order to achieve your goal as quoted above, please use the newly added properties as demonstrated below.

C#

Workbook wb = new Workbook("PivotTest3.xlsx");
Worksheet wsPivot = wb.Worksheets.Add("pvtNew Hardware"); Worksheet wsData = wb.Worksheets["New Hardware - Yearly"];

//Get the pivottables collection for the pivot sheet PivotTableCollection pivotTables = wsPivot.PivotTables;
//Add PivotTable to the worksheet int index = pivotTables.Add("='New Hardware - Yearly'!A1:D621", "A3", "HWCounts_PivotTable");
//Get the PivotTable object PivotTable pvtTable = pivotTables[index];

//Add vendor row field pvtTable.AddFieldToArea(PivotFieldType.Row, "Vendor");

//Add item row field pvtTable.AddFieldToArea(PivotFieldType.Row, "Item");
//Add data field pvtTable.AddFieldToArea(PivotFieldType.Data, "2014");
//Turn off the subtotals for the vendor row field PivotField pivotField = pvtTable.RowFields["Vendor"]; pivotField.SetSubtotals(PivotFieldSubtotalType.None, true);

//Turn off grand total pvtTable.ColumnGrand = false;

//Please call the PivotTable.RefreshData() and PivotTable.CalculateData() //before using PivotItem.Position, PivotItem.PositionInSameParentNode and PivotItem.Move(int count, bool isSameParent). pvtTable.RefreshData(); pvtTable.CalculateData();

pvtTable.RowFields["Item"].PivotItems["4H12"].PositionInSameParentNode = 1; pvtTable.RowFields["Item"].PivotItems["DIF400"].PositionInSameParentNode = 2;

//As a result of using PivotItem.PositionInSameParentNode,it will change the original sort sequence, //so when you use PivotItem.PositionInSameParentNode in another parent node,you need call the method named "CalculateData" again. pvtTable.CalculateData();
pvtTable.RowFields["Item"].PivotItems["CA32"].PositionInSameParentNode = 1; pvtTable.RowFields["Item"].PivotItems["AAA3"].PositionInSameParentNode = 2;

//Save file wb.Save("D:/PivotTest3_out.xlsx");

14

Thanks, I will try the new API’s.

15

Hi

I have just tested the new API's and it worked perfectly thanks.

Just one question - in the sample code you state that pvtTable.CalculateData() must be called a second time.

Am I right in saying that CalculateData must be called before calling PositionInSameParentNode for each new parent node? For example:

// parent node 1
pvtTable.CalculateData();
pvtTable.RowFields["Item"].PivotItems["A"].PositionInSameParentNode = 1;
pvtTable.RowFields["Item"].PivotItems["B"].PositionInSameParentNode = 2;

// parent node 2
pvtTable.CalculateData();
pvtTable.RowFields["Item"].PivotItems["C"].PositionInSameParentNode = 1;
pvtTable.RowFields["Item"].PivotItems["D].PositionInSameParentNode = 2;
pvtTable.RowFields["Item"].PivotItems["E"].PositionInSameParentNode = 3;

// parent node 3
pvtTable.CalculateData();
pvtTable.RowFields["Item"].PivotItems["F"].PositionInSameParentNode = 1;

16

Hi Brendan,


Yes, your understanding is correct. You should call the PivotTable.CalculateData method every time you are making changes to the parent node.
17

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

This message was posted using Notification2Forum from Downloads module by Aspose Notifier.