public ConditionalFormattingRule Convert(IXLConditionalFormat cf, int priority, XLWorkbook.SaveContext context)
        {
            String val = GetQuoted(cf.Values[1]);


            var conditionalFormattingRule = new ConditionalFormattingRule {
                FormatId = (UInt32)context.DifferentialFormats[cf.Style], Operator = cf.Operator.ToOpenXml(), Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority
            };

            var formula = new Formula();

            if (cf.Operator == XLCFOperator.Equal || cf.Operator == XLCFOperator.NotEqual)
            {
                formula.Text = val;
            }
            else
            {
                formula.Text = val;
            }
            conditionalFormattingRule.Append(formula);

            if (cf.Operator == XLCFOperator.Between || cf.Operator == XLCFOperator.NotBetween)
            {
                var formula2 = new Formula {
                    Text = GetQuoted(cf.Values[2])
                };
                conditionalFormattingRule.Append(formula2);
            }

            return(conditionalFormattingRule);
        }
        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);
        }
Example #3
0
        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());
        }
        public ConditionalFormattingRule Convert(IXLConditionalFormat cf, int priority, XLWorkbook.SaveContext context)
        {
            var conditionalFormattingRule = new ConditionalFormattingRule { FormatId = (UInt32)context.DifferentialFormats[cf.Style], Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority };

            var formula = new Formula { Text = "NOT(ISERROR(" + cf.Range.RangeAddress.FirstAddress.ToStringRelative(false) + "))" };

            conditionalFormattingRule.Append(formula);

            return conditionalFormattingRule;
        }
        public ConditionalFormattingRule Convert(IXLConditionalFormat cf, int priority, XLWorkbook.SaveContext context)
        {
            String val = cf.Values[1].Value;
            var conditionalFormattingRule = new ConditionalFormattingRule { FormatId = (UInt32)context.DifferentialFormats[cf.Style], Operator = cf.Operator.ToOpenXml(), Text = val, Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority };

            var formula = new Formula { Text = "RIGHT(" + cf.Range.RangeAddress.FirstAddress.ToStringRelative(false) + "," + val.Length.ToString() + ")=\"" + val + "\"" };

            conditionalFormattingRule.Append(formula);

            return conditionalFormattingRule;
        }
Example #6
0
        public ConditionalFormattingRule Convert(IXLConditionalFormat cf, int priority, XLWorkbook.SaveContext context)
        {
            String val = GetQuoted(cf.Values[1]);

            var conditionalFormattingRule = new ConditionalFormattingRule { FormatId = (UInt32)context.DifferentialFormats[cf.Style], Operator = cf.Operator.ToOpenXml(), Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority };

            var formula = new Formula();
            if (cf.Operator == XLCFOperator.Equal || cf.Operator == XLCFOperator.NotEqual)
                formula.Text = val;
            else
                formula.Text = val;
            conditionalFormattingRule.Append(formula);

            if(cf.Operator == XLCFOperator.Between || cf.Operator == XLCFOperator.NotBetween)
            {
                var formula2 = new Formula { Text = GetQuoted(cf.Values[2]) };
                conditionalFormattingRule.Append(formula2);
            }

            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.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);
        }
        public ConditionalFormattingRule Convert(IXLConditionalFormat cf, int priority, XLWorkbook.SaveContext context)
        {
            var conditionalFormattingRule = new ConditionalFormattingRule {
                FormatId = (UInt32)context.DifferentialFormats[cf.Style], Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority
            };

            var formula = new Formula {
                Text = "NOT(ISERROR(" + cf.Range.RangeAddress.FirstAddress.ToStringRelative(false) + "))"
            };

            conditionalFormattingRule.Append(formula);

            return(conditionalFormattingRule);
        }
        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;
        }
Example #10
0
        public ConditionalFormattingRule Convert(IXLConditionalFormat cf, int priority, XLWorkbook.SaveContext context)
        {
            String val = cf.Values[1].Value;
            var    conditionalFormattingRule = new ConditionalFormattingRule {
                FormatId = (UInt32)context.DifferentialFormats[cf.Style], Operator = ConditionalFormattingOperatorValues.EndsWith, Text = val, Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority
            };

            var formula = new Formula {
                Text = "RIGHT(" + cf.Range.RangeAddress.FirstAddress.ToStringRelative(false) + "," + val.Length.ToString() + ")=\"" + val + "\""
            };

            conditionalFormattingRule.Append(formula);

            return(conditionalFormattingRule);
        }
Example #11
0
        public void SetCondition(string range, string opr, string formula, int priority, int style)
        {
            ConditionalFormatting conditionalFormatting = this.part.Worksheet.Elements <ConditionalFormatting>().FirstOrDefault();

            if (conditionalFormatting == null)
            {
                conditionalFormatting = new ConditionalFormatting();
                conditionalFormatting.SequenceOfReferences = new ListValue <StringValue>();

                this.part.Worksheet.Append(conditionalFormatting);
            }

            conditionalFormatting.SequenceOfReferences.Items.Add(new StringValue()
            {
                InnerText = range
            });

            ConditionalFormattingOperatorValues mopr = ConditionalFormattingOperatorValues.Equal;

            if (opr == "<")
            {
                mopr = ConditionalFormattingOperatorValues.LessThan;
            }
            if (opr == "<=")
            {
                mopr = ConditionalFormattingOperatorValues.LessThanOrEqual;
            }
            if (opr == ">")
            {
                mopr = ConditionalFormattingOperatorValues.GreaterThan;
            }
            if (opr == ">=")
            {
                mopr = ConditionalFormattingOperatorValues.GreaterThanOrEqual;
            }

            ConditionalFormattingRule rule = new ConditionalFormattingRule()
            {
                Type     = ConditionalFormatValues.CellIs,
                FormatId = (UInt32)style,
                Priority = priority,
                Operator = mopr
            };

            rule.Append(new Formula(formula));

            conditionalFormatting.Append(rule);
        }
        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);
        }
        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;
        }
Example #15
0
        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);
        }
        private void AddConditionalFormatting(Worksheet ws, int index)
        {
            var conditionalFormatting1 =
                new ConditionalFormatting {
                SequenceOfReferences = new ListValue <StringValue> {
                    InnerText = formatReference
                }
            };
            var conditionalFormattingRule1 =
                new ConditionalFormattingRule {
                Type = ConditionalFormatValues.Expression, FormatId = (uint)index, Priority = 1
            };
            var formula1 = new Formula {
                Text = formatFormula
            };

            conditionalFormattingRule1.Append(formula1);
            conditionalFormatting1.Append(conditionalFormattingRule1);
            var pp = ws.Descendants <PhoneticProperties>()
                     .FirstOrDefault();

            ws.InsertAfter(conditionalFormatting1, pp);
        }
Example #17
0
        public void AppendConditionalFormatting(String[] sequenceOfReferences, UInt32 formatId, String formula)
        {
            ConditionalFormatting conditionalFormatting = new ConditionalFormatting()
            {
                SequenceOfReferences = new ListValue <StringValue>()
                {
                    InnerText = sequenceOfReferences[0]  // пока только первая стока
                }
            };
            ConditionalFormattingRule conditionalFormattingRule = new ConditionalFormattingRule()
            {
                Type     = ConditionalFormatValues.Expression,
                FormatId = formatId,
                Priority = 1
            };

            conditionalFormattingRule.Append(new Formula()
            {
                Text = formula
            });
            conditionalFormatting.Append(conditionalFormattingRule);
            worksheet.Append(conditionalFormatting);
        }
        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.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 ConditionalFormattingRule ToConditionalFormattingRule()
        {
            var cfr = new ConditionalFormattingRule();

            cfr.Type = Type;
            if (FormatId != null)
            {
                cfr.FormatId = FormatId.Value;
            }
            cfr.Priority = Priority;
            if (StopIfTrue)
            {
                cfr.StopIfTrue = StopIfTrue;
            }
            if (!AboveAverage)
            {
                cfr.AboveAverage = AboveAverage;
            }
            if (Percent)
            {
                cfr.Percent = Percent;
            }
            if (Bottom)
            {
                cfr.Bottom = Bottom;
            }
            if (HasOperator)
            {
                cfr.Operator = Operator;
            }
            if ((Text != null) && (Text.Length > 0))
            {
                cfr.Text = Text;
            }
            if (HasTimePeriod)
            {
                cfr.TimePeriod = TimePeriod;
            }
            if (Rank != null)
            {
                cfr.Rank = Rank.Value;
            }
            if (StdDev != null)
            {
                cfr.StdDev = StdDev.Value;
            }
            if (EqualAverage)
            {
                cfr.EqualAverage = EqualAverage;
            }

            foreach (var f in Formulas)
            {
                cfr.Append((Formula)f.CloneNode(true));
            }
            if (HasColorScale)
            {
                cfr.Append(ColorScale.ToColorScale());
            }
            if (HasDataBar)
            {
                cfr.Append(DataBar.ToDataBar());
            }
            if (HasIconSet)
            {
                cfr.Append(IconSet.ToIconSet());
            }

            if (Extensions.Count > 0)
            {
                var extlist = new ConditionalFormattingRuleExtensionList();
                foreach (var ext in Extensions)
                {
                    extlist.Append((ConditionalFormattingRuleExtension)ext.CloneNode(true));
                }
                cfr.Append(extlist);
            }

            return(cfr);
        }
        public ConditionalFormattingRule Convert(IXLConditionalFormat cf, XLWorkbook.SaveContext context)
        {
            ConditionalFormattingRule conditionalFormattingRule = new ConditionalFormattingRule()
            {
                Type = DocumentFormat.OpenXml.Spreadsheet.ConditionalFormatValues.DataBar,
                Id   = (cf as XLConditionalFormat).Id.WrapInBraces()
            };

            DataBar dataBar = new DataBar()
            {
                MinLength = 0,
                MaxLength = 100,
                Gradient  = true,
                ShowValue = !cf.ShowBarOnly
            };

            var cfMinType = cf.ContentTypes.TryGetValue(1, out var contentType1)
                ? Convert(contentType1.ToOpenXml())
                : ConditionalFormattingValueObjectTypeValues.AutoMin;
            var cfMin = new ConditionalFormattingValueObject {
                Type = cfMinType
            };

            if (cf.Values.Any() && cf.Values[1]?.Value != null)
            {
                cfMin.Type = ConditionalFormattingValueObjectTypeValues.Numeric;
                cfMin.Append(new Formula()
                {
                    Text = cf.Values[1].Value
                });
            }

            var cfMaxType = cf.ContentTypes.TryGetValue(2, out var contentType2)
                ? Convert(contentType2.ToOpenXml())
                : ConditionalFormattingValueObjectTypeValues.AutoMax;
            var cfMax = new ConditionalFormattingValueObject {
                Type = cfMaxType
            };

            if (cf.Values.Count >= 2 && cf.Values[2]?.Value != null)
            {
                cfMax.Type = ConditionalFormattingValueObjectTypeValues.Numeric;
                cfMax.Append(new Formula()
                {
                    Text = cf.Values[2].Value
                });
            }

            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);
        }
Example #21
0
        // Generates content of worksheetPart1.
        private void GenerateWorksheetPart1Content(WorksheetPart worksheetPart1)
        {
            Worksheet worksheet1 = new Worksheet();
            worksheet1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
            SheetDimension sheetDimension1 = new SheetDimension() { Reference = "A1:A2" };

            SheetViews sheetViews1 = new SheetViews();

            SheetView sheetView1 = new SheetView() { TabSelected = true, WorkbookViewId = (UInt32Value)0U };
            Selection selection1 = new Selection() { ActiveCell = "A2", SequenceOfReferences = new ListValue<StringValue>() { InnerText = "A2" } };

            sheetView1.Append(selection1);

            sheetViews1.Append(sheetView1);
            SheetFormatProperties sheetFormatProperties1 = new SheetFormatProperties() { DefaultRowHeight = 15D };

            SheetData sheetData1 = new SheetData();

            Row row1 = new Row() { RowIndex = (UInt32Value)1U, Spans = new ListValue<StringValue>() { InnerText = "1:1" } };

            Cell cell1 = new Cell() { CellReference = "A1" };
            CellValue cellValue1 = new CellValue();
            cellValue1.Text = "1";

            cell1.Append(cellValue1);

            row1.Append(cell1);

            Row row2 = new Row() { RowIndex = (UInt32Value)2U, Spans = new ListValue<StringValue>() { InnerText = "1:1" } };

            Cell cell2 = new Cell() { CellReference = "A2" };
            CellValue cellValue2 = new CellValue();
            cellValue2.Text = "-1";

            cell2.Append(cellValue2);

            row2.Append(cell2);

            sheetData1.Append(row1);
            sheetData1.Append(row2);

            ConditionalFormatting conditionalFormatting1 = new ConditionalFormatting() { SequenceOfReferences = new ListValue<StringValue>() { InnerText = "A1" } };

            ConditionalFormattingRule conditionalFormattingRule1 = new ConditionalFormattingRule() { Type = ConditionalFormatValues.CellIs, FormatId = (UInt32Value)2U, Priority = 2, Operator = ConditionalFormattingOperatorValues.GreaterThan };
            Formula formula1 = new Formula();
            formula1.Text = "0";

            conditionalFormattingRule1.Append(formula1);

            conditionalFormatting1.Append(conditionalFormattingRule1);

            ConditionalFormatting conditionalFormatting2 = new ConditionalFormatting() { SequenceOfReferences = new ListValue<StringValue>() { InnerText = "A2" } };

            ConditionalFormattingRule conditionalFormattingRule2 = new ConditionalFormattingRule() { Type = ConditionalFormatValues.CellIs, FormatId = (UInt32Value)0U, Priority = 1, Operator = ConditionalFormattingOperatorValues.LessThan };
            Formula formula2 = new Formula();
            formula2.Text = "0";

            conditionalFormattingRule2.Append(formula2);

            conditionalFormatting2.Append(conditionalFormattingRule2);
            PageMargins pageMargins1 = new PageMargins() { Left = 0.7D, Right = 0.7D, Top = 0.75D, Bottom = 0.75D, Header = 0.3D, Footer = 0.3D };

            worksheet1.Append(sheetDimension1);
            worksheet1.Append(sheetViews1);
            worksheet1.Append(sheetFormatProperties1);
            worksheet1.Append(sheetData1);
            worksheet1.Append(conditionalFormatting1);
            worksheet1.Append(conditionalFormatting2);
            worksheet1.Append(pageMargins1);

            worksheetPart1.Worksheet = worksheet1;
        }
        internal ConditionalFormattingRule ToConditionalFormattingRule()
        {
            ConditionalFormattingRule cfr = new ConditionalFormattingRule();

            cfr.Type = this.Type;
            if (this.FormatId != null)
            {
                cfr.FormatId = this.FormatId.Value;
            }
            cfr.Priority = this.Priority;
            if (this.StopIfTrue)
            {
                cfr.StopIfTrue = this.StopIfTrue;
            }
            if (!this.AboveAverage)
            {
                cfr.AboveAverage = this.AboveAverage;
            }
            if (this.Percent)
            {
                cfr.Percent = this.Percent;
            }
            if (this.Bottom)
            {
                cfr.Bottom = this.Bottom;
            }
            if (HasOperator)
            {
                cfr.Operator = this.Operator;
            }
            if (this.Text != null && this.Text.Length > 0)
            {
                cfr.Text = this.Text;
            }
            if (HasTimePeriod)
            {
                cfr.TimePeriod = this.TimePeriod;
            }
            if (this.Rank != null)
            {
                cfr.Rank = this.Rank.Value;
            }
            if (this.StdDev != null)
            {
                cfr.StdDev = this.StdDev.Value;
            }
            if (this.EqualAverage)
            {
                cfr.EqualAverage = this.EqualAverage;
            }

            foreach (Formula f in this.Formulas)
            {
                cfr.Append((Formula)f.CloneNode(true));
            }
            if (HasColorScale)
            {
                cfr.Append(this.ColorScale.ToColorScale());
            }
            if (HasDataBar)
            {
                cfr.Append(this.DataBar.ToDataBar());
            }
            if (HasIconSet)
            {
                cfr.Append(this.IconSet.ToIconSet());
            }

            if (this.Extensions.Count > 0)
            {
                ConditionalFormattingRuleExtensionList extlist = new ConditionalFormattingRuleExtensionList();
                foreach (ConditionalFormattingRuleExtension ext in this.Extensions)
                {
                    extlist.Append((ConditionalFormattingRuleExtension)ext.CloneNode(true));
                }
                cfr.Append(extlist);
            }

            return(cfr);
        }
Example #23
0
        private void AddConditionalFormatting()
        {
            //use openxml directly to apply conditional formatting.
            using (SpreadsheetDocument report = SpreadsheetDocument.Open(fileName, true))
            {
                Workbook workbook = report.WorkbookPart.Workbook;
                WorksheetPart worksheetPart = workbook.WorkbookPart.WorksheetParts.First();

                DifferentialFormats differentialFormats = new DifferentialFormats() { Count = (UInt32Value)2U };

                DifferentialFormat lessThanFormat = new DifferentialFormat();
                Font lessThanFont = new Font();
                lessThanFont.Append(new Condense() { Val = false });
                lessThanFont.Append(new Extend() { Val = false });
                lessThanFont.Append(new Color() { Rgb = "FF9C0006" });
                Fill lessThanFill = new Fill();
                PatternFill lessThanPatternFill = new PatternFill();
                lessThanPatternFill.Append(new BackgroundColor() { Rgb = "FFFFC7CE" });
                lessThanFill.Append(lessThanPatternFill);
                lessThanFormat.Append(lessThanFont);
                lessThanFormat.Append(lessThanFill);

                DifferentialFormat greaterThanFormat = new DifferentialFormat();
                Font greaterThanFont = new Font();
                greaterThanFont.Append(new Condense() { Val = false });
                greaterThanFont.Append(new Extend() { Val = false });
                greaterThanFont.Append(new Color() { Rgb = "FF006100" });
                Fill greatherThanFill = new Fill();
                PatternFill greaterThanPatternFill = new PatternFill();
                greaterThanPatternFill.Append(new BackgroundColor() { Rgb = "FFC6EFCE" });
                greatherThanFill.Append(greaterThanPatternFill);
                greaterThanFormat.Append(greaterThanFont);
                greaterThanFormat.Append(greatherThanFill);

                differentialFormats.Append(lessThanFormat);
                differentialFormats.Append(greaterThanFormat);
                workbook.WorkbookPart.WorkbookStylesPart.Stylesheet.Append(differentialFormats);

                ConditionalFormatting conditionalFormatting = new ConditionalFormatting() {
                    SequenceOfReferences = new ListValue<StringValue>() { InnerText = "D2:D10" }
                };

                ConditionalFormattingRule greaterThanRule = new ConditionalFormattingRule()
                {
                    Type = ConditionalFormatValues.CellIs,
                    FormatId = (UInt32Value)1U,
                    Priority = 2,
                    Operator = ConditionalFormattingOperatorValues.GreaterThan
                };
                Formula greaterThanFormula = new Formula();
                greaterThanFormula.Text = "0";
                greaterThanRule.Append(greaterThanFormula);

                ConditionalFormattingRule lessThanRule = new ConditionalFormattingRule()
                {
                    Type = ConditionalFormatValues.CellIs,
                    FormatId = (UInt32Value)0U,
                    Priority = 1,
                    Operator = ConditionalFormattingOperatorValues.LessThan
                };
                Formula lessThanFormula = new Formula();
                lessThanFormula.Text = "0";
                lessThanRule.Append(lessThanFormula);

                conditionalFormatting.Append(greaterThanRule);
                conditionalFormatting.Append(lessThanRule);

                worksheetPart.Worksheet.Append(conditionalFormatting);

                report.WorkbookPart.Workbook.Save();
                report.Close();
            }
        }
        internal ConditionalFormattingRule ToConditionalFormattingRule()
        {
            ConditionalFormattingRule cfr = new ConditionalFormattingRule();
            cfr.Type = this.Type;
            if (this.FormatId != null) cfr.FormatId = this.FormatId.Value;
            cfr.Priority = this.Priority;
            if (this.StopIfTrue) cfr.StopIfTrue = this.StopIfTrue;
            if (!this.AboveAverage) cfr.AboveAverage = this.AboveAverage;
            if (this.Percent) cfr.Percent = this.Percent;
            if (this.Bottom) cfr.Bottom = this.Bottom;
            if (HasOperator) cfr.Operator = this.Operator;
            if (this.Text != null && this.Text.Length > 0) cfr.Text = this.Text;
            if (HasTimePeriod) cfr.TimePeriod = this.TimePeriod;
            if (this.Rank != null) cfr.Rank = this.Rank.Value;
            if (this.StdDev != null) cfr.StdDev = this.StdDev.Value;
            if (this.EqualAverage) cfr.EqualAverage = this.EqualAverage;

            foreach (Formula f in this.Formulas)
            {
                cfr.Append((Formula)f.CloneNode(true));
            }
            if (HasColorScale) cfr.Append(this.ColorScale.ToColorScale());
            if (HasDataBar) cfr.Append(this.DataBar.ToDataBar());
            if (HasIconSet) cfr.Append(this.IconSet.ToIconSet());

            if (this.Extensions.Count > 0)
            {
                ConditionalFormattingRuleExtensionList extlist = new ConditionalFormattingRuleExtensionList();
                foreach (ConditionalFormattingRuleExtension ext in this.Extensions)
                {
                    extlist.Append((ConditionalFormattingRuleExtension)ext.CloneNode(true));
                }
                cfr.Append(extlist);
            }

            return cfr;
        }
Example #25
0
        static void Main(string[] args)
        {
            var workbookClosed = new XLWorkbook();

            var sheet = workbookClosed.Worksheets.Add("Sheet1");
            sheet.Cell(1, 1).Value = 1;
            sheet.Cell(2, 1).Value = -1;

            workbookClosed.SaveAs("closed.xlsx");

            using (SpreadsheetDocument report = SpreadsheetDocument.Open("closed.xlsx", true))
            {
                Workbook workbook = report.WorkbookPart.Workbook;
                WorksheetPart worksheetPart = workbook.WorkbookPart.WorksheetParts.First();

                DifferentialFormats differentialFormats = new DifferentialFormats() { Count = (UInt32Value)2U };

                DifferentialFormat lessThanFormat = new DifferentialFormat();
                Font lessThanFont = new Font();
                lessThanFont.Append(new Condense() { Val = false });
                lessThanFont.Append(new Extend() { Val = false });
                lessThanFont.Append(new Color() { Rgb = "FF9C0006" });
                Fill lessThanFill = new Fill();
                PatternFill lessThanPatternFill = new PatternFill();
                lessThanPatternFill.Append(new BackgroundColor() { Rgb = "FFFFC7CE" });
                lessThanFill.Append(lessThanPatternFill);
                lessThanFormat.Append(lessThanFont);
                lessThanFormat.Append(lessThanFill);

                DifferentialFormat greaterThanFormat = new DifferentialFormat();
                Font greaterThanFont = new Font();
                greaterThanFont.Append(new Condense() { Val = false });
                greaterThanFont.Append(new Extend() { Val = false });
                greaterThanFont.Append(new Color() { Rgb = "FF006100" });
                Fill greatherThanFill = new Fill();
                PatternFill greaterThanPatternFill = new PatternFill();
                greaterThanPatternFill.Append(new BackgroundColor() { Rgb = "FFC6EFCE" });
                greatherThanFill.Append(greaterThanPatternFill);
                greaterThanFormat.Append(greaterThanFont);
                greaterThanFormat.Append(greatherThanFill);

                differentialFormats.Append(lessThanFormat);
                differentialFormats.Append(greaterThanFormat);
                workbook.WorkbookPart.WorkbookStylesPart.Stylesheet.Append(differentialFormats);

                ConditionalFormatting conditionalFormatting = new ConditionalFormatting()
                {
                    SequenceOfReferences = new ListValue<StringValue>() { InnerText = "D2:D10" }
                };

                ConditionalFormattingRule greaterThanRule = new ConditionalFormattingRule()
                {
                    Type = ConditionalFormatValues.CellIs,
                    FormatId = (UInt32Value)1U,
                    Priority = 2,
                    Operator = ConditionalFormattingOperatorValues.GreaterThan
                };
                Formula greaterThanFormula = new Formula();
                greaterThanFormula.Text = "0";
                greaterThanRule.Append(greaterThanFormula);

                ConditionalFormattingRule lessThanRule = new ConditionalFormattingRule()
                {
                    Type = ConditionalFormatValues.CellIs,
                    FormatId = (UInt32Value)0U,
                    Priority = 1,
                    Operator = ConditionalFormattingOperatorValues.LessThan
                };
                Formula lessThanFormula = new Formula();
                lessThanFormula.Text = "0";
                lessThanRule.Append(lessThanFormula);

                conditionalFormatting.Append(greaterThanRule);
                conditionalFormatting.Append(lessThanRule);

                worksheetPart.Worksheet.PrependChild<ConditionalFormatting>(conditionalFormatting);

                report.WorkbookPart.Workbook.Save();
                report.Close();
            }
        }