Пример #1
0
        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);
        }
Пример #3
0
        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;
        }
Пример #5
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());
        }
        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);
        }
Пример #8
0
        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;
        }
Пример #11
0
        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);
        }
Пример #12
0
        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);
        }
Пример #13
0
        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);
        }
Пример #14
0
        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;
        }
Пример #16
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);
        }
Пример #17
0
        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);
        }