bom2excel.rb

#!/usr/bin/ruby

#require 'csv'
require 'axlsx'
require 'rexml/document'

Component = Struct.new(:item, :ref, :value, :footprint)
Order = Struct.new(:item, :value, :footprint, :count)

xml_file = File.expand_path(ARGV[0])
csv_file = ARGV[1] ? ARGV[1] : xml_file.sub(/\.xml$/, ".csv")
csv_file += ".csv" if xml_file == csv_file
excel_file = ARGV[1] ? ARGV[1] : xml_file.sub(/\.xml$/, ".xlsx")
excel_file += ".xlsx" if xml_file == csv_file

bom = REXML::Document.new(open(xml_file))

components = REXML::XPath.match(bom, "/export/components/comp").map{|comp|
  ref = comp.attribute("ref").value
  item = ref.sub(/\d+/, "")
  value = comp.elements["value"].text
  footprint = comp.elements["footprint"].text
  Component.new(item, ref, value, footprint)
}

components.sort!{|a,b|
  if a.item == b.item
    if a.value == b.value
      a.ref.gsub(/\D/, "").to_i <=> b.ref.gsub(/\D/, "").to_i
    else
      a.value <=> b.value
    end
  else
    a.item <=> b.item
  end
}

#CSV.open(csv_file, "w"){|csv|
#  csv << ["Ref", "Value", "Footprint"]
#  components.each{|row|
#    csv << [row.ref, row.value, row.footprint]
#  }
#}

excel = Axlsx::Package.new                             
order_sheet = excel.workbook.add_worksheet(name: 'order')
mount_sheet = excel.workbook.add_worksheet(name: 'mount')

mount_sheet.add_row(["Ref", "Value", "Footprint"])
components.each{|row|
  mount_sheet.add_row([row.ref, row.value, row.footprint])
}

order_items = []
count = 0
item = nil
value = nil
footprint = nil
components.each{|row|
  if (row.item == item) and (row.value == value) and (row.footprint == footprint)
    count += 1
  else
    order_items << Order.new(item, value, footprint, count) if item or value or footprint
    item = row.item
    value = row.value
    footprint = row.footprint
    count = 1
  end
}
order_items << Order.new(item, value, footprint, count)

order_sheet.add_row(["Item", "Value", "Footprint", "Count"])
order_items.each{|row|
  order_sheet.add_row([row.item, row.value, row.footprint, row.count])
}

excel.serialize(excel_file)