Cache col_ref to avoid allocations

In cases with lots of rows, each column will ask for its col_ref which will
always be the same for the same column_index. We can cache this to avoid
lots of small string allocations.

Modified `CellSerializer` to use `#col_ref` and `#row_ref` avoiding the string
allocation caused by `#col_r`

3 files changed, 32 insertions, 10 deletions

diff --git a/lib/axlsx.rb b/lib/axlsx.rb
index 4f15ba23..6e6a317d 100644
--- a/lib/axlsx.rb
+++ b/lib/axlsx.rb
@@ -112,21 +112,35 @@ module Axlsx
   # @note This follows the standard spreadsheet convention of naming columns A to Z, followed by AA to AZ etc.
   # @return [String]
   def self.col_ref(index)
-    chars = +''
-    while index >= 26 do
-      index, char = index.divmod(26)
-      chars.prepend((char + 65).chr)
-      index -= 1
+    # Every row will call this for each column / cell and so we can cache result and avoid lots of small object
+    # allocations.
+    @col_ref ||= {}
+    @col_ref[index] ||= begin
+      i = index
+      chars = +''
+      while i >= 26
+        i, char = i.divmod(26)
+        chars.prepend((char + 65).chr)
+        i -= 1
+      end
+      chars.prepend((i + 65).chr)
+      chars.freeze
+      chars
     end
-    chars.prepend((index + 65).chr)
-    chars
+  end
+
+  # converts the row index into string values.
+  # @note The spreadsheet rows are 1-based and the passed in index is 0-based, so we add 1.
+  # @return [String]
+  def self.row_ref(index)
+    (index + 1).to_s
   end
 
   # @return [String] The alpha(column)numeric(row) reference for this sell.
   # @example Relative Cell Reference
   #   ws.rows.first.cells.first.r #=> "A1"
   def self.cell_r(c_index, r_index)
-    col_ref(c_index) << (r_index + 1).to_s
+    col_ref(c_index) + row_ref(r_index)
   end
 
   # Creates an array of individual cell references based on an excel reference range.
diff --git a/lib/axlsx/workbook/worksheet/cell_serializer.rb b/lib/axlsx/workbook/worksheet/cell_serializer.rb
index e1bdf728..2baa4271 100644
--- a/lib/axlsx/workbook/worksheet/cell_serializer.rb
+++ b/lib/axlsx/workbook/worksheet/cell_serializer.rb
@@ -10,7 +10,9 @@ module Axlsx
       # @param [String] str The string to apend serialization to.
       # @return [String]
       def to_xml_string(row_index, column_index, cell, str = +'')
-        str << '<c r="' << Axlsx::cell_r(column_index, row_index) << '" s="' << cell.style.to_s << '" '
+        str << '<c r="'
+        str << Axlsx::col_ref(column_index) << Axlsx::row_ref(row_index)
+        str << '" s="' << cell.style.to_s << '" '
         return str << '/>' if cell.value.nil?
 
         method = cell.type
diff --git a/test/tc_axlsx.rb b/test/tc_axlsx.rb
index 95c77d88..a70722ee 100644
--- a/test/tc_axlsx.rb
+++ b/test/tc_axlsx.rb
@@ -83,8 +83,14 @@ class TestAxlsx < Test::Unit::TestCase
     end
   end
 
+  def test_row_ref
+    assert_equal('1', Axlsx.row_ref(0))
+    assert_equal('100', Axlsx.row_ref(99))
+  end
+
   def test_cell_r
-    # todo
+    assert_equal('A1', Axlsx.cell_r(0, 0))
+    assert_equal('Z26', Axlsx.cell_r(25, 25))
   end
 
   def test_range_to_a