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

Bobbee

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 ^_^

Tver

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.

Selfcommand

Answer 3

To apply a style to just one cell, when you call add_row you can specify an array of styles at the end. Place your style in the first one and fill the rest with nil. I think it may also work if you just have one style in the array,as you want it in the first column, but I'm not sure. Fell free to try it.

Example:

my_style = sheet.add_style :b=>true

sheet.add_row ["One","Two","Three"],:styles=>[my_style].fill(1..2,nil)

Thorner

When passing the :styles option to add_row the behaviour changes based on the data type of the :styles value. If it is an array, styles are applied to each cell in index order of the array. cells beyond the length of the array will not be styled. You can use nil anywhere in that array to not specify a style for any element in the cell data array. If :styles is an integer it will be applied to all cells in the row. Either way the poster only has access to row_style and col_style because they are using acts_as_xlsx. – Neptunium

Answer 4

It looks like you are using acts_as_xlsx instead of axlsx directly. While you could transpose your 'list_of_my_stuff' and iterate over the second column applying a 'percent_highlight_style' I think you are really trying to do something that is beyond such a simple gem.

alternatives

axlsx is the parent gem that acts_as_xlsx wraps. You would want to create some view objects or report generating classes within your application that can take the data you want to serialize and apply the headers and styles as you want.

rails_axlsx is another gem based on axlsx that allows you to create views and have much more control over how the spreadsheet is generated and may be an easier jump than going directly to axlsx.

Neptunium