元データからExcelのチケットを作成するスクリプト

createTicket.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 setFormat x,y,format
  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 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

$formats = {5=>"@", 16=>"@", 30=>"@", 35=>"@", 36=>"@", 37=>"@", 42=>"@", 50=>"@",
            12=>"#,##0", 13=>"#,##0", 26=>"#,##0", 27=>"#,##0", 28=>"#,##0", 32=>"#,##0", 45=>"#,##0",
            33=>"#,##0.####",}
$adv_cols = {6=>"XTKS", 13=>0, 18=>"", 26=>"", 27=>"", 28=>"", 29=>"", 30=>"", 31=>"", 
  32=>0, 33=>0, 34=>"Advice", 45=>"", 47=>"Agency", 48=>"Gross", 50=>"False",}
$disc_cols = {13=>0, 26=>"", 27=>"", 28=>"", 29=>"", 30=>"", 31=>"", 
  32=>0, 33=>0, 34=>"Discretion", 47=>"Principal", 48=>"Net", 50=>"True",}
$fill_adv_cols = {6=>"XTKS", 18=>"", 26=>"", 27=>"", 28=>"", 29=>"", 30=>"", 31=>"", 
  34=>"Advice", 45=>"", 47=>"Agency", 48=>"Gross", 50=>"False",}
$fill_disc_cols = {34=>"Discretion", 47=>"Principal", 48=>"Net", 50=>"True",}

$parentID = nil
$childID  = nil
$tradeID  = nil

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

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

def getStatus ex, sheetname
  ex.sheet = sheetname

  h = Hash.new
  l = 1
  until ex.empty?(1,l)
    h[l] ||= {0=>ex[1,l].Value, 14=>ex[2,l].Value, 15=>ex[3,l].Value, 16=>ex[4,l].Value, 99=>ex[5,l].Value}
    l = l + 1
  end

  return h
end

def readID ex, sheetname
  ex.sheet = sheetname

  l = 1
  until ex.empty?(1,l)
    case ex[1,l].Value
    when "ParentID"
      $parentID = ex.formula(2,l).to_i
    when "ChildID"
      $childID = ex.formula(2,l).to_i
    when "TradeID"
      $tradeID = ex.formula(2,l).to_i
    end
    l += 1
  end
end

def getLastRow ex, col, row = 1
  l = row 
  until ex.empty?(col, l)
    l += 1
  end
  return l - 1
end

def copyRows(ex, row, count)
  puts "#{row}, #{row+1}, #{row+count}"
  ex.ActiveSheet.Rows("#{row}:#{row}").Copy
  ex.ActiveSheet.Rows("#{row+1}:#{row+count}").Insert
end

def setRowValues(ex, row, cols)
  cols.each do |col, value|
    ex.setFormula(col, row, value)
  end
end

def setID(ex, row, status)
  ex.setFormula(5, row, ex[5,row].Value.to_i.to_s)
  ex.setFormula(49, row, ex[49,row].Value)
  if status[0] == "P"
    id = $parentID
  else
    id = $childID
  end
  ex.setFormula(17, row, "#{status[0]}-#{id}.#{status[99]}")
  ex.setFormula(35, row, $parentID.to_s)
  ex.setFormula(36, row, id.to_s)
  ex.setFormula(37, row, status[99])
end

def setTradeID(ex, row)
  ex.setFormula(31, row, "E-#{$tradeID}.1")
  $tradeID -= 1
end

def setStatus(ex, row, sts)
  sts.each do |r,st|
    diff = r - 1

    rd = row+diff
    $formats.each do |col, format|
      ex.setFormat(col, rd, format)
      if col == 33 
        if ex[col, rd].Value == ex[col, rd].Value.to_i 
          ex.setFormat(col, rd, "#,##0")
        end
      end
    end

    st.each do |col, value|
      ex.setFormula(col, rd, value) if col > 0 and col < 99
    end

    setID(ex, rd, st)

    unless st.value?("Filled") 
      if (st[0] == "P") 
        setRowValues(ex, rd, $adv_cols)
      else
        setRowValues(ex, rd, $disc_cols)
      end
    else
      if (st[0] == "P")
        setRowValues(ex, rd, $fill_adv_cols)
      else
        setTradeID(ex, rd)
        setRowValues(ex, rd, $fill_disc_cols)
      end
    end

  end
  $parentID -= 1
  $childID -= 1
end

exit unless ARGV[0]

begin
  ex = ExcelUtil.new
  ex.openExcel

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

    sts = getStatus(ex, "Status")
    readID(ex, "InitValue")

    ex.sheet = "Report"

    first = 2
    lst = getLastRow(ex, 2, first)

    l = lst
    count = sts.size - 1
    while l > first do
      copyRows(ex, l, count)
      setStatus(ex, l, sts)

      l -= 1
    end

    saveChangedFile ex
    ex.closeWorkbook
  end

ensure
  ex.closeExcel
end