- Exelには最終行の取得(End(xlUp),キー操作でいうところのcommand+up arrow)がある.
- それに相当する操作がNumbersにはない.
- Scriptobject を利用して最終行を取得できるようにする.
- すなわちオブジェクト指向プログラミングでやる.
- ここでは,"lastRow"はtableの最下セルの行番号,"endRow"はある列でデータを有するセルで最下のセルの行番号とする.
(1) 準備
- 15行,15列のtableを準備する.
- header row, header columnは削除する.
- 行の高さを36pt,列の幅を36ptにする.
- 10行,10列のセルにcell nameをを記入する.
- cell name とは,例えば "A1",
Prepare a table (15 x 15), part of which (10x10) have "cell names" in them
--block1 tell application "Numbers" activate make document tell front document tell front sheet delete every table make table with properties {column count:16, row count:16} tell front table delete row 1 delete column 1 set height of every row to 36 set width of every column to 36 repeat with i from 1 to 10 repeat with j from 1 to 10 set (value of cell j of column i) to (name of cell j of column i) end repeat end repeat end tell end tell end tell end tell -- HERE INSERT CODE (see article below)
(2) ハンドラの利用: うまくいかない
-- 下のやり方だと,エラ―となる.
This code doesn't work
--block2 tell application "Numbers"'s front document tell front sheet's front table F() -- It does't work !! end tell end tell on F() -- Somthing Code To Get "EndRow" end F
(3) スクリプトオブジェクトを利用(その1)
- 同一スクリプト内に最終行を取得するスクリプトオブジェクトを書き,それを利用する場合
- script TEST 内に最終行を取得するハンドラを作る.
- script TEST 内のハンドラは自身のメソッドとして働く.
- ハンドラ f("A")とするとA列の最終行を取得する.
- f("A") と f(1)とは同じ意味.
- (*) の箇所を以下のようにもかけるが冗長.
"set selection range to cell ((x & (lastRow - i)) as text) "
Script object, and Handler
--Block3 tell application "Numbers"'s front document tell front sheet's front table TEST's f("A") --OK. selection rangeがA10になる. -- Test's endRow -- It doesn't work. row of selection range end tell end tell --スクリプトオブジェクト script TEST on f(x) --最終行を取得する操作 tell application "Numbers"'s front document tell front sheet's front table set lastRow to count of every cell of column x set i to 0 repeat while i < lastRow if value of cell (lastRow - i) of column x ≠ missing value then exit repeat else set i to i + 1 end if end repeat set endRow to (lastRow - i) set selection range to cell (lastRow - i) of column x --(*) end tell end tell end f end script
(4) スクリプトオブジェクトを利用(その2)
- プロパティを利用する.
- グローバル変数endRowに局所変数endRowを代入する.endRowはどこからでも取り出せる.
- オブジェクト指向プラグラムを利用して,プログラムの本体のは簡潔に書ける(たった6行)
Script object, Handler, and Property
--block4 tell application "Numbers"'s front document tell front sheet's front table test's f("A") --OK test's endRow_ --OK end tell end tell script test property endRow_ : "" as number on f(x) --最終行を取得する操作 tell application "Numbers"'s front document tell front sheet's front table set lastRow to count of every cell of column x set i to 0 repeat while i < lastRow if value of cell (lastRow - i) of column x ≠ missing value then exit repeat else set i to i + 1 end if end repeat set endRow to (lastRow - i) set selection range to cell (lastRow - i) in column x set endRow_ to endRow end tell end tell end f end script
参考文献 References
(https://zariganitosh.hatenablog.jp/entry/20090210/1234233639:オブジェクト指向AppleScript言語 - ザリガニが見ていた...。) (http://tonbi.jp/AppleScript/Introduction/13/:鳶嶋工房 / AppleScript / 入門 / スクリプト属性を設定しよう) (http://tonbi.jp/AppleScript/Dictionary/Basic/Command/use.html:Basic/基本構文/use)
Translated To English
- Here making a script object to get the "endRow", by using object‐oriented programming.
- Here, "lastRow" mean lower end of a column. "endRow" mean the row of the lowest cell in which has data.
(1) Preparation
- Prepare a table with 15 rows and 15 columns.
- Delete the header row and header column.
- Make row height 36pt and column width 36pt.
- Write "cell name" in cells from 1 to 10 rows and from 1 to 10 column.
- The cell name is, for example, "A1",
(2) It doesn't work just adding a handler at end of the code.
(3) Using script object (part 1)
- Here, writing a script object to get the "endRow", and inserting it at end of code.
- And write a handler in the script object.
- Using the terms in the object-oriented programming, a handler in a script object is equal to "method" in a "class".
- Here, if doing f("A"), getting "endRow" of column A.
- f ("A") is the same meaning as f (1). (One uses "cell address" and the other uses "cell index").
- A bad thing: we can't use a local variable "endRow" outside the script object.
(4) Using script object (part 2)
- Using "property"
- Assign the local variable "endRow" to the global variable "endRow_".
- "endRow_" is a global variable, It is why we can use it everywhere in the script.
- Using the object-oriented manner, the body of the program can be written simply (only 6 rows!!).