Insert Slicer with C++

Possible Usage Scenarios

A slicer is used to filter data quickly. It can be used to filter data in either a table or a pivot table. Microsoft Excel allows you to create a slicer by selecting a table or pivot table and then clicking Insert > Slicer. Aspose.Cells also allows you to create a slicer using the Worksheet.Slicers.Add() method.

Create a Slicer for a Pivot Table

Please see the following sample code. It loads the sample Excel file that contains the pivot table. It then creates the slicer based on the first base pivot field. Finally, it saves the workbook in output XLSX and output XLSB formats. The following screenshot shows the slicer created by Aspose.Cells in the output Excel file.

Sample Code

#include <iostream>
#include "Aspose.Cells.h"

using namespace Aspose::Cells;
using namespace Aspose::Cells::Pivot;
using namespace Aspose::Cells::Slicers;

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

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

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

    // Path of input Excel file
    U16String inputFilePath = srcDir + u"sampleCreateSlicerToPivotTable.xlsx";

    // Paths of output Excel files
    U16String outputFilePathXlsx = outDir + u"outputCreateSlicerToPivotTable.xlsx";
    U16String outputFilePathXlsb = outDir + u"outputCreateSlicerToPivotTable.xlsb";

    // Load sample Excel file containing a pivot table
    Workbook wb(inputFilePath);

    // Access the first worksheet
    Worksheet ws = wb.GetWorksheets().Get(0);

    // Access the first pivot table inside the worksheet
    PivotTable pt = ws.GetPivotTables().Get(0);

    // Add a slicer related to the pivot table using the first base field at cell B22
    int idx = ws.GetSlicers().Add(pt, u"B22", pt.GetBaseFields().Get(0));

    // Access the newly added slicer from the slicer collection
    Slicer slicer = ws.GetSlicers().Get(idx);

    // Save the workbook in output XLSX format
    wb.Save(outputFilePathXlsx, SaveFormat::Xlsx);

    // Save the workbook in output XLSB format
    wb.Save(outputFilePathXlsb, SaveFormat::Xlsb);

    std::cout << "Slicer created and workbooks saved successfully!" << std::endl;

    Aspose::Cells::Cleanup();
}

Create a Slicer for an Excel Table

Please see the following sample code. It loads the sample Excel file that contains a table. It then creates the slicer based on the first column. Finally, it saves the workbook in output XLSX format.

Sample Code

#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\\");

    // Load sample Excel file containing a table
    Workbook workbook(srcDir + u"sampleCreateSlicerToExcelTable.xlsx");

    // Access the first worksheet
    Worksheet worksheet = workbook.GetWorksheets().Get(0);

    // Access the first table inside the worksheet
    ListObject table = worksheet.GetListObjects().Get(0);

    // Add a slicer
    int idx = worksheet.GetSlicers().Add(table, 0, u"H5");

    // Save the workbook in output XLSX format
    workbook.Save(outDir + u"outputCreateSlicerToExcelTable.xlsx", SaveFormat::Xlsx);

    std::cout << "Slicer added successfully to the Excel table!" << std::endl;

    Aspose::Cells::Cleanup();
}

Advanced Topics

• Change Slicer Properties
• Draw Slicer while rendering Excel to PDF
• Formatting Slicer
• Removing Slicer
• Rendering Slicer
• Updating Slicer