Updating Slicer with C++

Possible Usage Scenarios

Updating a slicer means filtering the data of the pivot table or table that the slicer is connected to. When you select or unselect slicer items in Microsoft Excel, the linked pivot table or table is recalculated accordingly. With Aspose.Cells for C++, you do not need to iterate the slicer cache items and refresh the slicer manually — the Slicer.SelectItems(Vector<U16String>, bool) method updates the slicer and recalculates the linked pivot table or table directly from the filter items:

  • Slicer.SelectItems(labels, append) — selects the specified labels and recalculates the linked pivot table or table. When append is true, the originally selected labels are kept and the specified labels are added to the selection; when append is false, all original selections are cleared and only the specified labels stay selected.
  • Slicer.UnselectItems(labels) — unselects part of the currently selected items.
  • Slicer.ClearFilter() — removes the filter and selects all items.

How to Update Slicer

The following sample code loads the sample Excel file that contains an existing slicer connected to a pivot table. It selects only the “Blue” and “Pink” items of the slicer with Slicer.SelectItems(labels, false) — the pivot table is recalculated directly from the filter items, without a manual Slicer.Refresh() call. It then saves the workbook as the output Excel file. The following screenshot shows the effect of the sample code on the sample Excel file. As you can see in the screenshot, the pivot table shows only the filtered items. todo:image_alt_text

Sample Code

Replace the current selection (only the specified labels stay selected) and recalculate the linked pivot table or table:

#include <iostream>
#include "Aspose.Cells.h"
using namespace Aspose::Cells;
int main()
{
    Aspose::Cells::Startup();
    // Load sample Excel file containing slicer.
    U16String inputFilePath = u"sampleUpdatingSlicer.xlsx";
    Workbook wb(inputFilePath);
    // Access first worksheet.
    Worksheet ws = wb.GetWorksheets().Get(0);
    // Access the first slicer inside the slicer collection.
    Slicer slicer = ws.GetSlicers().Get(0);
    // Select only "Blue" and "Pink"; the linked pivot table or table
    // is recalculated directly from the filter items.
    Vector<U16String> labels{ u"Blue", u"Pink" };
    slicer.SelectItems(labels, false);
    // Save the modified workbook.
    U16String outputFilePath = u"out.xlsx";
    wb.Save(outputFilePath);
    std::cout << "Slicer updated and workbook saved successfully!" << std::endl;
    Aspose::Cells::Cleanup();
    return 0;
}

Keep the currently selected labels and additionally select more items:

// Keep the current selection and add "Red" to it.
Vector<U16String> moreLabels{ u"Red" };
slicer.SelectItems(moreLabels, true);

Unselect part of the selected items:

// Remove "Pink" from the current selection.
Vector<U16String> toRemove{ u"Pink" };
slicer.UnselectItems(toRemove);

Remove the filter (select all items):

// Clear the filter; all slicer items become selected again.
slicer.ClearFilter();