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

Question

I am using the AXLSX Ruby gem to generate Excel reports and I'm having a problem applying several styles to cells in a modular way.

Here's an example that has just two styles ("on background" and "bold on background"),

require 'axlsx'

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

with_background = workbook.styles.add_style bg_color: "E2D3EB"
bold_with_background = workbook.styles.add_style bg_color: "E2D3EB", b: true

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

axlsx.serialize "grocery.xlsx"

and here's the result,

Now, suppose I have to apply a border around this table. If I understand it correctly, I'll have to have a lot of styles to get there: "bold on backbroung with top left edge", "bold on background with top edge", "bold on backgound with top right edge", "on background with right edge", etc.

Is there a way to apply several styles to cells instead of having to declare a style for each possible combination of base styles?

I'd like to have something like

sheet["B2"].add_style(bold).add_style(background).add_style(top_left_edge)

but not sure if the gem implements a similar solution.

Any ideas? Thanks!

Answer 1

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

Answer 2

FYI, there is a way to do this using "differential styles". Look at the docs on styles.rb

Basically you state your style type is :dxf. The default is :xf. Everything else is the same. From the link above (title, currency, and percent are normal styles defined earlier):

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

# define your styles
profitable = wb.styles.add_style(:bg_color => "FFFF0000",
                           :fg_color=>"#FF000000",
                           :type => :dxf)

ws.add_row ["Genreated At:", Time.now], :styles=>[nil, date_time]
ws.add_row ["Previous Year Quarterly Profits (JPY)"], :style=>title
ws.add_row ["Quarter", "Profit", "% of Total"], :style=>title
ws.add_row ["Q1", 4000, 40], :style=>[title, currency, percent]
ws.add_row ["Q2", 3000, 30], :style=>[title, currency, percent]
ws.add_row ["Q3", 1000, 10], :style=>[title, currency, percent]
ws.add_row ["Q4", 2000, 20], :style=>[title, currency, percent]

ws.add_conditional_formatting("A1:A7", { :type => :cellIs, :operator => :greaterThan, :formula => "2000", :dxfId => profitable, :priority => 1 })
f = File.open('example_differential_styling', 'w')
p.serialize(f)