Why Does Hyperlink Developed In RubyXL Work In LibreOffice And Not Excel

Question

I am working in Rails with RubyXL. I'm trying to create links to the different tabs in the file. When I try the links in LibreOffice (Version: 4.3.7.2), the links works great, but when I try them in Excel (Office 16) in Windows, an error message pops up with "Reference isn't valid." This is rather annoying.

I have a table of the tabs on the first sheet, worksheet_0. As I create other tabs, I create a link to each tab and then put the link on worksheet_0. The link text is just the tab number.

Here the code for creating the link:

  TOC_worksheet = workbook.add_worksheet("TOC") 
  products.each_with_index do |prod, index|
    ...
    worksheet = workbook.add_worksheet(name)

    row_number = index + 1
    link = %Q{HYPERLINK("##{worksheet.name}","#{row_number}")}
    TOC_worksheet.add_cell(row_number, 0, '', link)
    ...

This is what the link looks like in both LibreOffice and Excel

=HYPERLINK("#1 Centrify Server","1")

I'm fairly new at Ruby and Rails and would appreciate any insight.

Answer 1

The "reference not valid" points to the formatting of your link destination. Specifically, Excel doesn't like sheet names to have spaces.

=HYPERLINK("#'1 Centrify Server'","1")

or

=HYPERLINK("#'1 Centrify Server'!A1","1")

which would mean this:

link = %Q{HYPERLINK("#'#{worksheet.name}"',"#{row_number}")}

Should be more acceptable to Excel.