CODEWITHSUNDEEP
rubyaxlsx
aaandre
aaandre

Reputation: 2512

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

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?

Upvotes: 6

Views: 6166

Answers (2)

Prasad Kudalkar
Prasad Kudalkar

Reputation: 221

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

Upvotes: 3

rishi
rishi

Reputation: 51

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

Upvotes: 5

Related Questions