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

Aspose.Cells Product Family
,
1

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 peDataFieldBorder.PNG (49.6 KB)
sampledatainsurance.7z (102.6 KB)
rform 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 : XlPTSelectionMode enumeration (Excel) | Microsoft Learn and the function mentioned before.

I’ve attached an example of the expected result. Please share your positive or negative feedback on this topic, thank you

2

@GVA32156,

Thanks for the template Excel file and screenshot.

I evaluated and tried some approaches to apply formatting to specific data field area only. I tried to find the relevant data field manually and wrote some code segments like:
e.g.
Sample code:


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

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

        //Apply formatting directly to specific data fields.
        CellArea dataArea = pivotTable.getDataBodyRange();
        int dataRowStart = dataArea.StartRow;
        int dataRowEnd = dataArea.EndRow;

        String strFind = "Count of Location";
        FindOptions opts = new FindOptions();
        opts.setLookInType(LookInType.VALUES);
        Cell cell = null;
        do
        {
            cell = sheet.getCells().find(strFind, cell, opts);
            if(cell!=null)
            {
                int dataColNum = cell.getColumn();
                for(int row = dataRowStart; row <= dataRowEnd;row++){

                        cell = cells.get(row,dataColNum);
                        Style style = cell.getStyle();
                        style.setBorder(BorderType.LEFT_BORDER, CellBorderType.THIN, com.aspose.cells.Color.getBlack());
                        style.setBorder(BorderType.TOP_BORDER, CellBorderType.THIN, com.aspose.cells.Color.getBlack());
                        style.setBorder(BorderType.RIGHT_BORDER, CellBorderType.THIN, com.aspose.cells.Color.getBlack());
                        style.setBorder(BorderType.BOTTOM_BORDER, CellBorderType.THIN, com.aspose.cells.Color.getBlack());
                        pivotTable.format(row, dataColNum, style);

                    }
             }

        }
        while(cell!=null);
.....

but it turns out to be not applicable. Also, when we refresh pivot table, the formatting would be distorted. May be we need to implement some methods to get data body range based on specific data field or something else. Alternatively, we will write precise code segment for your task. We will soon log a ticket to investigate it in details.

3

@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-45189

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.

1 Like
4

@GVA32156

We plan to implement a solution to select data field.
And please show more about your need about this feature, we will look into it together.

5

Hello guys,

Thank you for taking this request. @simon.zhao basically my need would be to scope a single data field area in order to :

  1. apply a border around the whole area (as it was shown on the attachment)
  2. apply a background color to the whole area

Let me know if the result expected is misunderstood

6

@GVA32156
Thanks for your information, we will try to scope the data field as the following codes in the next version:
Workbook workbook = new Workbook(dir + “t.xlsx”);
PivotTable pt = workbook.Worksheets[“PIVOT”].PivotTables[0];
PivotArea pivotArea = new PivotArea(pt);
//only data field is supported
pivotArea.Select(PivotFieldType.Data, 0, PivotTableSelectionType.DataAndLabel);
Style s = workbook.CreateStyle();
s.SetBorder(BorderType.LeftBorder, CellBorderType.Thin, Color.Black);
s.SetBorder(BorderType.RightBorder, CellBorderType.Thin, Color.Black);
s.SetBorder(BorderType.TopBorder, CellBorderType.Thin, Color.Black);
s.SetBorder(BorderType.BottomBorder, CellBorderType.Thin, Color.Black);
pt.Format(pivotArea, s);
workbook.Save(dir + “dest.xlsx”);

1 Like
7

This is exactly what I’m looking for. I would even correct slightly :

Workbook workbook = new Workbook(dir + “t.xlsx”);
PivotTable pt = workbook.Worksheets[“PIVOT”].PivotTables[0];
PivotArea pivotArea = new PivotArea(pt);
//only data field is supported
pivotArea.Select(PivotFieldType.Data, 0, PivotTableSelectionType.DataAndLabel);
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.Black);
s.SetBorder(BorderType.RightBorder, CellBorderType.Thin, Color.Black);
s.SetBorder(BorderType.TopBorder, CellBorderType.Thin, Color.Black);
s.SetBorder(BorderType.BottomBorder, CellBorderType.Thin, Color.Black);
pt.Format(pivotArea, s);
workbook.Save(dir + “dest.xlsx”);

Maybe just a detail but I wanted to stress it in case you’re working on it :slight_smile:

8

@GVA32156,

We are working on your required feature. We will also check the feasibility and may allow to instantiate Style object based on existing style. Once we figure it out, we will update you.

1 Like
9

@GVA32156
We can not support pivotArea.GetStyle(). It’s complex to select an pivotarea from defined selections in the file. Excel will apply the style one by one,so we can append our setting with new Style.

1 Like
10

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

1 Like
11

Hello,

Thank you for the update on the new release first of all.
The style with custom borders as mentioned previously works, though I’m struggling applying a background color on the PivotArea object selected. It seems not work.

public void setDataAndLabelColors(String sheetName, String pivotName, String fieldName, int R, int G, int B) {
	
	
	try {
		PivotTableCollection pivotTables = workbook.getWorksheets().get(sheetName).getPivotTables();
		PivotTable pivotTable = pivotTables.get(pivotName);
		PivotFieldCollection dataFields = pivotTable.getDataFields();
		
		int dataFieldIndex=0;
		while(dataFields.get(dataFieldIndex).getName().compareTo(fieldName) != 0) {
			dataFieldIndex++;
		}
		
		//Aspose 23.3 version
		PivotArea pivotArea = new PivotArea(pivotTable);
		//only data field is supported
		pivotArea.select(PivotFieldType.DATA, dataFieldIndex, PivotTableSelectionType.DATA_AND_LABEL);
		//pivotArea.select(PivotFieldType.DATA, dataFieldIndex, PivotTableSelectionType.DATA_ONLY);
		Style style = workbook.createStyle(); 
		style.setBackgroundColor(Color.fromArgb(R, G, B);//DOES NOT WORK
		pivotTable.format(pivotArea, style);
		
	} catch(Exception e) {
		Logger.error("setDataAndLabelColors", e.getMessage(), e);
	}

}

Are there limitations on the style we apply to a PivotArea object ? Please let me know

12

@GVA32156,

We will look into it soon.

By the way, could you please try to replace the following line:

style.setBackgroundColor(Color.fromArgb(R, G, B);//DOES NOT WORK

with:

style.setPattern(BackgroundType.SOLID);
style.setForegroundColor(Color.fromArgb(R, G, B));

if it makes any difference.

13

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

14

@GVA32156,

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.

15

Actually I would like to mention two points

  1. 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);
  1. Is there a plan to support pivotArea.GetStyle() to avoid overriding the style ?

Thank you

16

@GVA32156
1

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.

1 Like
17

Of course, you can find attached an example of what I would like, SampleData.zip (62.0 KB) on “PIVOT” tab

18

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

19

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)

20

@GVA32156,

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.