Axlsx gem: is it possible to apply a background color to individual cells?

Question

It is possible to apply a font color to individual cells, without creating a style:

Axlsx::Package.new do |p|
  p.workbook.add_worksheet(:name => "test") do |ws|        
    ws.add_row ["a", "b", "c"]
    ws.add_row ["d", "e", "f"]
    ws.add_row ["g", "h", "i"]

    ws.rows.each do |r|
        r.cells.each do |c|
            c.color = "009900"
            if ['a', 'e', 'i'].include?(c.value)
              c.color = "009900" // how can I do the same for background color?
            end
        end
    end
  end
  p.serialize('test.xlsx')
end

It is possible to apply a style to individual cells:

Axlsx::Package.new do |p|
 p.workbook.add_worksheet(:name => "test") do |ws|
    style1 = ws.styles.add_style( :bg_color => "FFFFFF00")
    ws.add_row ["a", "b", "c"]
    ws.add_row ["d", "e", "f"]
    ws.add_row ["g", "h", "i"]

    ws.rows.each do |r|
        r.cells.each do |c|       
            if ['a', 'e', 'i'].include?(c.value)
              c.style = style1
            end
        end
    end
  end
  p.serialize('test.xlsx')
end

but in my case this is impractical, as I would like to have the cell background be a function of the cell value and would like to avoid building hundreds or thousands of styles.

Any suggestions?

Answer 1

I would answer both your question in one code block itself, to add style to specific cells and to add background color to cells, you could do like this:

Axlsx::Package.new do |p|
    p.workbook.add_worksheet(:name => "test") do |ws|
        style1 = ws.styles.add_style(:bg_color => "EF0920", :fg_color => "FFFFFF")
        ws.add_row ["a", "b", "c"], :style => style1
        ws.add_row ["d", "e", "f"], :bg_color 
    end
    p.serialize('test.xlsx')
end

note: bg_color is background color fg_color is foreground color (text color).