Select specific range/scope of a pivot table to apply format on (Aspose.Cells for Java)

Question

Is there an equivalent of this VBA function PivotTable.PivotSelect in Aspose.Cells product ? I am looking for a way to create a border on a whole selection of cells containing specific data field values, or on a specific scope as you prefer.

The VBA macro to perform this action is the following, by giving only the data field name parameter :

v_wbx.Sheets(v_SheetName).**PivotTables(v_CurrentPivotName).PivotSelect **DataFieldName**, xlDataAndLabel, True**
            Selection.Borders(xlDiagonalDown).LineStyle = xlNone
            Selection.Borders(xlDiagonalUp).LineStyle = xlNone
            With Selection.Borders(xlEdgeLeft)
                .LineStyle = xlContinuous
                .Color = v_Color
                .TintAndShade = 0
                .Weight = v_Weight
            End With
            With Selection.Borders(xlEdgeTop)
                .LineStyle = xlContinuous
                .Color = v_Color
                .TintAndShade = 0
                .Weight = v_Weight
            End With
            With Selection.Borders(xlEdgeBottom)
                .LineStyle = xlContinuous
                .Color = v_Color
                .TintAndShade = 0
                .Weight = v_Weight
            End With
            With Selection.Borders(xlEdgeRight)
                .LineStyle = xlContinuous
                .Color = v_Color
                .TintAndShade = 0
                .Weight = v_Weight
            End With

Is this covered by Aspose.Cells ? I looked into documentation but haven't found any solution. I'm aware that the "scope" selection is available with the Aspose conditional formatting, but here I want to create the border around the WHOLE selection of cells by specifying the data field name, and NOT use any Conditional Format rule. This is done via VBA using this enum : https://learn.microsoft.com/en-us/office/vba/api/excel.xlptselectionmode and the function mentioned before.

I precise I'm using the trial version of the product to evaluate the range of features. Please share your positive or negative feedback on this topic, thank you

Answer 1

You can format specific data area values in the Pivot Table via Aspose.Cells for Java. See the following example for your reference. You can apply formatting via both ways (i.e., use directly apply formatting and via pivot format condition).

e.g.

Sample code:

        //Instantiating a Workbook object
        Workbook workbook = new Workbook();

        //Obtaining the reference of the newly added worksheet
        int sheetIndex = workbook.getWorksheets().add();
        Worksheet sheet = workbook.getWorksheets().get(sheetIndex);
        Cells cells = sheet.getCells();

        //Setting the value to the cells
        Cell cell = cells.get("A1");
        cell.setValue("Sport");
        cell = cells.get("B1");
        cell.setValue("Quarter");
        cell = cells.get("C1");
        cell.setValue("Sales");

        cell = cells.get("A2");
        cell.setValue("Golf");
        cell = cells.get("A3");
        cell.setValue("Golf");
        cell = cells.get("A4");
        cell.setValue("Tennis");
        cell = cells.get("A5");
        cell.setValue("Tennis");
        cell = cells.get("A6");
        cell.setValue("Tennis");
        cell = cells.get("A7");
        cell.setValue("Tennis");
        cell = cells.get("A8");
        cell.setValue("Golf");

        cell = cells.get("B2");
        cell.setValue("Qtr3");
        cell = cells.get("B3");
        cell.setValue("Qtr4");
        cell = cells.get("B4");
        cell.setValue("Qtr3");
        cell = cells.get("B5");
        cell.setValue("Qtr4");
        cell = cells.get("B6");
        cell.setValue("Qtr3");
        cell = cells.get("B7");
        cell.setValue("Qtr4");
        cell = cells.get("B8");
        cell.setValue("Qtr3");

        cell = cells.get("C2");
        cell.setValue(1500);
        cell = cells.get("C3");
        cell.setValue(2000);
        cell = cells.get("C4");
        cell.setValue(600);
        cell = cells.get("C5");
        cell.setValue(1500);
        cell = cells.get("C6");
        cell.setValue(4070);
        cell = cells.get("C7");
        cell.setValue(5000);
        cell = cells.get("C8");
        cell.setValue(6430);

        PivotTableCollection pivotTables = sheet.getPivotTables();

        //Adding a PivotTable to the worksheet
        int index = pivotTables.add("=A1:C8", "E3", "PivotTable2");

        //Accessing the instance of the newly added PivotTable
        PivotTable pivotTable = pivotTables.get(index);

        //Unshowing grand totals for rows.
        pivotTable.setRowGrand(false);

        //Dragging the first field to the row area.
        pivotTable.addFieldToArea(PivotFieldType.ROW, 0);

        //Dragging the second field to the column area.
        pivotTable.addFieldToArea(PivotFieldType.COLUMN, 1);

        //Dragging the third field to the data area.
        pivotTable.addFieldToArea(PivotFieldType.DATA, 2);

        pivotTable.refreshData();
        pivotTable.calculateData();

        /*
        //Apply formatting to specific data area values via Pivot format condition.
        PivotFormatConditionCollection pfcc = pivotTable.getPivotFormatConditions();
        int pIndex = pfcc.add();
        PivotFormatCondition pfc = pfcc.get(pIndex);
        FormatConditionCollection fcc = pfc.getFormatConditions();
        CellArea dataBodyRange = pivotTable.getDataBodyRange();
        fcc.addArea(dataBodyRange);
        int idx = fcc.addCondition(FormatConditionType.CELL_VALUE);
        FormatCondition fc = fcc.get(idx);
        fc.setFormula1("6000");
        fc.setOperator(OperatorType.GREATER_OR_EQUAL);
        //fc.getStyle().setBackgroundColor(com.aspose.cells.Color.getRed());
        fc.getStyle().setBorder(BorderType.LEFT_BORDER, CellBorderType.THICK, com.aspose.cells.Color.getRed());
        fc.getStyle().setBorder(BorderType.TOP_BORDER, CellBorderType.THICK, com.aspose.cells.Color.getRed());
        fc.getStyle().setBorder(BorderType.RIGHT_BORDER, CellBorderType.THICK, com.aspose.cells.Color.getRed());
        fc.getStyle().setBorder(BorderType.BOTTOM_BORDER, CellBorderType.THICK, com.aspose.cells.Color.getRed());
        */
        ///*
        //Apply formatting directly to specific data fields. 
        CellArea dataArea = pivotTable.getDataBodyRange();
        for(int dataRowNum = dataArea.StartRow; dataRowNum <= dataArea.EndRow;dataRowNum++){
            for(int dataColNum = dataArea.StartColumn;dataColNum <= dataArea.EndColumn;dataColNum++){
                cell = cells.get(dataRowNum,dataColNum);
                int value = cell.getIntValue();
                System.out.println(value);
                if (value > 6000) {
                Style style = cell.getStyle();
                com.aspose.cells.Font font = style.getFont();
                font.setColor(com.aspose.cells.Color.getBlue());
                style.setBorder(BorderType.LEFT_BORDER, CellBorderType.THICK, com.aspose.cells.Color.getRed());
                style.setBorder(BorderType.TOP_BORDER, CellBorderType.THICK, com.aspose.cells.Color.getRed());
                style.setBorder(BorderType.RIGHT_BORDER, CellBorderType.THICK, com.aspose.cells.Color.getRed());
                style.setBorder(BorderType.BOTTOM_BORDER, CellBorderType.THICK, com.aspose.cells.Color.getRed());
                pivotTable.format(dataRowNum, dataColNum, style);

                }
            }
        }
        workbook.save("f:\\files\\out1.xlsx");

You may also post your queries in the dedicated forum.

PS. I am working as Support developer/ Evangelist at Aspose.