Free cookie consent management tool by TermsFeed Policy Generator

source: branches/2389-EpsLexicase/HeuristicLab.ExtLibs/HeuristicLab.EPPlus/4.0.3/EPPlus-4.0.3/ConditionalFormatting/CF Implementation.cs

Last change on this file was 12074, checked in by sraggl, 10 years ago

#2341: Added EPPlus-4.0.3 to ExtLibs
File size: 14.9 KB
Line 
1#region TODO
2//TODO: Add the "DataBar" extended options
3//TODO: Add tests for all the rules
4//TODO: Add the IconSet options
5//TODO: Add all the "extList" options
6#endregion
7
8#region §18.3.1.18 conditionalFormatting (Conditional Formatting)
9//Childs:
10//cfRule          (Conditional Formatting Rule) §18.3.1.10
11//extLst          (Future Feature Data Storage Area) §18.2.10
12
13//Attributes:
14//pivot
15//sqref           ST_Sqref simple type (§18.18.76)
16#endregion
17
18#region §18.3.1.10 cfRule (Conditional Formatting Rule)
19//Childs:
20//colorScale      (Color Scale) §18.3.1.16
21//dataBar         (Data Bar) §18.3.1.28
22//extLst          (Future Feature Data Storage Area) §18.2.10
23//formula         (Formula) §18.3.1.43
24//iconSet         (Icon Set) §18.3.1.49
25
26//Attributes:
27//-----------
28//priority        (Priority) The priority of this conditional formatting rule. This value is used to determine which
29//                format should be evaluated and rendered. Lower numeric values are higher priority than
30//                higher numeric values, where 1 is the highest priority.
31//stopIfTrue      (Stop If True) If this flag is 1, no rules with lower priority shall be applied over this rule, when this rule
32//                evaluates to true.
33//type            (Type) Type of conditional formatting rule. ST_CfType §18.18.12.
34//aboveAverage    Indicates whether the rule is an "above average" rule. 1 indicates 'above average'.
35//                This attribute is ignored if type is not equal to aboveAverage.
36//equalAverage    (Equal Average)
37//                Flag indicating whether the 'aboveAverage' and 'belowAverage' criteria is inclusive of the
38//                average itself, or exclusive of that value. 1 indicates to include the average value in the
39//                criteria. This attribute is ignored if type is not equal to aboveAverage.
40//bottom          (Bottom N) Indicates whether a "top/bottom n" rule is a "bottom n" rule. 1 indicates 'bottom'.
41//                This attribute is ignored if type is not equal to top10.
42//dxfId           (Differential Formatting Id)
43//                This is an index to a dxf element in the Styles Part indicating which cell formatting to
44//                apply when the conditional formatting rule criteria is met. ST_DxfId simple type (§18.18.25).
45//operator        (Operator) The operator in a "cell value is" conditional formatting rule. This attribute is ignored if
46//                type is not equal to cellIs. The possible values ST_ConditionalFormattingOperator simple type (§18.18.15).
47//percent         (Top 10 Percent)
48//                Indicates whether a "top/bottom n" rule is a "top/bottom n percent" rule. This attribute
49//                is ignored if type is not equal to top10.
50//rank            (Rank) The value of "n" in a "top/bottom n" conditional formatting rule. This attribute is ignored
51//                if type is not equal to top10.
52//stdDev          (StdDev) The number of standard deviations to include above or below the average in the
53//                conditional formatting rule. This attribute is ignored if type is not equal to aboveAverage.
54//                If a value is present for stdDev and the rule type = aboveAverage, then this rule is automatically an
55//                "above or below N standard deviations" rule.
56//text            (Text) The text value in a "text contains" conditional formatting rule. This attribute is ignored if
57//                type is not equal to containsText.
58//timePeriod      (Time Period) The applicable time period in a "date occurring…" conditional formatting rule. This
59//                attribute is ignored if type is not equal to timePeriod. ST_TimePeriod §18.18.82.
60#endregion
61
62#region Conditional Formatting XML examples
63// All the examples are assumed to be inside <conditionalFormatting sqref="A1:A10">
64
65#region Example "beginsWith"
66//<x:cfRule type="beginsWith" dxfId="6" priority="5" operator="beginsWith" text="a">
67//  <x:formula>LEFT(A1,LEN("a"))="a"</x:formula>
68//</x:cfRule>
69
70//<x:cfRule type="beginsWith" dxfId="5" priority="14" operator="beginsWith" text="&quot;&lt;&gt;">
71//  <x:formula>LEFT(A3,LEN("""&lt;&gt;"))="""&lt;&gt;"</x:formula>
72//</x:cfRule>
73#endregion
74
75#region Example "between"
76//<x:cfRule type="cellIs" dxfId="8" priority="10" operator="between">
77//  <x:formula>3</x:formula>
78//  <x:formula>7</x:formula>
79//</x:cfRule>
80#endregion
81
82#region Example "containsText"
83//<x:cfRule type="containsText" dxfId="5" priority="4" operator="containsText" text="c">
84//  <x:formula>NOT(ISERROR(SEARCH("c",A1)))</x:formula>
85//</x:cfRule>
86#endregion
87
88#region Example "endsWith"
89//<x:cfRule type="endsWith" dxfId="9" priority="11" operator="endsWith" text="c">
90//  <x:formula>RIGHT(A1,LEN("c"))="c"</x:formula>
91//</x:cfRule>
92#endregion
93
94#region Example "equal"
95//<x:cfRule type="cellIs" dxfId="7" priority="8" operator="equal">
96//  <x:formula>"ab"</x:formula>
97//</x:cfRule>
98#endregion
99
100#region Example "greaterThan"
101//<x:cfRule type="cellIs" dxfId="6" priority="7" operator="greaterThan">
102//  <x:formula>4</x:formula>
103//</x:cfRule>
104#endregion
105
106#region Example "greaterThanOrEqual"
107//<x:cfRule type="cellIs" dxfId="3" priority="4" operator="greaterThanOrEqual">
108//  <x:formula>4</x:formula>
109//</x:cfRule>
110#endregion
111
112#region Example "lessThan"
113//<x:cfRule type="cellIs" dxfId="5" priority="6" operator="lessThan">
114//  <x:formula>4</x:formula>
115//</x:cfRule>
116#endregion
117
118#region Example "lessThanOrEqual"
119//<x:cfRule type="cellIs" dxfId="4" priority="5" operator="lessThanOrEqual">
120//  <x:formula>4</x:formula>
121//</x:cfRule>
122#endregion
123
124#region Example "notBetween"
125//<x:cfRule type="cellIs" dxfId="2" priority="3" operator="notBetween">
126//  <x:formula>3</x:formula>
127//  <x:formula>7</x:formula>
128//</x:cfRule>
129#endregion
130
131#region Example "notContainsText"
132//<x:cfRule type="notContainsText" dxfId="4" priority="3" operator="notContains" text="c">
133//  <x:formula>ISERROR(SEARCH("c",A1))</x:formula>
134//</x:cfRule>
135#endregion
136
137#region Example "notEqual"
138//<x:cfRule type="cellIs" dxfId="1" priority="2" operator="notEqual">
139//  <x:formula>"ab"</x:formula>
140//</x:cfRule>
141#endregion
142
143#region Example "containsBlanks"
144//<x:cfRule type="containsBlanks" dxfId="20" priority="37">
145//  <x:formula>LEN(TRIM(A1))=0</x:formula>
146//</x:cfRule>
147#endregion
148
149#region Example "containsErrors"
150//<x:cfRule type="containsErrors" dxfId="15" priority="19">
151//  <x:formula>ISERROR(A1)</x:formula>
152//</x:cfRule>
153#endregion
154
155#region Example "expression"
156//<x:cfRule type="expression" dxfId="0" priority="1">
157//  <x:formula>RIGHT(J16,1)="b"</x:formula>
158//</x:cfRule>
159#endregion
160
161#region Example "duplicateValues"
162//<x:cfRule type="duplicateValues" dxfId="14" priority="16" />
163#endregion
164
165#region Example "notContainsBlanks"
166//<x:cfRule type="notContainsBlanks" dxfId="12" priority="14">
167//  <x:formula>LEN(TRIM(A1))&gt;0</x:formula>
168//</x:cfRule>
169#endregion
170
171#region Example "notContainsErrors"
172//<x:cfRule type="notContainsErrors" dxfId="11" priority="36">
173//  <x:formula>NOT(ISERROR(A1))</x:formula>
174//</x:cfRule>
175#endregion
176
177#region Example "uniqueValues"
178//<x:cfRule type="uniqueValues" dxfId="13" priority="15" />
179#endregion
180
181#region Example "last7Days"
182//<x:cfRule type="timePeriod" dxfId="39" priority="10" timePeriod="last7Days">
183//  <x:formula>AND(TODAY()-FLOOR(A1,1)&lt;=6,FLOOR(A1,1)&lt;=TODAY())</x:formula>
184//</x:cfRule>
185#endregion
186
187#region Example "lastMonth"
188//<x:cfRule type="timePeriod" dxfId="38" priority="9" timePeriod="lastMonth">
189//  <x:formula>AND(MONTH(A1)=MONTH(EDATE(TODAY(),0-1)),YEAR(A1)=YEAR(EDATE(TODAY(),0-1)))</x:formula>
190//</x:cfRule>
191#endregion
192
193#region Example "lastWeek"
194//<x:cfRule type="timePeriod" dxfId="37" priority="8" timePeriod="lastWeek">
195//  <x:formula>AND(TODAY()-ROUNDDOWN(A1,0)&gt;=(WEEKDAY(TODAY())),TODAY()-ROUNDDOWN(A1,0)&lt;(WEEKDAY(TODAY())+7))</x:formula>
196//</x:cfRule>
197#endregion
198
199#region Example "nextMonth"
200//<x:cfRule type="timePeriod" dxfId="36" priority="7" timePeriod="nextMonth">
201//  <x:formula>AND(MONTH(A1)=MONTH(EDATE(TODAY(),0+1)),YEAR(A1)=YEAR(EDATE(TODAY(),0+1)))</x:formula>
202//</x:cfRule>
203#endregion
204
205#region Example "nextWeek"
206//<x:cfRule type="timePeriod" dxfId="35" priority="6" timePeriod="nextWeek">
207//  <x:formula>AND(ROUNDDOWN(A1,0)-TODAY()&gt;(7-WEEKDAY(TODAY())),ROUNDDOWN(A1,0)-TODAY()&lt;(15-WEEKDAY(TODAY())))</x:formula>
208//</x:cfRule>
209#endregion
210
211#region Example "thisMonth"
212//<x:cfRule type="timePeriod" dxfId="34" priority="5" timePeriod="thisMonth">
213//  <x:formula>AND(MONTH(A1)=MONTH(TODAY()),YEAR(A1)=YEAR(TODAY()))</x:formula>
214//</x:cfRule>
215#endregion
216
217#region Example "thisWeek"
218//<x:cfRule type="timePeriod" dxfId="33" priority="4" timePeriod="thisWeek">
219//  <x:formula>AND(TODAY()-ROUNDDOWN(A1,0)&lt;=WEEKDAY(TODAY())-1,ROUNDDOWN(A1,0)-TODAY()&lt;=7-WEEKDAY(TODAY()))</x:formula>
220//</x:cfRule>
221#endregion
222
223#region Example "today"
224//<x:cfRule type="timePeriod" dxfId="32" priority="3" timePeriod="today">
225//  <x:formula>FLOOR(A1,1)=TODAY()</x:formula>
226//</x:cfRule>
227#endregion
228
229#region Example "tomorrow"
230//<x:cfRule type="timePeriod" dxfId="31" priority="2" timePeriod="tomorrow">
231//  <x:formula>FLOOR(A1,1)=TODAY()+1</x:formula>
232//</x:cfRule>
233#endregion
234
235#region Example "yesterday"
236//<x:cfRule type="timePeriod" dxfId="1" priority="1" timePeriod="yesterday">
237//  <x:formula>FLOOR(A1,1)=TODAY()-1</x:formula>
238//</x:cfRule>
239#endregion
240
241#region Example "twoColorScale"
242//<cfRule type="colorScale" priority="1">
243//  <colorScale>
244//    <cfvo type="min"/>
245//    <cfvo type="max"/>
246//    <color rgb="FFF8696B"/>
247//    <color rgb="FF63BE7B"/>
248//  </colorScale>
249//</cfRule>
250#endregion
251
252#region Examples "iconSet3" (x all the 3 IconSet options)
253//<x:cfRule type="iconSet" priority="30">
254//  <x:iconSet>
255//    <x:cfvo type="percent" val="0" />
256//    <x:cfvo type="percent" val="33" />
257//    <x:cfvo type="percent" val="67" />
258//  </x:iconSet>
259//</x:cfRule>
260
261//<x:cfRule type="iconSet" priority="38">
262//  <x:iconSet iconSet="3Arrows">
263//    <x:cfvo type="percent" val="0" />
264//    <x:cfvo type="percent" val="33" />
265//    <x:cfvo type="percent" val="67" />
266//  </x:iconSet>
267//</x:cfRule>
268#endregion
269
270#region Examples "iconSet4" (x all the 4 IconSet options)
271//<x:cfRule type="iconSet" priority="34">
272//  <x:iconSet iconSet="4ArrowsGray">
273//    <x:cfvo type="percent" val="0" />
274//    <x:cfvo type="percent" val="25" />
275//    <x:cfvo type="percent" val="50" />
276//    <x:cfvo type="percent" val="75" />
277//  </x:iconSet>
278//</x:cfRule>
279#endregion
280
281#region Examples "iconSet5" (x all the 5 IconSet options)
282//<x:cfRule type="iconSet" priority="32">
283//  <x:iconSet iconSet="5ArrowsGray">
284//    <x:cfvo type="percent" val="0" />
285//    <x:cfvo type="percent" val="20" />
286//    <x:cfvo type="percent" val="40" />
287//    <x:cfvo type="percent" val="60" />
288//    <x:cfvo type="percent" val="80" />
289//  </x:iconSet>
290//</x:cfRule>
291#endregion
292
293#region Examples "iconSet" Extended (not implemented yet)
294//<x:extLst>
295//  <x:ext xmlns:x14="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main" uri="{78C0D931-6437-407d-A8EE-F0AAD7539E65}">
296//    <x14:conditionalFormattings>
297//      <x14:conditionalFormatting xmlns:xm="http://schemas.microsoft.com/office/excel/2006/main">
298//        <x14:cfRule type="iconSet" priority="35" id="{F5114369-080A-47E6-B7EE-499137A3C896}">
299//          <x14:iconSet iconSet="3Triangles">
300//            <x14:cfvo type="percent">
301//              <xm:f>0</xm:f>
302//            </x14:cfvo>
303//            <x14:cfvo type="percent">
304//              <xm:f>33</xm:f>
305//            </x14:cfvo>
306//            <x14:cfvo type="percent">
307//              <xm:f>67</xm:f>
308//            </x14:cfvo>
309//          </x14:iconSet>
310//        </x14:cfRule>
311//        <xm:sqref>C3:C12</xm:sqref>
312//      </x14:conditionalFormatting>
313//      <x14:conditionalFormatting xmlns:xm="http://schemas.microsoft.com/office/excel/2006/main">
314//        <x14:cfRule type="iconSet" priority="6" id="{0A327384-BF2F-4BF5-9767-123CD690A536}">
315//          <x14:iconSet iconSet="3Stars">
316//            <x14:cfvo type="percent">
317//              <xm:f>0</xm:f>
318//            </x14:cfvo>
319//            <x14:cfvo type="percent">
320//              <xm:f>33</xm:f>
321//            </x14:cfvo>
322//            <x14:cfvo type="percent">
323//              <xm:f>67</xm:f>
324//            </x14:cfvo>
325//          </x14:iconSet>
326//        </x14:cfRule>
327//        <xm:sqref>A16:A25</xm:sqref>
328//      </x14:conditionalFormatting>
329//      <x14:conditionalFormatting xmlns:xm="http://schemas.microsoft.com/office/excel/2006/main">
330//        <x14:cfRule type="iconSet" priority="19" id="{0DDCA3E4-3536-44B3-A663-4877587295B8}">
331//          <x14:iconSet iconSet="3Triangles">
332//            <x14:cfvo type="percent">
333//              <xm:f>0</xm:f>
334//            </x14:cfvo>
335//            <x14:cfvo type="percent">
336//              <xm:f>33</xm:f>
337//            </x14:cfvo>
338//            <x14:cfvo type="percent">
339//              <xm:f>67</xm:f>
340//            </x14:cfvo>
341//          </x14:iconSet>
342//        </x14:cfRule>
343//        <xm:sqref>C16:C25</xm:sqref>
344//      </x14:conditionalFormatting>
345//      <x14:conditionalFormatting xmlns:xm="http://schemas.microsoft.com/office/excel/2006/main">
346//        <x14:cfRule type="iconSet" priority="2" id="{E4EDD7FB-880C-408F-B87C-C8DA446AEB78}">
347//          <x14:iconSet iconSet="5Boxes">
348//            <x14:cfvo type="percent">
349//              <xm:f>0</xm:f>
350//            </x14:cfvo>
351//            <x14:cfvo type="percent">
352//              <xm:f>20</xm:f>
353//            </x14:cfvo>
354//            <x14:cfvo type="percent">
355//              <xm:f>40</xm:f>
356//            </x14:cfvo>
357//            <x14:cfvo type="percent">
358//              <xm:f>60</xm:f>
359//            </x14:cfvo>
360//            <x14:cfvo type="percent">
361//              <xm:f>80</xm:f>
362//            </x14:cfvo>
363//          </x14:iconSet>
364//        </x14:cfRule>
365//        <xm:sqref>E16:E25</xm:sqref>
366//      </x14:conditionalFormatting>
367//      <x14:conditionalFormatting xmlns:xm="http://schemas.microsoft.com/office/excel/2006/main">
368//        <x14:cfRule type="iconSet" priority="1" id="{4CC82060-CB0A-4A31-AEF2-D1A587AC1674}">
369//          <x14:iconSet iconSet="3Stars" showValue="0" custom="1">
370//            <x14:cfvo type="percent">
371//              <xm:f>0</xm:f>
372//            </x14:cfvo>
373//            <x14:cfvo type="formula">
374//              <xm:f>$F$17</xm:f>
375//            </x14:cfvo>
376//            <x14:cfvo type="num">
377//              <xm:f>4</xm:f>
378//            </x14:cfvo>
379//            <x14:cfIcon iconSet="3Triangles" iconId="1" />
380//            <x14:cfIcon iconSet="4RedToBlack" iconId="3" />
381//            <x14:cfIcon iconSet="3Stars" iconId="2" />
382//          </x14:iconSet>
383//        </x14:cfRule>
384//        <xm:sqref>F16:F25</xm:sqref>
385//      </x14:conditionalFormatting>
386//    </x14:conditionalFormattings>
387//  </x:ext>
388//</x:extLst>
389#endregion
390
391
392#endregion
Note: See TracBrowser for help on using the repository browser.