ExpertXls Excel Library Blazor demo · .NET 10 and .NET 11

← All demos

C# code of the ConditionalFormatting demo

Features/ConditionalFormattingDemo.cs
using System;
using System.Drawing;
using System.IO;
using ExpertXls.ExcelLib;

namespace ExcelLibraryDemos
{
    /// <summary>
    /// Adds conditional formatting to a sales table: cell value rules and a formula rule that change the font and
    /// fill of the cells that meet a condition, a color scale, data bars and an icon set. Excel evaluates the rules
    /// when the workbook is displayed, so the formatting follows the values when they are changed. In the Excel
    /// 2007+ format the demo also adds sparklines, small charts drawn in cells.
    /// </summary>
    public static class ConditionalFormattingDemo
    {
        /// <summary>The name of the generated file, without extension.</summary>
        public const string OutputName = "ConditionalFormatting";

        /// <summary>
        /// Creates the workbook in the given format and saves it to the output stream.
        /// </summary>
        /// <param name="format">Excel 97-2003 (.xls) or Excel 2007+ (.xlsx).</param>
        /// <param name="inputFolder">The folder that contains the Images folder of the sample.</param>
        /// <param name="output">The stream that receives the workbook.</param>
        public static void CreateWorkbook(ExcelWorkbookFormat format, string inputFolder, Stream output)
        {
            ExcelWorkbook workbook = new ExcelWorkbook(format);
            // workbook.LicenseKey = "your license key";
            workbook.DocumentProperties.Subject = "Conditional formatting";
            workbook.DocumentProperties.Comments = "Created with ExpertXls Excel Library for .NET";

            Image logo = Image.FromFile(Path.Combine(inputFolder, "Images", "logo.jpg"));
            try
            {
                ExcelCellStyle titleStyle = DemoLayout.AddTitleStyle(workbook);
                ExcelCellStyle sectionStyle = DemoLayout.AddSectionStyle(workbook);

                // the first worksheet: rules that format the cells meeting a condition
                ExcelWorksheet rules = workbook.Worksheets[0];
                rules.Name = "Rules";
                DemoLayout.SetupPage(rules, logo, ExcelPageOrientation.Landscape);
                DemoLayout.WriteTitle(rules, "A2:G3", "Conditional Formatting Rules", titleStyle);
                DemoLayout.WriteSection(rules, "A5:G5", "Green: quarters over 1,000. Red: quarters under 300. Bold blue: a total over 4,000.", sectionStyle);
                DemoLayout.WriteSalesTable(rules, 6, 1);

                // the quarter values greater than 1000 and less than 300
                ExcelRange quarters = rules["C7:F18"];
                ExcelConditionalFormat high = quarters.ConditionalFormats.AddCellValueRule(ExcelConditionalFormatOperator.GreaterThan, "1000");
                high.BackColor = Color.FromArgb(198, 239, 206);
                high.FontColor = Color.FromArgb(0, 97, 0);
                high.Bold = true;
                ExcelConditionalFormat low = quarters.ConditionalFormats.AddCellValueRule(ExcelConditionalFormatOperator.LessThan, "300");
                low.BackColor = Color.FromArgb(255, 199, 206);
                low.FontColor = Color.FromArgb(156, 0, 6);

                // a formula rule: the product name is highlighted when the total of its row is over 4000;
                // the relative reference $G7 is evaluated for each row of the range
                ExcelConditionalFormat topProducts = rules["A7:A18"].ConditionalFormats.AddFormulaRule("$G7>4000");
                topProducts.FontColor = Color.FromArgb(0, 51, 153);
                topProducts.Bold = true;

                // the totals between two values
                ExcelConditionalFormat middle = rules["G7:G18"].ConditionalFormats.AddCellValueRule(
                    ExcelConditionalFormatOperator.Between, "2000", "3000");
                middle.BackColor = Color.FromArgb(255, 235, 156);
                middle.Italic = true;

                // the second worksheet: color scales, data bars and icon sets visualize the values
                ExcelWorksheet visuals = workbook.Worksheets.AddWorksheet("Visualizations");
                DemoLayout.SetupPage(visuals, logo, ExcelPageOrientation.Landscape);
                DemoLayout.WriteTitle(visuals, "A2:I3", "Color Scales, Data Bars and Icon Sets", titleStyle);
                DemoLayout.WriteSection(visuals, "A5:I5", "Color scale on the quarters, data bars on the totals, icons on the growth from Q1 to Q4.", sectionStyle);
                DemoLayout.WriteSalesTable(visuals, 6, 1);

                // the growth from Q1 to Q4 of each product, displayed as a percent
                visuals["H6"].Text = "Growth";
                visuals["H6"].Style.Font.Bold = true;
                for (int row = 7; row <= 18; row++)
                    visuals[row, 8].Formula = "=(F" + row + "-C" + row + ")/C" + row;
                visuals["H7:H18"].Style.Number.NumberFormatString = "0.0%";

                // a three color scale: red for the lowest values, yellow in the middle, green for the highest
                visuals["C7:F18"].ConditionalFormats.AddColorScale(Color.FromArgb(248, 105, 107), Color.FromArgb(255, 235, 132), Color.FromArgb(99, 190, 123));
                // data bars proportional to the totals
                visuals["G7:G18"].ConditionalFormats.AddDataBar(Color.FromArgb(99, 142, 198));
                // three arrows: up for the highest growth, sideways, down for the lowest
                visuals["H7:H18"].ConditionalFormats.AddIconSet(ExcelConditionalFormatIconSet.ThreeArrows);

                visuals["I6"].Text = "Trend";
                visuals["I6"].Style.Font.Bold = true;
                visuals["I6"].ColumnWidthInChars = 16;
                if (format == ExcelWorkbookFormat.Xlsx_2007)
                {
                    // a line sparkline in each cell of column I, drawn from the four quarters of the row
                    ExcelSparklineGroup trend = visuals.SparklineGroups.Add(ExcelSparklineType.Line, visuals["C7:F18"], visuals["I7:I18"]);
                    trend.ShowMarkers = true;
                    trend.ShowHighPoint = true;
                    trend.HighPointColor = Color.FromArgb(0, 176, 80);
                    // a column sparkline under each quarter, drawn from the 12 products
                    visuals["B19"].Text = "By product";
                    visuals.SparklineGroups.Add(ExcelSparklineType.Column, visuals["C7:F18"], visuals["C19:F19"]);
                    visuals["A19"].RowHeightInPoints = 30;
                }
                else
                {
                    visuals["I7"].Text = "Sparklines need the Excel 2007+ (.xlsx) format";
                    visuals["I7"].Style.Font.Italic = true;
                }

                workbook.Save(output);
            }
            finally
            {
                workbook.Close();
                logo.Dispose();
            }
        }
    }
}

Create, edit and read Excel workbooks in a Blazor application: the workbooks are created on the server and downloaded by the browser. The code of each workbook is in the Features folder.