Using PivotTable.Format() with a PivotField

Aspose.Cells Product Family
1

Hi, I’m evaluating using Aspose.Cells to generate Excel workbooks that contain Pivot Tables based on dynamic (but standardized) datasets.


There are certain rules that we would like to follow for formatting the headers of certain well-known columns. For example, if the “Win Rate” column is included in the report, then it should have a grey-blue background but an “Avg Bid” column should have a different color background.

Creating the styles is fairly easy. Applying those formats to cells in a sheet directly is also easy. But applying those styles to fields that may or may not be included in a pivot table seems arbitrarily hard – there just doesn’t seem to be a way to get from a field to the row/column offsets where it lives in the pivot table to call .Format with.

When I add the field, I have the PivotField object, so I know the name of the field, etc. But for some reason, I can’t find a way to figure out the row/column pair where the field header is going to be found. Can you provide any help?

I found a workaround using conditional formatting, but that seems a bit error prone, since I don’t actually know where the CellRange for the condition should be applied. I can guess and make it fairly large but that seems somewhat fragile.

I’ve attached an example report that shows the kinds of formatting that we’ll be looking for. Thanks in advance for any help!
2

Hi,


Thanks for the template file.

Well, I think you may simply find the PivotField header cell (after calculating PivotTable report data and refreshing it) using Aspose.Cells APIs, now you may simply apply formatting (you may create a Style object and specify your desired formatting etc.) to the cell. I used your template file and change the cell’s shading color to the header cell “Win Rate” for your reference.
e.g
Sample code:

var workbook = new Workbook(“e:\test2\example2.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(“Win Rate”,null, opts);

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

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

Hope, this helps you a bit.

Thank you.
3

Thanks for getting back to me so quickly.


Unfortunately, this doesn’t seem to work. Any styling change that I apply directly to cells ( either via worksheet.Cells[x].SetStyle() or using Find and then cell.SetStyle() like you suggested ) seems to be masked by the pivot table’s (lack of) styling.

Even if this approach did work, I think it would break if users changed the order of column in the pivot when working with the workbook, right? We really want the colors to stay “attached” to the pivot field header itself.

Any more ideas?

I’ve attached my source code demonstrating how the find approach isn’t working properly.

(For what it’s worth, I’m having other troubles with how PivotTables are formatted, described in this thread.)

4

Hi,


Thanks for the sample code.

After an initial test, I observed the issue as you mentioned. I even tried to use the
following sample code to update your code segment to make it work but it
does not work:


e.g


Sample code:


…

pivotTable.RefreshData();


pivotTable.CalculateData();



Style style = cell.GetStyle();
style.BackgroundColor = Color.Yellow;
style.Pattern = BackgroundType.Solid;
CellArea cellArea = pivotTable.TableRange1;
pivotTable.Format(cellArea.StartRow, cellArea.StartColumn + 1, style); //Apply format to Color cell header but it does not work

It works fine to apply formatting to Color header though, if I use:
e.g
Sample code:

pivotTable.Format(1, 1, new Style()
{
BackgroundColor = Color.Yellow,
Pattern = BackgroundType.Solid
});

So, you may try the above workaround to do it.

And, I have logged a ticket with an id "CELLSNET-43367" for your issue. We will look into it to figure it out soon.

Thank you.
5

Hi Amjad,


Thanks again for the reply. Unfortunately, I think we got a bit offtrack with this thread, and kind of missed the point.

I don’t mind calling the Format( row, column, Style ) method but I don’t have a good way to predict what the row and column values for any particular header are going to be. For example, when I add more “page” data, everything shifts down 1 or more rows. As I add more columns, things shift right.

Can you help me figure out how to predict what the row and column values for a particular field are going to be? Or do I have to build a mechanism that helps keep track of that for me (and figure out all of the rules that that implies)?

Thanks,
–Chris
6

Hi,


As I could understand you need to find the row/col indexes of PivotField headers based on their caption names, you might require some means or mechanism so one can format those field names (cells) accordingly. Am i right in understanding your requirements. Could you elaborate more and provide more details about your requirements, you may attach some sample Excel files, screen shots to highlight your core needs, so we could look into it and may log them against your ticket(s) into our database.

Thank you.



7

Hi -


Yes, you’re exactly on point. As my code is adding fields ( or later if necessary ) I need to be able to set the formatting for the header of that field, dependent upon rules defined by my business team for how the header should be formatted. That formatting needs to “stick” to the field, no matter how the user may alter the pivot.

I’ve attached an example that shows the desired end product ( which we’re currently producing from a manually maintained template – but we’d rather generate this format from Aspose instead ).
8

Hi,


Thanks for your confirmation and template file.

We have logged your requirements against your issue “CELLSNET-43367” into our database. Our concerned developer will look into it soon.

Once we have any update on it, we will let you know here.

Thank you.
9

Hi,

Thanks for your using Aspose.Cells.

Please download and try the latest fix: Aspose.Cells for .NET v8.5.1.1 and let us know your feedback.

About CELLSNET-43367, there are some tips for you:

1. For getting the Cell object by the DisplayName of PivotField, we added the PivotTable.GetCellByDisplayName(string displayName) method.

2. The example as follows:

Cell cell = pivotTable.GetCellByDisplayName(pivotTable.DataFields[0].DisplayName);

pivotTable.Format(cell.Row, cell.Column, style);

10

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

This message was posted using Notification2Forum from Downloads module by Aspose Notifier.