I am using conditional formatting in an Excel spreadsheet. Several rules work; however, I cannot seem to get ColorScale rules to work. After running my .NET program, I open the spreadsheet it produced but do not see the ColorScale rules at all. (Using Excel 2007, I go to Styles pane > Conditional Formatting > Manage Rules > Show for This Worksheet.)
Thanks, I upgraded from 7.0.2.2 to 7.0.2.4 and can now see the ColorScale working.
Is it possible to apply a ColorScale to a pivot table? I can do this manually in Excel, but when I use ASPOSE to apply the FormatCondition to an area corresponding to my pivot table cells, nothing appears.
Here is my sample code. Basically, I bind a datatable to Excel, process so that the pivot table is populated, then try to set conditional formatting for each column in the pivot table.
Dim designer As New WorkbookDesigner()
designer.Workbook = New Workbook("C:\Template.xlsx", New LoadOptions(LoadFormat.Xlsx))
designer.SetDataSource(dataTable)
designer.Process()
Dim wb As Workbook = designer.Workbook
Dim ws As Worksheet = wb.Worksheets(0)
Dim pt As PivotTable = ws.PivotTables(0)
pt.DataFields(0).NumberFormat = "0%"
pt.ColumnFields(0).IsAutoSort = True
pt.ColumnFields(0).IsAscendSort = True
pt.RowFields(0).IsAutoSort = True
pt.RowFields(0).IsAscendSort = True
For i As Integer = 0 To pt.ColumnRange.EndColumn - 1
Dim area As New CellArea()
area.StartRow = pt.RowRange.StartRow + 1
area.EndRow = pt.RowRange.EndRow
area.StartColumn = i + 1
area.EndColumn = i + 1
Dim fcc As FormatConditionCollection = ws.ConditionalFormattings(ws.ConditionalFormattings.Add())
fcc.AddArea(area)
Dim fc As FormatCondition = fcc(fcc.AddCondition(FormatConditionType.ColorScale))
Next
Hi,
Thanks for your response. I have a workaround for now, but hopefully can take advantage of this functionality in a later release!
Hi,
We have supported the condition format in pivot table in the latest: Aspose.Cells for .NET v7.0.4.4
Please use the following sample code :
C#
int index = wb.Worksheets[0].PivotTables[0].PivotFormatConditions.Add();
PivotFormatCondition pfc = wb.Worksheets[0].PivotTables[0].PivotFormatConditions[index];
pfc.ScopeType = PivotConditionFormatScopeType.data;
FormatConditionCollection fcs = pfc.FormatConditions;
CellArea ca = new CellArea();
ca.StartRow = 9;
ca.EndRow = 9;
ca.StartColumn = 5;
ca.EndColumn = 5;
int[] t = fcs.Add(ca, FormatConditionType.ColorScale, OperatorType.Equal, “”, “”);
FormatCondition fc = fcs[t[0]];
ColorScale scale = fc.ColorScale;
scale.MidColor = Color.Red;
scale.MinCfvo.Type = FormatConditionValueType.Max;
scale.MinCfvo.Value = 10;
The issues you have found earlier (filed as CELLSNET-40082) have been fixed in this update.
This message was posted using Notification2Forum from Downloads module by aspose.notifier.