Implement :offset option for worksheet#add_row (#87)

4 files changed, 54 insertions, 27 deletions

diff --git a/examples/complex_example.md b/examples/complex_example.md
index 51b85e6b..4f395b02 100644
--- a/examples/complex_example.md
+++ b/examples/complex_example.md
@@ -22,34 +22,34 @@ t_money    = s.add_style b: true, num_fmt: 5, bg_color: 'FFDFDEDF'
 
 wb.add_worksheet do |sheet|
   sheet.add_row
-  sheet.add_row [nil, 'College Budget'], style: [nil, header]
+  sheet.add_row ['College Budget'], style: header, offset: 1
 
   sheet.add_row
-  sheet.add_row [nil, "What's coming in this month.", nil, nil, 'How am I doing'], style: tbl_header
-  sheet.add_row [nil, 'Item', 'Amount', nil, 'Item', 'Amount'], style: [nil, ind_header, col_header, nil, ind_header, col_header]
-  sheet.add_row [nil, 'Estimated monthly net income', 500, nil, 'Monthly income', '=C9'], style: [nil, label, money, nil, label, money]
-  sheet.add_row [nil, 'Financial aid', 100, nil, 'Monthly expenses', '=C27'], style: [nil, label, money, nil, label, money]
-  sheet.add_row [nil, 'Allowance from mom & dad', 20000, nil, 'Semester expenses', '=F19'], style: [nil, label, money, nil, label, money]
-  sheet.add_row [nil, 'Total', '=SUM(C6:C8)', nil, 'Difference', '=F6 - SUM(F7:F8)'], style: [nil, t_label, t_money, nil, t_label, t_money]
+  sheet.add_row ["What's coming in this month.", nil, nil, 'How am I doing'], style: tbl_header, offset: 1
+  sheet.add_row ['Item', 'Amount', nil, 'Item', 'Amount'], style: [ind_header, col_header, nil, ind_header, col_header], offset: 1
+  sheet.add_row ['Estimated monthly net income', 500, nil, 'Monthly income', '=C9'], style: [label, money, nil, label, money], offset: 1
+  sheet.add_row ['Financial aid', 100, nil, 'Monthly expenses', '=C27'], style: [label, money, nil, label, money], offset: 1
+  sheet.add_row ['Allowance from mom & dad', 20000, nil, 'Semester expenses', '=F19'], style: [label, money, nil, label, money], offset: 1
+  sheet.add_row ['Total', '=SUM(C6:C8)', nil, 'Difference', '=F6 - SUM(F7:F8)'], style: [t_label, t_money, nil, t_label, t_money], offset: 1
 
   sheet.add_row
-  sheet.add_row [nil, "What's going out this month.", nil, nil, 'Semester Costs'], style: tbl_header
-  sheet.add_row [nil, 'Item', 'Amount', nil, 'Item', 'Amount'], style: [nil, ind_header, col_header, nil, ind_header, col_header]
-  sheet.add_row [nil, 'Rent', 650, nil, 'Tuition', 200], style: [nil, label, money, nil, label, money]
-  sheet.add_row [nil, 'Utilities', 120, nil, 'Lab fees', 50], style: [nil, label, money, nil, label, money]
-  sheet.add_row [nil, 'Cell phone', 100, nil, 'Other fees', 10], style: [nil, label, money, nil, label, money]
-  sheet.add_row [nil, 'Groceries', 75, nil, 'Books', 150], style: [nil, label, money, nil, label, money]
-  sheet.add_row [nil, 'Auto expenses', 0, nil, 'Deposits', 0], style: [nil, label, money, nil, label, money]
-  sheet.add_row [nil, 'Student loans', 0, nil, 'Transportation', 30], style: [nil, label, money, nil, label, money]
-  sheet.add_row [nil, 'Other loans', 350, nil, 'Total', '=SUM(F13:F18)'], style: [nil, label, money, nil, t_label, t_money]
-  sheet.add_row [nil, 'Credit cards', 450], style: [nil, label, money]
-  sheet.add_row [nil, 'Insurance', 0], style: [nil, label, money]
-  sheet.add_row [nil, 'Laundry', 10], style: [nil, label, money]
-  sheet.add_row [nil, 'Haircuts', 0], style: [nil, label, money]
-  sheet.add_row [nil, 'Medical expenses', 0], style: [nil, label, money]
-  sheet.add_row [nil, 'Entertainment', 500], style: [nil, label, money]
-  sheet.add_row [nil, 'Miscellaneous', 0], style: [nil, label, money]
-  sheet.add_row [nil, 'Total', '=SUM(C13:C26)'], style: [nil, t_label, t_money]
+  sheet.add_row ["What's going out this month.", nil, nil, 'Semester Costs'], style: tbl_header, offset: 1
+  sheet.add_row ['Item', 'Amount', nil, 'Item', 'Amount'], style: [ind_header, col_header, nil, ind_header, col_header], offset: 1
+  sheet.add_row ['Rent', 650, nil, 'Tuition', 200], style: [label, money, nil, label, money], offset: 1
+  sheet.add_row ['Utilities', 120, nil, 'Lab fees', 50], style: [label, money, nil, label, money], offset: 1
+  sheet.add_row ['Cell phone', 100, nil, 'Other fees', 10], style: [label, money, nil, label, money], offset: 1
+  sheet.add_row ['Groceries', 75, nil, 'Books', 150], style: [label, money, nil, label, money], offset: 1
+  sheet.add_row ['Auto expenses', 0, nil, 'Deposits', 0], style: [label, money, nil, label, money], offset: 1
+  sheet.add_row ['Student loans', 0, nil, 'Transportation', 30], style: [label, money, nil, label, money], offset: 1
+  sheet.add_row ['Other loans', 350, nil, 'Total', '=SUM(F13:F18)'], style: [label, money, nil, t_label, t_money], offset: 1
+  sheet.add_row ['Credit cards', 450], style: [label, money], offset: 1
+  sheet.add_row ['Insurance', 0], style: [label, money], offset: 1
+  sheet.add_row ['Laundry', 10], style: [label, money], offset: 1
+  sheet.add_row ['Haircuts', 0], style: [label, money], offset: 1
+  sheet.add_row ['Medical expenses', 0], style: [label, money], offset: 1
+  sheet.add_row ['Entertainment', 500], style: [label, money], offset: 1
+  sheet.add_row ['Miscellaneous', 0], style: [label, money], offset: 1
+  sheet.add_row ['Total', '=SUM(C13:C26)'], style: [t_label, t_money], offset: 1
 
   sheet.add_chart(Axlsx::Pie3DChart) do |chart|
     chart.title = sheet['B11']
diff --git a/lib/axlsx/workbook/worksheet/row.rb b/lib/axlsx/workbook/worksheet/row.rb
index decd27c7..80937cf2 100644
--- a/lib/axlsx/workbook/worksheet/row.rb
+++ b/lib/axlsx/workbook/worksheet/row.rb
@@ -25,11 +25,12 @@ module Axlsx
     # @option options [Array, Symbol] types
     # @option options [Array, Integer] style
     # @option options [Float] height the row's height (in points)
+    # @option options [Integer] offset - add empty columns before values
     # @see Row#array_to_cells
     # @see Cell
     def initialize(worksheet, values=[], options={})
       self.worksheet = worksheet
-      super(Cell, nil, values.size)
+      super(Cell, nil, values.size + options[:offset].to_i)
       self.height = options.delete(:height)
       worksheet.rows << self
       array_to_cells(values, options)
@@ -147,14 +148,15 @@ module Axlsx
     # @option options [Array, Integer] style
     def array_to_cells(values, options={})
       DataTypeValidator.validate :array_to_cells, Array, values
-      types, style, formula_values, escape_formulas = options.delete(:types), options.delete(:style), options.delete(:formula_values), options.delete(:escape_formulas)
+      types, style, formula_values, escape_formulas, offset = options.delete(:types), options.delete(:style), options.delete(:formula_values), options.delete(:escape_formulas), options.delete(:offset)
+      offset.to_i.times { |index| self[index] = Cell.new(self) } if offset
       values.each_with_index do |value, index|
         options[:style] = style.is_a?(Array) ? style[index] : style if style
         options[:type] = types.is_a?(Array) ? types[index] : types if types
         options[:escape_formulas] = escape_formulas.is_a?(Array) ? escape_formulas[index] : escape_formulas if escape_formulas
         options[:formula_value] = formula_values[index] if formula_values.is_a?(Array)
 
-        self[index] = Cell.new(self, value, options)
+        self[index + offset.to_i] = Cell.new(self, value, options)
       end
     end
   end
diff --git a/lib/axlsx/workbook/worksheet/worksheet.rb b/lib/axlsx/workbook/worksheet/worksheet.rb
index 9f3966f8..06a5e7f1 100644
--- a/lib/axlsx/workbook/worksheet/worksheet.rb
+++ b/lib/axlsx/workbook/worksheet/worksheet.rb
@@ -393,6 +393,9 @@ module Axlsx
     # @example - specify whether a certain cells in a row should escape formulas or not
     #     ws.add_row ['=IF(2+2=4,4,5)', '=IF(13+13=4,4,5)'], :escape_formulas=>[true, false]
     #
+    # @example - add a column offset when adding a row (inserts 'n' blank, unstyled columns before data)
+    #     ws.add_row ['I wish', 'for a fish', 'on my fish wish dish'], offset: 3
+    #
     # @see Worksheet#column_widths
     # @return [Row]
     # @option options [Array] values
@@ -400,6 +403,7 @@ module Axlsx
     # @option options [Array, Integer] style
     # @option options [Array] widths each member of the widths array will affect how auto_fit behavies.
     # @option options [Float] height the row's height (in points)
+    # @option options [Integer] offset - add empty columns before values
     # @option options [Array, Boolean] escape_formulas - Whether to treat a value starting with an equal
     #    sign as formula (default) or as simple string.
     #    Allowing user generated data to be interpreted as formulas can be dangerous
diff --git a/test/workbook/worksheet/tc_row.rb b/test/workbook/worksheet/tc_row.rb
index f909129c..e6dc3ef1 100644
--- a/test/workbook/worksheet/tc_row.rb
+++ b/test/workbook/worksheet/tc_row.rb
@@ -136,4 +136,25 @@ class TestRow < Test::Unit::TestCase
     assert_equal(r_s_xml.xpath(".//row[@r=1][@ht=20][@customHeight=1]").size, 1)
   end
 
+  def test_offsets
+    offset = 3
+    values = [1,2,3,4,5]
+    r = @ws.add_row(values, offset: offset, style: 1)
+    r.cells.each_with_index do |c, index| 
+      assert_equal(c.style, index < offset ? 0 : 1)
+      assert_equal(c.value, index < offset ? nil : values[index - offset])
+    end
+  end
+
+  def test_offsets_with_styles
+    offset = 3
+    values = [1,2,3,4,5]
+    styles = [6,7,8,9,10]
+    r = @ws.add_row(values, offset: offset, style: styles)
+    r.cells.each_with_index do |c, index| 
+      assert_equal(c.style, index < offset ? 0 : styles[index-offset])
+      assert_equal(c.value, index < offset ? nil : values[index - offset])
+    end
+  end
+
 end