#!/usr/bin/env ruby
# -*- coding: utf-8 -*-

require 'write_xlsx'

workbook  = WriteXLSX.new('conditional_format.xlsx')
worksheet1 = workbook.add_worksheet
worksheet2 = workbook.add_worksheet
worksheet3 = workbook.add_worksheet
worksheet4 = workbook.add_worksheet
worksheet5 = workbook.add_worksheet
worksheet6 = workbook.add_worksheet
worksheet7 = workbook.add_worksheet
worksheet8 = workbook.add_worksheet
worksheet9 = workbook.add_worksheet

# Light red fill with dark red text.
format1 = workbook.add_format(
    :bg_color => '#FFC7CE',
    :color    => '#9C0006'
)

# Green fill with dark green text.
format2 = workbook.add_format(
    :bg_color => '#C6EFCE',
    :color    => '#006100'
)

# Blue fill with dark blue text.
format3 = workbook.add_format(
    :bg_color => '#C6CEFF',
    :color    => '#0000FF'
)

# Some sample data to run the conditional formatting against.
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 ]
]

###############################################################################
#
# Example 1.
#
caption = 'Cells with values >= 50 are in light red. ' +
  'Values < 50 are in light green.'

# Write the data.
worksheet1.write('A1', caption)
worksheet1.write_col('B3', data)

# Write a conditional format over a range.
worksheet1.conditional_formatting('B3:K12',
    {
        :type     => 'cell',
        :criteria => '>=',
        :value    => 50,
        :format   => format1
    }
)

# Write another conditional format over the same range.
worksheet1.conditional_formatting('B3:K12',
    {
        :type     => 'cell',
        :criteria => '<',
        :value    => 50,
        :format   => format2
    }
)


###############################################################################
#
# Example 2.
#
caption = 'Values between 30 and 70 are in light red. ' +
  'Values outside that range are in light green.'

worksheet2.write('A1', caption)
worksheet2.write_col('B3', data)

worksheet2.conditional_formatting('B3:K12',
    {
        :type     => 'cell',
        :criteria => 'between',
        :minimum  => 30,
        :maximum  => 70,
        :format   => format1
    }
)

worksheet2.conditional_formatting('B3:K12',
    {
        :type     => 'cell',
        :criteria => 'not between',
        :minimum  => 30,
        :maximum  => 70,
        :format   => format2
    }
)


###############################################################################
#
# Example 3.
#
caption = 'Duplicate values are in light red. ' +
  'Unique values are in light green.'

worksheet3.write('A1', caption)
worksheet3.write_col('B3', data)

worksheet3.conditional_formatting('B3:K12',
    {
        :type     => 'duplicate',
        :format   => format1,
    }
)

worksheet3.conditional_formatting('B3:K12',
    {
        :type     => 'unique',
        :format   => format2,
    }
)


###############################################################################
#
# Example 4.
#
caption = 'Above average values are in light red. ' +
  'Below average values are in light green.'

worksheet4.write('A1', caption)
worksheet4.write_col('B3', data)

worksheet4.conditional_formatting('B3:K12',
    {
        :type     => 'average',
        :criteria => 'above',
        :format   => format1
    }
)

worksheet4.conditional_formatting('B3:K12',
    {
        :type     => 'average',
        :criteria => 'below',
        :format   => format2
    }
)


###############################################################################
#
# Example 5.
#
caption = 'Top 10 values are in light red. ' +
  'Bottom 10 values are in light green.'

worksheet5.write('A1', caption)
worksheet5.write_col('B3', data)

worksheet5.conditional_formatting('B3:K12',
    {
        :type     => 'top',
        :value    => '10',
        :format   => format1
    }
)

worksheet5.conditional_formatting('B3:K12',
    {
        :type     => 'bottom',
        :value    => '10',
        :format   => format2
    }
)


###############################################################################
#
# Example 6.
#
caption = 'Cells with values >= 50 are in light red. ' +
  'Values < 50 are in light green. Non-contiguous ranges.'

# Write the data.
worksheet6.write('A1', caption)
worksheet6.write_col('B3', data)

# Write a conditional format over a range.
worksheet6.conditional_formatting('B3:K6,B9:K12',
    {
        :type     => 'cell',
        :criteria => '>=',
        :value    => 50,
        :format   => format1
    }
)

# Write another conditional format over the same range.
worksheet6.conditional_formatting('B3:K6,B9:K12',
    {
        :type     => 'cell',
        :criteria => '<',
        :value    => 50,
        :format   => format2
    }
)


###############################################################################
#
# Example 7.
#
caption = 'Examples of color scales and data bars. Default colors.'

# Use different sample data for examples 7 and 8
data7 = 1 .. 12

worksheet7.write('A1', caption)

worksheet7.write('B2', "2 Color Scale")
worksheet7.write_col('B3', data7)

worksheet7.write('D2', "3 Color Scale")
worksheet7.write_col('D3', data7)

worksheet7.write('F2', "Data Bars")
worksheet7.write_col('F3', data7)


worksheet7.conditional_formatting('B3:B14',
    {
        :type => '2_color_scale'
    }
)

worksheet7.conditional_formatting('D3:D14',
    {
        :type => '3_color_scale'
    }
)

worksheet7.conditional_formatting('F3:F14',
    {
        :type => 'data_bar'
    }
)


###############################################################################
#
# Example 8.
#
caption = 'Examples of color scales and data bars. Modified colors.'

worksheet8.write('A1', caption)

worksheet8.write('B2', "2 Color Scale")
worksheet8.write_col('B3', data7)

worksheet8.write('D2', "3 Color Scale")
worksheet8.write_col('D3', data7)

worksheet8.write('F2', "Data Bars")
worksheet8.write_col('F3', data7)


worksheet8.conditional_formatting('B3:B14',
    {
        :type      => '2_color_scale',
        :min_color => "#FF0000",
        :max_color => "#00FF00"
    }
)

worksheet8.conditional_formatting('D3:D14',
    {
        :type      => '3_color_scale',
        :min_color => "#C5D9F1",
        :mid_color => "#8DB4E3",
        :max_color => "#538ED5"
    }
)

worksheet8.conditional_formatting('F3:F14',
    {
        :type      => 'data_bar',
        :bar_color => '#63C384'
    }
)

###############################################################################
#
# Example 9
#
caption = 'Cells with values >= 100 are always in blue. ' +
  'Otherwise, cells with values >= 50 are in light red ' +
  'and values < 50 are in light green.'

# Write the data.
worksheet9.write('A1', caption)
worksheet9.write_col('B3', data)

# Write a conditional format over a range.
# Use stopIfTrue to prevent previous formats from being used
# if the conditions of this format are met.
worksheet9.conditional_formatting('B3:K12',
    {
        :type         => 'cell',
        :criteria     => '>=',
        :value        => 100,
        :format       => format3,
        :stop_if_true => 1
    }
)

# Write another conditional format over the same range.
worksheet9.conditional_formatting('B3:K12',
    {
        :type     => 'cell',
        :criteria => '>=',
        :value    => 50,
        :format   => format1
    }
)

# Write another conditional format over the same range.
worksheet9.conditional_formatting('B3:K12',
    {
        :type     => 'cell',
        :criteria => '<',
        :value    => 50,
        :format   => format2
    }
)

workbook.close