元データからパフォーマンスレポートを作成するスクリプト

createPerf.rb

require "rubygems"
require "zipruby"
require "win32ole"

class ExcelUtil
  def initialize
    @xl = nil
    @filename = nil
  end

  def ActiveSheet
    @xl.ActiveSheet
  end

  def [] x, y
    @xl.ActiveSheet.Cells.Item(y,x)
  end

  def setValue x,y,val
    @xl.ActiveSheet.Cells.Item(y,x).Value = val
  end

  def formula x,y
    @xl.ActiveSheet.Cells.Item(y,x).FormulaR1C1
  end
  
  def setFormula x,y,formula
    @xl.ActiveSheet.Cells.Item(y,x).FormulaR1C1 = formula
  end

  def insertRow line
    @xl.ActiveSheet.Range("#{line}:#{line}").Insert
  end

  def setFormat x, y, val
    ash = @xl.ActiveSheet
    ash.Range(ash.Cells(y,x), ash.Cells(y,x)).NumberFormatLocal = val
  end

  def insertRows lines
    if lines.size > 50
      lines.max.downto(lines.min) do |l|
        insertRow l
      end
    else
      range = ""
      lines.each do |l|
        range += "#{l}:#{l},"
      end
      puts range
      @xl.ActiveSheet.Range(range[0..-2]).Insert
    end
  end

  def empty? x, y
    self[x,y].Value == "" or self[x,y].Value.nil?
  end

  def addSheet name
    @xl.Worksheets.Add.Name = name
  end

  def openWorkbook filename
    @xl ||= newExcel
    return @xl.Workbooks.Open(getAbsPath(filename))
  end

  def filename
    return @filename
  end

  def sheet= sheetname
    return unless @xl
    @xl.Worksheets(sheetname).Activate
  end

  def saveWorkbook name=""
    if name == ""
      @xl.ActiveWorkbook.Save
    else
      @xl.ActiveWorkbook.SaveAs name
    end
  end

  def closeWorkbook
    if @xl
      @xl.Workbooks.Close
    end
  end

  def openExcel
    @xl = WIN32OLE.new('Excel.Application')
    @xl.Visible = false
    @xl.DisplayAlerts = false
    return @xl
  end

  def closeExcel
    return unless @xl
    @xl.Workbooks.Close
    @xl.Quit
  end

  private
  def getAbsPath filename
    fso = WIN32OLE.new('Scripting.FileSystemObject')
    @filename = fso.GetAbsolutePathName(filename)
    return @filename
  end
end

def extractAllZip
  Dir.glob('*.zip') do |zipfile|
    Zip::Archive.open(zipfile) do |arcs|
      arcs.each do |f|
        open(f.name, 'wb') do |af|
          af << f.read
        end
      end
    end
  end
end

class Report
  def initialize ex
    @ex = ex

    @formats = {2=>"@", 4=>"@", 8=>"@", 10=>"@", 11=>"@", 16=>"@", 27=>"@", 32=>"@",
      6=>"#,##0", 12=>"#,##0", 17=>"#,##0", 18=>"#,##0", 19=>"#,##0.####",}

    @formulas = {20=>'=BDP("Z_CODE JT Equity","EQY_WEIGHTED_AVG_PX","VWAP_START_TIME=Z_S_TM","VWAP_END_TIME=Z_E_TM","VWAP_DT=Z_DT")',
      21=>'=BDP("Z_CODE JT Equity","EQY_WEIGHTED_AVG_PX","VWAP_DT=Z_DT")',
      22=>'=BDH("Z_CODE JT Equity","PX_CLOSE_1D","Z_DT","Z_DT")',
      23=>'=BDH("Z_CODE JT Equity","PX_OPEN","Z_DT","Z_DT")',
      24=>'=BDH("Z_CODE JT Equity","PX_HIGH","Z_DT","Z_DT")',
      25=>'=BDH("Z_CODE JT Equity","PX_LOW","Z_DT","Z_DT")',
      26=>'=BDH("Z_CODE JT Equity","PX_LAST","Z_DT","Z_DT")',
      28=>'=BDH("Z_CODE JT Equity","VOLUME","Z_DT","Z_DT")',
      29=>'=BDP("Z_CODE JT Equity","EQY_WEIGHTED_AVG_PX","VWAP_START_TIME=Z_S_TM","VWAP_END_TIME=15:00","VWAP_DT=Z_DT")',
      30=>'=BDH("Z_CODE JT Equity","PX_LAST_AM","Z_DT","Z_DT")',}

    @mapping = {2=>"Z_DT",3=>3, 4=>5, 5=>8, 6=>12, 8=>"Z_C_DT", 9=>"", 
      10=>"Z_S_DT", 11=>"Z_S_DT", 12=>12, 13=>18, 14=>"Z_BROKER_CODE", 15=>"", 
      16=>"Z_E_TM", 17=>26, 18=>28, 19=>27, 20=>"Z_F", 21=>"Z_F", 22=>"Z_F", 23=>"Z_F", 
      24=>"Z_F", 25=>"Z_F",26=>"Z_F",27=>"parentID", 28=>"Z_F",29=>"Z_F",30=>"Z_F",
      31=>4, 33=>"CashEquity", 34=>"Z_AKA", 35=>"", 36=>"",}
    
    @ivars = ["Z_CODE", "Z_AKA", "Z_BROKER_CODE", "Z_DT", "Z_C_DT", "Z_S_DT", "Z_E_TM", "Z_S_TM"]
  end

  def readInitValues
    @ex.sheet = "InitValue"
    l = 1
    until @ex.empty?(1,l)
      name = @ex[1,l].Value
      val = @ex[3,l].Value == "int" ? @ex.formula(2,l).to_i : @ex.formula(2,l)
      instance_variable_set("@#{name}", val)

      l = l + 1
    end
  end

  def generatePerfReport
    @ex.addSheet "Perf"
    @ex.sheet = "Report"
    first = 3
    l = first
    until @ex.empty?(2,l)
      l = l + 1
    end
    @max = l - 1

    @max.downto(3) do |l|
      puts l
      mapline = readLine l
      generateLine l, mapline
    end
  end

  def saveChangedFile
    newname = @ex.filename + "_compare.xls"
    @ex.saveWorkbook newname
    puts "New file #{newname} generated."
  end

  private
  def readLine l
    @ex.sheet = "Report"

    mappingLine = Hash.new
    @Z_CODE = @ex.formula(5, l)
    @mapping.each do |d_col, s_col|
      if s_col.is_a?(Integer)
        v = @ex.formula(s_col, l)
        mappingLine[d_col] = v
      else
        case s_col
        when "Z_F"
          mappingLine[d_col] = generateFormula(@formulas[d_col])
        when "parentID"
          mappingLine[d_col] = @parentID
          @parentID -= 1
        when *@ivars
          mappingLine[d_col] = eval("@#{s_col}")
        else
          mappingLine[d_col] = s_col
        end
      end
    end
    return mappingLine
  end

  def generateLine l, mapping
    @ex.sheet = "Perf"

    mapping.each do |col, val|
      @ex.setFormat(col, l, @formats[col])
      if col == 19 
        if val == val.to_i.to_s
          @ex.setFormat(col, l, "#,##0")
        end
      end
      @ex.setFormula(col, l, val)
    end
  end

  def generateFormula src
    formula = src[0..-1]
    @ivars.each do |ivar|
      formula.gsub!(ivar, eval("@#{ivar}"))
    end
    return formula
  end

end

exit unless ARGV[0]

begin
  ex = ExcelUtil.new
  ex.openExcel

  Dir.glob(ARGV[0]) do |xlsf|
    puts "Processing #{xlsf}."
    ex.openWorkbook(xlsf)
    rep = Report.new(ex)

    rep.readInitValues
    rep.generatePerfReport
    rep.saveChangedFile

    ex.closeWorkbook
  end
ensure
  ex.closeExcel
end