Ruby: transform a data structure into an excel sheet

Question

I have processed a log file and created a data structure of below format

values = [ {:VM=>"VM_US_OLA_1"}
{:id=>"OLFTB51", :datum=>{"LAP"=>"6.93817", "YCC_OWER"=>"1.0391"}}
{:id=>"OLFTB10", :datum=>{"LAP_2"=>"2.72646", "CFG_ON"=>"15.9489746", "YCC_ON"=>".401794"}}

{:VM=>"VM_ASIA_FLO_1"}
{:id=>"LOPMLAP", :datum=>{"LAP"=>"1.81048584", "FM_ON"=>".00"} ]

values is an array.

I'm trying to create a spreadsheet of below format where only the VM column gets highlighted in green and for every VM set, a blank line highlighted in 'yellow' should be inserted. I tried multiple approaches and went thru the axlsx documentation too but unable to get the desired format.

My excel snippet below:

require 'axlsx'
p = Axlsx::Package.new

p.workbook.add_worksheet(:name => "Statistics") do |sheet|
    style1 = sheet.styles.add_style(:bg_color => "EF0920", :fg_color => "FFFFFF", b:true)
    style2 = sheet.styles.add_style(:bg_color => "00FF00", :fg_color => "FFFFFF", b:true)
    sheet.add_row ["VM", "NAME", "DETAILS", "OCC"], :style => style1
    values.each do |val|
      sheet.add_row [ val[:VM], val[:id] ], :style =>[style2, nil]
      val[:datum].each do |k, v|
        sheet << ["", "",  k, v]        
       end 
         end
    sheet.add_row
end

p.serialize 'Stat.xlsx'

Any suggestions should be really helpful here, many thanks.

Expected Output

Current Output

Adding Log file and my code

Log

        ---- vm name ----
        VM_US_OLA_1

        OLFTB51


        OWNER                   IN_GB
        ------------------------------ ----------
        LAP                    6.93817
        YCC_OWER               1.0391

        =========================================


        ---- vm name ----


        OLFTB10


        OWNER                   IN_GB
        ------------------------------ ----------
        LAP_2                  2.7266846
        CFG_ON                 15.9489746
        YCC_ON                .401794


        =========================================


        ---- vm name ----
        VM_ASIA_FLO_1

        LOPMLAP


        OWNER                   IN_GB
        ------------------------------ ----------
        LAP                1.81048584
        FM_ON              .00


        =========================================


        ---- vm name ----

        INGTY_2


        OWNER                   IN_GB
        ------------------------------ ----------

  =========================================

    so on of the same format

Code to process the logs

require 'csv'

values = []

total = File.read("final.log")
total.each_line do |line|
  line.strip!
  next if line.empty?
  next if line.include?('selected') || line.include?('IN_GB')
  next if ['-','='].include? line[0]
  parts = line.split ' '
   if parts.size == 1 and line.start_with?('size')
    values[current += 1] = {vm: line.strip}
    next
  elsif parts.size == 1 and parts = /^(?!.*size_).*$/
    values[current += 1] = {id: line, datum: {}}
    next
  end

  parts.each_cons(2) do |key, value|
    values[current][:datum][key] = value 
    end
end

puts values

Answer 1

The problem is with the structure of your data. Try to change to have the values of your array in this structure

{:VM=>"VM_US_OLA_1", :id=>"OLFTB51", :datum=>{"LAP"=>"6.93817", "YCC_OWER"=>"1.0391"}

What's happening is VM is one record in your array, and the other data is another record in the array, you need to merge them so you access them in the same loop iteration

EDIT

I guess how we parse the log file is a bit tricky, so I am going to leave it but I am sure that this part I am going to introduce can be done in the log file parsing

First, we can change the structure of the array like that (before p.workbook.add_worksheet)

dataset = []
values.each_with_index do |value|
    if value[:VM].present? # or :vm, not sure about the key
        dataset << value
        dataset.last[:data] = []
    else 
        dataset.last[:data] << value
    end
end

Then this loop: values.each do |val|

Can be changed to dataset.each do |val|

Then you can continue with the rest of your logic:

  sheet.add_row [ val[:VM], val[:data][0][:id] ], :style =>[style2, nil]
  val[data].each do |record|
    record[:datanum].each do |k, v|

The problem with your previous code is, you used to add a new row sheet.add_row at the end of each iteration, so this was messing things up as you were expecting the data you need to be just right after your VM but a new line has already been inserted