ホーム / 旧ブログ /

Calc(34)セルに入力された日付を取得する方法

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

旧ブログ

t f B! P L
Calc(31)セルへ日付を入力する方法とは逆にセルに入力された日付-時間シリアル値をPythonマクロで取得する方法です。

前の関連記事：Calc(33)スプレッドシート関数をマクロで使う


セルに入力された日付をセルのメソッドで取得する

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
52
from com.sun.star.lang import Locale  # Struct
from com.sun.star.sheet import CellFlags  # 定数
from com.sun.star.util import NumberFormat  # 定数
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)  # セルの内容を削除。
    methods = "getFormula", "getValue", "getString", "getType"  # セルから取得するためのメソッド。
    sheet[0, 1].setString("Formatted")
    for i, method in enumerate(methods, start=2):  # 列Cから各methodについて。
        sheet[0, i].setString("{}()".format(method))
    datestring = "2017-10-25"  # 2017-10-25 or 10/25/2017
    sheet["A2"].setString('sheet["B1"].setFormula("{}")'.format(datestring))
    cell = sheet["B2"]
    cell.setFormula(datestring)  # 式で日付を入力する。
    for i, method in enumerate(methods, start=2):  # 列Cから各methodについて。
        sheet[1, i].setString(str(getattr(cell, method)()))
    formatstring = "YYYY-MM-DD"
    sheet["A3"].setString(formatstring)
    cell = sheet["B3"]
    cell.setPropertyValue("NumberFormat", createFormatKey(formatstring))  # セルの書式を設定。
    cell.setFormula(datestring)  # 式で日付を入力する。
    for i, method in enumerate(methods, start=2):  # 列Cから各methodについて。
        sheet[2, i].setString(str(getattr(cell, method)()))
    formatstring = "GE.MM.DD"
    sheet["A4"].setString(formatstring)
    cell = sheet["B4"]
    cell.setFormula(datestring)  # 式で日付を入力する。
    cell.setPropertyValue("NumberFormat", createFormatKey(formatstring))  # セルの書式を設定。
    for i, method in enumerate(methods, start=2):  # 列Cから各methodについて。
        sheet[3, i].setString(str(getattr(cell, method)())) 
    sheet["A5"].setString("Standard Date Format")
    cell = sheet["B5"]
    cell.setFormula(datestring)  # 式で日付を入力する。
    numberformats = doc.getNumberFormats()
    formatkey =numberformats.getStandardFormat(NumberFormat.DATE, Locale())
    cell.setPropertyValue("NumberFormat", formatkey)  # セルの書式を設定。
    for i, method in enumerate(methods, start=2):  # 列Cから各methodについて。
        sheet[4, i].setString(str(getattr(cell, method)()))
    sheet["A:F"].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
g_exportedScripts = macro, #マクロセレクターに限定表示させる関数をタプルで指定。
このマクロで、日付を入力したセルのメソッドgetFormula()、getValue()、getString()、getType()のそれぞれの戻り値を文字列にして出力しました。


getType()の結果でわかるように、書式設定をしたとしてもセルに入っているデータ形式はValueのままでした。

getString()では書式コードを適用後の文字列を取得できました。

セルに日付を代入するときは、"2017-10-25" や "10/25/2017"(月/日/年)という文字列をsetFormula()で代入していますが、getFormula()ではシリアル値の文字列が返ってきます。

getValue()ではセルの書式に関係なく常に日付-時間シリアル値が数値で返ってきます。

セル編集モードでの日付の書式


上のマクロで書式設定したB列のセルをダブルクリックしたときの書式をみてみました。

シリアル値のままのセルやYYYY-MM-DDの書式設定したセルの日付は、そのままの書式で編集できました。


GE.MM.DDの書式の日付は2017/10/25という形式で編集できるようになっていました。

元号のままでは編集できないようです。


「10月25日」という書式のセルも2017/10/25という形式で編集できるようになっていました。


「YYYY/M/D H:MM:SS」という書式のセルはそのままの形式で編集できました。

セルから日付を取得してPythonのdateオブジェクトにする


単なる加減算ならシリアル値のまま計算してセルに戻せばよいのですが、もっと複雑なことをしたくなることがあるかもしれないので、Pythonのdateオブジェクトにしてみます。
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
from datetime import date
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)
    todayvalue = functionaccess.callFunction("TODAY", ())  # スプレッドシート関数で今日の日付のシリアル値を取得。
    sheet["A1"].setValue(todayvalue)  # セルに日付時間シリアル値を入力。
    sheet["A1"].setPropertyValue("NumberFormat", createFormatKey("GE.MM.DD"))  # セルの書式を設定。
    datetimevalue = sheet["A1"].getValue()  # セルから日付時間シリアル値を取得。
    year = int(functionaccess.callFunction("YEAR", (datetimevalue,)))  # シリアル値から年を取得。floatで返ってくるので整数にする。
    month = int(functionaccess.callFunction("MONTH", (datetimevalue,)))  # シリアル値から月を取得。floatで返ってくるので整数にする。
    day = int(functionaccess.callFunction("DAY", (datetimevalue,)))  # シリアル値から日を取得。floatで返ってくるので整数にする。
    celldate = date(year, month, day)  # Pythonのdateオブジェクトにする。
    sheet["A2"].setString(celldate.isoformat())  # 文字列でセルに書き出す。
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
g_exportedScripts = macro, #マクロセレクターに限定表示させる関数をタプルで指定。
A1セルに今日の日付を入力して、そのセルから日付-時間シリアル値を取得して、Pythonのdateオブジェクトにして、その文字列をA2セルに出力して答え合わせしています。


BasicにはCDateToIso()という関数があって、Calcの日付-時間シリアル値をISO形式にしてくれます。

しかし、UNO APIには同じようなことをする関数は見つけられませんでした。

ということで、スプレッドシート関数のYEAR()、MONTH()、DAY()を使って年月日をそれぞれシリアル値から取得しています。

Calc(33)スプレッドシート関数をマクロで使うでセルを引数としたときは、タプルのタプルで値が返ってきましたが、シリアル値を引数にするとfloatで値が返ってきました。

Pythonのdateオブジェクトは整数を与えないといけないので整数に変換しています。

セルから日付時間を取得してPythonのdatetimeオブジェクトにする

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
from datetime import datetime
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)
    nowvalue = functionaccess.callFunction("NOW", ())  # スプレッドシート関数で今日の日付のシリアル値を取得。
    sheet["A1"].setValue(nowvalue)  # セルに日付時間シリアル値を入力。
    sheet["A1"].setPropertyValue("NumberFormat", createFormatKey("GE.MM.DD H:MM:SS"))  # セルの書式を設定。
    datetimevalue = sheet["A1"].getValue()  # セルから日付-時間シリアル値を取得。
    year = int(functionaccess.callFunction("YEAR", (datetimevalue,)))  # シリアル値から年を取得。floatで返ってくるので整数にする。
    month = int(functionaccess.callFunction("MONTH", (datetimevalue,)))  # シリアル値から月を取得。floatで返ってくるので整数にする。
    day = int(functionaccess.callFunction("DAY", (datetimevalue,)))  # シリアル値から日を取得。floatで返ってくるので整数にする。
    hour = int(functionaccess.callFunction("HOUR", (datetimevalue,)))  # シリアル値から時を取得。
    minute = int(functionaccess.callFunction("MINUTE", (datetimevalue,)))  # シリアル値から時を取得。
    second = int(functionaccess.callFunction("SECOND", (datetimevalue,)))  # シリアル値から時を取得。
    celldate = datetime(year, month, day, hour, minute, second)  # Pythonのdateオブジェクトにする。
    sheet["A2"].setString(celldate.isoformat())  # 文字列でセルに書き出す。
    sheet["A:A"].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
g_exportedScripts = macro, #マクロセレクターに限定表示させる関数をタプルで指定。
日付-時間シリアル値には時間まで含まれているので、年月日に加えて、時分秒を取り出すだけです。

スプレッドシート関数のNOW()で取得した値をA1セルに出力して、その日付-時間シリアル値から先ほどの年月日に加えて、HOUR()、MINUTE()、SECOND()でそれぞれ時分秒を取り出して、datetimeオブジェクトに与えています。

SECOND()でも小数点以下は0で、1秒未満はカウントしていないようでした。


datetimeオブジェクトをisofomatで文字列としてA2セルに取得して答え合わせをしています。

参考にしたサイト


CDateToIso 関数 [実行時] - LibreOffice Help
日付-時間シリアル値をISO形式に変換するBasicの関数。

日付と時刻関数 - LibreOffice Help
日付と時刻に関するスプレッドシート関数一覧。

8.1. datetime — 基本的な日付型および時間型 — Python 3.5.3 ドキュメント
Pythonの日付と時間を扱うモジュール。

次の関連記事:Calc(35)コンテクストメニューをカスタマイズする: その4

ブログ検索 by Blogger

Translate

全記事

Created by Calendar Gadget

QooQ