Conditional Formatting Color Scale with OpenXML SDK

The example shows how to insert color scale conditional formatting with OpenXML SDK.

Output: ConditionalFormattingColorScale.xlsx

        public void ConditionalFormattingColorScale()
        {
            using (var spreadsheetDocument = SpreadsheetDocument.Create("ConditionalFormattingColorScale.xlsx",
                SpreadsheetDocumentType.Workbook))
            {
                var workbookPart = spreadsheetDocument.AddWorkbookPart();
                
                var worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
                var workbook = new Workbook();

                var worksheet = new Worksheet();
                worksheetPart.Worksheet = worksheet;

                AddDataConditionalFormattingColorScale(workbookPart, worksheetPart);

                var b1f1 = worksheet.AppendChild(new ConditionalFormatting());
                b1f1.SequenceOfReferences = new ListValue<StringValue>();
                b1f1.SequenceOfReferences.Items.Add("B1:F1");
                var cfRuleB1F1 = b1f1.AppendChild(new ConditionalFormattingRule());
                cfRuleB1F1.StdDev = 0;
                cfRuleB1F1.Priority = 0;
                cfRuleB1F1.Type = ConditionalFormatValues.ColorScale;
                var colorScaleB1F1 = cfRuleB1F1.AppendChild(new ColorScale());
                colorScaleB1F1.AppendChild(new ConditionalFormatValueObject()
                    {Type = ConditionalFormatValueObjectValues.Min, Val = "0"});
                colorScaleB1F1.AppendChild(new ConditionalFormatValueObject()
                    { Type = ConditionalFormatValueObjectValues.Max , Val = "0" });
                colorScaleB1F1.Append(new Color(){Rgb = "FF0000FF" });
                colorScaleB1F1.Append(new Color() { Rgb = "FFFFFF00" });

                var b2f2 = worksheet.AppendChild(new ConditionalFormatting());
                b2f2.SequenceOfReferences = new ListValue<StringValue>();
                b2f2.SequenceOfReferences.Items.Add("B2:F2");
                var cfRuleB2F2 = b1f1.AppendChild(new ConditionalFormattingRule());
                cfRuleB2F2.StdDev = 0;
                cfRuleB2F2.Priority = 0;
                cfRuleB2F2.Type = ConditionalFormatValues.ColorScale;
                var colorScaleB2F2 = cfRuleB1F1.AppendChild(new ColorScale());
                colorScaleB2F2.AppendChild(new ConditionalFormatValueObject()
                    { Type = ConditionalFormatValueObjectValues.Percent, Val = "20" });
                colorScaleB2F2.AppendChild(new ConditionalFormatValueObject()
                    { Type = ConditionalFormatValueObjectValues.Percent, Val = "80" });
                colorScaleB2F2.Append(new Color() { Rgb = "FF0000FF" });
                colorScaleB2F2.Append(new Color() { Rgb = "FFFFFF00" });

                var b3f3 = worksheet.AppendChild(new ConditionalFormatting());
                b3f3.SequenceOfReferences = new ListValue<StringValue>();
                b3f3.SequenceOfReferences.Items.Add("B3:F3");
                var cfRuleB3F3 = b1f1.AppendChild(new ConditionalFormattingRule());
                cfRuleB3F3.StdDev = 0;
                cfRuleB3F3.Priority = 0;
                cfRuleB3F3.Type = ConditionalFormatValues.ColorScale;
                var colorScaleB3F3 = cfRuleB1F1.AppendChild(new ColorScale());
                colorScaleB3F3.AppendChild(new ConditionalFormatValueObject()
                    { Type = ConditionalFormatValueObjectValues.Min, Val = "0" });
                colorScaleB3F3.AppendChild(new ConditionalFormatValueObject()
                    { Type = ConditionalFormatValueObjectValues.Percentile, Val = "50" });
                colorScaleB3F3.AppendChild(new ConditionalFormatValueObject()
                    { Type = ConditionalFormatValueObjectValues.Max, Val = "0" });
                colorScaleB3F3.Append(new Color() { Rgb = "FF0000FF" });
                colorScaleB3F3.Append(new Color() { Rgb = "FFFFFF00" });
                colorScaleB3F3.Append(new Color() { Rgb = "FF00FF00" });

                worksheetPart.Worksheet.Save();
                var sheets = new Sheets();
                var sheet = new Sheet();
                sheet.Name = "Sheet1";
                sheet.SheetId = 1;
                sheet.Id = workbookPart.GetIdOfPart(worksheetPart);
                sheets.Append(sheet);
                workbook.Append(sheets);

                spreadsheetDocument.WorkbookPart.Workbook = workbook;
                spreadsheetDocument.WorkbookPart.Workbook.Save();
                spreadsheetDocument.Close();
            }

        }

        internal void AddDataConditionalFormattingColorScale(WorkbookPart workbookPart, WorksheetPart worksheetPart)
        {
            SheetData sheetData = new SheetData();

            var sharedStringPart = workbookPart.AddNewPart<SharedStringTablePart>();
            sharedStringPart.SharedStringTable = new SharedStringTable();
            sharedStringPart.SharedStringTable.AppendChild(new SharedStringItem(new Text("Two Color Scale Blue Yellow Lowest Highest")));
            sharedStringPart.SharedStringTable.AppendChild(new SharedStringItem(new Text("Two Color Scale Blue Yellow 20%-80%")));
            sharedStringPart.SharedStringTable.AppendChild(new SharedStringItem(new Text("Three Color Scale Red Yellow Green")));
            

            var row1 = new Row() { RowIndex = 1 };
            var row2 = new Row() { RowIndex = 2 };
            var row3 = new Row() { RowIndex = 3 };
            
            sheetData.Append(row1);
            sheetData.Append(row2);
            sheetData.Append(row3);
            

            var cellA1 = new Cell() { CellReference = "A1", DataType = new EnumValue<CellValues>(CellValues.SharedString) };
            cellA1.CellValue = new CellValue("0");
            var cellA2 = new Cell() { CellReference = "A2", DataType = new EnumValue<CellValues>(CellValues.SharedString) };
            cellA2.CellValue = new CellValue("1");
            var cellA3 = new Cell() { CellReference = "A3", DataType = new EnumValue<CellValues>(CellValues.SharedString) };
            cellA3.CellValue = new CellValue("2");
            
            row1.Append(cellA1);
            row2.Append(cellA2);
            row3.Append(cellA3);
            
            row1.Append(new Cell() { CellReference = "B1", CellValue = new CellValue("1") });
            row1.Append(new Cell() { CellReference = "C1", CellValue = new CellValue("2") });
            row1.Append(new Cell() { CellReference = "D1", CellValue = new CellValue("3") });
            row1.Append(new Cell() { CellReference = "E1", CellValue = new CellValue("4") });
            row1.Append(new Cell() { CellReference = "F1", CellValue = new CellValue("5") });

            row2.Append(new Cell() { CellReference = "B2", CellValue = new CellValue("1") });
            row2.Append(new Cell() { CellReference = "C2", CellValue = new CellValue("2") });
            row2.Append(new Cell() { CellReference = "D2", CellValue = new CellValue("3") });
            row2.Append(new Cell() { CellReference = "E2", CellValue = new CellValue("4") });
            row2.Append(new Cell() { CellReference = "F2", CellValue = new CellValue("5") });

            row3.Append(new Cell() { CellReference = "B3", CellValue = new CellValue("1") });
            row3.Append(new Cell() { CellReference = "C3", CellValue = new CellValue("2") });
            row3.Append(new Cell() { CellReference = "D3", CellValue = new CellValue("3") });
            row3.Append(new Cell() { CellReference = "E3", CellValue = new CellValue("4") });
            row3.Append(new Cell() { CellReference = "F3", CellValue = new CellValue("5") });

            
            worksheetPart.Worksheet.Append(sheetData);
        }