axlsx : acts_as_xlsx : how do i add style to only one cell?

Question

I see methods for col_style and row_style and add_conditional_formatting, but can't figure out how to set just one cell. In my example, col 1 is a date and column 2 is a percent. when i highlight the background of a row, i lose the date/percent formatting because Randy explained a cell can only have 1 style. How do I assign a date_with_background style to just the 1st cell in that row when necessary?

xlsx_package = Stuff.all.to_xlsx
xlsx_package.workbook.worksheets.first.tap do |sheet|

  sheet.col_style 0, date_format, {row_offset: 1}
  sheet.col_style 1, percent_format, {row_offset: 1}

  list_of_my_stuff.each_with_index do |item,index|
    if needs_background?(item)
      sheet.row_style index+1, with_background
    else
      sheet.row_style index+1, no_background
    end
  end
end

Answer 1

I wanted to style one cell too, not by marching along as I created the table, but actually editing it. This is very useful if you merge a bunch of cells. It's not really emphasized in the axlsx docs, but you can directly access cells and their styles.

Something like this:

wb = xlsx_package.workbook

wb.styles do |s|

  title_style = s.add_style :sz => 20, :alignment => { :horizontal=> :center }, :border => { :style => :thick, :color => "000000", :edges => [:left, :right, :top, :bottom] }

  wb.add_worksheet(name: "Standard Chart") do |sheet|

    #make a 30x30 spread sheet testbench of one space cells
    30.times { sheet.add_row Array.new(30, " ") }

    #merge some particular cells into a 4x4 block
    sheet.rows[7].cells[5].merge sheet.rows[10].cells[8]

    #set values for our merged cell and one on either side
    sheet.rows[7].cells[4].value = 'foo'
    sheet.rows[7].cells[5].value = "I am cell #{sheet.rows[7].cells[5].r}"
    sheet.rows[7].cells[9].value = 'bar'

    #style ONLY our merged cell
    sheet.rows[7].cells[5].style = title_style
  end

end #end styles

xlsx_package.use_shared_strings = true

Hope that helps someone ^_^

Answer 2

Rather then wrapping entire sheet with in styles you can create style like following

wb = xlsx_package.workbook
header_style = wb.styles.add_style(
  { alignment:
      { horizontal: :center,
        vertica: :center,
        wrap_text: true
      },
    b: true,
    bg_color: '00',
    fg_colog: 'ff'
  })

and use that style like following

heet.add_row ['Name:', 'Address', 'city'], style: header_style
sheet.rows[0].cells[0].style = header_style

Hope this help someone.