sheety.rb

require 'axlsx'
require 'batch_factory'

def off_balance?(count)
  !(count =~ /^0\d\d/).nil? || !(count =~ /^\D/).nil?
end

keys = [:code, :name, :sub1, :sub2, :sub3]

skk  = BatchFactory.from_file('skk.xlsx', keys: keys).drop(1)
work = BatchFactory.from_file('work.xlsx', keys: keys.push(:info)).drop(1)

skk_by_code = skk.reduce({}) { |memo, count| memo.merge(count[:code] => count) }
work_by_code = work.reduce({}) { |memo, count| memo.merge(count[:code] => count) }

codes = skk_by_code.keys | work_by_code.keys
sorted_codes = codes.sort do |a, b|
  if off_balance?(a) && !off_balance?(b)
    1
  elsif !off_balance?(a) && off_balance?(b)
    -1
  else
    a <=> b
  end
end

res = sorted_codes.map do |code|
  {
    skk:  skk_by_code[code] || {},
    work: work_by_code[code] || {}
  }
end

Axlsx::Package.new do |p|
  workbook = p.workbook
  workbook.add_worksheet do |sheet|
    # styles definition
    centered = sheet.styles.add_style alignment: { horizontal: :center }

    sheet.add_row ['1С: СКК', 'Рабочий план счетов', nil]
    res.each do |pair|
      skk  = pair[:skk]
      work = pair[:work]

      sheet.add_row [skk[:code], work[:code], work[:info]], types: [:string, :string, :string]
      sheet.add_row [skk[:name], work[:name]], types: [:string, :string]
      sheet.add_row [skk[:sub1], work[:sub1]], types: [:string, :string]
      sheet.add_row [skk[:sub2], work[:sub2]], types: [:string, :string]
      sheet.add_row [skk[:sub3], work[:sub3]], types: [:string, :string]

      sheet.add_row [nil, nil, nil]
      sheet.merge_cells sheet.rows.last.cells[(0..-1)]
      # sheet.rows.last.cells[0].value = '--------------------'
      # sheet.rows.last.cells[0].style = centered
    end
  end
  p.serialize 'results.xlsx'
end