Specifying the Absolute Position of the Pivot Item with C++

Sometimes, users need to specify the absolute position of the pivot items. Aspose.Cells API has exposed a few new properties and a method to achieve this requirement.

• Added PivotItem.GetPosition() property that can be used to specify the position index in all the PivotItems regardless of the parent node. Added PivotItem.GetPositionInSameParentNode() 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 positions to move the PivotItem up or down. If the count value is less than zero, the item will be moved up, whereas if the count value is larger than zero, the PivotItem will move down. The Boolean type isSameParent parameter specifies 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.

The following sample code creates a Pivot Table and then specifies the Pivot Items positions in the same parent node. You can download the source Excel and output Excel files for your reference. If you open the output Excel file, you will see the Pivot Item “4H12” is at the 0th position in parent “K11” and “DIF400” is at the 3rd position. Similarly, CA32 is at position 1 and AAA3 is at position 2.

#include <iostream>
#include "Aspose.Cells.h"
using namespace Aspose::Cells;

int main()
{
    Aspose::Cells::Startup();

    // Source directory path
    U16String srcDir(u"..\\Data\\01_SourceDirectory\\");

    // Output directory path
    U16String outDir(u"..\\Data\\02_OutputDirectory\\");

    // Create workbook
    Workbook wb(srcDir + u"source.xlsx");

    // Add new worksheet for pivot table
    WorksheetCollection worksheets = wb.GetWorksheets();
    Worksheet wsPivot = worksheets.Add(u"pvtNew Hardware");
    Worksheet wsData = worksheets.Get(u"New Hardware - Yearly");

    // Get the pivot tables collection for the pivot sheet
    PivotTableCollection pivotTables = wsPivot.GetPivotTables();

    // Add PivotTable to the worksheet
    int index = pivotTables.Add(u"='New Hardware - Yearly'!A1:D621", u"A3", u"HWCounts_PivotTable");

    // Get the PivotTable object
    PivotTable pvtTable = pivotTables.Get(index);

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

    // Add item row field
    pvtTable.AddFieldToArea(PivotFieldType::Row, u"Item");

    // Add data field
    pvtTable.AddFieldToArea(PivotFieldType::Data, u"2014");

    // Turn off the subtotals for the vendor row field
    PivotField pivotField = pvtTable.GetRowFields().Get(u"Vendor");
    pivotField.SetSubtotals(PivotFieldSubtotalType::None, true);

    // Turn off grand total
    pvtTable.SetShowColumnGrandTotals(false);

    // Refresh and calculate data before modifying pivot items
    pvtTable.RefreshData();
    pvtTable.CalculateData();

    // Set positions for specific pivot items
    pvtTable.GetRowFields().Get(u"Item").GetPivotItems().Get(u"4H12").SetPositionInSameParentNode(0);
    pvtTable.GetRowFields().Get(u"Item").GetPivotItems().Get(u"DIF400").SetPositionInSameParentNode(3);

    // Recalculate data after modifying pivot items
    pvtTable.CalculateData();

    // Set positions for additional pivot items
    pvtTable.GetRowFields().Get(u"Item").GetPivotItems().Get(u"CA32").SetPositionInSameParentNode(1);
    pvtTable.GetRowFields().Get(u"Item").GetPivotItems().Get(u"AAA3").SetPositionInSameParentNode(2);

    // Save the workbook
    wb.Save(outDir + u"output_out.xlsx");

    Aspose::Cells::Cleanup();
}

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