Change Background color

Aspose.Cells Product Family
1

Hi Aspose Support team,

I have a pivot table with two row fields and one column field.
I need to achive the following requirements

- put the background color for the each item in the column field
 - sub total of the row field also need to be bold.
- put the background color for the sub total of the row field

I have enclosed the screenshot for your kind reference.

In the sample picture, Ageing is the column field, and Main Principal and ATS year are the row fields.

Kindly advice me how to achieve to this feature? Thanks in advance.

Regards,

Saravanan K

2

Hi,

For setting Pivot Table appearance, please see this document: Setting PivotTables Appearance

If you still could not resolve it, then please let us know.

3

Hi,

I have tried PivotTable.AutoFormatType method, but i'm not satisfied with the appearance. Can you advise me if there is any other method to acheive my feature

Thank you

Saravanan

4

Hi,

Well, if your file is xlsx, then you can use advance Pivot Styles enumeration i.e
PivotTableStyleType

e.g


//apply styling

pivotTable.PivotTableStyleType = PivotTableStyleType.PivotTableStyleLight16;



The other way is to set the style of individual cell or column or rows. Please see these documents:

Formatting Rows & Columns
Approaches to Format Data in Cells

You can also see the Live Demos.

Formatting Rows & Columns
5

Hi Support team,


I have tried with the above, this one is working out side cell of the pivot table. But i need to modify the cell alignment within the pivot table. If you achieved inside the pivot table, can you give the sample piece of the code to achieve this feature? Thanks in advance

Regards,

Saravanan K
6

Hi,

Please check this code. The code formats the existing pivot table inside the source workbook and write the output workbook.

I have attached the source workbook and output workbook generated by the code. Also attached the screenshot.

C#


string filePath = @“F:\Downloads\sourcePivot.xlsx”;


//Open source workbook having your pivot table

Workbook workbook = new Workbook(filePath);


//Access the worksheet containing your pivot table

Worksheet worksheet = workbook.Worksheets[0];


//Access your pivot table

PivotTable pvtTable = worksheet.PivotTables[0];


//Set its style

pvtTable.PivotTableStyleType = PivotTableStyleType.PivotTableStyleDark11;


//Save the workbook

workbook.Save(filePath + “.out.xlsx”, SaveFormat.Xlsx);

Screenshot: