Hatena::ブログ(Diary)

@jitteの日記 RSSフィード

2006-08-21

[][] イベントインターフェースを取得する


このようなイベントの インタフェース名を調べるには一種のノウハウが必要です。

イベントのインタフェース名を調べる方法について ruby-list:39137 で、arton さんが書かれた方法が非常に参考になります。簡単な方法は助田さんが作成された Simple OLE Browser や Python Object Browser を用いることでしょう。

Rubyist Magazine - Win32OLE 活用法 【第 5 回】 Outlook

MSDNを調べろって言われても慣れてないとなかなか正解にたどり着かないのがMSDN。オンラインだとストレスがたまりやすくてあきらめることが多いのよね。ということで、作ってみた。

require 'win32ole'

tl = 'Microsoft Excel 9.0 Object Library'

ole_classes = WIN32OLE_TYPE.ole_classes(tl)
ole_classes.each do |c|
  begin
    methods = WIN32OLE_TYPE.new(tl, c.to_s).ole_methods
    methods.each do |m|
      p [c, m.event_interface, m] if m.event?
    end
  rescue
  end
end

tlのところは各自環境に合わせて適当に。

追記:http://msdn.microsoft.com/library/en-us/dno2kta/html/offaut.asp

以下、実行結果

[QueryTable, "_QueryTable", QueryInterface]
[QueryTable, "_QueryTable", AddRef]
[QueryTable, "_QueryTable", Release]
[QueryTable, "_QueryTable", GetTypeInfoCount]
[QueryTable, "_QueryTable", GetTypeInfo]
[QueryTable, "_QueryTable", GetIDsOfNames]
[QueryTable, "_QueryTable", Invoke]
[QueryTable, "RefreshEvents", QueryInterface]
[QueryTable, "RefreshEvents", AddRef]
[QueryTable, "RefreshEvents", Release]
[QueryTable, "RefreshEvents", GetTypeInfoCount]
[QueryTable, "RefreshEvents", GetTypeInfo]
[QueryTable, "RefreshEvents", GetIDsOfNames]
[QueryTable, "RefreshEvents", Invoke]
[QueryTable, "RefreshEvents", BeforeRefresh]
[QueryTable, "RefreshEvents", AfterRefresh]
[Application, "_Application", QueryInterface]
[Application, "_Application", AddRef]
[Application, "_Application", Release]
[Application, "_Application", GetTypeInfoCount]
[Application, "_Application", GetTypeInfo]
[Application, "_Application", GetIDsOfNames]
[Application, "_Application", Invoke]
[Application, "AppEvents", QueryInterface]
[Application, "AppEvents", AddRef]
[Application, "AppEvents", Release]
[Application, "AppEvents", GetTypeInfoCount]
[Application, "AppEvents", GetTypeInfo]
[Application, "AppEvents", GetIDsOfNames]
[Application, "AppEvents", Invoke]
[Application, "AppEvents", NewWorkbook]
[Application, "AppEvents", SheetSelectionChange]
[Application, "AppEvents", SheetBeforeDoubleClick]
[Application, "AppEvents", SheetBeforeRightClick]
[Application, "AppEvents", SheetActivate]
[Application, "AppEvents", SheetDeactivate]
[Application, "AppEvents", SheetCalculate]
[Application, "AppEvents", SheetChange]
[Application, "AppEvents", WorkbookOpen]
[Application, "AppEvents", WorkbookActivate]
[Application, "AppEvents", WorkbookDeactivate]
[Application, "AppEvents", WorkbookBeforeClose]
[Application, "AppEvents", WorkbookBeforeSave]
[Application, "AppEvents", WorkbookBeforePrint]
[Application, "AppEvents", WorkbookNewSheet]
[Application, "AppEvents", WorkbookAddinInstall]
[Application, "AppEvents", WorkbookAddinUninstall]
[Application, "AppEvents", WindowResize]
[Application, "AppEvents", WindowActivate]
[Application, "AppEvents", WindowDeactivate]
[Application, "AppEvents", SheetFollowHyperlink]
[Chart, "_Chart", QueryInterface]
[Chart, "_Chart", AddRef]
[Chart, "_Chart", Release]
[Chart, "_Chart", GetTypeInfoCount]
[Chart, "_Chart", GetTypeInfo]
[Chart, "_Chart", GetIDsOfNames]
[Chart, "_Chart", Invoke]
[Chart, "ChartEvents", QueryInterface]
[Chart, "ChartEvents", AddRef]
[Chart, "ChartEvents", Release]
[Chart, "ChartEvents", GetTypeInfoCount]
[Chart, "ChartEvents", GetTypeInfo]
[Chart, "ChartEvents", GetIDsOfNames]
[Chart, "ChartEvents", Invoke]
[Chart, "ChartEvents", Activate]
[Chart, "ChartEvents", Deactivate]
[Chart, "ChartEvents", Resize]
[Chart, "ChartEvents", MouseDown]
[Chart, "ChartEvents", MouseUp]
[Chart, "ChartEvents", MouseMove]
[Chart, "ChartEvents", BeforeRightClick]
[Chart, "ChartEvents", DragPlot]
[Chart, "ChartEvents", DragOver]
[Chart, "ChartEvents", BeforeDoubleClick]
[Chart, "ChartEvents", Select]
[Chart, "ChartEvents", SeriesChange]
[Chart, "ChartEvents", Calculate]
[Worksheet, "_Worksheet", QueryInterface]
[Worksheet, "_Worksheet", AddRef]
[Worksheet, "_Worksheet", Release]
[Worksheet, "_Worksheet", GetTypeInfoCount]
[Worksheet, "_Worksheet", GetTypeInfo]
[Worksheet, "_Worksheet", GetIDsOfNames]
[Worksheet, "_Worksheet", Invoke]
[Worksheet, "_Worksheet", Activate]
[Worksheet, "DocEvents", QueryInterface]
[Worksheet, "DocEvents", AddRef]
[Worksheet, "DocEvents", Release]
[Worksheet, "DocEvents", GetTypeInfoCount]
[Worksheet, "DocEvents", GetTypeInfo]
[Worksheet, "DocEvents", GetIDsOfNames]
[Worksheet, "DocEvents", Invoke]
[Worksheet, "DocEvents", SelectionChange]
[Worksheet, "DocEvents", BeforeDoubleClick]
[Worksheet, "DocEvents", BeforeRightClick]
[Worksheet, "DocEvents", Activate]
[Worksheet, "DocEvents", Deactivate]
[Worksheet, "DocEvents", Calculate]
[Worksheet, "DocEvents", Change]
[Worksheet, "DocEvents", FollowHyperlink]
[Workbook, "_Workbook", QueryInterface]
[Workbook, "_Workbook", AddRef]
[Workbook, "_Workbook", Release]
[Workbook, "_Workbook", GetTypeInfoCount]
[Workbook, "_Workbook", GetTypeInfo]
[Workbook, "_Workbook", GetIDsOfNames]
[Workbook, "_Workbook", Invoke]
[Workbook, "WorkbookEvents", QueryInterface]
[Workbook, "WorkbookEvents", AddRef]
[Workbook, "WorkbookEvents", Release]
[Workbook, "WorkbookEvents", GetTypeInfoCount]
[Workbook, "WorkbookEvents", GetTypeInfo]
[Workbook, "WorkbookEvents", GetIDsOfNames]
[Workbook, "WorkbookEvents", Invoke]
[Workbook, "WorkbookEvents", Open]
[Workbook, "WorkbookEvents", Activate]
[Workbook, "WorkbookEvents", Deactivate]
[Workbook, "WorkbookEvents", BeforeClose]
[Workbook, "WorkbookEvents", BeforeSave]
[Workbook, "WorkbookEvents", BeforePrint]
[Workbook, "WorkbookEvents", NewSheet]
[Workbook, "WorkbookEvents", AddinInstall]
[Workbook, "WorkbookEvents", AddinUninstall]
[Workbook, "WorkbookEvents", WindowResize]
[Workbook, "WorkbookEvents", WindowActivate]
[Workbook, "WorkbookEvents", WindowDeactivate]
[Workbook, "WorkbookEvents", SheetSelectionChange]
[Workbook, "WorkbookEvents", SheetBeforeDoubleClick]
[Workbook, "WorkbookEvents", SheetBeforeRightClick]
[Workbook, "WorkbookEvents", SheetActivate]
[Workbook, "WorkbookEvents", SheetDeactivate]
[Workbook, "WorkbookEvents", SheetCalculate]
[Workbook, "WorkbookEvents", SheetChange]
[Workbook, "WorkbookEvents", SheetFollowHyperlink]
[OLEObject, "_OLEObject", QueryInterface]
[OLEObject, "_OLEObject", AddRef]
[OLEObject, "_OLEObject", Release]
[OLEObject, "_OLEObject", GetTypeInfoCount]
[OLEObject, "_OLEObject", GetTypeInfo]
[OLEObject, "_OLEObject", GetIDsOfNames]
[OLEObject, "_OLEObject", Invoke]
[OLEObject, "OLEObjectEvents", QueryInterface]
[OLEObject, "OLEObjectEvents", AddRef]
[OLEObject, "OLEObjectEvents", Release]
[OLEObject, "OLEObjectEvents", GetTypeInfoCount]
[OLEObject, "OLEObjectEvents", GetTypeInfo]
[OLEObject, "OLEObjectEvents", GetIDsOfNames]
[OLEObject, "OLEObjectEvents", Invoke]
[OLEObject, "OLEObjectEvents", GotFocus]
[OLEObject, "OLEObjectEvents", LostFocus]

2006-08-20

[][] WIN32OLE_EVENTメモ

WIN32OLE_EVENTを使ってExcelのイベントにコールバックを登録する例を作ってみた。

require 'win32ole'

app = WIN32OLE.new('Excel.Application')
app.visible = true
wkb = app.workbooks.add

i = 100
WIN32OLE_EVENT.new(wkb, 'WorkbookEvents').on_event() do |*e|
  i = i - 1
  p(e.map do |a| WIN32OLE === a ? a.ole_obj_help : a end)
end

WIN32OLE_EVENT.message_loop while i > 0

疑問:コールバックを削除する方法がわからなかった。


[][] WIN32OLE_TYPEメモ

WIN32OLE_TYPE.typelibs

タイプライブラリ一覧を文字列の配列で取得。

puts WIN32OLE_TYPE.typelibs.grep(/PowerPoint/)
# =>
Microsoft PowerPoint 9.0 Object Library
Microsoft PowerPoint 12.0 Object Library

WIN32OLE_TYPE.ole_classes(typelib)

指定したtypelibのクラス一覧を配列で取得。

tl = WIN32OLE_TYPE.typelibs.grep(/PowerPoint/)[1]
puts WIN32OLE_TYPE.ole_classes(tl).map { |c| c.to_s }.grep(/Shape/)
# =>
Shapes
Shape
ShapeRange
GroupShapes
ShapeNodes
ShapeNode
PpShapeFormat
CanvasShapes

WIN32OLE_TYPE#variables

OLEクラスのインスタンスに定義された変数配列を取得する。実際には、定数一覧が得られるようだ。

WIN32OLE_TYPE.ole_classes(tl).map { |c| c.to_s }.grep(/Slide/).each do |c|
  va = WIN32OLE_TYPE.new(tl, c.to_s).variables
  pp [c, va] unless va.empty?
end

2006-08-10

[][] WIN32OLEのメソッドをirbで補完する

なんか動いたみたいだけどこんな簡単でいいのかな。

irb/ole.rb

require 'win32ole'
require 'irb/completion'

class WIN32OLE
  alias :orig_methods :methods
  def methods(flag = true)
    @ole_methods ||= ole_methods.map { |m| m.to_s.downcase }
    @ole_methods | orig_methods(flag)
  end
end

動作例:

D:\jitte\Ruby> irb -r irb/ole
irb(main):001:0> app = WIN32OLE.new('Excel.Application')
=> #<WIN32OLE:0x29b88e8>
irb(main):002:0> app.work[TAB][TAB]
app.workbooks          app.worksheetfunction  app.worksheets

追記:

(ole_methods rescue [])あたりにしとくと吉かも。

2006-08-04

jitte2006-08-04

[][][] 機種依存文字をチェックする

微妙に需要があるみたいだったので機種依存文字があるセルをチェックするツールを作ってみた。

  • 空白、Latin文字、全角英数記号、JIS第一水準、JIS第二水準はOKとする
  • 機種依存文字がみつかったセルの背景色を変える
  • 左端に絞り込み用のフラグを書き込み、オートフィルタをかける

使い方:exeにしてデスクトップ等に置き、csvやExcelファイルをドロップすればOK。


checkjis.rb

#! /usr/bin/ruby -Ks

require 'excel_lib'
require 'rubygems'
require 'win32/clipboard'

$KCODE = 's'

class Excel
  def initialize(visible = true)
    @raw_object = connector('Excel.Application')
    @raw_object.visible = visible
    if block_given?
      yield self
    end
  end

  def connector(app_name)
    begin
      raw_object = WIN32OLE.connect(app_name)
      @connected = true
    rescue
      raw_object = WIN32OLE.new(app_name)
    end
    WIN32OLE.my_const_load(raw_object, self.class)
    raw_object
  end

  class Worksheet
    def used_range
      Range.new(@raw_object.usedrange)
    end
  end

  class Range
    def copy_value
      self.copy
      value = Win32::Clipboard.data
      Win32::Clipboard.empty # 内容を消しておく
      value.split("\r\n").map do |s| s.split("\t") end
    end

    def paste_value(aa)
      Win32::Clipboard.set_data((aa.map do |a| a.join("\t") end).join("\r\n"))
      self.worksheet.paste('Destination' => @raw_object)
      Win32::Clipboard.empty # 内容を消しておく
    end
  end
end

class Excel
  class CheckJIS
    JIS_1_2 = %r(\A[
      \x20-\x7E  # latin
      \xA1-\xDF  # kana
       -╂      # non kanji
      亜-腕      # jis1
      弌-滌漾-熙 # jis2
    ]*\Z)xo

    def initialize(file)
      return unless file && File.exist?(file)
      @xls = Excel.new
      @wkb = @xls.open_book(file)
      check()
      autofilter()
    end

    def check
      @sht = @wkb.sheets(1)
      a = @sht.used_range.copy_value
      b = a.map { |r| r.map { |e| jis_1_2?(e) } }
      @sht.range('A:A').insert
      b.each_with_index do |r, y|
        next if r.all?
        @sht.cells(y + 1, 1).value = 1
        r.each_with_index do |e, x|
          @sht.cells(y + 1, x + 2).interior.colorIndex = 6 unless e
        end
      end
    end

    def autofilter
      @sht.range('A1').autofilter('Field' => 1, 'Criteria1' => '1')
    end
    
    def jis_1_2?(s)
      JIS_1_2.match(s) ? true : false
    end
  end
end

if __FILE__ == $0
  Excel::CheckJIS.new(ARGV[0])
end
  • 例によってexcel_lib使用。自作便利関数をコピペ。
  • win32/clipboardはgemになっていたのでgem install win32-clipboard
  • rubyscript2exeだと正規表現がエラーになるのでExerbを使った

rakefile.rb

debug = ''
script = 'checkjis.rb'
argv = 'sample.xls'

task :default => [:run]

task :run do
  ruby %(#{debug} #{script} #{argv})
end

task :debug do
  debug = '-d -r debug'
end

task :exe do
  ruby %(-rexerb/mkexy #{script} #{argv})
  sh %(exerb -c gui checkjis.exy)
end

exeを作るときはrake exeで。

2006-06-25

[][][] もんたマインドポイント(2)

やっぱり[]が残っていると格好悪いので、シェイプで黒塗りすることにした。

f:id:jitte:20060625011343g:image



mmpoint.rb

以下、変更分。動作確認はPowerPoint 2000のみ。残念ながら2007βで動かなくなってしまった。

MsoShapeRectangle = 1

class PowerPoint < Excel_Wrapper
  def mask(sl, tr, pos, len)
    t = tr.characters(pos, len)
    sh = sl.shapes.addshape(MsoShapeRectangle, t.boundLeft, t.boundTop, t.boundWidth, t.boundHeight)
    sh.fill.foreColor.schemeColor = PowerPoint::PpForeground
  end

  def monta_mask(sl, tr, masks, shown)
    cur = 0
    1.upto(masks) do |i|
      p1 = tr.find('[', cur).start
      tr.characters(p1, 1).delete
      p2 = tr.find(']', p1).start
      tr.characters(p2, 1).delete
      mask(sl, tr, p1, p2 - p1) if shown < i
      cur = p2
    end
  end

  def monta_method(pre, text)
    masks = text.scan(/\[[^\]]*\]/).size
    0.upto(masks) do |shown|
      sl, tb, tf, tr = takahashi_method(pre, text)
      monta_mask(sl, tr, masks, shown)
    end
  end
end