Select pivot data field of a pivot table to apply format on

@GVA32156

It’s a strange file ? Why are there so many formats for the same pivot field?

The format is generated via VBA. Depending on the area I specify, for example including the pivot field subtotals or not, the formatting is overridden This is why a single pivot field can contain several formats.

The macro in the file is the following, if it helps :

Public Sub SetDataAndlabelColors_GENERAL(v_CurrentPivotName As String, v_FieldName As String, v_FontColor As Variant, v_BackGroundColor As Variant)

'v_wbx.Sheets(v_SheetName).PivotTables(v_CurrentPivotName).PivotSelect v_FieldName, xlDataAndLabel, True
PivotTables(v_CurrentPivotName).PivotSelect v_FieldName, xlDataAndLabel, True

If Not IsNull(v_BackGroundColor) Then
 
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = v_BackGroundColor
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With

End If

If Not IsNull(v_FontColor) Then
    With Selection.Font
        .Color = v_FontColor
        .TintAndShade = 0
    End With
End If
End Sub

Sub Test()
         Call SetDataAndlabelColors_GENERAL("PivotTable1", "Rep", RGB(255, 255, 255), RGB(0, 0, 0))
         Call SetDataAndlabelColors_GENERAL("PivotTable1", "Rep[All; Total]", RGB(255, 255, 255), RGB(255, 0, 0))
         Call SetDataAndlabelColors_GENERAL("PivotTable1", "Sum of Units", RGB(0, 0, 0), RGB(255, 255, 0))
        'Call SetDataAndlabelColors_GENERAL("PivotTable1", "Sum of Units[All; Total]", RGB(0, 0, 0), RGB(255, 0, 255))

 End Sub

It is complex and maybe artificial but I tried to make it this way to have the same flexibility on the Java feature that I have in VBA. I want to be sure that such format is feasible.

@GVA32156,

Thanks for sharing further details.

We will evaluate and try to make similar flexibility in Aspose.Cells for Java API (if possible).

1 Like

The issues you have found earlier (filed as CELLSJAVA-45283) have been fixed in Aspose.Cells for Java 23.4.

Support PivotArea to select other pivot fields than PivotFieldType.Data works well, thank you.

However, I am still not able to select subtotals as I showed in the previous file.
Please find attached an Excel Macro file containing different examples with the same format applied on a specific Pivot Area sampledatainsurance.zip (182.1 KB)
:

  • tab PIVOT_ASPOSE_ALL, the Java Aspose solution to select the whole pivot field and apply format on
  • tab PIVOT_VBA_ALL, the VBA solution to select the whole pivot field and apply format on
  • tab PIVOT_VBA_SUBTOTALS, the VBA solution to select pivot field subtotals and apply format on

I would like to perform the same than the Pivot Table formatting of pivot field subtotals within Aspose, can we imagine having such feature in the next release ?
For example extending PivotArea.select(…) method to get something like :

PivotArea pivotArea = new PivotArea(pivotTable);
pivotArea.select(PivotFieldType.ROW, 2, PivotTableSelectionType.DATA_AND_LABEL, PivotFieldSelectionType.SUBTOTALS); 
//new class PivotFieldSelectionType
//PivotFieldSelectionType.ALL (static property)
//PivotFieldSelectionType.SUBTOTALS (static property)

I don’t know if my suggestion is the most relevant, but I think you’re able to understand the idea
Please let me know

@GVA32156
Thanks for your file and info. We will look into these feature later.

@GVA32156
We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.

Issue ID(s): CELLSJAVA-45351

You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.

@GVA32156
Please check the codes and result file in 23.4.1.zip (206.9 KB)

The PIVOT in 23.4.1.xlsx file is the result expected, yes.

Though I am not sure to understand the new component :

  pivotArea.Filters[0].SetSubtotals(PivotFieldSubtotalType.Automatic, true);

SetSubtotals(…) method is clear since it is the same than in PivotField class.
Does it mean pivotArea.Filters[0] return a PivotField object ? What is the index 0 for ?

I know it will be documented when released but I am just wondering

Thank you

@GVA32156
PivotArea.Select method sets property of area and adds a filter about area.
pivotArea.Filters[0] returns PivotAreaFilter object.
PivotAreaFilter.SetSubtotals() means selecting which subtoatals.

1 Like

@GVA32156
We have redesigned the formatting method, you can simply use the following method:
PivotTableFormatCollection.FormatArea(PivotFieldType axisType, int fieldPosition, PivotFieldSubtotalType subtotalType, bool onlyData, bool onlyLabel, bool isGrandRow, bool isGrandColumn, Style style)

Sample codes:

Workbook workbook = new Workbook(Constants.sourcePath + "CELLSJAVA45351.xlsx");
PivotTable pt = workbook.Worksheets["PIVOT_VBA_SUBTOTALS"].PivotTables[0];
//Style s = pivotArea.GetStyle(); //pointer to the style of the area and only add borders
Style s = workbook.CreateStyle(); //instead of creating a new style
s.SetBorder(BorderType.LeftBorder, CellBorderType.Thin, Color.Red);
s.SetBorder(BorderType.RightBorder, CellBorderType.Thin, Color.Red);
s.SetBorder(BorderType.TopBorder, CellBorderType.Thin, Color.Red);
s.SetBorder(BorderType.BottomBorder, CellBorderType.Thin, Color.Red);
s.Pattern = BackgroundType.Solid;
s.ForegroundColor = Color.Red;
s.BackgroundColor = Color.Red;

pt.PivotFormats.FormatArea(PivotFieldType.Row, 0, PivotFieldSubtotalType.Automatic, false, false, false, false, s);
pt.RefreshDataOnOpeningFile = true;
workbook.Save(Constants.destPath + "CELLSJAVA45351.xlsx");

I would have kept the usage of PivotTableSelectionType static property instead of boolean input parameters bool onlyData, bool onlyLabel
Because what happens if I set both onlyData and onlyLabel values to true ? :upside_down_face:

PivotTableSelectionType.DATA_AND_LABEL
PivotTableSelectionType.DATA_ONLY
PivotTableSelectionType.LABEL_ONLY

makes sense and is more relatable to VBA, this is why I like it

Thank you though, I appreciate it anyway.

@GVA32156
It’s not allowed to set both onlyData and onlyLabel values to true.
I will change method to keep PivotTableSelectionType and remove onlyData and onlyLabel

1 Like

The issues you have found earlier (filed as CELLSJAVA-45351) have been fixed in Aspose.Cells for Java 23.5.

@GVA32156
Please use method PivotTable.PivotFormats.FormatArea(PivotFieldType.Row, 0, PivotFieldSubtotalType.Automatic, PivotTableSelectionType.DataAndLabel,false, false, s);

It is tested and it works, thank you

@GVA32156,
Thank you for your feedback. If you have any questions, please feel free to contact us.