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!

Linderman

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!

Valona

Awesome answer! You might also consider setting the style in the add_row call via the :style option. rubydoc.info/github/randym/axlsx/Axlsx/Worksheet:add_row – Matteo

I recently learned about assigning styles as you described and have used it effectively as well. I may revise the above answer when I have the chance to offer that alternative. – Valona

One can also declare styles just by calling blue_link = wb.styles.add_style .... This is handy if you have a large document and don't want to use a wb.styles do .. end block. – Coppersmith

For those reading later: row.cells[123].color = "0000FF" seems to work for me too! – Lebanon

Answer 2

This seems to work:

require 'axlsx'
p = Axlsx::Package.new
ws = p.workbook.add_worksheet
ws.add_row ['hoge-hoge']
ws['A1'].color = '0000FF'
ws.add_hyperlink :location => 'F6', :target => :sheet, :ref => 'A1'
p.serialize 'where_is_my_color.xlsx'

Can you post a larger example of your code that does not set the color?

Matteo

Answer 3

Apparently Axlsx is only applying custom styles to String data types. Fixed this by setting each column to type :string like this:

Sheet.add_row [ "1", "2", "3" ], :types => [:string, :string, :string]

Thanks Randy!

Linderman