ConditionalFormatting of cell containing string in CAxlsx

Question

Using the CAxlsx gem (https://github.com/caxlsx/caxlsx), I'm trying to add conditional formatting to a range of cells, where the style should be applied if the cell contains the character -. Here's the snippet I'm using at the moment.

worksheet.add_conditional_formatting(range,
      type: :containsText,
      formula: "-",
      dxfId: @styles[:invalid],
      priority: 1)

Unfortunately, this doesn't seem to work. It does seem to apply the styling when the cell doesn't contain text, but a negative number, but that's not my use case. The documentation is severely lacking as well, and it doesn't offer a lot of explanation on what should be done in this case. (E.g., there's a cellIs type, with which the containsText operator can be used, but there's also a containsText type and no explanation as to what the difference between them is - and neither seem to work in my case.) Any pointers would be greatly appreciated, so far it's just been trial and error.

Answer 1

Assuming your range is something like "A1:A4" then formula you are looking for is NOT(ISERROR(SEARCH("-",A1))). Docs

Example:

require 'axlsx'
package = Axlsx::Package.new
workbook = package.workbook
s = workbook.styles.add_style({b:true, type: :dxf})
rows = ['a','b-c','d','e-f']
workbook.add_worksheet do |sheet|
  rows.each do |row| 
    sheet.add_row [row]
  end
  sheet.add_conditional_formatting("A1:A4", { :type => :containsText,
    :operator => :containsText,
    :formula => 'NOT(ISERROR(SEARCH("-",A1)))',
    :dxfId => s,
    :priority => 1 })
end

package.serialize('conditional_test.xlsx')

I have found that the easiest way to determine the appropriate formula is to:

• manually create a new excel work book
• fill in an appropriate number of cells
• conditionally format them in excel
• save this workbook and close
• change the extension to .zip (because xlsx is just zipped XML files)
• open the zip file and navigate to /xl/worksheets/sheet1.xml
• open this file and it will show you the formula used for the conditional formatting e.g.

<x14:conditionalFormattings>
  <x14:conditionalFormatting xmlns:xm="http://schemas.microsoft.com/office/excel/2006/main">
    <x14:cfRule type="containsText" priority="1" operator="containsText" id="{E75522C8-BC6E-4142-B282-D21DF586C852}">
      <xm:f>NOT(ISERROR(SEARCH("-",A1)))</xm:f>
      <xm:f>"-"</xm:f>
      <x14:dxf>
        <font>
           <color rgb="FF9C0006"/>
        </font>
        <fill>
          <patternFill>
            <bgColor rgb="FFFFC7CE"/>
          </patternFill>
        </fill>
      </x14:dxf>
    </x14:cfRule>
    <xm:sqref>A1:A4</xm:sqref>
  </x14:conditionalFormatting>
</x14:conditionalFormattings>