diff --git a/ClosedXML/ClosedXML.csproj b/ClosedXML/ClosedXML.csproj index 893e0dd..564d1ec 100644 --- a/ClosedXML/ClosedXML.csproj +++ b/ClosedXML/ClosedXML.csproj @@ -72,6 +72,9 @@ + + + diff --git a/ClosedXML/Excel/ConditionalFormats/IXLConditionalFormat.cs b/ClosedXML/Excel/ConditionalFormats/IXLConditionalFormat.cs index 962cc21..4b78aa2 100644 --- a/ClosedXML/Excel/ConditionalFormats/IXLConditionalFormat.cs +++ b/ClosedXML/Excel/ConditionalFormats/IXLConditionalFormat.cs @@ -62,13 +62,14 @@ public enum XLCFOperator { Equal, NotEqual, GreaterThan, LessThan, EqualOrGreaterThan, EqualOrLessThan, Between, NotBetween, Contains, NotContains, StartsWith, EndsWith } public interface IXLConditionalFormat { + String Name { get; set; } IXLStyle Style { get; set; } IXLStyle WhenIsBlank(); IXLStyle WhenNotBlank(); IXLStyle WhenIsError(); IXLStyle WhenNotError(); - IXLStyle WhenDateIs(XLTimePeriod timePeriod ); + IXLStyle WhenDateIs(XLTimePeriod timePeriod); IXLStyle WhenContains(String value); IXLStyle WhenNotContains(String value); IXLStyle WhenStartsWith(String value); @@ -98,7 +99,7 @@ IXLStyle WhenIsBottom(Int32 value, XLTopBottomType topBottomType); IXLCFColorScaleMin ColorScale(); - IXLCFDataBarMin DataBar(XLColor color, Boolean showBarOnly = false); + IXLCFDataBarMin DataBar(XLColor color, XLColor colorNegative, Boolean showBarOnly = false); IXLCFIconSet IconSet(XLIconSetStyle iconSetStyle, Boolean reverseIconOrder = false, Boolean showIconOnly = false); XLConditionalFormatType ConditionalFormatType { get; } diff --git a/ClosedXML/Excel/ConditionalFormats/Save/IXLCFConverterExtension.cs b/ClosedXML/Excel/ConditionalFormats/Save/IXLCFConverterExtension.cs new file mode 100644 index 0000000..82b11a0 --- /dev/null +++ b/ClosedXML/Excel/ConditionalFormats/Save/IXLCFConverterExtension.cs @@ -0,0 +1,13 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; +using DocumentFormat.OpenXml.Office2010.Excel; + +namespace ClosedXML.Excel +{ + internal interface IXLCFConverterExtension + { + ConditionalFormattingRule Convert(IXLConditionalFormat cf, XLWorkbook.SaveContext context); + } +} diff --git a/ClosedXML/Excel/ConditionalFormats/Save/XLCFConvertersExtension.cs b/ClosedXML/Excel/ConditionalFormats/Save/XLCFConvertersExtension.cs new file mode 100644 index 0000000..7b85951 --- /dev/null +++ b/ClosedXML/Excel/ConditionalFormats/Save/XLCFConvertersExtension.cs @@ -0,0 +1,28 @@ +using DocumentFormat.OpenXml.Office2010.Excel; +using System; +using System.Collections.Generic; + +namespace ClosedXML.Excel +{ + internal class XLCFConvertersExtension + { + private readonly static Dictionary Converters; + + static XLCFConvertersExtension() + { + XLCFConvertersExtension.Converters = new Dictionary() + { + { XLConditionalFormatType.DataBar, new XLCFDataBarConverterExtension() } + }; + } + + public XLCFConvertersExtension() + { + } + + public static ConditionalFormattingRule Convert(IXLConditionalFormat conditionalFormat, XLWorkbook.SaveContext context) + { + return XLCFConvertersExtension.Converters[conditionalFormat.ConditionalFormatType].Convert(conditionalFormat, context); + } + } +} \ No newline at end of file diff --git a/ClosedXML/Excel/ConditionalFormats/Save/XLCFDataBarConverter.cs b/ClosedXML/Excel/ConditionalFormats/Save/XLCFDataBarConverter.cs index f0bd151..cade461 100644 --- a/ClosedXML/Excel/ConditionalFormats/Save/XLCFDataBarConverter.cs +++ b/ClosedXML/Excel/ConditionalFormats/Save/XLCFDataBarConverter.cs @@ -7,17 +7,18 @@ namespace ClosedXML.Excel { - internal class XLCFDataBarConverter:IXLCFConverter + internal class XLCFDataBarConverter : IXLCFConverter { 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()}; + 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()}; + 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() }; @@ -26,7 +27,19 @@ dataBar.Append(conditionalFormatValueObject2); dataBar.Append(color); + + + 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.Name }; + conditionalFormattingRuleExtension.Append(id); + + conditionalFormattingRuleExtensionList.Append(conditionalFormattingRuleExtension); + conditionalFormattingRule.Append(dataBar); + conditionalFormattingRule.Append(conditionalFormattingRuleExtensionList); return conditionalFormattingRule; } diff --git a/ClosedXML/Excel/ConditionalFormats/Save/XLCFDataBarConverterExtension.cs b/ClosedXML/Excel/ConditionalFormats/Save/XLCFDataBarConverterExtension.cs new file mode 100644 index 0000000..7d489de --- /dev/null +++ b/ClosedXML/Excel/ConditionalFormats/Save/XLCFDataBarConverterExtension.cs @@ -0,0 +1,53 @@ +using DocumentFormat.OpenXml; +//using DocumentFormat.OpenXml.Spreadsheet; +using DocumentFormat.OpenXml.Office2010.Excel; +using System; +using System.Collections.Generic; + +namespace ClosedXML.Excel +{ + internal class XLCFDataBarConverterExtension : IXLCFConverterExtension + { + public XLCFDataBarConverterExtension() + { + } + + public ConditionalFormattingRule Convert(IXLConditionalFormat cf, XLWorkbook.SaveContext context) + { + ConditionalFormattingRule conditionalFormattingRule = new ConditionalFormattingRule() + { + Type = DocumentFormat.OpenXml.Spreadsheet.ConditionalFormatValues.DataBar, + Id = cf.Name + }; + + DataBar dataBar = new DataBar() + { + MinLength = 0, + MaxLength = 100, + Gradient = false, + AxisPosition = DataBarAxisPositionValues.Middle + }; + + var cfMin = new ConditionalFormattingValueObject { Type = ConditionalFormattingValueObjectTypeValues.AutoMin }; + var cfMax = new ConditionalFormattingValueObject() { Type = ConditionalFormattingValueObjectTypeValues.AutoMax }; + + var barAxisColor = new BarAxisColor { Rgb = XLColor.Black.Color.ToHex() }; + + var negativeFillColor = new NegativeFillColor { Rgb = cf.Colors[1].Color.ToHex() }; + if (cf.Colors.Count == 2) + { + negativeFillColor = new NegativeFillColor { Rgb = cf.Colors[2].Color.ToHex() }; + } + + dataBar.Append(cfMin); + dataBar.Append(cfMax); + + dataBar.Append(negativeFillColor); + dataBar.Append(barAxisColor); + + conditionalFormattingRule.Append(dataBar); + + return conditionalFormattingRule; + } + } +} diff --git a/ClosedXML/Excel/ConditionalFormats/XLConditionalFormat.cs b/ClosedXML/Excel/ConditionalFormats/XLConditionalFormat.cs index 96847d6..5cd29f5 100644 --- a/ClosedXML/Excel/ConditionalFormats/XLConditionalFormat.cs +++ b/ClosedXML/Excel/ConditionalFormats/XLConditionalFormat.cs @@ -5,11 +5,12 @@ namespace ClosedXML.Excel { - internal class XLConditionalFormat: IXLConditionalFormat, IXLStylized + internal class XLConditionalFormat : IXLConditionalFormat, IXLStylized { - + public XLConditionalFormat(XLRange range, Boolean copyDefaultModify = false) { + Name = string.Concat("{", Guid.NewGuid().ToString(), "}"); Range = range; Style = new XLStyle(this, range.Worksheet.Style); Values = new XLDictionary(); @@ -20,6 +21,7 @@ } public XLConditionalFormat(XLConditionalFormat other) { + Name = string.Concat("{", Guid.NewGuid().ToString(), "}"); Range = other.Range; Style = new XLStyle(this, other.Style); Values = new XLDictionary(other.Values); @@ -39,10 +41,11 @@ ShowBarOnly = other.ShowBarOnly; } + public String Name { get; set; } public Boolean CopyDefaultModify { get; set; } private IXLStyle _style; private Int32 _styleCacheId; - public IXLStyle Style{ get { return GetStyle(); } set { SetStyle(value); } } + public IXLStyle Style { get { return GetStyle(); } set { SetStyle(value); } } private IXLStyle GetStyle() { //return _style; @@ -88,12 +91,12 @@ public XLConditionalFormatType ConditionalFormatType { get; set; } public XLTimePeriod TimePeriod { get; set; } public XLIconSetStyle IconSetStyle { get; set; } - public XLCFOperator Operator { get; set; } - public Boolean Bottom { get; set; } - public Boolean Percent { get; set; } - public Boolean ReverseIconOrder { get; set; } - public Boolean ShowIconOnly { get; set; } - public Boolean ShowBarOnly { get; set; } + public XLCFOperator Operator { get; set; } + public Boolean Bottom { get; set; } + public Boolean Percent { get; set; } + public Boolean ReverseIconOrder { get; set; } + public Boolean ShowIconOnly { get; set; } + public Boolean ShowBarOnly { get; set; } public void CopyFrom(IXLConditionalFormat other) { @@ -279,8 +282,8 @@ return Style; } public IXLStyle WhenBetween(Double minValue, Double maxValue) - { - Values.Initialize(new XLFormula (minValue)); + { + Values.Initialize(new XLFormula(minValue)); Values.Add(new XLFormula(maxValue)); Operator = XLCFOperator.Between; ConditionalFormatType = XLConditionalFormatType.CellIs; @@ -308,7 +311,7 @@ public IXLStyle WhenIsTrue(String formula) { String f = formula.TrimStart()[0] == '=' ? formula : "=" + formula; - Values.Initialize(new XLFormula {Value = f}); + Values.Initialize(new XLFormula { Value = f }); ConditionalFormatType = XLConditionalFormatType.Expression; return Style; } @@ -328,15 +331,16 @@ Bottom = true; return Style; } - + public IXLCFColorScaleMin ColorScale() { ConditionalFormatType = XLConditionalFormatType.ColorScale; return new XLCFColorScaleMin(this); } - public IXLCFDataBarMin DataBar(XLColor color, Boolean showBarOnly = false) + public IXLCFDataBarMin DataBar(XLColor color, XLColor colorNegative, Boolean showBarOnly = false) { Colors.Initialize(color); + Colors.Add(colorNegative); ShowBarOnly = showBarOnly; ConditionalFormatType = XLConditionalFormatType.DataBar; return new XLCFDataBarMin(this); diff --git a/ClosedXML/Excel/XLWorkbook_Load.cs b/ClosedXML/Excel/XLWorkbook_Load.cs index a23ab4e..ec3a6b2 100644 --- a/ClosedXML/Excel/XLWorkbook_Load.cs +++ b/ClosedXML/Excel/XLWorkbook_Load.cs @@ -257,6 +257,8 @@ LoadRowBreaks((RowBreaks)reader.LoadCurrentElement(), ws); else if (reader.ElementType == typeof(ColumnBreaks)) LoadColumnBreaks((ColumnBreaks)reader.LoadCurrentElement(), ws); + else if (reader.ElementType == typeof(WorksheetExtensionList)) + LoadExtensions((WorksheetExtensionList)reader.LoadCurrentElement(), ws); else if (reader.ElementType == typeof(LegacyDrawing)) ws.LegacyDrawingId = (reader.LoadCurrentElement() as LegacyDrawing).Id.Value; } @@ -1843,6 +1845,12 @@ var dataBar = fr.Elements().First(); if (dataBar.ShowValue != null) conditionalFormat.ShowBarOnly = !dataBar.ShowValue.Value; + var id = fr.Descendants().FirstOrDefault(); + if (id != null) + { + + conditionalFormat.Name = id.Text; + } ExtractConditionalFormatValueObjects(conditionalFormat, dataBar); } else if (fr.Elements().Any()) @@ -1877,6 +1885,34 @@ } } + private void LoadExtensions(WorksheetExtensionList extensions, XLWorksheet ws) + { + if (extensions == null) + { + return; + } + + foreach (var conditionalFormattingRule in + from r in extensions.Descendants() + where r.Type == ConditionalFormatValues.DataBar + select r) + { + IXLConditionalFormat xLConditionalFormat = ( + from cf in ws.ConditionalFormats + where cf.Name == conditionalFormattingRule.Id + select cf).SingleOrDefault(); + + if (xLConditionalFormat == null) + { + continue; + } + + var negativeFillColor = conditionalFormattingRule.Descendants().SingleOrDefault(); + var color = new DocumentFormat.OpenXml.Spreadsheet.Color { Rgb = negativeFillColor.Rgb }; + xLConditionalFormat.Colors.Add(this.GetColor(color)); + } + } + private static XLFormula GetFormula(String value) { var formula = new XLFormula(); diff --git a/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/Excel/XLWorkbook_Save.cs index fcba6c8..d3ae289 100644 --- a/ClosedXML/Excel/XLWorkbook_Save.cs +++ b/ClosedXML/Excel/XLWorkbook_Save.cs @@ -4411,6 +4411,49 @@ } } + if (!worksheetPart.Worksheet.Elements().Any()) + { + var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.WorksheetExtensionList); + worksheetPart.Worksheet.InsertAfter(new WorksheetExtensionList(), previousElement); + } + WorksheetExtensionList worksheetExtensionList = worksheetPart.Worksheet.Elements().First(); + cm.SetElement(XLWSContentManager.XLWSContents.WorksheetExtensionList, worksheetExtensionList); + foreach (var cfGroup in (from c in xlWorksheet.ConditionalFormats where typeof(IXLConditionalFormat).IsAssignableFrom(c.GetType()) select c) + .GroupBy( + c => c.Range.RangeAddress.ToStringRelative(false), + c => c, + (key, g) => new { RangeId = key, CfList = g.ToList() } + ) + ) + { + foreach (IXLConditionalFormat xLConditionalFormat in cfGroup.CfList) + { + DocumentFormat.OpenXml.Office2010.Excel.ConditionalFormattingRule conditionalFormattingRule = ( + from r in worksheetExtensionList.Descendants() + where r.Id == xLConditionalFormat.Name + select r).SingleOrDefault(); + if (conditionalFormattingRule != null) + { + WorksheetExtension worksheetExtension = conditionalFormattingRule.Ancestors().SingleOrDefault(); + worksheetExtensionList.RemoveChild(worksheetExtension); + } + WorksheetExtension worksheetExtension1 = new WorksheetExtension { Uri = "{78C0D931-6437-407d-A8EE-F0AAD7539E65}" }; + + worksheetExtension1.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main"); + var conditionalFormattings = new DocumentFormat.OpenXml.Office2010.Excel.ConditionalFormattings(); + + var conditionalFormatting = new DocumentFormat.OpenXml.Office2010.Excel.ConditionalFormatting(); + conditionalFormatting.AddNamespaceDeclaration("xm", "http://schemas.microsoft.com/office/excel/2006/main"); + conditionalFormatting.Append(XLCFConvertersExtension.Convert(xLConditionalFormat, context)); + var referenceSequence = new DocumentFormat.OpenXml.Office.Excel.ReferenceSequence { Text = cfGroup.RangeId }; + conditionalFormatting.Append(referenceSequence); + + conditionalFormattings.Append(conditionalFormatting); + worksheetExtension1.Append(conditionalFormattings); + + worksheetExtensionList.Append(worksheetExtension1); + } + } #endregion Conditional Formatting #region DataValidations diff --git a/ClosedXML_Examples/ConditionalFormatting/ConditionalFormatting.cs b/ClosedXML_Examples/ConditionalFormatting/ConditionalFormatting.cs index a87bf0c..460a967 100644 --- a/ClosedXML_Examples/ConditionalFormatting/ConditionalFormatting.cs +++ b/ClosedXML_Examples/ConditionalFormatting/ConditionalFormatting.cs @@ -519,7 +519,7 @@ .CellBelow().SetValue(2) .CellBelow().SetValue(3); - ws.RangeUsed().AddConditionalFormat().DataBar(XLColor.Red, true) + ws.RangeUsed().AddConditionalFormat().DataBar(XLColor.Green, XLColor.Red, false) .LowestValue() .Maximum(XLCFContentType.Percent, "100"); @@ -579,7 +579,7 @@ var workbook = new XLWorkbook(); var ws = workbook.AddWorksheet("Sheet1"); - ws.Cell(2,1).SetValue(1) + ws.Cell(2, 1).SetValue(1) .CellRight().SetValue(1) .CellRight().SetValue(2) .CellRight().SetValue(3); @@ -606,7 +606,7 @@ .CellBelow().SetValue(3) .CellBelow().SetValue(4); - ws.RangeUsed().AddConditionalFormat().DataBar(XLColor.Red) + ws.RangeUsed().AddConditionalFormat().DataBar(XLColor.Red, XLColor.Green) .LowestValue() .HighestValue(); @@ -621,7 +621,7 @@ var workbook = new XLWorkbook(); var ws = workbook.AddWorksheet("Sheet1"); - using(var range = ws.Range("A1:A10")) + using (var range = ws.Range("A1:A10")) { range.AddConditionalFormat().WhenEquals("3") .Fill.SetBackgroundColor(XLColor.Blue);