UmyaSpreadsheet.ConditionalFormatting (umya_spreadsheet_ex v0.7.0)

spreadsheet - The spreadsheet struct
sheet_name - The name of the sheet
range - The cell range to apply formatting to (e.g., "A1:A10")
rule_type - The type of rule: "above", "below", "above_equal", "below_equal"
std_dev - Optional standard deviation for more advanced rules (nil for basic average)
format_style - The color to apply when the condition is met (e.g., "#FF0000" for red)

Rule Types

"above" - Highlight cells above the average
"below" - Highlight cells below the average
"above_equal" - Highlight cells above or equal to the average
"below_equal" - Highlight cells below or equal to the average

Examples

{:ok, spreadsheet} = UmyaSpreadsheet.read_file("input.xlsx")

# Highlight cells above average in green
:ok = UmyaSpreadsheet.add_above_below_average_rule(
  spreadsheet,
  "Sheet1",
  "A1:A10",
  "above",
  nil,
  "#00FF00"
)

# Highlight cells below average in red
:ok = UmyaSpreadsheet.add_above_below_average_rule(
  spreadsheet,
  "Sheet1",
  "B1:B10",
  "below",
  nil,
  "#FF0000"
)

# Highlight cells above average plus one standard deviation in blue
:ok = UmyaSpreadsheet.add_above_below_average_rule(
  spreadsheet,
  "Sheet1",
  "C1:C10",
  "above",
  1,
  "#0000FF"
)

add_cell_is_rule(spreadsheet, sheet_name, range, operator, value1, value2, format_style)

Adds a cell "is" rule for conditional formatting based on text conditions.

Parameters

spreadsheet - The spreadsheet struct
sheet_name - The name of the sheet
range - The cell range to apply formatting to (e.g., "A1:A10")
operator - The text comparison operator ("beginsWith", "endsWith", "contains", "notContains")
value1 - The text value for comparison
value2 - Not used, pass nil
format_style - The color to apply when the condition is met (e.g., "#FF0000" for red)

Examples

{:ok, spreadsheet} = UmyaSpreadsheet.read_file("input.xlsx")

# Highlight cells that begin with "Error" in red
:ok = UmyaSpreadsheet.add_cell_is_rule(
  spreadsheet,
  "Sheet1",
  "A1:A10",
  "beginsWith",
  "Error",
  nil,
  "#FF0000"
)

add_cell_value_rule(spreadsheet, sheet_name, range, operator, value1, value2, format_style)

Adds a cell value rule for conditional formatting.

Parameters

spreadsheet - The spreadsheet struct
sheet_name - The name of the sheet
range - The cell range to apply formatting to (e.g., "A1:A10")
operator - The comparison operator ("equal", "notEqual", "greaterThan", "lessThan", "greaterThanOrEqual", "lessThanOrEqual", "between", "notBetween")
value1 - The first value for comparison
value2 - The second value for comparison (required for "between" and "notBetween" operators)
format_style - The color to apply when the condition is met (e.g., "#FF0000" for red)

Examples

{:ok, spreadsheet} = UmyaSpreadsheet.read_file("input.xlsx")

# Highlight cells equal to 50 in red
:ok = UmyaSpreadsheet.add_cell_value_rule(
  spreadsheet,
  "Sheet1",
  "A1:A10",
  "equal",
  "50",
  nil,
  "#FF0000"
)

# Highlight cells between 30 and 70 in green
:ok = UmyaSpreadsheet.add_cell_value_rule(
  spreadsheet,
  "Sheet1",
  "A1:A10",
  "between",
  "30",
  "70",
  "#00FF00"
)

add_color_scale(spreadsheet, sheet_name, range, min_type, min_value, min_color, max_type, max_value, max_color)

Adds a color scale conditional formatting rule to a range of cells (two-color version).

Parameters

spreadsheet - The spreadsheet struct
sheet_name - The name of the sheet
range - The cell range to apply formatting to (e.g., "A1:A10")
min_type - The minimum value type-value tuple or nil for automatic
max_type - The maximum value type-value tuple or nil for automatic
color - The color to use for the data bars (e.g., "#638EC6")

Examples

{:ok, spreadsheet} = UmyaSpreadsheet.read_file("input.xlsx")

# Add data bars with automatic min/max
:ok = UmyaSpreadsheet.add_data_bar(
  spreadsheet,
  "Sheet1",
  "A1:A10",
  nil,
  nil,
  "#638EC6"
)

# Add data bars with fixed min/max
:ok = UmyaSpreadsheet.add_data_bar(
  spreadsheet,
  "Sheet1",
  "B1:B10",
  {"num", "0"},
  {"num", "100"},
  "#FF0000"
)

add_color_scale(spreadsheet, sheet_name, range, min_type, min_value, min_color, mid_type, mid_value, mid_color, max_type, max_value, max_color)

Adds a color scale conditional formatting rule to a range of cells (three-color version).

Parameters

spreadsheet - The spreadsheet struct
sheet_name - The name of the sheet
range - The cell range to apply formatting to (e.g., "A1:A10")
min_type - The minimum value type string or nil for automatic
min_value - The minimum value or nil for automatic
min_color - The color or color map for minimum values (e.g., "#FF0000" for red)
mid_type - The midpoint value type string or nil for automatic
mid_value - The midpoint value or nil for automatic
mid_color - The color or color map for midpoint values (e.g., "#FFFF00" for yellow)
max_type - The maximum value type string or nil for automatic
max_value - The maximum value or nil for automatic
max_color - The color or color map for maximum values (e.g., "#00FF00" for green)

Examples

{:ok, spreadsheet} = UmyaSpreadsheet.read_file("input.xlsx")

# Add a three-color scale (red-yellow-green)
:ok = UmyaSpreadsheet.add_color_scale(
  spreadsheet,
  "Sheet1",
  "A1:A10",
  "min",
  nil,
  %{argb: "FFFF0000"},
  "percentile",
  "50",
  %{argb: "FFFFFF00"},
  "max",
  nil,
  %{argb: "FF00FF00"}
)

add_data_bar(spreadsheet, sheet_name, range, min_value, max_value, color)

Adds a data bar conditional formatting rule to a range of cells.

Parameters

spreadsheet - The spreadsheet struct
sheet_name - The name of the sheet
range - The cell range to apply formatting to (e.g., "A1:A10")
min_value - Tuple of {type, value} for minimum, or nil for automatic
max_value - Tuple of {type, value} for maximum, or nil for automatic
color - The color to use for the data bars (e.g., "#638EC6")

Examples

{:ok, spreadsheet} = UmyaSpreadsheet.read_file("input.xlsx")

# Add data bars with automatic min/max
:ok = UmyaSpreadsheet.add_data_bar(
  spreadsheet,
  "Sheet1",
  "A1:A10",
  nil,
  nil,
  "#638EC6"
)

# Add data bars with fixed min/max
:ok = UmyaSpreadsheet.add_data_bar(
  spreadsheet,
  "Sheet1",
  "B1:B10",
  {"num", "0"},
  {"num", "100"},
  "#FF0000"
)

add_icon_set(spreadsheet, sheet_name, range, icon_style, thresholds)

Adds an icon set rule for conditional formatting.

Icon sets display different icons based on the values in the cells, providing a visual representation of data trends.

Parameters

spreadsheet - The spreadsheet struct
sheet_name - The name of the sheet
range - The cell range to apply formatting to (e.g., "A1:A10")
icon_style - The style of icons to use (currently supports basic icon sets)
thresholds - A list of threshold tuples {type, value} defining the icon boundaries

Threshold Types

{"min", ""} - Use the minimum value in the range
{"max", ""} - Use the maximum value in the range
{"number", "X"} - Use a specific number
{"percent", "X"} - Use X percent of the range
{"percentile", "X"} - Use the Xth percentile
{"formula", "X"} - Use a formula result

Examples

{:ok, spreadsheet} = UmyaSpreadsheet.read_file("input.xlsx")

# Simple 3-icon set with percentile thresholds
:ok = UmyaSpreadsheet.add_icon_set(
  spreadsheet,
  "Sheet1",
  "A1:A10",
  "3_traffic_lights",
  [
    {"percentile", "33"},
    {"percentile", "67"}
  ]
)

# 5-icon set with custom number thresholds
:ok = UmyaSpreadsheet.add_icon_set(
  spreadsheet,
  "Sheet1",
  "B1:B10",
  "5_arrows",
  [
    {"number", "20"},
    {"number", "40"},
    {"number", "60"},
    {"number", "80"}
  ]
)

add_text_rule(spreadsheet, sheet_name, range, operator, text, format_style)

Adds a text rule conditional formatting rule.

Parameters

spreadsheet - The spreadsheet struct
sheet_name - The name of the sheet
range - The cell range to apply formatting to (e.g., "A1:A10")
operator - The text operator ("contains", "notContains", "beginsWith", "endsWith")
text - The text to search for
format_style - The color to apply when the condition is met (e.g., "#FF0000" for red)

Examples

{:ok, spreadsheet} = UmyaSpreadsheet.read_file("input.xlsx")

# Highlight cells containing "Error" in red
:ok = UmyaSpreadsheet.add_text_rule(
  spreadsheet,
  "Sheet1",
  "A1:A10",
  "contains",
  "Error",
  "#FF0000"
)

# Highlight cells beginning with "Warning" in orange
:ok = UmyaSpreadsheet.add_text_rule(
  spreadsheet,
  "Sheet1",
  "A1:A10",
  "beginsWith",
  "Warning",
  "#FFA500"
)

add_top_bottom_rule(spreadsheet, sheet_name, range, rule_type, rank, percent, format_style)

Adds a top/bottom rule for conditional formatting.

Parameters

spreadsheet - The spreadsheet struct
sheet_name - The name of the sheet
range - The cell range to apply formatting to (e.g., "A1:A10")
rule_type - The rule type ("top", "bottom")
rank - The number of top/bottom items to highlight
percent - Whether rank is a percentage (true) or a count (false)
format_style - The color to apply when the condition is met (e.g., "#FF0000" for red)

Examples

{:ok, spreadsheet} = UmyaSpreadsheet.read_file("input.xlsx")

# Highlight top 3 values in green
:ok = UmyaSpreadsheet.add_top_bottom_rule(
  spreadsheet,
  "Sheet1",
  "A1:A10",
  "top",
  3,
  false,
  "#00FF00"
)

# Highlight bottom 10% of values in red
:ok = UmyaSpreadsheet.add_top_bottom_rule(
  spreadsheet,
  "Sheet1",
  "A1:A10",
  "bottom",
  10,
  true,
  "#FF0000"
)

get_above_below_average_rules(spreadsheet, sheet_name, range \\ nil)

Gets all above/below average rules for a sheet or specific range.

Parameters

spreadsheet - The spreadsheet struct
sheet_name - The name of the sheet
range - Optional. The cell range to get rules for. If nil, returns all rules for the sheet.

Returns

A list of maps, each representing an above/below average rule with the following keys:

:range - The cell range the rule applies to
:rule_type_value - "above", "below", "above_equal", or "below_equal"
:std_dev - Optional standard deviation value
:format_style - The color to apply when the condition is met

Examples

{:ok, spreadsheet} = UmyaSpreadsheet.read_file("input.xlsx")

# Get all above/below average rules in Sheet1
rules = UmyaSpreadsheet.get_above_below_average_rules(
  spreadsheet,
  "Sheet1"
)

get_cell_value_rules(spreadsheet, sheet_name, range \\ nil)

Gets all cell value rules for a sheet or specific range.

Parameters

spreadsheet - The spreadsheet struct
sheet_name - The name of the sheet
range - Optional. The cell range to get rules for. If nil, returns all rules for the sheet.

Returns

A list of maps, each representing a cell value rule with the following keys:

:range - The cell range the rule applies to
:operator - The comparison operator (e.g., ":equal", ":greater_than")
:formula - The formula or value to compare against
:format_style - The color or style to apply when the condition is met

Examples

{:ok, spreadsheet} = UmyaSpreadsheet.read_file("input.xlsx")

# Get all cell value rules in Sheet1
rules = UmyaSpreadsheet.get_cell_value_rules(
  spreadsheet,
  "Sheet1"
)

get_color_scales(spreadsheet, sheet_name, range \\ nil)

Gets all color scale rules for a sheet or specific range.

Parameters

spreadsheet - The spreadsheet struct
sheet_name - The name of the sheet
range - Optional. The cell range to get rules for. If nil, returns all rules for the sheet.

Returns

A list of maps, each representing a color scale rule with the following keys:

:range - The cell range the rule applies to
:min_type - The type for minimum value (e.g., ":min", ":number", ":percent")
:min_value - The minimum value (if applicable)
:min_color - The color for minimum values
:mid_type - Optional. The type for midpoint value
:mid_value - Optional. The midpoint value
:mid_color - Optional. The color for midpoint values
:max_type - The type for maximum value
:max_value - The maximum value (if applicable)
:max_color - The color for maximum values

Examples

{:ok, spreadsheet} = UmyaSpreadsheet.read_file("input.xlsx")

# Get all color scale rules in Sheet1
rules = UmyaSpreadsheet.get_color_scales(
  spreadsheet,
  "Sheet1"
)

get_conditional_formatting_rules(spreadsheet, sheet_name, range \\ nil)

Gets all conditional formatting rules for a sheet or specific range.

Parameters

spreadsheet - The spreadsheet struct
sheet_name - The name of the sheet
range - Optional. The cell range to get rules for. If nil, returns all rules for the sheet.

Returns

A list of maps, each representing a conditional formatting rule with the following keys:

:range - The cell range the rule applies to
:rule_type - The type of rule (":cell_is", ":color_scale", ":data_bar", ":icon_set", etc.)
Other rule-specific fields depending on the rule type

Examples

{:ok, spreadsheet} = UmyaSpreadsheet.read_file("input.xlsx")

# Get all conditional formatting rules in Sheet1
rules = UmyaSpreadsheet.get_conditional_formatting_rules(
  spreadsheet,
  "Sheet1"
)

# Get conditional formatting rules for a specific range
range_rules = UmyaSpreadsheet.get_conditional_formatting_rules(
  spreadsheet,
  "Sheet1",
  "A1:A10"
)

get_data_bars(spreadsheet, sheet_name, range \\ nil)

Gets all data bar rules for a sheet or specific range.

Parameters

spreadsheet - The spreadsheet struct
sheet_name - The name of the sheet
range - Optional. The cell range to get rules for. If nil, returns all rules for the sheet.

Returns

A list of maps, each representing a data bar rule with the following keys:

:range - The cell range the rule applies to
:min_value - Optional tuple of {type, value} for minimum
:max_value - Optional tuple of {type, value} for maximum
:color - The color of the data bars

Examples

{:ok, spreadsheet} = UmyaSpreadsheet.read_file("input.xlsx")

# Get all data bar rules in Sheet1
rules = UmyaSpreadsheet.get_data_bars(
  spreadsheet,
  "Sheet1"
)

get_icon_sets(spreadsheet, sheet_name, range \\ nil)

Gets all icon set rules for a sheet or specific range.

Parameters

spreadsheet - The spreadsheet struct
sheet_name - The name of the sheet
range - Optional. The cell range to get rules for. If nil, returns all rules for the sheet.

Returns

A list of maps, each representing an icon set rule with the following keys:

:range - The cell range the rule applies to
:icon_style - The style of icons to use
:thresholds - A list of threshold tuples {type, value} defining the icon boundaries

Examples

{:ok, spreadsheet} = UmyaSpreadsheet.read_file("input.xlsx")

# Get all icon set rules in Sheet1
rules = UmyaSpreadsheet.get_icon_sets(
  spreadsheet,
  "Sheet1"
)

get_text_rules(spreadsheet, sheet_name, range \\ nil)

Gets all text rules for a sheet or specific range.

Parameters

spreadsheet - The spreadsheet struct
sheet_name - The name of the sheet
range - Optional. The cell range to get rules for. If nil, returns all rules for the sheet.

Returns

A list of maps, each representing a text rule with the following keys:

:range - The cell range the rule applies to
:operator - The text operator ("contains", "notContains", "beginsWith", "endsWith")
:text - The text to search for
:format_style - The color to apply when the condition is met

Examples

{:ok, spreadsheet} = UmyaSpreadsheet.read_file("input.xlsx")

# Get all text rules in Sheet1
rules = UmyaSpreadsheet.get_text_rules(
  spreadsheet,
  "Sheet1"
)

get_top_bottom_rules(spreadsheet, sheet_name, range \\ nil)

Gets all top/bottom rules for a sheet or specific range.

Parameters

spreadsheet - The spreadsheet struct
sheet_name - The name of the sheet
range - Optional. The cell range to get rules for. If nil, returns all rules for the sheet.

Returns

A list of maps, each representing a top/bottom rule with the following keys:

:range - The cell range the rule applies to
:rule_type_value - "top" or "bottom"
:rank - The number of top/bottom items to highlight
:percent - Whether rank is a percentage (true) or a count (false)
:format_style - The color to apply when the condition is met

Examples

{:ok, spreadsheet} = UmyaSpreadsheet.read_file("input.xlsx")

# Get all top/bottom rules in Sheet1
rules = UmyaSpreadsheet.get_top_bottom_rules(
  spreadsheet,
  "Sheet1"
)