時系列可視化

to_graph.rb

#coding: Windows-31J

require 'win32ole'
require 'date'

$ROUND_MIN = 15

module ExcelConstants
end

class Status
  attr_accessor :prog_id, :stime, :etime, :stime_raw, :etime_raw
  def to_s
    "(" + [@prog_id, @stime_raw.strftime("%Y/%m/%d %H:%M"), @etime_raw.strftime("%Y/%m/%d %H:%M")].join(", ") + ")"
  end
end

def create_new_sheet(e)
  e.sheetsInNewWorkbook = 1
  book = e.workbooks.add
  sheet = book.activeSheet
  sheet.name = "実行結果(" + $ROUND_MIN.to_s + "分単位)"
  
  yield sheet
  
  book.SaveAs(Filename: File.expand_path("./output.xls").gsub(/\//, "\\"))
end

def range_id(col, row)
  col + row.to_s
end

def write_timeline_header(sheet, min, max)
  sheet.Range("A1").value = "日付"
  sheet.Range("A2").value = "時"
  sheet.Range("A3").value = "分"
  
  t = min
  prev_date = nil
  prev_hour = nil
  col = 'B'
  while t <= max
    date = t.strftime('%Y/%m/%d')
    hour = t.strftime('%H')
    min  = t.strftime('%M')
    
    sheet.Range(range_id(col, 1)).value = date if !prev_date or prev_date != date
    sheet.Range(range_id(col, 2)).value = hour if !prev_hour or prev_hour != hour
    sheet.Range(range_id(col, 3)).value = min
    
    col = col.succ
    
    prev_date = date
    prev_hour = hour
    
    t += by_min * $ROUND_MIN
  end
  sheet.Columns("B:" + col).ColumnWidth = 2
end

def setup_sheet(sheet)
  sheet.Cells.Font.Name = "MS ゴシック"
  sheet.Cells.Font.Size = 10
  
  sheet.Rows(1).NumberFormat = "@"
  sheet.Range("B4").select
  sheet.parent.parent.ActiveWindow.FreezePanes = true
end

def gen_column_list(min, max)
  columns = []
  t = min
  col = 'B'
  while t <= max
    columns << col
    col = col.succ
    t += by_min * $ROUND_MIN
  end
  columns
end


def write_timeline(sheet, result, min, max)
  #puts "min max"
  #puts [min, max].join("\t")
  color_index_list = [
    38, #ローズ       #FF99CC
    34, #薄い水色     #CCFFFF
    36, #薄い黄       #FFFF99
    37, #ペールブルー #99CCFF
    35, #薄い緑       #CCFFCC
    39, #ラベンダー   #CC99FF
    40, #ベージュ     #FFCC99
  ]
  column_list = gen_column_list(min, max)
  row_offset = 4
  result.each_with_index do |r, i|
    row = (row_offset + i).to_s
    color = color_index_list[i % color_index_list.size]
    sheet.Range("A" + row).value = r.prog_id
    sheet.Range("A" + row).Interior.ColorIndex = color
    stime_offset = ((r.stime - min) / (by_min * $ROUND_MIN)).to_i
    etime_offset = ((r.etime - min) / (by_min * $ROUND_MIN)).to_i
    #p [r.prog_id, stime_offset, etime_offset, r.stime, r.etime]
    
    scol = column_list[stime_offset] + row
    ecol = column_list[etime_offset] + row
    pos = scol + ":" + ecol
    
    sheet.Range(pos).value = "■"
    sheet.Range(pos).Interior.ColorIndex = color
    sheet.Range(pos).VerticalAlignment = ExcelConstants::XlVAlignCenter
    sheet.Range(pos).HorizontalAlignment = ExcelConstants::XlHAlignCenter
    
    #開始-終了(実行時間(分))
    sheet.Range(column_list[etime_offset].succ + row).value =
    r.stime_raw.strftime("%H:%M:%S") + "-" + r.etime_raw.strftime("%H:%M:%S") + 
    "(" + ((r.etime_raw - r.stime_raw) / by_min).to_i.to_s + "分)"
  end
end

def run_excel(e)
  result = load_result($stdin)
  
  min_datetime = result.min_by{|x| x.stime}.stime
  max_datetime = result.max_by{|x| x.etime}.etime
  
  create_new_sheet(e) do |sheet|
    setup_sheet(sheet)
    write_timeline_header(sheet, min_datetime, max_datetime)
    
    write_timeline(sheet, result, min_datetime, max_datetime)
  end
end

def by_sec
  Rational(1, 86400) # 24 * 60 * 60
end

def by_min
  Rational(1, 1440) #24 * 60
end

#prog_id\tstart datetime\tend datetime
def load_result(input)
  identity = ->(x) {x}
  
  to_time = ->(x) {
    DateTime.strptime(x, '%Y/%m/%d %H:%M:%S')
  }
  
  to_rounded_time = ->(x) {
    d = DateTime.strptime(x, '%Y/%m/%d %H:%M:%S')
    d = d - by_sec * d.sec #秒を0にする
    
    #$ROUND_MIN分単位で丸める
    delta = d.min - (d.min / $ROUND_MIN) * $ROUND_MIN 
    d - by_min * delta
  }

  input.each_line.inject([]) do |acc, line|
    obj = Status.new
    
    syms = [:prog_id, :stime, :etime, :stime_raw, :etime_raw]
    converters = [identity, to_rounded_time, to_rounded_time, to_time, to_time]
    prog_id, start_time, end_time = line.chomp.split(/\t/)
    
    syms.zip([prog_id, start_time, end_time, start_time, end_time], converters).each do |sym, val, converter|
      obj.send((sym.to_s + "=").to_sym, converter.(val))
    end
    
    acc << obj
    acc
  end
end

def with_excel
  excel = WIN32OLE.new('Excel.Application')
  WIN32OLE.const_load(excel, ExcelConstants)
  
  excel.visible = true
  excel.displayAlerts = false
  
  run_excel(excel)
ensure
  excel.quit
end

def main
  with_excel do |e|
    run_excel(e)
  end
end

main