ホーム / 旧ブログ /

Calc(33)スプレッドシート関数をマクロで使う

公開日: 2017年10月29日 更新日: 2019年05月11日

旧ブログ

t f B! P L
Calcのスプレッドシート関数をマクロで使う方法をやります。FunctionAccessサービスをインスタンス化してそのメソッドcallFunction()の引数に関数名と引数を渡せすだけです。

前の関連記事：Calc(32)sheet["A1"]とsheet[0, 0]とsheet[0:1, 0]の違い


スプレッドシート関数をマクロで使う

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
from com.sun.star.lang import Locale  # Struct
from com.sun.star.sheet import CellFlags  # 定数
def macro():
    doc = XSCRIPTCONTEXT.getDocument()  # ドキュメントを取得。
    createFormatKey = formatkeyCreator(doc)
    sheets = doc.getSheets()  # ドキュメントのシートコレクションを取得。。
    sheet = sheets[0]  # シートコレクションのインデックス0のシートを取得。
    sheet.clearContents(CellFlags.VALUE+CellFlags.DATETIME+CellFlags.STRING+CellFlags.ANNOTATION+CellFlags.FORMULA+CellFlags.HARDATTR+CellFlags.STYLES)  # セルの内容を削除。
    ctx = XSCRIPTCONTEXT.getComponentContext()  # コンポーネントコンテクストの取得。
    smgr = ctx.getServiceManager()  # サービスマネージャーの取得。 
    functionaccess = smgr.createInstanceWithContext("com.sun.star.sheet.FunctionAccess", ctx)
    headers = "Sheet Function", "Return Type", "Return Value", "Format or Formula", "Formatted Value"
    for i, header in enumerate(headers):
        sheet[0, i].setString(header)
    today = functionaccess.callFunction("TODAY", ())  # 引数のないスプレッドシート関数。
    txts = "TODAY()", type(today).__name__, str(today), "YYYY-MM-DD"
    for i, t in enumerate(txts):
        sheet[1, i].setString(t)
    cell = castToXCellRange(sheet[1, i+1])  # 次にcallFunction()の引数にいれるために、com.sun.star.table.XCellRange型でセルを取得する。
    cell.setValue(today)
    cell.setPropertyValue("NumberFormat", createFormatKey(t))  # セルの書式を設定。
    year = functionaccess.callFunction("YEAR", (cell,))  # 引数のあるスプレッドシート関数。タプルの入れ子で返ってくる。
    txts = 'year = YEAR("C2")', type(year).__name__, str(year), "year[0][0]"
    for i, t in enumerate(txts):
        sheet[2, i].setString(t)
    sheet[2, i+1].setValue(year[0][0])
    now = functionaccess.callFunction("NOW", ())  # 引数のない関数の例。
    txts = "NOW()", type(now).__name__, str(now), "YYYY/M/D H:MM:SS"
    for i, t in enumerate(txts):
        sheet[3, i].setString(t)
    sheet[3, i+1].setValue(now)
    sheet[3, i+1].setPropertyValue("NumberFormat", createFormatKey(t))  # セルの書式を設定。
    sheet["A:E"].getColumns().setPropertyValue("OptimalWidth", True)  # 列幅を最適化する。
def formatkeyCreator(doc):  # ドキュメントを引数にする。
    def createFormatKey(formatstring):  # formatstringの書式はLocalによって異なる。
        numberformats = doc.getNumberFormats()  # ドキュメントのフォーマット一覧を取得。デフォルトのフォーマット一覧はCalcの書式→セル→数値でみれる。
        locale = Locale(Language="ja", Country="JP")  # フォーマット一覧をくくる言語と国を設定。インストールしていないUIの言語でもよい。。
        formatkey = numberformats.queryKey(formatstring, locale, True)  # formatstringが既存のフォーマット一覧にあるか調べて取得。第3引数のブーリアンは意味はないはず。
        if formatkey == -1:  # デフォルトのフォーマットにformatstringがないとき。
            formatkey = numberformats.addNew(formatstring, locale)  # フォーマット一覧に追加する。保存はドキュメントごと。
        return formatkey
    return createFormatKey
def castToXCellRange(cell):  # セルをcom.sun.star.table.XCell型からcom.sun.star.table.XCellRange型に変換する。
    if cell.supportsService("com.sun.star.sheet.SheetCell"):  # 引数がセルのとき
        absolutename = cell.getPropertyValue("AbsoluteName")  # AbsoluteNameを取得。
        stringaddress = absolutename.split(".")[-1].replace("$", "")  # シート名を削除後$も削除して、セルの文字列アドレスを取得。
        sheet = cell.getSpreadsheet()  # セルのシートを取得。
        return sheet[stringaddress]  # com.sun.star.table.XCellRange型のセルを返す。
    else:
        raise RuntimeError("The argument of castToXCellRange() must be a cell.")
g_exportedScripts = macro, #マクロセレクターに限定表示させる関数をタプルで指定。
11行目でFunctionAccessサービスをインスタンス化して、15、22、27行目でそのcallFunction()メソッドを使っています


Calcでこのマクロを実行するとスプレッドシート関数のTODAY()、YEAR()、NOW()の戻り値をC列に出力します。

TODAY()とNOW()は日付-時間シリアル値で数値として値が返ってきます(Calc(31)セルへ日付を入力する方法参照)。

それに対してYEAR()は日付-時間シリアル値を引数にとって年を返しますが、それが数値ではなく、その数値をタプルのタプルにしたものが返ってきました。
(2017.10.29追記。タプルのタプルで返ってくるのはセルを渡した時だけで、引数に日付-時間シリアル値を渡した時はfloatで返ってきました。Calc(34)セルに入力された日付を取得する方法参照。)

カテゴリ別の関数 - LibreOffice Helpにスプレッドシート関数のヘルプがありますが、戻り値の型の解説は見つけられませんでした。

callFunction()メソッドの引数にはCalc(32)sheet["A1"]とsheet[0, 0]とsheet[0:1, 0]の違いでやったように、セル範囲が渡せます。

上のマクロではYEAR()の引数にC2セルをセル範囲として渡しています。

FunctionAccessのサービスとインターフェイス一覧


1
2
3
4
5
6
def macro():
    ctx = XSCRIPTCONTEXT.getComponentContext()  # コンポーネントコンテクストの取得。
    smgr = ctx.getServiceManager()  # サービスマネージャーの取得。
    tcu = smgr.createInstanceWithContext("pq.Tcu", ctx)  # サービス名か実装名でインスタンス化。
    functionaccess = smgr.createInstanceWithContext("com.sun.star.sheet.FunctionAccess", ctx)
    tcu.wtree(functionaccess)


└─.sheet.FunctionAccess
     │   boolean  IsArrayFunction
     ├─.sheet.XFunctionAccess
     │      any  callFunction( [in] string aName,
     │                         [in]  [any] aArguments
     │              ) raises ( .lang.IllegalArgumentException,
     │                         .container.NoSuchElementException)
     └─.sheet.SpreadsheetDocumentSettings
        │                      boolean  CalcAsShown
        │                 .lang.Locale  CharLocale
        │                 .lang.Locale  CharLocaleAsian
        │                 .lang.Locale  CharLocaleComplex
        │                        short  DefaultTabStop
        │   .i18n.XForbiddenCharacters  ForbiddenCharacters
        │                      boolean  HasDrawPages
        │                      boolean  IgnoreCase
        │                      boolean  IsAdjustHeightEnabled
        │                      boolean  IsExecuteLinkEnabled
        │                      boolean  IsIterationEnabled
        │                      boolean  IsLoaded
        │                      boolean  IsRecordChangesProtected
        │                      boolean  IsUndoEnabled
        │                         long  IterationCount
        │                       double  IterationEpsilon
        │                      boolean  LookUpLabels
        │                      boolean  MatchWholeCell
        │                   .util.Date  NullDate
        │                      boolean  RecordChanges
        │                 .awt.XDevice  ReferenceDevice
        │                      boolean  RegularExpressions
        │                      boolean  SpellOnline
        │                        short  StandardDecimals
        │                      boolean  Wildcards
        └─.beans.XPropertySet
                                 void  addPropertyChangeListener( [in]                         string aPropertyName,
                                                                  [in] .beans.XPropertyChangeListener xListener
                                                       ) raises ( .lang.WrappedTargetException,
                                                                  .beans.UnknownPropertyException)
                                 void  addVetoableChangeListener( [in]                         string PropertyName,
                                                                  [in] .beans.XVetoableChangeListener aListener
                                                       ) raises ( .lang.WrappedTargetException,
                                                                  .beans.UnknownPropertyException)
              .beans.XPropertySetInfo  getPropertySetInfo()
                                  any  getPropertyValue( [in] string PropertyName
                                              ) raises ( .lang.WrappedTargetException,
                                                         .beans.UnknownPropertyException)
                                 void  removePropertyChangeListener( [in]                         string aPropertyName,
                                                                     [in] .beans.XPropertyChangeListener aListener
                                                          ) raises ( .lang.WrappedTargetException,
                                                                     .beans.UnknownPropertyException)
                                 void  removeVetoableChangeListener( [in]                         string PropertyName,
                                                                     [in] .beans.XVetoableChangeListener aListener
                                                          ) raises ( .lang.WrappedTargetException,
                                                                     .beans.UnknownPropertyException)
                                 void  setPropertyValue( [in] string aPropertyName,
                                                         [in]    any aValue
                                              ) raises ( .lang.WrappedTargetException,
                                                         .lang.IllegalArgumentException,
                                                         .beans.PropertyVetoException,
                                                         .beans.UnknownPropertyException)

参考にしたサイト


カテゴリ別の関数 - LibreOffice Help
LibreOffice Calc のスプレッドシート関数。

OOoBasic/Generic/function - ...?
マクロでスプレッドシート関数を使うBasicの例。

次の関連記事:Calc(34)セルに入力された日付を取得する方法

ブログ検索 by Blogger

Translate

全記事

Created by Calendar Gadget

QooQ