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); }