[9580] | 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 | /// The conditional formatting constants
|
---|
| 41 | /// </summary>
|
---|
| 42 | internal static class ExcelConditionalFormattingConstants
|
---|
| 43 | {
|
---|
| 44 | #region Errors
|
---|
| 45 | internal class Errors
|
---|
| 46 | {
|
---|
| 47 | internal const string CommaSeparatedAddresses = @"Multiple addresses may not be commaseparated, use space instead";
|
---|
| 48 | internal const string InvalidCfruleObject = @"The supplied item must inherit OfficeOpenXml.ConditionalFormatting.ExcelConditionalFormattingRule";
|
---|
| 49 | internal const string InvalidConditionalFormattingObject = @"The supplied item must inherit OfficeOpenXml.ConditionalFormatting.ExcelConditionalFormatting";
|
---|
| 50 | internal const string InvalidPriority = @"Invalid priority number. Must be bigger than zero";
|
---|
| 51 | internal const string InvalidRemoveRuleOperation = @"Invalid remove rule operation";
|
---|
| 52 | internal const string MissingCfvoNode = @"Missing 'cfvo' node in Conditional Formatting";
|
---|
| 53 | internal const string MissingCfvoParentNode = @"Missing 'cfvo' parent node in Conditional Formatting";
|
---|
| 54 | internal const string MissingConditionalFormattingNode = @"Missing 'conditionalFormatting' node in Conditional Formatting";
|
---|
| 55 | internal const string MissingItemRuleList = @"Missing item with address '{0}' in Conditional Formatting Rule List";
|
---|
| 56 | internal const string MissingPriorityAttribute = @"Missing 'priority' attribute in Conditional Formatting Rule";
|
---|
| 57 | internal const string MissingRuleType = @"Missing eExcelConditionalFormattingRuleType Type in Conditional Formatting";
|
---|
| 58 | internal const string MissingSqrefAttribute = @"Missing 'sqref' attribute in Conditional Formatting";
|
---|
| 59 | internal const string MissingTypeAttribute = @"Missing 'type' attribute in Conditional Formatting Rule";
|
---|
| 60 | internal const string MissingWorksheetNode = @"Missing 'worksheet' node";
|
---|
| 61 | internal const string NonSupportedRuleType = @"Non supported conditionalFormattingType: {0}";
|
---|
| 62 | internal const string UnexistentCfvoTypeAttribute = @"Unexistent eExcelConditionalFormattingValueObjectType attribute in Conditional Formatting";
|
---|
| 63 | internal const string UnexistentOperatorTypeAttribute = @"Unexistent eExcelConditionalFormattingOperatorType attribute in Conditional Formatting";
|
---|
| 64 | internal const string UnexistentTimePeriodTypeAttribute = @"Unexistent eExcelConditionalFormattingTimePeriodType attribute in Conditional Formatting";
|
---|
| 65 | internal const string UnexpectedRuleTypeAttribute = @"Unexpected eExcelConditionalFormattingRuleType attribute in Conditional Formatting Rule";
|
---|
| 66 | internal const string UnexpectedRuleTypeName = @"Unexpected eExcelConditionalFormattingRuleType TypeName in Conditional Formatting Rule";
|
---|
| 67 | internal const string WrongNumberCfvoColorNodes = @"Wrong number of 'cfvo'/'color' nodes in Conditional Formatting Rule";
|
---|
| 68 | }
|
---|
| 69 | #endregion Errors
|
---|
| 70 |
|
---|
| 71 | #region Nodes
|
---|
| 72 | internal class Nodes
|
---|
| 73 | {
|
---|
| 74 | internal const string Worksheet = "worksheet";
|
---|
| 75 | internal const string ConditionalFormatting = "conditionalFormatting";
|
---|
| 76 | internal const string CfRule = "cfRule";
|
---|
| 77 | internal const string ColorScale = "colorScale";
|
---|
| 78 | internal const string Cfvo = "cfvo";
|
---|
| 79 | internal const string Color = "color";
|
---|
| 80 | internal const string DataBar = "dataBar";
|
---|
| 81 | internal const string IconSet = "iconSet";
|
---|
| 82 | internal const string Formula = "formula";
|
---|
| 83 | }
|
---|
| 84 | #endregion Nodes
|
---|
| 85 |
|
---|
| 86 | #region Attributes
|
---|
| 87 | internal class Attributes
|
---|
| 88 | {
|
---|
| 89 | internal const string AboveAverage = "aboveAverage";
|
---|
| 90 | internal const string Bottom = "bottom";
|
---|
| 91 | internal const string DxfId = "dxfId";
|
---|
| 92 | internal const string EqualAverage = "equalAverage";
|
---|
| 93 | internal const string IconSet = "iconSet";
|
---|
| 94 | internal const string Operator = "operator";
|
---|
| 95 | internal const string Percent = "percent";
|
---|
| 96 | internal const string Priority = "priority";
|
---|
| 97 | internal const string Rank = "rank";
|
---|
| 98 | internal const string Reverse = "reverse";
|
---|
| 99 | internal const string Rgb = "rgb";
|
---|
| 100 | internal const string ShowValue = "showValue";
|
---|
| 101 | internal const string Sqref = "sqref";
|
---|
| 102 | internal const string StdDev = "stdDev";
|
---|
| 103 | internal const string StopIfTrue = "stopIfTrue";
|
---|
| 104 | internal const string Text = "text";
|
---|
| 105 | internal const string Theme = "theme";
|
---|
| 106 | internal const string TimePeriod = "timePeriod";
|
---|
| 107 | internal const string Tint = "tint";
|
---|
| 108 | internal const string Type = "type";
|
---|
| 109 | internal const string Val = "val";
|
---|
| 110 | }
|
---|
| 111 | #endregion Attributes
|
---|
| 112 |
|
---|
| 113 | #region XML Paths
|
---|
| 114 | internal class Paths
|
---|
| 115 | {
|
---|
| 116 | // Main node and attributes
|
---|
| 117 | internal const string Worksheet = "d:" + Nodes.Worksheet;
|
---|
| 118 |
|
---|
| 119 | // <conditionalFormatting> §18.3.1.18 node
|
---|
| 120 | // can appear more than once in a worksheet
|
---|
| 121 | internal const string ConditionalFormatting = "d:" + Nodes.ConditionalFormatting;
|
---|
| 122 |
|
---|
| 123 | // <cfRule> §18.3.1.10 node
|
---|
| 124 | // can appear more than once in a <conditionalFormatting>
|
---|
| 125 | internal const string CfRule = "d:" + Nodes.CfRule;
|
---|
| 126 |
|
---|
| 127 | // <colorScale> §18.3.1.16 node
|
---|
| 128 | internal const string ColorScale = "d:" + Nodes.ColorScale;
|
---|
| 129 |
|
---|
| 130 | // <cfvo> §18.3.1.11 node
|
---|
| 131 | internal const string Cfvo = "d:" + Nodes.Cfvo;
|
---|
| 132 |
|
---|
| 133 | // <color> §18.3.1.15 node
|
---|
| 134 | internal const string Color = "d:" + Nodes.Color;
|
---|
| 135 |
|
---|
| 136 | // <dataBar> §18.3.1.28 node
|
---|
| 137 | internal const string DataBar = "d:" + Nodes.DataBar;
|
---|
| 138 |
|
---|
| 139 | // <iconSet> §18.3.1.49 node
|
---|
| 140 | internal const string IconSet = "d:" + Nodes.IconSet;
|
---|
| 141 |
|
---|
| 142 | // <formula> §18.3.1.43 node
|
---|
| 143 | internal const string Formula = "d:" + Nodes.Formula;
|
---|
| 144 |
|
---|
| 145 | // Attributes (for all the nodes)
|
---|
| 146 | internal const string AboveAverageAttribute = "@" + Attributes.AboveAverage;
|
---|
| 147 | internal const string BottomAttribute = "@" + Attributes.Bottom;
|
---|
| 148 | internal const string DxfIdAttribute = "@" + Attributes.DxfId;
|
---|
| 149 | internal const string EqualAverageAttribute = "@" + Attributes.EqualAverage;
|
---|
| 150 | internal const string IconSetAttribute = "@" + Attributes.IconSet;
|
---|
| 151 | internal const string OperatorAttribute = "@" + Attributes.Operator;
|
---|
| 152 | internal const string PercentAttribute = "@" + Attributes.Percent;
|
---|
| 153 | internal const string PriorityAttribute = "@" + Attributes.Priority;
|
---|
| 154 | internal const string RankAttribute = "@" + Attributes.Rank;
|
---|
| 155 | internal const string ReverseAttribute = "@" + Attributes.Reverse;
|
---|
| 156 | internal const string RgbAttribute = "@" + Attributes.Rgb;
|
---|
| 157 | internal const string ShowValueAttribute = "@" + Attributes.ShowValue;
|
---|
| 158 | internal const string SqrefAttribute = "@" + Attributes.Sqref;
|
---|
| 159 | internal const string StdDevAttribute = "@" + Attributes.StdDev;
|
---|
| 160 | internal const string StopIfTrueAttribute = "@" + Attributes.StopIfTrue;
|
---|
| 161 | internal const string TextAttribute = "@" + Attributes.Text;
|
---|
| 162 | internal const string ThemeAttribute = "@" + Attributes.Theme;
|
---|
| 163 | internal const string TimePeriodAttribute = "@" + Attributes.TimePeriod;
|
---|
| 164 | internal const string TintAttribute = "@" + Attributes.Tint;
|
---|
| 165 | internal const string TypeAttribute = "@" + Attributes.Type;
|
---|
| 166 | internal const string ValAttribute = "@" + Attributes.Val;
|
---|
| 167 | }
|
---|
| 168 | #endregion XML Paths
|
---|
| 169 |
|
---|
| 170 | #region Rule Type ST_CfType §18.18.12 (with small EPPlus changes)
|
---|
| 171 | internal class RuleType
|
---|
| 172 | {
|
---|
| 173 | internal const string AboveAverage = "aboveAverage";
|
---|
| 174 | internal const string BeginsWith = "beginsWith";
|
---|
| 175 | internal const string CellIs = "cellIs";
|
---|
| 176 | internal const string ColorScale = "colorScale";
|
---|
| 177 | internal const string ContainsBlanks = "containsBlanks";
|
---|
| 178 | internal const string ContainsErrors = "containsErrors";
|
---|
| 179 | internal const string ContainsText = "containsText";
|
---|
| 180 | internal const string DataBar = "dataBar";
|
---|
| 181 | internal const string DuplicateValues = "duplicateValues";
|
---|
| 182 | internal const string EndsWith = "endsWith";
|
---|
| 183 | internal const string Expression = "expression";
|
---|
| 184 | internal const string IconSet = "iconSet";
|
---|
| 185 | internal const string NotContainsBlanks = "notContainsBlanks";
|
---|
| 186 | internal const string NotContainsErrors = "notContainsErrors";
|
---|
| 187 | internal const string NotContainsText = "notContainsText";
|
---|
| 188 | internal const string TimePeriod = "timePeriod";
|
---|
| 189 | internal const string Top10 = "top10";
|
---|
| 190 | internal const string UniqueValues = "uniqueValues";
|
---|
| 191 |
|
---|
| 192 | // EPPlus Extended Types
|
---|
| 193 | internal const string AboveOrEqualAverage = "aboveOrEqualAverage";
|
---|
| 194 | internal const string AboveStdDev = "aboveStdDev";
|
---|
| 195 | internal const string BelowAverage = "belowAverage";
|
---|
| 196 | internal const string BelowOrEqualAverage = "belowOrEqualAverage";
|
---|
| 197 | internal const string BelowStdDev = "belowStdDev";
|
---|
| 198 | internal const string Between = "between";
|
---|
| 199 | internal const string Bottom = "bottom";
|
---|
| 200 | internal const string BottomPercent = "bottomPercent";
|
---|
| 201 | internal const string Equal = "equal";
|
---|
| 202 | internal const string GreaterThan = "greaterThan";
|
---|
| 203 | internal const string GreaterThanOrEqual = "greaterThanOrEqual";
|
---|
| 204 | internal const string IconSet3 = "iconSet3";
|
---|
| 205 | internal const string IconSet4 = "iconSet4";
|
---|
| 206 | internal const string IconSet5 = "iconSet5";
|
---|
| 207 | internal const string Last7Days = "last7Days";
|
---|
| 208 | internal const string LastMonth = "lastMonth";
|
---|
| 209 | internal const string LastWeek = "lastWeek";
|
---|
| 210 | internal const string LessThan = "lessThan";
|
---|
| 211 | internal const string LessThanOrEqual = "lessThanOrEqual";
|
---|
| 212 | internal const string NextMonth = "nextMonth";
|
---|
| 213 | internal const string NextWeek = "nextWeek";
|
---|
| 214 | internal const string NotBetween = "notBetween";
|
---|
| 215 | internal const string NotEqual = "notEqual";
|
---|
| 216 | internal const string ThisMonth = "thisMonth";
|
---|
| 217 | internal const string ThisWeek = "thisWeek";
|
---|
| 218 | internal const string ThreeColorScale = "threeColorScale";
|
---|
| 219 | internal const string Today = "today";
|
---|
| 220 | internal const string Tomorrow = "tomorrow";
|
---|
| 221 | internal const string Top = "top";
|
---|
| 222 | internal const string TopPercent = "topPercent";
|
---|
| 223 | internal const string TwoColorScale = "twoColorScale";
|
---|
| 224 | internal const string Yesterday = "yesterday";
|
---|
| 225 | }
|
---|
| 226 | #endregion Rule Type ST_CfType §18.18.12 (with small EPPlus changes)
|
---|
| 227 |
|
---|
| 228 | #region CFVO Type ST_CfvoType §18.18.13
|
---|
| 229 | internal class CfvoType
|
---|
| 230 | {
|
---|
| 231 | internal const string Min = "min";
|
---|
| 232 | internal const string Max = "max";
|
---|
| 233 | internal const string Num = "num";
|
---|
| 234 | internal const string Formula = "formula";
|
---|
| 235 | internal const string Percent = "percent";
|
---|
| 236 | internal const string Percentile = "percentile";
|
---|
| 237 | }
|
---|
| 238 | #endregion CFVO Type ST_CfvoType §18.18.13
|
---|
| 239 |
|
---|
| 240 | #region Operator Type ST_ConditionalFormattingOperator §18.18.15
|
---|
| 241 | internal class Operators
|
---|
| 242 | {
|
---|
| 243 | internal const string BeginsWith = "beginsWith";
|
---|
| 244 | internal const string Between = "between";
|
---|
| 245 | internal const string ContainsText = "containsText";
|
---|
| 246 | internal const string EndsWith = "endsWith";
|
---|
| 247 | internal const string Equal = "equal";
|
---|
| 248 | internal const string GreaterThan = "greaterThan";
|
---|
| 249 | internal const string GreaterThanOrEqual = "greaterThanOrEqual";
|
---|
| 250 | internal const string LessThan = "lessThan";
|
---|
| 251 | internal const string LessThanOrEqual = "lessThanOrEqual";
|
---|
| 252 | internal const string NotBetween = "notBetween";
|
---|
| 253 | internal const string NotContains = "notContains";
|
---|
| 254 | internal const string NotEqual = "notEqual";
|
---|
| 255 | }
|
---|
| 256 | #endregion Operator Type ST_ConditionalFormattingOperator §18.18.15
|
---|
| 257 |
|
---|
| 258 | #region Time Period Type ST_TimePeriod §18.18.82
|
---|
| 259 | internal class TimePeriods
|
---|
| 260 | {
|
---|
| 261 | internal const string Last7Days = "last7Days";
|
---|
| 262 | internal const string LastMonth = "lastMonth";
|
---|
| 263 | internal const string LastWeek = "lastWeek";
|
---|
| 264 | internal const string NextMonth = "nextMonth";
|
---|
| 265 | internal const string NextWeek = "nextWeek";
|
---|
| 266 | internal const string ThisMonth = "thisMonth";
|
---|
| 267 | internal const string ThisWeek = "thisWeek";
|
---|
| 268 | internal const string Today = "today";
|
---|
| 269 | internal const string Tomorrow = "tomorrow";
|
---|
| 270 | internal const string Yesterday = "yesterday";
|
---|
| 271 | }
|
---|
| 272 | #endregion Time Period Type ST_TimePeriod §18.18.82
|
---|
| 273 |
|
---|
| 274 | #region Colors
|
---|
| 275 | internal class Colors
|
---|
| 276 | {
|
---|
| 277 | internal const string CfvoLowValue = @"#FFF8696B";
|
---|
| 278 | internal const string CfvoMiddleValue = @"#FFFFEB84";
|
---|
| 279 | internal const string CfvoHighValue = @"#FF63BE7B";
|
---|
| 280 | }
|
---|
| 281 | #endregion Colors
|
---|
| 282 | }
|
---|
| 283 | } |
---|