The replacement you propose did not work. However you put me on the right track to find the solution. The following combination worked as expected :
style.setPattern(BackgroundType.SOLID);
style.setBackgroundColor(Color.fromArgb(R, G, B));
Thanks for your help
Good to know that your issue is sorted out now. Feel free to write us back if you have further queries or issue, we will be happy to assist you soon.
Actually I would like to mention two points
I want to know if other pivot fields type than PivotFieldType.Data will be supported in the future for the PivotArea selection :
pivotArea.Select(PivotFieldType.ROW, 0, PivotTableSelectionType.DataAndLabel);
pivotArea.Select(PivotFieldType.COLUMN, 0, PivotTableSelectionType.DataAndLabel);
pivotArea.Select(PivotFieldType.PAGE, 0, PivotTableSelectionType.DataAndLabel);
Currently it does not work, as you warned before the update in your code :
//only data field is supported
pivotArea.Select(PivotFieldType.Data, 0, PivotTableSelectionType.DataAndLabel);
Thank you
We will look into it and try to implement it. If possible, please provide more sample files of this type.
We have no plan to support it because it’s hard to convert setting in the file to PIvotArea selection.
Of course, you can find attached an example of what I would like, SampleData.zip (62.0 KB) on “PIVOT” tab
@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.