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.

1 files changed, 8 insertions, 3 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