add hyperlinks for worksheets #118

10 files changed, 239 insertions, 7 deletions

diff --git a/examples/example.rb b/examples/example.rb
index 413fce55..23616452 100755
--- a/examples/example.rb
+++ b/examples/example.rb
@@ -1,6 +1,6 @@
 #!/usr/bin/env ruby -w -s
 # -*- coding: utf-8 -*-
-# $LOAD_PATH.unshift "#{File.dirname(__FILE__)}/../lib"
+$LOAD_PATH.unshift "#{File.dirname(__FILE__)}/../lib"
 
 #```ruby
 require 'axlsx'
@@ -202,8 +202,32 @@ wb.add_worksheet(:name => "Automatic cell types") do |sheet|
   sheet.add_row ["Date", "Time", "String", "Boolean", "Float", "Integer"]
   sheet.add_row [Date.today, Time.now, "value", true, 0.1, 1], :style => [date_format, time_format]
 end
+
+
+# Hyperlinks in worksheet
+wb.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_row ['next sheet']
+end
+
+wb.add_worksheet(:name => 'Next Sheet') do |sheet|
+  sheet.add_row ['hello!']
+end
 #```
 
+##Number formatting and currency
+wb.add_worksheet(:name => "Formats and Currency") do |sheet|
+  currency = wb.styles.add_style :num_fmt => 5
+  red_negative = wb.styles.add_style :num_fmt => 8
+  comma = wb.styles.add_style :num_fmt => 3
+  super_funk = wb.styles.add_style :format_code => '[Green]"super funk: " #'
+  sheet.add_row %w(Currency RedNegative, Comma Custom)
+  sheet.add_row [1500, -122.34, 123456789, 594829], :style=> [currency, red_negative, comma, super_funk]
+end
 
 ##Generating A Bar Chart
 
diff --git a/examples/hyperlinks.rb b/examples/hyperlinks.rb
new file mode 100644
index 00000000..d4d332ee
--- /dev/null
+++ b/examples/hyperlinks.rb
@@ -0,0 +1,23 @@
+#!/usr/bin/env ruby -w -s
+# -*- coding: utf-8 -*-
+$LOAD_PATH.unshift "#{File.dirname(__FILE__)}/../lib"
+
+#```ruby
+require 'axlsx'
+
+p = Axlsx::Package.new
+wb = p.workbook
+wb.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_row ['next sheet']
+end
+
+wb.add_worksheet(:name => 'Next Sheet') do |sheet|
+  sheet.add_row ['hello!']
+end
+
+p.serialize 'hyperlinks.xlsx'
diff --git a/lib/axlsx/util/constants.rb b/lib/axlsx/util/constants.rb
index e5fb08f1..122f680d 100644
--- a/lib/axlsx/util/constants.rb
+++ b/lib/axlsx/util/constants.rb
@@ -76,7 +76,7 @@ module Axlsx
   # image rels namespace
   IMAGE_R = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/image"
 
-  # image rels namespace
+  # hyperlink rels namespace
   HYPERLINK_R = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/hyperlink"
 
   # comment rels namespace
diff --git a/lib/axlsx/util/simple_typed_list.rb b/lib/axlsx/util/simple_typed_list.rb
index a7622890..a6c11a5d 100644
--- a/lib/axlsx/util/simple_typed_list.rb
+++ b/lib/axlsx/util/simple_typed_list.rb
@@ -43,7 +43,9 @@ module Axlsx
     def to_ary
       @list
     end
-
+   
+    alias :to_a :to_ary
+    
     # Unlock the list
     # @return [self]
     def unlock
diff --git a/lib/axlsx/workbook/workbook.rb b/lib/axlsx/workbook/workbook.rb
index 9e327e02..17e11ced 100644
--- a/lib/axlsx/workbook/workbook.rb
+++ b/lib/axlsx/workbook/workbook.rb
@@ -27,6 +27,8 @@ require 'axlsx/workbook/worksheet/dimension.rb'
 require 'axlsx/workbook/worksheet/sheet_data.rb'
 require 'axlsx/workbook/worksheet/worksheet_drawing.rb'
 require 'axlsx/workbook/worksheet/worksheet_comments.rb'
+require 'axlsx/workbook/worksheet/worksheet_hyperlink'
+require 'axlsx/workbook/worksheet/worksheet_hyperlinks'
 require 'axlsx/workbook/worksheet/worksheet.rb'
 require 'axlsx/workbook/shared_strings_table.rb'
 require 'axlsx/workbook/defined_name.rb'
diff --git a/lib/axlsx/workbook/worksheet/worksheet.rb b/lib/axlsx/workbook/worksheet/worksheet.rb
index 197e77c8..c92e513e 100644
--- a/lib/axlsx/workbook/worksheet/worksheet.rb
+++ b/lib/axlsx/workbook/worksheet/worksheet.rb
@@ -70,7 +70,11 @@ module Axlsx
     def tables
       @tables ||=  Tables.new self
     end
-    
+
+    def hyperlinks
+      @hyperlinks ||= WorksheetHyperlinks.new self
+    end
+
     # The a shortcut to the worksheet_comments list of comments
     # @return [Array|SimpleTypedList]
     def comments
@@ -391,6 +395,14 @@ module Axlsx
       data_validations << dv
     end
 
+    # Adds a new hyperlink to the worksheet
+    # @param [Hash] options for the hyperlink
+    # @see WorksheetHyperlink for a list of options
+    # @return [WorksheetHyperlink]
+    def add_hyperlink(options={})
+      hyperlinks.add(options)
+    end
+
     # Adds a chart to this worksheets drawing. This is the recommended way to create charts for your worksheet. This method wraps the complexity of dealing with ooxml drawing, anchors, markers graphic frames chart objects and all the other dirty details.
     # @param [Class] chart_type
     # @option options [Array] start_at
@@ -490,12 +502,18 @@ module Axlsx
     # @return [Relationships]
     def relationships
       r = Relationships.new
-      r + [tables.relationships, 
-           worksheet_comments.relationships, 
+      r + [tables.relationships,
+           worksheet_comments.relationships,
+           hyperlinks.relationships,
            worksheet_drawing.relationship].flatten.compact || []
       r
     end
 
+    def relationships_index_of(object)
+      objects = [tables.to_a, worksheet_comments.comments.to_a, hyperlinks.to_a, worksheet_drawing.drawing].flatten.compact || []
+      objects.index(object)
+    end
+
     # Returns the cell or cells defined using excel style A1:B3 references.
     # @param [String|Integer] cell_def the string defining the cell or range of cells, or the rownumber
     # @return [Cell, Array]
@@ -548,7 +566,7 @@ module Axlsx
       [sheet_pr, dimension, sheet_view, column_info,
        sheet_data, @sheet_protection, protected_ranges,
        auto_filter, merged_cells, conditional_formattings,
-       data_validations, print_options, page_margins,
+       data_validations, hyperlinks, print_options, page_margins,
        page_setup, worksheet_drawing, worksheet_comments,
        tables]
     end
diff --git a/lib/axlsx/workbook/worksheet/worksheet_hyperlink.rb b/lib/axlsx/workbook/worksheet/worksheet_hyperlink.rb
new file mode 100644
index 00000000..1c288582
--- /dev/null
+++ b/lib/axlsx/workbook/worksheet/worksheet_hyperlink.rb
@@ -0,0 +1,82 @@
+module Axlsx
+
+  # A worksheet hyperlink object. Note that this is not the same as a drawing hyperlink object.
+  class WorksheetHyperlink
+
+    # Creates a new hyperlink object.
+    # @note the preferred way to add hyperlinks to your worksheet is the Worksheet#add_hyperlink method
+    # @param [Worksheet] worksheet the Worksheet that owns this hyperlink
+    # @param [Hash] options options to use when creating this hyperlink
+    # @option [String] display Display string, if different from string in string table. This is a property on the hyperlink object, but does not need to appear in the spreadsheet application UI.
+    # @option [String] location Location within target. If target is a workbook (or this workbook) this shall refer to a sheet and cell or a defined name. Can also be an HTML anchor if target is HTML file.
+    # @option [String] tooltip The tip to display when the user positions the mouse cursor over this hyperlink
+    # @option [Symbol] target This is :external by default. If you set it to anything else, the location is interpreted to be the current workbook.
+    # @option [String|Cell] ref The location of this hyperlink in the worksheet
+    def initialize(worksheet, options={})
+      DataTypeValidator.validate "Hyperlink.worksheet", [Worksheet], worksheet
+      @worksheet = worksheet
+      @target = :external
+      options.each do |o|
+        self.send("#{o[0]}=", o[1]) if self.respond_to? "#{o[0]}="
+      end
+      yield self if block_given?
+    end
+
+    # String attributes for this object
+    STRING_ATTRIBUTES = %w(display location tooltip)
+
+    #Cell location of hyperlink on worksheet.
+    # @return [String]
+    attr_reader :ref
+
+    # Sets the target for this hyperlink. Anything other than :external instructs the library to treat the location as an in-workbook reference.
+    # @param [Symbol] target
+    def target=(target)
+      @target = target
+    end
+
+    # Sets the cell location of this hyperlink in the worksheet
+    # @param [String|Cell] The string reference or cell that defines where this hyperlink shows in the worksheet.
+    def ref=(cell_reference)
+      cell_reference = cell_reference.r if cell_reference.is_a?(Cell)
+
+      Axlsx::validate_string cell_reference
+      @ref = cell_reference
+    end
+
+    # Dynamically create string attribute accessors
+    STRING_ATTRIBUTES.each do |attr|
+      class_eval %{
+        # The #{attr} attribute reader
+        # @return [String]
+        attr_reader :#{attr}
+
+        # The #{attr} writer
+        # @param [String] value The value to assign to #{attr}
+        # @return [String]
+        def #{attr}=(value)
+          Axlsx::validate_string(value)
+          @#{attr}= value
+        end
+      }
+    end
+
+    def relationship
+      return unless @target == :external
+      Relationship.new HYPERLINK_R, location, :target_mode => :External
+    end
+
+    def id
+     "rId#{@worksheet.relationships_index_of(self)+1}"
+    end
+
+    def to_xml_string(str='')
+      h = instance_values.select { |key, value| %w(display ref tooltip).include? key }
+      h['r:id'] = id if @target == :external
+      h['location'] = location unless @target == :external
+      str << '<hyperlink '
+      h.map { |key, value| str << key.to_s << '="' << value.to_s << '" ' }
+      str << '/>'
+    end
+  end
+end
diff --git a/lib/axlsx/workbook/worksheet/worksheet_hyperlinks.rb b/lib/axlsx/workbook/worksheet/worksheet_hyperlinks.rb
new file mode 100644
index 00000000..b4cdad19
--- /dev/null
+++ b/lib/axlsx/workbook/worksheet/worksheet_hyperlinks.rb
@@ -0,0 +1,35 @@
+module Axlsx
+  #A collection of hyperlink objects for a worksheet
+  class WorksheetHyperlinks < SimpleTypedList
+
+    # Creates a new Hyperlinks collection
+    # @param [Worksheet] worksheet the worksheet that owns these hyperlinks
+    def initialize(worksheet)
+      DataTypeValidator.validate "Hyperlinks.worksheet", [Worksheet], worksheet
+      @worksheet = worksheet
+      super WorksheetHyperlink
+    end
+
+    # Creates and adds a new hyperlink based on the options provided
+    # @see WorksheetHyperlink#initialize
+    # @return [WorksheetHyperlink]
+    def add(options)
+      @list << WorksheetHyperlink.new(@worksheet, options)
+      @list.last
+    end
+
+    def relationships
+      return [] if empty?
+      map { |hyperlink| hyperlink.relationship }
+    end
+
+    # seralize the collection of hyperlinks
+    # @return [String]
+    def to_xml_string(str='')
+      return if empty?
+      str << '<hyperlinks>'
+      @list.each { |hyperlink| hyperlink.to_xml_string(str) }
+      str << '</hyperlinks>'
+    end
+  end
+end
diff --git a/test/tc_package.rb b/test/tc_package.rb
index df56b044..d4333993 100644
--- a/test/tc_package.rb
+++ b/test/tc_package.rb
@@ -7,6 +7,7 @@ class TestPackage < Test::Unit::TestCase
     ws = @package.workbook.add_worksheet
     ws.add_row ['Can', 'we', 'build it?']
     ws.add_row ['Yes!', 'We', 'can!']
+    ws.add_hyperlink :ref => ws.rows.first.cells.last, :location => 'https://github.com/randym'
     ws.workbook.add_defined_name("#{ws.name}!A1:C2", :name => '_xlnm.Print_Titles', :hidden => true)
     ws.protect_range('A1:C1')
     ws.protect_range(ws.rows.last.cells)
diff --git a/test/workbook/worksheet/tc_worksheet_hyperlink.rb b/test/workbook/worksheet/tc_worksheet_hyperlink.rb
new file mode 100644
index 00000000..f8c6bcff
--- /dev/null
+++ b/test/workbook/worksheet/tc_worksheet_hyperlink.rb
@@ -0,0 +1,45 @@
+require 'tc_helper.rb'
+
+class TestWorksheetHyperlink < Test::Unit::TestCase
+  def setup
+    p = Axlsx::Package.new
+    wb = p.workbook
+    @ws = wb.add_worksheet
+    @options = { :location => 'https://github.com/randym/axlsx', :tooltip => 'axlsx', :ref => 'A1', :display => 'AXSLX', :r_id => 'rId1' }
+    @a = @ws.add_hyperlink @options
+  end
+
+  def test_initailize
+    assert_raise(ArgumentError) { Axlsx::WorksheetHyperlink.new }
+  end
+
+  def test_location
+    assert_equal(@options[:location], @a.location)
+  end
+
+  def test_tooltip
+    assert_equal(@options[:tooltip], @a.tooltip)
+  end
+
+  def test_display
+    assert_equal(@options[:display], @a.display)
+  end
+  def test_ref
+    assert_equal(@options[:ref], @a.ref)
+  end
+  def test_r_id
+    assert_equal("rId1", @a.r_id)
+  end
+
+
+  def test_to_xml_string
+    doc = Nokogiri::XML(@a.to_xml_string)
+    puts doc.to_xml
+    assert_equal(doc.xpath("//hyperlink[@ref='#{@a.ref}']").size, 1)
+    assert_equal(doc.xpath("//hyperlink[@tooltip='#{@a.tooltip}']").size, 1)
+    assert_equal(doc.xpath("//hyperlink[@display='#{@a.display}']").size, 1)
+    assert_equal(doc.xpath("//hyperlink[@location='#{@a.location}']").size, 1)
+  end
+end
+
+