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