Axlsx - Formatting Text Within a Cell

Formatting a cell as Text using the axlsx spreadsheet ruby gem?

You can override the type of data using the types option on add row.

Something like:

worksheet.add_row ['0012342'], :types => [:string]

Grab me on irc (JST) if you need any help getting that to work.

Best

randym

edit --

I've added an example for this to examples/example.rb in the repo.

wb.add_worksheet(:name => "Override Data Type") do |sheet|
  sheet.add_row ['dont eat my zeros!', '0088'] , :types => [nil, :string]
end

https://github.com/randym/axlsx/blob/master/examples/example.rb#L349

Axlsx Formatting

I figured this out, you need to do set your style using something like

percent = s.add_style(:num_fmt => 9)
even_row_percent = s.add_style(:bg_color => 'blue', :fg_color => 'white', :b => false, :format_code => 0%)
even_row = s.add_style(:bg_color => 'blue', :fg_color =>'white', :b => false)

then in your loop just use an each with index and then use a if statement like

if index.odd
 sheet.add_row[
 item[:value], 
 item[:value_percent]], :style => [nil, percent]
else
 sheet.add_row[
 item[:value], 
 item[:value]], :style => [even_row, even_row_percent]
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

---

Related Topics