diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/XLConditionalFormat.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/XLConditionalFormat.cs index 3b90f5b..98bccb7 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/XLConditionalFormat.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/XLConditionalFormat.cs @@ -64,12 +64,12 @@ public XLDictionary ContentTypes { get; private set; } public XLDictionary IconSetOperators { get; private set; } - public XLCFOperator Operator { get; private set; } - public Boolean Bottom { get; private set; } - public Boolean Percent { get; private set; } - public Boolean ReverseIconOrder { get; private set; } - public Boolean ShowIconOnly { get; private set; } - public Boolean ShowBarOnly { get; private 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 IXLStyle WhenIsBlank() { diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs index 6926796..446b8c4 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs @@ -109,7 +109,8 @@ var fills = s.Fills; var borders = s.Borders; var fonts = s.Fonts; - + Int32 dfCount = 0; + var differentialFormats = s.DifferentialFormats.Elements().ToDictionary(k => dfCount++); var sheets = dSpreadsheet.WorkbookPart.Workbook.Sheets; Int32 position = 0; foreach (Sheet dSheet in sheets.OfType()) @@ -183,6 +184,8 @@ LoadSheetProtection((SheetProtection)reader.LoadCurrentElement(), ws); else if (reader.ElementType == typeof(DataValidations)) LoadDataValidations((DataValidations)reader.LoadCurrentElement(), ws); + else if (reader.ElementType == typeof(ConditionalFormatting)) + LoadConditionalFormatting((ConditionalFormatting)reader.LoadCurrentElement(), ws, differentialFormats); else if (reader.ElementType == typeof(Hyperlinks)) LoadHyperlinks((Hyperlinks)reader.LoadCurrentElement(), wsPart, ws); else if (reader.ElementType == typeof(PrintOptions)) @@ -851,8 +854,66 @@ } } + private void LoadNumberFormat(NumberingFormat nfSource, IXLNumberFormat nf) + { + if (nfSource == null) return; + + if (nfSource.FormatCode != null) + nf.Format = nfSource.FormatCode.Value; + //if (nfSource.NumberFormatId != null) + // nf.NumberFormatId = (Int32)nfSource.NumberFormatId.Value; + } + + private void LoadBorder(Border borderSource, IXLBorder border) + { + if (borderSource == null) return; + + LoadBorderValues(borderSource.DiagonalBorder, border.SetDiagonalBorder, border.SetDiagonalBorderColor); + + if (borderSource.DiagonalUp != null ) + border.DiagonalUp = borderSource.DiagonalUp.Value; + if (borderSource.DiagonalDown != null) + border.DiagonalDown = borderSource.DiagonalDown.Value; + + LoadBorderValues(borderSource.LeftBorder, border.SetLeftBorder, border.SetLeftBorderColor); + LoadBorderValues(borderSource.RightBorder, border.SetRightBorder, border.SetRightBorderColor); + LoadBorderValues(borderSource.TopBorder, border.SetTopBorder, border.SetTopBorderColor); + LoadBorderValues(borderSource.BottomBorder, border.SetBottomBorder, border.SetBottomBorderColor); + + } + + private void LoadBorderValues(BorderPropertiesType source, Func setBorder, Func setColor ) + { + if (source != null) + { + if (source.Style != null) + setBorder(source.Style.Value.ToClosedXml()); + if (source.Color != null) + setColor(GetColor(source.Color)); + } + } + + + + private void LoadFill(Fill fillSource, IXLFill fill) + { + if (fillSource == null) return; + + if(fillSource.PatternFill != null) + { + if (fillSource.PatternFill.PatternType != null) + fill.PatternType = fillSource.PatternFill.PatternType.Value.ToClosedXml(); + if (fillSource.PatternFill.ForegroundColor != null) + fill.PatternColor = GetColor(fillSource.PatternFill.ForegroundColor); + if (fillSource.PatternFill.BackgroundColor != null) + fill.PatternBackgroundColor = GetColor(fillSource.PatternFill.BackgroundColor); + } + } + private void LoadFont(OpenXmlElement fontSource, IXLFontBase fontBase) { + if (fontSource == null) return; + fontBase.Bold = GetBoolean(fontSource.Elements().FirstOrDefault()); var fontColor = GetColor(fontSource.Elements().FirstOrDefault()); if (fontColor.HasValue) @@ -1211,6 +1272,63 @@ } } + private void LoadConditionalFormatting(ConditionalFormatting conditionalFormatting, XLWorksheet ws, Dictionary differentialFormats) + { + if (conditionalFormatting == null) return; + + foreach (var sor in conditionalFormatting.SequenceOfReferences.Items) + { + var conditionalFormat = new XLConditionalFormat(ws.Range(sor.Value)); + foreach (var fr in conditionalFormatting.Elements()) + { + if (fr.FormatId != null) + { + LoadFont(differentialFormats[(Int32) fr.FormatId.Value].Font, conditionalFormat.Style.Font); + LoadFill(differentialFormats[(Int32) fr.FormatId.Value].Fill, conditionalFormat.Style.Fill); + LoadBorder(differentialFormats[(Int32) fr.FormatId.Value].Border, conditionalFormat.Style.Border); + LoadNumberFormat(differentialFormats[(Int32) fr.FormatId.Value].NumberingFormat, conditionalFormat.Style.NumberFormat); + } + if (fr.Operator != null) + conditionalFormat.Operator = fr.Operator.Value.ToClosedXml(); + if (fr.Type != null) + conditionalFormat.ConditionalFormatType = fr.Type.Value.ToClosedXml(); + if (fr.Text != null) + conditionalFormat.Values.Add(fr.Text.Value); + + + if (fr.Elements().Any()) + { + var colorScale = fr.Elements().First(); + foreach (var c in colorScale.Elements()) + { + conditionalFormat.ContentTypes.Add(c.Type.Value.ToClosedXml() ); + conditionalFormat.Values.Add(c.Val.Value); + } + foreach (var c in colorScale.Elements()) + { + conditionalFormat.Colors.Add(GetColor(c)); + } + } + else + { + foreach (var formula in fr.Elements()) + { + if (formula.Text != null) + { + String val = formula.Text; + if (val.StartsWith("\"")) val = val.Substring(1, val.Length - 2); + conditionalFormat.Values.Add(val); + } + } + } + + } + + ws.ConditionalFormats.Add(conditionalFormat); + } + + } + private static void LoadHyperlinks(Hyperlinks hyperlinks, WorksheetPart worksheetPart, XLWorksheet ws) { var hyperlinkDictionary = new Dictionary(); diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs index 9bfb567..059eb66 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs @@ -1824,6 +1824,8 @@ { if (workbookStylesPart.Stylesheet.DifferentialFormats == null) workbookStylesPart.Stylesheet.DifferentialFormats = new DifferentialFormats(); + else + workbookStylesPart.Stylesheet.DifferentialFormats.RemoveAllChildren(); var differentialFormats = workbookStylesPart.Stylesheet.DifferentialFormats; @@ -3090,6 +3092,7 @@ } else { + worksheetPart.Worksheet.RemoveAllChildren(); var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.ConditionalFormatting); diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/ConditionalFormatting/ConditionalFormatting.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/ConditionalFormatting/ConditionalFormatting.cs index a8f1322..b001f50 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/ConditionalFormatting/ConditionalFormatting.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/ConditionalFormatting/ConditionalFormatting.cs @@ -489,4 +489,50 @@ workbook.SaveAs(filePath); } } + + public class CFTwoConditions : 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().IconSet(XLIconSetStyle.ThreeTrafficLights2, true, true) + .AddValue(XLCFIconSetOperator.EqualOrGreaterThan, "0", XLCFContentType.Number) + .AddValue(XLCFIconSetOperator.EqualOrGreaterThan, "2", XLCFContentType.Number) + .AddValue(XLCFIconSetOperator.EqualOrGreaterThan, "3", XLCFContentType.Number); + + ws.RangeUsed().AddConditionalFormat().WhenContains("1") + .Fill.SetBackgroundColor(XLColor.Red); + + workbook.SaveAs(filePath); + } + } + + public class CFTest : 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().ColorScale() + .LowestValue(XLColor.Red) + .Midpoint(XLCFContentType.Percent, "50", XLColor.Yellow) + .HighestValue(XLColor.Green); + + + workbook.SaveAs(filePath); + } + } }