@GVA32156
We have opened the following new ticket for your requirement and will deliver their fixes according to the terms mentioned in Free Support Policies.
Issue ID(s): CELLSJAVA-45283
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.
I provide you another workbook containing pivot table formatting generated via Macros, it may be more relevant than the previous one SampleData_xlsm.zip (70.2 KB)
Thanks for the file.
We have logged it with your existing ticket “CELLSJAVA-45283” into our database. We will evaluate using your file as well.
By the way, we have now supported the feature, i.e., Support PivotArea to select other pivot fields than PivotFieldType.Data. The fix will be included in an upcoming release (Aspose.Cells v23.4) that we plan to release in the first half of April 2023. You will be notified when the next version is released.
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).
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)
:
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.
@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
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