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.
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
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.
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 ? 
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