Setting a hyperlink text color in axlsx

Question

I'm trying to set the foreground color of text in a hyperlink cell but it doesn't seem to work.

Using something like: sheet["A1"].color = "0000FF" works fine for a normal cell, but not for a hyperlinked cell

This code simply creates a link to cell D1 on the "Log" sheet (which works fine) but A1 never turns blue!

sheet.add_hyperlink :location => "'Log'!D1", :target => :sheet, :ref => "A1" sheet["A1"].color = "0000FF"

Thanks!

Answer 1

There are two important things to do before applying a color to a link:

1. You have to define the color within a style, and
2. You have to know the exact address of the cell in question.

Styles are normally applied to rows, but in this case you want to apply it to a specific cell. This is possible, but you need to address the cell directly through the Sheet Object. Also, and somewhat counter intuitively, the 'add_hyperlink' method is available to the Sheet object, not the Cell. So beware of that as well.

Here is an example of how to apply a style to a cell containing a link:

p = Axlsx::Package.new
p.workbook do |wb|
  wb.styles do |s|
    blue_link = s.add_style :fg_color => '0000FF'
    wb.add_worksheet(:name => "Anchor Link Test") do |sheet|
      sheet.add_row ['Title', 'Link']

      # Define the row here, we will use that later
      row = sheet.add_row ['Google', 'Click to go']

      # Add the hyperlink by addressing the column you have used and add 1 to the row's index value.
      sheet.add_hyperlink :location => "http://www.google.com", :ref => "B#{row.index + 1}"
      sheet["B#{row.index + 1}"].style = blue_link
    end
    s = p.to_stream()
    File.open("anchor_link_test.xlsx", 'w') { |f| f.write(s.read) }
  end
end  

Final note: You might note that I have written this spreadsheet using the methods

s = p.to_stream()
File.open("anchor_link_test.xlsx", 'w') { |f| f.write(s.read) }

There is evidence presented on the Axlsx Github Issues Page which shows that this means of writing out the file is significantly faster than

p.serialize

Just thought that deserved mention somewhere on StackOverflow!