/
ConditionalFormatting.cs
397 lines (380 loc) · 23 KB
/
ConditionalFormatting.cs
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DevExpress.Spreadsheet;
using System.Drawing;
using DevExpress.Utils;
namespace ConditionalFormatting_WPF_Examples {
class ConditionalFormatting {
#region Actions
public static Action<IWorkbook> AddAverageConditionalFormattingAction = AddAverageConditionalFormatting;
public static Action<IWorkbook> AddRangeConditionalFormattingAction = AddRangeConditionalFormatting;
public static Action<IWorkbook> AddRankConditionalFormattingAction = AddRankConditionalFormatting;
public static Action<IWorkbook> AddTextConditionalFormattingAction = AddTextConditionalFormatting;
public static Action<IWorkbook> AddSpecialConditionalFormattingAction = AddSpecialConditionalFormatting;
public static Action<IWorkbook> AddTimePeriodConditionalFormattingAction = AddTimePeriodConditionalFormatting;
public static Action<IWorkbook> AddExpressionConditionalFormattingAction = AddExpressionConditionalFormatting;
public static Action<IWorkbook> AddFormulaExpressionConditionalFormattingAction = AddFormulaExpressionConditionalFormatting;
public static Action<IWorkbook> AddColorScale2ConditionalFormattingAction = AddColorScale2ConditionalFormatting;
public static Action<IWorkbook> AddColorScale3ConditionalFormattingAction = AddColorScale3ConditionalFormatting;
public static Action<IWorkbook> AddDataBarConditionalFormattingAction = AddDataBarConditionalFormatting;
public static Action<IWorkbook> AddIconSetConditionalFormattingAction = AddIconSetConditionalFormatting;
#endregion
static void AddAverageConditionalFormatting(IWorkbook workbook)
{
workbook.Calculate();
workbook.BeginUpdate();
try
{
Worksheet worksheet = workbook.Worksheets["cfBooks"];
workbook.Worksheets.ActiveWorksheet = worksheet;
#region #AverageConditionalFormatting
ConditionalFormattingCollection conditionalFormattings = worksheet.ConditionalFormattings;
// Create the rule highlighting values that are above the average in cells C2 through C15.
AverageConditionalFormatting cfRule1 = conditionalFormattings.AddAverageConditionalFormatting(worksheet.Range["$C$2:$C$15"], ConditionalFormattingAverageCondition.AboveOrEqual);
// Specify formatting options to be applied to cells if the condition is true.
// Set the background color to yellow.
cfRule1.Formatting.Fill.BackgroundColor = Color.FromArgb(255, 0xFA, 0xF7, 0xAA);
// Set the font color to red.
cfRule1.Formatting.Font.Color = Color.Red;
// Create the rule highlighting values that are one standard deviation below the mean in cells D2 through D15.
AverageConditionalFormatting cfRule2 = conditionalFormattings.AddAverageConditionalFormatting(worksheet.Range["$D$2:$D$15"], ConditionalFormattingAverageCondition.BelowOrEqual, 1);
// Specify formatting options to be applied to cells if the conditions is true.
// Set the background color to light-green.
cfRule2.Formatting.Fill.BackgroundColor = Color.FromArgb(255, 0x9F, 0xFB, 0x69);
// Set the font color to blue-violet.
cfRule2.Formatting.Font.Color = Color.BlueViolet;
#endregion #AverageConditionalFormatting
// Add an explanation to the created rule.
CellRange ruleExplanation = worksheet.Range["A17:G18"];
ruleExplanation.Value = "Determine cost values that are above the average in the first quarter and one standard deviation below the mean in the second quarter.";
}
finally
{
workbook.EndUpdate();
}
}
static void AddRangeConditionalFormatting(IWorkbook workbook)
{
workbook.Calculate();
workbook.BeginUpdate();
try
{
Worksheet worksheet = workbook.Worksheets["cfBooks"];
workbook.Worksheets.ActiveWorksheet = worksheet;
#region #RangeConditionalFormatting
// Create the rule to identify values below 7 and above 19 in cells F2 through F15.
RangeConditionalFormatting cfRule = worksheet.ConditionalFormattings.AddRangeConditionalFormatting(worksheet.Range["$F$2:$F$15"], ConditionalFormattingRangeCondition.Outside, "7", "19");
// Specify formatting options to be applied to cells if the condition is true.
// Set the background color to yellow.
cfRule.Formatting.Fill.BackgroundColor = Color.FromArgb(255, 0xFA, 0xF7, 0xAA);
// Set the font color to red.
cfRule.Formatting.Font.Color = Color.Red;
#endregion #RangeConditionalFormatting
// Add an explanation to the created rule.
CellRange ruleExplanation = worksheet.Range["A17:G18"];
ruleExplanation.Value = "Identify book prices that are below $7 and above $19.";
}
finally
{
workbook.EndUpdate();
}
}
static void AddRankConditionalFormatting(IWorkbook workbook)
{
workbook.Calculate();
workbook.BeginUpdate();
try
{
Worksheet worksheet = workbook.Worksheets["cfBooks"];
workbook.Worksheets.ActiveWorksheet = worksheet;
#region #RankConditionalFormatting
// Create the rule to identify top three values in cells F2 through F15.
RankConditionalFormatting cfRule = worksheet.ConditionalFormattings.AddRankConditionalFormatting(worksheet.Range["$F$2:$F$15"], ConditionalFormattingRankCondition.TopByRank, 3);
// Specify formatting options to be applied to cells if the condition is true.
// Set the background color to dark orchid.
cfRule.Formatting.Fill.BackgroundColor = Color.DarkOrchid;
// Set the outline borders.
cfRule.Formatting.Borders.SetOutsideBorders(Color.Black, BorderLineStyle.Thin);
// Set the font color to white.
cfRule.Formatting.Font.Color = Color.White;
#endregion #RankConditionalFormatting
// Add an explanation to the created rule.
CellRange ruleExplanation = worksheet.Range["A17:G18"];
ruleExplanation.Value = "Identify the top three price values.";
}
finally
{
workbook.EndUpdate();
}
}
static void AddTextConditionalFormatting(IWorkbook workbook)
{
workbook.Calculate();
workbook.BeginUpdate();
try
{
Worksheet worksheet = workbook.Worksheets["cfBooks"];
workbook.Worksheets.ActiveWorksheet = worksheet;
#region #TextConditionalFormatting
// Create the rule to highlight values with the given text string in cells A2 through A15.
TextConditionalFormatting cfRule = worksheet.ConditionalFormattings.AddTextConditionalFormatting(worksheet.Range["$A$2:$A$15"], ConditionalFormattingTextCondition.Contains, "Bradbury");
// Specify formatting options to be applied to cells if the condition is true.
// Set the background color to pink.
cfRule.Formatting.Fill.BackgroundColor = Color.FromArgb(255, 0xE1, 0x95, 0xC2);
#endregion #TextConditionalFormatting
// Add an explanation to the created rule.
CellRange ruleExplanation = worksheet.Range["A17:G18"];
ruleExplanation.Value = "Quickly find books written by Ray Bradbury.";
}
finally
{
workbook.EndUpdate();
}
}
static void AddSpecialConditionalFormatting(IWorkbook workbook)
{
workbook.Calculate();
workbook.BeginUpdate();
try
{
Worksheet worksheet = workbook.Worksheets["cfBooks"];
workbook.Worksheets.ActiveWorksheet = worksheet;
#region #SpecialConditionalFormatting
// Create the rule to identify unique values in cells A2 through A15.
SpecialConditionalFormatting cfRule = worksheet.ConditionalFormattings.AddSpecialConditionalFormatting(worksheet.Range["$A$2:$A$15"], ConditionalFormattingSpecialCondition.ContainUniqueValue);
// Specify formatting options to be applied to cells if the condition is true.
// Set the background color to yellow.
cfRule.Formatting.Fill.BackgroundColor = Color.FromArgb(255, 0xFA, 0xF7, 0xAA);
#endregion #SpecialConditionalFormatting
// Add an explanation to the created rule.
CellRange ruleExplanation = worksheet.Range["A17:G18"];
ruleExplanation.Value = "In a list of authors quickly identify unique values.";
}
finally
{
workbook.EndUpdate();
}
}
static void AddTimePeriodConditionalFormatting(IWorkbook workbook)
{
workbook.Calculate();
workbook.BeginUpdate();
try
{
Worksheet worksheet = workbook.Worksheets["cfTasks"];
workbook.Worksheets.ActiveWorksheet = worksheet;
#region #TimePeriodConditionalFormatting
// Create the rule to highlight today's dates in cells B2 through B6.
TimePeriodConditionalFormatting cfRule =
worksheet.ConditionalFormattings.AddTimePeriodConditionalFormatting(worksheet.Range["$B$2:$B$6"], ConditionalFormattingTimePeriod.Today);
// Specify formatting options to be applied to cells if the condition is true.
// Set the background color to pink.
cfRule.Formatting.Fill.BackgroundColor = Color.FromArgb(255, 0xF2, 0xAE, 0xE3);
#endregion #TimePeriodConditionalFormatting
// Add an explanation to the created rule.
CellRange ruleExplanation = worksheet.Range["A8:B9"];
ruleExplanation.Value = "Determine the today's task in the TO DO list.";
}
finally
{
workbook.EndUpdate();
}
}
static void AddExpressionConditionalFormatting(IWorkbook workbook)
{
workbook.Calculate();
workbook.BeginUpdate();
try
{
Worksheet worksheet = workbook.Worksheets["cfBooks"];
workbook.Worksheets.ActiveWorksheet = worksheet;
#region #ExpressionConditionalFormatting
// Create the rule to identify values that are above the average in cells F2 through F15.
ExpressionConditionalFormatting cfRule =
worksheet.ConditionalFormattings.AddExpressionConditionalFormatting(worksheet.Range["$F$2:$F$15"], ConditionalFormattingExpressionCondition.GreaterThan, "=AVERAGE($F$2:$F$15)");
// Specify formatting options to be applied to cells if the condition is true.
// Set the background color to yellow.
cfRule.Formatting.Fill.BackgroundColor = Color.FromArgb(255, 0xFA, 0xF7, 0xAA);
// Set the font color to red.
cfRule.Formatting.Font.Color = Color.Red;
#endregion #ExpressionConditionalFormatting
// Add an explanation to the created rule.
CellRange ruleExplanation = worksheet.Range["A17:G18"];
ruleExplanation.Value = "Identify book prices that are greater than the average price.";
}
finally
{
workbook.EndUpdate();
}
}
static void AddFormulaExpressionConditionalFormatting(IWorkbook workbook)
{
workbook.Calculate();
workbook.BeginUpdate();
try
{
Worksheet worksheet = workbook.Worksheets["cfBooks"];
workbook.Worksheets.ActiveWorksheet = worksheet;
#region #FormulaExpressionConditionalFormatting
// Create the rule to shade alternate rows without applying a new style.
FormulaExpressionConditionalFormatting cfRule = worksheet.ConditionalFormattings.AddFormulaExpressionConditionalFormatting(worksheet.Range["$A$2:$G$15"], "=MOD(ROW(),2)=1");
// Specify formatting options to be applied to cells if the condition is true.
// Set the background color to light blue.
cfRule.Formatting.Fill.BackgroundColor = Color.FromArgb(255, 0xBC, 0xDA, 0xF7);
#endregion #FormulaExpressionConditionalFormatting
// Add an explanation to the created rule.
CellRange ruleExplanation = worksheet.Range["A17:G18"];
ruleExplanation.Value = "Shade alternate rows in light blue without applying a new style.";
}
finally
{
workbook.EndUpdate();
}
}
static void AddColorScale2ConditionalFormatting(IWorkbook workbook)
{
workbook.Calculate();
workbook.BeginUpdate();
try
{
Worksheet worksheet = workbook.Worksheets["cfBooks"];
workbook.Worksheets.ActiveWorksheet = worksheet;
#region #ColorScale2ConditionalFormatting
ConditionalFormattingCollection conditionalFormattings = worksheet.ConditionalFormattings;
// Set the minimum threshold to the lowest value in the range of cells.
ConditionalFormattingValue minPoint = conditionalFormattings.CreateValue(ConditionalFormattingValueType.MinMax);
// Set the maximum threshold to the highest value in the range of cells.
ConditionalFormattingValue maxPoint = conditionalFormattings.CreateValue(ConditionalFormattingValueType.MinMax);
// Create the two-color scale rule to differentiate low and high values in cells C2 through D15. Blue represents the lower values and yellow represents the higher values.
ColorScale2ConditionalFormatting cfRule = conditionalFormattings.AddColorScale2ConditionalFormatting(worksheet.Range["$C$2:$D$15"], minPoint, Color.FromArgb(255, 0x9D, 0xE9, 0xFA), maxPoint, Color.FromArgb(255, 0xFF, 0xF6, 0xA9));
#endregion #ColorScale2ConditionalFormatting
// Add an explanation to the created rule.
CellRange ruleExplanation = worksheet.Range["A17:G18"];
ruleExplanation.Value = "Examine cost distribution using a gradation of two colors. Blue represents the lower values and yellow represents the higher values.";
}
finally
{
workbook.EndUpdate();
}
}
static void AddColorScale3ConditionalFormatting(IWorkbook workbook)
{
workbook.Calculate();
workbook.BeginUpdate();
try
{
Worksheet worksheet = workbook.Worksheets["cfBooks"];
workbook.Worksheets.ActiveWorksheet = worksheet;
#region #ColorScale3ConditionalFormatting
ConditionalFormattingCollection conditionalFormattings = worksheet.ConditionalFormattings;
// Set the minimum threshold to the lowest value in the range of cells using the MIN() formula.
ConditionalFormattingValue minPoint = conditionalFormattings.CreateValue(ConditionalFormattingValueType.Formula, "=MIN($C$2:$D$15)");
// Set the midpoint threshold to the 50th percentile.
ConditionalFormattingValue midPoint = conditionalFormattings.CreateValue(ConditionalFormattingValueType.Percentile, "50");
// Set the maximum threshold to the highest value in the range of cells using the MAX() formula.
ConditionalFormattingValue maxPoint = conditionalFormattings.CreateValue(ConditionalFormattingValueType.Number, "=MAX($C$2:$D$15)");
// Create the three-color scale rule to determine how values in cells C2 through D15 vary. Red represents the lower values, yellow represents the medium values and sky blue represents the higher values.
ColorScale3ConditionalFormatting cfRule = conditionalFormattings.AddColorScale3ConditionalFormatting(worksheet.Range["$C$2:$D$15"], minPoint, Color.Red, midPoint, Color.Yellow, maxPoint, Color.SkyBlue);
#endregion #ColorScale3ConditionalFormatting
// Add an explanation to the created rule.
CellRange ruleExplanation = worksheet.Range["A17:G18"];
ruleExplanation.Value = "Examine cost distribution using a gradation of three colors. Red represents the lower values, yellow represents the medium values and sky blue represents the higher values.";
}
finally
{
workbook.EndUpdate();
}
}
static void AddDataBarConditionalFormatting(IWorkbook workbook)
{
workbook.Calculate();
workbook.BeginUpdate();
try
{
Worksheet worksheet = workbook.Worksheets["cfBooks"];
workbook.Worksheets.ActiveWorksheet = worksheet;
#region #DataBarConditionalFormatting
ConditionalFormattingCollection conditionalFormattings = worksheet.ConditionalFormattings;
// Set the value corresponding to the shortest bar to the lowest value.
ConditionalFormattingValue lowBound1 = conditionalFormattings.CreateValue(ConditionalFormattingValueType.MinMax);
// Set the value corresponding to the longest bar to the highest value.
ConditionalFormattingValue highBound1 = conditionalFormattings.CreateValue(ConditionalFormattingValueType.MinMax);
// Create the rule to compare values in cells E2 through E15 using data bars.
DataBarConditionalFormatting cfRule1 = conditionalFormattings.AddDataBarConditionalFormatting(worksheet.Range["$E$2:$E$15"], lowBound1, highBound1, DXColor.Green);
// Set the positive bar border color to green.
cfRule1.BorderColor = DXColor.Green;
// Set the negative bar color to red.
cfRule1.NegativeBarColor = DXColor.Red;
// Set the negative bar border color to red.
cfRule1.NegativeBarBorderColor = DXColor.Red;
// Set the axis position to display the axis in the middle of the cell.
cfRule1.AxisPosition = ConditionalFormattingDataBarAxisPosition.Middle;
// Set the axis color to dark blue.
cfRule1.AxisColor = Color.DarkBlue;
// Set the value corresponding to the shortest bar to 0 percent.
ConditionalFormattingValue lowBound2 = conditionalFormattings.CreateValue(ConditionalFormattingValueType.Percent, "0");
// Set the value corresponding to the longest bar to 100 percent.
ConditionalFormattingValue highBound2 = conditionalFormattings.CreateValue(ConditionalFormattingValueType.Percent, "100");
// Create the rule to compare values in cells G2 through G15 using data bars.
DataBarConditionalFormatting cfRule2 = conditionalFormattings.AddDataBarConditionalFormatting(worksheet.Range["$G$2:$G$15"], lowBound2, highBound2, DXColor.SkyBlue);
// Set the data bar border color to sky blue.
cfRule2.BorderColor = DXColor.SkyBlue;
// Specify the solid fill type.
cfRule2.GradientFill = false;
// Hide values of cells to which the rule is applied.
cfRule2.ShowValue = false;
#endregion #DataBarConditionalFormatting
// Add an explanation to the created rule.
CellRange ruleExplanation = worksheet.Range["A17:G18"];
ruleExplanation.Value = "Compare values in the \"Cost Trend\" and \"Markup\" columns using data bars.";
}
finally
{
workbook.EndUpdate();
}
}
static void AddIconSetConditionalFormatting(IWorkbook workbook)
{
workbook.Calculate();
workbook.BeginUpdate();
try
{
Worksheet worksheet = workbook.Worksheets["cfBooks"];
workbook.Worksheets.ActiveWorksheet = worksheet;
#region #IconSetConditionalFormatting
ConditionalFormattingCollection conditionalFormattings = worksheet.ConditionalFormattings;
// Set the first threshold to the lowest value in the range of cells using the MIN() formula.
ConditionalFormattingIconSetValue minPoint = conditionalFormattings.CreateIconSetValue(ConditionalFormattingValueType.Formula, "=MIN($E$2:$E$15)", ConditionalFormattingValueOperator.GreaterOrEqual);
// Set the second threshold to 0.
ConditionalFormattingIconSetValue midPoint = conditionalFormattings.CreateIconSetValue(ConditionalFormattingValueType.Number, "0", ConditionalFormattingValueOperator.GreaterOrEqual);
// Set the third threshold to 0.01.
ConditionalFormattingIconSetValue maxPoint = conditionalFormattings.CreateIconSetValue(ConditionalFormattingValueType.Number, "0.01", ConditionalFormattingValueOperator.GreaterOrEqual);
// Create the rule to apply a specific icon from the three arrow icon set to each cell in the range E2:E15 based on its value.
IconSetConditionalFormatting cfRule = conditionalFormattings.AddIconSetConditionalFormatting(worksheet.Range["$E$2:$E$15"], IconSetType.Arrows3, new ConditionalFormattingIconSetValue[] { minPoint, midPoint, maxPoint });
// Specify the custom icon to be displayed if the second condition is true.
// To do this, set the IconSetConditionalFormatting.IsCustom property to true, which is false by default.
cfRule.IsCustom = true;
// Initialize the ConditionalFormattingCustomIcon object.
ConditionalFormattingCustomIcon cfCustomIcon = new ConditionalFormattingCustomIcon();
// Specify the icon set where you wish to get the icon.
cfCustomIcon.IconSet = IconSetType.TrafficLights13;
// Specify the index of the desired icon in the set.
cfCustomIcon.IconIndex = 1;
// Add the custom icon at the specified position in the initial icon set.
cfRule.SetCustomIcon(1, cfCustomIcon);
// Hide values of cells to which the rule is applied.
cfRule.ShowValue = false;
#endregion #IconSetConditionalFormatting
// Add an explanation to the created rule.
CellRange ruleExplanation = worksheet.Range["A17:G18"];
ruleExplanation.Value = "Identify upward and downward cost trends.";
}
finally
{
workbook.EndUpdate();
}
}
}
}