diff --git a/ClosedXML/Excel/ConditionalFormats/IXLConditionalFormat.cs b/ClosedXML/Excel/ConditionalFormats/IXLConditionalFormat.cs index 4b78aa2..b5b3b24 100644 --- a/ClosedXML/Excel/ConditionalFormats/IXLConditionalFormat.cs +++ b/ClosedXML/Excel/ConditionalFormats/IXLConditionalFormat.cs @@ -99,6 +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); diff --git a/ClosedXML/Excel/ConditionalFormats/Save/XLCFDataBarConverter.cs b/ClosedXML/Excel/ConditionalFormats/Save/XLCFDataBarConverter.cs index cade461..f661a01 100644 --- a/ClosedXML/Excel/ConditionalFormats/Save/XLCFDataBarConverter.cs +++ b/ClosedXML/Excel/ConditionalFormats/Save/XLCFDataBarConverter.cs @@ -28,19 +28,21 @@ 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); + 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.Name }; + conditionalFormattingRuleExtension.Append(id); + + conditionalFormattingRuleExtensionList.Append(conditionalFormattingRuleExtension); + + conditionalFormattingRule.Append(conditionalFormattingRuleExtensionList); + } return conditionalFormattingRule; } } diff --git a/ClosedXML/Excel/ConditionalFormats/XLConditionalFormat.cs b/ClosedXML/Excel/ConditionalFormats/XLConditionalFormat.cs index 5cd29f5..e318a5b 100644 --- a/ClosedXML/Excel/ConditionalFormats/XLConditionalFormat.cs +++ b/ClosedXML/Excel/ConditionalFormats/XLConditionalFormat.cs @@ -337,6 +337,13 @@ ConditionalFormatType = XLConditionalFormatType.ColorScale; return new XLCFColorScaleMin(this); } + public IXLCFDataBarMin DataBar(XLColor color, Boolean showBarOnly = false) + { + Colors.Initialize(color); + ShowBarOnly = showBarOnly; + ConditionalFormatType = XLConditionalFormatType.DataBar; + return new XLCFDataBarMin(this); + } public IXLCFDataBarMin DataBar(XLColor color, XLColor colorNegative, Boolean showBarOnly = false) { Colors.Initialize(color); diff --git a/ClosedXML/Excel/ConditionalFormats/XLConditionalFormats.cs b/ClosedXML/Excel/ConditionalFormats/XLConditionalFormats.cs index 5d1ea42..104dcb7 100644 --- a/ClosedXML/Excel/ConditionalFormats/XLConditionalFormats.cs +++ b/ClosedXML/Excel/ConditionalFormats/XLConditionalFormats.cs @@ -5,11 +5,16 @@ namespace ClosedXML.Excel { - internal class XLConditionalFormats: IXLConditionalFormats + internal class XLConditionalFormats : IXLConditionalFormats { private readonly List _conditionalFormats = new List(); public void Add(IXLConditionalFormat conditionalFormat) { + byte[] bytes = new byte[16]; + BitConverter.GetBytes(_conditionalFormats.Count + 1).CopyTo(bytes, 0); + var guid = new Guid(bytes); + conditionalFormat.Name = string.Concat("{", guid.ToString(), "}"); + _conditionalFormats.Add(conditionalFormat); } diff --git a/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/Excel/XLWorkbook_Save.cs index d3ae289..bcd2dc1 100644 --- a/ClosedXML/Excel/XLWorkbook_Save.cs +++ b/ClosedXML/Excel/XLWorkbook_Save.cs @@ -4411,49 +4411,57 @@ } } - if (!worksheetPart.Worksheet.Elements().Any()) + + var exlst = from c in xlWorksheet.ConditionalFormats where c.ConditionalFormatType == XLConditionalFormatType.DataBar && c.Colors.Count > 1 && typeof(IXLConditionalFormat).IsAssignableFrom(c.GetType()) select c; + if (exlst != null && exlst.Count() > 0) { - 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) + if (!worksheetPart.Worksheet.Elements().Any()) { - DocumentFormat.OpenXml.Office2010.Excel.ConditionalFormattingRule conditionalFormattingRule = ( - from r in worksheetExtensionList.Descendants() - where r.Id == xLConditionalFormat.Name - select r).SingleOrDefault(); - if (conditionalFormattingRule != null) + 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 exlst + .GroupBy( + c => c.Range.RangeAddress.ToStringRelative(false), + c => c, + (key, g) => new { RangeId = key, CfList = g.ToList() } + ) + ) + { + foreach (IXLConditionalFormat xLConditionalFormat in cfGroup.CfList) { - WorksheetExtension worksheetExtension = conditionalFormattingRule.Ancestors().SingleOrDefault(); - worksheetExtensionList.RemoveChild(worksheetExtension); + 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); } - 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 460a967..b14e339 100644 --- a/ClosedXML_Examples/ConditionalFormatting/ConditionalFormatting.cs +++ b/ClosedXML_Examples/ConditionalFormatting/ConditionalFormatting.cs @@ -519,6 +519,26 @@ .CellBelow().SetValue(2) .CellBelow().SetValue(3); + ws.RangeUsed().AddConditionalFormat().DataBar(XLColor.Red, true) + .LowestValue() + .Maximum(XLCFContentType.Percent, "100"); + + workbook.SaveAs(filePath); + } + } + + public class CFDataBarNegative : IXLExample + { + public void Create(String filePath) + { + var workbook = new XLWorkbook(); + var ws = workbook.AddWorksheet("Sheet1"); + + ws.FirstCell().SetValue(1) + .CellBelow().SetValue(1) + .CellBelow().SetValue(2) + .CellBelow().SetValue(3); + ws.RangeUsed().AddConditionalFormat().DataBar(XLColor.Green, XLColor.Red, false) .LowestValue() .Maximum(XLCFContentType.Percent, "100"); diff --git a/ClosedXML_Tests/ClosedXML_Tests.csproj b/ClosedXML_Tests/ClosedXML_Tests.csproj index 487ea3a..6879598 100644 --- a/ClosedXML_Tests/ClosedXML_Tests.csproj +++ b/ClosedXML_Tests/ClosedXML_Tests.csproj @@ -285,6 +285,7 @@ + diff --git a/ClosedXML_Tests/Examples/ConditionalFormattingTests.cs b/ClosedXML_Tests/Examples/ConditionalFormattingTests.cs index 0760da0..8a298ae 100644 --- a/ClosedXML_Tests/Examples/ConditionalFormattingTests.cs +++ b/ClosedXML_Tests/Examples/ConditionalFormattingTests.cs @@ -37,6 +37,12 @@ } [Test] + public void CFDataBarNegative() + { + TestHelper.RunTestExample(@"ConditionalFormatting\CFDataBarNegative.xlsx"); + } + + [Test] public void CFEndsWith() { TestHelper.RunTestExample(@"ConditionalFormatting\CFEndsWith.xlsx"); diff --git a/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFDataBarNegative.xlsx b/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFDataBarNegative.xlsx new file mode 100644 index 0000000..b743aaf --- /dev/null +++ b/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFDataBarNegative.xlsx Binary files differ