Added example of conditional formatting to match text in cells

2 files changed, 29 insertions, 0 deletions

diff --git a/examples/conditional_formatting/example_conditional_formatting.rb b/examples/conditional_formatting/example_conditional_formatting.rb
index 320ae9f1..37df52f0 100644
--- a/examples/conditional_formatting/example_conditional_formatting.rb
+++ b/examples/conditional_formatting/example_conditional_formatting.rb
@@ -9,6 +9,7 @@ book = p.workbook
 # define your regular styles
 percent = book.styles.add_style(:format_code => "0.00%", :border => Axlsx::STYLE_THIN_BORDER)
 money = book.styles.add_style(:format_code => '0,000', :border => Axlsx::STYLE_THIN_BORDER)
+status = book.styles.add_style(:border => Axlsx::STYLE_THIN_BORDER)
 
 # define the style for conditional formatting
 profitable = book.styles.add_style( :fg_color => "428751", :type => :dxf )
@@ -71,4 +72,18 @@ book.add_worksheet(:name => "Icon Set") do |ws|
   ws.add_conditional_formatting("B3:B100", { :type => :iconSet, :dxfId => profitable, :priority => 1, :icon_set => icon_set })
 end
 
+book.add_worksheet(:name => "Contains Text") do |ws|
+  ws.add_row ["Previous Year Quarterly Profits (JPY)"]
+  ws.add_row ["Quarter", "Profit", "% of Total", "Status"]
+  offset = 3
+  rows = 20
+  offset.upto(rows + offset) do |i|
+    ws.add_row ["Q#{i}", 10000*((rows/2-i) * (rows/2-i)), "=100*B#{i}/SUM(B3:B#{rows+offset})", (10000*((rows/2-i) * (rows/2-i))) > 100000 ? "PROFIT" : "LOSS"], :style=>[nil, money, percent, status]
+  end
+
+# Apply conditional formatting to range D3:D100 in the worksheet
+  ws.add_conditional_formatting("D3:D100", { :type => :containsText, :operator => :equal, :text => "PROFIT", :dxfId => profitable, :priority => 1 })
+  ws.add_conditional_formatting("D3:D100", { :type => :containsText, :operator => :equal, :text => "LOSS", :dxfId => unprofitable, :priority => 1 })
+end
+
 p.serialize('example_conditional_formatting.xlsx')
diff --git a/examples/example.rb b/examples/example.rb
index 15d34ed6..558d3544 100755
--- a/examples/example.rb
+++ b/examples/example.rb
@@ -700,6 +700,7 @@ end
 if examples.include? :conditional_formatting
   percent = wb.styles.add_style(:format_code => "0.00%", :border => Axlsx::STYLE_THIN_BORDER)
   money = wb.styles.add_style(:format_code => '0,000', :border => Axlsx::STYLE_THIN_BORDER)
+  status = wb.styles.add_style(:border => Axlsx::STYLE_THIN_BORDER)
 
   # define the style for conditional formatting
   profitable = wb.styles.add_style( :fg_color => "FF428751", :type => :dxf )
@@ -763,6 +764,19 @@ if examples.include? :conditional_formatting
     icon_set = Axlsx::IconSet.new
     sheet.add_conditional_formatting("B3:B100", { :type => :iconSet, :dxfId => profitable, :priority => 1, :icon_set => icon_set })
   end
+
+  wb.add_worksheet(:name => "Contains Text") do |sheet|
+    sheet.add_row ["Previous Year Quarterly Profits (JPY)"]
+    sheet.add_row ["Quarter", "Profit", "% of Total", "Status"]
+    offset = 3
+    rows = 20
+    offset.upto(rows + offset) do |i|
+      sheet.add_row ["Q#{i}", 10000*((rows/2-i) * (rows/2-i)), "=100*B#{i}/SUM(B3:B#{rows+offset})", (10000*((rows/2-i) * (rows/2-i))) > 100000 ? "PROFIT" : "LOSS"], :style=>[nil, money, percent, status]
+    end
+  # Apply conditional formatting to range D3:D100 in the worksheet to match words.
+    sheet.add_conditional_formatting("D3:D100", { :type => :containsText, :operator => :equal, :text => "PROFIT", :dxfId => profitable, :priority => 1 })
+    sheet.add_conditional_formatting("D3:D100", { :type => :containsText, :operator => :equal, :text => "LOSS", :dxfId => unprofitable, :priority => 1 })
+  end
 end
 
 # Page Breaks