Excel 2007 default style not applied to Aspose Pivot Table

Aspose.Cells Product Family
1

Hi,

I created a default pivot style in Excel, saved the template, then used Aspose to insert some data and a pivot table into the workbook. When I open the workbook the pivot table has no formatting. I'm not using the AutoFormatType and I assumed that Excel would apply the style as needed, is this not the case? Do I need to call something to apply this default style?

2

Hi,


Please download and try this latest fix: Aspose.Cells for .NET (Latest Version) and let us know your feedback

If you still find any issue, kindly attach your template file and paste your sample code here. Also, provide the output file. We will check your issue soon.

Thank you.

3

OK that seems to do the trick but now I’m trying to apply a conditional format to the pivot table and a CellArea object is required. I understand what this is but how can you possible set a Conditional Format on a Data Field in the pivot if you don’t know the CellArea for it? Is there a way to get the CellArea or Range for a given Data Field in the Pivot?

4

Hi,


Please refer to the following code below for your reference. You may use PivotTable.PivotFormatConditions attribute to add formatted conditions for conditional formattings on your pivot table report for your needs.

Sample code:

Workbook workbook = new Workbook();

//Create data source Table1
Worksheet worksheet = workbook.Worksheets[0];

worksheet.Cells[0, 0].Value = “Header1”;
worksheet.Cells[0, 1].Value = “Header2”;
worksheet.Cells[0, 2].Value = “Header3”;

for (int i = 1; i < 21; i++)
{
int value = i - 1;
worksheet.Cells[i, 0].Value = “Value1-” + (value < 10 ? “a” : “b”);
worksheet.Cells[i, 1].Value = “Value2-” + value;
worksheet.Cells[i, 2].Value = value;
}

worksheet.ListObjects.Add(0, 0, 20, 2, true);

//Create PivotTable
workbook.Worksheets.Add(“PivotSheet”);
worksheet = workbook.Worksheets[1];

int index = worksheet.PivotTables.Add("=Sheet1!A1:C21", “A1”, “PT”);
PivotTable pivotTable = worksheet.PivotTables[index];

pivotTable.AddFieldToArea(PivotFieldType.Row, “Header1”);
pivotTable.AddFieldToArea(PivotFieldType.Row, “Header2”);
pivotTable.AddFieldToArea(PivotFieldType.Data, “Header3”);

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


var fc = pivotTable.PivotFormatConditions[pivotTable.PivotFormatConditions.Add()];
fc.ScopeType = PivotConditionFormatScopeType.field;

FormatConditionCollection fcs = fc.FormatConditions;
CellArea area = new CellArea
{
StartRow = 1,
EndRow = 23,
StartColumn = 2,
EndColumn = 2
};

FormatCondition condition = fcs[fcs.Add(area, FormatConditionType.DataBar, OperatorType.Equal, “”, “”)[0]];
condition.Type = FormatConditionType.DataBar;

fcs.AddArea(area);

condition.DataBar.MinCfvo.Type = FormatConditionValueType.Number;
condition.DataBar.MinCfvo.Value = 0;
condition.DataBar.MaxCfvo.Type = FormatConditionValueType.Number;
condition.DataBar.MaxCfvo.Value = 1;
condition.DataBar.Color = Color.Green;
condition.DataBar.ShowValue = true;


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

yes I found this code a few days ago but I asked specifically about the cell area. you have hard coded the cell area but in fact the area for a pivot can be dynamic. so how do you handle the variable size? here's what i started to do but you will see the comment where i hacked the position because you need to know what column you are applying the condition on:

PivotField pfUtil = pivotTable.DataFields["ConditionalField"];
if (pfUtil != null)
{
for (int i = pivotTable.DataBodyRange.StartColumn; i < pivotTable.DataBodyRange.EndColumn; i++)
{

//TODO HACK hard coded position
if (i % 3 != 0)
continue;


PivotFormatCondition pfc = pivotTable.PivotFormatConditions[pivotTable.PivotFormatConditions.Add()];
pfc.ScopeType = PivotConditionFormatScopeType.field;

//figureout the cell area
//every second column in the data area needs this...


CellArea area = new CellArea
{
StartRow = pivotTable.DataBodyRange.StartRow,
EndRow = pivotTable.DataBodyRange.EndRow,
StartColumn = i-1,
EndColumn = i-1
};


FormatCondition condition = pfc.FormatConditions[pfc.FormatConditions.Add(area, FormatConditionType.CellValue, OperatorType.GreaterThan, "100", "")[0]];
condition.Style.BackgroundColor = Color.Red;
condition.Style.Font.Color = Color.White;
}
}

Isn't there a better way?

6

Hi,

Thanks for your posting and using Aspose.Cells.

We are afraid, there is no better way to meet your needs.

7

OK maybe if I ask the question differently you will have a solution.

Is there any way to get the range for a single column within a pivot range?

for example if I add myFieldA to the column area and call calculaterange() etc is there any way to find out what cells are used for myFieldA???

8

Hi,

We could get the column range of the pivottable,but could not get area of the only one pivot field

Please try this sample code:

C#


CellArea ca = wb.Worksheets[0].PivotTables[0].ColumnRange;