Adding Conditional Formatting
Conditional formatting is a feature of Excel which allows you to apply a format to a cell or a range of cells based on user defined rules. For example you might apply rules like the following to highlight cells in different ranges.
The examples below show how to use the various types of conditional formatting
with rust_xlsxwriter.
Some examples:
Example 1. Cell conditional formatting. Cells with values >= 50 are in light red. Values < 50 are in light green.
See ConditionalFormatCell for more details.
Code to generate the above example:
// SPDX-License-Identifier: MIT OR Apache-2.0
//
// Copyright 2022-2024, John McNamara, jmcnamara@cpan.org
//! Example of how to add conditional formatting to a worksheet using the
//! rust_xlsxwriter library.
//!
//! Conditional formatting allows you to apply a format to a cell or a range of
//! cells based on user defined rule.
use rust_xlsxwriter::{
ConditionalFormat2ColorScale, ConditionalFormat3ColorScale, ConditionalFormatAverage,
ConditionalFormatAverageRule, ConditionalFormatCell, ConditionalFormatCellRule,
ConditionalFormatDataBar, ConditionalFormatDataBarDirection, ConditionalFormatDuplicate,
ConditionalFormatFormula, ConditionalFormatIconSet, ConditionalFormatIconType,
ConditionalFormatText, ConditionalFormatTextRule, ConditionalFormatTop, Format, Workbook,
XlsxError,
};
fn main() -> Result<(), XlsxError> {
// Create a new Excel file object.
let mut workbook = Workbook::new();
// Add a format. Light red fill with dark red text.
let format1 = Format::new()
.set_font_color("9C0006")
.set_background_color("FFC7CE");
// Add a format. Green fill with dark green text.
let format2 = Format::new()
.set_font_color("006100")
.set_background_color("C6EFCE");
// Add a format for headers.
let bold = Format::new().set_bold();
// Add a format for descriptions.
let indent = Format::new().set_indent(2);
// some sample data to run the conditional formatting against.
let data = [
[34, 72, 38, 30, 75, 48, 75, 66, 84, 86],
[6, 24, 1, 84, 54, 62, 60, 3, 26, 59],
[28, 79, 97, 13, 85, 93, 93, 22, 5, 14],
[27, 71, 40, 17, 18, 79, 90, 93, 29, 47],
[88, 25, 33, 23, 67, 1, 59, 79, 47, 36],
[24, 100, 20, 88, 29, 33, 38, 54, 54, 88],
[6, 57, 88, 28, 10, 26, 37, 7, 41, 48],
[52, 78, 1, 96, 26, 45, 47, 33, 96, 36],
[60, 54, 81, 66, 81, 90, 80, 93, 12, 55],
[70, 5, 46, 14, 71, 19, 66, 36, 41, 21],
];
// -----------------------------------------------------------------------
// Worksheet 1. Cell conditional formatting.
// -----------------------------------------------------------------------
let caption = "Cells with values >= 50 are in light red. Values < 50 are in light green.";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
worksheet.write_row_matrix(2, 1, data)?;
// Set the column widths for clarity.
for col_num in 1..=10u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write a conditional format over a range.
let conditional_format = ConditionalFormatCell::new()
.set_rule(ConditionalFormatCellRule::GreaterThanOrEqualTo(50))
.set_format(&format1);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// Write another conditional format over the same range.
let conditional_format = ConditionalFormatCell::new()
.set_rule(ConditionalFormatCellRule::LessThan(50))
.set_format(&format2);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 2. Cell conditional formatting with between ranges.
// -----------------------------------------------------------------------
let caption =
"Values between 30 and 70 are in light red. Values outside that range are in light green.";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
worksheet.write_row_matrix(2, 1, data)?;
// Set the column widths for clarity.
for col_num in 1..=10u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write a conditional format over a range.
let conditional_format = ConditionalFormatCell::new()
.set_rule(ConditionalFormatCellRule::Between(30, 70))
.set_format(&format1);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// Write another conditional format over the same range.
let conditional_format = ConditionalFormatCell::new()
.set_rule(ConditionalFormatCellRule::NotBetween(30, 70))
.set_format(&format2);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 3. Duplicate and Unique conditional formats.
// -----------------------------------------------------------------------
let caption = "Duplicate values are in light red. Unique values are in light green.";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
worksheet.write_row_matrix(2, 1, data)?;
// Set the column widths for clarity.
for col_num in 1..=10u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write a conditional format over a range.
let conditional_format = ConditionalFormatDuplicate::new().set_format(&format1);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// Invert the duplicate conditional format to show unique values in the
// same range.
let conditional_format = ConditionalFormatDuplicate::new()
.invert()
.set_format(&format2);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 4. Above and Below Average conditional formats.
// -----------------------------------------------------------------------
let caption = "Above average values are in light red. Below average values are in light green.";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
worksheet.write_row_matrix(2, 1, data)?;
// Set the column widths for clarity.
for col_num in 1..=10u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write a conditional format over a range. The default criteria is Above Average.
let conditional_format = ConditionalFormatAverage::new().set_format(&format1);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// Write another conditional format over the same range.
let conditional_format = ConditionalFormatAverage::new()
.set_rule(ConditionalFormatAverageRule::BelowAverage)
.set_format(&format2);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 5. Top and Bottom range conditional formats.
// -----------------------------------------------------------------------
let caption = "Top 10 values are in light red. Bottom 10 values are in light green.";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
worksheet.write_row_matrix(2, 1, data)?;
// Set the column widths for clarity.
for col_num in 1..=10u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write a conditional format over a range.
let conditional_format = ConditionalFormatTop::new()
.set_rule(rust_xlsxwriter::ConditionalFormatTopRule::Top(10))
.set_format(&format1);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// Also show the bottom values in the same range.
let conditional_format = ConditionalFormatTop::new()
.set_rule(rust_xlsxwriter::ConditionalFormatTopRule::Bottom(10))
.set_format(&format2);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 6. Cell conditional formatting in non-contiguous range.
// -----------------------------------------------------------------------
let caption = "Cells with values >= 50 are in light red. Values < 50 are in light green. Non-contiguous ranges.";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
worksheet.write_row_matrix(2, 1, data)?;
// Set the column widths for clarity.
for col_num in 1..=10u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write a conditional format over a non-contiguous range.
let conditional_format = ConditionalFormatCell::new()
.set_rule(ConditionalFormatCellRule::GreaterThanOrEqualTo(50))
.set_multi_range("B3:D6 I3:K6 B9:D12 I9:K12")
.set_format(&format1);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// Write another conditional format over the same range.
let conditional_format = ConditionalFormatCell::new()
.set_rule(ConditionalFormatCellRule::LessThan(50))
.set_multi_range("B3:D6 I3:K6 B9:D12 I9:K12")
.set_format(&format2);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 7. Formula conditional formatting.
// -----------------------------------------------------------------------
let caption = "Even numbered cells are in light green. Odd numbered cells are in light red.";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
worksheet.write_row_matrix(2, 1, data)?;
// Set the column widths for clarity.
for col_num in 1..=10u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write a conditional format over a range.
let conditional_format = ConditionalFormatFormula::new()
.set_rule("=ISODD(B3)")
.set_format(&format1);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// Write another conditional format over the same range.
let conditional_format = ConditionalFormatFormula::new()
.set_rule("=ISEVEN(B3)")
.set_format(&format2);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 8. Text style conditional formats.
// -----------------------------------------------------------------------
let caption =
"Column A shows words that contain the sub-word 'rust'. Column C shows words that start/end with 't'";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 0, caption)?;
// Add some sample data.
let word_list = [
"apocrustic",
"burstwort",
"cloudburst",
"crustification",
"distrustfulness",
"laurustine",
"outburst",
"rusticism",
"thunderburst",
"trustee",
"trustworthiness",
"unburstableness",
"unfrustratable",
];
worksheet.write_column(1, 0, word_list)?;
worksheet.write_column(1, 2, word_list)?;
// Set the column widths for clarity.
worksheet.set_column_width(0, 20)?;
worksheet.set_column_width(2, 20)?;
// Write a text "containing" conditional format over a range.
let conditional_format = ConditionalFormatText::new()
.set_rule(ConditionalFormatTextRule::Contains("rust".to_string()))
.set_format(&format2);
worksheet.add_conditional_format(1, 0, 13, 0, &conditional_format)?;
// Write a text "not containing" conditional format over the same range.
let conditional_format = ConditionalFormatText::new()
.set_rule(ConditionalFormatTextRule::DoesNotContain(
"rust".to_string(),
))
.set_format(&format1);
worksheet.add_conditional_format(1, 0, 13, 0, &conditional_format)?;
// Write a text "begins with" conditional format over a range.
let conditional_format = ConditionalFormatText::new()
.set_rule(ConditionalFormatTextRule::BeginsWith("t".to_string()))
.set_format(&format2);
worksheet.add_conditional_format(1, 2, 13, 2, &conditional_format)?;
// Write a text "ends with" conditional format over the same range.
let conditional_format = ConditionalFormatText::new()
.set_rule(ConditionalFormatTextRule::EndsWith("t".to_string()))
.set_format(&format1);
worksheet.add_conditional_format(1, 2, 13, 2, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 9. Examples of 2 color scale conditional formats.
// -----------------------------------------------------------------------
let caption = "Examples of 2 color scale conditional formats";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
let data = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10];
worksheet.write_column(2, 1, data)?;
worksheet.write_column(2, 3, data)?;
worksheet.write_column(2, 5, data)?;
worksheet.write_column(2, 7, data)?;
worksheet.write_column(2, 9, data)?;
worksheet.write_column(2, 11, data)?;
// Set the column widths for clarity.
for col_num in 0..=12u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write 2 color scale formats with standard Excel colors.
let conditional_format = ConditionalFormat2ColorScale::new()
.set_minimum_color("F8696B")
.set_maximum_color("FCFCFF");
worksheet.add_conditional_format(2, 1, 11, 1, &conditional_format)?;
let conditional_format = ConditionalFormat2ColorScale::new()
.set_minimum_color("FCFCFF")
.set_maximum_color("F8696B");
worksheet.add_conditional_format(2, 3, 11, 3, &conditional_format)?;
let conditional_format = ConditionalFormat2ColorScale::new()
.set_minimum_color("FCFCFF")
.set_maximum_color("63BE7B");
worksheet.add_conditional_format(2, 5, 11, 5, &conditional_format)?;
let conditional_format = ConditionalFormat2ColorScale::new()
.set_minimum_color("63BE7B")
.set_maximum_color("FCFCFF");
worksheet.add_conditional_format(2, 7, 11, 7, &conditional_format)?;
let conditional_format = ConditionalFormat2ColorScale::new()
.set_minimum_color("FFEF9C")
.set_maximum_color("63BE7B");
worksheet.add_conditional_format(2, 9, 11, 9, &conditional_format)?;
let conditional_format = ConditionalFormat2ColorScale::new()
.set_minimum_color("63BE7B")
.set_maximum_color("FFEF9C");
worksheet.add_conditional_format(2, 11, 11, 11, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 10. Examples of 3 color scale conditional formats.
// -----------------------------------------------------------------------
let caption = "Examples of 3 color scale conditional formats";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
let data = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10];
worksheet.write_column(2, 1, data)?;
worksheet.write_column(2, 3, data)?;
worksheet.write_column(2, 5, data)?;
worksheet.write_column(2, 7, data)?;
worksheet.write_column(2, 9, data)?;
worksheet.write_column(2, 11, data)?;
// Set the column widths for clarity.
for col_num in 0..=12u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write 3 color scale formats with standard Excel colors.
let conditional_format = ConditionalFormat3ColorScale::new()
.set_minimum_color("F8696B")
.set_midpoint_color("FFEB84")
.set_maximum_color("63BE7B");
worksheet.add_conditional_format(2, 1, 11, 1, &conditional_format)?;
let conditional_format = ConditionalFormat3ColorScale::new()
.set_minimum_color("63BE7B")
.set_midpoint_color("FFEB84")
.set_maximum_color("F8696B");
worksheet.add_conditional_format(2, 3, 11, 3, &conditional_format)?;
let conditional_format = ConditionalFormat3ColorScale::new()
.set_minimum_color("F8696B")
.set_midpoint_color("FCFCFF")
.set_maximum_color("63BE7B");
worksheet.add_conditional_format(2, 5, 11, 5, &conditional_format)?;
let conditional_format = ConditionalFormat3ColorScale::new()
.set_minimum_color("63BE7B")
.set_midpoint_color("FCFCFF")
.set_maximum_color("F8696B");
worksheet.add_conditional_format(2, 7, 11, 7, &conditional_format)?;
let conditional_format = ConditionalFormat3ColorScale::new()
.set_minimum_color("F8696B")
.set_midpoint_color("FCFCFF")
.set_maximum_color("5A8AC6");
worksheet.add_conditional_format(2, 9, 11, 9, &conditional_format)?;
let conditional_format = ConditionalFormat3ColorScale::new()
.set_minimum_color("5A8AC6")
.set_midpoint_color("FCFCFF")
.set_maximum_color("F8696B");
worksheet.add_conditional_format(2, 11, 11, 11, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 11. Examples of data bars.
// -----------------------------------------------------------------------
let caption = "Examples of data bars";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write_with_format(0, 1, caption, &bold)?;
worksheet.write(1, 1, "Default")?;
worksheet.write(1, 3, "Default negative")?;
worksheet.write(1, 5, "User color")?;
worksheet.write(1, 7, "Changed direction")?;
// Write the worksheet data.
let data1 = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10];
let data2 = [6, 4, 2, -2, -4, -6, -4, -2, 2, 4];
worksheet.write_column(2, 1, data1)?;
worksheet.write_column(2, 3, data2)?;
worksheet.write_column(2, 5, data1)?;
worksheet.write_column(2, 7, data1)?;
// Write a standard Excel data bar.
let conditional_format = ConditionalFormatDataBar::new();
worksheet.add_conditional_format(2, 1, 11, 1, &conditional_format)?;
// Write a standard Excel data bar with negative data
let conditional_format = ConditionalFormatDataBar::new();
worksheet.add_conditional_format(2, 3, 11, 3, &conditional_format)?;
// Write a data bar with a user defined fill color.
let conditional_format = ConditionalFormatDataBar::new().set_fill_color("009933");
worksheet.add_conditional_format(2, 5, 11, 5, &conditional_format)?;
// Write a data bar with the direction changed.
let conditional_format = ConditionalFormatDataBar::new()
.set_direction(ConditionalFormatDataBarDirection::RightToLeft);
worksheet.add_conditional_format(2, 7, 11, 7, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 12. Examples of icon style conditional formats.
// -----------------------------------------------------------------------
let caption = "Examples of icon style conditional formats.";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write_with_format(0, 0, caption, &bold)?;
worksheet.write_with_format(1, 0, "Three Traffic lights - Green is highest", &indent)?;
worksheet.write_with_format(2, 0, "Reversed - Red is highest", &indent)?;
worksheet.write_with_format(3, 0, "Icons only - The number data is hidden", &indent)?;
worksheet.write_with_format(4, 0, "Other three-five icon examples", &bold)?;
worksheet.write_with_format(5, 0, "Three arrows", &indent)?;
worksheet.write_with_format(6, 0, "Three symbols", &indent)?;
worksheet.write_with_format(7, 0, "Three stars", &indent)?;
worksheet.write_with_format(8, 0, "Four arrows", &indent)?;
worksheet.write_with_format(9, 0, "Four circles - Red (highest) to Black", &indent)?;
worksheet.write_with_format(10, 0, "Four rating histograms", &indent)?;
worksheet.write_with_format(11, 0, "Five arrows", &indent)?;
worksheet.write_with_format(12, 0, "Five rating histograms", &indent)?;
worksheet.write_with_format(13, 0, "Five rating quadrants", &indent)?;
// Set the column width for clarity.
worksheet.set_column_width(0, 35)?;
// Write the worksheet data.
worksheet.write_row(1, 1, [1, 2, 3])?;
worksheet.write_row(2, 1, [1, 2, 3])?;
worksheet.write_row(3, 1, [1, 2, 3])?;
worksheet.write_row(5, 1, [1, 2, 3])?;
worksheet.write_row(6, 1, [1, 2, 3])?;
worksheet.write_row(7, 1, [1, 2, 3])?;
worksheet.write_row(8, 1, [1, 2, 3, 4])?;
worksheet.write_row(9, 1, [1, 2, 3, 4])?;
worksheet.write_row(10, 1, [1, 2, 3, 4])?;
worksheet.write_row(11, 1, [1, 2, 3, 4, 5])?;
worksheet.write_row(12, 1, [1, 2, 3, 4, 5])?;
worksheet.write_row(13, 1, [1, 2, 3, 4, 5])?;
// Three Traffic lights - Green is highest.
let conditional_format = ConditionalFormatIconSet::new()
.set_icon_type(ConditionalFormatIconType::ThreeTrafficLights);
worksheet.add_conditional_format(1, 1, 1, 3, &conditional_format)?;
// Reversed - Red is highest.
let conditional_format = ConditionalFormatIconSet::new()
.set_icon_type(ConditionalFormatIconType::ThreeTrafficLights)
.reverse_icons(true);
worksheet.add_conditional_format(2, 1, 2, 3, &conditional_format)?;
// Icons only - The number data is hidden.
let conditional_format = ConditionalFormatIconSet::new()
.set_icon_type(ConditionalFormatIconType::ThreeTrafficLights)
.show_icons_only(true);
worksheet.add_conditional_format(3, 1, 3, 3, &conditional_format)?;
// Three arrows.
let conditional_format =
ConditionalFormatIconSet::new().set_icon_type(ConditionalFormatIconType::ThreeArrows);
worksheet.add_conditional_format(5, 1, 5, 3, &conditional_format)?;
// Three symbols.
let conditional_format = ConditionalFormatIconSet::new()
.set_icon_type(ConditionalFormatIconType::ThreeSymbolsCircled);
worksheet.add_conditional_format(6, 1, 6, 3, &conditional_format)?;
// Three stars.
let conditional_format =
ConditionalFormatIconSet::new().set_icon_type(ConditionalFormatIconType::ThreeStars);
worksheet.add_conditional_format(7, 1, 7, 3, &conditional_format)?;
// Four Arrows.
let conditional_format =
ConditionalFormatIconSet::new().set_icon_type(ConditionalFormatIconType::FourArrows);
worksheet.add_conditional_format(8, 1, 8, 4, &conditional_format)?;
// Four circles - Red (highest) to Black (lowest).
let conditional_format =
ConditionalFormatIconSet::new().set_icon_type(ConditionalFormatIconType::FourRedToBlack);
worksheet.add_conditional_format(9, 1, 9, 4, &conditional_format)?;
// Four rating histograms.
let conditional_format =
ConditionalFormatIconSet::new().set_icon_type(ConditionalFormatIconType::FourHistograms);
worksheet.add_conditional_format(10, 1, 10, 4, &conditional_format)?;
// Four Arrows.
let conditional_format =
ConditionalFormatIconSet::new().set_icon_type(ConditionalFormatIconType::FiveArrows);
worksheet.add_conditional_format(11, 1, 11, 5, &conditional_format)?;
// Four rating histograms.
let conditional_format =
ConditionalFormatIconSet::new().set_icon_type(ConditionalFormatIconType::FiveHistograms);
worksheet.add_conditional_format(12, 1, 12, 5, &conditional_format)?;
// Four rating quadrants.
let conditional_format =
ConditionalFormatIconSet::new().set_icon_type(ConditionalFormatIconType::FiveQuadrants);
worksheet.add_conditional_format(13, 1, 13, 5, &conditional_format)?;
// -----------------------------------------------------------------------
// Save and close the file.
// -----------------------------------------------------------------------
workbook.save("conditional_formats.xlsx")?;
Ok(())
}Example 2. Cell conditional formatting with between ranges. Values between 30 and 70 are in light red. Values outside that range are in light green.
See ConditionalFormatCell for more details.
Code to generate the above example:
// SPDX-License-Identifier: MIT OR Apache-2.0
//
// Copyright 2022-2024, John McNamara, jmcnamara@cpan.org
//! Example of how to add conditional formatting to a worksheet using the
//! rust_xlsxwriter library.
//!
//! Conditional formatting allows you to apply a format to a cell or a range of
//! cells based on user defined rule.
use rust_xlsxwriter::{
ConditionalFormat2ColorScale, ConditionalFormat3ColorScale, ConditionalFormatAverage,
ConditionalFormatAverageRule, ConditionalFormatCell, ConditionalFormatCellRule,
ConditionalFormatDataBar, ConditionalFormatDataBarDirection, ConditionalFormatDuplicate,
ConditionalFormatFormula, ConditionalFormatIconSet, ConditionalFormatIconType,
ConditionalFormatText, ConditionalFormatTextRule, ConditionalFormatTop, Format, Workbook,
XlsxError,
};
fn main() -> Result<(), XlsxError> {
// Create a new Excel file object.
let mut workbook = Workbook::new();
// Add a format. Light red fill with dark red text.
let format1 = Format::new()
.set_font_color("9C0006")
.set_background_color("FFC7CE");
// Add a format. Green fill with dark green text.
let format2 = Format::new()
.set_font_color("006100")
.set_background_color("C6EFCE");
// Add a format for headers.
let bold = Format::new().set_bold();
// Add a format for descriptions.
let indent = Format::new().set_indent(2);
// some sample data to run the conditional formatting against.
let data = [
[34, 72, 38, 30, 75, 48, 75, 66, 84, 86],
[6, 24, 1, 84, 54, 62, 60, 3, 26, 59],
[28, 79, 97, 13, 85, 93, 93, 22, 5, 14],
[27, 71, 40, 17, 18, 79, 90, 93, 29, 47],
[88, 25, 33, 23, 67, 1, 59, 79, 47, 36],
[24, 100, 20, 88, 29, 33, 38, 54, 54, 88],
[6, 57, 88, 28, 10, 26, 37, 7, 41, 48],
[52, 78, 1, 96, 26, 45, 47, 33, 96, 36],
[60, 54, 81, 66, 81, 90, 80, 93, 12, 55],
[70, 5, 46, 14, 71, 19, 66, 36, 41, 21],
];
// -----------------------------------------------------------------------
// Worksheet 1. Cell conditional formatting.
// -----------------------------------------------------------------------
let caption = "Cells with values >= 50 are in light red. Values < 50 are in light green.";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
worksheet.write_row_matrix(2, 1, data)?;
// Set the column widths for clarity.
for col_num in 1..=10u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write a conditional format over a range.
let conditional_format = ConditionalFormatCell::new()
.set_rule(ConditionalFormatCellRule::GreaterThanOrEqualTo(50))
.set_format(&format1);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// Write another conditional format over the same range.
let conditional_format = ConditionalFormatCell::new()
.set_rule(ConditionalFormatCellRule::LessThan(50))
.set_format(&format2);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 2. Cell conditional formatting with between ranges.
// -----------------------------------------------------------------------
let caption =
"Values between 30 and 70 are in light red. Values outside that range are in light green.";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
worksheet.write_row_matrix(2, 1, data)?;
// Set the column widths for clarity.
for col_num in 1..=10u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write a conditional format over a range.
let conditional_format = ConditionalFormatCell::new()
.set_rule(ConditionalFormatCellRule::Between(30, 70))
.set_format(&format1);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// Write another conditional format over the same range.
let conditional_format = ConditionalFormatCell::new()
.set_rule(ConditionalFormatCellRule::NotBetween(30, 70))
.set_format(&format2);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 3. Duplicate and Unique conditional formats.
// -----------------------------------------------------------------------
let caption = "Duplicate values are in light red. Unique values are in light green.";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
worksheet.write_row_matrix(2, 1, data)?;
// Set the column widths for clarity.
for col_num in 1..=10u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write a conditional format over a range.
let conditional_format = ConditionalFormatDuplicate::new().set_format(&format1);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// Invert the duplicate conditional format to show unique values in the
// same range.
let conditional_format = ConditionalFormatDuplicate::new()
.invert()
.set_format(&format2);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 4. Above and Below Average conditional formats.
// -----------------------------------------------------------------------
let caption = "Above average values are in light red. Below average values are in light green.";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
worksheet.write_row_matrix(2, 1, data)?;
// Set the column widths for clarity.
for col_num in 1..=10u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write a conditional format over a range. The default criteria is Above Average.
let conditional_format = ConditionalFormatAverage::new().set_format(&format1);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// Write another conditional format over the same range.
let conditional_format = ConditionalFormatAverage::new()
.set_rule(ConditionalFormatAverageRule::BelowAverage)
.set_format(&format2);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 5. Top and Bottom range conditional formats.
// -----------------------------------------------------------------------
let caption = "Top 10 values are in light red. Bottom 10 values are in light green.";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
worksheet.write_row_matrix(2, 1, data)?;
// Set the column widths for clarity.
for col_num in 1..=10u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write a conditional format over a range.
let conditional_format = ConditionalFormatTop::new()
.set_rule(rust_xlsxwriter::ConditionalFormatTopRule::Top(10))
.set_format(&format1);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// Also show the bottom values in the same range.
let conditional_format = ConditionalFormatTop::new()
.set_rule(rust_xlsxwriter::ConditionalFormatTopRule::Bottom(10))
.set_format(&format2);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 6. Cell conditional formatting in non-contiguous range.
// -----------------------------------------------------------------------
let caption = "Cells with values >= 50 are in light red. Values < 50 are in light green. Non-contiguous ranges.";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
worksheet.write_row_matrix(2, 1, data)?;
// Set the column widths for clarity.
for col_num in 1..=10u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write a conditional format over a non-contiguous range.
let conditional_format = ConditionalFormatCell::new()
.set_rule(ConditionalFormatCellRule::GreaterThanOrEqualTo(50))
.set_multi_range("B3:D6 I3:K6 B9:D12 I9:K12")
.set_format(&format1);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// Write another conditional format over the same range.
let conditional_format = ConditionalFormatCell::new()
.set_rule(ConditionalFormatCellRule::LessThan(50))
.set_multi_range("B3:D6 I3:K6 B9:D12 I9:K12")
.set_format(&format2);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 7. Formula conditional formatting.
// -----------------------------------------------------------------------
let caption = "Even numbered cells are in light green. Odd numbered cells are in light red.";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
worksheet.write_row_matrix(2, 1, data)?;
// Set the column widths for clarity.
for col_num in 1..=10u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write a conditional format over a range.
let conditional_format = ConditionalFormatFormula::new()
.set_rule("=ISODD(B3)")
.set_format(&format1);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// Write another conditional format over the same range.
let conditional_format = ConditionalFormatFormula::new()
.set_rule("=ISEVEN(B3)")
.set_format(&format2);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 8. Text style conditional formats.
// -----------------------------------------------------------------------
let caption =
"Column A shows words that contain the sub-word 'rust'. Column C shows words that start/end with 't'";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 0, caption)?;
// Add some sample data.
let word_list = [
"apocrustic",
"burstwort",
"cloudburst",
"crustification",
"distrustfulness",
"laurustine",
"outburst",
"rusticism",
"thunderburst",
"trustee",
"trustworthiness",
"unburstableness",
"unfrustratable",
];
worksheet.write_column(1, 0, word_list)?;
worksheet.write_column(1, 2, word_list)?;
// Set the column widths for clarity.
worksheet.set_column_width(0, 20)?;
worksheet.set_column_width(2, 20)?;
// Write a text "containing" conditional format over a range.
let conditional_format = ConditionalFormatText::new()
.set_rule(ConditionalFormatTextRule::Contains("rust".to_string()))
.set_format(&format2);
worksheet.add_conditional_format(1, 0, 13, 0, &conditional_format)?;
// Write a text "not containing" conditional format over the same range.
let conditional_format = ConditionalFormatText::new()
.set_rule(ConditionalFormatTextRule::DoesNotContain(
"rust".to_string(),
))
.set_format(&format1);
worksheet.add_conditional_format(1, 0, 13, 0, &conditional_format)?;
// Write a text "begins with" conditional format over a range.
let conditional_format = ConditionalFormatText::new()
.set_rule(ConditionalFormatTextRule::BeginsWith("t".to_string()))
.set_format(&format2);
worksheet.add_conditional_format(1, 2, 13, 2, &conditional_format)?;
// Write a text "ends with" conditional format over the same range.
let conditional_format = ConditionalFormatText::new()
.set_rule(ConditionalFormatTextRule::EndsWith("t".to_string()))
.set_format(&format1);
worksheet.add_conditional_format(1, 2, 13, 2, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 9. Examples of 2 color scale conditional formats.
// -----------------------------------------------------------------------
let caption = "Examples of 2 color scale conditional formats";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
let data = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10];
worksheet.write_column(2, 1, data)?;
worksheet.write_column(2, 3, data)?;
worksheet.write_column(2, 5, data)?;
worksheet.write_column(2, 7, data)?;
worksheet.write_column(2, 9, data)?;
worksheet.write_column(2, 11, data)?;
// Set the column widths for clarity.
for col_num in 0..=12u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write 2 color scale formats with standard Excel colors.
let conditional_format = ConditionalFormat2ColorScale::new()
.set_minimum_color("F8696B")
.set_maximum_color("FCFCFF");
worksheet.add_conditional_format(2, 1, 11, 1, &conditional_format)?;
let conditional_format = ConditionalFormat2ColorScale::new()
.set_minimum_color("FCFCFF")
.set_maximum_color("F8696B");
worksheet.add_conditional_format(2, 3, 11, 3, &conditional_format)?;
let conditional_format = ConditionalFormat2ColorScale::new()
.set_minimum_color("FCFCFF")
.set_maximum_color("63BE7B");
worksheet.add_conditional_format(2, 5, 11, 5, &conditional_format)?;
let conditional_format = ConditionalFormat2ColorScale::new()
.set_minimum_color("63BE7B")
.set_maximum_color("FCFCFF");
worksheet.add_conditional_format(2, 7, 11, 7, &conditional_format)?;
let conditional_format = ConditionalFormat2ColorScale::new()
.set_minimum_color("FFEF9C")
.set_maximum_color("63BE7B");
worksheet.add_conditional_format(2, 9, 11, 9, &conditional_format)?;
let conditional_format = ConditionalFormat2ColorScale::new()
.set_minimum_color("63BE7B")
.set_maximum_color("FFEF9C");
worksheet.add_conditional_format(2, 11, 11, 11, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 10. Examples of 3 color scale conditional formats.
// -----------------------------------------------------------------------
let caption = "Examples of 3 color scale conditional formats";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
let data = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10];
worksheet.write_column(2, 1, data)?;
worksheet.write_column(2, 3, data)?;
worksheet.write_column(2, 5, data)?;
worksheet.write_column(2, 7, data)?;
worksheet.write_column(2, 9, data)?;
worksheet.write_column(2, 11, data)?;
// Set the column widths for clarity.
for col_num in 0..=12u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write 3 color scale formats with standard Excel colors.
let conditional_format = ConditionalFormat3ColorScale::new()
.set_minimum_color("F8696B")
.set_midpoint_color("FFEB84")
.set_maximum_color("63BE7B");
worksheet.add_conditional_format(2, 1, 11, 1, &conditional_format)?;
let conditional_format = ConditionalFormat3ColorScale::new()
.set_minimum_color("63BE7B")
.set_midpoint_color("FFEB84")
.set_maximum_color("F8696B");
worksheet.add_conditional_format(2, 3, 11, 3, &conditional_format)?;
let conditional_format = ConditionalFormat3ColorScale::new()
.set_minimum_color("F8696B")
.set_midpoint_color("FCFCFF")
.set_maximum_color("63BE7B");
worksheet.add_conditional_format(2, 5, 11, 5, &conditional_format)?;
let conditional_format = ConditionalFormat3ColorScale::new()
.set_minimum_color("63BE7B")
.set_midpoint_color("FCFCFF")
.set_maximum_color("F8696B");
worksheet.add_conditional_format(2, 7, 11, 7, &conditional_format)?;
let conditional_format = ConditionalFormat3ColorScale::new()
.set_minimum_color("F8696B")
.set_midpoint_color("FCFCFF")
.set_maximum_color("5A8AC6");
worksheet.add_conditional_format(2, 9, 11, 9, &conditional_format)?;
let conditional_format = ConditionalFormat3ColorScale::new()
.set_minimum_color("5A8AC6")
.set_midpoint_color("FCFCFF")
.set_maximum_color("F8696B");
worksheet.add_conditional_format(2, 11, 11, 11, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 11. Examples of data bars.
// -----------------------------------------------------------------------
let caption = "Examples of data bars";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write_with_format(0, 1, caption, &bold)?;
worksheet.write(1, 1, "Default")?;
worksheet.write(1, 3, "Default negative")?;
worksheet.write(1, 5, "User color")?;
worksheet.write(1, 7, "Changed direction")?;
// Write the worksheet data.
let data1 = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10];
let data2 = [6, 4, 2, -2, -4, -6, -4, -2, 2, 4];
worksheet.write_column(2, 1, data1)?;
worksheet.write_column(2, 3, data2)?;
worksheet.write_column(2, 5, data1)?;
worksheet.write_column(2, 7, data1)?;
// Write a standard Excel data bar.
let conditional_format = ConditionalFormatDataBar::new();
worksheet.add_conditional_format(2, 1, 11, 1, &conditional_format)?;
// Write a standard Excel data bar with negative data
let conditional_format = ConditionalFormatDataBar::new();
worksheet.add_conditional_format(2, 3, 11, 3, &conditional_format)?;
// Write a data bar with a user defined fill color.
let conditional_format = ConditionalFormatDataBar::new().set_fill_color("009933");
worksheet.add_conditional_format(2, 5, 11, 5, &conditional_format)?;
// Write a data bar with the direction changed.
let conditional_format = ConditionalFormatDataBar::new()
.set_direction(ConditionalFormatDataBarDirection::RightToLeft);
worksheet.add_conditional_format(2, 7, 11, 7, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 12. Examples of icon style conditional formats.
// -----------------------------------------------------------------------
let caption = "Examples of icon style conditional formats.";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write_with_format(0, 0, caption, &bold)?;
worksheet.write_with_format(1, 0, "Three Traffic lights - Green is highest", &indent)?;
worksheet.write_with_format(2, 0, "Reversed - Red is highest", &indent)?;
worksheet.write_with_format(3, 0, "Icons only - The number data is hidden", &indent)?;
worksheet.write_with_format(4, 0, "Other three-five icon examples", &bold)?;
worksheet.write_with_format(5, 0, "Three arrows", &indent)?;
worksheet.write_with_format(6, 0, "Three symbols", &indent)?;
worksheet.write_with_format(7, 0, "Three stars", &indent)?;
worksheet.write_with_format(8, 0, "Four arrows", &indent)?;
worksheet.write_with_format(9, 0, "Four circles - Red (highest) to Black", &indent)?;
worksheet.write_with_format(10, 0, "Four rating histograms", &indent)?;
worksheet.write_with_format(11, 0, "Five arrows", &indent)?;
worksheet.write_with_format(12, 0, "Five rating histograms", &indent)?;
worksheet.write_with_format(13, 0, "Five rating quadrants", &indent)?;
// Set the column width for clarity.
worksheet.set_column_width(0, 35)?;
// Write the worksheet data.
worksheet.write_row(1, 1, [1, 2, 3])?;
worksheet.write_row(2, 1, [1, 2, 3])?;
worksheet.write_row(3, 1, [1, 2, 3])?;
worksheet.write_row(5, 1, [1, 2, 3])?;
worksheet.write_row(6, 1, [1, 2, 3])?;
worksheet.write_row(7, 1, [1, 2, 3])?;
worksheet.write_row(8, 1, [1, 2, 3, 4])?;
worksheet.write_row(9, 1, [1, 2, 3, 4])?;
worksheet.write_row(10, 1, [1, 2, 3, 4])?;
worksheet.write_row(11, 1, [1, 2, 3, 4, 5])?;
worksheet.write_row(12, 1, [1, 2, 3, 4, 5])?;
worksheet.write_row(13, 1, [1, 2, 3, 4, 5])?;
// Three Traffic lights - Green is highest.
let conditional_format = ConditionalFormatIconSet::new()
.set_icon_type(ConditionalFormatIconType::ThreeTrafficLights);
worksheet.add_conditional_format(1, 1, 1, 3, &conditional_format)?;
// Reversed - Red is highest.
let conditional_format = ConditionalFormatIconSet::new()
.set_icon_type(ConditionalFormatIconType::ThreeTrafficLights)
.reverse_icons(true);
worksheet.add_conditional_format(2, 1, 2, 3, &conditional_format)?;
// Icons only - The number data is hidden.
let conditional_format = ConditionalFormatIconSet::new()
.set_icon_type(ConditionalFormatIconType::ThreeTrafficLights)
.show_icons_only(true);
worksheet.add_conditional_format(3, 1, 3, 3, &conditional_format)?;
// Three arrows.
let conditional_format =
ConditionalFormatIconSet::new().set_icon_type(ConditionalFormatIconType::ThreeArrows);
worksheet.add_conditional_format(5, 1, 5, 3, &conditional_format)?;
// Three symbols.
let conditional_format = ConditionalFormatIconSet::new()
.set_icon_type(ConditionalFormatIconType::ThreeSymbolsCircled);
worksheet.add_conditional_format(6, 1, 6, 3, &conditional_format)?;
// Three stars.
let conditional_format =
ConditionalFormatIconSet::new().set_icon_type(ConditionalFormatIconType::ThreeStars);
worksheet.add_conditional_format(7, 1, 7, 3, &conditional_format)?;
// Four Arrows.
let conditional_format =
ConditionalFormatIconSet::new().set_icon_type(ConditionalFormatIconType::FourArrows);
worksheet.add_conditional_format(8, 1, 8, 4, &conditional_format)?;
// Four circles - Red (highest) to Black (lowest).
let conditional_format =
ConditionalFormatIconSet::new().set_icon_type(ConditionalFormatIconType::FourRedToBlack);
worksheet.add_conditional_format(9, 1, 9, 4, &conditional_format)?;
// Four rating histograms.
let conditional_format =
ConditionalFormatIconSet::new().set_icon_type(ConditionalFormatIconType::FourHistograms);
worksheet.add_conditional_format(10, 1, 10, 4, &conditional_format)?;
// Four Arrows.
let conditional_format =
ConditionalFormatIconSet::new().set_icon_type(ConditionalFormatIconType::FiveArrows);
worksheet.add_conditional_format(11, 1, 11, 5, &conditional_format)?;
// Four rating histograms.
let conditional_format =
ConditionalFormatIconSet::new().set_icon_type(ConditionalFormatIconType::FiveHistograms);
worksheet.add_conditional_format(12, 1, 12, 5, &conditional_format)?;
// Four rating quadrants.
let conditional_format =
ConditionalFormatIconSet::new().set_icon_type(ConditionalFormatIconType::FiveQuadrants);
worksheet.add_conditional_format(13, 1, 13, 5, &conditional_format)?;
// -----------------------------------------------------------------------
// Save and close the file.
// -----------------------------------------------------------------------
workbook.save("conditional_formats.xlsx")?;
Ok(())
}Example 3. Duplicate and Unique conditional formats. Duplicate values are in light red. Unique values are in light green.
See ConditionalFormatDuplicate for more details.
Code to generate the above example:
// SPDX-License-Identifier: MIT OR Apache-2.0
//
// Copyright 2022-2024, John McNamara, jmcnamara@cpan.org
//! Example of how to add conditional formatting to a worksheet using the
//! rust_xlsxwriter library.
//!
//! Conditional formatting allows you to apply a format to a cell or a range of
//! cells based on user defined rule.
use rust_xlsxwriter::{
ConditionalFormat2ColorScale, ConditionalFormat3ColorScale, ConditionalFormatAverage,
ConditionalFormatAverageRule, ConditionalFormatCell, ConditionalFormatCellRule,
ConditionalFormatDataBar, ConditionalFormatDataBarDirection, ConditionalFormatDuplicate,
ConditionalFormatFormula, ConditionalFormatIconSet, ConditionalFormatIconType,
ConditionalFormatText, ConditionalFormatTextRule, ConditionalFormatTop, Format, Workbook,
XlsxError,
};
fn main() -> Result<(), XlsxError> {
// Create a new Excel file object.
let mut workbook = Workbook::new();
// Add a format. Light red fill with dark red text.
let format1 = Format::new()
.set_font_color("9C0006")
.set_background_color("FFC7CE");
// Add a format. Green fill with dark green text.
let format2 = Format::new()
.set_font_color("006100")
.set_background_color("C6EFCE");
// Add a format for headers.
let bold = Format::new().set_bold();
// Add a format for descriptions.
let indent = Format::new().set_indent(2);
// some sample data to run the conditional formatting against.
let data = [
[34, 72, 38, 30, 75, 48, 75, 66, 84, 86],
[6, 24, 1, 84, 54, 62, 60, 3, 26, 59],
[28, 79, 97, 13, 85, 93, 93, 22, 5, 14],
[27, 71, 40, 17, 18, 79, 90, 93, 29, 47],
[88, 25, 33, 23, 67, 1, 59, 79, 47, 36],
[24, 100, 20, 88, 29, 33, 38, 54, 54, 88],
[6, 57, 88, 28, 10, 26, 37, 7, 41, 48],
[52, 78, 1, 96, 26, 45, 47, 33, 96, 36],
[60, 54, 81, 66, 81, 90, 80, 93, 12, 55],
[70, 5, 46, 14, 71, 19, 66, 36, 41, 21],
];
// -----------------------------------------------------------------------
// Worksheet 1. Cell conditional formatting.
// -----------------------------------------------------------------------
let caption = "Cells with values >= 50 are in light red. Values < 50 are in light green.";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
worksheet.write_row_matrix(2, 1, data)?;
// Set the column widths for clarity.
for col_num in 1..=10u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write a conditional format over a range.
let conditional_format = ConditionalFormatCell::new()
.set_rule(ConditionalFormatCellRule::GreaterThanOrEqualTo(50))
.set_format(&format1);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// Write another conditional format over the same range.
let conditional_format = ConditionalFormatCell::new()
.set_rule(ConditionalFormatCellRule::LessThan(50))
.set_format(&format2);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 2. Cell conditional formatting with between ranges.
// -----------------------------------------------------------------------
let caption =
"Values between 30 and 70 are in light red. Values outside that range are in light green.";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
worksheet.write_row_matrix(2, 1, data)?;
// Set the column widths for clarity.
for col_num in 1..=10u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write a conditional format over a range.
let conditional_format = ConditionalFormatCell::new()
.set_rule(ConditionalFormatCellRule::Between(30, 70))
.set_format(&format1);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// Write another conditional format over the same range.
let conditional_format = ConditionalFormatCell::new()
.set_rule(ConditionalFormatCellRule::NotBetween(30, 70))
.set_format(&format2);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 3. Duplicate and Unique conditional formats.
// -----------------------------------------------------------------------
let caption = "Duplicate values are in light red. Unique values are in light green.";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
worksheet.write_row_matrix(2, 1, data)?;
// Set the column widths for clarity.
for col_num in 1..=10u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write a conditional format over a range.
let conditional_format = ConditionalFormatDuplicate::new().set_format(&format1);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// Invert the duplicate conditional format to show unique values in the
// same range.
let conditional_format = ConditionalFormatDuplicate::new()
.invert()
.set_format(&format2);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 4. Above and Below Average conditional formats.
// -----------------------------------------------------------------------
let caption = "Above average values are in light red. Below average values are in light green.";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
worksheet.write_row_matrix(2, 1, data)?;
// Set the column widths for clarity.
for col_num in 1..=10u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write a conditional format over a range. The default criteria is Above Average.
let conditional_format = ConditionalFormatAverage::new().set_format(&format1);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// Write another conditional format over the same range.
let conditional_format = ConditionalFormatAverage::new()
.set_rule(ConditionalFormatAverageRule::BelowAverage)
.set_format(&format2);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 5. Top and Bottom range conditional formats.
// -----------------------------------------------------------------------
let caption = "Top 10 values are in light red. Bottom 10 values are in light green.";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
worksheet.write_row_matrix(2, 1, data)?;
// Set the column widths for clarity.
for col_num in 1..=10u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write a conditional format over a range.
let conditional_format = ConditionalFormatTop::new()
.set_rule(rust_xlsxwriter::ConditionalFormatTopRule::Top(10))
.set_format(&format1);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// Also show the bottom values in the same range.
let conditional_format = ConditionalFormatTop::new()
.set_rule(rust_xlsxwriter::ConditionalFormatTopRule::Bottom(10))
.set_format(&format2);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 6. Cell conditional formatting in non-contiguous range.
// -----------------------------------------------------------------------
let caption = "Cells with values >= 50 are in light red. Values < 50 are in light green. Non-contiguous ranges.";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
worksheet.write_row_matrix(2, 1, data)?;
// Set the column widths for clarity.
for col_num in 1..=10u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write a conditional format over a non-contiguous range.
let conditional_format = ConditionalFormatCell::new()
.set_rule(ConditionalFormatCellRule::GreaterThanOrEqualTo(50))
.set_multi_range("B3:D6 I3:K6 B9:D12 I9:K12")
.set_format(&format1);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// Write another conditional format over the same range.
let conditional_format = ConditionalFormatCell::new()
.set_rule(ConditionalFormatCellRule::LessThan(50))
.set_multi_range("B3:D6 I3:K6 B9:D12 I9:K12")
.set_format(&format2);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 7. Formula conditional formatting.
// -----------------------------------------------------------------------
let caption = "Even numbered cells are in light green. Odd numbered cells are in light red.";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
worksheet.write_row_matrix(2, 1, data)?;
// Set the column widths for clarity.
for col_num in 1..=10u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write a conditional format over a range.
let conditional_format = ConditionalFormatFormula::new()
.set_rule("=ISODD(B3)")
.set_format(&format1);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// Write another conditional format over the same range.
let conditional_format = ConditionalFormatFormula::new()
.set_rule("=ISEVEN(B3)")
.set_format(&format2);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 8. Text style conditional formats.
// -----------------------------------------------------------------------
let caption =
"Column A shows words that contain the sub-word 'rust'. Column C shows words that start/end with 't'";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 0, caption)?;
// Add some sample data.
let word_list = [
"apocrustic",
"burstwort",
"cloudburst",
"crustification",
"distrustfulness",
"laurustine",
"outburst",
"rusticism",
"thunderburst",
"trustee",
"trustworthiness",
"unburstableness",
"unfrustratable",
];
worksheet.write_column(1, 0, word_list)?;
worksheet.write_column(1, 2, word_list)?;
// Set the column widths for clarity.
worksheet.set_column_width(0, 20)?;
worksheet.set_column_width(2, 20)?;
// Write a text "containing" conditional format over a range.
let conditional_format = ConditionalFormatText::new()
.set_rule(ConditionalFormatTextRule::Contains("rust".to_string()))
.set_format(&format2);
worksheet.add_conditional_format(1, 0, 13, 0, &conditional_format)?;
// Write a text "not containing" conditional format over the same range.
let conditional_format = ConditionalFormatText::new()
.set_rule(ConditionalFormatTextRule::DoesNotContain(
"rust".to_string(),
))
.set_format(&format1);
worksheet.add_conditional_format(1, 0, 13, 0, &conditional_format)?;
// Write a text "begins with" conditional format over a range.
let conditional_format = ConditionalFormatText::new()
.set_rule(ConditionalFormatTextRule::BeginsWith("t".to_string()))
.set_format(&format2);
worksheet.add_conditional_format(1, 2, 13, 2, &conditional_format)?;
// Write a text "ends with" conditional format over the same range.
let conditional_format = ConditionalFormatText::new()
.set_rule(ConditionalFormatTextRule::EndsWith("t".to_string()))
.set_format(&format1);
worksheet.add_conditional_format(1, 2, 13, 2, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 9. Examples of 2 color scale conditional formats.
// -----------------------------------------------------------------------
let caption = "Examples of 2 color scale conditional formats";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
let data = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10];
worksheet.write_column(2, 1, data)?;
worksheet.write_column(2, 3, data)?;
worksheet.write_column(2, 5, data)?;
worksheet.write_column(2, 7, data)?;
worksheet.write_column(2, 9, data)?;
worksheet.write_column(2, 11, data)?;
// Set the column widths for clarity.
for col_num in 0..=12u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write 2 color scale formats with standard Excel colors.
let conditional_format = ConditionalFormat2ColorScale::new()
.set_minimum_color("F8696B")
.set_maximum_color("FCFCFF");
worksheet.add_conditional_format(2, 1, 11, 1, &conditional_format)?;
let conditional_format = ConditionalFormat2ColorScale::new()
.set_minimum_color("FCFCFF")
.set_maximum_color("F8696B");
worksheet.add_conditional_format(2, 3, 11, 3, &conditional_format)?;
let conditional_format = ConditionalFormat2ColorScale::new()
.set_minimum_color("FCFCFF")
.set_maximum_color("63BE7B");
worksheet.add_conditional_format(2, 5, 11, 5, &conditional_format)?;
let conditional_format = ConditionalFormat2ColorScale::new()
.set_minimum_color("63BE7B")
.set_maximum_color("FCFCFF");
worksheet.add_conditional_format(2, 7, 11, 7, &conditional_format)?;
let conditional_format = ConditionalFormat2ColorScale::new()
.set_minimum_color("FFEF9C")
.set_maximum_color("63BE7B");
worksheet.add_conditional_format(2, 9, 11, 9, &conditional_format)?;
let conditional_format = ConditionalFormat2ColorScale::new()
.set_minimum_color("63BE7B")
.set_maximum_color("FFEF9C");
worksheet.add_conditional_format(2, 11, 11, 11, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 10. Examples of 3 color scale conditional formats.
// -----------------------------------------------------------------------
let caption = "Examples of 3 color scale conditional formats";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
let data = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10];
worksheet.write_column(2, 1, data)?;
worksheet.write_column(2, 3, data)?;
worksheet.write_column(2, 5, data)?;
worksheet.write_column(2, 7, data)?;
worksheet.write_column(2, 9, data)?;
worksheet.write_column(2, 11, data)?;
// Set the column widths for clarity.
for col_num in 0..=12u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write 3 color scale formats with standard Excel colors.
let conditional_format = ConditionalFormat3ColorScale::new()
.set_minimum_color("F8696B")
.set_midpoint_color("FFEB84")
.set_maximum_color("63BE7B");
worksheet.add_conditional_format(2, 1, 11, 1, &conditional_format)?;
let conditional_format = ConditionalFormat3ColorScale::new()
.set_minimum_color("63BE7B")
.set_midpoint_color("FFEB84")
.set_maximum_color("F8696B");
worksheet.add_conditional_format(2, 3, 11, 3, &conditional_format)?;
let conditional_format = ConditionalFormat3ColorScale::new()
.set_minimum_color("F8696B")
.set_midpoint_color("FCFCFF")
.set_maximum_color("63BE7B");
worksheet.add_conditional_format(2, 5, 11, 5, &conditional_format)?;
let conditional_format = ConditionalFormat3ColorScale::new()
.set_minimum_color("63BE7B")
.set_midpoint_color("FCFCFF")
.set_maximum_color("F8696B");
worksheet.add_conditional_format(2, 7, 11, 7, &conditional_format)?;
let conditional_format = ConditionalFormat3ColorScale::new()
.set_minimum_color("F8696B")
.set_midpoint_color("FCFCFF")
.set_maximum_color("5A8AC6");
worksheet.add_conditional_format(2, 9, 11, 9, &conditional_format)?;
let conditional_format = ConditionalFormat3ColorScale::new()
.set_minimum_color("5A8AC6")
.set_midpoint_color("FCFCFF")
.set_maximum_color("F8696B");
worksheet.add_conditional_format(2, 11, 11, 11, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 11. Examples of data bars.
// -----------------------------------------------------------------------
let caption = "Examples of data bars";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write_with_format(0, 1, caption, &bold)?;
worksheet.write(1, 1, "Default")?;
worksheet.write(1, 3, "Default negative")?;
worksheet.write(1, 5, "User color")?;
worksheet.write(1, 7, "Changed direction")?;
// Write the worksheet data.
let data1 = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10];
let data2 = [6, 4, 2, -2, -4, -6, -4, -2, 2, 4];
worksheet.write_column(2, 1, data1)?;
worksheet.write_column(2, 3, data2)?;
worksheet.write_column(2, 5, data1)?;
worksheet.write_column(2, 7, data1)?;
// Write a standard Excel data bar.
let conditional_format = ConditionalFormatDataBar::new();
worksheet.add_conditional_format(2, 1, 11, 1, &conditional_format)?;
// Write a standard Excel data bar with negative data
let conditional_format = ConditionalFormatDataBar::new();
worksheet.add_conditional_format(2, 3, 11, 3, &conditional_format)?;
// Write a data bar with a user defined fill color.
let conditional_format = ConditionalFormatDataBar::new().set_fill_color("009933");
worksheet.add_conditional_format(2, 5, 11, 5, &conditional_format)?;
// Write a data bar with the direction changed.
let conditional_format = ConditionalFormatDataBar::new()
.set_direction(ConditionalFormatDataBarDirection::RightToLeft);
worksheet.add_conditional_format(2, 7, 11, 7, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 12. Examples of icon style conditional formats.
// -----------------------------------------------------------------------
let caption = "Examples of icon style conditional formats.";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write_with_format(0, 0, caption, &bold)?;
worksheet.write_with_format(1, 0, "Three Traffic lights - Green is highest", &indent)?;
worksheet.write_with_format(2, 0, "Reversed - Red is highest", &indent)?;
worksheet.write_with_format(3, 0, "Icons only - The number data is hidden", &indent)?;
worksheet.write_with_format(4, 0, "Other three-five icon examples", &bold)?;
worksheet.write_with_format(5, 0, "Three arrows", &indent)?;
worksheet.write_with_format(6, 0, "Three symbols", &indent)?;
worksheet.write_with_format(7, 0, "Three stars", &indent)?;
worksheet.write_with_format(8, 0, "Four arrows", &indent)?;
worksheet.write_with_format(9, 0, "Four circles - Red (highest) to Black", &indent)?;
worksheet.write_with_format(10, 0, "Four rating histograms", &indent)?;
worksheet.write_with_format(11, 0, "Five arrows", &indent)?;
worksheet.write_with_format(12, 0, "Five rating histograms", &indent)?;
worksheet.write_with_format(13, 0, "Five rating quadrants", &indent)?;
// Set the column width for clarity.
worksheet.set_column_width(0, 35)?;
// Write the worksheet data.
worksheet.write_row(1, 1, [1, 2, 3])?;
worksheet.write_row(2, 1, [1, 2, 3])?;
worksheet.write_row(3, 1, [1, 2, 3])?;
worksheet.write_row(5, 1, [1, 2, 3])?;
worksheet.write_row(6, 1, [1, 2, 3])?;
worksheet.write_row(7, 1, [1, 2, 3])?;
worksheet.write_row(8, 1, [1, 2, 3, 4])?;
worksheet.write_row(9, 1, [1, 2, 3, 4])?;
worksheet.write_row(10, 1, [1, 2, 3, 4])?;
worksheet.write_row(11, 1, [1, 2, 3, 4, 5])?;
worksheet.write_row(12, 1, [1, 2, 3, 4, 5])?;
worksheet.write_row(13, 1, [1, 2, 3, 4, 5])?;
// Three Traffic lights - Green is highest.
let conditional_format = ConditionalFormatIconSet::new()
.set_icon_type(ConditionalFormatIconType::ThreeTrafficLights);
worksheet.add_conditional_format(1, 1, 1, 3, &conditional_format)?;
// Reversed - Red is highest.
let conditional_format = ConditionalFormatIconSet::new()
.set_icon_type(ConditionalFormatIconType::ThreeTrafficLights)
.reverse_icons(true);
worksheet.add_conditional_format(2, 1, 2, 3, &conditional_format)?;
// Icons only - The number data is hidden.
let conditional_format = ConditionalFormatIconSet::new()
.set_icon_type(ConditionalFormatIconType::ThreeTrafficLights)
.show_icons_only(true);
worksheet.add_conditional_format(3, 1, 3, 3, &conditional_format)?;
// Three arrows.
let conditional_format =
ConditionalFormatIconSet::new().set_icon_type(ConditionalFormatIconType::ThreeArrows);
worksheet.add_conditional_format(5, 1, 5, 3, &conditional_format)?;
// Three symbols.
let conditional_format = ConditionalFormatIconSet::new()
.set_icon_type(ConditionalFormatIconType::ThreeSymbolsCircled);
worksheet.add_conditional_format(6, 1, 6, 3, &conditional_format)?;
// Three stars.
let conditional_format =
ConditionalFormatIconSet::new().set_icon_type(ConditionalFormatIconType::ThreeStars);
worksheet.add_conditional_format(7, 1, 7, 3, &conditional_format)?;
// Four Arrows.
let conditional_format =
ConditionalFormatIconSet::new().set_icon_type(ConditionalFormatIconType::FourArrows);
worksheet.add_conditional_format(8, 1, 8, 4, &conditional_format)?;
// Four circles - Red (highest) to Black (lowest).
let conditional_format =
ConditionalFormatIconSet::new().set_icon_type(ConditionalFormatIconType::FourRedToBlack);
worksheet.add_conditional_format(9, 1, 9, 4, &conditional_format)?;
// Four rating histograms.
let conditional_format =
ConditionalFormatIconSet::new().set_icon_type(ConditionalFormatIconType::FourHistograms);
worksheet.add_conditional_format(10, 1, 10, 4, &conditional_format)?;
// Four Arrows.
let conditional_format =
ConditionalFormatIconSet::new().set_icon_type(ConditionalFormatIconType::FiveArrows);
worksheet.add_conditional_format(11, 1, 11, 5, &conditional_format)?;
// Four rating histograms.
let conditional_format =
ConditionalFormatIconSet::new().set_icon_type(ConditionalFormatIconType::FiveHistograms);
worksheet.add_conditional_format(12, 1, 12, 5, &conditional_format)?;
// Four rating quadrants.
let conditional_format =
ConditionalFormatIconSet::new().set_icon_type(ConditionalFormatIconType::FiveQuadrants);
worksheet.add_conditional_format(13, 1, 13, 5, &conditional_format)?;
// -----------------------------------------------------------------------
// Save and close the file.
// -----------------------------------------------------------------------
workbook.save("conditional_formats.xlsx")?;
Ok(())
}Example 4. Above and Below Average conditional formats. Above average values are in light red. Below average values are in light green.
See ConditionalFormatAverage for more details.
Code to generate the above example:
// SPDX-License-Identifier: MIT OR Apache-2.0
//
// Copyright 2022-2024, John McNamara, jmcnamara@cpan.org
//! Example of how to add conditional formatting to a worksheet using the
//! rust_xlsxwriter library.
//!
//! Conditional formatting allows you to apply a format to a cell or a range of
//! cells based on user defined rule.
use rust_xlsxwriter::{
ConditionalFormat2ColorScale, ConditionalFormat3ColorScale, ConditionalFormatAverage,
ConditionalFormatAverageRule, ConditionalFormatCell, ConditionalFormatCellRule,
ConditionalFormatDataBar, ConditionalFormatDataBarDirection, ConditionalFormatDuplicate,
ConditionalFormatFormula, ConditionalFormatIconSet, ConditionalFormatIconType,
ConditionalFormatText, ConditionalFormatTextRule, ConditionalFormatTop, Format, Workbook,
XlsxError,
};
fn main() -> Result<(), XlsxError> {
// Create a new Excel file object.
let mut workbook = Workbook::new();
// Add a format. Light red fill with dark red text.
let format1 = Format::new()
.set_font_color("9C0006")
.set_background_color("FFC7CE");
// Add a format. Green fill with dark green text.
let format2 = Format::new()
.set_font_color("006100")
.set_background_color("C6EFCE");
// Add a format for headers.
let bold = Format::new().set_bold();
// Add a format for descriptions.
let indent = Format::new().set_indent(2);
// some sample data to run the conditional formatting against.
let data = [
[34, 72, 38, 30, 75, 48, 75, 66, 84, 86],
[6, 24, 1, 84, 54, 62, 60, 3, 26, 59],
[28, 79, 97, 13, 85, 93, 93, 22, 5, 14],
[27, 71, 40, 17, 18, 79, 90, 93, 29, 47],
[88, 25, 33, 23, 67, 1, 59, 79, 47, 36],
[24, 100, 20, 88, 29, 33, 38, 54, 54, 88],
[6, 57, 88, 28, 10, 26, 37, 7, 41, 48],
[52, 78, 1, 96, 26, 45, 47, 33, 96, 36],
[60, 54, 81, 66, 81, 90, 80, 93, 12, 55],
[70, 5, 46, 14, 71, 19, 66, 36, 41, 21],
];
// -----------------------------------------------------------------------
// Worksheet 1. Cell conditional formatting.
// -----------------------------------------------------------------------
let caption = "Cells with values >= 50 are in light red. Values < 50 are in light green.";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
worksheet.write_row_matrix(2, 1, data)?;
// Set the column widths for clarity.
for col_num in 1..=10u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write a conditional format over a range.
let conditional_format = ConditionalFormatCell::new()
.set_rule(ConditionalFormatCellRule::GreaterThanOrEqualTo(50))
.set_format(&format1);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// Write another conditional format over the same range.
let conditional_format = ConditionalFormatCell::new()
.set_rule(ConditionalFormatCellRule::LessThan(50))
.set_format(&format2);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 2. Cell conditional formatting with between ranges.
// -----------------------------------------------------------------------
let caption =
"Values between 30 and 70 are in light red. Values outside that range are in light green.";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
worksheet.write_row_matrix(2, 1, data)?;
// Set the column widths for clarity.
for col_num in 1..=10u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write a conditional format over a range.
let conditional_format = ConditionalFormatCell::new()
.set_rule(ConditionalFormatCellRule::Between(30, 70))
.set_format(&format1);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// Write another conditional format over the same range.
let conditional_format = ConditionalFormatCell::new()
.set_rule(ConditionalFormatCellRule::NotBetween(30, 70))
.set_format(&format2);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 3. Duplicate and Unique conditional formats.
// -----------------------------------------------------------------------
let caption = "Duplicate values are in light red. Unique values are in light green.";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
worksheet.write_row_matrix(2, 1, data)?;
// Set the column widths for clarity.
for col_num in 1..=10u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write a conditional format over a range.
let conditional_format = ConditionalFormatDuplicate::new().set_format(&format1);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// Invert the duplicate conditional format to show unique values in the
// same range.
let conditional_format = ConditionalFormatDuplicate::new()
.invert()
.set_format(&format2);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 4. Above and Below Average conditional formats.
// -----------------------------------------------------------------------
let caption = "Above average values are in light red. Below average values are in light green.";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
worksheet.write_row_matrix(2, 1, data)?;
// Set the column widths for clarity.
for col_num in 1..=10u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write a conditional format over a range. The default criteria is Above Average.
let conditional_format = ConditionalFormatAverage::new().set_format(&format1);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// Write another conditional format over the same range.
let conditional_format = ConditionalFormatAverage::new()
.set_rule(ConditionalFormatAverageRule::BelowAverage)
.set_format(&format2);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 5. Top and Bottom range conditional formats.
// -----------------------------------------------------------------------
let caption = "Top 10 values are in light red. Bottom 10 values are in light green.";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
worksheet.write_row_matrix(2, 1, data)?;
// Set the column widths for clarity.
for col_num in 1..=10u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write a conditional format over a range.
let conditional_format = ConditionalFormatTop::new()
.set_rule(rust_xlsxwriter::ConditionalFormatTopRule::Top(10))
.set_format(&format1);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// Also show the bottom values in the same range.
let conditional_format = ConditionalFormatTop::new()
.set_rule(rust_xlsxwriter::ConditionalFormatTopRule::Bottom(10))
.set_format(&format2);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 6. Cell conditional formatting in non-contiguous range.
// -----------------------------------------------------------------------
let caption = "Cells with values >= 50 are in light red. Values < 50 are in light green. Non-contiguous ranges.";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
worksheet.write_row_matrix(2, 1, data)?;
// Set the column widths for clarity.
for col_num in 1..=10u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write a conditional format over a non-contiguous range.
let conditional_format = ConditionalFormatCell::new()
.set_rule(ConditionalFormatCellRule::GreaterThanOrEqualTo(50))
.set_multi_range("B3:D6 I3:K6 B9:D12 I9:K12")
.set_format(&format1);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// Write another conditional format over the same range.
let conditional_format = ConditionalFormatCell::new()
.set_rule(ConditionalFormatCellRule::LessThan(50))
.set_multi_range("B3:D6 I3:K6 B9:D12 I9:K12")
.set_format(&format2);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 7. Formula conditional formatting.
// -----------------------------------------------------------------------
let caption = "Even numbered cells are in light green. Odd numbered cells are in light red.";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
worksheet.write_row_matrix(2, 1, data)?;
// Set the column widths for clarity.
for col_num in 1..=10u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write a conditional format over a range.
let conditional_format = ConditionalFormatFormula::new()
.set_rule("=ISODD(B3)")
.set_format(&format1);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// Write another conditional format over the same range.
let conditional_format = ConditionalFormatFormula::new()
.set_rule("=ISEVEN(B3)")
.set_format(&format2);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 8. Text style conditional formats.
// -----------------------------------------------------------------------
let caption =
"Column A shows words that contain the sub-word 'rust'. Column C shows words that start/end with 't'";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 0, caption)?;
// Add some sample data.
let word_list = [
"apocrustic",
"burstwort",
"cloudburst",
"crustification",
"distrustfulness",
"laurustine",
"outburst",
"rusticism",
"thunderburst",
"trustee",
"trustworthiness",
"unburstableness",
"unfrustratable",
];
worksheet.write_column(1, 0, word_list)?;
worksheet.write_column(1, 2, word_list)?;
// Set the column widths for clarity.
worksheet.set_column_width(0, 20)?;
worksheet.set_column_width(2, 20)?;
// Write a text "containing" conditional format over a range.
let conditional_format = ConditionalFormatText::new()
.set_rule(ConditionalFormatTextRule::Contains("rust".to_string()))
.set_format(&format2);
worksheet.add_conditional_format(1, 0, 13, 0, &conditional_format)?;
// Write a text "not containing" conditional format over the same range.
let conditional_format = ConditionalFormatText::new()
.set_rule(ConditionalFormatTextRule::DoesNotContain(
"rust".to_string(),
))
.set_format(&format1);
worksheet.add_conditional_format(1, 0, 13, 0, &conditional_format)?;
// Write a text "begins with" conditional format over a range.
let conditional_format = ConditionalFormatText::new()
.set_rule(ConditionalFormatTextRule::BeginsWith("t".to_string()))
.set_format(&format2);
worksheet.add_conditional_format(1, 2, 13, 2, &conditional_format)?;
// Write a text "ends with" conditional format over the same range.
let conditional_format = ConditionalFormatText::new()
.set_rule(ConditionalFormatTextRule::EndsWith("t".to_string()))
.set_format(&format1);
worksheet.add_conditional_format(1, 2, 13, 2, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 9. Examples of 2 color scale conditional formats.
// -----------------------------------------------------------------------
let caption = "Examples of 2 color scale conditional formats";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
let data = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10];
worksheet.write_column(2, 1, data)?;
worksheet.write_column(2, 3, data)?;
worksheet.write_column(2, 5, data)?;
worksheet.write_column(2, 7, data)?;
worksheet.write_column(2, 9, data)?;
worksheet.write_column(2, 11, data)?;
// Set the column widths for clarity.
for col_num in 0..=12u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write 2 color scale formats with standard Excel colors.
let conditional_format = ConditionalFormat2ColorScale::new()
.set_minimum_color("F8696B")
.set_maximum_color("FCFCFF");
worksheet.add_conditional_format(2, 1, 11, 1, &conditional_format)?;
let conditional_format = ConditionalFormat2ColorScale::new()
.set_minimum_color("FCFCFF")
.set_maximum_color("F8696B");
worksheet.add_conditional_format(2, 3, 11, 3, &conditional_format)?;
let conditional_format = ConditionalFormat2ColorScale::new()
.set_minimum_color("FCFCFF")
.set_maximum_color("63BE7B");
worksheet.add_conditional_format(2, 5, 11, 5, &conditional_format)?;
let conditional_format = ConditionalFormat2ColorScale::new()
.set_minimum_color("63BE7B")
.set_maximum_color("FCFCFF");
worksheet.add_conditional_format(2, 7, 11, 7, &conditional_format)?;
let conditional_format = ConditionalFormat2ColorScale::new()
.set_minimum_color("FFEF9C")
.set_maximum_color("63BE7B");
worksheet.add_conditional_format(2, 9, 11, 9, &conditional_format)?;
let conditional_format = ConditionalFormat2ColorScale::new()
.set_minimum_color("63BE7B")
.set_maximum_color("FFEF9C");
worksheet.add_conditional_format(2, 11, 11, 11, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 10. Examples of 3 color scale conditional formats.
// -----------------------------------------------------------------------
let caption = "Examples of 3 color scale conditional formats";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
let data = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10];
worksheet.write_column(2, 1, data)?;
worksheet.write_column(2, 3, data)?;
worksheet.write_column(2, 5, data)?;
worksheet.write_column(2, 7, data)?;
worksheet.write_column(2, 9, data)?;
worksheet.write_column(2, 11, data)?;
// Set the column widths for clarity.
for col_num in 0..=12u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write 3 color scale formats with standard Excel colors.
let conditional_format = ConditionalFormat3ColorScale::new()
.set_minimum_color("F8696B")
.set_midpoint_color("FFEB84")
.set_maximum_color("63BE7B");
worksheet.add_conditional_format(2, 1, 11, 1, &conditional_format)?;
let conditional_format = ConditionalFormat3ColorScale::new()
.set_minimum_color("63BE7B")
.set_midpoint_color("FFEB84")
.set_maximum_color("F8696B");
worksheet.add_conditional_format(2, 3, 11, 3, &conditional_format)?;
let conditional_format = ConditionalFormat3ColorScale::new()
.set_minimum_color("F8696B")
.set_midpoint_color("FCFCFF")
.set_maximum_color("63BE7B");
worksheet.add_conditional_format(2, 5, 11, 5, &conditional_format)?;
let conditional_format = ConditionalFormat3ColorScale::new()
.set_minimum_color("63BE7B")
.set_midpoint_color("FCFCFF")
.set_maximum_color("F8696B");
worksheet.add_conditional_format(2, 7, 11, 7, &conditional_format)?;
let conditional_format = ConditionalFormat3ColorScale::new()
.set_minimum_color("F8696B")
.set_midpoint_color("FCFCFF")
.set_maximum_color("5A8AC6");
worksheet.add_conditional_format(2, 9, 11, 9, &conditional_format)?;
let conditional_format = ConditionalFormat3ColorScale::new()
.set_minimum_color("5A8AC6")
.set_midpoint_color("FCFCFF")
.set_maximum_color("F8696B");
worksheet.add_conditional_format(2, 11, 11, 11, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 11. Examples of data bars.
// -----------------------------------------------------------------------
let caption = "Examples of data bars";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write_with_format(0, 1, caption, &bold)?;
worksheet.write(1, 1, "Default")?;
worksheet.write(1, 3, "Default negative")?;
worksheet.write(1, 5, "User color")?;
worksheet.write(1, 7, "Changed direction")?;
// Write the worksheet data.
let data1 = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10];
let data2 = [6, 4, 2, -2, -4, -6, -4, -2, 2, 4];
worksheet.write_column(2, 1, data1)?;
worksheet.write_column(2, 3, data2)?;
worksheet.write_column(2, 5, data1)?;
worksheet.write_column(2, 7, data1)?;
// Write a standard Excel data bar.
let conditional_format = ConditionalFormatDataBar::new();
worksheet.add_conditional_format(2, 1, 11, 1, &conditional_format)?;
// Write a standard Excel data bar with negative data
let conditional_format = ConditionalFormatDataBar::new();
worksheet.add_conditional_format(2, 3, 11, 3, &conditional_format)?;
// Write a data bar with a user defined fill color.
let conditional_format = ConditionalFormatDataBar::new().set_fill_color("009933");
worksheet.add_conditional_format(2, 5, 11, 5, &conditional_format)?;
// Write a data bar with the direction changed.
let conditional_format = ConditionalFormatDataBar::new()
.set_direction(ConditionalFormatDataBarDirection::RightToLeft);
worksheet.add_conditional_format(2, 7, 11, 7, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 12. Examples of icon style conditional formats.
// -----------------------------------------------------------------------
let caption = "Examples of icon style conditional formats.";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write_with_format(0, 0, caption, &bold)?;
worksheet.write_with_format(1, 0, "Three Traffic lights - Green is highest", &indent)?;
worksheet.write_with_format(2, 0, "Reversed - Red is highest", &indent)?;
worksheet.write_with_format(3, 0, "Icons only - The number data is hidden", &indent)?;
worksheet.write_with_format(4, 0, "Other three-five icon examples", &bold)?;
worksheet.write_with_format(5, 0, "Three arrows", &indent)?;
worksheet.write_with_format(6, 0, "Three symbols", &indent)?;
worksheet.write_with_format(7, 0, "Three stars", &indent)?;
worksheet.write_with_format(8, 0, "Four arrows", &indent)?;
worksheet.write_with_format(9, 0, "Four circles - Red (highest) to Black", &indent)?;
worksheet.write_with_format(10, 0, "Four rating histograms", &indent)?;
worksheet.write_with_format(11, 0, "Five arrows", &indent)?;
worksheet.write_with_format(12, 0, "Five rating histograms", &indent)?;
worksheet.write_with_format(13, 0, "Five rating quadrants", &indent)?;
// Set the column width for clarity.
worksheet.set_column_width(0, 35)?;
// Write the worksheet data.
worksheet.write_row(1, 1, [1, 2, 3])?;
worksheet.write_row(2, 1, [1, 2, 3])?;
worksheet.write_row(3, 1, [1, 2, 3])?;
worksheet.write_row(5, 1, [1, 2, 3])?;
worksheet.write_row(6, 1, [1, 2, 3])?;
worksheet.write_row(7, 1, [1, 2, 3])?;
worksheet.write_row(8, 1, [1, 2, 3, 4])?;
worksheet.write_row(9, 1, [1, 2, 3, 4])?;
worksheet.write_row(10, 1, [1, 2, 3, 4])?;
worksheet.write_row(11, 1, [1, 2, 3, 4, 5])?;
worksheet.write_row(12, 1, [1, 2, 3, 4, 5])?;
worksheet.write_row(13, 1, [1, 2, 3, 4, 5])?;
// Three Traffic lights - Green is highest.
let conditional_format = ConditionalFormatIconSet::new()
.set_icon_type(ConditionalFormatIconType::ThreeTrafficLights);
worksheet.add_conditional_format(1, 1, 1, 3, &conditional_format)?;
// Reversed - Red is highest.
let conditional_format = ConditionalFormatIconSet::new()
.set_icon_type(ConditionalFormatIconType::ThreeTrafficLights)
.reverse_icons(true);
worksheet.add_conditional_format(2, 1, 2, 3, &conditional_format)?;
// Icons only - The number data is hidden.
let conditional_format = ConditionalFormatIconSet::new()
.set_icon_type(ConditionalFormatIconType::ThreeTrafficLights)
.show_icons_only(true);
worksheet.add_conditional_format(3, 1, 3, 3, &conditional_format)?;
// Three arrows.
let conditional_format =
ConditionalFormatIconSet::new().set_icon_type(ConditionalFormatIconType::ThreeArrows);
worksheet.add_conditional_format(5, 1, 5, 3, &conditional_format)?;
// Three symbols.
let conditional_format = ConditionalFormatIconSet::new()
.set_icon_type(ConditionalFormatIconType::ThreeSymbolsCircled);
worksheet.add_conditional_format(6, 1, 6, 3, &conditional_format)?;
// Three stars.
let conditional_format =
ConditionalFormatIconSet::new().set_icon_type(ConditionalFormatIconType::ThreeStars);
worksheet.add_conditional_format(7, 1, 7, 3, &conditional_format)?;
// Four Arrows.
let conditional_format =
ConditionalFormatIconSet::new().set_icon_type(ConditionalFormatIconType::FourArrows);
worksheet.add_conditional_format(8, 1, 8, 4, &conditional_format)?;
// Four circles - Red (highest) to Black (lowest).
let conditional_format =
ConditionalFormatIconSet::new().set_icon_type(ConditionalFormatIconType::FourRedToBlack);
worksheet.add_conditional_format(9, 1, 9, 4, &conditional_format)?;
// Four rating histograms.
let conditional_format =
ConditionalFormatIconSet::new().set_icon_type(ConditionalFormatIconType::FourHistograms);
worksheet.add_conditional_format(10, 1, 10, 4, &conditional_format)?;
// Four Arrows.
let conditional_format =
ConditionalFormatIconSet::new().set_icon_type(ConditionalFormatIconType::FiveArrows);
worksheet.add_conditional_format(11, 1, 11, 5, &conditional_format)?;
// Four rating histograms.
let conditional_format =
ConditionalFormatIconSet::new().set_icon_type(ConditionalFormatIconType::FiveHistograms);
worksheet.add_conditional_format(12, 1, 12, 5, &conditional_format)?;
// Four rating quadrants.
let conditional_format =
ConditionalFormatIconSet::new().set_icon_type(ConditionalFormatIconType::FiveQuadrants);
worksheet.add_conditional_format(13, 1, 13, 5, &conditional_format)?;
// -----------------------------------------------------------------------
// Save and close the file.
// -----------------------------------------------------------------------
workbook.save("conditional_formats.xlsx")?;
Ok(())
}Example 5. Top and Bottom range conditional formats. Top 10 values are in light red. Bottom 10 values are in light green.
See ConditionalFormatTop for more details.
Code to generate the above example:
// SPDX-License-Identifier: MIT OR Apache-2.0
//
// Copyright 2022-2024, John McNamara, jmcnamara@cpan.org
//! Example of how to add conditional formatting to a worksheet using the
//! rust_xlsxwriter library.
//!
//! Conditional formatting allows you to apply a format to a cell or a range of
//! cells based on user defined rule.
use rust_xlsxwriter::{
ConditionalFormat2ColorScale, ConditionalFormat3ColorScale, ConditionalFormatAverage,
ConditionalFormatAverageRule, ConditionalFormatCell, ConditionalFormatCellRule,
ConditionalFormatDataBar, ConditionalFormatDataBarDirection, ConditionalFormatDuplicate,
ConditionalFormatFormula, ConditionalFormatIconSet, ConditionalFormatIconType,
ConditionalFormatText, ConditionalFormatTextRule, ConditionalFormatTop, Format, Workbook,
XlsxError,
};
fn main() -> Result<(), XlsxError> {
// Create a new Excel file object.
let mut workbook = Workbook::new();
// Add a format. Light red fill with dark red text.
let format1 = Format::new()
.set_font_color("9C0006")
.set_background_color("FFC7CE");
// Add a format. Green fill with dark green text.
let format2 = Format::new()
.set_font_color("006100")
.set_background_color("C6EFCE");
// Add a format for headers.
let bold = Format::new().set_bold();
// Add a format for descriptions.
let indent = Format::new().set_indent(2);
// some sample data to run the conditional formatting against.
let data = [
[34, 72, 38, 30, 75, 48, 75, 66, 84, 86],
[6, 24, 1, 84, 54, 62, 60, 3, 26, 59],
[28, 79, 97, 13, 85, 93, 93, 22, 5, 14],
[27, 71, 40, 17, 18, 79, 90, 93, 29, 47],
[88, 25, 33, 23, 67, 1, 59, 79, 47, 36],
[24, 100, 20, 88, 29, 33, 38, 54, 54, 88],
[6, 57, 88, 28, 10, 26, 37, 7, 41, 48],
[52, 78, 1, 96, 26, 45, 47, 33, 96, 36],
[60, 54, 81, 66, 81, 90, 80, 93, 12, 55],
[70, 5, 46, 14, 71, 19, 66, 36, 41, 21],
];
// -----------------------------------------------------------------------
// Worksheet 1. Cell conditional formatting.
// -----------------------------------------------------------------------
let caption = "Cells with values >= 50 are in light red. Values < 50 are in light green.";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
worksheet.write_row_matrix(2, 1, data)?;
// Set the column widths for clarity.
for col_num in 1..=10u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write a conditional format over a range.
let conditional_format = ConditionalFormatCell::new()
.set_rule(ConditionalFormatCellRule::GreaterThanOrEqualTo(50))
.set_format(&format1);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// Write another conditional format over the same range.
let conditional_format = ConditionalFormatCell::new()
.set_rule(ConditionalFormatCellRule::LessThan(50))
.set_format(&format2);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 2. Cell conditional formatting with between ranges.
// -----------------------------------------------------------------------
let caption =
"Values between 30 and 70 are in light red. Values outside that range are in light green.";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
worksheet.write_row_matrix(2, 1, data)?;
// Set the column widths for clarity.
for col_num in 1..=10u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write a conditional format over a range.
let conditional_format = ConditionalFormatCell::new()
.set_rule(ConditionalFormatCellRule::Between(30, 70))
.set_format(&format1);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// Write another conditional format over the same range.
let conditional_format = ConditionalFormatCell::new()
.set_rule(ConditionalFormatCellRule::NotBetween(30, 70))
.set_format(&format2);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 3. Duplicate and Unique conditional formats.
// -----------------------------------------------------------------------
let caption = "Duplicate values are in light red. Unique values are in light green.";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
worksheet.write_row_matrix(2, 1, data)?;
// Set the column widths for clarity.
for col_num in 1..=10u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write a conditional format over a range.
let conditional_format = ConditionalFormatDuplicate::new().set_format(&format1);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// Invert the duplicate conditional format to show unique values in the
// same range.
let conditional_format = ConditionalFormatDuplicate::new()
.invert()
.set_format(&format2);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 4. Above and Below Average conditional formats.
// -----------------------------------------------------------------------
let caption = "Above average values are in light red. Below average values are in light green.";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
worksheet.write_row_matrix(2, 1, data)?;
// Set the column widths for clarity.
for col_num in 1..=10u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write a conditional format over a range. The default criteria is Above Average.
let conditional_format = ConditionalFormatAverage::new().set_format(&format1);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// Write another conditional format over the same range.
let conditional_format = ConditionalFormatAverage::new()
.set_rule(ConditionalFormatAverageRule::BelowAverage)
.set_format(&format2);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 5. Top and Bottom range conditional formats.
// -----------------------------------------------------------------------
let caption = "Top 10 values are in light red. Bottom 10 values are in light green.";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
worksheet.write_row_matrix(2, 1, data)?;
// Set the column widths for clarity.
for col_num in 1..=10u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write a conditional format over a range.
let conditional_format = ConditionalFormatTop::new()
.set_rule(rust_xlsxwriter::ConditionalFormatTopRule::Top(10))
.set_format(&format1);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// Also show the bottom values in the same range.
let conditional_format = ConditionalFormatTop::new()
.set_rule(rust_xlsxwriter::ConditionalFormatTopRule::Bottom(10))
.set_format(&format2);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 6. Cell conditional formatting in non-contiguous range.
// -----------------------------------------------------------------------
let caption = "Cells with values >= 50 are in light red. Values < 50 are in light green. Non-contiguous ranges.";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
worksheet.write_row_matrix(2, 1, data)?;
// Set the column widths for clarity.
for col_num in 1..=10u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write a conditional format over a non-contiguous range.
let conditional_format = ConditionalFormatCell::new()
.set_rule(ConditionalFormatCellRule::GreaterThanOrEqualTo(50))
.set_multi_range("B3:D6 I3:K6 B9:D12 I9:K12")
.set_format(&format1);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// Write another conditional format over the same range.
let conditional_format = ConditionalFormatCell::new()
.set_rule(ConditionalFormatCellRule::LessThan(50))
.set_multi_range("B3:D6 I3:K6 B9:D12 I9:K12")
.set_format(&format2);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 7. Formula conditional formatting.
// -----------------------------------------------------------------------
let caption = "Even numbered cells are in light green. Odd numbered cells are in light red.";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
worksheet.write_row_matrix(2, 1, data)?;
// Set the column widths for clarity.
for col_num in 1..=10u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write a conditional format over a range.
let conditional_format = ConditionalFormatFormula::new()
.set_rule("=ISODD(B3)")
.set_format(&format1);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// Write another conditional format over the same range.
let conditional_format = ConditionalFormatFormula::new()
.set_rule("=ISEVEN(B3)")
.set_format(&format2);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 8. Text style conditional formats.
// -----------------------------------------------------------------------
let caption =
"Column A shows words that contain the sub-word 'rust'. Column C shows words that start/end with 't'";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 0, caption)?;
// Add some sample data.
let word_list = [
"apocrustic",
"burstwort",
"cloudburst",
"crustification",
"distrustfulness",
"laurustine",
"outburst",
"rusticism",
"thunderburst",
"trustee",
"trustworthiness",
"unburstableness",
"unfrustratable",
];
worksheet.write_column(1, 0, word_list)?;
worksheet.write_column(1, 2, word_list)?;
// Set the column widths for clarity.
worksheet.set_column_width(0, 20)?;
worksheet.set_column_width(2, 20)?;
// Write a text "containing" conditional format over a range.
let conditional_format = ConditionalFormatText::new()
.set_rule(ConditionalFormatTextRule::Contains("rust".to_string()))
.set_format(&format2);
worksheet.add_conditional_format(1, 0, 13, 0, &conditional_format)?;
// Write a text "not containing" conditional format over the same range.
let conditional_format = ConditionalFormatText::new()
.set_rule(ConditionalFormatTextRule::DoesNotContain(
"rust".to_string(),
))
.set_format(&format1);
worksheet.add_conditional_format(1, 0, 13, 0, &conditional_format)?;
// Write a text "begins with" conditional format over a range.
let conditional_format = ConditionalFormatText::new()
.set_rule(ConditionalFormatTextRule::BeginsWith("t".to_string()))
.set_format(&format2);
worksheet.add_conditional_format(1, 2, 13, 2, &conditional_format)?;
// Write a text "ends with" conditional format over the same range.
let conditional_format = ConditionalFormatText::new()
.set_rule(ConditionalFormatTextRule::EndsWith("t".to_string()))
.set_format(&format1);
worksheet.add_conditional_format(1, 2, 13, 2, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 9. Examples of 2 color scale conditional formats.
// -----------------------------------------------------------------------
let caption = "Examples of 2 color scale conditional formats";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
let data = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10];
worksheet.write_column(2, 1, data)?;
worksheet.write_column(2, 3, data)?;
worksheet.write_column(2, 5, data)?;
worksheet.write_column(2, 7, data)?;
worksheet.write_column(2, 9, data)?;
worksheet.write_column(2, 11, data)?;
// Set the column widths for clarity.
for col_num in 0..=12u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write 2 color scale formats with standard Excel colors.
let conditional_format = ConditionalFormat2ColorScale::new()
.set_minimum_color("F8696B")
.set_maximum_color("FCFCFF");
worksheet.add_conditional_format(2, 1, 11, 1, &conditional_format)?;
let conditional_format = ConditionalFormat2ColorScale::new()
.set_minimum_color("FCFCFF")
.set_maximum_color("F8696B");
worksheet.add_conditional_format(2, 3, 11, 3, &conditional_format)?;
let conditional_format = ConditionalFormat2ColorScale::new()
.set_minimum_color("FCFCFF")
.set_maximum_color("63BE7B");
worksheet.add_conditional_format(2, 5, 11, 5, &conditional_format)?;
let conditional_format = ConditionalFormat2ColorScale::new()
.set_minimum_color("63BE7B")
.set_maximum_color("FCFCFF");
worksheet.add_conditional_format(2, 7, 11, 7, &conditional_format)?;
let conditional_format = ConditionalFormat2ColorScale::new()
.set_minimum_color("FFEF9C")
.set_maximum_color("63BE7B");
worksheet.add_conditional_format(2, 9, 11, 9, &conditional_format)?;
let conditional_format = ConditionalFormat2ColorScale::new()
.set_minimum_color("63BE7B")
.set_maximum_color("FFEF9C");
worksheet.add_conditional_format(2, 11, 11, 11, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 10. Examples of 3 color scale conditional formats.
// -----------------------------------------------------------------------
let caption = "Examples of 3 color scale conditional formats";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
let data = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10];
worksheet.write_column(2, 1, data)?;
worksheet.write_column(2, 3, data)?;
worksheet.write_column(2, 5, data)?;
worksheet.write_column(2, 7, data)?;
worksheet.write_column(2, 9, data)?;
worksheet.write_column(2, 11, data)?;
// Set the column widths for clarity.
for col_num in 0..=12u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write 3 color scale formats with standard Excel colors.
let conditional_format = ConditionalFormat3ColorScale::new()
.set_minimum_color("F8696B")
.set_midpoint_color("FFEB84")
.set_maximum_color("63BE7B");
worksheet.add_conditional_format(2, 1, 11, 1, &conditional_format)?;
let conditional_format = ConditionalFormat3ColorScale::new()
.set_minimum_color("63BE7B")
.set_midpoint_color("FFEB84")
.set_maximum_color("F8696B");
worksheet.add_conditional_format(2, 3, 11, 3, &conditional_format)?;
let conditional_format = ConditionalFormat3ColorScale::new()
.set_minimum_color("F8696B")
.set_midpoint_color("FCFCFF")
.set_maximum_color("63BE7B");
worksheet.add_conditional_format(2, 5, 11, 5, &conditional_format)?;
let conditional_format = ConditionalFormat3ColorScale::new()
.set_minimum_color("63BE7B")
.set_midpoint_color("FCFCFF")
.set_maximum_color("F8696B");
worksheet.add_conditional_format(2, 7, 11, 7, &conditional_format)?;
let conditional_format = ConditionalFormat3ColorScale::new()
.set_minimum_color("F8696B")
.set_midpoint_color("FCFCFF")
.set_maximum_color("5A8AC6");
worksheet.add_conditional_format(2, 9, 11, 9, &conditional_format)?;
let conditional_format = ConditionalFormat3ColorScale::new()
.set_minimum_color("5A8AC6")
.set_midpoint_color("FCFCFF")
.set_maximum_color("F8696B");
worksheet.add_conditional_format(2, 11, 11, 11, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 11. Examples of data bars.
// -----------------------------------------------------------------------
let caption = "Examples of data bars";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write_with_format(0, 1, caption, &bold)?;
worksheet.write(1, 1, "Default")?;
worksheet.write(1, 3, "Default negative")?;
worksheet.write(1, 5, "User color")?;
worksheet.write(1, 7, "Changed direction")?;
// Write the worksheet data.
let data1 = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10];
let data2 = [6, 4, 2, -2, -4, -6, -4, -2, 2, 4];
worksheet.write_column(2, 1, data1)?;
worksheet.write_column(2, 3, data2)?;
worksheet.write_column(2, 5, data1)?;
worksheet.write_column(2, 7, data1)?;
// Write a standard Excel data bar.
let conditional_format = ConditionalFormatDataBar::new();
worksheet.add_conditional_format(2, 1, 11, 1, &conditional_format)?;
// Write a standard Excel data bar with negative data
let conditional_format = ConditionalFormatDataBar::new();
worksheet.add_conditional_format(2, 3, 11, 3, &conditional_format)?;
// Write a data bar with a user defined fill color.
let conditional_format = ConditionalFormatDataBar::new().set_fill_color("009933");
worksheet.add_conditional_format(2, 5, 11, 5, &conditional_format)?;
// Write a data bar with the direction changed.
let conditional_format = ConditionalFormatDataBar::new()
.set_direction(ConditionalFormatDataBarDirection::RightToLeft);
worksheet.add_conditional_format(2, 7, 11, 7, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 12. Examples of icon style conditional formats.
// -----------------------------------------------------------------------
let caption = "Examples of icon style conditional formats.";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write_with_format(0, 0, caption, &bold)?;
worksheet.write_with_format(1, 0, "Three Traffic lights - Green is highest", &indent)?;
worksheet.write_with_format(2, 0, "Reversed - Red is highest", &indent)?;
worksheet.write_with_format(3, 0, "Icons only - The number data is hidden", &indent)?;
worksheet.write_with_format(4, 0, "Other three-five icon examples", &bold)?;
worksheet.write_with_format(5, 0, "Three arrows", &indent)?;
worksheet.write_with_format(6, 0, "Three symbols", &indent)?;
worksheet.write_with_format(7, 0, "Three stars", &indent)?;
worksheet.write_with_format(8, 0, "Four arrows", &indent)?;
worksheet.write_with_format(9, 0, "Four circles - Red (highest) to Black", &indent)?;
worksheet.write_with_format(10, 0, "Four rating histograms", &indent)?;
worksheet.write_with_format(11, 0, "Five arrows", &indent)?;
worksheet.write_with_format(12, 0, "Five rating histograms", &indent)?;
worksheet.write_with_format(13, 0, "Five rating quadrants", &indent)?;
// Set the column width for clarity.
worksheet.set_column_width(0, 35)?;
// Write the worksheet data.
worksheet.write_row(1, 1, [1, 2, 3])?;
worksheet.write_row(2, 1, [1, 2, 3])?;
worksheet.write_row(3, 1, [1, 2, 3])?;
worksheet.write_row(5, 1, [1, 2, 3])?;
worksheet.write_row(6, 1, [1, 2, 3])?;
worksheet.write_row(7, 1, [1, 2, 3])?;
worksheet.write_row(8, 1, [1, 2, 3, 4])?;
worksheet.write_row(9, 1, [1, 2, 3, 4])?;
worksheet.write_row(10, 1, [1, 2, 3, 4])?;
worksheet.write_row(11, 1, [1, 2, 3, 4, 5])?;
worksheet.write_row(12, 1, [1, 2, 3, 4, 5])?;
worksheet.write_row(13, 1, [1, 2, 3, 4, 5])?;
// Three Traffic lights - Green is highest.
let conditional_format = ConditionalFormatIconSet::new()
.set_icon_type(ConditionalFormatIconType::ThreeTrafficLights);
worksheet.add_conditional_format(1, 1, 1, 3, &conditional_format)?;
// Reversed - Red is highest.
let conditional_format = ConditionalFormatIconSet::new()
.set_icon_type(ConditionalFormatIconType::ThreeTrafficLights)
.reverse_icons(true);
worksheet.add_conditional_format(2, 1, 2, 3, &conditional_format)?;
// Icons only - The number data is hidden.
let conditional_format = ConditionalFormatIconSet::new()
.set_icon_type(ConditionalFormatIconType::ThreeTrafficLights)
.show_icons_only(true);
worksheet.add_conditional_format(3, 1, 3, 3, &conditional_format)?;
// Three arrows.
let conditional_format =
ConditionalFormatIconSet::new().set_icon_type(ConditionalFormatIconType::ThreeArrows);
worksheet.add_conditional_format(5, 1, 5, 3, &conditional_format)?;
// Three symbols.
let conditional_format = ConditionalFormatIconSet::new()
.set_icon_type(ConditionalFormatIconType::ThreeSymbolsCircled);
worksheet.add_conditional_format(6, 1, 6, 3, &conditional_format)?;
// Three stars.
let conditional_format =
ConditionalFormatIconSet::new().set_icon_type(ConditionalFormatIconType::ThreeStars);
worksheet.add_conditional_format(7, 1, 7, 3, &conditional_format)?;
// Four Arrows.
let conditional_format =
ConditionalFormatIconSet::new().set_icon_type(ConditionalFormatIconType::FourArrows);
worksheet.add_conditional_format(8, 1, 8, 4, &conditional_format)?;
// Four circles - Red (highest) to Black (lowest).
let conditional_format =
ConditionalFormatIconSet::new().set_icon_type(ConditionalFormatIconType::FourRedToBlack);
worksheet.add_conditional_format(9, 1, 9, 4, &conditional_format)?;
// Four rating histograms.
let conditional_format =
ConditionalFormatIconSet::new().set_icon_type(ConditionalFormatIconType::FourHistograms);
worksheet.add_conditional_format(10, 1, 10, 4, &conditional_format)?;
// Four Arrows.
let conditional_format =
ConditionalFormatIconSet::new().set_icon_type(ConditionalFormatIconType::FiveArrows);
worksheet.add_conditional_format(11, 1, 11, 5, &conditional_format)?;
// Four rating histograms.
let conditional_format =
ConditionalFormatIconSet::new().set_icon_type(ConditionalFormatIconType::FiveHistograms);
worksheet.add_conditional_format(12, 1, 12, 5, &conditional_format)?;
// Four rating quadrants.
let conditional_format =
ConditionalFormatIconSet::new().set_icon_type(ConditionalFormatIconType::FiveQuadrants);
worksheet.add_conditional_format(13, 1, 13, 5, &conditional_format)?;
// -----------------------------------------------------------------------
// Save and close the file.
// -----------------------------------------------------------------------
workbook.save("conditional_formats.xlsx")?;
Ok(())
}Example 6. Cell conditional formatting in non-contiguous range. Cells with values >= 50 are in light red. Values < 50 are in light green. Non-contiguous ranges.
Code to generate the above example:
// SPDX-License-Identifier: MIT OR Apache-2.0
//
// Copyright 2022-2024, John McNamara, jmcnamara@cpan.org
//! Example of how to add conditional formatting to a worksheet using the
//! rust_xlsxwriter library.
//!
//! Conditional formatting allows you to apply a format to a cell or a range of
//! cells based on user defined rule.
use rust_xlsxwriter::{
ConditionalFormat2ColorScale, ConditionalFormat3ColorScale, ConditionalFormatAverage,
ConditionalFormatAverageRule, ConditionalFormatCell, ConditionalFormatCellRule,
ConditionalFormatDataBar, ConditionalFormatDataBarDirection, ConditionalFormatDuplicate,
ConditionalFormatFormula, ConditionalFormatIconSet, ConditionalFormatIconType,
ConditionalFormatText, ConditionalFormatTextRule, ConditionalFormatTop, Format, Workbook,
XlsxError,
};
fn main() -> Result<(), XlsxError> {
// Create a new Excel file object.
let mut workbook = Workbook::new();
// Add a format. Light red fill with dark red text.
let format1 = Format::new()
.set_font_color("9C0006")
.set_background_color("FFC7CE");
// Add a format. Green fill with dark green text.
let format2 = Format::new()
.set_font_color("006100")
.set_background_color("C6EFCE");
// Add a format for headers.
let bold = Format::new().set_bold();
// Add a format for descriptions.
let indent = Format::new().set_indent(2);
// some sample data to run the conditional formatting against.
let data = [
[34, 72, 38, 30, 75, 48, 75, 66, 84, 86],
[6, 24, 1, 84, 54, 62, 60, 3, 26, 59],
[28, 79, 97, 13, 85, 93, 93, 22, 5, 14],
[27, 71, 40, 17, 18, 79, 90, 93, 29, 47],
[88, 25, 33, 23, 67, 1, 59, 79, 47, 36],
[24, 100, 20, 88, 29, 33, 38, 54, 54, 88],
[6, 57, 88, 28, 10, 26, 37, 7, 41, 48],
[52, 78, 1, 96, 26, 45, 47, 33, 96, 36],
[60, 54, 81, 66, 81, 90, 80, 93, 12, 55],
[70, 5, 46, 14, 71, 19, 66, 36, 41, 21],
];
// -----------------------------------------------------------------------
// Worksheet 1. Cell conditional formatting.
// -----------------------------------------------------------------------
let caption = "Cells with values >= 50 are in light red. Values < 50 are in light green.";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
worksheet.write_row_matrix(2, 1, data)?;
// Set the column widths for clarity.
for col_num in 1..=10u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write a conditional format over a range.
let conditional_format = ConditionalFormatCell::new()
.set_rule(ConditionalFormatCellRule::GreaterThanOrEqualTo(50))
.set_format(&format1);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// Write another conditional format over the same range.
let conditional_format = ConditionalFormatCell::new()
.set_rule(ConditionalFormatCellRule::LessThan(50))
.set_format(&format2);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 2. Cell conditional formatting with between ranges.
// -----------------------------------------------------------------------
let caption =
"Values between 30 and 70 are in light red. Values outside that range are in light green.";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
worksheet.write_row_matrix(2, 1, data)?;
// Set the column widths for clarity.
for col_num in 1..=10u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write a conditional format over a range.
let conditional_format = ConditionalFormatCell::new()
.set_rule(ConditionalFormatCellRule::Between(30, 70))
.set_format(&format1);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// Write another conditional format over the same range.
let conditional_format = ConditionalFormatCell::new()
.set_rule(ConditionalFormatCellRule::NotBetween(30, 70))
.set_format(&format2);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 3. Duplicate and Unique conditional formats.
// -----------------------------------------------------------------------
let caption = "Duplicate values are in light red. Unique values are in light green.";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
worksheet.write_row_matrix(2, 1, data)?;
// Set the column widths for clarity.
for col_num in 1..=10u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write a conditional format over a range.
let conditional_format = ConditionalFormatDuplicate::new().set_format(&format1);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// Invert the duplicate conditional format to show unique values in the
// same range.
let conditional_format = ConditionalFormatDuplicate::new()
.invert()
.set_format(&format2);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 4. Above and Below Average conditional formats.
// -----------------------------------------------------------------------
let caption = "Above average values are in light red. Below average values are in light green.";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
worksheet.write_row_matrix(2, 1, data)?;
// Set the column widths for clarity.
for col_num in 1..=10u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write a conditional format over a range. The default criteria is Above Average.
let conditional_format = ConditionalFormatAverage::new().set_format(&format1);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// Write another conditional format over the same range.
let conditional_format = ConditionalFormatAverage::new()
.set_rule(ConditionalFormatAverageRule::BelowAverage)
.set_format(&format2);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 5. Top and Bottom range conditional formats.
// -----------------------------------------------------------------------
let caption = "Top 10 values are in light red. Bottom 10 values are in light green.";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
worksheet.write_row_matrix(2, 1, data)?;
// Set the column widths for clarity.
for col_num in 1..=10u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write a conditional format over a range.
let conditional_format = ConditionalFormatTop::new()
.set_rule(rust_xlsxwriter::ConditionalFormatTopRule::Top(10))
.set_format(&format1);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// Also show the bottom values in the same range.
let conditional_format = ConditionalFormatTop::new()
.set_rule(rust_xlsxwriter::ConditionalFormatTopRule::Bottom(10))
.set_format(&format2);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 6. Cell conditional formatting in non-contiguous range.
// -----------------------------------------------------------------------
let caption = "Cells with values >= 50 are in light red. Values < 50 are in light green. Non-contiguous ranges.";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
worksheet.write_row_matrix(2, 1, data)?;
// Set the column widths for clarity.
for col_num in 1..=10u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write a conditional format over a non-contiguous range.
let conditional_format = ConditionalFormatCell::new()
.set_rule(ConditionalFormatCellRule::GreaterThanOrEqualTo(50))
.set_multi_range("B3:D6 I3:K6 B9:D12 I9:K12")
.set_format(&format1);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// Write another conditional format over the same range.
let conditional_format = ConditionalFormatCell::new()
.set_rule(ConditionalFormatCellRule::LessThan(50))
.set_multi_range("B3:D6 I3:K6 B9:D12 I9:K12")
.set_format(&format2);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 7. Formula conditional formatting.
// -----------------------------------------------------------------------
let caption = "Even numbered cells are in light green. Odd numbered cells are in light red.";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
worksheet.write_row_matrix(2, 1, data)?;
// Set the column widths for clarity.
for col_num in 1..=10u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write a conditional format over a range.
let conditional_format = ConditionalFormatFormula::new()
.set_rule("=ISODD(B3)")
.set_format(&format1);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// Write another conditional format over the same range.
let conditional_format = ConditionalFormatFormula::new()
.set_rule("=ISEVEN(B3)")
.set_format(&format2);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 8. Text style conditional formats.
// -----------------------------------------------------------------------
let caption =
"Column A shows words that contain the sub-word 'rust'. Column C shows words that start/end with 't'";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 0, caption)?;
// Add some sample data.
let word_list = [
"apocrustic",
"burstwort",
"cloudburst",
"crustification",
"distrustfulness",
"laurustine",
"outburst",
"rusticism",
"thunderburst",
"trustee",
"trustworthiness",
"unburstableness",
"unfrustratable",
];
worksheet.write_column(1, 0, word_list)?;
worksheet.write_column(1, 2, word_list)?;
// Set the column widths for clarity.
worksheet.set_column_width(0, 20)?;
worksheet.set_column_width(2, 20)?;
// Write a text "containing" conditional format over a range.
let conditional_format = ConditionalFormatText::new()
.set_rule(ConditionalFormatTextRule::Contains("rust".to_string()))
.set_format(&format2);
worksheet.add_conditional_format(1, 0, 13, 0, &conditional_format)?;
// Write a text "not containing" conditional format over the same range.
let conditional_format = ConditionalFormatText::new()
.set_rule(ConditionalFormatTextRule::DoesNotContain(
"rust".to_string(),
))
.set_format(&format1);
worksheet.add_conditional_format(1, 0, 13, 0, &conditional_format)?;
// Write a text "begins with" conditional format over a range.
let conditional_format = ConditionalFormatText::new()
.set_rule(ConditionalFormatTextRule::BeginsWith("t".to_string()))
.set_format(&format2);
worksheet.add_conditional_format(1, 2, 13, 2, &conditional_format)?;
// Write a text "ends with" conditional format over the same range.
let conditional_format = ConditionalFormatText::new()
.set_rule(ConditionalFormatTextRule::EndsWith("t".to_string()))
.set_format(&format1);
worksheet.add_conditional_format(1, 2, 13, 2, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 9. Examples of 2 color scale conditional formats.
// -----------------------------------------------------------------------
let caption = "Examples of 2 color scale conditional formats";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
let data = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10];
worksheet.write_column(2, 1, data)?;
worksheet.write_column(2, 3, data)?;
worksheet.write_column(2, 5, data)?;
worksheet.write_column(2, 7, data)?;
worksheet.write_column(2, 9, data)?;
worksheet.write_column(2, 11, data)?;
// Set the column widths for clarity.
for col_num in 0..=12u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write 2 color scale formats with standard Excel colors.
let conditional_format = ConditionalFormat2ColorScale::new()
.set_minimum_color("F8696B")
.set_maximum_color("FCFCFF");
worksheet.add_conditional_format(2, 1, 11, 1, &conditional_format)?;
let conditional_format = ConditionalFormat2ColorScale::new()
.set_minimum_color("FCFCFF")
.set_maximum_color("F8696B");
worksheet.add_conditional_format(2, 3, 11, 3, &conditional_format)?;
let conditional_format = ConditionalFormat2ColorScale::new()
.set_minimum_color("FCFCFF")
.set_maximum_color("63BE7B");
worksheet.add_conditional_format(2, 5, 11, 5, &conditional_format)?;
let conditional_format = ConditionalFormat2ColorScale::new()
.set_minimum_color("63BE7B")
.set_maximum_color("FCFCFF");
worksheet.add_conditional_format(2, 7, 11, 7, &conditional_format)?;
let conditional_format = ConditionalFormat2ColorScale::new()
.set_minimum_color("FFEF9C")
.set_maximum_color("63BE7B");
worksheet.add_conditional_format(2, 9, 11, 9, &conditional_format)?;
let conditional_format = ConditionalFormat2ColorScale::new()
.set_minimum_color("63BE7B")
.set_maximum_color("FFEF9C");
worksheet.add_conditional_format(2, 11, 11, 11, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 10. Examples of 3 color scale conditional formats.
// -----------------------------------------------------------------------
let caption = "Examples of 3 color scale conditional formats";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
let data = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10];
worksheet.write_column(2, 1, data)?;
worksheet.write_column(2, 3, data)?;
worksheet.write_column(2, 5, data)?;
worksheet.write_column(2, 7, data)?;
worksheet.write_column(2, 9, data)?;
worksheet.write_column(2, 11, data)?;
// Set the column widths for clarity.
for col_num in 0..=12u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write 3 color scale formats with standard Excel colors.
let conditional_format = ConditionalFormat3ColorScale::new()
.set_minimum_color("F8696B")
.set_midpoint_color("FFEB84")
.set_maximum_color("63BE7B");
worksheet.add_conditional_format(2, 1, 11, 1, &conditional_format)?;
let conditional_format = ConditionalFormat3ColorScale::new()
.set_minimum_color("63BE7B")
.set_midpoint_color("FFEB84")
.set_maximum_color("F8696B");
worksheet.add_conditional_format(2, 3, 11, 3, &conditional_format)?;
let conditional_format = ConditionalFormat3ColorScale::new()
.set_minimum_color("F8696B")
.set_midpoint_color("FCFCFF")
.set_maximum_color("63BE7B");
worksheet.add_conditional_format(2, 5, 11, 5, &conditional_format)?;
let conditional_format = ConditionalFormat3ColorScale::new()
.set_minimum_color("63BE7B")
.set_midpoint_color("FCFCFF")
.set_maximum_color("F8696B");
worksheet.add_conditional_format(2, 7, 11, 7, &conditional_format)?;
let conditional_format = ConditionalFormat3ColorScale::new()
.set_minimum_color("F8696B")
.set_midpoint_color("FCFCFF")
.set_maximum_color("5A8AC6");
worksheet.add_conditional_format(2, 9, 11, 9, &conditional_format)?;
let conditional_format = ConditionalFormat3ColorScale::new()
.set_minimum_color("5A8AC6")
.set_midpoint_color("FCFCFF")
.set_maximum_color("F8696B");
worksheet.add_conditional_format(2, 11, 11, 11, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 11. Examples of data bars.
// -----------------------------------------------------------------------
let caption = "Examples of data bars";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write_with_format(0, 1, caption, &bold)?;
worksheet.write(1, 1, "Default")?;
worksheet.write(1, 3, "Default negative")?;
worksheet.write(1, 5, "User color")?;
worksheet.write(1, 7, "Changed direction")?;
// Write the worksheet data.
let data1 = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10];
let data2 = [6, 4, 2, -2, -4, -6, -4, -2, 2, 4];
worksheet.write_column(2, 1, data1)?;
worksheet.write_column(2, 3, data2)?;
worksheet.write_column(2, 5, data1)?;
worksheet.write_column(2, 7, data1)?;
// Write a standard Excel data bar.
let conditional_format = ConditionalFormatDataBar::new();
worksheet.add_conditional_format(2, 1, 11, 1, &conditional_format)?;
// Write a standard Excel data bar with negative data
let conditional_format = ConditionalFormatDataBar::new();
worksheet.add_conditional_format(2, 3, 11, 3, &conditional_format)?;
// Write a data bar with a user defined fill color.
let conditional_format = ConditionalFormatDataBar::new().set_fill_color("009933");
worksheet.add_conditional_format(2, 5, 11, 5, &conditional_format)?;
// Write a data bar with the direction changed.
let conditional_format = ConditionalFormatDataBar::new()
.set_direction(ConditionalFormatDataBarDirection::RightToLeft);
worksheet.add_conditional_format(2, 7, 11, 7, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 12. Examples of icon style conditional formats.
// -----------------------------------------------------------------------
let caption = "Examples of icon style conditional formats.";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write_with_format(0, 0, caption, &bold)?;
worksheet.write_with_format(1, 0, "Three Traffic lights - Green is highest", &indent)?;
worksheet.write_with_format(2, 0, "Reversed - Red is highest", &indent)?;
worksheet.write_with_format(3, 0, "Icons only - The number data is hidden", &indent)?;
worksheet.write_with_format(4, 0, "Other three-five icon examples", &bold)?;
worksheet.write_with_format(5, 0, "Three arrows", &indent)?;
worksheet.write_with_format(6, 0, "Three symbols", &indent)?;
worksheet.write_with_format(7, 0, "Three stars", &indent)?;
worksheet.write_with_format(8, 0, "Four arrows", &indent)?;
worksheet.write_with_format(9, 0, "Four circles - Red (highest) to Black", &indent)?;
worksheet.write_with_format(10, 0, "Four rating histograms", &indent)?;
worksheet.write_with_format(11, 0, "Five arrows", &indent)?;
worksheet.write_with_format(12, 0, "Five rating histograms", &indent)?;
worksheet.write_with_format(13, 0, "Five rating quadrants", &indent)?;
// Set the column width for clarity.
worksheet.set_column_width(0, 35)?;
// Write the worksheet data.
worksheet.write_row(1, 1, [1, 2, 3])?;
worksheet.write_row(2, 1, [1, 2, 3])?;
worksheet.write_row(3, 1, [1, 2, 3])?;
worksheet.write_row(5, 1, [1, 2, 3])?;
worksheet.write_row(6, 1, [1, 2, 3])?;
worksheet.write_row(7, 1, [1, 2, 3])?;
worksheet.write_row(8, 1, [1, 2, 3, 4])?;
worksheet.write_row(9, 1, [1, 2, 3, 4])?;
worksheet.write_row(10, 1, [1, 2, 3, 4])?;
worksheet.write_row(11, 1, [1, 2, 3, 4, 5])?;
worksheet.write_row(12, 1, [1, 2, 3, 4, 5])?;
worksheet.write_row(13, 1, [1, 2, 3, 4, 5])?;
// Three Traffic lights - Green is highest.
let conditional_format = ConditionalFormatIconSet::new()
.set_icon_type(ConditionalFormatIconType::ThreeTrafficLights);
worksheet.add_conditional_format(1, 1, 1, 3, &conditional_format)?;
// Reversed - Red is highest.
let conditional_format = ConditionalFormatIconSet::new()
.set_icon_type(ConditionalFormatIconType::ThreeTrafficLights)
.reverse_icons(true);
worksheet.add_conditional_format(2, 1, 2, 3, &conditional_format)?;
// Icons only - The number data is hidden.
let conditional_format = ConditionalFormatIconSet::new()
.set_icon_type(ConditionalFormatIconType::ThreeTrafficLights)
.show_icons_only(true);
worksheet.add_conditional_format(3, 1, 3, 3, &conditional_format)?;
// Three arrows.
let conditional_format =
ConditionalFormatIconSet::new().set_icon_type(ConditionalFormatIconType::ThreeArrows);
worksheet.add_conditional_format(5, 1, 5, 3, &conditional_format)?;
// Three symbols.
let conditional_format = ConditionalFormatIconSet::new()
.set_icon_type(ConditionalFormatIconType::ThreeSymbolsCircled);
worksheet.add_conditional_format(6, 1, 6, 3, &conditional_format)?;
// Three stars.
let conditional_format =
ConditionalFormatIconSet::new().set_icon_type(ConditionalFormatIconType::ThreeStars);
worksheet.add_conditional_format(7, 1, 7, 3, &conditional_format)?;
// Four Arrows.
let conditional_format =
ConditionalFormatIconSet::new().set_icon_type(ConditionalFormatIconType::FourArrows);
worksheet.add_conditional_format(8, 1, 8, 4, &conditional_format)?;
// Four circles - Red (highest) to Black (lowest).
let conditional_format =
ConditionalFormatIconSet::new().set_icon_type(ConditionalFormatIconType::FourRedToBlack);
worksheet.add_conditional_format(9, 1, 9, 4, &conditional_format)?;
// Four rating histograms.
let conditional_format =
ConditionalFormatIconSet::new().set_icon_type(ConditionalFormatIconType::FourHistograms);
worksheet.add_conditional_format(10, 1, 10, 4, &conditional_format)?;
// Four Arrows.
let conditional_format =
ConditionalFormatIconSet::new().set_icon_type(ConditionalFormatIconType::FiveArrows);
worksheet.add_conditional_format(11, 1, 11, 5, &conditional_format)?;
// Four rating histograms.
let conditional_format =
ConditionalFormatIconSet::new().set_icon_type(ConditionalFormatIconType::FiveHistograms);
worksheet.add_conditional_format(12, 1, 12, 5, &conditional_format)?;
// Four rating quadrants.
let conditional_format =
ConditionalFormatIconSet::new().set_icon_type(ConditionalFormatIconType::FiveQuadrants);
worksheet.add_conditional_format(13, 1, 13, 5, &conditional_format)?;
// -----------------------------------------------------------------------
// Save and close the file.
// -----------------------------------------------------------------------
workbook.save("conditional_formats.xlsx")?;
Ok(())
}Example 7. Formula conditional formatting. Even numbered cells are in light green. Odd numbered cells are in light red.
See ConditionalFormatFormula for more details.
Code to generate the above example:
// SPDX-License-Identifier: MIT OR Apache-2.0
//
// Copyright 2022-2024, John McNamara, jmcnamara@cpan.org
//! Example of how to add conditional formatting to a worksheet using the
//! rust_xlsxwriter library.
//!
//! Conditional formatting allows you to apply a format to a cell or a range of
//! cells based on user defined rule.
use rust_xlsxwriter::{
ConditionalFormat2ColorScale, ConditionalFormat3ColorScale, ConditionalFormatAverage,
ConditionalFormatAverageRule, ConditionalFormatCell, ConditionalFormatCellRule,
ConditionalFormatDataBar, ConditionalFormatDataBarDirection, ConditionalFormatDuplicate,
ConditionalFormatFormula, ConditionalFormatIconSet, ConditionalFormatIconType,
ConditionalFormatText, ConditionalFormatTextRule, ConditionalFormatTop, Format, Workbook,
XlsxError,
};
fn main() -> Result<(), XlsxError> {
// Create a new Excel file object.
let mut workbook = Workbook::new();
// Add a format. Light red fill with dark red text.
let format1 = Format::new()
.set_font_color("9C0006")
.set_background_color("FFC7CE");
// Add a format. Green fill with dark green text.
let format2 = Format::new()
.set_font_color("006100")
.set_background_color("C6EFCE");
// Add a format for headers.
let bold = Format::new().set_bold();
// Add a format for descriptions.
let indent = Format::new().set_indent(2);
// some sample data to run the conditional formatting against.
let data = [
[34, 72, 38, 30, 75, 48, 75, 66, 84, 86],
[6, 24, 1, 84, 54, 62, 60, 3, 26, 59],
[28, 79, 97, 13, 85, 93, 93, 22, 5, 14],
[27, 71, 40, 17, 18, 79, 90, 93, 29, 47],
[88, 25, 33, 23, 67, 1, 59, 79, 47, 36],
[24, 100, 20, 88, 29, 33, 38, 54, 54, 88],
[6, 57, 88, 28, 10, 26, 37, 7, 41, 48],
[52, 78, 1, 96, 26, 45, 47, 33, 96, 36],
[60, 54, 81, 66, 81, 90, 80, 93, 12, 55],
[70, 5, 46, 14, 71, 19, 66, 36, 41, 21],
];
// -----------------------------------------------------------------------
// Worksheet 1. Cell conditional formatting.
// -----------------------------------------------------------------------
let caption = "Cells with values >= 50 are in light red. Values < 50 are in light green.";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
worksheet.write_row_matrix(2, 1, data)?;
// Set the column widths for clarity.
for col_num in 1..=10u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write a conditional format over a range.
let conditional_format = ConditionalFormatCell::new()
.set_rule(ConditionalFormatCellRule::GreaterThanOrEqualTo(50))
.set_format(&format1);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// Write another conditional format over the same range.
let conditional_format = ConditionalFormatCell::new()
.set_rule(ConditionalFormatCellRule::LessThan(50))
.set_format(&format2);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 2. Cell conditional formatting with between ranges.
// -----------------------------------------------------------------------
let caption =
"Values between 30 and 70 are in light red. Values outside that range are in light green.";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
worksheet.write_row_matrix(2, 1, data)?;
// Set the column widths for clarity.
for col_num in 1..=10u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write a conditional format over a range.
let conditional_format = ConditionalFormatCell::new()
.set_rule(ConditionalFormatCellRule::Between(30, 70))
.set_format(&format1);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// Write another conditional format over the same range.
let conditional_format = ConditionalFormatCell::new()
.set_rule(ConditionalFormatCellRule::NotBetween(30, 70))
.set_format(&format2);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 3. Duplicate and Unique conditional formats.
// -----------------------------------------------------------------------
let caption = "Duplicate values are in light red. Unique values are in light green.";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
worksheet.write_row_matrix(2, 1, data)?;
// Set the column widths for clarity.
for col_num in 1..=10u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write a conditional format over a range.
let conditional_format = ConditionalFormatDuplicate::new().set_format(&format1);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// Invert the duplicate conditional format to show unique values in the
// same range.
let conditional_format = ConditionalFormatDuplicate::new()
.invert()
.set_format(&format2);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 4. Above and Below Average conditional formats.
// -----------------------------------------------------------------------
let caption = "Above average values are in light red. Below average values are in light green.";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
worksheet.write_row_matrix(2, 1, data)?;
// Set the column widths for clarity.
for col_num in 1..=10u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write a conditional format over a range. The default criteria is Above Average.
let conditional_format = ConditionalFormatAverage::new().set_format(&format1);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// Write another conditional format over the same range.
let conditional_format = ConditionalFormatAverage::new()
.set_rule(ConditionalFormatAverageRule::BelowAverage)
.set_format(&format2);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 5. Top and Bottom range conditional formats.
// -----------------------------------------------------------------------
let caption = "Top 10 values are in light red. Bottom 10 values are in light green.";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
worksheet.write_row_matrix(2, 1, data)?;
// Set the column widths for clarity.
for col_num in 1..=10u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write a conditional format over a range.
let conditional_format = ConditionalFormatTop::new()
.set_rule(rust_xlsxwriter::ConditionalFormatTopRule::Top(10))
.set_format(&format1);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// Also show the bottom values in the same range.
let conditional_format = ConditionalFormatTop::new()
.set_rule(rust_xlsxwriter::ConditionalFormatTopRule::Bottom(10))
.set_format(&format2);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 6. Cell conditional formatting in non-contiguous range.
// -----------------------------------------------------------------------
let caption = "Cells with values >= 50 are in light red. Values < 50 are in light green. Non-contiguous ranges.";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
worksheet.write_row_matrix(2, 1, data)?;
// Set the column widths for clarity.
for col_num in 1..=10u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write a conditional format over a non-contiguous range.
let conditional_format = ConditionalFormatCell::new()
.set_rule(ConditionalFormatCellRule::GreaterThanOrEqualTo(50))
.set_multi_range("B3:D6 I3:K6 B9:D12 I9:K12")
.set_format(&format1);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// Write another conditional format over the same range.
let conditional_format = ConditionalFormatCell::new()
.set_rule(ConditionalFormatCellRule::LessThan(50))
.set_multi_range("B3:D6 I3:K6 B9:D12 I9:K12")
.set_format(&format2);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 7. Formula conditional formatting.
// -----------------------------------------------------------------------
let caption = "Even numbered cells are in light green. Odd numbered cells are in light red.";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
worksheet.write_row_matrix(2, 1, data)?;
// Set the column widths for clarity.
for col_num in 1..=10u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write a conditional format over a range.
let conditional_format = ConditionalFormatFormula::new()
.set_rule("=ISODD(B3)")
.set_format(&format1);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// Write another conditional format over the same range.
let conditional_format = ConditionalFormatFormula::new()
.set_rule("=ISEVEN(B3)")
.set_format(&format2);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 8. Text style conditional formats.
// -----------------------------------------------------------------------
let caption =
"Column A shows words that contain the sub-word 'rust'. Column C shows words that start/end with 't'";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 0, caption)?;
// Add some sample data.
let word_list = [
"apocrustic",
"burstwort",
"cloudburst",
"crustification",
"distrustfulness",
"laurustine",
"outburst",
"rusticism",
"thunderburst",
"trustee",
"trustworthiness",
"unburstableness",
"unfrustratable",
];
worksheet.write_column(1, 0, word_list)?;
worksheet.write_column(1, 2, word_list)?;
// Set the column widths for clarity.
worksheet.set_column_width(0, 20)?;
worksheet.set_column_width(2, 20)?;
// Write a text "containing" conditional format over a range.
let conditional_format = ConditionalFormatText::new()
.set_rule(ConditionalFormatTextRule::Contains("rust".to_string()))
.set_format(&format2);
worksheet.add_conditional_format(1, 0, 13, 0, &conditional_format)?;
// Write a text "not containing" conditional format over the same range.
let conditional_format = ConditionalFormatText::new()
.set_rule(ConditionalFormatTextRule::DoesNotContain(
"rust".to_string(),
))
.set_format(&format1);
worksheet.add_conditional_format(1, 0, 13, 0, &conditional_format)?;
// Write a text "begins with" conditional format over a range.
let conditional_format = ConditionalFormatText::new()
.set_rule(ConditionalFormatTextRule::BeginsWith("t".to_string()))
.set_format(&format2);
worksheet.add_conditional_format(1, 2, 13, 2, &conditional_format)?;
// Write a text "ends with" conditional format over the same range.
let conditional_format = ConditionalFormatText::new()
.set_rule(ConditionalFormatTextRule::EndsWith("t".to_string()))
.set_format(&format1);
worksheet.add_conditional_format(1, 2, 13, 2, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 9. Examples of 2 color scale conditional formats.
// -----------------------------------------------------------------------
let caption = "Examples of 2 color scale conditional formats";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
let data = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10];
worksheet.write_column(2, 1, data)?;
worksheet.write_column(2, 3, data)?;
worksheet.write_column(2, 5, data)?;
worksheet.write_column(2, 7, data)?;
worksheet.write_column(2, 9, data)?;
worksheet.write_column(2, 11, data)?;
// Set the column widths for clarity.
for col_num in 0..=12u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write 2 color scale formats with standard Excel colors.
let conditional_format = ConditionalFormat2ColorScale::new()
.set_minimum_color("F8696B")
.set_maximum_color("FCFCFF");
worksheet.add_conditional_format(2, 1, 11, 1, &conditional_format)?;
let conditional_format = ConditionalFormat2ColorScale::new()
.set_minimum_color("FCFCFF")
.set_maximum_color("F8696B");
worksheet.add_conditional_format(2, 3, 11, 3, &conditional_format)?;
let conditional_format = ConditionalFormat2ColorScale::new()
.set_minimum_color("FCFCFF")
.set_maximum_color("63BE7B");
worksheet.add_conditional_format(2, 5, 11, 5, &conditional_format)?;
let conditional_format = ConditionalFormat2ColorScale::new()
.set_minimum_color("63BE7B")
.set_maximum_color("FCFCFF");
worksheet.add_conditional_format(2, 7, 11, 7, &conditional_format)?;
let conditional_format = ConditionalFormat2ColorScale::new()
.set_minimum_color("FFEF9C")
.set_maximum_color("63BE7B");
worksheet.add_conditional_format(2, 9, 11, 9, &conditional_format)?;
let conditional_format = ConditionalFormat2ColorScale::new()
.set_minimum_color("63BE7B")
.set_maximum_color("FFEF9C");
worksheet.add_conditional_format(2, 11, 11, 11, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 10. Examples of 3 color scale conditional formats.
// -----------------------------------------------------------------------
let caption = "Examples of 3 color scale conditional formats";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
let data = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10];
worksheet.write_column(2, 1, data)?;
worksheet.write_column(2, 3, data)?;
worksheet.write_column(2, 5, data)?;
worksheet.write_column(2, 7, data)?;
worksheet.write_column(2, 9, data)?;
worksheet.write_column(2, 11, data)?;
// Set the column widths for clarity.
for col_num in 0..=12u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write 3 color scale formats with standard Excel colors.
let conditional_format = ConditionalFormat3ColorScale::new()
.set_minimum_color("F8696B")
.set_midpoint_color("FFEB84")
.set_maximum_color("63BE7B");
worksheet.add_conditional_format(2, 1, 11, 1, &conditional_format)?;
let conditional_format = ConditionalFormat3ColorScale::new()
.set_minimum_color("63BE7B")
.set_midpoint_color("FFEB84")
.set_maximum_color("F8696B");
worksheet.add_conditional_format(2, 3, 11, 3, &conditional_format)?;
let conditional_format = ConditionalFormat3ColorScale::new()
.set_minimum_color("F8696B")
.set_midpoint_color("FCFCFF")
.set_maximum_color("63BE7B");
worksheet.add_conditional_format(2, 5, 11, 5, &conditional_format)?;
let conditional_format = ConditionalFormat3ColorScale::new()
.set_minimum_color("63BE7B")
.set_midpoint_color("FCFCFF")
.set_maximum_color("F8696B");
worksheet.add_conditional_format(2, 7, 11, 7, &conditional_format)?;
let conditional_format = ConditionalFormat3ColorScale::new()
.set_minimum_color("F8696B")
.set_midpoint_color("FCFCFF")
.set_maximum_color("5A8AC6");
worksheet.add_conditional_format(2, 9, 11, 9, &conditional_format)?;
let conditional_format = ConditionalFormat3ColorScale::new()
.set_minimum_color("5A8AC6")
.set_midpoint_color("FCFCFF")
.set_maximum_color("F8696B");
worksheet.add_conditional_format(2, 11, 11, 11, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 11. Examples of data bars.
// -----------------------------------------------------------------------
let caption = "Examples of data bars";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write_with_format(0, 1, caption, &bold)?;
worksheet.write(1, 1, "Default")?;
worksheet.write(1, 3, "Default negative")?;
worksheet.write(1, 5, "User color")?;
worksheet.write(1, 7, "Changed direction")?;
// Write the worksheet data.
let data1 = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10];
let data2 = [6, 4, 2, -2, -4, -6, -4, -2, 2, 4];
worksheet.write_column(2, 1, data1)?;
worksheet.write_column(2, 3, data2)?;
worksheet.write_column(2, 5, data1)?;
worksheet.write_column(2, 7, data1)?;
// Write a standard Excel data bar.
let conditional_format = ConditionalFormatDataBar::new();
worksheet.add_conditional_format(2, 1, 11, 1, &conditional_format)?;
// Write a standard Excel data bar with negative data
let conditional_format = ConditionalFormatDataBar::new();
worksheet.add_conditional_format(2, 3, 11, 3, &conditional_format)?;
// Write a data bar with a user defined fill color.
let conditional_format = ConditionalFormatDataBar::new().set_fill_color("009933");
worksheet.add_conditional_format(2, 5, 11, 5, &conditional_format)?;
// Write a data bar with the direction changed.
let conditional_format = ConditionalFormatDataBar::new()
.set_direction(ConditionalFormatDataBarDirection::RightToLeft);
worksheet.add_conditional_format(2, 7, 11, 7, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 12. Examples of icon style conditional formats.
// -----------------------------------------------------------------------
let caption = "Examples of icon style conditional formats.";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write_with_format(0, 0, caption, &bold)?;
worksheet.write_with_format(1, 0, "Three Traffic lights - Green is highest", &indent)?;
worksheet.write_with_format(2, 0, "Reversed - Red is highest", &indent)?;
worksheet.write_with_format(3, 0, "Icons only - The number data is hidden", &indent)?;
worksheet.write_with_format(4, 0, "Other three-five icon examples", &bold)?;
worksheet.write_with_format(5, 0, "Three arrows", &indent)?;
worksheet.write_with_format(6, 0, "Three symbols", &indent)?;
worksheet.write_with_format(7, 0, "Three stars", &indent)?;
worksheet.write_with_format(8, 0, "Four arrows", &indent)?;
worksheet.write_with_format(9, 0, "Four circles - Red (highest) to Black", &indent)?;
worksheet.write_with_format(10, 0, "Four rating histograms", &indent)?;
worksheet.write_with_format(11, 0, "Five arrows", &indent)?;
worksheet.write_with_format(12, 0, "Five rating histograms", &indent)?;
worksheet.write_with_format(13, 0, "Five rating quadrants", &indent)?;
// Set the column width for clarity.
worksheet.set_column_width(0, 35)?;
// Write the worksheet data.
worksheet.write_row(1, 1, [1, 2, 3])?;
worksheet.write_row(2, 1, [1, 2, 3])?;
worksheet.write_row(3, 1, [1, 2, 3])?;
worksheet.write_row(5, 1, [1, 2, 3])?;
worksheet.write_row(6, 1, [1, 2, 3])?;
worksheet.write_row(7, 1, [1, 2, 3])?;
worksheet.write_row(8, 1, [1, 2, 3, 4])?;
worksheet.write_row(9, 1, [1, 2, 3, 4])?;
worksheet.write_row(10, 1, [1, 2, 3, 4])?;
worksheet.write_row(11, 1, [1, 2, 3, 4, 5])?;
worksheet.write_row(12, 1, [1, 2, 3, 4, 5])?;
worksheet.write_row(13, 1, [1, 2, 3, 4, 5])?;
// Three Traffic lights - Green is highest.
let conditional_format = ConditionalFormatIconSet::new()
.set_icon_type(ConditionalFormatIconType::ThreeTrafficLights);
worksheet.add_conditional_format(1, 1, 1, 3, &conditional_format)?;
// Reversed - Red is highest.
let conditional_format = ConditionalFormatIconSet::new()
.set_icon_type(ConditionalFormatIconType::ThreeTrafficLights)
.reverse_icons(true);
worksheet.add_conditional_format(2, 1, 2, 3, &conditional_format)?;
// Icons only - The number data is hidden.
let conditional_format = ConditionalFormatIconSet::new()
.set_icon_type(ConditionalFormatIconType::ThreeTrafficLights)
.show_icons_only(true);
worksheet.add_conditional_format(3, 1, 3, 3, &conditional_format)?;
// Three arrows.
let conditional_format =
ConditionalFormatIconSet::new().set_icon_type(ConditionalFormatIconType::ThreeArrows);
worksheet.add_conditional_format(5, 1, 5, 3, &conditional_format)?;
// Three symbols.
let conditional_format = ConditionalFormatIconSet::new()
.set_icon_type(ConditionalFormatIconType::ThreeSymbolsCircled);
worksheet.add_conditional_format(6, 1, 6, 3, &conditional_format)?;
// Three stars.
let conditional_format =
ConditionalFormatIconSet::new().set_icon_type(ConditionalFormatIconType::ThreeStars);
worksheet.add_conditional_format(7, 1, 7, 3, &conditional_format)?;
// Four Arrows.
let conditional_format =
ConditionalFormatIconSet::new().set_icon_type(ConditionalFormatIconType::FourArrows);
worksheet.add_conditional_format(8, 1, 8, 4, &conditional_format)?;
// Four circles - Red (highest) to Black (lowest).
let conditional_format =
ConditionalFormatIconSet::new().set_icon_type(ConditionalFormatIconType::FourRedToBlack);
worksheet.add_conditional_format(9, 1, 9, 4, &conditional_format)?;
// Four rating histograms.
let conditional_format =
ConditionalFormatIconSet::new().set_icon_type(ConditionalFormatIconType::FourHistograms);
worksheet.add_conditional_format(10, 1, 10, 4, &conditional_format)?;
// Four Arrows.
let conditional_format =
ConditionalFormatIconSet::new().set_icon_type(ConditionalFormatIconType::FiveArrows);
worksheet.add_conditional_format(11, 1, 11, 5, &conditional_format)?;
// Four rating histograms.
let conditional_format =
ConditionalFormatIconSet::new().set_icon_type(ConditionalFormatIconType::FiveHistograms);
worksheet.add_conditional_format(12, 1, 12, 5, &conditional_format)?;
// Four rating quadrants.
let conditional_format =
ConditionalFormatIconSet::new().set_icon_type(ConditionalFormatIconType::FiveQuadrants);
worksheet.add_conditional_format(13, 1, 13, 5, &conditional_format)?;
// -----------------------------------------------------------------------
// Save and close the file.
// -----------------------------------------------------------------------
workbook.save("conditional_formats.xlsx")?;
Ok(())
}Example 8. Text style conditional formats. Column A shows words that contain the sub-word 'rust'. Column C shows words that start/end with 't'
See ConditionalFormatText for more details.
Code to generate the above example:
// SPDX-License-Identifier: MIT OR Apache-2.0
//
// Copyright 2022-2024, John McNamara, jmcnamara@cpan.org
//! Example of how to add conditional formatting to a worksheet using the
//! rust_xlsxwriter library.
//!
//! Conditional formatting allows you to apply a format to a cell or a range of
//! cells based on user defined rule.
use rust_xlsxwriter::{
ConditionalFormat2ColorScale, ConditionalFormat3ColorScale, ConditionalFormatAverage,
ConditionalFormatAverageRule, ConditionalFormatCell, ConditionalFormatCellRule,
ConditionalFormatDataBar, ConditionalFormatDataBarDirection, ConditionalFormatDuplicate,
ConditionalFormatFormula, ConditionalFormatIconSet, ConditionalFormatIconType,
ConditionalFormatText, ConditionalFormatTextRule, ConditionalFormatTop, Format, Workbook,
XlsxError,
};
fn main() -> Result<(), XlsxError> {
// Create a new Excel file object.
let mut workbook = Workbook::new();
// Add a format. Light red fill with dark red text.
let format1 = Format::new()
.set_font_color("9C0006")
.set_background_color("FFC7CE");
// Add a format. Green fill with dark green text.
let format2 = Format::new()
.set_font_color("006100")
.set_background_color("C6EFCE");
// Add a format for headers.
let bold = Format::new().set_bold();
// Add a format for descriptions.
let indent = Format::new().set_indent(2);
// some sample data to run the conditional formatting against.
let data = [
[34, 72, 38, 30, 75, 48, 75, 66, 84, 86],
[6, 24, 1, 84, 54, 62, 60, 3, 26, 59],
[28, 79, 97, 13, 85, 93, 93, 22, 5, 14],
[27, 71, 40, 17, 18, 79, 90, 93, 29, 47],
[88, 25, 33, 23, 67, 1, 59, 79, 47, 36],
[24, 100, 20, 88, 29, 33, 38, 54, 54, 88],
[6, 57, 88, 28, 10, 26, 37, 7, 41, 48],
[52, 78, 1, 96, 26, 45, 47, 33, 96, 36],
[60, 54, 81, 66, 81, 90, 80, 93, 12, 55],
[70, 5, 46, 14, 71, 19, 66, 36, 41, 21],
];
// -----------------------------------------------------------------------
// Worksheet 1. Cell conditional formatting.
// -----------------------------------------------------------------------
let caption = "Cells with values >= 50 are in light red. Values < 50 are in light green.";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
worksheet.write_row_matrix(2, 1, data)?;
// Set the column widths for clarity.
for col_num in 1..=10u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write a conditional format over a range.
let conditional_format = ConditionalFormatCell::new()
.set_rule(ConditionalFormatCellRule::GreaterThanOrEqualTo(50))
.set_format(&format1);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// Write another conditional format over the same range.
let conditional_format = ConditionalFormatCell::new()
.set_rule(ConditionalFormatCellRule::LessThan(50))
.set_format(&format2);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 2. Cell conditional formatting with between ranges.
// -----------------------------------------------------------------------
let caption =
"Values between 30 and 70 are in light red. Values outside that range are in light green.";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
worksheet.write_row_matrix(2, 1, data)?;
// Set the column widths for clarity.
for col_num in 1..=10u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write a conditional format over a range.
let conditional_format = ConditionalFormatCell::new()
.set_rule(ConditionalFormatCellRule::Between(30, 70))
.set_format(&format1);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// Write another conditional format over the same range.
let conditional_format = ConditionalFormatCell::new()
.set_rule(ConditionalFormatCellRule::NotBetween(30, 70))
.set_format(&format2);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 3. Duplicate and Unique conditional formats.
// -----------------------------------------------------------------------
let caption = "Duplicate values are in light red. Unique values are in light green.";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
worksheet.write_row_matrix(2, 1, data)?;
// Set the column widths for clarity.
for col_num in 1..=10u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write a conditional format over a range.
let conditional_format = ConditionalFormatDuplicate::new().set_format(&format1);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// Invert the duplicate conditional format to show unique values in the
// same range.
let conditional_format = ConditionalFormatDuplicate::new()
.invert()
.set_format(&format2);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 4. Above and Below Average conditional formats.
// -----------------------------------------------------------------------
let caption = "Above average values are in light red. Below average values are in light green.";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
worksheet.write_row_matrix(2, 1, data)?;
// Set the column widths for clarity.
for col_num in 1..=10u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write a conditional format over a range. The default criteria is Above Average.
let conditional_format = ConditionalFormatAverage::new().set_format(&format1);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// Write another conditional format over the same range.
let conditional_format = ConditionalFormatAverage::new()
.set_rule(ConditionalFormatAverageRule::BelowAverage)
.set_format(&format2);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 5. Top and Bottom range conditional formats.
// -----------------------------------------------------------------------
let caption = "Top 10 values are in light red. Bottom 10 values are in light green.";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
worksheet.write_row_matrix(2, 1, data)?;
// Set the column widths for clarity.
for col_num in 1..=10u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write a conditional format over a range.
let conditional_format = ConditionalFormatTop::new()
.set_rule(rust_xlsxwriter::ConditionalFormatTopRule::Top(10))
.set_format(&format1);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// Also show the bottom values in the same range.
let conditional_format = ConditionalFormatTop::new()
.set_rule(rust_xlsxwriter::ConditionalFormatTopRule::Bottom(10))
.set_format(&format2);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 6. Cell conditional formatting in non-contiguous range.
// -----------------------------------------------------------------------
let caption = "Cells with values >= 50 are in light red. Values < 50 are in light green. Non-contiguous ranges.";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
worksheet.write_row_matrix(2, 1, data)?;
// Set the column widths for clarity.
for col_num in 1..=10u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write a conditional format over a non-contiguous range.
let conditional_format = ConditionalFormatCell::new()
.set_rule(ConditionalFormatCellRule::GreaterThanOrEqualTo(50))
.set_multi_range("B3:D6 I3:K6 B9:D12 I9:K12")
.set_format(&format1);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// Write another conditional format over the same range.
let conditional_format = ConditionalFormatCell::new()
.set_rule(ConditionalFormatCellRule::LessThan(50))
.set_multi_range("B3:D6 I3:K6 B9:D12 I9:K12")
.set_format(&format2);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 7. Formula conditional formatting.
// -----------------------------------------------------------------------
let caption = "Even numbered cells are in light green. Odd numbered cells are in light red.";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
worksheet.write_row_matrix(2, 1, data)?;
// Set the column widths for clarity.
for col_num in 1..=10u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write a conditional format over a range.
let conditional_format = ConditionalFormatFormula::new()
.set_rule("=ISODD(B3)")
.set_format(&format1);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// Write another conditional format over the same range.
let conditional_format = ConditionalFormatFormula::new()
.set_rule("=ISEVEN(B3)")
.set_format(&format2);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 8. Text style conditional formats.
// -----------------------------------------------------------------------
let caption =
"Column A shows words that contain the sub-word 'rust'. Column C shows words that start/end with 't'";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 0, caption)?;
// Add some sample data.
let word_list = [
"apocrustic",
"burstwort",
"cloudburst",
"crustification",
"distrustfulness",
"laurustine",
"outburst",
"rusticism",
"thunderburst",
"trustee",
"trustworthiness",
"unburstableness",
"unfrustratable",
];
worksheet.write_column(1, 0, word_list)?;
worksheet.write_column(1, 2, word_list)?;
// Set the column widths for clarity.
worksheet.set_column_width(0, 20)?;
worksheet.set_column_width(2, 20)?;
// Write a text "containing" conditional format over a range.
let conditional_format = ConditionalFormatText::new()
.set_rule(ConditionalFormatTextRule::Contains("rust".to_string()))
.set_format(&format2);
worksheet.add_conditional_format(1, 0, 13, 0, &conditional_format)?;
// Write a text "not containing" conditional format over the same range.
let conditional_format = ConditionalFormatText::new()
.set_rule(ConditionalFormatTextRule::DoesNotContain(
"rust".to_string(),
))
.set_format(&format1);
worksheet.add_conditional_format(1, 0, 13, 0, &conditional_format)?;
// Write a text "begins with" conditional format over a range.
let conditional_format = ConditionalFormatText::new()
.set_rule(ConditionalFormatTextRule::BeginsWith("t".to_string()))
.set_format(&format2);
worksheet.add_conditional_format(1, 2, 13, 2, &conditional_format)?;
// Write a text "ends with" conditional format over the same range.
let conditional_format = ConditionalFormatText::new()
.set_rule(ConditionalFormatTextRule::EndsWith("t".to_string()))
.set_format(&format1);
worksheet.add_conditional_format(1, 2, 13, 2, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 9. Examples of 2 color scale conditional formats.
// -----------------------------------------------------------------------
let caption = "Examples of 2 color scale conditional formats";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
let data = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10];
worksheet.write_column(2, 1, data)?;
worksheet.write_column(2, 3, data)?;
worksheet.write_column(2, 5, data)?;
worksheet.write_column(2, 7, data)?;
worksheet.write_column(2, 9, data)?;
worksheet.write_column(2, 11, data)?;
// Set the column widths for clarity.
for col_num in 0..=12u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write 2 color scale formats with standard Excel colors.
let conditional_format = ConditionalFormat2ColorScale::new()
.set_minimum_color("F8696B")
.set_maximum_color("FCFCFF");
worksheet.add_conditional_format(2, 1, 11, 1, &conditional_format)?;
let conditional_format = ConditionalFormat2ColorScale::new()
.set_minimum_color("FCFCFF")
.set_maximum_color("F8696B");
worksheet.add_conditional_format(2, 3, 11, 3, &conditional_format)?;
let conditional_format = ConditionalFormat2ColorScale::new()
.set_minimum_color("FCFCFF")
.set_maximum_color("63BE7B");
worksheet.add_conditional_format(2, 5, 11, 5, &conditional_format)?;
let conditional_format = ConditionalFormat2ColorScale::new()
.set_minimum_color("63BE7B")
.set_maximum_color("FCFCFF");
worksheet.add_conditional_format(2, 7, 11, 7, &conditional_format)?;
let conditional_format = ConditionalFormat2ColorScale::new()
.set_minimum_color("FFEF9C")
.set_maximum_color("63BE7B");
worksheet.add_conditional_format(2, 9, 11, 9, &conditional_format)?;
let conditional_format = ConditionalFormat2ColorScale::new()
.set_minimum_color("63BE7B")
.set_maximum_color("FFEF9C");
worksheet.add_conditional_format(2, 11, 11, 11, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 10. Examples of 3 color scale conditional formats.
// -----------------------------------------------------------------------
let caption = "Examples of 3 color scale conditional formats";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
let data = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10];
worksheet.write_column(2, 1, data)?;
worksheet.write_column(2, 3, data)?;
worksheet.write_column(2, 5, data)?;
worksheet.write_column(2, 7, data)?;
worksheet.write_column(2, 9, data)?;
worksheet.write_column(2, 11, data)?;
// Set the column widths for clarity.
for col_num in 0..=12u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write 3 color scale formats with standard Excel colors.
let conditional_format = ConditionalFormat3ColorScale::new()
.set_minimum_color("F8696B")
.set_midpoint_color("FFEB84")
.set_maximum_color("63BE7B");
worksheet.add_conditional_format(2, 1, 11, 1, &conditional_format)?;
let conditional_format = ConditionalFormat3ColorScale::new()
.set_minimum_color("63BE7B")
.set_midpoint_color("FFEB84")
.set_maximum_color("F8696B");
worksheet.add_conditional_format(2, 3, 11, 3, &conditional_format)?;
let conditional_format = ConditionalFormat3ColorScale::new()
.set_minimum_color("F8696B")
.set_midpoint_color("FCFCFF")
.set_maximum_color("63BE7B");
worksheet.add_conditional_format(2, 5, 11, 5, &conditional_format)?;
let conditional_format = ConditionalFormat3ColorScale::new()
.set_minimum_color("63BE7B")
.set_midpoint_color("FCFCFF")
.set_maximum_color("F8696B");
worksheet.add_conditional_format(2, 7, 11, 7, &conditional_format)?;
let conditional_format = ConditionalFormat3ColorScale::new()
.set_minimum_color("F8696B")
.set_midpoint_color("FCFCFF")
.set_maximum_color("5A8AC6");
worksheet.add_conditional_format(2, 9, 11, 9, &conditional_format)?;
let conditional_format = ConditionalFormat3ColorScale::new()
.set_minimum_color("5A8AC6")
.set_midpoint_color("FCFCFF")
.set_maximum_color("F8696B");
worksheet.add_conditional_format(2, 11, 11, 11, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 11. Examples of data bars.
// -----------------------------------------------------------------------
let caption = "Examples of data bars";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write_with_format(0, 1, caption, &bold)?;
worksheet.write(1, 1, "Default")?;
worksheet.write(1, 3, "Default negative")?;
worksheet.write(1, 5, "User color")?;
worksheet.write(1, 7, "Changed direction")?;
// Write the worksheet data.
let data1 = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10];
let data2 = [6, 4, 2, -2, -4, -6, -4, -2, 2, 4];
worksheet.write_column(2, 1, data1)?;
worksheet.write_column(2, 3, data2)?;
worksheet.write_column(2, 5, data1)?;
worksheet.write_column(2, 7, data1)?;
// Write a standard Excel data bar.
let conditional_format = ConditionalFormatDataBar::new();
worksheet.add_conditional_format(2, 1, 11, 1, &conditional_format)?;
// Write a standard Excel data bar with negative data
let conditional_format = ConditionalFormatDataBar::new();
worksheet.add_conditional_format(2, 3, 11, 3, &conditional_format)?;
// Write a data bar with a user defined fill color.
let conditional_format = ConditionalFormatDataBar::new().set_fill_color("009933");
worksheet.add_conditional_format(2, 5, 11, 5, &conditional_format)?;
// Write a data bar with the direction changed.
let conditional_format = ConditionalFormatDataBar::new()
.set_direction(ConditionalFormatDataBarDirection::RightToLeft);
worksheet.add_conditional_format(2, 7, 11, 7, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 12. Examples of icon style conditional formats.
// -----------------------------------------------------------------------
let caption = "Examples of icon style conditional formats.";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write_with_format(0, 0, caption, &bold)?;
worksheet.write_with_format(1, 0, "Three Traffic lights - Green is highest", &indent)?;
worksheet.write_with_format(2, 0, "Reversed - Red is highest", &indent)?;
worksheet.write_with_format(3, 0, "Icons only - The number data is hidden", &indent)?;
worksheet.write_with_format(4, 0, "Other three-five icon examples", &bold)?;
worksheet.write_with_format(5, 0, "Three arrows", &indent)?;
worksheet.write_with_format(6, 0, "Three symbols", &indent)?;
worksheet.write_with_format(7, 0, "Three stars", &indent)?;
worksheet.write_with_format(8, 0, "Four arrows", &indent)?;
worksheet.write_with_format(9, 0, "Four circles - Red (highest) to Black", &indent)?;
worksheet.write_with_format(10, 0, "Four rating histograms", &indent)?;
worksheet.write_with_format(11, 0, "Five arrows", &indent)?;
worksheet.write_with_format(12, 0, "Five rating histograms", &indent)?;
worksheet.write_with_format(13, 0, "Five rating quadrants", &indent)?;
// Set the column width for clarity.
worksheet.set_column_width(0, 35)?;
// Write the worksheet data.
worksheet.write_row(1, 1, [1, 2, 3])?;
worksheet.write_row(2, 1, [1, 2, 3])?;
worksheet.write_row(3, 1, [1, 2, 3])?;
worksheet.write_row(5, 1, [1, 2, 3])?;
worksheet.write_row(6, 1, [1, 2, 3])?;
worksheet.write_row(7, 1, [1, 2, 3])?;
worksheet.write_row(8, 1, [1, 2, 3, 4])?;
worksheet.write_row(9, 1, [1, 2, 3, 4])?;
worksheet.write_row(10, 1, [1, 2, 3, 4])?;
worksheet.write_row(11, 1, [1, 2, 3, 4, 5])?;
worksheet.write_row(12, 1, [1, 2, 3, 4, 5])?;
worksheet.write_row(13, 1, [1, 2, 3, 4, 5])?;
// Three Traffic lights - Green is highest.
let conditional_format = ConditionalFormatIconSet::new()
.set_icon_type(ConditionalFormatIconType::ThreeTrafficLights);
worksheet.add_conditional_format(1, 1, 1, 3, &conditional_format)?;
// Reversed - Red is highest.
let conditional_format = ConditionalFormatIconSet::new()
.set_icon_type(ConditionalFormatIconType::ThreeTrafficLights)
.reverse_icons(true);
worksheet.add_conditional_format(2, 1, 2, 3, &conditional_format)?;
// Icons only - The number data is hidden.
let conditional_format = ConditionalFormatIconSet::new()
.set_icon_type(ConditionalFormatIconType::ThreeTrafficLights)
.show_icons_only(true);
worksheet.add_conditional_format(3, 1, 3, 3, &conditional_format)?;
// Three arrows.
let conditional_format =
ConditionalFormatIconSet::new().set_icon_type(ConditionalFormatIconType::ThreeArrows);
worksheet.add_conditional_format(5, 1, 5, 3, &conditional_format)?;
// Three symbols.
let conditional_format = ConditionalFormatIconSet::new()
.set_icon_type(ConditionalFormatIconType::ThreeSymbolsCircled);
worksheet.add_conditional_format(6, 1, 6, 3, &conditional_format)?;
// Three stars.
let conditional_format =
ConditionalFormatIconSet::new().set_icon_type(ConditionalFormatIconType::ThreeStars);
worksheet.add_conditional_format(7, 1, 7, 3, &conditional_format)?;
// Four Arrows.
let conditional_format =
ConditionalFormatIconSet::new().set_icon_type(ConditionalFormatIconType::FourArrows);
worksheet.add_conditional_format(8, 1, 8, 4, &conditional_format)?;
// Four circles - Red (highest) to Black (lowest).
let conditional_format =
ConditionalFormatIconSet::new().set_icon_type(ConditionalFormatIconType::FourRedToBlack);
worksheet.add_conditional_format(9, 1, 9, 4, &conditional_format)?;
// Four rating histograms.
let conditional_format =
ConditionalFormatIconSet::new().set_icon_type(ConditionalFormatIconType::FourHistograms);
worksheet.add_conditional_format(10, 1, 10, 4, &conditional_format)?;
// Four Arrows.
let conditional_format =
ConditionalFormatIconSet::new().set_icon_type(ConditionalFormatIconType::FiveArrows);
worksheet.add_conditional_format(11, 1, 11, 5, &conditional_format)?;
// Four rating histograms.
let conditional_format =
ConditionalFormatIconSet::new().set_icon_type(ConditionalFormatIconType::FiveHistograms);
worksheet.add_conditional_format(12, 1, 12, 5, &conditional_format)?;
// Four rating quadrants.
let conditional_format =
ConditionalFormatIconSet::new().set_icon_type(ConditionalFormatIconType::FiveQuadrants);
worksheet.add_conditional_format(13, 1, 13, 5, &conditional_format)?;
// -----------------------------------------------------------------------
// Save and close the file.
// -----------------------------------------------------------------------
workbook.save("conditional_formats.xlsx")?;
Ok(())
}Example 9. Examples of 2 color scale conditional formats.
See ConditionalFormat2ColorScale for more details.
Code to generate the above example:
// SPDX-License-Identifier: MIT OR Apache-2.0
//
// Copyright 2022-2024, John McNamara, jmcnamara@cpan.org
//! Example of how to add conditional formatting to a worksheet using the
//! rust_xlsxwriter library.
//!
//! Conditional formatting allows you to apply a format to a cell or a range of
//! cells based on user defined rule.
use rust_xlsxwriter::{
ConditionalFormat2ColorScale, ConditionalFormat3ColorScale, ConditionalFormatAverage,
ConditionalFormatAverageRule, ConditionalFormatCell, ConditionalFormatCellRule,
ConditionalFormatDataBar, ConditionalFormatDataBarDirection, ConditionalFormatDuplicate,
ConditionalFormatFormula, ConditionalFormatIconSet, ConditionalFormatIconType,
ConditionalFormatText, ConditionalFormatTextRule, ConditionalFormatTop, Format, Workbook,
XlsxError,
};
fn main() -> Result<(), XlsxError> {
// Create a new Excel file object.
let mut workbook = Workbook::new();
// Add a format. Light red fill with dark red text.
let format1 = Format::new()
.set_font_color("9C0006")
.set_background_color("FFC7CE");
// Add a format. Green fill with dark green text.
let format2 = Format::new()
.set_font_color("006100")
.set_background_color("C6EFCE");
// Add a format for headers.
let bold = Format::new().set_bold();
// Add a format for descriptions.
let indent = Format::new().set_indent(2);
// some sample data to run the conditional formatting against.
let data = [
[34, 72, 38, 30, 75, 48, 75, 66, 84, 86],
[6, 24, 1, 84, 54, 62, 60, 3, 26, 59],
[28, 79, 97, 13, 85, 93, 93, 22, 5, 14],
[27, 71, 40, 17, 18, 79, 90, 93, 29, 47],
[88, 25, 33, 23, 67, 1, 59, 79, 47, 36],
[24, 100, 20, 88, 29, 33, 38, 54, 54, 88],
[6, 57, 88, 28, 10, 26, 37, 7, 41, 48],
[52, 78, 1, 96, 26, 45, 47, 33, 96, 36],
[60, 54, 81, 66, 81, 90, 80, 93, 12, 55],
[70, 5, 46, 14, 71, 19, 66, 36, 41, 21],
];
// -----------------------------------------------------------------------
// Worksheet 1. Cell conditional formatting.
// -----------------------------------------------------------------------
let caption = "Cells with values >= 50 are in light red. Values < 50 are in light green.";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
worksheet.write_row_matrix(2, 1, data)?;
// Set the column widths for clarity.
for col_num in 1..=10u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write a conditional format over a range.
let conditional_format = ConditionalFormatCell::new()
.set_rule(ConditionalFormatCellRule::GreaterThanOrEqualTo(50))
.set_format(&format1);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// Write another conditional format over the same range.
let conditional_format = ConditionalFormatCell::new()
.set_rule(ConditionalFormatCellRule::LessThan(50))
.set_format(&format2);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 2. Cell conditional formatting with between ranges.
// -----------------------------------------------------------------------
let caption =
"Values between 30 and 70 are in light red. Values outside that range are in light green.";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
worksheet.write_row_matrix(2, 1, data)?;
// Set the column widths for clarity.
for col_num in 1..=10u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write a conditional format over a range.
let conditional_format = ConditionalFormatCell::new()
.set_rule(ConditionalFormatCellRule::Between(30, 70))
.set_format(&format1);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// Write another conditional format over the same range.
let conditional_format = ConditionalFormatCell::new()
.set_rule(ConditionalFormatCellRule::NotBetween(30, 70))
.set_format(&format2);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 3. Duplicate and Unique conditional formats.
// -----------------------------------------------------------------------
let caption = "Duplicate values are in light red. Unique values are in light green.";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
worksheet.write_row_matrix(2, 1, data)?;
// Set the column widths for clarity.
for col_num in 1..=10u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write a conditional format over a range.
let conditional_format = ConditionalFormatDuplicate::new().set_format(&format1);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// Invert the duplicate conditional format to show unique values in the
// same range.
let conditional_format = ConditionalFormatDuplicate::new()
.invert()
.set_format(&format2);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 4. Above and Below Average conditional formats.
// -----------------------------------------------------------------------
let caption = "Above average values are in light red. Below average values are in light green.";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
worksheet.write_row_matrix(2, 1, data)?;
// Set the column widths for clarity.
for col_num in 1..=10u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write a conditional format over a range. The default criteria is Above Average.
let conditional_format = ConditionalFormatAverage::new().set_format(&format1);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// Write another conditional format over the same range.
let conditional_format = ConditionalFormatAverage::new()
.set_rule(ConditionalFormatAverageRule::BelowAverage)
.set_format(&format2);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 5. Top and Bottom range conditional formats.
// -----------------------------------------------------------------------
let caption = "Top 10 values are in light red. Bottom 10 values are in light green.";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
worksheet.write_row_matrix(2, 1, data)?;
// Set the column widths for clarity.
for col_num in 1..=10u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write a conditional format over a range.
let conditional_format = ConditionalFormatTop::new()
.set_rule(rust_xlsxwriter::ConditionalFormatTopRule::Top(10))
.set_format(&format1);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// Also show the bottom values in the same range.
let conditional_format = ConditionalFormatTop::new()
.set_rule(rust_xlsxwriter::ConditionalFormatTopRule::Bottom(10))
.set_format(&format2);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 6. Cell conditional formatting in non-contiguous range.
// -----------------------------------------------------------------------
let caption = "Cells with values >= 50 are in light red. Values < 50 are in light green. Non-contiguous ranges.";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
worksheet.write_row_matrix(2, 1, data)?;
// Set the column widths for clarity.
for col_num in 1..=10u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write a conditional format over a non-contiguous range.
let conditional_format = ConditionalFormatCell::new()
.set_rule(ConditionalFormatCellRule::GreaterThanOrEqualTo(50))
.set_multi_range("B3:D6 I3:K6 B9:D12 I9:K12")
.set_format(&format1);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// Write another conditional format over the same range.
let conditional_format = ConditionalFormatCell::new()
.set_rule(ConditionalFormatCellRule::LessThan(50))
.set_multi_range("B3:D6 I3:K6 B9:D12 I9:K12")
.set_format(&format2);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 7. Formula conditional formatting.
// -----------------------------------------------------------------------
let caption = "Even numbered cells are in light green. Odd numbered cells are in light red.";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
worksheet.write_row_matrix(2, 1, data)?;
// Set the column widths for clarity.
for col_num in 1..=10u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write a conditional format over a range.
let conditional_format = ConditionalFormatFormula::new()
.set_rule("=ISODD(B3)")
.set_format(&format1);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// Write another conditional format over the same range.
let conditional_format = ConditionalFormatFormula::new()
.set_rule("=ISEVEN(B3)")
.set_format(&format2);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 8. Text style conditional formats.
// -----------------------------------------------------------------------
let caption =
"Column A shows words that contain the sub-word 'rust'. Column C shows words that start/end with 't'";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 0, caption)?;
// Add some sample data.
let word_list = [
"apocrustic",
"burstwort",
"cloudburst",
"crustification",
"distrustfulness",
"laurustine",
"outburst",
"rusticism",
"thunderburst",
"trustee",
"trustworthiness",
"unburstableness",
"unfrustratable",
];
worksheet.write_column(1, 0, word_list)?;
worksheet.write_column(1, 2, word_list)?;
// Set the column widths for clarity.
worksheet.set_column_width(0, 20)?;
worksheet.set_column_width(2, 20)?;
// Write a text "containing" conditional format over a range.
let conditional_format = ConditionalFormatText::new()
.set_rule(ConditionalFormatTextRule::Contains("rust".to_string()))
.set_format(&format2);
worksheet.add_conditional_format(1, 0, 13, 0, &conditional_format)?;
// Write a text "not containing" conditional format over the same range.
let conditional_format = ConditionalFormatText::new()
.set_rule(ConditionalFormatTextRule::DoesNotContain(
"rust".to_string(),
))
.set_format(&format1);
worksheet.add_conditional_format(1, 0, 13, 0, &conditional_format)?;
// Write a text "begins with" conditional format over a range.
let conditional_format = ConditionalFormatText::new()
.set_rule(ConditionalFormatTextRule::BeginsWith("t".to_string()))
.set_format(&format2);
worksheet.add_conditional_format(1, 2, 13, 2, &conditional_format)?;
// Write a text "ends with" conditional format over the same range.
let conditional_format = ConditionalFormatText::new()
.set_rule(ConditionalFormatTextRule::EndsWith("t".to_string()))
.set_format(&format1);
worksheet.add_conditional_format(1, 2, 13, 2, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 9. Examples of 2 color scale conditional formats.
// -----------------------------------------------------------------------
let caption = "Examples of 2 color scale conditional formats";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
let data = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10];
worksheet.write_column(2, 1, data)?;
worksheet.write_column(2, 3, data)?;
worksheet.write_column(2, 5, data)?;
worksheet.write_column(2, 7, data)?;
worksheet.write_column(2, 9, data)?;
worksheet.write_column(2, 11, data)?;
// Set the column widths for clarity.
for col_num in 0..=12u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write 2 color scale formats with standard Excel colors.
let conditional_format = ConditionalFormat2ColorScale::new()
.set_minimum_color("F8696B")
.set_maximum_color("FCFCFF");
worksheet.add_conditional_format(2, 1, 11, 1, &conditional_format)?;
let conditional_format = ConditionalFormat2ColorScale::new()
.set_minimum_color("FCFCFF")
.set_maximum_color("F8696B");
worksheet.add_conditional_format(2, 3, 11, 3, &conditional_format)?;
let conditional_format = ConditionalFormat2ColorScale::new()
.set_minimum_color("FCFCFF")
.set_maximum_color("63BE7B");
worksheet.add_conditional_format(2, 5, 11, 5, &conditional_format)?;
let conditional_format = ConditionalFormat2ColorScale::new()
.set_minimum_color("63BE7B")
.set_maximum_color("FCFCFF");
worksheet.add_conditional_format(2, 7, 11, 7, &conditional_format)?;
let conditional_format = ConditionalFormat2ColorScale::new()
.set_minimum_color("FFEF9C")
.set_maximum_color("63BE7B");
worksheet.add_conditional_format(2, 9, 11, 9, &conditional_format)?;
let conditional_format = ConditionalFormat2ColorScale::new()
.set_minimum_color("63BE7B")
.set_maximum_color("FFEF9C");
worksheet.add_conditional_format(2, 11, 11, 11, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 10. Examples of 3 color scale conditional formats.
// -----------------------------------------------------------------------
let caption = "Examples of 3 color scale conditional formats";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
let data = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10];
worksheet.write_column(2, 1, data)?;
worksheet.write_column(2, 3, data)?;
worksheet.write_column(2, 5, data)?;
worksheet.write_column(2, 7, data)?;
worksheet.write_column(2, 9, data)?;
worksheet.write_column(2, 11, data)?;
// Set the column widths for clarity.
for col_num in 0..=12u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write 3 color scale formats with standard Excel colors.
let conditional_format = ConditionalFormat3ColorScale::new()
.set_minimum_color("F8696B")
.set_midpoint_color("FFEB84")
.set_maximum_color("63BE7B");
worksheet.add_conditional_format(2, 1, 11, 1, &conditional_format)?;
let conditional_format = ConditionalFormat3ColorScale::new()
.set_minimum_color("63BE7B")
.set_midpoint_color("FFEB84")
.set_maximum_color("F8696B");
worksheet.add_conditional_format(2, 3, 11, 3, &conditional_format)?;
let conditional_format = ConditionalFormat3ColorScale::new()
.set_minimum_color("F8696B")
.set_midpoint_color("FCFCFF")
.set_maximum_color("63BE7B");
worksheet.add_conditional_format(2, 5, 11, 5, &conditional_format)?;
let conditional_format = ConditionalFormat3ColorScale::new()
.set_minimum_color("63BE7B")
.set_midpoint_color("FCFCFF")
.set_maximum_color("F8696B");
worksheet.add_conditional_format(2, 7, 11, 7, &conditional_format)?;
let conditional_format = ConditionalFormat3ColorScale::new()
.set_minimum_color("F8696B")
.set_midpoint_color("FCFCFF")
.set_maximum_color("5A8AC6");
worksheet.add_conditional_format(2, 9, 11, 9, &conditional_format)?;
let conditional_format = ConditionalFormat3ColorScale::new()
.set_minimum_color("5A8AC6")
.set_midpoint_color("FCFCFF")
.set_maximum_color("F8696B");
worksheet.add_conditional_format(2, 11, 11, 11, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 11. Examples of data bars.
// -----------------------------------------------------------------------
let caption = "Examples of data bars";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write_with_format(0, 1, caption, &bold)?;
worksheet.write(1, 1, "Default")?;
worksheet.write(1, 3, "Default negative")?;
worksheet.write(1, 5, "User color")?;
worksheet.write(1, 7, "Changed direction")?;
// Write the worksheet data.
let data1 = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10];
let data2 = [6, 4, 2, -2, -4, -6, -4, -2, 2, 4];
worksheet.write_column(2, 1, data1)?;
worksheet.write_column(2, 3, data2)?;
worksheet.write_column(2, 5, data1)?;
worksheet.write_column(2, 7, data1)?;
// Write a standard Excel data bar.
let conditional_format = ConditionalFormatDataBar::new();
worksheet.add_conditional_format(2, 1, 11, 1, &conditional_format)?;
// Write a standard Excel data bar with negative data
let conditional_format = ConditionalFormatDataBar::new();
worksheet.add_conditional_format(2, 3, 11, 3, &conditional_format)?;
// Write a data bar with a user defined fill color.
let conditional_format = ConditionalFormatDataBar::new().set_fill_color("009933");
worksheet.add_conditional_format(2, 5, 11, 5, &conditional_format)?;
// Write a data bar with the direction changed.
let conditional_format = ConditionalFormatDataBar::new()
.set_direction(ConditionalFormatDataBarDirection::RightToLeft);
worksheet.add_conditional_format(2, 7, 11, 7, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 12. Examples of icon style conditional formats.
// -----------------------------------------------------------------------
let caption = "Examples of icon style conditional formats.";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write_with_format(0, 0, caption, &bold)?;
worksheet.write_with_format(1, 0, "Three Traffic lights - Green is highest", &indent)?;
worksheet.write_with_format(2, 0, "Reversed - Red is highest", &indent)?;
worksheet.write_with_format(3, 0, "Icons only - The number data is hidden", &indent)?;
worksheet.write_with_format(4, 0, "Other three-five icon examples", &bold)?;
worksheet.write_with_format(5, 0, "Three arrows", &indent)?;
worksheet.write_with_format(6, 0, "Three symbols", &indent)?;
worksheet.write_with_format(7, 0, "Three stars", &indent)?;
worksheet.write_with_format(8, 0, "Four arrows", &indent)?;
worksheet.write_with_format(9, 0, "Four circles - Red (highest) to Black", &indent)?;
worksheet.write_with_format(10, 0, "Four rating histograms", &indent)?;
worksheet.write_with_format(11, 0, "Five arrows", &indent)?;
worksheet.write_with_format(12, 0, "Five rating histograms", &indent)?;
worksheet.write_with_format(13, 0, "Five rating quadrants", &indent)?;
// Set the column width for clarity.
worksheet.set_column_width(0, 35)?;
// Write the worksheet data.
worksheet.write_row(1, 1, [1, 2, 3])?;
worksheet.write_row(2, 1, [1, 2, 3])?;
worksheet.write_row(3, 1, [1, 2, 3])?;
worksheet.write_row(5, 1, [1, 2, 3])?;
worksheet.write_row(6, 1, [1, 2, 3])?;
worksheet.write_row(7, 1, [1, 2, 3])?;
worksheet.write_row(8, 1, [1, 2, 3, 4])?;
worksheet.write_row(9, 1, [1, 2, 3, 4])?;
worksheet.write_row(10, 1, [1, 2, 3, 4])?;
worksheet.write_row(11, 1, [1, 2, 3, 4, 5])?;
worksheet.write_row(12, 1, [1, 2, 3, 4, 5])?;
worksheet.write_row(13, 1, [1, 2, 3, 4, 5])?;
// Three Traffic lights - Green is highest.
let conditional_format = ConditionalFormatIconSet::new()
.set_icon_type(ConditionalFormatIconType::ThreeTrafficLights);
worksheet.add_conditional_format(1, 1, 1, 3, &conditional_format)?;
// Reversed - Red is highest.
let conditional_format = ConditionalFormatIconSet::new()
.set_icon_type(ConditionalFormatIconType::ThreeTrafficLights)
.reverse_icons(true);
worksheet.add_conditional_format(2, 1, 2, 3, &conditional_format)?;
// Icons only - The number data is hidden.
let conditional_format = ConditionalFormatIconSet::new()
.set_icon_type(ConditionalFormatIconType::ThreeTrafficLights)
.show_icons_only(true);
worksheet.add_conditional_format(3, 1, 3, 3, &conditional_format)?;
// Three arrows.
let conditional_format =
ConditionalFormatIconSet::new().set_icon_type(ConditionalFormatIconType::ThreeArrows);
worksheet.add_conditional_format(5, 1, 5, 3, &conditional_format)?;
// Three symbols.
let conditional_format = ConditionalFormatIconSet::new()
.set_icon_type(ConditionalFormatIconType::ThreeSymbolsCircled);
worksheet.add_conditional_format(6, 1, 6, 3, &conditional_format)?;
// Three stars.
let conditional_format =
ConditionalFormatIconSet::new().set_icon_type(ConditionalFormatIconType::ThreeStars);
worksheet.add_conditional_format(7, 1, 7, 3, &conditional_format)?;
// Four Arrows.
let conditional_format =
ConditionalFormatIconSet::new().set_icon_type(ConditionalFormatIconType::FourArrows);
worksheet.add_conditional_format(8, 1, 8, 4, &conditional_format)?;
// Four circles - Red (highest) to Black (lowest).
let conditional_format =
ConditionalFormatIconSet::new().set_icon_type(ConditionalFormatIconType::FourRedToBlack);
worksheet.add_conditional_format(9, 1, 9, 4, &conditional_format)?;
// Four rating histograms.
let conditional_format =
ConditionalFormatIconSet::new().set_icon_type(ConditionalFormatIconType::FourHistograms);
worksheet.add_conditional_format(10, 1, 10, 4, &conditional_format)?;
// Four Arrows.
let conditional_format =
ConditionalFormatIconSet::new().set_icon_type(ConditionalFormatIconType::FiveArrows);
worksheet.add_conditional_format(11, 1, 11, 5, &conditional_format)?;
// Four rating histograms.
let conditional_format =
ConditionalFormatIconSet::new().set_icon_type(ConditionalFormatIconType::FiveHistograms);
worksheet.add_conditional_format(12, 1, 12, 5, &conditional_format)?;
// Four rating quadrants.
let conditional_format =
ConditionalFormatIconSet::new().set_icon_type(ConditionalFormatIconType::FiveQuadrants);
worksheet.add_conditional_format(13, 1, 13, 5, &conditional_format)?;
// -----------------------------------------------------------------------
// Save and close the file.
// -----------------------------------------------------------------------
workbook.save("conditional_formats.xlsx")?;
Ok(())
}Example 10. Examples of 3 color scale conditional formats.
See ConditionalFormat3ColorScale for more details.
Code to generate the above example:
// SPDX-License-Identifier: MIT OR Apache-2.0
//
// Copyright 2022-2024, John McNamara, jmcnamara@cpan.org
//! Example of how to add conditional formatting to a worksheet using the
//! rust_xlsxwriter library.
//!
//! Conditional formatting allows you to apply a format to a cell or a range of
//! cells based on user defined rule.
use rust_xlsxwriter::{
ConditionalFormat2ColorScale, ConditionalFormat3ColorScale, ConditionalFormatAverage,
ConditionalFormatAverageRule, ConditionalFormatCell, ConditionalFormatCellRule,
ConditionalFormatDataBar, ConditionalFormatDataBarDirection, ConditionalFormatDuplicate,
ConditionalFormatFormula, ConditionalFormatIconSet, ConditionalFormatIconType,
ConditionalFormatText, ConditionalFormatTextRule, ConditionalFormatTop, Format, Workbook,
XlsxError,
};
fn main() -> Result<(), XlsxError> {
// Create a new Excel file object.
let mut workbook = Workbook::new();
// Add a format. Light red fill with dark red text.
let format1 = Format::new()
.set_font_color("9C0006")
.set_background_color("FFC7CE");
// Add a format. Green fill with dark green text.
let format2 = Format::new()
.set_font_color("006100")
.set_background_color("C6EFCE");
// Add a format for headers.
let bold = Format::new().set_bold();
// Add a format for descriptions.
let indent = Format::new().set_indent(2);
// some sample data to run the conditional formatting against.
let data = [
[34, 72, 38, 30, 75, 48, 75, 66, 84, 86],
[6, 24, 1, 84, 54, 62, 60, 3, 26, 59],
[28, 79, 97, 13, 85, 93, 93, 22, 5, 14],
[27, 71, 40, 17, 18, 79, 90, 93, 29, 47],
[88, 25, 33, 23, 67, 1, 59, 79, 47, 36],
[24, 100, 20, 88, 29, 33, 38, 54, 54, 88],
[6, 57, 88, 28, 10, 26, 37, 7, 41, 48],
[52, 78, 1, 96, 26, 45, 47, 33, 96, 36],
[60, 54, 81, 66, 81, 90, 80, 93, 12, 55],
[70, 5, 46, 14, 71, 19, 66, 36, 41, 21],
];
// -----------------------------------------------------------------------
// Worksheet 1. Cell conditional formatting.
// -----------------------------------------------------------------------
let caption = "Cells with values >= 50 are in light red. Values < 50 are in light green.";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
worksheet.write_row_matrix(2, 1, data)?;
// Set the column widths for clarity.
for col_num in 1..=10u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write a conditional format over a range.
let conditional_format = ConditionalFormatCell::new()
.set_rule(ConditionalFormatCellRule::GreaterThanOrEqualTo(50))
.set_format(&format1);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// Write another conditional format over the same range.
let conditional_format = ConditionalFormatCell::new()
.set_rule(ConditionalFormatCellRule::LessThan(50))
.set_format(&format2);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 2. Cell conditional formatting with between ranges.
// -----------------------------------------------------------------------
let caption =
"Values between 30 and 70 are in light red. Values outside that range are in light green.";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
worksheet.write_row_matrix(2, 1, data)?;
// Set the column widths for clarity.
for col_num in 1..=10u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write a conditional format over a range.
let conditional_format = ConditionalFormatCell::new()
.set_rule(ConditionalFormatCellRule::Between(30, 70))
.set_format(&format1);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// Write another conditional format over the same range.
let conditional_format = ConditionalFormatCell::new()
.set_rule(ConditionalFormatCellRule::NotBetween(30, 70))
.set_format(&format2);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 3. Duplicate and Unique conditional formats.
// -----------------------------------------------------------------------
let caption = "Duplicate values are in light red. Unique values are in light green.";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
worksheet.write_row_matrix(2, 1, data)?;
// Set the column widths for clarity.
for col_num in 1..=10u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write a conditional format over a range.
let conditional_format = ConditionalFormatDuplicate::new().set_format(&format1);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// Invert the duplicate conditional format to show unique values in the
// same range.
let conditional_format = ConditionalFormatDuplicate::new()
.invert()
.set_format(&format2);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 4. Above and Below Average conditional formats.
// -----------------------------------------------------------------------
let caption = "Above average values are in light red. Below average values are in light green.";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
worksheet.write_row_matrix(2, 1, data)?;
// Set the column widths for clarity.
for col_num in 1..=10u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write a conditional format over a range. The default criteria is Above Average.
let conditional_format = ConditionalFormatAverage::new().set_format(&format1);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// Write another conditional format over the same range.
let conditional_format = ConditionalFormatAverage::new()
.set_rule(ConditionalFormatAverageRule::BelowAverage)
.set_format(&format2);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 5. Top and Bottom range conditional formats.
// -----------------------------------------------------------------------
let caption = "Top 10 values are in light red. Bottom 10 values are in light green.";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
worksheet.write_row_matrix(2, 1, data)?;
// Set the column widths for clarity.
for col_num in 1..=10u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write a conditional format over a range.
let conditional_format = ConditionalFormatTop::new()
.set_rule(rust_xlsxwriter::ConditionalFormatTopRule::Top(10))
.set_format(&format1);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// Also show the bottom values in the same range.
let conditional_format = ConditionalFormatTop::new()
.set_rule(rust_xlsxwriter::ConditionalFormatTopRule::Bottom(10))
.set_format(&format2);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 6. Cell conditional formatting in non-contiguous range.
// -----------------------------------------------------------------------
let caption = "Cells with values >= 50 are in light red. Values < 50 are in light green. Non-contiguous ranges.";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
worksheet.write_row_matrix(2, 1, data)?;
// Set the column widths for clarity.
for col_num in 1..=10u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write a conditional format over a non-contiguous range.
let conditional_format = ConditionalFormatCell::new()
.set_rule(ConditionalFormatCellRule::GreaterThanOrEqualTo(50))
.set_multi_range("B3:D6 I3:K6 B9:D12 I9:K12")
.set_format(&format1);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// Write another conditional format over the same range.
let conditional_format = ConditionalFormatCell::new()
.set_rule(ConditionalFormatCellRule::LessThan(50))
.set_multi_range("B3:D6 I3:K6 B9:D12 I9:K12")
.set_format(&format2);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 7. Formula conditional formatting.
// -----------------------------------------------------------------------
let caption = "Even numbered cells are in light green. Odd numbered cells are in light red.";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
worksheet.write_row_matrix(2, 1, data)?;
// Set the column widths for clarity.
for col_num in 1..=10u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write a conditional format over a range.
let conditional_format = ConditionalFormatFormula::new()
.set_rule("=ISODD(B3)")
.set_format(&format1);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// Write another conditional format over the same range.
let conditional_format = ConditionalFormatFormula::new()
.set_rule("=ISEVEN(B3)")
.set_format(&format2);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 8. Text style conditional formats.
// -----------------------------------------------------------------------
let caption =
"Column A shows words that contain the sub-word 'rust'. Column C shows words that start/end with 't'";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 0, caption)?;
// Add some sample data.
let word_list = [
"apocrustic",
"burstwort",
"cloudburst",
"crustification",
"distrustfulness",
"laurustine",
"outburst",
"rusticism",
"thunderburst",
"trustee",
"trustworthiness",
"unburstableness",
"unfrustratable",
];
worksheet.write_column(1, 0, word_list)?;
worksheet.write_column(1, 2, word_list)?;
// Set the column widths for clarity.
worksheet.set_column_width(0, 20)?;
worksheet.set_column_width(2, 20)?;
// Write a text "containing" conditional format over a range.
let conditional_format = ConditionalFormatText::new()
.set_rule(ConditionalFormatTextRule::Contains("rust".to_string()))
.set_format(&format2);
worksheet.add_conditional_format(1, 0, 13, 0, &conditional_format)?;
// Write a text "not containing" conditional format over the same range.
let conditional_format = ConditionalFormatText::new()
.set_rule(ConditionalFormatTextRule::DoesNotContain(
"rust".to_string(),
))
.set_format(&format1);
worksheet.add_conditional_format(1, 0, 13, 0, &conditional_format)?;
// Write a text "begins with" conditional format over a range.
let conditional_format = ConditionalFormatText::new()
.set_rule(ConditionalFormatTextRule::BeginsWith("t".to_string()))
.set_format(&format2);
worksheet.add_conditional_format(1, 2, 13, 2, &conditional_format)?;
// Write a text "ends with" conditional format over the same range.
let conditional_format = ConditionalFormatText::new()
.set_rule(ConditionalFormatTextRule::EndsWith("t".to_string()))
.set_format(&format1);
worksheet.add_conditional_format(1, 2, 13, 2, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 9. Examples of 2 color scale conditional formats.
// -----------------------------------------------------------------------
let caption = "Examples of 2 color scale conditional formats";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
let data = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10];
worksheet.write_column(2, 1, data)?;
worksheet.write_column(2, 3, data)?;
worksheet.write_column(2, 5, data)?;
worksheet.write_column(2, 7, data)?;
worksheet.write_column(2, 9, data)?;
worksheet.write_column(2, 11, data)?;
// Set the column widths for clarity.
for col_num in 0..=12u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write 2 color scale formats with standard Excel colors.
let conditional_format = ConditionalFormat2ColorScale::new()
.set_minimum_color("F8696B")
.set_maximum_color("FCFCFF");
worksheet.add_conditional_format(2, 1, 11, 1, &conditional_format)?;
let conditional_format = ConditionalFormat2ColorScale::new()
.set_minimum_color("FCFCFF")
.set_maximum_color("F8696B");
worksheet.add_conditional_format(2, 3, 11, 3, &conditional_format)?;
let conditional_format = ConditionalFormat2ColorScale::new()
.set_minimum_color("FCFCFF")
.set_maximum_color("63BE7B");
worksheet.add_conditional_format(2, 5, 11, 5, &conditional_format)?;
let conditional_format = ConditionalFormat2ColorScale::new()
.set_minimum_color("63BE7B")
.set_maximum_color("FCFCFF");
worksheet.add_conditional_format(2, 7, 11, 7, &conditional_format)?;
let conditional_format = ConditionalFormat2ColorScale::new()
.set_minimum_color("FFEF9C")
.set_maximum_color("63BE7B");
worksheet.add_conditional_format(2, 9, 11, 9, &conditional_format)?;
let conditional_format = ConditionalFormat2ColorScale::new()
.set_minimum_color("63BE7B")
.set_maximum_color("FFEF9C");
worksheet.add_conditional_format(2, 11, 11, 11, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 10. Examples of 3 color scale conditional formats.
// -----------------------------------------------------------------------
let caption = "Examples of 3 color scale conditional formats";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
let data = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10];
worksheet.write_column(2, 1, data)?;
worksheet.write_column(2, 3, data)?;
worksheet.write_column(2, 5, data)?;
worksheet.write_column(2, 7, data)?;
worksheet.write_column(2, 9, data)?;
worksheet.write_column(2, 11, data)?;
// Set the column widths for clarity.
for col_num in 0..=12u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write 3 color scale formats with standard Excel colors.
let conditional_format = ConditionalFormat3ColorScale::new()
.set_minimum_color("F8696B")
.set_midpoint_color("FFEB84")
.set_maximum_color("63BE7B");
worksheet.add_conditional_format(2, 1, 11, 1, &conditional_format)?;
let conditional_format = ConditionalFormat3ColorScale::new()
.set_minimum_color("63BE7B")
.set_midpoint_color("FFEB84")
.set_maximum_color("F8696B");
worksheet.add_conditional_format(2, 3, 11, 3, &conditional_format)?;
let conditional_format = ConditionalFormat3ColorScale::new()
.set_minimum_color("F8696B")
.set_midpoint_color("FCFCFF")
.set_maximum_color("63BE7B");
worksheet.add_conditional_format(2, 5, 11, 5, &conditional_format)?;
let conditional_format = ConditionalFormat3ColorScale::new()
.set_minimum_color("63BE7B")
.set_midpoint_color("FCFCFF")
.set_maximum_color("F8696B");
worksheet.add_conditional_format(2, 7, 11, 7, &conditional_format)?;
let conditional_format = ConditionalFormat3ColorScale::new()
.set_minimum_color("F8696B")
.set_midpoint_color("FCFCFF")
.set_maximum_color("5A8AC6");
worksheet.add_conditional_format(2, 9, 11, 9, &conditional_format)?;
let conditional_format = ConditionalFormat3ColorScale::new()
.set_minimum_color("5A8AC6")
.set_midpoint_color("FCFCFF")
.set_maximum_color("F8696B");
worksheet.add_conditional_format(2, 11, 11, 11, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 11. Examples of data bars.
// -----------------------------------------------------------------------
let caption = "Examples of data bars";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write_with_format(0, 1, caption, &bold)?;
worksheet.write(1, 1, "Default")?;
worksheet.write(1, 3, "Default negative")?;
worksheet.write(1, 5, "User color")?;
worksheet.write(1, 7, "Changed direction")?;
// Write the worksheet data.
let data1 = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10];
let data2 = [6, 4, 2, -2, -4, -6, -4, -2, 2, 4];
worksheet.write_column(2, 1, data1)?;
worksheet.write_column(2, 3, data2)?;
worksheet.write_column(2, 5, data1)?;
worksheet.write_column(2, 7, data1)?;
// Write a standard Excel data bar.
let conditional_format = ConditionalFormatDataBar::new();
worksheet.add_conditional_format(2, 1, 11, 1, &conditional_format)?;
// Write a standard Excel data bar with negative data
let conditional_format = ConditionalFormatDataBar::new();
worksheet.add_conditional_format(2, 3, 11, 3, &conditional_format)?;
// Write a data bar with a user defined fill color.
let conditional_format = ConditionalFormatDataBar::new().set_fill_color("009933");
worksheet.add_conditional_format(2, 5, 11, 5, &conditional_format)?;
// Write a data bar with the direction changed.
let conditional_format = ConditionalFormatDataBar::new()
.set_direction(ConditionalFormatDataBarDirection::RightToLeft);
worksheet.add_conditional_format(2, 7, 11, 7, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 12. Examples of icon style conditional formats.
// -----------------------------------------------------------------------
let caption = "Examples of icon style conditional formats.";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write_with_format(0, 0, caption, &bold)?;
worksheet.write_with_format(1, 0, "Three Traffic lights - Green is highest", &indent)?;
worksheet.write_with_format(2, 0, "Reversed - Red is highest", &indent)?;
worksheet.write_with_format(3, 0, "Icons only - The number data is hidden", &indent)?;
worksheet.write_with_format(4, 0, "Other three-five icon examples", &bold)?;
worksheet.write_with_format(5, 0, "Three arrows", &indent)?;
worksheet.write_with_format(6, 0, "Three symbols", &indent)?;
worksheet.write_with_format(7, 0, "Three stars", &indent)?;
worksheet.write_with_format(8, 0, "Four arrows", &indent)?;
worksheet.write_with_format(9, 0, "Four circles - Red (highest) to Black", &indent)?;
worksheet.write_with_format(10, 0, "Four rating histograms", &indent)?;
worksheet.write_with_format(11, 0, "Five arrows", &indent)?;
worksheet.write_with_format(12, 0, "Five rating histograms", &indent)?;
worksheet.write_with_format(13, 0, "Five rating quadrants", &indent)?;
// Set the column width for clarity.
worksheet.set_column_width(0, 35)?;
// Write the worksheet data.
worksheet.write_row(1, 1, [1, 2, 3])?;
worksheet.write_row(2, 1, [1, 2, 3])?;
worksheet.write_row(3, 1, [1, 2, 3])?;
worksheet.write_row(5, 1, [1, 2, 3])?;
worksheet.write_row(6, 1, [1, 2, 3])?;
worksheet.write_row(7, 1, [1, 2, 3])?;
worksheet.write_row(8, 1, [1, 2, 3, 4])?;
worksheet.write_row(9, 1, [1, 2, 3, 4])?;
worksheet.write_row(10, 1, [1, 2, 3, 4])?;
worksheet.write_row(11, 1, [1, 2, 3, 4, 5])?;
worksheet.write_row(12, 1, [1, 2, 3, 4, 5])?;
worksheet.write_row(13, 1, [1, 2, 3, 4, 5])?;
// Three Traffic lights - Green is highest.
let conditional_format = ConditionalFormatIconSet::new()
.set_icon_type(ConditionalFormatIconType::ThreeTrafficLights);
worksheet.add_conditional_format(1, 1, 1, 3, &conditional_format)?;
// Reversed - Red is highest.
let conditional_format = ConditionalFormatIconSet::new()
.set_icon_type(ConditionalFormatIconType::ThreeTrafficLights)
.reverse_icons(true);
worksheet.add_conditional_format(2, 1, 2, 3, &conditional_format)?;
// Icons only - The number data is hidden.
let conditional_format = ConditionalFormatIconSet::new()
.set_icon_type(ConditionalFormatIconType::ThreeTrafficLights)
.show_icons_only(true);
worksheet.add_conditional_format(3, 1, 3, 3, &conditional_format)?;
// Three arrows.
let conditional_format =
ConditionalFormatIconSet::new().set_icon_type(ConditionalFormatIconType::ThreeArrows);
worksheet.add_conditional_format(5, 1, 5, 3, &conditional_format)?;
// Three symbols.
let conditional_format = ConditionalFormatIconSet::new()
.set_icon_type(ConditionalFormatIconType::ThreeSymbolsCircled);
worksheet.add_conditional_format(6, 1, 6, 3, &conditional_format)?;
// Three stars.
let conditional_format =
ConditionalFormatIconSet::new().set_icon_type(ConditionalFormatIconType::ThreeStars);
worksheet.add_conditional_format(7, 1, 7, 3, &conditional_format)?;
// Four Arrows.
let conditional_format =
ConditionalFormatIconSet::new().set_icon_type(ConditionalFormatIconType::FourArrows);
worksheet.add_conditional_format(8, 1, 8, 4, &conditional_format)?;
// Four circles - Red (highest) to Black (lowest).
let conditional_format =
ConditionalFormatIconSet::new().set_icon_type(ConditionalFormatIconType::FourRedToBlack);
worksheet.add_conditional_format(9, 1, 9, 4, &conditional_format)?;
// Four rating histograms.
let conditional_format =
ConditionalFormatIconSet::new().set_icon_type(ConditionalFormatIconType::FourHistograms);
worksheet.add_conditional_format(10, 1, 10, 4, &conditional_format)?;
// Four Arrows.
let conditional_format =
ConditionalFormatIconSet::new().set_icon_type(ConditionalFormatIconType::FiveArrows);
worksheet.add_conditional_format(11, 1, 11, 5, &conditional_format)?;
// Four rating histograms.
let conditional_format =
ConditionalFormatIconSet::new().set_icon_type(ConditionalFormatIconType::FiveHistograms);
worksheet.add_conditional_format(12, 1, 12, 5, &conditional_format)?;
// Four rating quadrants.
let conditional_format =
ConditionalFormatIconSet::new().set_icon_type(ConditionalFormatIconType::FiveQuadrants);
worksheet.add_conditional_format(13, 1, 13, 5, &conditional_format)?;
// -----------------------------------------------------------------------
// Save and close the file.
// -----------------------------------------------------------------------
workbook.save("conditional_formats.xlsx")?;
Ok(())
}Example 11. Examples of data bars.
See ConditionalFormatDataBar for more details.
Code to generate the above example:
// SPDX-License-Identifier: MIT OR Apache-2.0
//
// Copyright 2022-2024, John McNamara, jmcnamara@cpan.org
//! Example of how to add conditional formatting to a worksheet using the
//! rust_xlsxwriter library.
//!
//! Conditional formatting allows you to apply a format to a cell or a range of
//! cells based on user defined rule.
use rust_xlsxwriter::{
ConditionalFormat2ColorScale, ConditionalFormat3ColorScale, ConditionalFormatAverage,
ConditionalFormatAverageRule, ConditionalFormatCell, ConditionalFormatCellRule,
ConditionalFormatDataBar, ConditionalFormatDataBarDirection, ConditionalFormatDuplicate,
ConditionalFormatFormula, ConditionalFormatIconSet, ConditionalFormatIconType,
ConditionalFormatText, ConditionalFormatTextRule, ConditionalFormatTop, Format, Workbook,
XlsxError,
};
fn main() -> Result<(), XlsxError> {
// Create a new Excel file object.
let mut workbook = Workbook::new();
// Add a format. Light red fill with dark red text.
let format1 = Format::new()
.set_font_color("9C0006")
.set_background_color("FFC7CE");
// Add a format. Green fill with dark green text.
let format2 = Format::new()
.set_font_color("006100")
.set_background_color("C6EFCE");
// Add a format for headers.
let bold = Format::new().set_bold();
// Add a format for descriptions.
let indent = Format::new().set_indent(2);
// some sample data to run the conditional formatting against.
let data = [
[34, 72, 38, 30, 75, 48, 75, 66, 84, 86],
[6, 24, 1, 84, 54, 62, 60, 3, 26, 59],
[28, 79, 97, 13, 85, 93, 93, 22, 5, 14],
[27, 71, 40, 17, 18, 79, 90, 93, 29, 47],
[88, 25, 33, 23, 67, 1, 59, 79, 47, 36],
[24, 100, 20, 88, 29, 33, 38, 54, 54, 88],
[6, 57, 88, 28, 10, 26, 37, 7, 41, 48],
[52, 78, 1, 96, 26, 45, 47, 33, 96, 36],
[60, 54, 81, 66, 81, 90, 80, 93, 12, 55],
[70, 5, 46, 14, 71, 19, 66, 36, 41, 21],
];
// -----------------------------------------------------------------------
// Worksheet 1. Cell conditional formatting.
// -----------------------------------------------------------------------
let caption = "Cells with values >= 50 are in light red. Values < 50 are in light green.";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
worksheet.write_row_matrix(2, 1, data)?;
// Set the column widths for clarity.
for col_num in 1..=10u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write a conditional format over a range.
let conditional_format = ConditionalFormatCell::new()
.set_rule(ConditionalFormatCellRule::GreaterThanOrEqualTo(50))
.set_format(&format1);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// Write another conditional format over the same range.
let conditional_format = ConditionalFormatCell::new()
.set_rule(ConditionalFormatCellRule::LessThan(50))
.set_format(&format2);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 2. Cell conditional formatting with between ranges.
// -----------------------------------------------------------------------
let caption =
"Values between 30 and 70 are in light red. Values outside that range are in light green.";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
worksheet.write_row_matrix(2, 1, data)?;
// Set the column widths for clarity.
for col_num in 1..=10u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write a conditional format over a range.
let conditional_format = ConditionalFormatCell::new()
.set_rule(ConditionalFormatCellRule::Between(30, 70))
.set_format(&format1);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// Write another conditional format over the same range.
let conditional_format = ConditionalFormatCell::new()
.set_rule(ConditionalFormatCellRule::NotBetween(30, 70))
.set_format(&format2);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 3. Duplicate and Unique conditional formats.
// -----------------------------------------------------------------------
let caption = "Duplicate values are in light red. Unique values are in light green.";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
worksheet.write_row_matrix(2, 1, data)?;
// Set the column widths for clarity.
for col_num in 1..=10u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write a conditional format over a range.
let conditional_format = ConditionalFormatDuplicate::new().set_format(&format1);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// Invert the duplicate conditional format to show unique values in the
// same range.
let conditional_format = ConditionalFormatDuplicate::new()
.invert()
.set_format(&format2);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 4. Above and Below Average conditional formats.
// -----------------------------------------------------------------------
let caption = "Above average values are in light red. Below average values are in light green.";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
worksheet.write_row_matrix(2, 1, data)?;
// Set the column widths for clarity.
for col_num in 1..=10u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write a conditional format over a range. The default criteria is Above Average.
let conditional_format = ConditionalFormatAverage::new().set_format(&format1);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// Write another conditional format over the same range.
let conditional_format = ConditionalFormatAverage::new()
.set_rule(ConditionalFormatAverageRule::BelowAverage)
.set_format(&format2);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 5. Top and Bottom range conditional formats.
// -----------------------------------------------------------------------
let caption = "Top 10 values are in light red. Bottom 10 values are in light green.";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
worksheet.write_row_matrix(2, 1, data)?;
// Set the column widths for clarity.
for col_num in 1..=10u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write a conditional format over a range.
let conditional_format = ConditionalFormatTop::new()
.set_rule(rust_xlsxwriter::ConditionalFormatTopRule::Top(10))
.set_format(&format1);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// Also show the bottom values in the same range.
let conditional_format = ConditionalFormatTop::new()
.set_rule(rust_xlsxwriter::ConditionalFormatTopRule::Bottom(10))
.set_format(&format2);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 6. Cell conditional formatting in non-contiguous range.
// -----------------------------------------------------------------------
let caption = "Cells with values >= 50 are in light red. Values < 50 are in light green. Non-contiguous ranges.";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
worksheet.write_row_matrix(2, 1, data)?;
// Set the column widths for clarity.
for col_num in 1..=10u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write a conditional format over a non-contiguous range.
let conditional_format = ConditionalFormatCell::new()
.set_rule(ConditionalFormatCellRule::GreaterThanOrEqualTo(50))
.set_multi_range("B3:D6 I3:K6 B9:D12 I9:K12")
.set_format(&format1);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// Write another conditional format over the same range.
let conditional_format = ConditionalFormatCell::new()
.set_rule(ConditionalFormatCellRule::LessThan(50))
.set_multi_range("B3:D6 I3:K6 B9:D12 I9:K12")
.set_format(&format2);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 7. Formula conditional formatting.
// -----------------------------------------------------------------------
let caption = "Even numbered cells are in light green. Odd numbered cells are in light red.";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
worksheet.write_row_matrix(2, 1, data)?;
// Set the column widths for clarity.
for col_num in 1..=10u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write a conditional format over a range.
let conditional_format = ConditionalFormatFormula::new()
.set_rule("=ISODD(B3)")
.set_format(&format1);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// Write another conditional format over the same range.
let conditional_format = ConditionalFormatFormula::new()
.set_rule("=ISEVEN(B3)")
.set_format(&format2);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 8. Text style conditional formats.
// -----------------------------------------------------------------------
let caption =
"Column A shows words that contain the sub-word 'rust'. Column C shows words that start/end with 't'";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 0, caption)?;
// Add some sample data.
let word_list = [
"apocrustic",
"burstwort",
"cloudburst",
"crustification",
"distrustfulness",
"laurustine",
"outburst",
"rusticism",
"thunderburst",
"trustee",
"trustworthiness",
"unburstableness",
"unfrustratable",
];
worksheet.write_column(1, 0, word_list)?;
worksheet.write_column(1, 2, word_list)?;
// Set the column widths for clarity.
worksheet.set_column_width(0, 20)?;
worksheet.set_column_width(2, 20)?;
// Write a text "containing" conditional format over a range.
let conditional_format = ConditionalFormatText::new()
.set_rule(ConditionalFormatTextRule::Contains("rust".to_string()))
.set_format(&format2);
worksheet.add_conditional_format(1, 0, 13, 0, &conditional_format)?;
// Write a text "not containing" conditional format over the same range.
let conditional_format = ConditionalFormatText::new()
.set_rule(ConditionalFormatTextRule::DoesNotContain(
"rust".to_string(),
))
.set_format(&format1);
worksheet.add_conditional_format(1, 0, 13, 0, &conditional_format)?;
// Write a text "begins with" conditional format over a range.
let conditional_format = ConditionalFormatText::new()
.set_rule(ConditionalFormatTextRule::BeginsWith("t".to_string()))
.set_format(&format2);
worksheet.add_conditional_format(1, 2, 13, 2, &conditional_format)?;
// Write a text "ends with" conditional format over the same range.
let conditional_format = ConditionalFormatText::new()
.set_rule(ConditionalFormatTextRule::EndsWith("t".to_string()))
.set_format(&format1);
worksheet.add_conditional_format(1, 2, 13, 2, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 9. Examples of 2 color scale conditional formats.
// -----------------------------------------------------------------------
let caption = "Examples of 2 color scale conditional formats";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
let data = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10];
worksheet.write_column(2, 1, data)?;
worksheet.write_column(2, 3, data)?;
worksheet.write_column(2, 5, data)?;
worksheet.write_column(2, 7, data)?;
worksheet.write_column(2, 9, data)?;
worksheet.write_column(2, 11, data)?;
// Set the column widths for clarity.
for col_num in 0..=12u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write 2 color scale formats with standard Excel colors.
let conditional_format = ConditionalFormat2ColorScale::new()
.set_minimum_color("F8696B")
.set_maximum_color("FCFCFF");
worksheet.add_conditional_format(2, 1, 11, 1, &conditional_format)?;
let conditional_format = ConditionalFormat2ColorScale::new()
.set_minimum_color("FCFCFF")
.set_maximum_color("F8696B");
worksheet.add_conditional_format(2, 3, 11, 3, &conditional_format)?;
let conditional_format = ConditionalFormat2ColorScale::new()
.set_minimum_color("FCFCFF")
.set_maximum_color("63BE7B");
worksheet.add_conditional_format(2, 5, 11, 5, &conditional_format)?;
let conditional_format = ConditionalFormat2ColorScale::new()
.set_minimum_color("63BE7B")
.set_maximum_color("FCFCFF");
worksheet.add_conditional_format(2, 7, 11, 7, &conditional_format)?;
let conditional_format = ConditionalFormat2ColorScale::new()
.set_minimum_color("FFEF9C")
.set_maximum_color("63BE7B");
worksheet.add_conditional_format(2, 9, 11, 9, &conditional_format)?;
let conditional_format = ConditionalFormat2ColorScale::new()
.set_minimum_color("63BE7B")
.set_maximum_color("FFEF9C");
worksheet.add_conditional_format(2, 11, 11, 11, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 10. Examples of 3 color scale conditional formats.
// -----------------------------------------------------------------------
let caption = "Examples of 3 color scale conditional formats";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
let data = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10];
worksheet.write_column(2, 1, data)?;
worksheet.write_column(2, 3, data)?;
worksheet.write_column(2, 5, data)?;
worksheet.write_column(2, 7, data)?;
worksheet.write_column(2, 9, data)?;
worksheet.write_column(2, 11, data)?;
// Set the column widths for clarity.
for col_num in 0..=12u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write 3 color scale formats with standard Excel colors.
let conditional_format = ConditionalFormat3ColorScale::new()
.set_minimum_color("F8696B")
.set_midpoint_color("FFEB84")
.set_maximum_color("63BE7B");
worksheet.add_conditional_format(2, 1, 11, 1, &conditional_format)?;
let conditional_format = ConditionalFormat3ColorScale::new()
.set_minimum_color("63BE7B")
.set_midpoint_color("FFEB84")
.set_maximum_color("F8696B");
worksheet.add_conditional_format(2, 3, 11, 3, &conditional_format)?;
let conditional_format = ConditionalFormat3ColorScale::new()
.set_minimum_color("F8696B")
.set_midpoint_color("FCFCFF")
.set_maximum_color("63BE7B");
worksheet.add_conditional_format(2, 5, 11, 5, &conditional_format)?;
let conditional_format = ConditionalFormat3ColorScale::new()
.set_minimum_color("63BE7B")
.set_midpoint_color("FCFCFF")
.set_maximum_color("F8696B");
worksheet.add_conditional_format(2, 7, 11, 7, &conditional_format)?;
let conditional_format = ConditionalFormat3ColorScale::new()
.set_minimum_color("F8696B")
.set_midpoint_color("FCFCFF")
.set_maximum_color("5A8AC6");
worksheet.add_conditional_format(2, 9, 11, 9, &conditional_format)?;
let conditional_format = ConditionalFormat3ColorScale::new()
.set_minimum_color("5A8AC6")
.set_midpoint_color("FCFCFF")
.set_maximum_color("F8696B");
worksheet.add_conditional_format(2, 11, 11, 11, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 11. Examples of data bars.
// -----------------------------------------------------------------------
let caption = "Examples of data bars";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write_with_format(0, 1, caption, &bold)?;
worksheet.write(1, 1, "Default")?;
worksheet.write(1, 3, "Default negative")?;
worksheet.write(1, 5, "User color")?;
worksheet.write(1, 7, "Changed direction")?;
// Write the worksheet data.
let data1 = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10];
let data2 = [6, 4, 2, -2, -4, -6, -4, -2, 2, 4];
worksheet.write_column(2, 1, data1)?;
worksheet.write_column(2, 3, data2)?;
worksheet.write_column(2, 5, data1)?;
worksheet.write_column(2, 7, data1)?;
// Write a standard Excel data bar.
let conditional_format = ConditionalFormatDataBar::new();
worksheet.add_conditional_format(2, 1, 11, 1, &conditional_format)?;
// Write a standard Excel data bar with negative data
let conditional_format = ConditionalFormatDataBar::new();
worksheet.add_conditional_format(2, 3, 11, 3, &conditional_format)?;
// Write a data bar with a user defined fill color.
let conditional_format = ConditionalFormatDataBar::new().set_fill_color("009933");
worksheet.add_conditional_format(2, 5, 11, 5, &conditional_format)?;
// Write a data bar with the direction changed.
let conditional_format = ConditionalFormatDataBar::new()
.set_direction(ConditionalFormatDataBarDirection::RightToLeft);
worksheet.add_conditional_format(2, 7, 11, 7, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 12. Examples of icon style conditional formats.
// -----------------------------------------------------------------------
let caption = "Examples of icon style conditional formats.";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write_with_format(0, 0, caption, &bold)?;
worksheet.write_with_format(1, 0, "Three Traffic lights - Green is highest", &indent)?;
worksheet.write_with_format(2, 0, "Reversed - Red is highest", &indent)?;
worksheet.write_with_format(3, 0, "Icons only - The number data is hidden", &indent)?;
worksheet.write_with_format(4, 0, "Other three-five icon examples", &bold)?;
worksheet.write_with_format(5, 0, "Three arrows", &indent)?;
worksheet.write_with_format(6, 0, "Three symbols", &indent)?;
worksheet.write_with_format(7, 0, "Three stars", &indent)?;
worksheet.write_with_format(8, 0, "Four arrows", &indent)?;
worksheet.write_with_format(9, 0, "Four circles - Red (highest) to Black", &indent)?;
worksheet.write_with_format(10, 0, "Four rating histograms", &indent)?;
worksheet.write_with_format(11, 0, "Five arrows", &indent)?;
worksheet.write_with_format(12, 0, "Five rating histograms", &indent)?;
worksheet.write_with_format(13, 0, "Five rating quadrants", &indent)?;
// Set the column width for clarity.
worksheet.set_column_width(0, 35)?;
// Write the worksheet data.
worksheet.write_row(1, 1, [1, 2, 3])?;
worksheet.write_row(2, 1, [1, 2, 3])?;
worksheet.write_row(3, 1, [1, 2, 3])?;
worksheet.write_row(5, 1, [1, 2, 3])?;
worksheet.write_row(6, 1, [1, 2, 3])?;
worksheet.write_row(7, 1, [1, 2, 3])?;
worksheet.write_row(8, 1, [1, 2, 3, 4])?;
worksheet.write_row(9, 1, [1, 2, 3, 4])?;
worksheet.write_row(10, 1, [1, 2, 3, 4])?;
worksheet.write_row(11, 1, [1, 2, 3, 4, 5])?;
worksheet.write_row(12, 1, [1, 2, 3, 4, 5])?;
worksheet.write_row(13, 1, [1, 2, 3, 4, 5])?;
// Three Traffic lights - Green is highest.
let conditional_format = ConditionalFormatIconSet::new()
.set_icon_type(ConditionalFormatIconType::ThreeTrafficLights);
worksheet.add_conditional_format(1, 1, 1, 3, &conditional_format)?;
// Reversed - Red is highest.
let conditional_format = ConditionalFormatIconSet::new()
.set_icon_type(ConditionalFormatIconType::ThreeTrafficLights)
.reverse_icons(true);
worksheet.add_conditional_format(2, 1, 2, 3, &conditional_format)?;
// Icons only - The number data is hidden.
let conditional_format = ConditionalFormatIconSet::new()
.set_icon_type(ConditionalFormatIconType::ThreeTrafficLights)
.show_icons_only(true);
worksheet.add_conditional_format(3, 1, 3, 3, &conditional_format)?;
// Three arrows.
let conditional_format =
ConditionalFormatIconSet::new().set_icon_type(ConditionalFormatIconType::ThreeArrows);
worksheet.add_conditional_format(5, 1, 5, 3, &conditional_format)?;
// Three symbols.
let conditional_format = ConditionalFormatIconSet::new()
.set_icon_type(ConditionalFormatIconType::ThreeSymbolsCircled);
worksheet.add_conditional_format(6, 1, 6, 3, &conditional_format)?;
// Three stars.
let conditional_format =
ConditionalFormatIconSet::new().set_icon_type(ConditionalFormatIconType::ThreeStars);
worksheet.add_conditional_format(7, 1, 7, 3, &conditional_format)?;
// Four Arrows.
let conditional_format =
ConditionalFormatIconSet::new().set_icon_type(ConditionalFormatIconType::FourArrows);
worksheet.add_conditional_format(8, 1, 8, 4, &conditional_format)?;
// Four circles - Red (highest) to Black (lowest).
let conditional_format =
ConditionalFormatIconSet::new().set_icon_type(ConditionalFormatIconType::FourRedToBlack);
worksheet.add_conditional_format(9, 1, 9, 4, &conditional_format)?;
// Four rating histograms.
let conditional_format =
ConditionalFormatIconSet::new().set_icon_type(ConditionalFormatIconType::FourHistograms);
worksheet.add_conditional_format(10, 1, 10, 4, &conditional_format)?;
// Four Arrows.
let conditional_format =
ConditionalFormatIconSet::new().set_icon_type(ConditionalFormatIconType::FiveArrows);
worksheet.add_conditional_format(11, 1, 11, 5, &conditional_format)?;
// Four rating histograms.
let conditional_format =
ConditionalFormatIconSet::new().set_icon_type(ConditionalFormatIconType::FiveHistograms);
worksheet.add_conditional_format(12, 1, 12, 5, &conditional_format)?;
// Four rating quadrants.
let conditional_format =
ConditionalFormatIconSet::new().set_icon_type(ConditionalFormatIconType::FiveQuadrants);
worksheet.add_conditional_format(13, 1, 13, 5, &conditional_format)?;
// -----------------------------------------------------------------------
// Save and close the file.
// -----------------------------------------------------------------------
workbook.save("conditional_formats.xlsx")?;
Ok(())
}Example 12. Examples of icon style conditional formats.
See ConditionalFormatIconSet for more details.
Code to generate the above example:
// SPDX-License-Identifier: MIT OR Apache-2.0
//
// Copyright 2022-2024, John McNamara, jmcnamara@cpan.org
//! Example of how to add conditional formatting to a worksheet using the
//! rust_xlsxwriter library.
//!
//! Conditional formatting allows you to apply a format to a cell or a range of
//! cells based on user defined rule.
use rust_xlsxwriter::{
ConditionalFormat2ColorScale, ConditionalFormat3ColorScale, ConditionalFormatAverage,
ConditionalFormatAverageRule, ConditionalFormatCell, ConditionalFormatCellRule,
ConditionalFormatDataBar, ConditionalFormatDataBarDirection, ConditionalFormatDuplicate,
ConditionalFormatFormula, ConditionalFormatIconSet, ConditionalFormatIconType,
ConditionalFormatText, ConditionalFormatTextRule, ConditionalFormatTop, Format, Workbook,
XlsxError,
};
fn main() -> Result<(), XlsxError> {
// Create a new Excel file object.
let mut workbook = Workbook::new();
// Add a format. Light red fill with dark red text.
let format1 = Format::new()
.set_font_color("9C0006")
.set_background_color("FFC7CE");
// Add a format. Green fill with dark green text.
let format2 = Format::new()
.set_font_color("006100")
.set_background_color("C6EFCE");
// Add a format for headers.
let bold = Format::new().set_bold();
// Add a format for descriptions.
let indent = Format::new().set_indent(2);
// some sample data to run the conditional formatting against.
let data = [
[34, 72, 38, 30, 75, 48, 75, 66, 84, 86],
[6, 24, 1, 84, 54, 62, 60, 3, 26, 59],
[28, 79, 97, 13, 85, 93, 93, 22, 5, 14],
[27, 71, 40, 17, 18, 79, 90, 93, 29, 47],
[88, 25, 33, 23, 67, 1, 59, 79, 47, 36],
[24, 100, 20, 88, 29, 33, 38, 54, 54, 88],
[6, 57, 88, 28, 10, 26, 37, 7, 41, 48],
[52, 78, 1, 96, 26, 45, 47, 33, 96, 36],
[60, 54, 81, 66, 81, 90, 80, 93, 12, 55],
[70, 5, 46, 14, 71, 19, 66, 36, 41, 21],
];
// -----------------------------------------------------------------------
// Worksheet 1. Cell conditional formatting.
// -----------------------------------------------------------------------
let caption = "Cells with values >= 50 are in light red. Values < 50 are in light green.";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
worksheet.write_row_matrix(2, 1, data)?;
// Set the column widths for clarity.
for col_num in 1..=10u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write a conditional format over a range.
let conditional_format = ConditionalFormatCell::new()
.set_rule(ConditionalFormatCellRule::GreaterThanOrEqualTo(50))
.set_format(&format1);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// Write another conditional format over the same range.
let conditional_format = ConditionalFormatCell::new()
.set_rule(ConditionalFormatCellRule::LessThan(50))
.set_format(&format2);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 2. Cell conditional formatting with between ranges.
// -----------------------------------------------------------------------
let caption =
"Values between 30 and 70 are in light red. Values outside that range are in light green.";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
worksheet.write_row_matrix(2, 1, data)?;
// Set the column widths for clarity.
for col_num in 1..=10u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write a conditional format over a range.
let conditional_format = ConditionalFormatCell::new()
.set_rule(ConditionalFormatCellRule::Between(30, 70))
.set_format(&format1);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// Write another conditional format over the same range.
let conditional_format = ConditionalFormatCell::new()
.set_rule(ConditionalFormatCellRule::NotBetween(30, 70))
.set_format(&format2);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 3. Duplicate and Unique conditional formats.
// -----------------------------------------------------------------------
let caption = "Duplicate values are in light red. Unique values are in light green.";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
worksheet.write_row_matrix(2, 1, data)?;
// Set the column widths for clarity.
for col_num in 1..=10u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write a conditional format over a range.
let conditional_format = ConditionalFormatDuplicate::new().set_format(&format1);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// Invert the duplicate conditional format to show unique values in the
// same range.
let conditional_format = ConditionalFormatDuplicate::new()
.invert()
.set_format(&format2);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 4. Above and Below Average conditional formats.
// -----------------------------------------------------------------------
let caption = "Above average values are in light red. Below average values are in light green.";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
worksheet.write_row_matrix(2, 1, data)?;
// Set the column widths for clarity.
for col_num in 1..=10u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write a conditional format over a range. The default criteria is Above Average.
let conditional_format = ConditionalFormatAverage::new().set_format(&format1);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// Write another conditional format over the same range.
let conditional_format = ConditionalFormatAverage::new()
.set_rule(ConditionalFormatAverageRule::BelowAverage)
.set_format(&format2);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 5. Top and Bottom range conditional formats.
// -----------------------------------------------------------------------
let caption = "Top 10 values are in light red. Bottom 10 values are in light green.";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
worksheet.write_row_matrix(2, 1, data)?;
// Set the column widths for clarity.
for col_num in 1..=10u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write a conditional format over a range.
let conditional_format = ConditionalFormatTop::new()
.set_rule(rust_xlsxwriter::ConditionalFormatTopRule::Top(10))
.set_format(&format1);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// Also show the bottom values in the same range.
let conditional_format = ConditionalFormatTop::new()
.set_rule(rust_xlsxwriter::ConditionalFormatTopRule::Bottom(10))
.set_format(&format2);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 6. Cell conditional formatting in non-contiguous range.
// -----------------------------------------------------------------------
let caption = "Cells with values >= 50 are in light red. Values < 50 are in light green. Non-contiguous ranges.";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
worksheet.write_row_matrix(2, 1, data)?;
// Set the column widths for clarity.
for col_num in 1..=10u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write a conditional format over a non-contiguous range.
let conditional_format = ConditionalFormatCell::new()
.set_rule(ConditionalFormatCellRule::GreaterThanOrEqualTo(50))
.set_multi_range("B3:D6 I3:K6 B9:D12 I9:K12")
.set_format(&format1);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// Write another conditional format over the same range.
let conditional_format = ConditionalFormatCell::new()
.set_rule(ConditionalFormatCellRule::LessThan(50))
.set_multi_range("B3:D6 I3:K6 B9:D12 I9:K12")
.set_format(&format2);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 7. Formula conditional formatting.
// -----------------------------------------------------------------------
let caption = "Even numbered cells are in light green. Odd numbered cells are in light red.";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
worksheet.write_row_matrix(2, 1, data)?;
// Set the column widths for clarity.
for col_num in 1..=10u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write a conditional format over a range.
let conditional_format = ConditionalFormatFormula::new()
.set_rule("=ISODD(B3)")
.set_format(&format1);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// Write another conditional format over the same range.
let conditional_format = ConditionalFormatFormula::new()
.set_rule("=ISEVEN(B3)")
.set_format(&format2);
worksheet.add_conditional_format(2, 1, 11, 10, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 8. Text style conditional formats.
// -----------------------------------------------------------------------
let caption =
"Column A shows words that contain the sub-word 'rust'. Column C shows words that start/end with 't'";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 0, caption)?;
// Add some sample data.
let word_list = [
"apocrustic",
"burstwort",
"cloudburst",
"crustification",
"distrustfulness",
"laurustine",
"outburst",
"rusticism",
"thunderburst",
"trustee",
"trustworthiness",
"unburstableness",
"unfrustratable",
];
worksheet.write_column(1, 0, word_list)?;
worksheet.write_column(1, 2, word_list)?;
// Set the column widths for clarity.
worksheet.set_column_width(0, 20)?;
worksheet.set_column_width(2, 20)?;
// Write a text "containing" conditional format over a range.
let conditional_format = ConditionalFormatText::new()
.set_rule(ConditionalFormatTextRule::Contains("rust".to_string()))
.set_format(&format2);
worksheet.add_conditional_format(1, 0, 13, 0, &conditional_format)?;
// Write a text "not containing" conditional format over the same range.
let conditional_format = ConditionalFormatText::new()
.set_rule(ConditionalFormatTextRule::DoesNotContain(
"rust".to_string(),
))
.set_format(&format1);
worksheet.add_conditional_format(1, 0, 13, 0, &conditional_format)?;
// Write a text "begins with" conditional format over a range.
let conditional_format = ConditionalFormatText::new()
.set_rule(ConditionalFormatTextRule::BeginsWith("t".to_string()))
.set_format(&format2);
worksheet.add_conditional_format(1, 2, 13, 2, &conditional_format)?;
// Write a text "ends with" conditional format over the same range.
let conditional_format = ConditionalFormatText::new()
.set_rule(ConditionalFormatTextRule::EndsWith("t".to_string()))
.set_format(&format1);
worksheet.add_conditional_format(1, 2, 13, 2, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 9. Examples of 2 color scale conditional formats.
// -----------------------------------------------------------------------
let caption = "Examples of 2 color scale conditional formats";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
let data = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10];
worksheet.write_column(2, 1, data)?;
worksheet.write_column(2, 3, data)?;
worksheet.write_column(2, 5, data)?;
worksheet.write_column(2, 7, data)?;
worksheet.write_column(2, 9, data)?;
worksheet.write_column(2, 11, data)?;
// Set the column widths for clarity.
for col_num in 0..=12u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write 2 color scale formats with standard Excel colors.
let conditional_format = ConditionalFormat2ColorScale::new()
.set_minimum_color("F8696B")
.set_maximum_color("FCFCFF");
worksheet.add_conditional_format(2, 1, 11, 1, &conditional_format)?;
let conditional_format = ConditionalFormat2ColorScale::new()
.set_minimum_color("FCFCFF")
.set_maximum_color("F8696B");
worksheet.add_conditional_format(2, 3, 11, 3, &conditional_format)?;
let conditional_format = ConditionalFormat2ColorScale::new()
.set_minimum_color("FCFCFF")
.set_maximum_color("63BE7B");
worksheet.add_conditional_format(2, 5, 11, 5, &conditional_format)?;
let conditional_format = ConditionalFormat2ColorScale::new()
.set_minimum_color("63BE7B")
.set_maximum_color("FCFCFF");
worksheet.add_conditional_format(2, 7, 11, 7, &conditional_format)?;
let conditional_format = ConditionalFormat2ColorScale::new()
.set_minimum_color("FFEF9C")
.set_maximum_color("63BE7B");
worksheet.add_conditional_format(2, 9, 11, 9, &conditional_format)?;
let conditional_format = ConditionalFormat2ColorScale::new()
.set_minimum_color("63BE7B")
.set_maximum_color("FFEF9C");
worksheet.add_conditional_format(2, 11, 11, 11, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 10. Examples of 3 color scale conditional formats.
// -----------------------------------------------------------------------
let caption = "Examples of 3 color scale conditional formats";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write(0, 1, caption)?;
// Write the worksheet data.
let data = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10];
worksheet.write_column(2, 1, data)?;
worksheet.write_column(2, 3, data)?;
worksheet.write_column(2, 5, data)?;
worksheet.write_column(2, 7, data)?;
worksheet.write_column(2, 9, data)?;
worksheet.write_column(2, 11, data)?;
// Set the column widths for clarity.
for col_num in 0..=12u16 {
worksheet.set_column_width(col_num, 6)?;
}
// Write 3 color scale formats with standard Excel colors.
let conditional_format = ConditionalFormat3ColorScale::new()
.set_minimum_color("F8696B")
.set_midpoint_color("FFEB84")
.set_maximum_color("63BE7B");
worksheet.add_conditional_format(2, 1, 11, 1, &conditional_format)?;
let conditional_format = ConditionalFormat3ColorScale::new()
.set_minimum_color("63BE7B")
.set_midpoint_color("FFEB84")
.set_maximum_color("F8696B");
worksheet.add_conditional_format(2, 3, 11, 3, &conditional_format)?;
let conditional_format = ConditionalFormat3ColorScale::new()
.set_minimum_color("F8696B")
.set_midpoint_color("FCFCFF")
.set_maximum_color("63BE7B");
worksheet.add_conditional_format(2, 5, 11, 5, &conditional_format)?;
let conditional_format = ConditionalFormat3ColorScale::new()
.set_minimum_color("63BE7B")
.set_midpoint_color("FCFCFF")
.set_maximum_color("F8696B");
worksheet.add_conditional_format(2, 7, 11, 7, &conditional_format)?;
let conditional_format = ConditionalFormat3ColorScale::new()
.set_minimum_color("F8696B")
.set_midpoint_color("FCFCFF")
.set_maximum_color("5A8AC6");
worksheet.add_conditional_format(2, 9, 11, 9, &conditional_format)?;
let conditional_format = ConditionalFormat3ColorScale::new()
.set_minimum_color("5A8AC6")
.set_midpoint_color("FCFCFF")
.set_maximum_color("F8696B");
worksheet.add_conditional_format(2, 11, 11, 11, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 11. Examples of data bars.
// -----------------------------------------------------------------------
let caption = "Examples of data bars";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write_with_format(0, 1, caption, &bold)?;
worksheet.write(1, 1, "Default")?;
worksheet.write(1, 3, "Default negative")?;
worksheet.write(1, 5, "User color")?;
worksheet.write(1, 7, "Changed direction")?;
// Write the worksheet data.
let data1 = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10];
let data2 = [6, 4, 2, -2, -4, -6, -4, -2, 2, 4];
worksheet.write_column(2, 1, data1)?;
worksheet.write_column(2, 3, data2)?;
worksheet.write_column(2, 5, data1)?;
worksheet.write_column(2, 7, data1)?;
// Write a standard Excel data bar.
let conditional_format = ConditionalFormatDataBar::new();
worksheet.add_conditional_format(2, 1, 11, 1, &conditional_format)?;
// Write a standard Excel data bar with negative data
let conditional_format = ConditionalFormatDataBar::new();
worksheet.add_conditional_format(2, 3, 11, 3, &conditional_format)?;
// Write a data bar with a user defined fill color.
let conditional_format = ConditionalFormatDataBar::new().set_fill_color("009933");
worksheet.add_conditional_format(2, 5, 11, 5, &conditional_format)?;
// Write a data bar with the direction changed.
let conditional_format = ConditionalFormatDataBar::new()
.set_direction(ConditionalFormatDataBarDirection::RightToLeft);
worksheet.add_conditional_format(2, 7, 11, 7, &conditional_format)?;
// -----------------------------------------------------------------------
// Worksheet 12. Examples of icon style conditional formats.
// -----------------------------------------------------------------------
let caption = "Examples of icon style conditional formats.";
// Add a worksheet to the workbook.
let worksheet = workbook.add_worksheet();
// Write the caption.
worksheet.write_with_format(0, 0, caption, &bold)?;
worksheet.write_with_format(1, 0, "Three Traffic lights - Green is highest", &indent)?;
worksheet.write_with_format(2, 0, "Reversed - Red is highest", &indent)?;
worksheet.write_with_format(3, 0, "Icons only - The number data is hidden", &indent)?;
worksheet.write_with_format(4, 0, "Other three-five icon examples", &bold)?;
worksheet.write_with_format(5, 0, "Three arrows", &indent)?;
worksheet.write_with_format(6, 0, "Three symbols", &indent)?;
worksheet.write_with_format(7, 0, "Three stars", &indent)?;
worksheet.write_with_format(8, 0, "Four arrows", &indent)?;
worksheet.write_with_format(9, 0, "Four circles - Red (highest) to Black", &indent)?;
worksheet.write_with_format(10, 0, "Four rating histograms", &indent)?;
worksheet.write_with_format(11, 0, "Five arrows", &indent)?;
worksheet.write_with_format(12, 0, "Five rating histograms", &indent)?;
worksheet.write_with_format(13, 0, "Five rating quadrants", &indent)?;
// Set the column width for clarity.
worksheet.set_column_width(0, 35)?;
// Write the worksheet data.
worksheet.write_row(1, 1, [1, 2, 3])?;
worksheet.write_row(2, 1, [1, 2, 3])?;
worksheet.write_row(3, 1, [1, 2, 3])?;
worksheet.write_row(5, 1, [1, 2, 3])?;
worksheet.write_row(6, 1, [1, 2, 3])?;
worksheet.write_row(7, 1, [1, 2, 3])?;
worksheet.write_row(8, 1, [1, 2, 3, 4])?;
worksheet.write_row(9, 1, [1, 2, 3, 4])?;
worksheet.write_row(10, 1, [1, 2, 3, 4])?;
worksheet.write_row(11, 1, [1, 2, 3, 4, 5])?;
worksheet.write_row(12, 1, [1, 2, 3, 4, 5])?;
worksheet.write_row(13, 1, [1, 2, 3, 4, 5])?;
// Three Traffic lights - Green is highest.
let conditional_format = ConditionalFormatIconSet::new()
.set_icon_type(ConditionalFormatIconType::ThreeTrafficLights);
worksheet.add_conditional_format(1, 1, 1, 3, &conditional_format)?;
// Reversed - Red is highest.
let conditional_format = ConditionalFormatIconSet::new()
.set_icon_type(ConditionalFormatIconType::ThreeTrafficLights)
.reverse_icons(true);
worksheet.add_conditional_format(2, 1, 2, 3, &conditional_format)?;
// Icons only - The number data is hidden.
let conditional_format = ConditionalFormatIconSet::new()
.set_icon_type(ConditionalFormatIconType::ThreeTrafficLights)
.show_icons_only(true);
worksheet.add_conditional_format(3, 1, 3, 3, &conditional_format)?;
// Three arrows.
let conditional_format =
ConditionalFormatIconSet::new().set_icon_type(ConditionalFormatIconType::ThreeArrows);
worksheet.add_conditional_format(5, 1, 5, 3, &conditional_format)?;
// Three symbols.
let conditional_format = ConditionalFormatIconSet::new()
.set_icon_type(ConditionalFormatIconType::ThreeSymbolsCircled);
worksheet.add_conditional_format(6, 1, 6, 3, &conditional_format)?;
// Three stars.
let conditional_format =
ConditionalFormatIconSet::new().set_icon_type(ConditionalFormatIconType::ThreeStars);
worksheet.add_conditional_format(7, 1, 7, 3, &conditional_format)?;
// Four Arrows.
let conditional_format =
ConditionalFormatIconSet::new().set_icon_type(ConditionalFormatIconType::FourArrows);
worksheet.add_conditional_format(8, 1, 8, 4, &conditional_format)?;
// Four circles - Red (highest) to Black (lowest).
let conditional_format =
ConditionalFormatIconSet::new().set_icon_type(ConditionalFormatIconType::FourRedToBlack);
worksheet.add_conditional_format(9, 1, 9, 4, &conditional_format)?;
// Four rating histograms.
let conditional_format =
ConditionalFormatIconSet::new().set_icon_type(ConditionalFormatIconType::FourHistograms);
worksheet.add_conditional_format(10, 1, 10, 4, &conditional_format)?;
// Four Arrows.
let conditional_format =
ConditionalFormatIconSet::new().set_icon_type(ConditionalFormatIconType::FiveArrows);
worksheet.add_conditional_format(11, 1, 11, 5, &conditional_format)?;
// Four rating histograms.
let conditional_format =
ConditionalFormatIconSet::new().set_icon_type(ConditionalFormatIconType::FiveHistograms);
worksheet.add_conditional_format(12, 1, 12, 5, &conditional_format)?;
// Four rating quadrants.
let conditional_format =
ConditionalFormatIconSet::new().set_icon_type(ConditionalFormatIconType::FiveQuadrants);
worksheet.add_conditional_format(13, 1, 13, 5, &conditional_format)?;
// -----------------------------------------------------------------------
// Save and close the file.
// -----------------------------------------------------------------------
workbook.save("conditional_formats.xlsx")?;
Ok(())
}