Conditional Formatting with ColorScale

Aspose.Cells Product Family
1

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.)


Here is my sample code:

Dim wb As New Workbook()
Dim ws As Worksheet = wb.Worksheets(0)

Dim area As New CellArea()
area.StartRow = 11
area.EndRow = 20
area.StartColumn = 1
area.EndColumn = 1

Dim fcs As FormatConditionCollection = ws.ConditionalFormattings(ws.ConditionalFormattings.Add())
fcs.AddArea(area)
Dim fc As FormatCondition = fcs(fcs.AddCondition(FormatConditionType.ColorScale))
fc.ColorScale.MaxColor = System.Drawing.Color.Green
fc.ColorScale.MaxCfvo.Type = FormatConditionValueType.Max
fc.ColorScale.MidColor = System.Drawing.Color.Yellow
fc.ColorScale.MidCfvo.Type = FormatConditionValueType.Percentile
fc.ColorScale.MidCfvo.Value = 50
fc.ColorScale.MinColor = System.Drawing.Color.Red
fc.ColorScale.MinCfvo.Type = FormatConditionValueType.Min


If I use another type for conditional formatting, it does work. For instance, if I change to this:

Dim fc As FormatCondition = fcs(fcs.AddCondition(FormatConditionType.ColorScale, OperatorType.Between, “5”, “10”))
fc.Style.BackgroundColor = Color.Red

I see the rules and I see cells highlighted in red.

Please advise as to what is preventing the ColorScale rules from being created. Thanks in advance,

John
2
Hi,

Please try our latest version/fix v7.0.2.4. I have tested with my following sample code it works fine.

Sample code:

Dim wb As New Workbook()
Dim ws As Worksheet = wb.Worksheets(0)

Dim area As New CellArea()
area.StartRow = 11
area.EndRow = 20
area.StartColumn = 1
area.EndColumn = 1

Dim fcs As FormatConditionCollection = ws.ConditionalFormattings(ws.ConditionalFormattings.Add())
fcs.AddArea(area)
Dim fc As FormatCondition = fcs(fcs.AddCondition(FormatConditionType.ColorScale))


fc.ColorScale.MinCfvo.Type = FormatConditionValueType.Number
fc.ColorScale.MinCfvo.Value = 9
fc.ColorScale.MinColor = Color.Red

wb.Save("e:\test2\conditoutput.xlsx")

If you still find the issue, give us complete sample code with template file here to show the issue, we will check it soon.

Thank you.
3

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

4

Hi,


I am afraid apply conditional formatting to pivot report cells is not supported. I have logged a ticket for your feature request with an id: CELLSNET-40082. We will look into it soon.

Thank you.
5

Thanks for your response. I have a workaround for now, but hopefully can take advantage of this functionality in a later release!

6

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;



7

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.