Fix worksheet name escaping and cell sorting in Axlsx.cell_range

We've discovered that Excel do not allow ' in defined_names to
reference a worksheet name. All it does allow is to escape the simple
quote by doubling it. Given that Worksheet#name is escaped using
HTMLEntities, a simple quote appears as ' we're simply replacing it by
the correct '' when using absolute reference to worksheet range.

2 files changed, 28 insertions, 5 deletions

diff --git a/lib/axlsx.rb b/lib/axlsx.rb
index c0ed2e9c..2c64a193 100644
--- a/lib/axlsx.rb
+++ b/lib/axlsx.rb
@@ -48,9 +48,14 @@ module Axlsx
   # determines the cell range for the items provided
   def self.cell_range(cells, absolute=true)
     return "" unless cells.first.is_a? Cell
-    sort_cells(cells)
+    cells = sort_cells(cells)
     reference = "#{cells.first.reference(absolute)}:#{cells.last.reference(absolute)}"
-    absolute ? "'#{cells.first.row.worksheet.name}'!#{reference}" : reference
+    if absolute
+      escaped_name = cells.first.row.worksheet.name.gsub "'", "''"
+      "'#{escaped_name}'!#{reference}"
+    else
+      reference
+    end
   end
 
   # sorts the array of cells provided to start from the minimum x,y to
@@ -66,7 +71,7 @@ module Axlsx
   def self.coder
     @@coder ||= ::HTMLEntities.new
   end
-  
+
   # returns the x, y position of a cell
   def self.name_to_indices(name)
     raise ArgumentError, 'invalid cell name' unless name.size > 1
diff --git a/test/tc_axlsx.rb b/test/tc_axlsx.rb
index fe3b7edd..4901ede2 100644
--- a/test/tc_axlsx.rb
+++ b/test/tc_axlsx.rb
@@ -14,8 +14,26 @@ class TestAxlsx < Test::Unit::TestCase
     }
   end
 
-  def test_cell_range
-    #To do
+  def test_cell_range_empty_if_no_cell
+    assert_equal(Axlsx.cell_range([]), "")
+  end
+
+  def test_cell_range_relative
+    p = Axlsx::Package.new
+    ws = p.workbook.add_worksheet
+    row = ws.add_row
+    c1 = row.add_cell
+    c2 = row.add_cell
+    assert_equal(Axlsx.cell_range([c2, c1], false), "A1:B1")
+  end
+
+  def test_cell_range_absolute
+    p = Axlsx::Package.new
+    ws = p.workbook.add_worksheet :name => "Sheet <'>\" 1"
+    row = ws.add_row
+    c1 = row.add_cell
+    c2 = row.add_cell
+    assert_equal(Axlsx.cell_range([c2, c1], true), "'Sheet &lt;''&gt;&quot; 1'!$A$1:$B$1")
   end
 
   def test_name_to_indices