internal ConditionalFormatValueObject ToConditionalFormatValueObject()
{
ConditionalFormatValueObject cfvo = new ConditionalFormatValueObject();
cfvo.Type = this.Type;
if (this.Val.Length > 0)
{
if (this.Val.StartsWith("="))
{
cfvo.Val = this.Val.Substring(1);
}
else
{
cfvo.Val = this.Val;
}
}
if (!this.GreaterThanOrEqual)
{
cfvo.GreaterThanOrEqual = false;
}
return(cfvo);
}
public ConditionalFormattingRule Convert(IXLConditionalFormat cf, Int32 priority, XLWorkbook.SaveContext context)
{
var conditionalFormattingRule = new ConditionalFormattingRule {
Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority
};
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].Value
};
colorScale.Append(conditionalFormatValueObject);
}
for (Int32 i = 1; i <= cf.Values.Count; i++)
{
Color color = new Color {
Rgb = cf.Colors[i].Color.ToHex()
};
colorScale.Append(color);
}
conditionalFormattingRule.Append(colorScale);
return(conditionalFormattingRule);
}
public ConditionalFormattingRule Convert(IXLConditionalFormat cf, Int32 priority, XLWorkbook.SaveContext context)
{
var conditionalFormattingRule = XLCFBaseConverter.Convert(cf, priority);
var colorScale = new ColorScale();
for (Int32 i = 1; i <= cf.ContentTypes.Count; i++)
{
var type = cf.ContentTypes[i].ToOpenXml();
var val = (cf.Values.ContainsKey(i) && cf.Values[i] != null) ? cf.Values[i].Value : null;
var conditionalFormatValueObject = new ConditionalFormatValueObject {
Type = type
};
if (val != null)
{
conditionalFormatValueObject.Val = val;
}
colorScale.Append(conditionalFormatValueObject);
}
for (Int32 i = 1; i <= cf.Colors.Count; i++)
{
Color color = new Color {
Rgb = cf.Colors[i].Color.ToHex()
};
colorScale.Append(color);
}
conditionalFormattingRule.Append(colorScale);
return(conditionalFormattingRule);
}
internal void FromConditionalFormatValueObject(ConditionalFormatValueObject cfvo)
{
this.SetAllNull();
this.Type = cfvo.Type.Value;
if (cfvo.Val != null) this.Val = cfvo.Val.Value;
if (cfvo.GreaterThanOrEqual != null) this.GreaterThanOrEqual = cfvo.GreaterThanOrEqual.Value;
}
public static void AddConditionalFormatting(this Worksheet worksheet, int rowStart, int rowCount, int columnStart, int columnCount)
{
string range = ComputeRange(rowStart, rowCount, columnStart, columnCount);
ConditionalFormatting conditionalFormatting1 = new ConditionalFormatting()
{
SequenceOfReferences = new ListValue <StringValue>()
{
InnerText = range
}
};
ConditionalFormattingRule conditionalFormattingRule1 =
new ConditionalFormattingRule()
{
Type = ConditionalFormatValues.ColorScale, Priority = 1
};
ColorScale colorScale1 = new ColorScale();
ConditionalFormatValueObject conditionalFormatValueObject1 = new ConditionalFormatValueObject()
{
Type = ConditionalFormatValueObjectValues.Number, Val = "0"
};
ConditionalFormatValueObject conditionalFormatValueObject2 = new ConditionalFormatValueObject()
{
Type = ConditionalFormatValueObjectValues.Percentile, Val = "50"
};
ConditionalFormatValueObject conditionalFormatValueObject3 = new ConditionalFormatValueObject()
{
Type = ConditionalFormatValueObjectValues.Number, Val = "100"
};
Color color1 = new Color()
{
Rgb = "FFF8696B"
};
Color color2 = new Color()
{
Rgb = "FFFFEB84"
};
Color color3 = new Color()
{
Rgb = "FF63BE7B"
};
colorScale1.Append(conditionalFormatValueObject1);
colorScale1.Append(conditionalFormatValueObject2);
colorScale1.Append(conditionalFormatValueObject3);
colorScale1.Append(color1);
colorScale1.Append(color2);
colorScale1.Append(color3);
conditionalFormattingRule1.Append(colorScale1);
conditionalFormatting1.Append(conditionalFormattingRule1);
// If we don't have this after SheetData, it corrupts the file if we have added hyperlinks before
worksheet.InsertAfter(conditionalFormatting1, worksheet.Descendants <SheetData>().First());
}
internal ConditionalFormatValueObject ToConditionalFormatValueObject()
{
ConditionalFormatValueObject cfvo = new ConditionalFormatValueObject();
cfvo.Type = this.Type;
cfvo.Val = this.Val;
if (!this.GreaterThanOrEqual) cfvo.GreaterThanOrEqual = false;
return cfvo;
}
public ConditionalFormattingRule Convert(IXLConditionalFormat cf, Int32 priority, XLWorkbook.SaveContext context)
{
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()
};
if (cf.Values.Any() && cf.Values[1]?.Value != null)
{
conditionalFormatValueObject1.Val = cf.Values[1].Value;
}
var conditionalFormatValueObject2 = new ConditionalFormatValueObject {
Type = cf.ContentTypes[2].ToOpenXml()
};
if (cf.Values.Count >= 2 && cf.Values[2]?.Value != null)
{
conditionalFormatValueObject2.Val = cf.Values[2].Value;
}
var color = new Color();
switch (cf.Colors[1].ColorType)
{
case XLColorType.Color:
color.Rgb = cf.Colors[1].Color.ToHex();
break;
case XLColorType.Theme:
color.Theme = System.Convert.ToUInt32(cf.Colors[1].ThemeColor);
break;
case XLColorType.Indexed:
color.Indexed = System.Convert.ToUInt32(cf.Colors[1].Indexed);
break;
}
dataBar.Append(conditionalFormatValueObject1);
dataBar.Append(conditionalFormatValueObject2);
dataBar.Append(color);
conditionalFormattingRule.Append(dataBar);
return(conditionalFormattingRule);
}
internal void FromConditionalFormatValueObject(ConditionalFormatValueObject cfvo)
{
this.SetAllNull();
this.Type = cfvo.Type.Value;
if (cfvo.Val != null)
{
this.Val = cfvo.Val.Value;
}
if (cfvo.GreaterThanOrEqual != null)
{
this.GreaterThanOrEqual = cfvo.GreaterThanOrEqual.Value;
}
}
public ConditionalFormattingRule Convert(IXLConditionalFormat cf, Int32 priority, XLWorkbook.SaveContext context)
{
var conditionalFormattingRule = new ConditionalFormattingRule { Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority };
var iconSet = new IconSet {ShowValue = !cf.ShowIconOnly, Reverse = cf.ReverseIconOrder, IconSetValue = cf.IconSetStyle.ToOpenXml()};
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].Value, GreaterThanOrEqual = cf.IconSetOperators[i] == XLCFIconSetOperator.EqualOrGreaterThan};
iconSet.Append(conditionalFormatValueObject);
}
conditionalFormattingRule.Append(iconSet);
return conditionalFormattingRule;
}
internal ConditionalFormatValueObject ToConditionalFormatValueObject()
{
ConditionalFormatValueObject cfvo = new ConditionalFormatValueObject();
cfvo.Type = this.Type;
if (this.Val.Length > 0)
{
if (this.Val.StartsWith("=")) cfvo.Val = this.Val.Substring(1);
else cfvo.Val = this.Val;
}
if (!this.GreaterThanOrEqual) cfvo.GreaterThanOrEqual = false;
return cfvo;
}
public ConditionalFormattingRule Convert(IXLConditionalFormat cf, Int32 priority, XLWorkbook.SaveContext context)
{
var conditionalFormattingRule = XLCFBaseConverter.Convert(cf, priority);
var colorScale = new ColorScale();
for (Int32 i = 1; i <= cf.ContentTypes.Count; i++)
{
var type = cf.ContentTypes[i].ToOpenXml();
var val = cf.Values.TryGetValue(i, out XLFormula formula) ? formula?.Value : null;
var conditionalFormatValueObject = new ConditionalFormatValueObject {
Type = type
};
if (val != null)
{
conditionalFormatValueObject.Val = val;
}
colorScale.Append(conditionalFormatValueObject);
}
for (Int32 i = 1; i <= cf.Colors.Count; i++)
{
var xlColor = cf.Colors[i];
var color = new Color();
switch (xlColor.ColorType)
{
case XLColorType.Color:
color.Rgb = xlColor.Color.ToHex();
break;
case XLColorType.Theme:
color.Theme = System.Convert.ToUInt32(xlColor.ThemeColor);
break;
case XLColorType.Indexed:
color.Indexed = System.Convert.ToUInt32(xlColor.Indexed);
break;
}
colorScale.Append(color);
}
conditionalFormattingRule.Append(colorScale);
return(conditionalFormattingRule);
}
public ConditionalFormattingRule Convert(IXLConditionalFormat cf, Int32 priority, XLWorkbook.SaveContext context)
{
var conditionalFormattingRule = XLCFBaseConverter.Convert(cf, priority);
var iconSet = new IconSet {
ShowValue = !cf.ShowIconOnly, Reverse = cf.ReverseIconOrder, IconSetValue = cf.IconSetStyle.ToOpenXml()
};
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].Value, GreaterThanOrEqual = cf.IconSetOperators[i] == XLCFIconSetOperator.EqualOrGreaterThan
};
iconSet.Append(conditionalFormatValueObject);
}
conditionalFormattingRule.Append(iconSet);
return(conditionalFormattingRule);
}
private ConditionalFormatValueObject GetConditionalFormatValueObjectByIndex(IXLConditionalFormat cf, int index, ConditionalFormatValueObjectValues defaultType)
{
var conditionalFormatValueObject = new ConditionalFormatValueObject();
if (cf.ContentTypes.TryGetValue(index, out var contentType))
{
conditionalFormatValueObject.Type = contentType.ToOpenXml();
}
else
{
conditionalFormatValueObject.Type = defaultType;
}
if (cf.Values.TryGetValue(index, out var value1) && value1?.Value != null)
{
conditionalFormatValueObject.Val = value1.Value;
}
return(conditionalFormatValueObject);
}
public ConditionalFormattingRule Convert(IXLConditionalFormat cf, Int32 priority, XLWorkbook.SaveContext context)
{
var conditionalFormattingRule = new ConditionalFormattingRule { Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority };
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].Value };
colorScale.Append(conditionalFormatValueObject);
}
for (Int32 i = 1; i <= cf.Values.Count; i++)
{
Color color = new Color { Rgb = cf.Colors[i].Color.ToHex() };
colorScale.Append(color);
}
conditionalFormattingRule.Append(colorScale);
return conditionalFormattingRule;
}
public ConditionalFormattingRule Convert(IXLConditionalFormat cf, Int32 priority, XLWorkbook.SaveContext context)
{
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()};
if (cf.Values.Count >= 1) conditionalFormatValueObject1.Val = cf.Values[1].Value;
var conditionalFormatValueObject2 = new ConditionalFormatValueObject { Type = cf.ContentTypes[2].ToOpenXml()};
if (cf.Values.Count >= 2) conditionalFormatValueObject2.Val = cf.Values[2].Value;
var color = new Color { Rgb = cf.Colors[1].Color.ToHex() };
dataBar.Append(conditionalFormatValueObject1);
dataBar.Append(conditionalFormatValueObject2);
dataBar.Append(color);
conditionalFormattingRule.Append(dataBar);
return conditionalFormattingRule;
}
public ConditionalFormattingRule Convert(IXLConditionalFormat cf, Int32 priority, XLWorkbook.SaveContext context)
{
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()
};
if (cf.Values.Count >= 1)
{
conditionalFormatValueObject1.Val = cf.Values[1].Value;
}
var conditionalFormatValueObject2 = new ConditionalFormatValueObject {
Type = cf.ContentTypes[2].ToOpenXml()
};
if (cf.Values.Count >= 2)
{
conditionalFormatValueObject2.Val = cf.Values[2].Value;
}
var color = new Color {
Rgb = cf.Colors[1].Color.ToHex()
};
dataBar.Append(conditionalFormatValueObject1);
dataBar.Append(conditionalFormatValueObject2);
dataBar.Append(color);
conditionalFormattingRule.Append(dataBar);
return(conditionalFormattingRule);
}
public ConditionalFormattingRule Convert(IXLConditionalFormat cf, Int32 priority, XLWorkbook.SaveContext context)
{
var conditionalFormattingRule = XLCFBaseConverter.Convert(cf, priority);
var dataBar = new DataBar {
ShowValue = !cf.ShowBarOnly
};
var conditionalFormatValueObject1 = new ConditionalFormatValueObject {
Type = cf.ContentTypes[1].ToOpenXml()
};
if (cf.Values.Any() && cf.Values[1]?.Value != null)
{
conditionalFormatValueObject1.Val = cf.Values[1].Value;
}
var conditionalFormatValueObject2 = new ConditionalFormatValueObject {
Type = cf.ContentTypes[2].ToOpenXml()
};
if (cf.Values.Count >= 2 && cf.Values[2]?.Value != null)
{
conditionalFormatValueObject2.Val = cf.Values[2].Value;
}
var color = new Color();
switch (cf.Colors[1].ColorType)
{
case XLColorType.Color:
color.Rgb = cf.Colors[1].Color.ToHex();
break;
case XLColorType.Theme:
color.Theme = System.Convert.ToUInt32(cf.Colors[1].ThemeColor);
break;
case XLColorType.Indexed:
color.Indexed = System.Convert.ToUInt32(cf.Colors[1].Indexed);
break;
}
dataBar.Append(conditionalFormatValueObject1);
dataBar.Append(conditionalFormatValueObject2);
dataBar.Append(color);
conditionalFormattingRule.Append(dataBar);
if (cf.Colors.Count > 1)
{
ConditionalFormattingRuleExtensionList conditionalFormattingRuleExtensionList = new ConditionalFormattingRuleExtensionList();
ConditionalFormattingRuleExtension conditionalFormattingRuleExtension = new ConditionalFormattingRuleExtension {
Uri = "{B025F937-C7B1-47D3-B67F-A62EFF666E3E}"
};
conditionalFormattingRuleExtension.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main");
DocumentFormat.OpenXml.Office2010.Excel.Id id = new DocumentFormat.OpenXml.Office2010.Excel.Id
{
Text = (cf as XLConditionalFormat).Id.WrapInBraces()
};
conditionalFormattingRuleExtension.Append(id);
conditionalFormattingRuleExtensionList.Append(conditionalFormattingRuleExtension);
conditionalFormattingRule.Append(conditionalFormattingRuleExtensionList);
}
return(conditionalFormattingRule);
}
