private ConditionalFormattingRuleExtension BuildRuleExtension(IXLConditionalFormat cf)
{
var conditionalFormattingRuleExtension = new ConditionalFormattingRuleExtension {
Uri = "{B025F937-C7B1-47D3-B67F-A62EFF666E3E}"
};
conditionalFormattingRuleExtension.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main");
var id = new DocumentFormat.OpenXml.Office2010.Excel.Id
{
Text = (cf as XLConditionalFormat).Id.WrapInBraces()
};
conditionalFormattingRuleExtension.Append(id);
return(conditionalFormattingRuleExtension);
}
/// <summary>
/// Adds conditional formatting into the currently selected worksheet.
/// </summary>
/// <param name="ConditionalFormatting">An SLConditionalFormatting object with the formatting rules already set. Remember to set at least one formatting rule (a data bar, color scale, icon set or some custom rule).</param>
/// <returns>True if successfully added. False otherwise.</returns>
public bool AddConditionalFormatting(SLConditionalFormatting ConditionalFormatting)
{
bool result = false;
if (ConditionalFormatting.Rules.Count > 0 && ConditionalFormatting.SequenceOfReferences.Count > 0)
{
result = true;
int index = ConditionalFormatting.Rules.Count;
foreach (SLConditionalFormatting cf in slws.ConditionalFormattings)
{
foreach (SLConditionalFormattingRule cfr in cf.Rules)
{
cfr.Priority += index;
}
}
foreach (SLConditionalFormatting2010 cf2010 in slws.ConditionalFormattings2010)
{
foreach (SLConditionalFormattingRule2010 cfr2010 in cf2010.Rules)
{
if (cfr2010.Priority != null) cfr2010.Priority += index;
}
}
bool bIs2010 = false;
bool bIsDataBar2010 = false;
bool bIsIconSet2010 = false;
string sGuid = string.Empty;
SLConditionalFormatting2010 cf2010new = new SLConditionalFormatting2010();
foreach (SLCellPointRange pt in ConditionalFormatting.SequenceOfReferences)
{
cf2010new.ReferenceSequence.Add(new SLCellPointRange(pt.StartRowIndex, pt.StartColumnIndex, pt.EndRowIndex, pt.EndColumnIndex));
}
SLConditionalFormattingRule2010 cfr2010new;
ConditionalFormattingRuleExtension cfrext;
index = 1;
int i;
// the latest added rule takes first priority
// And also we might delete (2010 icon set) so need to start at the end
for (i = ConditionalFormatting.Rules.Count - 1; i >= 0; --i)
{
ConditionalFormatting.Rules[i].Priority = index;
++index;
if (ConditionalFormatting.Rules[i].HasDifferentialFormat)
{
ConditionalFormatting.Rules[i].FormatId = (uint)this.SaveToStylesheetDifferentialFormat(ConditionalFormatting.Rules[i].DifferentialFormat.ToHash());
}
bIsDataBar2010 = ConditionalFormatting.Rules[i].HasDataBar && ConditionalFormatting.Rules[i].DataBar.Is2010;
bIsIconSet2010 = ConditionalFormatting.Rules[i].HasIconSet && ConditionalFormatting.Rules[i].IconSet.Is2010;
// supposedly both cannot be true at the same time because each rule
// can only have one rule (duh) at any one time, whether it's color scale,
// data bar, icon set, top 10 or whatever rule there is.
if (bIsDataBar2010 || bIsIconSet2010)
{
bIs2010 = true;
cfr2010new = ConditionalFormatting.Rules[i].ToSLConditionalFormattingRule2010();
if (bIsDataBar2010)
{
// go read the Open XML specs on why it has to be null.
// We null the priority so the extension fill color is not rendered.
// Presumably, it uses the color in the normally placed data bar.
// I don't know why Microsoft made it such that data bars exist in the
// normal place and in the extension place.
// Why not exist entirely in either the normal or extension like the
// Excel 2010 icon sets?
cfr2010new.Priority = null;
// If I'm reading the specs correctly, if there's an extension, then
// the normal placed data bar needs to have the min/max lengths as defaults.
// AKA 10 and 90 percent respectively.
// Hey reading that part of the specs takes longer than watching the Titanic
// movie... and more convulated than a legal clause...
ConditionalFormatting.Rules[i].DataBar.MinLength = 10;
ConditionalFormatting.Rules[i].DataBar.MaxLength = 90;
sGuid = string.Format("{{{0}}}", Guid.NewGuid().ToString().ToUpperInvariant());
cfr2010new.Id = sGuid;
cfrext = new ConditionalFormattingRuleExtension();
cfrext.Uri = SLConstants.DataBarExtensionUri;
cfrext.Append(new X14.Id(sGuid));
ConditionalFormatting.Rules[i].Extensions.Add(cfrext);
}
if (bIsIconSet2010)
{
sGuid = string.Format("{{{0}}}", Guid.NewGuid().ToString().ToUpperInvariant());
cfr2010new.Id = sGuid;
// 2010 icon set exists entirely in the extension part
ConditionalFormatting.Rules.RemoveAt(i);
}
// we insert at index 0 because we started from the end of the existing set
// of rules. The new 2010 version will then follow the existing order (sort of).
cf2010new.Rules.Insert(0, cfr2010new.Clone());
}
}
// in case there's only 2010 icon set rule
if (ConditionalFormatting.Rules.Count > 0)
{
slws.ConditionalFormattings.Add(ConditionalFormatting.Clone());
}
if (bIs2010)
{
slws.ConditionalFormattings2010.Add(cf2010new.Clone());
}
}
return result;
}
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);
}
