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 ColorScale ToColorScale()
{
ColorScale cs = new ColorScale();
foreach (SLConditionalFormatValueObject cfvo in this.Cfvos)
{
cs.Append(cfvo.ToConditionalFormatValueObject());
}
foreach (SLColor clr in this.Colors)
{
cs.Append(clr.ToSpreadsheetColor());
}
return cs;
}
internal ColorScale ToColorScale()
{
var cs = new ColorScale();
foreach (var cfvo in Cfvos)
{
cs.Append(cfvo.ToConditionalFormatValueObject());
}
foreach (var clr in Colors)
{
cs.Append(clr.ToSpreadsheetColor());
}
return(cs);
}
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 = 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 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());
}
