Style for Pivot Table

Aspose.Cells Product Family
1

How do you style a Total column on a pivottable? Please see attached.

2

Hi,

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

Please also provide us the source xls/xlsx file having your pivot in your desired formatting.

We will look into it and provide you a sample code.

3

See Grand Total column which has a fill color of gray.

4

Hi,

Please see the following code. It applies red color to your Pivot.xls file, you can choose any color.

Please see the output xlsx file and the screenshot.

C#


string filePath = @“F:\Pivot.xls”;


Workbook workbook = new Workbook(filePath);


Worksheet worksheet = workbook.Worksheets[0];


string yourGrandTotalColumns = “H2:H5”;


Range rng = worksheet.Cells.CreateRange(yourGrandTotalColumns);


StyleFlag flag = new StyleFlag() { CellShading = true };


//Create your style with your desired color

//I will use red color for an example

Style style = workbook.CreateStyle();


style.Pattern = BackgroundType.Solid;

style.ForegroundColor = Color.Red;



rng.ApplyStyle(style, flag);


//Save your workbook

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


Screenshot:
5

It does work in the limited example but does not work with a more real-life example.

Additionally, I tried to create it using the Aspose documentation sample for pivot tables and it is throwing an error in Excel saying the pivot table was discarded:

"A PivotTable report on '[book1.xls]Sheet1' was discarded due to integrity problems."

I have attached a project and data file. The project contains 2 methods: Test1 is a straight copy from the Aspose website illustrating the integrity issue. See

Test2 is a real life example showing how the styling does not happen. I see a blink for a sec which may indicate something behind the scenes but it is not appearing on the pivot table.

I have tested with Aspose.Cells v5 and the latest version downloaded yesterday.

6

Hi,


1) I have tested your Test1() code with our latest fix, it works fine. Please download and use the latest version: Aspose.Cells for .NET v7.1.2.5

2) I have found the issue even I tried different options and techniques for Test2() but all in vain. I have logged a ticket with an id:CELLSNET-40542. We will look into it soon.

Thank you.

7

Hi,

We have fixed this issue. Please download and use the latest version: Aspose.Cells for .NET v7.1.2.6.

About Test2(), please use this sample code:

C#

Style style = wb.CreateStyle();
style.Pattern = BackgroundType.Solid;
style.BackgroundColor = Color.Gray;
pt.CalculateData();
//rng.ApplyStyle(style, flag);
for (int j = 3; j < 12;j++ )
pt.Format(j, 3, style);


8

Thanks. I can confirm it is fixed.

- Alan

9

The issues you have found earlier (filed as CELLSNET-40542) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by aspose.notifier.