template_generator.rb

require 'rubyXL'

workbook = RubyXL::Parser.parse('testsheet.xlsx')
sheet = workbook.worksheets[0]

class TemplateSheet
  attr_reader :sheet, :row_count

  RowRepeater = Struct.new(:row, :array_name, :item_variable_name, :index_variable_name)

  ROW_META_REGEXP = /^::for_each_row_of::(\w+)::(\w+)::(\w+)$/
  MOUSTACHE_REGEXP = /^{{([a-zA-Z.]+)}}$/

  def initialize(sheet)
    @sheet = sheet
    # Memoize as sheet might start changing
    expressions
    meta_comments
    @row_count = expressions.keys.map(&:first).max + 1
  end

  def repeaters_for_row(row)
    meta_comments.find { |repeater| repeater.row == row }
  end

  def expression(row, column)
    @expressions[[row, column]]
  end

  private

  def expressions
    @expressions ||= sheet
      .to_a
      .compact
      .flat_map(&:cells)
      .compact
      .map { |cell|
        match = cell.value.scan(MOUSTACHE_REGEXP).first
        [[cell.row, cell.column], match.first] if match
      }
      .compact
      .to_h
  end

  def meta_comments
    @meta_comments ||= sheet
      .comments
      .flat_map(&:comment_list)
      .map { |comment|
        match = comment.text.to_s.scan(ROW_META_REGEXP).first
        if match
          array_name, item_variable_name, index_variable_name = match
          RowRepeater.new(comment.ref.first_row, array_name, item_variable_name, index_variable_name)
        end
      }
      .compact
      .sort_by(&:row)
  end
end

class SheetProcessor
  attr_reader :sheet, :form, :template

  def initialize(sheet, form)
    @sheet = sheet
    @form = form
    @template = TemplateSheet.new(sheet)
  end

  def process!
    @template_row = 0
    @current_row = 0

    while @template_row < template.row_count
      repeaters = template.repeaters_for_row(@template_row)
      apply_repeaters(repeaters)

      @template_row += 1
    end

    # Note: This might be excessive, we could only delete meta comments.
    sheet.comments = []
  end

  private

  def apply_repeaters(repeater)
    @repeater_variables = {}
    if repeater.nil?
      process_row_expressions(@current_row)
      @current_row += 1
      return
    end
    array = get_data(repeater.array_name)
    if array.length.zero?
      sheet.delete_row(@current_row)
    else
      array.each_with_index do |_item, index|
        next if index.zero?
        sheet.insert_row(@current_row + index)
        copy_row_to(@current_row, @current_row + index)
      end
      array.each_with_index do |item, index|
        bind_repeater_variables(repeater, item, index)
        process_row_expressions(@current_row + index)
      end
    end
    @current_row += array.length
  end

  def bind_repeater_variables(repeater, item, index)
    @repeater_variables[repeater.item_variable_name] = item
    @repeater_variables[repeater.index_variable_name] = index
  end

  def copy_row_to(source_index_row, to_index_row)
    sheet[source_index_row].cells.each do |cell|
      next if cell.nil?
      sheet[to_index_row][cell.column].change_contents(cell.value, cell.formula)
    end
  end

  def process_row_expressions(row_index)
    sheet[row_index].cells.each do |cell|
      next if cell.nil?
      expression = template.expression(@template_row, cell.column)
      update_cell_contents(get_data(expression)) if expression
    end
  end

  def update_cell_contents(value)
    if value.is_a?(Numeric)
      cell.change_contents('', value)
    else
      cell.change_contents(value)
    end
  end

  def get_data(name, contexts: [@repeater_variables, form])
    contexts
      .map { |context|
        name
          .split('.')
          .reduce(context) { |c, part| fetch_from_context(part, c) }
      }
      .compact
      .first
  end

  def fetch_from_context(key, context)
    if context.nil?
      nil
    elsif context.is_a?(Hash)
      context[key]
    else
      context.public_send(key)
    end
  end
end

form = OpenStruct.new(
  foo: 5,
  bar: 'ProductName',
  some_array: [
    OpenStruct.new(value: 5),
    OpenStruct.new(value: 'abc'),
    OpenStruct.new(value: 'efg')
  ]
)
SheetProcessor.new(sheet, form).process!
# workbook.write('test.xlsx')