How to set pivot table bgcolor

Aspose.Cells Product Family
1

I can set the normal excel bgcolor,but it’s hard to set pivot table bgcolor!

need your help ,TKS!
2

Hi,

Thanks for your posting and using Aspose.Cells for .NET.

You will have to apply Style object to your Pivot Cells. In order to set the background color, you will have to use Style.Pattern and Style.ForegroundColor property.

The following code applies the green fill color to cell C3.

C#


Workbook workbook = new Workbook();


Worksheet worksheet = workbook.Worksheets[0];


Cell cell = worksheet.Cells[“C3”];


Style style = cell.GetStyle();

style.Pattern = BackgroundType.Solid;

style.ForegroundColor = Color.Green;

cell.SetStyle(style);



workbook.Save(@“F:\Shak-Data-RW\Downloads\output.xlsx”);

3
Thanks for your help!
I mean it's a pivot table, using Aspose.Cells.Pivot.PivotTable
It seem to have only one way to change the style
vPivotTable.PivotTableStyleType = Aspose.Cells.Pivot.PivotTableStyleType.PivotTableStyleDark21;

I want to change the pivot table bgcolor by myself
is there any way else can do it

4
Hi,

I think you may try to use PivotTable.Format and PivotTable.FormatAll methods for your needs. All you need to create the style and specify your style/formatting attributes and then use the method accordingly.

Sample code:

PivotTable pvtTable = worksheet.PivotTables[0];
pvtTable.IsAutoFormat = true;
pvtTable.PivotTableStyleType = PivotTableStyleType.PivotTableStyleLight16;
Style style = workbook.CreateStyle();
style.Custom = "$#,##0.00_);[Red]($#,##0.00)";
pvtTable.FormatAll(style);

Thank you.
5

Successful!

thank you very much!
6

Hi,


Good to know that your issue is resolved following the suggestion.

Thanks,
7
Share my code:


Aspose.Cells.Style cellStyle = new Aspose.Cells.Style();
Aspose.Cells.CellArea vPivotTableCell = vPivotTable.DataBodyRange;
for (int i = vPivotTableCell.StartRow; i <= vPivotTableCell.EndRow; i++)
{
for (int j = vPivotTableCell.StartColumn; j <= vPivotTableCell.EndColumn-1; j++)
{
string bgcolor = "#D8E4BC";
if (j % 2 == 0)
{
bgcolor = "#FDE9D9";
}
cellStyle.BackgroundColor = System.Drawing.ColorTranslator.FromHtml(bgcolor);
vPivotTable.Format(i, j, cellStyle);
}

}
8

Hi,


Thanks for sharing the sample code, it might be helpful for the other users.

Thank you.