Thank you for your response.
I wanted to know how can I apply the conditional formatting on only one of the data fields and not all?
Also I am not getting how sorting in descending order will be applied on data fields as we are setting ascend sort false for one of the rowfields?
@Shivammirje1998,
In the pivot table, you can’t sort the datafield directly. You need to sort rowfields according to datafields, so as to sort datafields indirectly. After rowfields are sorted in descending order according to datafields, datafields are also sorted in descending order naturally.
@Shivammirje1998，
About how can I apply the conditional formatting on only one of the data fields:
Please check the attachment datafield.PNG (4.4 KB)
and try it:
Workbook workbook = new Workbook("Sample.xlsx");
Worksheet sheet = workbook.Worksheets.Add("NewSheet");
PivotTableCollection pivotTables = sheet.PivotTables;
// Adding a PivotTable to the worksheet
int index = pivotTables.Add("=Masterfile!$A$1:$D$8", "A1", "PivotTable3");
// Accessing the instance of the newly added PivotTable
Aspose.Cells.Pivot.PivotTable pivotTable = pivotTables[index];
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, 0);
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Page, 1);
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Data, 2);
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Data, 3);
//Add the following code to move PivotTable.DataField to column area
if (pivotTable.DataField != null)
{
pivotTable.AddFieldToArea(PivotFieldType.Column, pivotTable.DataField);
}
pivotTable.PivotTableStyleType = PivotTableStyleType.PivotTableStyleLight16;
pivotTable.DataFields[0].NumberFormat = "0.0,";
pivotTable.DataFields[1].NumberFormat = "0.0,";
#region sort pivot table data columns in descending order
PivotField currRowField = pivotTable.RowFields[0];
currRowField.IsAutoSort = true;
currRowField.IsAscendSort = false;
//Sort PivotField in row area via DataField named "Quantity";
currRowField.AutoSortField = 0;
#endregion
#region add conditional foramts
int formatIndex = pivotTable.PivotFormatConditions.Add();
PivotFormatCondition pfc = pivotTable.PivotFormatConditions[formatIndex];
//Set conditional styles only in the DataField named "Quantity"
pfc.AddDataAreaCondition(pivotTable.DataFields[0]);
pfc.SetConditionalAreas();
FormatConditionCollection fcc = pfc.FormatConditions;
int idx = fcc.AddCondition(FormatConditionType.CellValue);
FormatCondition fc = fcc[idx];
fc.Formula1 = "3000";
fc.Operator = OperatorType.GreaterOrEqual;
fc.Style.BackgroundColor = Color.Red;
#endregion
// Saving the Excel file
workbook.Save("sample_out.xlsx");
Hi @John.He,
Thanks for the quick response.
So it will first sort “Quantity” column in descending order and then the “Total Sales” column right?
If so is it possible to make it otherwise without changing column order?
@Shivammirje1998,
First arrange the three numbers in Figure 1 in descending order, and then arrange the whole row of data in descending order.
Please check the attachment.sort result.PNG (84.5 KB)
@Shivammirje1998,
Thanks for your feedback and using Aspose.Cells.
Let us know if you encounter any other issue, we will be glad to look into it and help you further.
Yes, you need to buy new license (if your license is already expired).
PS. you may open your license into notepad and check its subscription expiry date for confirmation. Please do not edit/update your license, least it will not work any more.
Please note, I am able to reproduce an issue while setting columns’ width in pivot table. I found I could not extend columns widths if those columns are part of Pivot table. I tried to add the following lines to the code segment but to no avail:
e.g. Sample code:
.........
CellArea area = pivotTable.RowRange;
int start = area.StartColumn;
sheet.Cells.SetColumnWidthPixel(start, 220);
I have logged a ticket with an id “CELLSNET-49896” for your issue. We will look into it soon.
Once we have an update on it, we will let you know.
Currently, you can uncheck “AutoFit columns widths on update” to fix your issue for now. See the attached screenshot for your reference. And, we will provide a property to get and set “AutoFit columns widths on update” attribute programmatically. Please note, you have to set the property as false to make Cells.SetColumnWidth work. image_2021_11_22T14_06_47_419Z.png (253.6 KB)
we will hope we can provide a fix within 1-2 days.
Hi @Amjad_Sahi,
Thanks for your response. I tried unchecking the “AutoFit columns widths on update” but as soon as new pivot table is created that option is already set to true. So I am not able to get required result.
I appreciate your efforts.
And I am looking forward for the property to get and set this field programmatically.
