blum_price_list.rb

require 'rubyXL'

class BlumPriceList
  def initialize(input_file, output_file, skip_rows = 0)
    @input_file = input_file
    @output_file = output_file
    @skip_rows = skip_rows
  end

  def process
    data = read_xlsx
    hierarchical_data = sort_data(data)
    processed_data = process_data(hierarchical_data)
    write_xlsx(processed_data)
    puts "Данные успешно сохранены в файл: #{@output_file}"
  rescue => e
    puts "Произошла ошибка: #{e.message}"
  end

  private

  def read_xlsx
    workbook = RubyXL::Parser.parse(@input_file)
    sheet = workbook[0]
    data = []

    sheet.each_with_index do |row, row_index|
      next if row_index < @skip_rows || row.nil?

      row_data = row.cells.map do |cell|
        if cell&.value.is_a?(Float)
          format('%.2f', cell.value).gsub('.', ',')
        else
          cell&.value || nil
        end
      end
      data << row_data
    end
    data
  end

  def sort_data(data)
    data.map do |d|
      if d[5].nil?
        level = case d[1]
                when /^ {8}\S/ then 0
                when /^ {12}\S/ then 1
                when /^ {16}\S/ then 2
                when /^ {20}\S/ then 3
                else
                  raise d[1]
                end

        name = d[1].gsub('/', '&')
                   .gsub(' & ', '&')
                   .gsub(' &', '&')
                   .gsub('& ', '&')
                   .strip


        [level, name]
      else
        d
      end
    end
  end

  def process_data(tree)
    names = []
    result = []

    tree.each do |e|
      if e.size == 2
        names = [] if e[0]&.zero?
        names[e[0]] = e[1]
      else
        group_name = "Blum/#{names.join('/')}"
        result << [e[2], e[1].strip, group_name, e[4], e[5], e[3], 'EUR']
      end
    end

    result
  end

  def write_xlsx(data)
    workbook = RubyXL::Workbook.new
    worksheet = workbook[0]

    headers = [
      'Артикул материала',
      'Наименование материала',
      'Наименование группы',
      'Стоимость',
      'Единица измерения',
      'Идентификатор для синхронизации',
      'Валюта'
    ]

    headers.each_with_index { |header, index| worksheet.add_cell(0, index, header) }

    data.each_with_index do |row, row_index|
      row.each_with_index do |value, col_index|
        worksheet.add_cell(row_index + 1, col_index, value)
      end
    end

    workbook.write(@output_file)
  end
end

input_file = 'Blum price 06.2024.xlsx'
output_file = 'Blum price 06 2024 БМ.xlsx'
skip_rows = 8

processor = BlumPriceList.new(input_file, output_file, skip_rows)
processor.process