ホーム / 旧ブログ /

Calc(24)セルカーサーでセル範囲を変更する

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

旧ブログ

t f B! P L
セルカーサーを使ってみました。セルのアドレス表示にはCalc(23)セルやセル範囲のアドレスの取得方法のgetRangeAddressesAsString()関数を使っています。

前の関連記事：Calc(23)セルやセル範囲のアドレスの取得方法


gotoOffset()でセル範囲を大きさを保ったままずらす

1
2
3
4
5
6
7
8
9
10
11
12
def macro():
    doc = XSCRIPTCONTEXT.getDocument()  # ドキュメントを取得。
    sheets = doc.getSheets()  # シートコレクション。
    sheet = sheets[0]  # 最初のシート。
    sheet.clearContents(511)  # シートのセルの内容をすべてを削除。
    cursor = sheet.createCursorByRange(sheet["D4:F5"])  # セル範囲を指定してセルカーサーを取得。
    cursor.setPropertyValue("CellBackColor", 0x8080FF)  # セルカーサーの範囲に紫色をつける。
    sheet[0, 0].setString("Initial range: {}".format(getRangeAddressesAsString(cursor)))
    cursor.gotoOffset(*(-2, -1)[::-1])  # セル範囲を相対的に移動させる。
    cursor.setPropertyValue("CellBackColor", 0xFFFF80)  # セルカーサーの範囲に黄色をつける。
    sheet[1, 0].setString("gotoOffset(*(-2, -1)[::-1]): {}".format(getRangeAddressesAsString(cursor)))
    sheet[:, 0].getColumns().setPropertyValue("OptimalWidth", True)  # 列幅を最適化する
D5:F4のセル範囲でセルカーサーを作成して、それを2行上に1列左に移動させています。


黄色の範囲がgotoOffset(*(-2, -1)[::-1])の移動先です。

gotoOffset()メソッドの引数は(列, 行)ですが、Calc(3)セルへのPythonのシーケンス型アクセス法にそろえてわざわざ*(行, 列)[::-1]と入力しています。


シート外にはみ出るように移動させようとしたこの場合は、C1:E1というふうにはみ出るのではなく全く移動しませんでした。

gotoEnd()でセルカーサーのセル範囲の右下のセルにセル範囲を変更する


セルカーサーのセル範囲の中を移動するものだと思っていたのですが、正確には「移動する」のではなく、セルカーサーの「セル範囲を右下のセルに変更する」ものでした。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
def macro():
    doc = XSCRIPTCONTEXT.getDocument()  # ドキュメントを取得。
    sheets = doc.getSheets()  # シートコレクション。
    sheet = sheets[0]  # 最初のシート。
    sheet.clearContents(511)  # シートのすべてを削除。
    cursor = sheet.createCursorByRange(sheet["D4:F5"])  # セル範囲を指定してセルカーサーを取得。
    cursor.setPropertyValue("CellBackColor", 0x8080FF)  # セルカーサーの範囲に紫色をつける。
    sheet[0, 0].setString("Initial range: {}".format(getRangeAddressesAsString(cursor)))
    cursor.gotoOffset(*(-2, -1)[::-1])  # セル範囲を相対的に移動させる。
    cursor.setPropertyValue("CellBackColor", 0xFFFF80)  # セルカーサーの範囲に黄色をつける。
    sheet[1, 0].setString("gotoOffset(*(-2, -1)[::-1]): {}".format(getRangeAddressesAsString(cursor)))
    cursor.gotoEnd()  # セルカーサーの範囲の右下のセルをセル範囲にする。
    sheet[3, 0].setString("gotoEnd(): {}".format(getRangeAddressesAsString(cursor)))
    sheet[:, 0].getColumns().setPropertyValue("OptimalWidth", True)  # 列幅を最適化する。
D4:F5からgotoOffset()でC2:E3にセル範囲を変更したセルカーサーでgotoEnd()とするとC2:E3の右下のE3セルにカーサーのセル範囲が変更になります。


セル範囲が変更になってしまうので、元のセル範囲の情報は失われてしまいます。

gotoEnd()に代わってgotoNext()にするとD2に、gotoPrevious()にするとB2にセル範囲が変更になりました。

しかしgotoStart()にするとC2になると思ったら、gotoEnd()と同じE3に変更になりました(バグ?)。


最初の範囲をC4:E5にすると、gotoEnd()でD3に行くと思ったら、なぜか黄色の範囲外のA2に行ってしまいました(バグ?)。

セル範囲の移動はインデックスの操作でもできるので、その目的ではちょっと挙動が読めないセルカーサーを使うことはなさそうです。

gotoEndOfUsedArea(False)でシート上の使用範囲の最下行と最右列のアドレスをもつセルにセル範囲を変更する

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
def macro():
    doc = XSCRIPTCONTEXT.getDocument()  # ドキュメントを取得。
    sheets = doc.getSheets()  # シートコレクション。
    sheet = sheets[0]  # 最初のシート。
    sheet.clearContents(511)  # シートのセルの内容をすべてを削除。
    cursor = sheet.createCursorByRange(sheet["D4:F5"])  # セル範囲を指定してセルカーサーを取得。-2,-1オフセット後gotoEndとするとA2になる。
    cursor.setPropertyValue("CellBackColor", 0x8080FF)  # セルカーサーの範囲に紫色をつける。
    sheet[0, 0].setString("Initial range: {}".format(getRangeAddressesAsString(cursor)))
    cursor.gotoOffset(*(-2, -1)[::-1])  # セル範囲を相対的に移動させる。
    cursor.setPropertyValue("CellBackColor", 0xFFFF80)  # セルカーサーの範囲に黄色をつける。
    sheet[1, 0].setString("gotoOffset(*(-2, -1)[::-1]): {}".format(getRangeAddressesAsString(cursor)))
    cursor.gotoEnd()  # セルカーサーの範囲の右下のセルをセル範囲にする。
    sheet[3, 0].setString("gotoEnd(): {}".format(getRangeAddressesAsString(cursor)))
    cursor.gotoStartOfUsedArea(False)  # 使用範囲の左上のセルにセル範囲を変更する。
    sheet[5, 0].setString("gotoStartOfUsedArea(False): {}".format(getRangeAddressesAsString(cursor)))
    sheet[6, 2].setString("C Last Row")  # C7に文字列を入れる。
    sheet[8, 3].setString("D Last Row")  # D9に文字列を入れる。
    cursor.gotoEndOfUsedArea(False)  # 使用範囲の右下のセルにセル範囲を変更する。
    sheet[7, 0].setString("gotoEndOfUsedArea(False): {}".format(getRangeAddressesAsString(cursor)))
    sheet[:, 0].getColumns().setPropertyValue("OptimalWidth", True)  # 列幅を最適化する。
使用範囲の最下行はD9のセル、最右列はF5なので、gotoEndOfUsedArea(False)メソッドでセル範囲がF9になります。



データだけでなく背景色の設定でもセルを使用していることになります。

gotoEndOfUsedArea(True)とするとその時のセル範囲がF9まで拡大されることになります。

各行や各列のデータの入っている最後のセルを求める(いまいちな方法)


これもセルカーサーを使わなくても取得できますが、セルカーサーのgotoEndOfUsedArea()メソッドを使った方が計算量が少なくて済みます(と思いましたが、行や列全体に背景色を設定していると計算量が膨大になります)。

gotoEndOfUsedArea(False)はシート全体でしか右下のセルを求められないので、各列については、使用範囲の右下のセルを最大値として、そこから上に遡ってデータの入っているセルを求めます。
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
def macro():
    doc = XSCRIPTCONTEXT.getDocument()  # ドキュメントを取得。
    sheets = doc.getSheets()  # シートコレクション。
    sheet = sheets[0]  # 最初のシート。
    sheet.clearContents(511)  # シートのセルの内容をすべてを削除。
    cursor = sheet.createCursorByRange(sheet["D4:F5"])  # セル範囲を指定してセルカーサーを取得。-2,-1オフセット後gotoEndとするとA2になる。
    cursor.setPropertyValue("CellBackColor", 0x8080FF)  # セルカーサーの範囲に紫色をつける。
    sheet[0, 0].setString("Initial range: {}".format(getRangeAddressesAsString(cursor)))
    cursor.gotoOffset(*(-2, -1)[::-1])  # セル範囲を相対的に移動させる。
    cursor.setPropertyValue("CellBackColor", 0xFFFF80)  # セルカーサーの範囲に黄色をつける。
    sheet[1, 0].setString("gotoOffset(*(-2, -1)[::-1]): {}".format(getRangeAddressesAsString(cursor)))
    cursor.gotoEnd()  # セルカーサーの範囲の右下のセルをセル範囲にする。
    sheet[3, 0].setString("gotoEnd(): {}".format(getRangeAddressesAsString(cursor)))
    cursor.gotoStartOfUsedArea(False)  # 使用範囲の左上のセルにセル範囲を変更する。
    sheet[5, 0].setString("gotoStartOfUsedArea(False): {}".format(getRangeAddressesAsString(cursor)))
    sheet[6, 2].setString("C Last Row")  # C7に文字列を入れる。
    sheet[8, 3].setString("D Last Row")  # D9に文字列を入れる。
    cursor.gotoEndOfUsedArea(False)  # 使用範囲の右下のセルにセル範囲を変更する。
    sheet[7, 0].setString("gotoEndOfUsedArea(False): {}".format(getRangeAddressesAsString(cursor))) 
    # C列の最終使用行を求める。
#  cursor = sheet.createCursor()  # セルカーサーの取得。
#  cursor.gotoEndOfUsedArea(False)  # シート上の使用範囲の右下のセルを取得。
    usedrowindex = cursor.getRangeAddress().EndRow  # シート全体の使用最終行インデックスを取得。
    columnrange = sheet["{0}1:{0}{1}".format("C", usedrowindex+1)]  # C列の使用セル範囲を取得。
    columndata = columnrange.getDataArray()  # (('',), ('',), ('',), ('',), ('',), ('',), ('C Last Row',), ('',), ('',))
    for i in range(usedrowindex, -2, -1):  # 最大使用範囲の行から上にデータの有無をみる。
        if columndata[i][0]:
            break  # データがあるセルにたどりついたらfor文を出る。
    sheet[8, 0].setString("Last used row index in column C: {}".format(i))  # ないときは-1を返す。
21行目以降がC列のみの最大使用範囲を求めるコードです。

行のデータの入っている最後のセルを求めることも同様にできますが、getDataArray()で返ってくる入れ子のタプルの処理に対応して少し処理を変える必要があります。
1
2
3
4
5
6
7
8
9
10
    # 行インデックス6(行7)の最終使用列を求める。
#  cursor = sheet.createCursor()  # セルカーサーの取得。
#  cursor.gotoEndOfUsedArea(False)  # シート上の使用範囲の右下のセルを取得。
    usedcolumnindex = cursor.getRangeAddress().EndColumn  # シート全体の使用最終行インデックスを取得。
    rowrange = sheet[6, :usedcolumnindex+1]  # 行7の使用セル範囲を取得。
    rowdata = rowrange.getDataArray()[0]  # ('', '', 'C Last Row', '', '', '')
    for i in range(usedcolumnindex, -2, -1):  # 最大使用範囲の列から左にデータの有無をみる。
        if rowdata[i]:
            break  # データがあるセルにたどりついたらfor文を出る。
    sheet[9, 0].setString("Last used columns index in row 7: {}".format(i))  # ないときは-1を返す。
これらを実行すると次のような結果になります。


それぞれC列と行7の最大使用範囲のセルC7の行インデックス６と列インデックス2が取得できています。

この場合は背景色を設定していてもデータの入っていないセルは使用範囲には含まれません。

データのあるセルを抽出する

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
from com.sun.star.sheet import CellFlags
def macro():
    doc = XSCRIPTCONTEXT.getDocument()  # ドキュメントを取得。
    sheets = doc.getSheets()  # シートコレクション。
    sheet = sheets[0]  # 最初のシート。
    sheet[:, 0].clearContents(511)  # A列の内容をすべてを削除。
    # B列(列インデックス1)からF列(列インデックス5)までの各列のセルを求める。
    columns = sheet.getColumns()
    sheet[0, 0].setString("Cells containing values and strings for each column.")
    for i in range(1, 6):  # 列インデックス1から5まで。
        cellranges = sheet[:, i].queryContentCells(CellFlags.VALUE+CellFlags.STRING)  # 数値か文字列の入っているセルに限定して抽出。
        cells = cellranges.getCells()  # セルコレクションを取得。
        cellstringaddresses = [getRangeAddressesAsString(c) for c in cells]  # セルを取り出して文字列アドレスのリストを取得。
        sheet[i, 0].setString("{}: {}".format(columns[i].getName(), cellstringaddresses))
    sheet[9, 0].setString("Cells containing values and strings, formulas for each row.")
    for i in range(0, 9):  # 行1(行インデックス0)から行9(行インデックス8)まで。
        cellranges = sheet[i, :].queryContentCells(CellFlags.VALUE+CellFlags.STRING+CellFlags.FORMULA)  # 数値か文字列か式の入っているセルに限定して抽出。
        cells = cellranges.getCells()  # セルコレクションを取得。
        cellstringaddresses = [getRangeAddressesAsString(c) for c in cells]  # セルを取り出して文字列アドレスのリストを取得。
        sheet[i+10, 0].setString("{}: {}".format(i+1, cellstringaddresses))
    sheet[:, 0].getColumns().setPropertyValue("OptimalWidth", True)  # 列幅を最適化する。
queryContentCells()メソッドでデータのあるセルを抽出しました。


C4のみ式が入っています。

列については値と文字列を含んだセル、行については値と文字列と式を含んだセル、を抽出して、その文字列アドレスをA列に出力しています。

getCells()メソッドで返ってくるセルの順番が保障されているのなら、その最後のセルが最終セルになります。
(2018.7.21追記。linuxBeanのLibreOffice5.4では、queryRowDifferences()メソッドの戻り値のセル範囲コレクションのgetCells()メソッドは、なぜか、何もイテレートされませんでした。)

いまのところ思いつく中では最終セルを求めるにはこの方法が一番よいように思います。

(2018.1.17追記。cellrangesをqueryContentCells()の戻り値とするとcellranges.getRangeAddresses()[-1].EndRowで最終行インデックスを取得できます。ただし、この方法は複数列が存在するときは結局すべてのセル範囲のEndRowを求めて最大値を求めるしかないようです。)

選択しているセルはドキュメントのコントローラーのselect()メソッドで変更する


セルカーサーはセル範囲に自身を変更するメソッドを追加したものであって、セルの選択とはまた別のものです。

選択しているセルを変更するにはドキュメントのコントローラーのselect()メソッドを使います。
1
2
controller = doc.getCurrentController()  # ドキュメントのコントローラ。
controller.select(sheet[1:3, :2])  # A2:B3を選択する。
これでA2:B3が選択状態になります。


参考にしたサイト


OOoBasic/Calc/cellcursor - ...?
セルカーサーの解説。

Cell Cursor - Apache OpenOffice Wiki
デベロッパーガイドのセルカーサーの解説。

 OOoBasic/Calc/cellrangesquery - ...?
セル範囲のセルを抽出する方法。

次の関連記事:Calc(25)フレームのサービスとインターフェイス一覧

ブログ検索 by Blogger

Translate

全記事

«
Oct. 2017
»
Sun
Mon
Tue
Wed
Thu
Fri
Sat
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
Created by Calendar Gadget

QooQ