diff --git a/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj b/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj
index 5950972..ad72f79 100644
--- a/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj
+++ b/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj
@@ -193,6 +193,7 @@
+
diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs
index 1a3e21f..6046c91 100644
--- a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs
+++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs
@@ -1693,8 +1693,29 @@
SetStyle(source._style ?? source.Worksheet.Workbook.GetStyleById(source._styleCacheId));
var conditionalFormats = otherCell.Worksheet.ConditionalFormats.Where(c => c.Range.Contains(otherCell)).ToList();
- foreach(var cf in conditionalFormats)
- _worksheet.ConditionalFormats.Add(new XLConditionalFormat(cf as XLConditionalFormat) { Range = AsRange()});
+ foreach (var cf in conditionalFormats)
+ {
+ var c = new XLConditionalFormat(cf as XLConditionalFormat) { Range = AsRange() };
+ var oldValues = c.Values.Values.ToList();
+ c.Values.Clear();
+ foreach(var v in oldValues)
+ {
+ String f = v.Value;
+ if (v.IsFormula)
+ {
+ using (var asRange = AsRange())
+ {
+ var r1c1 = otherCell.GetFormulaR1C1(f);
+ f = GetFormulaA1(r1c1);
+ }
+ }
+
+
+ c.Values.Add(new XLFormula { _value = f});
+ }
+
+ _worksheet.ConditionalFormats.Add(c);
+ }
if (copyDataValidations)
{
@@ -1767,14 +1788,9 @@
if (!A1ColumnRegex.IsMatch(rangeAddress))
{
var matchRange = worksheetInAction.Workbook.Worksheet(sheetName).Range(rangeAddress);
- if (shiftedRange.RangeAddress.FirstAddress.RowNumber <=
- matchRange.RangeAddress.LastAddress.RowNumber
- &&
- shiftedRange.RangeAddress.FirstAddress.ColumnNumber <=
- matchRange.RangeAddress.FirstAddress.ColumnNumber
- &&
- shiftedRange.RangeAddress.LastAddress.ColumnNumber >=
- matchRange.RangeAddress.LastAddress.ColumnNumber)
+ if (shiftedRange.RangeAddress.FirstAddress.RowNumber <= matchRange.RangeAddress.LastAddress.RowNumber
+ && shiftedRange.RangeAddress.FirstAddress.ColumnNumber <= matchRange.RangeAddress.FirstAddress.ColumnNumber
+ && shiftedRange.RangeAddress.LastAddress.ColumnNumber >= matchRange.RangeAddress.LastAddress.ColumnNumber)
{
if (A1RowRegex.IsMatch(rangeAddress))
{
diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/IXLConditionalFormat.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/IXLConditionalFormat.cs
index a09e4d9..c211f7a 100644
--- a/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/IXLConditionalFormat.cs
+++ b/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/IXLConditionalFormat.cs
@@ -109,7 +109,7 @@
Boolean ShowBarOnly { get; }
IXLRange Range { get; set; }
- XLDictionary Values { get; }
+ XLDictionary Values { get; }
XLDictionary Colors { get; }
XLDictionary ContentTypes { get; }
XLDictionary IconSetOperators { get; }
diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/Save/XLCFCellIsConverter.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/Save/XLCFCellIsConverter.cs
index 0199071..96c9c75 100644
--- a/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/Save/XLCFCellIsConverter.cs
+++ b/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/Save/XLCFCellIsConverter.cs
@@ -10,7 +10,9 @@
{
public ConditionalFormattingRule Convert(IXLConditionalFormat cf, int priority, XLWorkbook.SaveContext context)
{
- String val = cf.Values[1];//.Replace("\"", "\"\"");
+ String val = GetQuoted(cf.Values[1].Value);
+
+
var conditionalFormattingRule = new ConditionalFormattingRule { FormatId = (UInt32)context.DifferentialFormats[cf.Style], Operator = cf.Operator.ToOpenXml(), Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority };
var formula = new Formula();
@@ -22,13 +24,22 @@
if(cf.Operator == XLCFOperator.Between || cf.Operator == XLCFOperator.NotBetween)
{
- var formula2 = new Formula { Text = cf.Values[2] };//.Replace("\"", "\"\"")};
+ var formula2 = new Formula { Text = GetQuoted(cf.Values[2].Value) };//.Replace("\"", "\"\"")};
conditionalFormattingRule.Append(formula2);
}
return conditionalFormattingRule;
}
+ private String GetQuoted(String value)
+ {
+ Double num;
+ if (!Double.TryParse(value, out num) && value[0] != '\"' && !value.EndsWith("\""))
+ return String.Format("\"{0}\"", value.Replace("\"", "\"\""));
+
+ return value;
+ }
+
}
}
diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/Save/XLCFColorScaleConverter.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/Save/XLCFColorScaleConverter.cs
index 183310e..5d970d5 100644
--- a/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/Save/XLCFColorScaleConverter.cs
+++ b/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/Save/XLCFColorScaleConverter.cs
@@ -16,7 +16,7 @@
var colorScale = new ColorScale();
for(Int32 i = 1; i <= cf.Values.Count; i++)
{
- var conditionalFormatValueObject = new ConditionalFormatValueObject { Type = cf.ContentTypes[i].ToOpenXml(), Val = cf.Values[i] };
+ var conditionalFormatValueObject = new ConditionalFormatValueObject { Type = cf.ContentTypes[i].ToOpenXml(), Val = cf.Values[i].Value };
colorScale.Append(conditionalFormatValueObject);
}
diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/Save/XLCFContainsConverter.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/Save/XLCFContainsConverter.cs
index 96b69d9..3abf4c3 100644
--- a/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/Save/XLCFContainsConverter.cs
+++ b/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/Save/XLCFContainsConverter.cs
@@ -10,7 +10,7 @@
{
public ConditionalFormattingRule Convert(IXLConditionalFormat cf, int priority, XLWorkbook.SaveContext context)
{
- String val = cf.Values[1];
+ String val = cf.Values[1].Value;
var conditionalFormattingRule = new ConditionalFormattingRule { FormatId = (UInt32)context.DifferentialFormats[cf.Style], Operator = cf.Operator.ToOpenXml(), Text = val, Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority };
var formula = new Formula { Text = "NOT(ISERROR(SEARCH(\"" + val + "\"," + cf.Range.RangeAddress.FirstAddress.ToStringRelative(false) + ")))" };
diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/Save/XLCFDataBarConverter.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/Save/XLCFDataBarConverter.cs
index 4bc9b79..afe0a10 100644
--- a/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/Save/XLCFDataBarConverter.cs
+++ b/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/Save/XLCFDataBarConverter.cs
@@ -14,8 +14,8 @@
var conditionalFormattingRule = new ConditionalFormattingRule { Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority };
var dataBar = new DataBar {ShowValue = !cf.ShowBarOnly};
- var conditionalFormatValueObject1 = new ConditionalFormatValueObject { Type = cf.ContentTypes[1].ToOpenXml() , Val = cf.Values[1] };
- var conditionalFormatValueObject2 = new ConditionalFormatValueObject { Type = cf.ContentTypes[2].ToOpenXml(), Val = cf.Values[2] };
+ var conditionalFormatValueObject1 = new ConditionalFormatValueObject { Type = cf.ContentTypes[1].ToOpenXml() , Val = cf.Values[1].Value };
+ var conditionalFormatValueObject2 = new ConditionalFormatValueObject { Type = cf.ContentTypes[2].ToOpenXml(), Val = cf.Values[2].Value };
var color = new Color { Rgb = cf.Colors[1].Color.ToHex() };
dataBar.Append(conditionalFormatValueObject1);
diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/Save/XLCFEndsWithConverter.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/Save/XLCFEndsWithConverter.cs
index d8ca520..1fa1532 100644
--- a/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/Save/XLCFEndsWithConverter.cs
+++ b/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/Save/XLCFEndsWithConverter.cs
@@ -10,7 +10,7 @@
{
public ConditionalFormattingRule Convert(IXLConditionalFormat cf, int priority, XLWorkbook.SaveContext context)
{
- String val = cf.Values[1];
+ String val = cf.Values[1].Value;
var conditionalFormattingRule = new ConditionalFormattingRule { FormatId = (UInt32)context.DifferentialFormats[cf.Style], Operator = cf.Operator.ToOpenXml(), Text = val, Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority };
var formula = new Formula { Text = "RIGHT(" + cf.Range.RangeAddress.FirstAddress.ToStringRelative(false) + "," + val.Length.ToString() + ")=\"" + val + "\"" };
diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/Save/XLCFIconSetConverter.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/Save/XLCFIconSetConverter.cs
index 9656913..b5922ec 100644
--- a/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/Save/XLCFIconSetConverter.cs
+++ b/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/Save/XLCFIconSetConverter.cs
@@ -17,7 +17,7 @@
Int32 count = cf.Values.Count;
for(Int32 i=1;i<= count; i++ )
{
- var conditionalFormatValueObject = new ConditionalFormatValueObject { Type = cf.ContentTypes[i].ToOpenXml(), Val = cf.Values[i], GreaterThanOrEqual = cf.IconSetOperators[i] == XLCFIconSetOperator.EqualOrGreaterThan};
+ var conditionalFormatValueObject = new ConditionalFormatValueObject { Type = cf.ContentTypes[i].ToOpenXml(), Val = cf.Values[i].Value, GreaterThanOrEqual = cf.IconSetOperators[i] == XLCFIconSetOperator.EqualOrGreaterThan};
iconSet.Append(conditionalFormatValueObject);
}
diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/Save/XLCFNotContainsConverter.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/Save/XLCFNotContainsConverter.cs
index 46ed6ca..86dcee8 100644
--- a/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/Save/XLCFNotContainsConverter.cs
+++ b/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/Save/XLCFNotContainsConverter.cs
@@ -10,7 +10,7 @@
{
public ConditionalFormattingRule Convert(IXLConditionalFormat cf, int priority, XLWorkbook.SaveContext context)
{
- String val = cf.Values[1];
+ String val = cf.Values[1].Value;
var conditionalFormattingRule = new ConditionalFormattingRule { FormatId = (UInt32)context.DifferentialFormats[cf.Style], Operator = cf.Operator.ToOpenXml(), Text = val, Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority };
var formula = new Formula { Text = "ISERROR(SEARCH(\"" + val + "\"," + cf.Range.RangeAddress.FirstAddress.ToStringRelative(false) + "))" };
diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/Save/XLCFStartsWithConverter.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/Save/XLCFStartsWithConverter.cs
index 68f41a1..82cfb88 100644
--- a/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/Save/XLCFStartsWithConverter.cs
+++ b/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/Save/XLCFStartsWithConverter.cs
@@ -10,7 +10,7 @@
{
public ConditionalFormattingRule Convert(IXLConditionalFormat cf, int priority, XLWorkbook.SaveContext context)
{
- String val = cf.Values[1];
+ String val = cf.Values[1].Value;
var conditionalFormattingRule = new ConditionalFormattingRule { FormatId = (UInt32)context.DifferentialFormats[cf.Style], Operator = cf.Operator.ToOpenXml(), Text = val, Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority };
var formula = new Formula { Text = "LEFT(" + cf.Range.RangeAddress.FirstAddress.ToStringRelative(false) + "," + val.Length.ToString() + ")=\"" + val + "\"" };
diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/Save/XLCFTopConverter.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/Save/XLCFTopConverter.cs
index 12f7731..57b90f3 100644
--- a/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/Save/XLCFTopConverter.cs
+++ b/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/Save/XLCFTopConverter.cs
@@ -10,7 +10,7 @@
{
public ConditionalFormattingRule Convert(IXLConditionalFormat cf, int priority, XLWorkbook.SaveContext context)
{
- UInt32 val = UInt32.Parse(cf.Values[1]);
+ UInt32 val = UInt32.Parse(cf.Values[1].Value);
var conditionalFormattingRule = new ConditionalFormattingRule { FormatId = (UInt32)context.DifferentialFormats[cf.Style], Percent = cf.Percent, Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority, Rank = val, Bottom = cf.Bottom};
return conditionalFormattingRule;
}
diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/XLCFColorScaleMax.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/XLCFColorScaleMax.cs
index 2aee533..af2a1cd 100644
--- a/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/XLCFColorScaleMax.cs
+++ b/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/XLCFColorScaleMax.cs
@@ -15,7 +15,7 @@
public void Maximum(XLCFContentType type, String value, XLColor color)
{
- _conditionalFormat.Values.Add(value);
+ _conditionalFormat.Values.Add(new XLFormula { Value = value });
_conditionalFormat.Colors.Add(color);
_conditionalFormat.ContentTypes.Add(type);
}
diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/XLCFColorScaleMid.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/XLCFColorScaleMid.cs
index b992309..85ec2b5 100644
--- a/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/XLCFColorScaleMid.cs
+++ b/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/XLCFColorScaleMid.cs
@@ -14,7 +14,7 @@
}
public IXLCFColorScaleMax Midpoint(XLCFContentType type, String value, XLColor color)
{
- _conditionalFormat.Values.Add(value);
+ _conditionalFormat.Values.Add(new XLFormula { Value = value });
_conditionalFormat.Colors.Add(color);
_conditionalFormat.ContentTypes.Add(type);
return new XLCFColorScaleMax(_conditionalFormat);
diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/XLCFColorScaleMin.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/XLCFColorScaleMin.cs
index fe39453..9da4137 100644
--- a/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/XLCFColorScaleMin.cs
+++ b/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/XLCFColorScaleMin.cs
@@ -15,7 +15,7 @@
public IXLCFColorScaleMid Minimum(XLCFContentType type, String value, XLColor color)
{
- _conditionalFormat.Values.Initialize(value);
+ _conditionalFormat.Values.Initialize(new XLFormula { Value = value });
_conditionalFormat.Colors.Initialize(color);
_conditionalFormat.ContentTypes.Initialize(type);
return new XLCFColorScaleMid(_conditionalFormat);
diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/XLCFDataBarMax.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/XLCFDataBarMax.cs
index 004e68d..4664b42 100644
--- a/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/XLCFDataBarMax.cs
+++ b/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/XLCFDataBarMax.cs
@@ -16,7 +16,7 @@
public void Maximum(XLCFContentType type, String value)
{
_conditionalFormat.ContentTypes.Add(type);
- _conditionalFormat.Values.Add(value);
+ _conditionalFormat.Values.Add(new XLFormula { Value = value });
}
public void Maximum(XLCFContentType type, Double value)
{
diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/XLCFDataBarMin.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/XLCFDataBarMin.cs
index 83ad237..d57f403 100644
--- a/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/XLCFDataBarMin.cs
+++ b/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/XLCFDataBarMin.cs
@@ -16,7 +16,7 @@
public IXLCFDataBarMax Minimum(XLCFContentType type, String value)
{
_conditionalFormat.ContentTypes.Initialize(type);
- _conditionalFormat.Values.Initialize(value);
+ _conditionalFormat.Values.Initialize(new XLFormula { Value = value });
return new XLCFDataBarMax(_conditionalFormat);
}
public IXLCFDataBarMax Minimum(XLCFContentType type, Double value)
diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/XLCFIconSet.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/XLCFIconSet.cs
index 21c0899..4b9bab1 100644
--- a/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/XLCFIconSet.cs
+++ b/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/XLCFIconSet.cs
@@ -15,7 +15,7 @@
public IXLCFIconSet AddValue(XLCFIconSetOperator setOperator, String value, XLCFContentType type)
{
_conditionalFormat.IconSetOperators.Add(setOperator);
- _conditionalFormat.Values.Add(value);
+ _conditionalFormat.Values.Add(new XLFormula { Value = value });
_conditionalFormat.ContentTypes.Add(type);
return new XLCFIconSet(_conditionalFormat);
}
diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/XLConditionalFormat.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/XLConditionalFormat.cs
index e23cf98..aa6e14e 100644
--- a/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/XLConditionalFormat.cs
+++ b/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/XLConditionalFormat.cs
@@ -12,7 +12,7 @@
{
Range = range;
Style = new XLStyle(this, range.Worksheet.Style);
- Values = new XLDictionary();
+ Values = new XLDictionary();
Colors = new XLDictionary();
ContentTypes = new XLDictionary();
IconSetOperators = new XLDictionary();
@@ -21,7 +21,7 @@
{
Range = other.Range;
Style = new XLStyle(this, other.Style);
- Values = new XLDictionary(other.Values);
+ Values = new XLDictionary(other.Values);
Colors = new XLDictionary(other.Colors);
ContentTypes = new XLDictionary(other.ContentTypes);
IconSetOperators = new XLDictionary(other.IconSetOperators);
@@ -76,7 +76,7 @@
}
public bool StyleChanged { get; set; }
- public XLDictionary Values { get; private set; }
+ public XLDictionary Values { get; private set; }
public XLDictionary Colors { get; private set; }
public XLDictionary ContentTypes { get; private set; }
public XLDictionary IconSetOperators { get; private set; }
@@ -145,92 +145,87 @@
}
public IXLStyle WhenContains(String value)
{
- Values.Initialize(value);
+ Values.Initialize(new XLFormula { Value = value });
ConditionalFormatType = XLConditionalFormatType.ContainsText;
Operator = XLCFOperator.Contains;
return Style;
}
public IXLStyle WhenNotContains(String value)
{
- Values.Initialize(value);
+ Values.Initialize(new XLFormula { Value = value });
ConditionalFormatType = XLConditionalFormatType.NotContainsText;
Operator = XLCFOperator.NotContains;
return Style;
}
public IXLStyle WhenStartsWith(String value)
{
- Values.Initialize(value);
+ Values.Initialize(new XLFormula { Value = value });
ConditionalFormatType = XLConditionalFormatType.StartsWith;
Operator = XLCFOperator.StartsWith;
return Style;
}
public IXLStyle WhenEndsWith(String value)
{
- Values.Initialize(value);
+ Values.Initialize(new XLFormula { Value = value });
ConditionalFormatType = XLConditionalFormatType.EndsWith;
Operator = XLCFOperator.EndsWith;
return Style;
}
- private String GetProperValue(String value)
- {
- return String.Format("\"{0}\"", value.Replace("\"", "\"\""));
- }
-
public IXLStyle WhenEquals(String value)
{
- Values.Initialize(GetProperValue(value));
+ Values.Initialize(new XLFormula { Value = value });
Operator = XLCFOperator.Equal;
ConditionalFormatType = XLConditionalFormatType.CellIs;
return Style;
}
public IXLStyle WhenNotEquals(String value)
{
- Values.Initialize(GetProperValue(value));
+ Values.Initialize(new XLFormula { Value = value });
Operator = XLCFOperator.NotEqual;
ConditionalFormatType = XLConditionalFormatType.CellIs;
return Style;
}
public IXLStyle WhenGreaterThan(String value)
{
- Values.Initialize(GetProperValue(value));
+ Values.Initialize(new XLFormula { Value = value });
Operator = XLCFOperator.GreaterThan;
ConditionalFormatType = XLConditionalFormatType.CellIs;
return Style;
}
public IXLStyle WhenLessThan(String value)
{
- Values.Initialize(GetProperValue(value));
+ Values.Initialize(new XLFormula { Value = value });
Operator = XLCFOperator.LessThan;
ConditionalFormatType = XLConditionalFormatType.CellIs;
return Style;
}
public IXLStyle WhenEqualOrGreaterThan(String value)
{
- Values.Initialize(GetProperValue(value));
+ Values.Initialize(new XLFormula { Value = value });
Operator = XLCFOperator.EqualOrGreaterThan;
ConditionalFormatType = XLConditionalFormatType.CellIs;
return Style;
}
public IXLStyle WhenEqualOrLessThan(String value)
{
- Values.Initialize(GetProperValue(value));
+ Values.Initialize(new XLFormula { Value = value });
Operator = XLCFOperator.EqualOrLessThan;
ConditionalFormatType = XLConditionalFormatType.CellIs;
return Style;
}
public IXLStyle WhenBetween(String minValue, String maxValue)
{
- Values.Initialize(GetProperValue(minValue));
- Values.Add(GetProperValue(maxValue));
+ Values.Initialize(new XLFormula { Value = minValue });
+ Values.Add(new XLFormula { Value = maxValue });
Operator = XLCFOperator.Between;
ConditionalFormatType = XLConditionalFormatType.CellIs;
return Style;
}
public IXLStyle WhenNotBetween(String minValue, String maxValue)
{
- Values.Initialize(GetProperValue(minValue));
- Values.Add(GetProperValue(maxValue));
+ Values.Initialize(new XLFormula { Value = minValue });
+ Values.Add(new XLFormula { Value = maxValue });
Operator = XLCFOperator.NotBetween;
ConditionalFormatType = XLConditionalFormatType.CellIs;
return Style;
@@ -238,58 +233,58 @@
public IXLStyle WhenEquals(Double value)
{
- Values.Initialize(value.ToString());
+ Values.Initialize(new XLFormula { Value = value.ToString() });
Operator = XLCFOperator.Equal;
ConditionalFormatType = XLConditionalFormatType.CellIs;
return Style;
}
public IXLStyle WhenNotEquals(Double value)
{
- Values.Initialize(value.ToString());
+ Values.Initialize(new XLFormula { Value = value.ToString() });
Operator = XLCFOperator.NotEqual;
ConditionalFormatType = XLConditionalFormatType.CellIs;
return Style;
}
public IXLStyle WhenGreaterThan(Double value)
{
- Values.Initialize(value.ToString());
+ Values.Initialize(new XLFormula { Value = value.ToString() });
Operator = XLCFOperator.GreaterThan;
ConditionalFormatType = XLConditionalFormatType.CellIs;
return Style;
}
public IXLStyle WhenLessThan(Double value)
{
- Values.Initialize(value.ToString());
+ Values.Initialize(new XLFormula { Value = value.ToString() });
Operator = XLCFOperator.LessThan;
ConditionalFormatType = XLConditionalFormatType.CellIs;
return Style;
}
public IXLStyle WhenEqualOrGreaterThan(Double value)
{
- Values.Initialize(value.ToString());
+ Values.Initialize(new XLFormula { Value = value.ToString() });
Operator = XLCFOperator.EqualOrGreaterThan;
ConditionalFormatType = XLConditionalFormatType.CellIs;
return Style;
}
public IXLStyle WhenEqualOrLessThan(Double value)
{
- Values.Initialize(value.ToString());
+ Values.Initialize(new XLFormula { Value = value.ToString() });
Operator = XLCFOperator.EqualOrLessThan;
ConditionalFormatType = XLConditionalFormatType.CellIs;
return Style;
}
public IXLStyle WhenBetween(Double minValue, Double maxValue)
{
- Values.Initialize(minValue.ToString());
- Values.Add(maxValue.ToString());
+ Values.Initialize(new XLFormula { Value = minValue.ToString() });
+ Values.Add(new XLFormula { Value = maxValue.ToString() });
Operator = XLCFOperator.Between;
ConditionalFormatType = XLConditionalFormatType.CellIs;
return Style;
}
public IXLStyle WhenNotBetween(Double minValue, Double maxValue)
{
- Values.Initialize(minValue.ToString());
- Values.Add(maxValue.ToString());
+ Values.Initialize(new XLFormula { Value = minValue.ToString() });
+ Values.Add(new XLFormula { Value = maxValue.ToString() });
Operator = XLCFOperator.NotBetween;
ConditionalFormatType = XLConditionalFormatType.CellIs;
return Style;
@@ -307,13 +302,14 @@
}
public IXLStyle WhenIsTrue(String formula)
{
- Values.Initialize(formula);
+ String f = formula.TrimStart()[0] == '=' ? formula : "=" + formula;
+ Values.Initialize(new XLFormula {Value = f});
ConditionalFormatType = XLConditionalFormatType.Expression;
return Style;
}
public IXLStyle WhenIsTop(Int32 value, XLTopBottomType topBottomType = XLTopBottomType.Items)
{
- Values.Initialize(value.ToString());
+ Values.Initialize(new XLFormula { Value = value.ToString() });
Percent = topBottomType == XLTopBottomType.Percent;
ConditionalFormatType = XLConditionalFormatType.Top10;
Bottom = false;
@@ -321,7 +317,7 @@
}
public IXLStyle WhenIsBottom(Int32 value, XLTopBottomType topBottomType = XLTopBottomType.Items)
{
- Values.Initialize(value.ToString());
+ Values.Initialize(new XLFormula { Value = value.ToString() });
Percent = topBottomType == XLTopBottomType.Percent;
ConditionalFormatType = XLConditionalFormatType.Top10;
Bottom = true;
diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Misc/XLDictionary.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Misc/XLDictionary.cs
index 026f80b..a79707c 100644
--- a/ClosedXML/ClosedXML/ClosedXML/Excel/Misc/XLDictionary.cs
+++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Misc/XLDictionary.cs
@@ -15,6 +15,7 @@
{
other.Values.ForEach(Add);
}
+
public void Initialize(T value)
{
if (Count > 0)
diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Misc/XLFormula.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Misc/XLFormula.cs
new file mode 100644
index 0000000..ff104bb
--- /dev/null
+++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Misc/XLFormula.cs
@@ -0,0 +1,37 @@
+using System;
+using System.Collections.Generic;
+using System.Linq;
+using System.Text;
+
+namespace ClosedXML.Excel
+{
+ public class XLFormula
+ {
+ internal String _value;
+ public String Value
+ {
+ get { return _value; }
+ set
+ {
+ if (value == null)
+ {
+ _value = String.Empty;
+ }
+ else
+ {
+ _value = value.Trim();
+ Double num;
+ IsFormula = !XLHelper.IsNullOrWhiteSpace(_value) && _value.TrimStart()[0] == '=' ;
+ if (IsFormula)
+ _value = _value.Substring(1);
+ //else if (!XLHelper.IsNullOrWhiteSpace(_value) && (!Double.TryParse(_value, out num) && _value[0] != '\"' && !_value.EndsWith("\"")))
+ // _value = String.Format("\"{0}\"", _value.Replace("\"", "\"\""));
+ }
+
+
+ }
+ }
+
+ public Boolean IsFormula { get; private set; }
+ }
+}
diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs
index db0e773..ad0bdce 100644
--- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs
+++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs
@@ -1324,13 +1324,13 @@
if (fr.Type != null)
conditionalFormat.ConditionalFormatType = fr.Type.Value.ToClosedXml();
if (fr.Text != null)
- conditionalFormat.Values.Add(fr.Text.Value);
+ conditionalFormat.Values.Add(new XLFormula { Value = fr.Text.Value });
if (fr.Percent != null)
conditionalFormat.Percent = fr.Percent.Value;
if (fr.Bottom != null)
conditionalFormat.Bottom = fr.Bottom.Value;
if(fr.Rank != null)
- conditionalFormat.Values.Add(fr.Rank.Value.ToString());
+ conditionalFormat.Values.Add(new XLFormula { Value = fr.Rank.Value.ToString() });
if (fr.Elements().Any())
{
@@ -1370,7 +1370,7 @@
String val = formula.Text.Replace("\"\"", "\"");
//if (val.StartsWith("\"")) val = val.Substring(1, val.Length - 2);
- conditionalFormat.Values.Add(val);
+ conditionalFormat.Values.Add(new XLFormula { Value = val });
}
}
}
@@ -1387,7 +1387,7 @@
if (c.Type != null)
conditionalFormat.ContentTypes.Add(c.Type.Value.ToClosedXml());
if (c.Val != null)
- conditionalFormat.Values.Add(c.Val.Value);
+ conditionalFormat.Values.Add(new XLFormula { Value = c.Val.Value });
if (c.GreaterThanOrEqual != null)
conditionalFormat.IconSetOperators.Add(c.GreaterThanOrEqual.Value ? XLCFIconSetOperator.EqualOrGreaterThan : XLCFIconSetOperator.GreaterThan);
diff --git a/ClosedXML/ClosedXML/ClosedXML_Net3.5/ClosedXML_Net3.5.csproj b/ClosedXML/ClosedXML/ClosedXML_Net3.5/ClosedXML_Net3.5.csproj
index de90b71..4e61e5d 100644
--- a/ClosedXML/ClosedXML/ClosedXML_Net3.5/ClosedXML_Net3.5.csproj
+++ b/ClosedXML/ClosedXML/ClosedXML_Net3.5/ClosedXML_Net3.5.csproj
@@ -468,6 +468,9 @@
Excel\Misc\XLDictionary.cs
+
+ Excel\Misc\XLFormula.cs
+
Excel\Misc\XLIdManager.cs
diff --git a/ClosedXML/ClosedXML/ClosedXML_Tests/Excel/Misc/CopyContentsTest.cs b/ClosedXML/ClosedXML/ClosedXML_Tests/Excel/Misc/CopyContentsTest.cs
index 3ffffbf..91de6ee 100644
--- a/ClosedXML/ClosedXML/ClosedXML_Tests/Excel/Misc/CopyContentsTest.cs
+++ b/ClosedXML/ClosedXML/ClosedXML_Tests/Excel/Misc/CopyContentsTest.cs
@@ -66,5 +66,18 @@
ws.Cell("A2").Value = ws.FirstCell();
Assert.AreEqual(2, ws.ConditionalFormats.Count());
}
+
+ [TestMethod]
+ public void CopyConditionalFormatsRelative()
+ {
+ var wb = new XLWorkbook();
+ var ws = wb.AddWorksheet("Sheet1");
+ ws.Cell("A1").Value = "1";
+ ws.Cell("B1").Value = "1";
+ ws.Cell("A1").AddConditionalFormat().WhenEquals("=B1").Fill.SetBackgroundColor(XLColor.Blue);
+ ws.Cell("A2").Value = ws.Cell("A1");
+ Assert.IsTrue(ws.ConditionalFormats.Any(cf => cf.Values.Any(v => v.Value.Value == "B1")));
+ Assert.IsTrue(ws.ConditionalFormats.Any(cf => cf.Values.Any(v => v.Value.Value == "B2")));
+ }
}
}