Excel のデータを扱うことは今までにも何度もありましたが、私の場合は大抵、テキストファイルに変換してから処理していました。だから AppleScript で Excel を直接操作するスクリプトは書いたことがありません。ところが、ある案件で Excel を起動させ、どうにもそこから直接データを取り出すことが必要となりました。で、休み前に初めて Excelのスクリプトを書きました。
結合セルは面倒だ
HTMLにしろ InDesignにしろ、何かにつけて面倒な記述に table というものがあります。列方向、あるいや行方向に隣のセルが結合されたアレがイヤ。HTMLを手書きで書いた時も、InDesignタグの表を扱った時も、面倒この上ない・・・苦手です。
Excelでレイアウトされたスプレッドシートは、セル同士が隣り合っていて、そして値が同じならセルは結合されています。人間の目で見て、一瞬にして表の構造を把握するには当たり前の処理だと思います。ところが、これがプログラミングで処理するとなると・・・あ〜、面倒ですね〜。データベースの感覚で、同じ値であろうと省略せずにキッチリと入力してあると簡単なんですが。
結合セルを含む表のデータをゲットする
さて、上のテーブルの３行、A,B,C列を選択した上で、AppleScript を使って値をゲットしてみます。
tell application "Microsoft Excel" set selected_value to value of selection end tell
Result
{{"ABC", "DEF", "GHI"}, {123.0, "456", ""}, {"def", "ghi", ""}}
値の構造はある程度予測した通りでした。しかし、このリストからは結合セルの情報は読み取れません。「結合されて見えなくなっているセルの空白」と、単に「値が入力されていないセルの空白」との区別ができません。
結合セルのプロパティはないのか？
当然、結合セルのプロパティがあるに違いありません。そこで用語辞書を調べてみると merge cells がそれに相当するようです。
tell application "Microsoft Excel" set selected_value to merge cells of selection end tell
セルを１つだけ選択して実行すると boolean値で結果がわかります。 「お、楽勝じゃないか！」と喜んでみたのですが、その考えは甘かったようです。value of selection で得たリストの空白部分が何を意味しているのか？ それを調べることが目的なんです。結合された結果、空白なのか？ 意図的に未入力にしたから空白なのか？ それを達成するには、選択されたすべてのセルの merge cells の値を調べなくてはいけません。選択した範囲の every cell の参照が必要です。「選択した範囲」というオブジェクトがないので、個々のセルの参照が必要なんですね。列の数、行の数はわかっているのでアクセスするのは簡単です。
1 global colorValue
2
3 tell application "Microsoft Excel"
4 set rangeValue to get address of selection
5 set colorValue to {227, 247, 241}
6 end tell
7
8 -- get range infomation as bounds data
9 set bounds_cell to get_bounds_cell of me given range:rangeValue
10 -- convert alphabet to ascii number
11 set item 2 of bounds_cell to ASCII number (item 2 of bounds_cell)
12 set item 4 of bounds_cell to ASCII number (item 4 of bounds_cell)
13 -- marking merge cells
14 repeat with row_number from (item 1 of bounds_cell) to (item 3 of bounds_cell)
15 repeat with col from (item 2 of bounds_cell) to (item 4 of bounds_cell)
16 set col_string to ASCII character (col)
17 tell application "Microsoft Excel" to set aCell to range (col_string & row_number)
18 merge_cells_check of me given cell:aCell
19 end repeat
20 end repeat
21
22 -----------------------------------------------------------------------------------------
23 -- marking merge cells
24 on merge_cells_check given cell:aCell
25 tell application "Microsoft Excel"
26 if merge cells of aCell then
27 set color of interior object of aCell to colorValue
28 end if
29 end tell
30 end merge_cells_check
31
32 -----------------------------------------------------------------------------------------
33 -- Getting bounds data as each value; top, left, bottom, right
34 on get_bounds_cell given range:rangeValue
35 set range_array to split of me given stringValue:rangeValue, delimitersChar:":"
36 set left_top to split of me given stringValue:item 1 of range_array, delimitersChar:"$"
37 set right_bottom to split of me given stringValue:item 2 of range_array, delimitersChar:"$"
38 return {item 3 of left_top, item 2 of left_top, item 3 of right_bottom, item 2 of right_bottom}
39 end get_bounds_cell
40
41 ---------------------------------------------------------------------------------------------------------------------------------
42 -- This code return a array that is made from string using delimiters character.
43 on split given stringValue:aText, delimitersChar:deliChar
44 set aText to aText as Unicode text
45 set defaultDelimita to AppleScript's text item delimiters
46 try
47 set AppleScript's text item delimiters to deliChar
48 set array to text items of aText as list
49 set AppleScript's text item delimiters to defaultDelimita
50 on error errMsg number errNum
51 set AppleScript's text item delimiters to defaultDelimita
52 display dialog errMsg & errNum buttons {"Quit"} default button 1
53 error number -128
54 end try
55 return array
56 end split
ちなみに以下のような複雑に結合している table で試してみました。結合セルの部分の背景が着色されます。
これを見ると一見問題ないように見えますが、実は大きな落とし穴がありました。ここでつまずきました。
merge cells の値だけでは、グルーピイングができない!
つまり、例えば E6 は結合セルです。E8 は結合セルではありません。でも結合セルの範囲内です。そして、D8 も結合セルです。さて、ここで value of selection で得たリスト構造を処理していく過程で、E8 がどのセルに属する結合セルの一部なのか？ 判断ができません。D8 と結合しているのでしょうか？ 個々のセルを単独に調べる限り、リストのデータを正しく処理できないようです。
ごめんなさい。私には文章を書く能力が不足しているようなので、うまくこの状態を説明することができません。実際に頭の中でデータのハンドリングを想像すると解ると思うのですが・・・・。
どうやら結合セルの範囲も含め、それらの情報をゲットできるのは merge cells で true が返ってきた時にその範囲を調べるしか方法がないようです。
さらに、一番の問題がここにあります。結合された範囲の参照を get address で得るには UI上で selection 状態にしないと得られませんでした。例えば、"E6" のセルが merge cells だからと言って、その range を引数にして get address で得ようとしても同じ結果が返ってくるだけです。UIで "E6" を選択状態にすれば、その結果、結合セルとして構成されている他のセルも選択され、初めて目的の range が得られる形になります。ただでさえ選択されたセルすべてをチェックしているスクリプトなのに、このうえさらに選択状態を繰り返すなんて・・・。これは避けたい。避けたいけど、他に方法が見つかりません。
Excelの結合セルを含む table を扱うスクリプト
私は普段、Ruby で書かれた Hiki というツールをメモとして利用しています。その中では結合セルも含めて Table を表示することができます。そこで、その Table を表示するための form を Excel から得るためのスクリプトを書きました。
Excelのセルを選択し、Hiki用 tableソースを生成する。（長いので ブラウザー上への表示は省略します）
使い方
- Excel上のCellで、Hikiに記述したい部分を選択する。
- このスクリプトを実行する。
- クリップボードにテキストがコピーされるので、それをHikiにペーストする。
- セル内改行には対応していません。
こんな感じで表示されます。背景にある罫線が表の罫線を見えにくくしていますが、ノートのようなデザインでHikiをレイアウトしているので勘弁してください。
|| ||ABC||>DEF|| ||^123.0||456.0||^^GHI||789.0|| || ||^^JKL|| ||日本||^英国|| || ||あいう|| ||>>>Global|| ||>かきく||Apple||Windows||Unix
Hikiでの記述は上の通りです。空白のセルの部分には全角のスペースが代入されています。Safariで表示した場合、最後のセルを空白セルにするために必要でした。
一時は Perlのモジュールをダウンロードしたりしのですが、自分のマシン以外の環境で間違いなく使えるようにしなくてはいけない案件があり、それを踏まえての検証作業のため、こんな形になってしまいました。
他にもおかしな部分が
それにしても、文字列としてセルの書式は設定しているのに数値としてゲットしてしまう件も問題だ。はじめは結合セルの時だけ文字列としてゲットできないのかと考えましたが、そうでもないようです。いろいろ試してみましたが、どうも数値扱いにされてしまうパターンが読み取れません。課題は山積みです。
]]>
