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?

Debose

take a look at conditional formatting I use it all the time for this type of thing. – Nelsen

Thank you, this allows me to apply a differential style based on the cell values. What I am asking is: How can I apply unique background colors to cells without creating a style for each color? – Debose

You can't. If you were doing this in excel would you not have to specify each "style" in the GUI? Yes it might seem easier using the interface provided but the background concept is the same Excel saves this information as a "style" so that it knows where to apply it and how to reuse it if needed. You could however create your styles in a loop if needed especially if you are just changing the color this should be fairly easy. – Nelsen

Yes, that's what I did that in my app: I make a list of all colors, create all styles, then apply them where needed. If you see the first code example above, it shows that it is possible to assign color to the text without using a style. So I hoped the same would be possible for the cell background color. Thank you for trying to help; I think the style list solution is good enough for my purposes. – Debose

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).

Hemlock

Answer 2

You can add style to your rows and cells in the alter mode where you'll be having your sheet ready with required rows. The way might be too static where you'll have to hard code all the rows and cell details.

package = Axlsx::Package.new
package.workbook.add_worksheet(name: 'Worksheet') do |sheet|
    # after adding required rows
    style1 = sheet.styles.add_style(:bg_color => "EF0920", :fg_color => "FFFFFF")
    sheet.rows[6].cells[1].style = style1
end

Sade