How to Give a Date a Background Color with Axlsx

How to give a date a background color with axlsx?

You will need to specify the number format as well so your style would look like

style1 = ws.styles.add_style(:bg_color => "EF0920", :fg_color => "FFFFFF", :format_code => "dd.mm.yyyy")

When adding a style it will overwrite the default style and all formatting. e.g.

ws.add_row [ Date.today, "Style with colors --The date is no date any longer"], :style => [style1,nil]

This will produce a red date formatted like the other rows and a no fill column B.

This style will be for the date only so if you want the whole column I would recommend something like

red_style_h  = {:bg_color => "EF0920", :fg_color => "FFFFFF"} 
red_date_h = red_style_h.merge(:format_code => "dd.mm.yyyy")

red_style = ws.styles.add_style(red_style_h)
red_date_style = ws.styles.add_style(red_date_h)

Then style your rows as

ws.add_row [ Date.today, "Style with colors --The date is no date any longer"], :style => [red_date_style,red_style]

This will produce a red row (columns A and B) with the date formatted the same as the other rows.

Git Hub Source

There are also predefined styles available through numFmts and the global named constants NUM_FMT_PERCENTAGE,NUM_FMT_YYYYMMDD,NUM_FMT_YYYYMMDDHHMMSS, etc.

When defining many different formats I find it easiest to use a YML file and then parse that to define styles e.g.

red_style:&red
  bg_color: "EF0920"
  fg_color: "FFFFFF"
red_date: 
  <<: *red
  format_code: dd.mm.yyyy

Then something like

class MyStylizedSheet < ::Axlsx::Workbook
  STYLES_FILE = YAML.load(File.read(YOUR_YML_FILE)).deep_symbolize_keys
  PREDEFINED_STYLES = {}
  def initialize(options={})
    super
    initialize_with_styles
  end
  private
  def initialize_with_styles
    STYLES_FILE.each do |k,v|
      PREDEFINED_STYLES[k] = @styles.add_style(v)
    end
  end
end

Then you can initiate your Workbook from your custom class and reference styles through the PREDEFINED_STYLES constant.

Rails Axlsx render conditional row background color

I haven't tested it but this should do the job.

wb = xlsx_package.workbook

wb.styles do |s|
   title = s.add_style :b => true, :sz => 10,
           :border => { :style => :thin, :color => "00" },
           :alignment => {
             :horizontal => :center,
             :vertical => :center
           }
   row = s.add_style :b => false,
         :sz => 10,
         :border => { :style => :thin, :color => "00" },
         :alignment => {
            :horizontal => :left,
            :vertical => :center
         }

   red_cell_row = s.add_style :b => false,
                  :sz => 10,
                  :border => { :style => :thin, :color => "00" },
                  :alignment => {
                    :horizontal => :left,
                    :vertical => :center
                  },
                  :bg_color => "FF0000", 
                  :fg_color => "000000"

   wb.add_worksheet(name: "Customer") do |sheet|
   sheet.add_row ['Customer Name', 'Status'] :style => title
   @customers.each do |customer|
      if customer.status == "Late Payment"
        sheet.add_row [customer.name, customer.status] :style => red_cell_row
      else
        sheet.add_row [customer.name, customer.status] :style => row
      end
   end
end

Applying several styles to a cell in Excel spreadsheet with AXLSX gem

I've managed to overlay cell styles by monkey patching Axlsx classes. The idea is to first apply raw styles to cell in the form of Ruby hashes. When that is finished one can generate Axlsx styles for the workbook and apply them. I can now separate the markup from style, having the styles applied as

sheet["B2:D2"].add_style(b: true)
sheet["B2:D5"].add_style(bg_color: "E2D3EB")
workbook.apply_styles

Below is the full listing of my hacky solution. This doesn't include identifying unique styles among other things that should be done in professional code. Looking forward to any feedback.

require 'axlsx'

class Array
  def add_style(style)
    return unless map{ |e| e.kind_of? Axlsx::Cell }.uniq.first
    each { |cell| cell.add_style(style) }
  end
end

class Axlsx::Workbook
  attr_accessor :styled_cells

  def add_styled_cell(cell)
    self.styled_cells ||= []
    self.styled_cells << cell
  end

  def apply_styles
    return unless styled_cells
    styled_cells.each do |cell|
      cell.style = styles.add_style(cell.raw_style)
    end
  end
end

class Axlsx::Cell
  attr_accessor :raw_style

  def workbook
    row.worksheet.workbook
  end

  def add_style(style)
    self.raw_style ||= {}
    self.raw_style = raw_style.merge(style)
    workbook.add_styled_cell(self)
  end
end

axlsx = Axlsx::Package.new
workbook = axlsx.workbook

workbook.add_worksheet do |sheet|
  sheet.add_row
  sheet.add_row ["", "Product", "Category", "Price"]
  sheet.add_row ["", "Butter", "Dairy", 4.99]
  sheet.add_row ["", "Bread", "Baked Goods", 3.45]
  sheet.add_row ["", "Broccoli", "Produce", 2.99]

  sheet["B2:D2"].add_style(b: true)
  sheet["B2:D5"].add_style(bg_color: "E2D3EB")
end

workbook.apply_styles

axlsx.serialize "grocery.xlsx"

Edit: I've leaned up my solution and extracted it into a gem https://github.com/sakovias/axlsx_styler

How to create Header and footer using Axlsx Gem in Rails?

I suggest you to add a row & apply style properties to this row.
In below code, I have changed the background color to gray & text is made bold for header. Hope this helps!!

p = Axlsx::Package.new
wb = p.workbook

head_style = wb.styles.add_style bg_color: "DDDDDD",  b: true

wb.add_worksheet(name: "Sheet 1") do |sheet|
  sheet.add_row %w(Name Age)
  sheet.row_style 0, head_style

  sheet.add_row ['Tom',20]
  sheet.add_row ['Pete',22]

end

---

Related Topics