How to I Add a Hyperlink to a Cell in Axlsx

How to I add a hyperlink to a cell in axlsx?

You can add both links within workbook and URLs.

p = Axlsx::Package.new
book = p.workbook
book.add_worksheet(:name => 'hyperlinks') do |sheet|
  # external references
  sheet.add_row ['axlsx']
  sheet.add_hyperlink :location => 'https://github.com/randym/axlsx', :ref => sheet.rows.first.cells.first
  # internal references
  sheet.add_hyperlink :location => "'Next Sheet'!A1", :ref => 'A2', :target => :sheet
  sheet.add_row ['next sheet']
end

Add image from URL to Excel with Axlsx

Try using read to pull the contents into a tempfile and use that location:

t = Tempfile.new('my_image')
t.binmode
t.write image.read
t.close
ws.add_image(:image_src => t.path, ...

Rails Axlsx New Line in Cell

For a forced line feed use "\x0A" (breaks between paragraphs.)

If you want both carriage return and line feed, use "\x0D\x0A".

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