1 | /*******************************************************************************
|
---|
2 | * You may amend and distribute as you like, but don't remove this header!
|
---|
3 | *
|
---|
4 | * EPPlus provides server-side generation of Excel 2007/2010 spreadsheets.
|
---|
5 | * See http://www.codeplex.com/EPPlus for details.
|
---|
6 | *
|
---|
7 | * Copyright (C) 2011 Jan Källman
|
---|
8 | *
|
---|
9 | * This library is free software; you can redistribute it and/or
|
---|
10 | * modify it under the terms of the GNU Lesser General Public
|
---|
11 | * License as published by the Free Software Foundation; either
|
---|
12 | * version 2.1 of the License, or (at your option) any later version.
|
---|
13 |
|
---|
14 | * This library is distributed in the hope that it will be useful,
|
---|
15 | * but WITHOUT ANY WARRANTY; without even the implied warranty of
|
---|
16 | * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
|
---|
17 | * See the GNU Lesser General Public License for more details.
|
---|
18 | *
|
---|
19 | * The GNU Lesser General Public License can be viewed at http://www.opensource.org/licenses/lgpl-license.php
|
---|
20 | * If you unfamiliar with this license or have questions about it, here is an http://www.gnu.org/licenses/gpl-faq.html
|
---|
21 | *
|
---|
22 | * All code and executables are provided "as is" with no warranty either express or implied.
|
---|
23 | * The author accepts no liability for any damage or loss of business that this product may cause.
|
---|
24 | *
|
---|
25 | * Code change notes:
|
---|
26 | *
|
---|
27 | * Author Change Date
|
---|
28 | * ******************************************************************************
|
---|
29 | * Eyal Seagull Conditional Formatting Adaption 2012-04-03
|
---|
30 | *******************************************************************************/
|
---|
31 | using System;
|
---|
32 | using System.Collections.Generic;
|
---|
33 | using System.Linq;
|
---|
34 | using System.Text;
|
---|
35 | using System.Xml;
|
---|
36 |
|
---|
37 | namespace OfficeOpenXml.ConditionalFormatting
|
---|
38 | {
|
---|
39 | /// <summary>
|
---|
40 | /// Functions related to the ExcelConditionalFormattingRule
|
---|
41 | /// </summary>
|
---|
42 | internal static class ExcelConditionalFormattingRuleType
|
---|
43 | {
|
---|
44 | /// <summary>
|
---|
45 | ///
|
---|
46 | /// </summary>
|
---|
47 | /// <param name="attribute"></param>
|
---|
48 | /// <param name="topNode"></param>
|
---|
49 | /// <param name="nameSpaceManager"></param>
|
---|
50 | /// <returns></returns>
|
---|
51 | internal static eExcelConditionalFormattingRuleType GetTypeByAttrbiute(
|
---|
52 | string attribute,
|
---|
53 | XmlNode topNode,
|
---|
54 | XmlNamespaceManager nameSpaceManager)
|
---|
55 | {
|
---|
56 | switch (attribute)
|
---|
57 | {
|
---|
58 | case ExcelConditionalFormattingConstants.RuleType.AboveAverage:
|
---|
59 | return GetAboveAverageType(
|
---|
60 | topNode,
|
---|
61 | nameSpaceManager);
|
---|
62 |
|
---|
63 | case ExcelConditionalFormattingConstants.RuleType.Top10:
|
---|
64 | return GetTop10Type(
|
---|
65 | topNode,
|
---|
66 | nameSpaceManager);
|
---|
67 |
|
---|
68 | case ExcelConditionalFormattingConstants.RuleType.TimePeriod:
|
---|
69 | return GetTimePeriodType(
|
---|
70 | topNode,
|
---|
71 | nameSpaceManager);
|
---|
72 | case ExcelConditionalFormattingConstants.RuleType.CellIs:
|
---|
73 | return GetCellIs((XmlElement)topNode);
|
---|
74 | case ExcelConditionalFormattingConstants.RuleType.BeginsWith:
|
---|
75 | return eExcelConditionalFormattingRuleType.BeginsWith;
|
---|
76 |
|
---|
77 | //case ExcelConditionalFormattingConstants.RuleType.Between:
|
---|
78 | // return eExcelConditionalFormattingRuleType.Between;
|
---|
79 |
|
---|
80 | case ExcelConditionalFormattingConstants.RuleType.ContainsBlanks:
|
---|
81 | return eExcelConditionalFormattingRuleType.ContainsBlanks;
|
---|
82 |
|
---|
83 | case ExcelConditionalFormattingConstants.RuleType.ContainsErrors:
|
---|
84 | return eExcelConditionalFormattingRuleType.ContainsErrors;
|
---|
85 |
|
---|
86 | case ExcelConditionalFormattingConstants.RuleType.ContainsText:
|
---|
87 | return eExcelConditionalFormattingRuleType.ContainsText;
|
---|
88 |
|
---|
89 | case ExcelConditionalFormattingConstants.RuleType.DuplicateValues:
|
---|
90 | return eExcelConditionalFormattingRuleType.DuplicateValues;
|
---|
91 |
|
---|
92 | case ExcelConditionalFormattingConstants.RuleType.EndsWith:
|
---|
93 | return eExcelConditionalFormattingRuleType.EndsWith;
|
---|
94 |
|
---|
95 | //case ExcelConditionalFormattingConstants.RuleType.Equal:
|
---|
96 | // return eExcelConditionalFormattingRuleType.Equal;
|
---|
97 |
|
---|
98 | case ExcelConditionalFormattingConstants.RuleType.Expression:
|
---|
99 | return eExcelConditionalFormattingRuleType.Expression;
|
---|
100 |
|
---|
101 | //case ExcelConditionalFormattingConstants.RuleType.GreaterThan:
|
---|
102 | // return eExcelConditionalFormattingRuleType.GreaterThan;
|
---|
103 |
|
---|
104 | //case ExcelConditionalFormattingConstants.RuleType.GreaterThanOrEqual:
|
---|
105 | // return eExcelConditionalFormattingRuleType.GreaterThanOrEqual;
|
---|
106 |
|
---|
107 | //case ExcelConditionalFormattingConstants.RuleType.LessThan:
|
---|
108 | // return eExcelConditionalFormattingRuleType.LessThan;
|
---|
109 |
|
---|
110 | //case ExcelConditionalFormattingConstants.RuleType.LessThanOrEqual:
|
---|
111 | // return eExcelConditionalFormattingRuleType.LessThanOrEqual;
|
---|
112 |
|
---|
113 | //case ExcelConditionalFormattingConstants.RuleType.NotBetween:
|
---|
114 | // return eExcelConditionalFormattingRuleType.NotBetween;
|
---|
115 |
|
---|
116 | case ExcelConditionalFormattingConstants.RuleType.NotContainsBlanks:
|
---|
117 | return eExcelConditionalFormattingRuleType.NotContainsBlanks;
|
---|
118 |
|
---|
119 | case ExcelConditionalFormattingConstants.RuleType.NotContainsErrors:
|
---|
120 | return eExcelConditionalFormattingRuleType.NotContainsErrors;
|
---|
121 |
|
---|
122 | case ExcelConditionalFormattingConstants.RuleType.NotContainsText:
|
---|
123 | return eExcelConditionalFormattingRuleType.NotContainsText;
|
---|
124 |
|
---|
125 | //case ExcelConditionalFormattingConstants.RuleType.NotEqual:
|
---|
126 | // return eExcelConditionalFormattingRuleType.NotEqual;
|
---|
127 |
|
---|
128 | case ExcelConditionalFormattingConstants.RuleType.UniqueValues:
|
---|
129 | return eExcelConditionalFormattingRuleType.UniqueValues;
|
---|
130 |
|
---|
131 | case ExcelConditionalFormattingConstants.RuleType.ColorScale:
|
---|
132 | return GetColorScaleType(
|
---|
133 | topNode,
|
---|
134 | nameSpaceManager);
|
---|
135 | case ExcelConditionalFormattingConstants.RuleType.IconSet:
|
---|
136 | return GetIconSetType(topNode, nameSpaceManager);
|
---|
137 | case ExcelConditionalFormattingConstants.RuleType.DataBar:
|
---|
138 | return eExcelConditionalFormattingRuleType.DataBar;
|
---|
139 | }
|
---|
140 |
|
---|
141 | throw new Exception(
|
---|
142 | ExcelConditionalFormattingConstants.Errors.UnexpectedRuleTypeAttribute);
|
---|
143 | }
|
---|
144 |
|
---|
145 | private static eExcelConditionalFormattingRuleType GetCellIs(XmlElement node)
|
---|
146 | {
|
---|
147 | switch(node.GetAttribute("operator"))
|
---|
148 | {
|
---|
149 | case ExcelConditionalFormattingConstants.Operators.BeginsWith:
|
---|
150 | return eExcelConditionalFormattingRuleType.BeginsWith;
|
---|
151 | case ExcelConditionalFormattingConstants.Operators.Between:
|
---|
152 | return eExcelConditionalFormattingRuleType.Between;
|
---|
153 |
|
---|
154 | case ExcelConditionalFormattingConstants.Operators.ContainsText:
|
---|
155 | return eExcelConditionalFormattingRuleType.ContainsText;
|
---|
156 |
|
---|
157 | case ExcelConditionalFormattingConstants.Operators.EndsWith:
|
---|
158 | return eExcelConditionalFormattingRuleType.EndsWith;
|
---|
159 |
|
---|
160 | case ExcelConditionalFormattingConstants.Operators.Equal:
|
---|
161 | return eExcelConditionalFormattingRuleType.Equal;
|
---|
162 |
|
---|
163 | case ExcelConditionalFormattingConstants.Operators.GreaterThan:
|
---|
164 | return eExcelConditionalFormattingRuleType.GreaterThan;
|
---|
165 |
|
---|
166 | case ExcelConditionalFormattingConstants.Operators.GreaterThanOrEqual:
|
---|
167 | return eExcelConditionalFormattingRuleType.GreaterThanOrEqual;
|
---|
168 |
|
---|
169 | case ExcelConditionalFormattingConstants.Operators.LessThan:
|
---|
170 | return eExcelConditionalFormattingRuleType.LessThan;
|
---|
171 |
|
---|
172 | case ExcelConditionalFormattingConstants.Operators.LessThanOrEqual:
|
---|
173 | return eExcelConditionalFormattingRuleType.LessThanOrEqual;
|
---|
174 |
|
---|
175 | case ExcelConditionalFormattingConstants.Operators.NotBetween:
|
---|
176 | return eExcelConditionalFormattingRuleType.NotBetween;
|
---|
177 |
|
---|
178 | case ExcelConditionalFormattingConstants.Operators.NotContains:
|
---|
179 | return eExcelConditionalFormattingRuleType.NotContains;
|
---|
180 |
|
---|
181 | case ExcelConditionalFormattingConstants.Operators.NotEqual:
|
---|
182 | return eExcelConditionalFormattingRuleType.NotEqual;
|
---|
183 | default:
|
---|
184 | throw new Exception(
|
---|
185 | ExcelConditionalFormattingConstants.Errors.UnexistentOperatorTypeAttribute);
|
---|
186 | }
|
---|
187 | }
|
---|
188 | private static eExcelConditionalFormattingRuleType GetIconSetType(XmlNode topNode, XmlNamespaceManager nameSpaceManager)
|
---|
189 | {
|
---|
190 | var node = topNode.SelectSingleNode("d:iconSet/@iconSet", nameSpaceManager);
|
---|
191 | if (node == null)
|
---|
192 | {
|
---|
193 | return eExcelConditionalFormattingRuleType.ThreeIconSet;
|
---|
194 | }
|
---|
195 | else
|
---|
196 | {
|
---|
197 | var v = node.Value;
|
---|
198 |
|
---|
199 | if (v[0] == '3')
|
---|
200 | {
|
---|
201 | return eExcelConditionalFormattingRuleType.ThreeIconSet;
|
---|
202 | }
|
---|
203 | else if (v[0] == '4')
|
---|
204 | {
|
---|
205 | return eExcelConditionalFormattingRuleType.FourIconSet;
|
---|
206 | }
|
---|
207 | else
|
---|
208 | {
|
---|
209 | return eExcelConditionalFormattingRuleType.FiveIconSet;
|
---|
210 | }
|
---|
211 | }
|
---|
212 | }
|
---|
213 |
|
---|
214 | /// <summary>
|
---|
215 | /// Get the "colorScale" rule type according to the number of "cfvo" and "color" nodes.
|
---|
216 | /// If we have excatly 2 "cfvo" and "color" childs, then we return "twoColorScale"
|
---|
217 | /// </summary>
|
---|
218 | /// <returns>TwoColorScale or ThreeColorScale</returns>
|
---|
219 | internal static eExcelConditionalFormattingRuleType GetColorScaleType(
|
---|
220 | XmlNode topNode,
|
---|
221 | XmlNamespaceManager nameSpaceManager)
|
---|
222 | {
|
---|
223 | // Get the <cfvo> nodes
|
---|
224 | var cfvoNodes = topNode.SelectNodes(
|
---|
225 | string.Format(
|
---|
226 | "{0}/{1}",
|
---|
227 | ExcelConditionalFormattingConstants.Paths.ColorScale,
|
---|
228 | ExcelConditionalFormattingConstants.Paths.Cfvo),
|
---|
229 | nameSpaceManager);
|
---|
230 |
|
---|
231 | // Get the <color> nodes
|
---|
232 | var colorNodes = topNode.SelectNodes(
|
---|
233 | string.Format(
|
---|
234 | "{0}/{1}",
|
---|
235 | ExcelConditionalFormattingConstants.Paths.ColorScale,
|
---|
236 | ExcelConditionalFormattingConstants.Paths.Color),
|
---|
237 | nameSpaceManager);
|
---|
238 |
|
---|
239 | // We determine if it is "TwoColorScale" or "ThreeColorScale" by the
|
---|
240 | // number of <cfvo> and <color> inside the <colorScale> node
|
---|
241 | if ((cfvoNodes == null) || (cfvoNodes.Count < 2) || (cfvoNodes.Count > 3)
|
---|
242 | || (colorNodes == null) || (colorNodes.Count < 2) || (colorNodes.Count > 3)
|
---|
243 | || (cfvoNodes.Count != colorNodes.Count))
|
---|
244 | {
|
---|
245 | throw new Exception(
|
---|
246 | ExcelConditionalFormattingConstants.Errors.WrongNumberCfvoColorNodes);
|
---|
247 | }
|
---|
248 |
|
---|
249 | // Return the corresponding rule type (TwoColorScale or ThreeColorScale)
|
---|
250 | return (cfvoNodes.Count == 2)
|
---|
251 | ? eExcelConditionalFormattingRuleType.TwoColorScale
|
---|
252 | : eExcelConditionalFormattingRuleType.ThreeColorScale;
|
---|
253 | }
|
---|
254 |
|
---|
255 | /// <summary>
|
---|
256 | /// Get the "aboveAverage" rule type according to the follwoing attributes:
|
---|
257 | /// "AboveAverage", "EqualAverage" and "StdDev".
|
---|
258 | ///
|
---|
259 | /// @StdDev greater than "0" == AboveStdDev
|
---|
260 | /// @StdDev less than "0" == BelowStdDev
|
---|
261 | /// @AboveAverage = "1"/null and @EqualAverage = "0"/null == AboveAverage
|
---|
262 | /// @AboveAverage = "1"/null and @EqualAverage = "1" == AboveOrEqualAverage
|
---|
263 | /// @AboveAverage = "0" and @EqualAverage = "0"/null == BelowAverage
|
---|
264 | /// @AboveAverage = "0" and @EqualAverage = "1" == BelowOrEqualAverage
|
---|
265 | /// /// </summary>
|
---|
266 | /// <returns>AboveAverage, AboveOrEqualAverage, BelowAverage or BelowOrEqualAverage</returns>
|
---|
267 | internal static eExcelConditionalFormattingRuleType GetAboveAverageType(
|
---|
268 | XmlNode topNode,
|
---|
269 | XmlNamespaceManager nameSpaceManager)
|
---|
270 | {
|
---|
271 | // Get @StdDev attribute
|
---|
272 | int? stdDev = ExcelConditionalFormattingHelper.GetAttributeIntNullable(
|
---|
273 | topNode,
|
---|
274 | ExcelConditionalFormattingConstants.Attributes.StdDev);
|
---|
275 |
|
---|
276 | if (stdDev > 0)
|
---|
277 | {
|
---|
278 | // @StdDev > "0" --> AboveStdDev
|
---|
279 | return eExcelConditionalFormattingRuleType.AboveStdDev;
|
---|
280 | }
|
---|
281 |
|
---|
282 | if (stdDev < 0)
|
---|
283 | {
|
---|
284 | // @StdDev < "0" --> BelowStdDev
|
---|
285 | return eExcelConditionalFormattingRuleType.BelowStdDev;
|
---|
286 | }
|
---|
287 |
|
---|
288 | // Get @AboveAverage attribute
|
---|
289 | bool? isAboveAverage = ExcelConditionalFormattingHelper.GetAttributeBoolNullable(
|
---|
290 | topNode,
|
---|
291 | ExcelConditionalFormattingConstants.Attributes.AboveAverage);
|
---|
292 |
|
---|
293 | // Get @EqualAverage attribute
|
---|
294 | bool? isEqualAverage = ExcelConditionalFormattingHelper.GetAttributeBoolNullable(
|
---|
295 | topNode,
|
---|
296 | ExcelConditionalFormattingConstants.Attributes.EqualAverage);
|
---|
297 |
|
---|
298 | if ((isAboveAverage == null) || (isAboveAverage == true))
|
---|
299 | {
|
---|
300 | if (isEqualAverage == true)
|
---|
301 | {
|
---|
302 | // @AboveAverage = "1"/null and @EqualAverage = "1" == AboveOrEqualAverage
|
---|
303 | return eExcelConditionalFormattingRuleType.AboveOrEqualAverage;
|
---|
304 | }
|
---|
305 |
|
---|
306 | // @AboveAverage = "1"/null and @EqualAverage = "0"/null == AboveAverage
|
---|
307 | return eExcelConditionalFormattingRuleType.AboveAverage;
|
---|
308 | }
|
---|
309 |
|
---|
310 | if (isEqualAverage == true)
|
---|
311 | {
|
---|
312 | // @AboveAverage = "0" and @EqualAverage = "1" == BelowOrEqualAverage
|
---|
313 | return eExcelConditionalFormattingRuleType.BelowOrEqualAverage;
|
---|
314 | }
|
---|
315 |
|
---|
316 | // @AboveAverage = "0" and @EqualAverage = "0"/null == BelowAverage
|
---|
317 | return eExcelConditionalFormattingRuleType.BelowAverage;
|
---|
318 | }
|
---|
319 |
|
---|
320 | /// <summary>
|
---|
321 | /// Get the "top10" rule type according to the follwoing attributes:
|
---|
322 | /// "Bottom" and "Percent"
|
---|
323 | ///
|
---|
324 | /// @Bottom = "1" and @Percent = "0"/null == Bottom
|
---|
325 | /// @Bottom = "1" and @Percent = "1" == BottomPercent
|
---|
326 | /// @Bottom = "0"/null and @Percent = "0"/null == Top
|
---|
327 | /// @Bottom = "0"/null and @Percent = "1" == TopPercent
|
---|
328 | /// /// </summary>
|
---|
329 | /// <returns>Top, TopPercent, Bottom or BottomPercent</returns>
|
---|
330 | public static eExcelConditionalFormattingRuleType GetTop10Type(
|
---|
331 | XmlNode topNode,
|
---|
332 | XmlNamespaceManager nameSpaceManager)
|
---|
333 | {
|
---|
334 | // Get @Bottom attribute
|
---|
335 | bool? isBottom = ExcelConditionalFormattingHelper.GetAttributeBoolNullable(
|
---|
336 | topNode,
|
---|
337 | ExcelConditionalFormattingConstants.Attributes.Bottom);
|
---|
338 |
|
---|
339 | // Get @Percent attribute
|
---|
340 | bool? isPercent = ExcelConditionalFormattingHelper.GetAttributeBoolNullable(
|
---|
341 | topNode,
|
---|
342 | ExcelConditionalFormattingConstants.Attributes.Percent);
|
---|
343 |
|
---|
344 | if (isBottom == true)
|
---|
345 | {
|
---|
346 | if (isPercent == true)
|
---|
347 | {
|
---|
348 | // @Bottom = "1" and @Percent = "1" == BottomPercent
|
---|
349 | return eExcelConditionalFormattingRuleType.BottomPercent;
|
---|
350 | }
|
---|
351 |
|
---|
352 | // @Bottom = "1" and @Percent = "0"/null == Bottom
|
---|
353 | return eExcelConditionalFormattingRuleType.Bottom;
|
---|
354 | }
|
---|
355 |
|
---|
356 | if (isPercent == true)
|
---|
357 | {
|
---|
358 | // @Bottom = "0"/null and @Percent = "1" == TopPercent
|
---|
359 | return eExcelConditionalFormattingRuleType.TopPercent;
|
---|
360 | }
|
---|
361 |
|
---|
362 | // @Bottom = "0"/null and @Percent = "0"/null == Top
|
---|
363 | return eExcelConditionalFormattingRuleType.Top;
|
---|
364 | }
|
---|
365 |
|
---|
366 | /// <summary>
|
---|
367 | /// Get the "timePeriod" rule type according to "TimePeriod" attribute.
|
---|
368 | /// /// </summary>
|
---|
369 | /// <returns>Last7Days, LastMonth etc.</returns>
|
---|
370 | public static eExcelConditionalFormattingRuleType GetTimePeriodType(
|
---|
371 | XmlNode topNode,
|
---|
372 | XmlNamespaceManager nameSpaceManager)
|
---|
373 | {
|
---|
374 | eExcelConditionalFormattingTimePeriodType timePeriod = ExcelConditionalFormattingTimePeriodType.GetTypeByAttribute(
|
---|
375 | ExcelConditionalFormattingHelper.GetAttributeString(
|
---|
376 | topNode,
|
---|
377 | ExcelConditionalFormattingConstants.Attributes.TimePeriod));
|
---|
378 |
|
---|
379 | switch (timePeriod)
|
---|
380 | {
|
---|
381 | case eExcelConditionalFormattingTimePeriodType.Last7Days:
|
---|
382 | return eExcelConditionalFormattingRuleType.Last7Days;
|
---|
383 |
|
---|
384 | case eExcelConditionalFormattingTimePeriodType.LastMonth:
|
---|
385 | return eExcelConditionalFormattingRuleType.LastMonth;
|
---|
386 |
|
---|
387 | case eExcelConditionalFormattingTimePeriodType.LastWeek:
|
---|
388 | return eExcelConditionalFormattingRuleType.LastWeek;
|
---|
389 |
|
---|
390 | case eExcelConditionalFormattingTimePeriodType.NextMonth:
|
---|
391 | return eExcelConditionalFormattingRuleType.NextMonth;
|
---|
392 |
|
---|
393 | case eExcelConditionalFormattingTimePeriodType.NextWeek:
|
---|
394 | return eExcelConditionalFormattingRuleType.NextWeek;
|
---|
395 |
|
---|
396 | case eExcelConditionalFormattingTimePeriodType.ThisMonth:
|
---|
397 | return eExcelConditionalFormattingRuleType.ThisMonth;
|
---|
398 |
|
---|
399 | case eExcelConditionalFormattingTimePeriodType.ThisWeek:
|
---|
400 | return eExcelConditionalFormattingRuleType.ThisWeek;
|
---|
401 |
|
---|
402 | case eExcelConditionalFormattingTimePeriodType.Today:
|
---|
403 | return eExcelConditionalFormattingRuleType.Today;
|
---|
404 |
|
---|
405 | case eExcelConditionalFormattingTimePeriodType.Tomorrow:
|
---|
406 | return eExcelConditionalFormattingRuleType.Tomorrow;
|
---|
407 |
|
---|
408 | case eExcelConditionalFormattingTimePeriodType.Yesterday:
|
---|
409 | return eExcelConditionalFormattingRuleType.Yesterday;
|
---|
410 | }
|
---|
411 |
|
---|
412 | throw new Exception(
|
---|
413 | ExcelConditionalFormattingConstants.Errors.UnexistentTimePeriodTypeAttribute);
|
---|
414 | }
|
---|
415 |
|
---|
416 | /// <summary>
|
---|
417 | ///
|
---|
418 | /// </summary>
|
---|
419 | /// <param name="type"></param>
|
---|
420 | /// <returns></returns>
|
---|
421 | public static string GetAttributeByType(
|
---|
422 | eExcelConditionalFormattingRuleType type)
|
---|
423 | {
|
---|
424 | switch (type)
|
---|
425 | {
|
---|
426 | case eExcelConditionalFormattingRuleType.AboveAverage:
|
---|
427 | case eExcelConditionalFormattingRuleType.AboveOrEqualAverage:
|
---|
428 | case eExcelConditionalFormattingRuleType.BelowAverage:
|
---|
429 | case eExcelConditionalFormattingRuleType.BelowOrEqualAverage:
|
---|
430 | case eExcelConditionalFormattingRuleType.AboveStdDev:
|
---|
431 | case eExcelConditionalFormattingRuleType.BelowStdDev:
|
---|
432 | return ExcelConditionalFormattingConstants.RuleType.AboveAverage;
|
---|
433 |
|
---|
434 | case eExcelConditionalFormattingRuleType.Bottom:
|
---|
435 | case eExcelConditionalFormattingRuleType.BottomPercent:
|
---|
436 | case eExcelConditionalFormattingRuleType.Top:
|
---|
437 | case eExcelConditionalFormattingRuleType.TopPercent:
|
---|
438 | return ExcelConditionalFormattingConstants.RuleType.Top10;
|
---|
439 |
|
---|
440 | case eExcelConditionalFormattingRuleType.Last7Days:
|
---|
441 | case eExcelConditionalFormattingRuleType.LastMonth:
|
---|
442 | case eExcelConditionalFormattingRuleType.LastWeek:
|
---|
443 | case eExcelConditionalFormattingRuleType.NextMonth:
|
---|
444 | case eExcelConditionalFormattingRuleType.NextWeek:
|
---|
445 | case eExcelConditionalFormattingRuleType.ThisMonth:
|
---|
446 | case eExcelConditionalFormattingRuleType.ThisWeek:
|
---|
447 | case eExcelConditionalFormattingRuleType.Today:
|
---|
448 | case eExcelConditionalFormattingRuleType.Tomorrow:
|
---|
449 | case eExcelConditionalFormattingRuleType.Yesterday:
|
---|
450 | return ExcelConditionalFormattingConstants.RuleType.TimePeriod;
|
---|
451 |
|
---|
452 | case eExcelConditionalFormattingRuleType.Between:
|
---|
453 | case eExcelConditionalFormattingRuleType.Equal:
|
---|
454 | case eExcelConditionalFormattingRuleType.GreaterThan:
|
---|
455 | case eExcelConditionalFormattingRuleType.GreaterThanOrEqual:
|
---|
456 | case eExcelConditionalFormattingRuleType.LessThan:
|
---|
457 | case eExcelConditionalFormattingRuleType.LessThanOrEqual:
|
---|
458 | case eExcelConditionalFormattingRuleType.NotBetween:
|
---|
459 | case eExcelConditionalFormattingRuleType.NotEqual:
|
---|
460 | return ExcelConditionalFormattingConstants.RuleType.CellIs;
|
---|
461 |
|
---|
462 | case eExcelConditionalFormattingRuleType.ThreeIconSet:
|
---|
463 | case eExcelConditionalFormattingRuleType.FourIconSet:
|
---|
464 | case eExcelConditionalFormattingRuleType.FiveIconSet:
|
---|
465 | return ExcelConditionalFormattingConstants.RuleType.IconSet;
|
---|
466 |
|
---|
467 | case eExcelConditionalFormattingRuleType.ThreeColorScale:
|
---|
468 | case eExcelConditionalFormattingRuleType.TwoColorScale:
|
---|
469 | return ExcelConditionalFormattingConstants.RuleType.ColorScale;
|
---|
470 |
|
---|
471 | case eExcelConditionalFormattingRuleType.BeginsWith:
|
---|
472 | return ExcelConditionalFormattingConstants.RuleType.BeginsWith;
|
---|
473 |
|
---|
474 | case eExcelConditionalFormattingRuleType.ContainsBlanks:
|
---|
475 | return ExcelConditionalFormattingConstants.RuleType.ContainsBlanks;
|
---|
476 |
|
---|
477 | case eExcelConditionalFormattingRuleType.ContainsErrors:
|
---|
478 | return ExcelConditionalFormattingConstants.RuleType.ContainsErrors;
|
---|
479 |
|
---|
480 | case eExcelConditionalFormattingRuleType.ContainsText:
|
---|
481 | return ExcelConditionalFormattingConstants.RuleType.ContainsText;
|
---|
482 |
|
---|
483 | case eExcelConditionalFormattingRuleType.DuplicateValues:
|
---|
484 | return ExcelConditionalFormattingConstants.RuleType.DuplicateValues;
|
---|
485 |
|
---|
486 | case eExcelConditionalFormattingRuleType.EndsWith:
|
---|
487 | return ExcelConditionalFormattingConstants.RuleType.EndsWith;
|
---|
488 |
|
---|
489 | case eExcelConditionalFormattingRuleType.Expression:
|
---|
490 | return ExcelConditionalFormattingConstants.RuleType.Expression;
|
---|
491 |
|
---|
492 | case eExcelConditionalFormattingRuleType.NotContainsBlanks:
|
---|
493 | return ExcelConditionalFormattingConstants.RuleType.NotContainsBlanks;
|
---|
494 |
|
---|
495 | case eExcelConditionalFormattingRuleType.NotContainsErrors:
|
---|
496 | return ExcelConditionalFormattingConstants.RuleType.NotContainsErrors;
|
---|
497 |
|
---|
498 | case eExcelConditionalFormattingRuleType.NotContainsText:
|
---|
499 | return ExcelConditionalFormattingConstants.RuleType.NotContainsText;
|
---|
500 |
|
---|
501 | case eExcelConditionalFormattingRuleType.UniqueValues:
|
---|
502 | return ExcelConditionalFormattingConstants.RuleType.UniqueValues;
|
---|
503 |
|
---|
504 | case eExcelConditionalFormattingRuleType.DataBar:
|
---|
505 | return ExcelConditionalFormattingConstants.RuleType.DataBar;
|
---|
506 | }
|
---|
507 |
|
---|
508 | throw new Exception(
|
---|
509 | ExcelConditionalFormattingConstants.Errors.MissingRuleType);
|
---|
510 | }
|
---|
511 |
|
---|
512 | /// <summary>
|
---|
513 | /// Return cfvo §18.3.1.11 parent according to the rule type
|
---|
514 | /// </summary>
|
---|
515 | /// <param name="type"></param>
|
---|
516 | /// <returns></returns>
|
---|
517 | public static string GetCfvoParentPathByType(
|
---|
518 | eExcelConditionalFormattingRuleType type)
|
---|
519 | {
|
---|
520 | switch (type)
|
---|
521 | {
|
---|
522 | case eExcelConditionalFormattingRuleType.TwoColorScale:
|
---|
523 | case eExcelConditionalFormattingRuleType.ThreeColorScale:
|
---|
524 | return ExcelConditionalFormattingConstants.Paths.ColorScale;
|
---|
525 |
|
---|
526 | case eExcelConditionalFormattingRuleType.ThreeIconSet:
|
---|
527 | case eExcelConditionalFormattingRuleType.FourIconSet:
|
---|
528 | case eExcelConditionalFormattingRuleType.FiveIconSet:
|
---|
529 | return ExcelConditionalFormattingConstants.RuleType.IconSet;
|
---|
530 |
|
---|
531 | case eExcelConditionalFormattingRuleType.DataBar:
|
---|
532 | return ExcelConditionalFormattingConstants.RuleType.DataBar;
|
---|
533 | }
|
---|
534 |
|
---|
535 | throw new Exception(
|
---|
536 | ExcelConditionalFormattingConstants.Errors.MissingRuleType);
|
---|
537 | }
|
---|
538 | }
|
---|
539 | } |
---|