How to set background colour/format of pivot fields

Aspose.Cells Product Family
1

Am trying to set the background colour of the pivot fields of a pivot table (see the attachment) using Aspose.cells for .NET.


The following code only set the colour of the header/caption but I want to set the colour of the entire pivotfield column:

var workbook = new Workbook(“C:\Temp\PivotTableSample.xlsx”);

var worksheet = workbook.Worksheets[0];

var pivotTable = worksheet.PivotTables[0];

// workbook.CalculateFormula();

pivotTable.RefreshData();
pivotTable.CalculateData();

//Specify Find options
FindOptions opts = new FindOptions();
opts.SearchNext = true;
opts.LookInType = LookInType.Values;
opts.LookAtType = LookAtType.Contains;
//Find your header cell
Cell cell = worksheet.Cells.Find(“Sum of Sales Amount”, null, opts);

if (cell != null)
{
Style style = cell.GetStyle();
style.ForegroundColor = Color.Yellow;
style.Pattern = BackgroundType.Solid;
cell.SetStyle(style);
}

workbook.Save(“C:\Temp\outexample2.xlsx”);
2

Hi,


This is due to your code segment which finds a string and then apply the formatting that cell only:
e.g
Sample code:

…
//Find your header cell
Cell cell = worksheet.Cells.Find(“Sum of Sales Amount”, null, opts);

if (cell != null)
{
Style style = cell.GetStyle();
style.ForegroundColor = Color.Yellow;
style.Pattern = BackgroundType.Solid;
cell.SetStyle(style);
}
…

You got to apply style to all the cells or to your desired area/range of cells accordingly.

Thank you.
3

Hi Sahi


Yes, I know that it is because only a header is found but how do you "apply style to all the cells or to your desired area/range of cells "?

4

Hi,


Could you provide us your template file (e.g “PivotTableSample.xlsx”), we will check and help you through.

Thank you.
5

Hi Sahi


Find attached the “PivotTableSample.xlsx” file as requested. I urgently need a solution.


Many thanks
6

Hi,


Thanks for the template file.

Well, you may try to use PivotTable.RowRange, PivotTable.DataBodyRange to get the row area and data area in the PivotTable report and apply formatting to the specified cells range accordingly. There are PivotTable.Format and PivotTable.FormatAll() methods that you may use too. See the following sample code for your reference, I have used your template file to update the formatting of some areas in the PivotTable:
e.g
Sample code:

var workbook = new Workbook(“e:\test2\PivotTableSample.xlsx”);
var pivot = workbook.Worksheets[0].PivotTables[0];

// pivot.RefreshData();
// pivot.CalculateData();
// pivot.CalculateRange();

Style style1 = workbook.CreateStyle();
style1.ForegroundColor = Color.LightGray;
style1.Pattern = BackgroundType.Solid;

Style style2 = workbook.CreateStyle();
style2.ForegroundColor = Color.Gray;
style2.Pattern = BackgroundType.Solid;

Style style3 = workbook.CreateStyle();
style3.ForegroundColor = Color.LightGreen;
style3.Pattern = BackgroundType.Solid;

Style style4 = workbook.CreateStyle();
style4.ForegroundColor = Color.GreenYellow;
style4.Pattern = BackgroundType.Solid;


CellArea area = pivot.RowRange; //A5:A8
CellArea area2 = pivot.DataBodyRange;//B5:C8

int start = area.StartColumn;

for (int i = area.StartRow; i <= area.EndRow; i++)
{
for (int j = area.StartColumn; j <= area.EndColumn; j++)
{
if (i % 2 == 0)
{
pivot.Format(i, j, style3);
}
else
{
pivot.Format(i, j, style4);
}
}
}
for (int r = area2.StartRow; r <= area2.EndRow; r++)
{
for (int c = area2.StartColumn; c <= area2.EndColumn; c++)
{
if (r % 2 == 0)
{
pivot.Format(r, c, style1);
}
else
{
pivot.Format(r, c, style2);
}

}

}

workbook.Save(“e:\test2\out1.xlsx”);

Hope, this helps a bit.

Thank you.
7

Hi Sahi


Thanks for providing the code sample which is a step in the right direction. However, I require each column in the pivot (including the header) to be of one colour.


Thanks
8

Hi,


I have written the following sample code for your requirements for your reference. I have applied different color for each column in the PivotTable report. The sample code is only for demonstration purpose as you should build your own logic and write your own code to specify/store your desired colors to be applied for different cells range/area in the PivotTable accordingly:
e.g
Sample code:

var workbook = new Workbook(“e:\test2\PivotTableSample.xlsx”);
var pivot = workbook.Worksheets[0].PivotTables[0];
//Define a style with solid background
Style style = workbook.CreateStyle();
style.Pattern = BackgroundType.Solid;

//Define an array of some colors
Color[] colors = { Color.Yellow, Color.Blue, Color.Green, Color.Brown, Color.Red, Color.Purple, Color.Gray, Color.LightGreen, Color.White };
int i=0;
// 1) Get the TableRange1 that returns complete Pivot Table area except page fields
var tableRange1 = pivot.TableRange1;
// 2) Loop through the Pivottable report area and apply background color for each column separately
for (int c = tableRange1.StartColumn; c <= tableRange1.EndColumn; c++)
{
for (int r = tableRange1.StartRow; r <= tableRange1.EndRow; r++)
{
//Set different background color
style.ForegroundColor = colors[i];
pivot.Format(r, c, style);


}
i++;
}

workbook.Save(“e:\test2\out1.xlsx”);

I have also attached the output Excel file for your reference.

Hope, this helps a bit.

Thank you.
9

Hi, that helps. Indeed, I’ll create my own colours etc.


Cheers
10

I have the following method which is an adaptation of the suggested solutions, but it failed to set the colours. What can be the cause?

(Ps: This method is part of a large code, hence it is not possible to send any other runnable version).


private void addBckgnColour(Worksheet wSheet, int pivotNumber)
{
//Define a style with solid background
Style bgndStyle = workbook.CreateStyle();
bgndStyle.Pattern = BackgroundType.Solid;

//Define an array of some colors
Color[] colors = { Color.Yellow, Color.Blue, Color.Green, Color.Brown, Color.Red, Color.Purple, Color.Gray, Color.LightGreen, Color.White };
int i = 0;

// 1) Get the TableRange1 that returns complete Pivot Table area except page fields
// var tableRange1 = pivot.TableRange1;
var tableRange1 = wSheet.PivotTables[pivotNumber].TableRange1;

// 2) Loop through the Pivottable report area and apply background color for each column separately
for (int c = tableRange1.StartColumn; c <= tableRange1.EndColumn; c++)
{
for (int r = tableRange1.StartRow; r <= tableRange1.EndRow; r++)
{
//Set different background color
bgndStyle.ForegroundColor = colors[i];
wSheet.PivotTables[pivotNumber].Format(r, c, bgndStyle);


}
i++;
}

wSheet.PivotTables[pivotNumber].RefreshData();

workbook.Save("C:\\Temp\\TemporaryBGNDColour.xlsx");

}


Thanks
11

Hi,


Thanks for sharing the code segment and details.

I am afraid, we have no clue why it is not working for your scenario/ case unless we could reproduce the issue on our end. You already noticed that we have conducted test with the sample code and your template file, it works fine and we attached the output file for your reference too, see the post in the thread. We appreciate if you could create a sample console application (runnable) to reproduce the issue, you may zip the project prior attaching here, we will check it soon. Also attach your template file(s) (if any).

Thank you.
12

Could the style applied to the pivot table be an issue?

13

Hi,


You mean PivotTable Styles (e.g. built-in Styles) of MS Excel? Well, I am not entirely certain about it. You may simply remove the existing pivot table styles and then try to apply background colors/style, etc. But if this is an issue we got to reproduce it first so we could evaluate and figure it out soon. As requested earlier, we require a sample console application (runnable) to reproduce the issue.

Thank you.
14

Thanks. This is a tricky one as the complete code itself is quite huge hence trying to recreate the issue in a small console application is a bit of a challenge. I just wondered if you guys have dealt with such issues before or is there a function that need to be set for the colour to be applied?



Regards
15

Hi,


We really appreciate your work as you are already trying to reproduce the issue in a separate application. We are waiting for your sample console application (runnable), so we could evaluate your issue on our end precisely. I do not remember if we did deal with such an issue before where you are finding issue in duplicating it in a separate application.

Anyways, I will still check with product team and if we got something to share with you I will share with you here.

Thank you.