xlsxwriter.utility

Python ライブラリ xlsxwriter の xlsxwriter.utility は、xlsxwriter内部で使われるヘルパーメソッドですが便利なのでユーザーにも開放されています。

f:id:chiyoh:20190615212135p:plain

xlsxwriter.utility

前書き

いつものように、調べた結果を羅列していきます。

準備(xlswritewを使ってみる)

準備としてxlsxwriterでインスタンスを作ります。

import xlsxwriter
import xlsxwriter.utility

# VBAみたいですね。はじめにWorkbookのインスタンスを作ります
workbook = xlsxwriter.Workbook('example01.xlsx')
# workbookにworksheetを追加します
worksheet = workbook.add_worksheet()

# セルデータを配列とtupleで作ります
data = (
    ['経路', '運賃','特急料金'],
    ['大阪', 8960, 0 ],
    ['新大阪', 0, 5700],
    ['東京', 0,0],
    ['舞浜/リゾートゲートウェイ', 260,0],
    ['東京ディズニーシー',    0,0],
    ['小計', '=SUM(B2:B6)' , '=SUM(C2:C6)'],
    ['合計',  '' ,'=B7+C7'],
)

# 配列を使うのでindexはゼロから始めます
row = 0
col = 0

# ワークシートにwriteメソッドでrow,colを指定して書き込んでいるだけです。
for item, cost1,cost2 in (data):
    worksheet.write(row, col,     item)
    worksheet.write(row, col + 1, cost1)
    worksheet.write(row, col + 2, cost2)
    row += 1

#　作ったワークブックを閉じると保存されるみたいです。
workbook.close()

f:id:chiyoh:20190615212255p:plain

うーん。簡単でしょ？！

xlsxwriter.utility

xlsxwriter.utilityで定義されているメソッド一覧です

xl_rowcol_to_cell(row, col, row_abs=False, col_abs=False)

row(行)とcolumn(列)のセル参照をA1表示方式の文字列に変換します。rowとcolは0スタートの整数。row_absとcol_abs は絶対値表記で'$'が付きます。

引数

row: int セル行(first rowは、0)
col: int セル列(first columnは、0)
row_abs: boolean セル行を絶対値にする。ディフォルトはFalse
- True セル行を絶対値にする
- False セル行を相対値にする
col_abs: boolean セル列を絶対値にする。ディフォルトはFalse
- True セル列を絶対値にする
- False セル列を相対値にする

戻り値

str A1表示方式の文字列

xlsxwriter.utility.xl_rowcol_to_cell(0,0)

'A1'

xlsxwriter.utility.xl_rowcol_to_cell(1,20,0,1)

'$U2'

【解説】A1表示方式は、表を見ながら操作するのには便利であるがプログラムする側からするとカラムを５つ奥に移動するとcolumn名は何になる？forでループはできない？などと不便である。VBAではoffset等を使ってしのいでいるが。。。なのでセルに数式で埋め込むとき等に便利そうなメソッドである。

xl_rowcol_to_cell_fast(row, col)

xl_rowcol_to_cell関数の最適化バージョン内部でのみ使用されています。

引数

row: int セル行(first rowは、0)
col: int セル列(first colは、0)

戻り値

str A1表示方式の文字列

【解説】絶対値表記等は、EXCEL上の操作の際に意味があることでバックグランドのプログラムではrow,col共に配列番号なので必要ないので余計なところを端折って高速化したメソッドである。

xl_col_to_name(col, col_abs=False)

列番号を列セル参照を文字列に変換します。col_abs は絶対値表記で'$'が付きます。

引数

col: int セル列(first columnは、0)
col_abs: boolean セル列を絶対値にする。ディフォルトはFalse
- True セル列を絶対値にする
- False セル列を相対値にする

戻り値

str A1表記方式の列文字列

xlsxwriter.utility.xl_col_to_name(0,1)

'$A'

xlsxwriter.utility.xl_col_to_name(30)

'AE'

【解説】早い話列番号を列アルファベット文字列に変換。行に関してはゼロスタートなのでA1表示方式にするには＋１すれば終わる。

xl_cell_to_rowcol(cell_str)

A1表記方式文字列を、row,col数値行列に変換する。

引数

cell_str str A1表記方式文字列($A$1等の絶対値表記も可)

戻り値

row: int セル行(first rowは、0)
col: int セル列(first columnは、0)

xlsxwriter.utility.xl_cell_to_rowcol('ABC$1000')

(999, 730)

【解説】xl_rowcol_to_cell_fastの逆変換

xl_cell_to_rowcol_abs(cell_str)

A1表記方式文字列を、row,col数値行列に変換する。絶対値表記も処理する

引数

cell_str str A1表記方式文字列($A$1等の絶対値表記も可)

戻り値

row: int セル行(first rowは、0)
col: int セル列(first columnは、0)
row_abs: boolean セル行を絶対値にする。ディフォルトはFalse
- True セル行を絶対値にする
- False セル行を相対値にする
col_abs: boolean セル列を絶対値にする。ディフォルトはFalse
- True セル列を絶対値にする
- False セル列を相対値にする

xlsxwriter.utility.xl_cell_to_rowcol_abs('ABC$1000')

(999, 730, True, False)

【解説】xl_rowcol_to_cellの逆変換

xl_range(first_row, first_col, last_row, last_col)

行と列のセル参照をA1:B2形式範囲文字列に変換します。

引数

first_row: int 最初のセル行(first rowは、0)
first_col: int 最初のセル列(first columnは、0)
last_row: int 最後のセル行(first rowは、0)
last_col: int 最後のセル列(first rowは、0)

戻り値

str A1:B2形式範囲文字列

xlsxwriter.utility.xl_range(10,20,30,40)

'U11:AO31'

【解説】:でxl_rowcol_to_cell_fastを使って挟んだ感じ

xl_range_abs(first_row, first_col, last_row, last_col)

行と列のセル参照を$A$1:$B$2形式絶対範囲文字列に変換します。

引数

first_row: int 最初のセル行(first rowは、0)
first_col: int 最初のセル列(first rowは、0)
last_row: int 最後のセル行(first rowは、0)
last_col: int 最後のセル列(first rowは、0)

戻り値

str $A$1:$B$2形式範囲文字列

xlsxwriter.utility.xl_range_abs(40,30,20,10)

'$AE$41:$K$21'

【解説】xl_rangeの絶対値付きの範囲文字列変換であるが、相対値と絶対値の混合ができない

xl_range_formula(sheetname, first_row, first_col, last_row, last_col)

ワークシート名と行列番号をSheet1！A1:B2範囲表示方式文字列に変換

引数

sheetname: str ワークシート名
first_row: int 最初のセル行(first rowは、0)
first_col: int 最初のセル列(first columnは、0)
last_row: int 最後のセル行(first rowは、0)
last_col: int 最後のセル列(first columnは、0)

戻り値

str Sheet1！A1:B2範囲表示方式文字列に変換

xlsxwriter.utility.xl_range_formula('sheet1',4,3,7,101)

'sheet1!$D$5:$CX$8'

【解説】quote_sheetnameとxl_range_absを足した感じ

quote_sheetname(sheetname)

ワークシート名にスペースまたは特殊文字が含まれている場合は、その名前を引用符付きの名前に変換します。

引数

sheetname: str ワークシートの名前

戻り値

str 引用符付きのワークシート

【解説】引数が数値文字列以外は、文字列を引用符付きにする。例外処理もやる

# シート名は引用符を付ける
st_name = xlsxwriter.utility.quote_sheetname('結果')
st_name

'結果'

# シート名にスペースが入っていようとなかろうと関係ない
print(xlsxwriter.utility.quote_sheetname('結果 1'))

'結果 1'

# 2重にかけても無視される
print(xlsxwriter.utility.quote_sheetname(st_name))

結果

# VBAとかでもシート名を数値(バーバリアン型)で表すので数値で戻す
print(xlsxwriter.utility.quote_sheetname("5"))

xl_color(color)

XlsxWriterのcolorメソッドで一緒に使用して変換してます。RGB形式の文字列に変換します。これらの色は以前のバージョンのExcelとの下位互換性。

引数

color: str 色名または、 RGB形式文字列

戻り値

str ARGB形式

注意

'AARRGGBB'フォーマットで返される
対応色名
- 'black','blue','brown','cyan','gray','green','lime','magenta',
- 'navy','orange','pink','purple','red','silver','white','yellow'

#色名
xlsxwriter.utility.xl_color('silver')

'FFC0C0C0'

#色コード
xlsxwriter.utility.xl_color('#00FF00')

'FF00FF00'

#アルファ付き色コード
xlsxwriter.utility.xl_color('#80FFFF00')

'FF80FFFF00'

get_rgb_color(color)

色名をRGB形式の文字列に変換します。

引数

color: str 色名

戻り値

str RGB形式

注意

'RRGGBB'フォーマットで返される

#色名
xlsxwriter.utility.get_rgb_color('silver')

'C0C0C0'

【解説】xl_color(color)のアルファ無版

get_sparkline_style(style_id)

スパークラインのスタイルを収得します(add_sparklineで使用している)

引数

style_id: int スタイル番号

戻り値

dict スタイル情報

注意

サポートされているのは0~36

xlsxwriter.utility.get_sparkline_style(0)

{'series': {'theme': '4', 'tint': '-0.499984740745262'},
 'negative': {'theme': '5'},
 'markers': {'theme': '4', 'tint': '-0.499984740745262'},
 'first': {'theme': '4', 'tint': '0.39997558519241921'},
 'last': {'theme': '4', 'tint': '0.39997558519241921'},
 'high': {'theme': '4'},
 'low': {'theme': '4'}}

xlsxwriter.utility.get_sparkline_style(36)

{'series': {'theme': '1'},
 'negative': {'theme': '9'},
 'markers': {'theme': '8'},
 'first': {'theme': '4'},
 'last': {'theme': '5'},
 'high': {'theme': '6'},
 'low': {'theme': '7'}}

supported_datetime(dt)

xlsxwrite(excel)のChartやWorksheetでサポートされている日時オブジェクトかどうかを判別します。

引数

dt: 検査するオブジェクト

戻り値

boolean
- True サポートする
- False サポートしない

注意

サポートされているのはdatetimeのdatetime,date,time,timedeltaです

from datetime import datetime
xlsxwriter.utility.supported_datetime(datetime.today())

True

print(datetime.today())

2019-06-15 21:18:11.820430

xlsxwriter.utility.supported_datetime("2019-06-15 10:16:46.687568")

False

d = datetime(2019, 2, 3)
xlsxwriter.utility.supported_datetime(d)

True

remove_datetime_timezone(dt_obj, remove_timezone)

Excelでは日時のタイムゾーンはサポートされていないため削除する関数。引数で指定した日時オブジェクトからtzinfoを削除します。

引数

dt_obj: datetimeクラスのオブジェクト
remove_timezone: 削除するタイムゾーン

戻り値

datetimeクラスのオブジェクト

from datetime import datetime, timedelta, timezone
JST = timezone(timedelta(hours=+9), 'JST')

jtime = datetime.now(JST)
jtime

datetime.datetime(2019, 6, 15, 21, 18, 12, 452426, tzinfo=datetime.timezone(datetime.timedelta(seconds=32400), 'JST'))

xlsxwriter.utility.supported_datetime(jtime)

True

t = xlsxwriter.utility.remove_datetime_timezone(jtime,'JST')
t

datetime.datetime(2019, 6, 15, 21, 18, 12, 452426)

xlsxwriter.utility.supported_datetime(t)

True

datetime_to_excel_datetime(dt_obj, date_1904, remove_timezone)

日時オブジェクトをExcelのシリアルの日時に変換します。数値の整数部にはエポック以降の日数が格納され、小数部にはその日の割合が格納されます。

引数

dt_obj: datetimeクラスのオブジェクト
date_1904: boolean エポックを'1904-01-01'にするか'1899-12-31'にするか選択。
- True '1904-01-01'
- False '1899-12-31'
remove_timezone: boolean remove_datetime_timezoneを使ってタイムゾーンを削除するかどうか
- True 削除する
- False 削除しない

戻り値

浮動小数点でEXCELで使用されているシリアル日時

xlsxwriter.utility.datetime_to_excel_datetime(jtime,True,True)

42169.8876441253

xlsxwriter.utility.datetime_to_excel_datetime(jtime,False,True)

43631.8876441253