C# code of the ConditionalFormatting demo
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.