/// <summary>
/// Build the ConditionalFormatting used for the ActivityClass column
/// </summary>
/// <returns></returns>
public static CondFormat BuildActivityClassCondFormat()
{
CondFormatRule rule;
Color color;
string name;
CondFormat cf = CondFormat.BuildDefaultConditionalFormatting();
//cf.ShowInHeaders = false;
cf.ColumnType = MetaColumnType.String;
cf.Name = "Activity Class Conditional Formatting";
for (int ri = 0; ri < cf.Rules.Count; ri++) // change to equality on integer class values
{
CondFormatRule r = cf.Rules[ri];
if (r.OpCode != CondFormatOpCode.Null)
{
r.OpCode = CondFormatOpCode.Eq;
r.Op = CondFormatRule.ConvertOpCodeToName(r.OpCode);
r.Value = r.Name;
}
}
cf.Rules.InitializeInternalMatchValues(cf.ColumnType);
return(cf);
}
public static CondFormat Deserialize(
XmlTextReader tr)
{
string txt;
CondFormat cf = new CondFormat();
txt = tr.GetAttribute("ColumnType");
if (txt != null)
{
EnumUtil.TryParse(txt, out cf.ColumnType);
}
XmlUtil.GetStringAttribute(tr, "Name", ref cf.Name);
XmlUtil.GetBoolAttribute(tr, "Option1", ref cf.Option1);
XmlUtil.GetBoolAttribute(tr, "Option2", ref cf.Option2);
//XmlUtil.GetBoolAttribute(tr, "ShowInHeaders", ref cf.ShowInHeaders);
tr.Read(); // get CondFormatRules element
tr.MoveToContent();
if (!Lex.Eq(tr.Name, "CondFormatRules"))
{
throw new Exception("CondFormat.Deserialize - \"CondFormat\" end element not found");
}
if (!tr.IsEmptyElement)
{
cf.Rules = CondFormatRules.Deserialize(tr);
cf.Rules.InitializeInternalMatchValues(cf.ColumnType);
}
else
{
cf.Rules = new CondFormatRules(); // no rules
}
tr.Read(); // get CondFormat end element
tr.MoveToContent();
if (!Lex.Eq(tr.Name, "CondFormat") || tr.NodeType != XmlNodeType.EndElement)
{
throw new Exception("CondFormat.Deserialize - Expected CondFormat end element");
}
if (cf.ColumnType == MetaColumnType.Date && cf.Rules != null)
{ // store normalized dates
foreach (CondFormatRule rule in cf.Rules)
{
if (!String.IsNullOrEmpty(rule.Value))
{
rule.ValueNormalized = DateTimeMx.Normalize(rule.Value);
}
if (!String.IsNullOrEmpty(rule.Value2))
{
rule.Value2Normalized = DateTimeMx.Normalize(rule.Value2);
}
}
}
return(cf);
}
/// <summary>
/// Convert dates to doubles to allow for databar and color scale Cond formats
/// </summary>
/// <param name="condFormat"></param>
/// <param name="stringValue"></param>
/// <param name="v1Normal"></param>
/// <param name="v2Normal"></param>
/// <param name="v"></param>
/// <param name="v1"></param>
/// <param name="v2"></param>
static void ConvertDateValuesToDoubles(
CondFormat condFormat,
string stringValue,
string v1Normal,
string v2Normal,
out double v,
out double v1,
out double v2)
{
DateTime date, date1, date2; // convert to delta day values relative to date1
if (condFormat.ColumnType == MetaColumnType.Date) // !String.IsNullOrEmpty(r1.ValueNormalized))
{ // build between date values
date1 = DateTimeMx.NormalizedToDateTime(v1Normal);
date2 = DateTimeMx.NormalizedToDateTime(v2Normal);
date = DateTimeMx.NormalizedToDateTime(stringValue);
v = date.Subtract(date1).TotalDays;
v1 = 0;
v2 = date2.Subtract(date1).TotalDays;
}
else
{
v = v1 = v2 = 0; // treat ordinal continuous coloring as numeric conditional formatting
}
return;
}
/// <summary>
/// Clone cf
/// </summary>
/// <returns></returns>
public CondFormat Clone()
{
string serializedForm = this.Serialize();
CondFormat clone = CondFormat.Deserialize(serializedForm);
return(clone);
}
/// <summary>
/// Match a general object value to conditional formatting & return the first matching item
/// </summary>
/// <param name="rules"></param>
/// <param name="o"></param>
/// <returns></returns>
public static CondFormatRule Match(
CondFormat condFormat,
object o)
{
if (o == null)
{
return(MatchNull(condFormat));
}
else if (o is double)
{
return(Match(condFormat, (double)o));
}
else if (o is int)
{
return(Match(condFormat, (int)o));
}
else if (o is string)
{
return(Match(condFormat, (string)o));
}
else if (o is DateTime)
{
return(Match(condFormat, (DateTime)o));
}
else if (o is CompoundId)
{
return(Match(condFormat, ((CompoundId)o).Value));
}
else if (o is MoleculeMx)
{
return(Match(condFormat, (MoleculeMx)o));
}
else if (o is QualifiedNumber)
{
return(Match(condFormat, (QualifiedNumber)o));
}
else if (o is NumberMx)
{
return(Match(condFormat, (o as NumberMx).Value));
}
else if (o is StringMx)
{
return(Match(condFormat, (o as StringMx).Value));
}
else if (o is DateTimeMx)
{
return(Match(condFormat, (o as DateTimeMx).Value));
}
else
{
return(null);
}
}
/// <summary>
/// Add or update a predefined cf
/// </summary>
/// <param name="cfName"></param>
/// <param name="cf"></param>
public static void UpdatePredefined(
string cfName,
CondFormat cf)
{
cfName = cfName.ToUpper();
CondFormat cf2 = GetPredefined(cfName); // be sure dict has been build
string content = cf.Serialize();
PredefinedDict[cfName] = content;
return;
}
/// <summary>
/// Lookup predefined conditional formatting by name
/// </summary>
/// <param name="cfName"></param>
/// <returns></returns>
public static CondFormat GetPredefined(string cfName)
{
CondFormat cf = null;
cfName = cfName.ToUpper();
if (Lex.Eq(cfName, "Activity Class Conditional Formatting") || Lex.Eq(cfName, "ActivityClassCondFormat")) // special internal format
{
cf = UnpivotedAssayResult.BuildActivityClassCondFormat();
return(cf);
}
else if (Lex.Eq(cfName, "Activity Bin Conditional Formatting") || Lex.Eq(cfName, "ActivityBinCondFormat")) // special internal format
{
cf = UnpivotedAssayResult.BuildActivityBinCondFormat();
return(cf);
}
else if (Lex.StartsWith(cfName, "CONDFORMAT_"))
{
if (PredefinedDict == null) // need to build?
{
if (InterfaceRefs.IUserObjectTree == null)
{
return(null);
}
List <UserObject> uoList = InterfaceRefs.IUserObjectTree.GetUserObjectsByType(UserObjectType.CondFormat);
if (uoList == null)
{
return(null);
}
PredefinedDict = new Dictionary <string, string>();
foreach (UserObject uo0 in uoList)
{
PredefinedDict[uo0.InternalName.ToUpper()] = uo0.Content;
}
}
if (!PredefinedDict.ContainsKey(cfName))
{
return(null);
}
cf = CondFormat.Deserialize(PredefinedDict[cfName]);
return(cf);
}
else
{
return(null);
}
}
/// <summary>
/// Reset formatting to default values
/// </summary>
public void ResetFormatting()
{
QueryColumn qc0 = new QueryColumn();
CondFormat = qc0.CondFormat;
Label = qc0.Label;
DisplayFormat = qc0.DisplayFormat;
DisplayWidth = qc0.DisplayWidth;
Decimals = qc0.Decimals;
HorizontalAlignment = qc0.HorizontalAlignment;
VerticalAlignment = qc0.VerticalAlignment;
return;
}
/// <summary>
/// Match a DateTime value
/// </summary>
/// <param name="rules"></param>
/// <param name="dtValue"></param>
/// <returns></returns>
public static CondFormatRule Match(
CondFormat condFormat,
DateTime dtValue)
{
CondFormatRules rules = condFormat.Rules;
if (dtValue == DateTime.MinValue)
{
return(MatchNull(condFormat));
}
string dateString = DateTimeMx.Normalize(dtValue);
CondFormatRule matchingRule = Match(condFormat, dateString);
return(matchingRule);
}
/// <summary>
/// Match a null value
/// </summary>
/// <param name="rules"></param>
/// <returns></returns>
public static CondFormatRule MatchNull(
CondFormat condFormat)
{
CondFormatRules rules = condFormat.Rules;
for (int ri = 0; ri < rules.Count; ri++)
{
CondFormatRule rule = rules[ri];
if (rule.OpCode == CondFormatOpCode.Null)
{
return(rule);
}
}
return(null);
}
/// <summary>
/// Setup conditional formatting in basic Critical Success Factor format
/// </summary>
/// <param name="cf"></param>
public static CondFormat BuildDefaultConditionalFormatting()
{
CondFormatRule r;
CondFormat cf = new CondFormat();
cf.Rules.ColoringStyle = CondFormatStyle.ColorSet;
if (Bitmaps.ColorSetImageColors.ContainsKey(DefaultColorSet))
{
Color[] colors = Bitmaps.ColorSetImageColors[DefaultColorSet];
if (colors.Length >= 4)
{
CsfGreen = colors[0];
CsfYellow = colors[1];
CsfRed = colors[2];
CsfUndefined = colors[3];
}
}
r = new CondFormatRule();
r.Name = "Pass";
r.Op = "<=";
r.OpCode = CondFormatOpCode.Le;
r.BackColor1 = CsfGreen;
cf.Rules.Add(r);
r = new CondFormatRule();
r.Name = "BorderLine";
r.OpCode = CondFormatOpCode.Le;
r.Op = "<=";
r.BackColor1 = CsfYellow;
cf.Rules.Add(r);
r = new CondFormatRule();
r.Name = "Fail";
r.OpCode = CondFormatOpCode.NotNull;
r.Op = "Any other value";
r.BackColor1 = CsfRed;
cf.Rules.Add(r);
return(cf);
}
/// <summary>
/// Match QualifiedNumber value
/// </summary>
/// <param name="rules"></param>
/// <param name="value"></param>
/// <returns></returns>
public static CondFormatRule Match(
CondFormat condFormat,
QualifiedNumber value)
{
if (value.IsNull)
{
return(Match(condFormat, NullValue.NullNumber));
}
else
{
return(Match(condFormat, value.NumberValue));
}
// else if (MetaColumn.IsNumericMetaColumnType(condFormat.ColumnType))
// {
// return Match(condFormat, value.NumberValue);
// }
// else return Match(condFormat, value.TextValue);
}
/// <summary>
/// Match a CompoundId value (not currently used)
/// </summary>
/// <param name="rules"></param>
/// <param name="dtValue"></param>
/// <returns></returns>
public static CondFormatRule Match(
CondFormat condFormat,
CompoundId cid)
{
CondFormatRules rules = condFormat.Rules;
CondFormatRule rule = null;
CidList cidList;
int ri, objId;
for (ri = 0; ri < rules.Count; ri++)
{
rule = rules[ri];
if (rule.ValueDict == null) // read in cid list if not done yet
{
rule.ValueDict = new Dictionary <string, object>(); // set empty dict so if fails we won't try again
if (!int.TryParse(rule.Value2, out objId))
{
continue;
}
if (CidList.ICidListDao == null)
{
continue;
}
cidList = CidList.ICidListDao.VirtualRead(objId, null);
rule.ValueDict["CidList"] = cidList;
}
cidList = rule.ValueDict["CidList"] as CidList;
if (cidList.Contains(cid.Value))
{
break;
}
}
if (ri < rules.Count)
{
return(rule);
}
else
{
return(null);
}
}
/// <summary>
/// Match a structure to conditional formatting & return the first matching item
/// </summary>
/// <param name="rules"></param>
/// <param name="structure"></param>
/// <returns></returns>
public static CondFormatRule Match(
CondFormat condFormat,
MoleculeMx structure)
{
CondFormatRules rules = condFormat.Rules;
CondFormatRule rule = null;
int ri;
if (structure == null || structure.PrimaryFormat == MoleculeFormat.Unknown)
{
return(MatchNull(condFormat));
}
StructureMatcher matcher = new StructureMatcher();
for (ri = 0; ri < rules.Count; ri++) // match rules one at a time until get a hit
{
rule = rules[ri];
if (String.IsNullOrEmpty(rule.Value))
{
continue;
}
MoleculeMx query = new MoleculeMx(rule.Value);
matcher.SetSSSQueryMolecule(query);
bool matches = matcher.IsSSSMatch(structure);
//DebugLog.Message("Rule: " + ri + ", " + query.SmilesString + ", " + structure.SmilesString + ", " + matches);
if (matches)
{
break;
}
}
if (ri < rules.Count)
{
return(rule);
}
else
{
return(null);
}
}
/// <summary>
/// Deserialize old format conditional formatting.
/// </summary>
/// <param name="serializedForm"></param>
/// <returns></returns>
public static CondFormat DeserializeOld(
string serializedForm)
{
CondFormat cf = new CondFormat();
string[] sa = serializedForm.Split('\n');
string[] sa2 = sa[0].Split('\t');
// cf.IsCSF = Boolean.Parse(sa2[0]); // (obsolete)
if (sa2.Length >= 3)
{
MetaTable mt = MetaTableCollection.Get(sa2[1]);
if (mt == null)
{
return(null);
}
MetaColumn mc = mt.GetMetaColumnByName(sa2[2]);
if (mc != null)
{
cf.ColumnType = mc.DataType;
}
}
if (sa.Length > 1 && sa[1] != "")
{
cf.Rules.Add(CondFormatRules.DeserializeOld(sa[1]));
}
if (sa.Length > 2 && sa[2] != "")
{
cf.Rules.Add(CondFormatRules.DeserializeOld(sa[2]));
}
if (sa.Length > 3 && sa[3] != "")
{
cf.Rules.Add(CondFormatRules.DeserializeOld(sa[3]));
}
if (sa.Length > 4 && sa[4] != "")
{
cf.Rules.Add(CondFormatRules.DeserializeOld(sa[4]));
}
return(cf);
}
/// <summary>
/// Build the ConditionalFormatting used for the ActivityClass column
/// </summary>
/// <returns></returns>
public static CondFormat BuildActivityBinCondFormat()
{
CondFormatRule rule;
CondFormat cf = new CondFormat();
//cf.ShowInHeaders = false;
cf.ColumnType = MetaColumnType.Integer;
cf.Name = "Activity Bin Conditional Formatting";
CondFormatRules rules = cf.Rules = new CondFormatRules();
for (int bin = 1; bin <= 10; bin++) // create one rule for each value from 1 - 10
{
rule = new CondFormatRule();
rule.Name = GetBinLabel(bin);
rule.Op = "Equal to";
rule.OpCode = CondFormatOpCode.Eq;
rule.Value = bin.ToString();
rule.BackColor1 = CalculateBinColor(bin);
rules.Add(rule);
}
//rule = new CondFormatRule("Low", CondFormatOpCode.Lt, "1");
//rule.BackColor = CalculateBinColor(1);
//rules.Add(rule);
//rule = new CondFormatRule("High", CondFormatOpCode.Gt, "10");
//rule.BackColor = CalculateBinColor(10);
//rules.Add(rule);
rule = new CondFormatRule("Missing", CondFormatOpCode.Null, "");
rule.BackColor1 = Color.White;
rules.Add(rule);
cf.Rules.InitializeInternalMatchValues(cf.ColumnType);
return(cf);
}
/// <summary>
/// Deserialize conditional formatting.
/// </summary>
/// <param name="serializedForm"></param>
/// <returns></returns>
public static CondFormat Deserialize(
string serializedForm)
{
if (!Lex.Contains(serializedForm, "<Condformat "))
{
return(DeserializeOld(serializedForm));
}
XmlMemoryStreamTextReader mstr = new XmlMemoryStreamTextReader(serializedForm);
XmlTextReader tr = mstr.Reader;
tr.Read(); // get CondFormat element
tr.MoveToContent();
if (!Lex.Eq(tr.Name, "CondFormat"))
{
throw new Exception("CondFormat.Deserialize - \"CondFormat\" element not found");
}
CondFormat cf = Deserialize(mstr.Reader);
mstr.Close();
return(cf);
}
/// <summary>
/// Match a string value to conditional formatting & return the first matching item
/// Handles dates converted to normalized string values also (yyyymmdd)
/// </summary>
/// <param name="rules"></param>
/// <param name="value"></param>
/// <returns></returns>
public static CondFormatRule Match(
CondFormat condFormat,
string stringValue)
{
double v, v1, v2;
int ri;
object o;
CondFormatRules rules = condFormat.Rules;
CondFormatRule rule = null;
DateTime dtValue = DateTime.MinValue, dt2;
if (condFormat.ColumnType == MetaColumnType.CompoundId)
{
return(Match(condFormat, new CompoundId(stringValue)));
}
if (String.IsNullOrEmpty(stringValue))
{
return(MatchNull(condFormat));
}
int lowerLimitRule = -1;
int upperLimitRule = -1;
for (ri = 0; ri < rules.Count; ri++)
{
rule = rules[ri];
if (!String.IsNullOrEmpty(stringValue))
{ // check where we have a value
string value = rule.Value;
if (rule.ValueNormalized != null)
{
value = rule.ValueNormalized;
}
string value2 = rule.Value2;
if (rule.Value2Normalized != null)
{
value2 = rule.Value2Normalized;
}
if (rule.OpCode == CondFormatOpCode.Within) // check date range
{
dtValue = DateTimeMx.NormalizedToDateTime(stringValue); // convert to a DateTime
double withinValue = rule.ValueNumber;
if (Lex.Contains(value2, "Day"))
{
dt2 = dtValue.AddDays(withinValue);
}
else if (Lex.Contains(value2, "Week"))
{
dt2 = dtValue.AddDays(withinValue * 7);
}
else if (Lex.Contains(value2, "Month"))
{
dt2 = dtValue.AddMonths((int)withinValue); // note must be integer months
}
else if (Lex.Contains(value2, "Year"))
{
dt2 = dtValue.AddYears((int)withinValue); // note must be integer years
}
else
{
throw new Exception("Unexpected date unit: " + value2);
}
dt2 = dt2.AddDays(1); // add one day to dt2 since it's time is 12:00 AM and the Now date includes the hours passed so far today
if (DateTime.Compare(dt2, DateTime.Now) >= 0)
{
break; // if stored date/time + within value is >= current date/time then condition passes
}
else
{
continue;
}
}
if (rule.OpCode == CondFormatOpCode.Between && value2 != null && value != null)
{
if (Lex.Gt(value, value2))
{
string s1 = value; value = value2; value2 = s1;
}
if (Lex.Ge(stringValue, value) && Lex.Le(stringValue, value2))
{
break;
}
}
else if (rule.OpCode == CondFormatOpCode.NotBetween && value2 != null && value != null)
{
if (Lex.Gt(value, value2))
{
string s1 = value; value = value2; value2 = s1;
}
if (Lex.Lt(stringValue, value) || Lex.Gt(stringValue, value2))
{
break;
}
}
else if (rule.OpCode == CondFormatOpCode.Eq &&
Lex.Eq(stringValue, value))
{
break;
}
else if (rule.OpCode == CondFormatOpCode.NotEq &&
Lex.Ne(stringValue, value))
{
break;
}
else if (rule.OpCode == CondFormatOpCode.Gt &&
Lex.Gt(stringValue, value))
{
break;
}
else if (rule.OpCode == CondFormatOpCode.Lt &&
Lex.Lt(stringValue, value))
{
break;
}
else if (rule.OpCode == CondFormatOpCode.Ge &&
Lex.Ge(stringValue, value))
{
if (rules.ColoringStyle == CondFormatStyle.ColorScale || ri > rules.Count - 1)
{
break;
}
else
{
lowerLimitRule = ri;
}
}
else if (rule.OpCode == CondFormatOpCode.Le &&
Lex.Le(stringValue, value))
{
upperLimitRule = ri;
break;
}
else if (rule.OpCode == CondFormatOpCode.Substring &&
stringValue.ToLower().IndexOf(value.ToLower()) >= 0)
{
break;
}
else if (rule.OpCode == CondFormatOpCode.NotNull || rule.OpCode == CondFormatOpCode.Exists) // value is not null or exists
{
break;
}
else if (rule.OpCode == CondFormatOpCode.Unknown && // treat unknown as equal
Lex.Eq(stringValue, value))
{
break;
}
}
else if (rule.OpCode == CondFormatOpCode.Null || rule.OpCode == CondFormatOpCode.NotExists)
{
break; // null value & null conditional format operator
}
}
//DebugLog.Message(stringValue + " " + ri); // debug
if (ri >= rules.Count)
{
return(null); // return null if no rule matches
}
// Matches a rule associated with a particular color
if (rules.ColoringStyle == CondFormatStyle.ColorSet)
{
return(rule);
}
// Calculate a linear gradient between the two color limits
else if (rules.ColoringStyle == CondFormatStyle.ColorScale && rule.OpCode == CondFormatOpCode.Between)
{ // newer single color scale rule
ConvertDateValuesToDoubles(condFormat, stringValue, rule.ValueNormalized, rule.Value2Normalized, out v, out v1, out v2);
rule.ValueNumber = v1;
rule.Value2Number = v2;
rule = BuildColorRuleForColorScale(v, rule);
return(rule);
}
else if (rules.ColoringStyle == CondFormatStyle.ColorScale && lowerLimitRule >= 0 && upperLimitRule >= lowerLimitRule)
{ // older two-rule, double color scale rules
CondFormatRule r1 = rules[lowerLimitRule];
CondFormatRule r2 = rules[upperLimitRule];
ConvertDateValuesToDoubles(condFormat, stringValue, r1.ValueNormalized, r2.ValueNormalized, out v, out v1, out v2);
rule.ValueNumber = v1;
rule.Value2Number = v2;
rule = BuildColorRuleForGradientValue(v, v1, v2, r1.BackColor1, r2.BackColor1);
return(rule);
}
// Calculate a percentage for a data bar for a value between the two color limits
else if (rules.ColoringStyle == CondFormatStyle.DataBar)
{
ConvertDateValuesToDoubles(condFormat, stringValue, rule.ValueNormalized, rule.Value2Normalized, out v, out v1, out v2);
rule.ValueNumber = v1;
rule.Value2Number = v2;
rule = BuildPercentageRuleForDataBarValue(v, rule);
return(rule);
}
else if (rules.ColoringStyle == CondFormatStyle.IconSet)
{
rule = BuildImageRuleForIconSetValue(rule);
return(rule); // image name should be included in rule
}
else
{
return(rule); // shouldn't happen
}
}
/// <summary>
/// Deserialize CalcField from an XmlTextReader
/// </summary>
/// <param name="tr"></param>
/// <returns></returns>
public static CalcField Deserialize(
XmlTextReader tr)
{
string mtName, mcName, txt, errMsg = "";
MetaTable mt;
MetaColumn mc;
CalcField cf = new CalcField();
XmlUtil.GetStringAttribute(tr, "Name", ref cf.UserObject.Name);
txt = tr.GetAttribute("CalcType");
EnumUtil.TryParse(txt, out cf.CalcType);
if (cf.CalcType != CalcTypeEnum.Basic && cf.CalcType != CalcTypeEnum.Advanced)
{
cf.CalcType = CalcTypeEnum.Basic; // default to basic type
}
txt = tr.GetAttribute("SourceColumnType");
if (txt != null)
{
cf.SourceColumnType = MetaColumn.ParseMetaColumnTypeString(txt);
}
txt = tr.GetAttribute("PreclassificationlResultType");
if (txt != null)
{
cf.PreclassificationlResultType = MetaColumn.ParseMetaColumnTypeString(txt);
}
for (int ci = 1; ; ci++) // get the set of columns
{
mtName = tr.GetAttribute("Table" + ci);
if (String.IsNullOrEmpty(mtName))
{
//if (ci == 1) continue; // first col may be undefined
//else
if (ci > 1)
{
break; // if beyond the first then col then all done
}
}
if (ci > cf.CfCols.Count)
{
cf.CfCols.Add(new CalcFieldColumn());
}
CalcFieldColumn cfc = cf.CfCols[ci - 1];
mt = MetaTableCollection.Get(mtName);
if (mt != null)
{
mcName = tr.GetAttribute("Column" + ci);
if (mcName != null)
{
cfc.MetaColumn = mt.GetMetaColumnByName(mcName);
if (cfc.MetaColumn == null)
{
errMsg += "Unable to find column: " + mcName + " in data table " + mt.Label + "(" + mt.Name + ")\r\n";
}
}
}
else if (ci != 1)
{
errMsg += "Unable to find data table: " + mtName + "\r\n";
}
txt = tr.GetAttribute("Function" + ci);
if (txt != null)
{
cfc.Function = txt;
}
txt = tr.GetAttribute("Constant" + ci);
if (txt != null)
{
cfc.Constant = txt;
}
}
txt = tr.GetAttribute("Operation");
if (txt != null)
{
cf.Operation = txt;
}
XmlUtil.GetStringAttribute(tr, "AdvancedExpr", ref cf.AdvancedExpr);
XmlUtil.GetStringAttribute(tr, "OuterJoinRoot", ref cf.OuterJoinRoot);
XmlUtil.GetStringAttribute(tr, "ColumnLabel", ref cf.ColumnLabel);
XmlUtil.GetStringAttribute(tr, "Description", ref cf.Description);
XmlUtil.GetStringAttribute(tr, "Prompt", ref cf.Prompt);
if (!tr.IsEmptyElement)
{
tr.Read();
tr.MoveToContent();
if (Lex.Eq(tr.Name, "CondFormat")) // and cond format
{
if (!tr.IsEmptyElement)
{
cf.Classification = CondFormat.Deserialize(tr);
}
tr.Read(); // get next element
tr.MoveToContent();
}
if (!Lex.Eq(tr.Name, "CalcField") || tr.NodeType != XmlNodeType.EndElement)
{
throw new Exception("CalcField.Deserialize - Expected CalcField end element");
}
}
cf.SetDerivedValues();
return(cf);
}
/// <summary>
/// Match integer value
/// </summary>
/// <param name="rules"></param>
/// <param name="value"></param>
/// <returns></returns>
public static CondFormatRule Match(
CondFormat condFormat,
int value)
{
return(Match(condFormat, (double)value));
}
/// <summary>
/// Match a double value to conditional formatting & return the first matching item
/// </summary>
/// <param name="value"></param>
/// <returns></returns>
public static CondFormatRule Match(
CondFormat condFormat,
double value)
{
CondFormatRules rules = condFormat.Rules;
CondFormatRule rule = null;
int ri;
object o;
if (value == NullValue.NullNumber)
{
return(MatchNull(condFormat));
}
int lowerLimitRule = -1;
int upperLimitRule = -1;
for (ri = 0; ri < rules.Count; ri++)
{
rule = rules[ri];
if (rule.OpCode == CondFormatOpCode.Between)
{
if (rules.ColoringStyle == CondFormatStyle.ColorScale)
{
break; // only one between rule for newer color scale cond formatting (allow reverse ordering)
}
if (rules.ColoringStyle == CondFormatStyle.DataBar)
{
break; // only one between rule for data bars
}
if (rule.ValueNumber > rule.Value2Number) // order low to high
{
double d1 = rule.ValueNumber; rule.ValueNumber = rule.Value2Number; rule.Value2Number = d1;
}
if (value >= rule.ValueNumber && value <= rule.Value2Number)
{
break; // within range?
}
}
else if (rule.OpCode == CondFormatOpCode.NotBetween)
{
if (rule.ValueNumber > rule.Value2Number)
{
double d1 = rule.ValueNumber; rule.ValueNumber = rule.Value2Number; rule.Value2Number = d1;
}
if (value + rule.Epsilon < rule.ValueNumber || value - rule.Epsilon > rule.Value2Number)
{
break;
}
}
else if (rule.OpCode == CondFormatOpCode.Eq &&
Math.Abs(value - rule.ValueNumber) < rule.Epsilon)
{
break;
}
else if (rule.OpCode == CondFormatOpCode.NotEq &&
Math.Abs(value - rule.ValueNumber) >= rule.Epsilon)
{
break;
}
else if (rule.OpCode == CondFormatOpCode.Gt &&
value + rule.Epsilon > rule.ValueNumber)
{
break;
}
else if (rule.OpCode == CondFormatOpCode.Lt &&
value - rule.Epsilon < rule.ValueNumber)
{
break;
}
else if (rule.OpCode == CondFormatOpCode.Ge)
{
if (rules.ColoringStyle == CondFormatStyle.ColorScale) // older continuous coloring?
{
lowerLimitRule = ri;
}
else if (value + rule.Epsilon >= rule.ValueNumber)
{
break;
}
}
else if (rule.OpCode == CondFormatOpCode.Le)
{
if (rules.ColoringStyle == CondFormatStyle.ColorScale) // older continuous coloring?
{
upperLimitRule = ri;
break;
}
else if (value - rule.Epsilon <= rule.ValueNumber)
{
break;
}
}
else if (rule.OpCode == CondFormatOpCode.NotNull || rule.OpCode == CondFormatOpCode.Exists) // value is not null or exists
{
break;
}
}
if (ri >= rules.Count)
{
return(null); // return null if no rule matches
}
// Matches a rule associated with a particular color
if (rules.ColoringStyle == CondFormatStyle.ColorSet)
{
return(rule);
}
// Calculate a linear gradient between the two color limits
else if (rules.ColoringStyle == CondFormatStyle.ColorScale && rule.OpCode == CondFormatOpCode.Between)
{ // newer single color scale rule
rule = BuildColorRuleForColorScale(value, rule);
return(rule);
}
else if (rules.ColoringStyle == CondFormatStyle.ColorScale && lowerLimitRule >= 0 && upperLimitRule >= lowerLimitRule)
{ // older two-rule, double color scale rules
CondFormatRule r1 = rules[lowerLimitRule];
CondFormatRule r2 = rules[upperLimitRule];
rule = BuildColorRuleForGradientValue(value, r1.ValueNumber, r2.ValueNumber, r1.BackColor1, r2.BackColor1);
return(rule);
}
// Calculate a percentage for a data bar for a value between the two color limits
else if (rules.ColoringStyle == CondFormatStyle.DataBar)
{
rule = BuildPercentageRuleForDataBarValue(value, rule);
return(rule);
}
else if (rules.ColoringStyle == CondFormatStyle.IconSet)
{
rule = BuildImageRuleForIconSetValue(rule);
return(rule); // image name should be included in rule
}
else
{
return(rule); // shouldn't happen
}
}
