My_Play_Lists ~BlackCatWhiteTail’s blog~

Talk About My Favorite Songs

applescript+numbers > 最終行の取得(2)

  • 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) "
  • 難点は,スクリプトオブジェクト内で準備した変数 "endRow" を,スクリプトオブジェクト外では利用できない点.



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)



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!!).

 このウェブサイトについて

 練習用のサイトです.

 自分の好きな曲について感想を述べます.

 また,世に役に立つかもと考えたことも書きます.

 

TOP 1 To 5

Title Artist Album
ロックバンド 中村中 私を抱いて下さい
犬の生活 SEX MACHINEGUNS SEX MACHINEGUNS
Megalovania Toby Fox UNDERTALE Soundtrack
Bokura No Fly Away Base Ball Bear Bokura No Fly Away - single
人魚の檻 陰陽座 迦陵頻伽(Ka Ryo Bin Ga