2017-10-20

[Python] openpyxl で Excel を操作してみた！

今携わっている案件でお世話になったので Excel を操作するライブラリ openpyxl についてまとめておきます。

手元にExcelを操作できるソフトがないよってひとは OneDrive にアップロードすると Excel Online でファイルが開けるので是非使ってみてください。お金はかかりません。(ただしマイクロソフトアカウントが必要)

全部の機能が使えるわけじゃないので注意してください。

ここからは openpyxl のお話。インストールは pip で

$ pip install openpyxl

今回利用したopenpyxlのバージョンは現在の最新である 2.4.8 です。 2.3.x と違う部分が結構あると思うので気をつけてください。特にスタイル周り。

検証では基本的に Python 3.6.2 を使ってますが、一部 3.5.1 を使ってます。多分動作には影響ないと思いますが、少々表示が違ったらそのせいということにしておいてください。

ライブラリの大まかな使い方としては、ワークブックを起点にワークシートを選択しセルを操作する、という流れになると思います。

順に見ていきましょう。

ワークブック

ワークブックはExcelファイルの単位です。新規で作ることもできますし、既存のファイルから読み込むこともできます。

from openpyxl import Workbook, load_workbook
# 新規作成の場合は Workbook
wb = Workbook()
# ファイルの保存は save メソッドでファイル名を指定する(上書きの場合も同様)
wb.save('test.xlsx')
# 既存ファイルの読込は load_workbook関数で読み込む
wb2 = load_workbook('test.xlsx', read_only=True)  # 読み込み専用にしたい場合は read_only=True にする

ワークシート

ワークシートを操作するためにワークブック(Workbook) インスタンスが必要になります。必要な方法でワークシートを抽出しましょう。

# シート一覧は worksheets 属性から
wb.worksheets
# シート名の抽出
wb.get_sheet_names()
# シートはシート名のキーアクセスによって抽出できる
ws = wb['Sheet']  # 最初は Sheet だけ存在している
wb.active  # アクティブなシートを抽出することもできる
ws2 = wb.create_sheet('Sheet2')
# 増えてる
wb.worksheets
# 削除する
wb.remove_sheet(ws)
# 減ってる
wb.worksheets
# アクティブなシートも変わる
wb.active
# シートの名前を変える
ws2.title = 'test_sheet'
# シート名が変わるとアクセス可能なキーも変わるので注意
# ws2 = wb['Sheet'] # => KeyError

セル

セルは前述したワークシート (Worksheet) インスタンスが必要になります。ワークシートに対し、セルの位置を渡すことで抽出できます。

Excelを使ったことがある人はわかると思いますが、列(横)がアルファベット、行(縦)が数値となっており、これを結合した B3 のような文字列でアクセスできます。この場合2列目の3行目のセルを指します。

列は Z までいったら、文字が増えます。ちなみにアルファベットは大文字小文字の区別がありません。

ws = wb.create_sheet('Sheet3')
# 列と行を両方指定するとセルが抽出できる
ws['B3']
# 列だけ指定すると、その列に該当するセルがタプルで抽出される(※この場合はB3セルアクセスしたときに作られたB1,B2,B3のみが抽出されるらしい)
ws['B']
# 行だけ指定すると、その行に該当するセルがタプルで抽出される(※この場合はB3セルアクセスしたときに作られたA3,B3のみが抽出されるらしい)
ws['3']
# cellメソッドでセルを抽出すると列を数値で指定できる
ws.cell(row=2, column=2)

from datetime import date, datetime
ws['a1'].value = 12345678
ws['b2'].value = 1.234
ws['c3'].value = 'abc'
ws['d4'].value = date(2017, 1, 2)
ws['e5'].value = datetime(2017, 1, 2, 3, 45)
# 数式も同じように使える
ws['f6'].value = '=SQRT(5)'

出力結果

ちなみに数式をパースするためのTokenizer関数があります。

Excelファイルを読み取るとき役に立ちそうですが、今回は使わない。

リファレンス

http://openpyxl.readthedocs.io/en/default/formula.html

幅と高さ

幅や高さを変えたいときはそれぞれシートに紐づく column_dimensions, row_dimensions 属性をいじります。

from openpyxl import Workbook, load_workbook
# 新規作成の場合は Workbook
wb = Workbook()

ws = wb.create_sheet('width-height')

for i, c in enumerate('abcde', start=1): ws.column_dimensions[c].width = i * 5

for i in range(1, 6): ws.row_dimensions[i].height = i * 10

wb.save('test.xlsx')

出力結果

スタイルの適用

さて、データが入った感動を味わったところで、セルにスタイルを当ててみましょう。これができないんじゃ実用には耐えませんね。というわけで style に入ってると思ってみてみると

>>> ws['a1'].style
'Normal'

..うーん。 Normal ってなんだろう。ちょっと後回しにしましょう。

実は詳細なスタイル設定値は以下のようにバラバラの属性に入っているんですね。

>>> # フォント
>>> ws['a1'].font

Parameters:
name='Calibri', charset=None, family=2.0, b=False, i=False, strike=None, outline=None, shadow=None, condense=None, color=
Parameters:
rgb=None, indexed=None, auto=None, theme=1, tint=0.0, type='theme', extend=None, sz=11.0, u=None, vertAlign=None, scheme='minor'
>>> # 背景色
>>> ws['a1'].fill

Parameters:
patternType=None, fgColor=
Parameters:
rgb='00000000', indexed=None, auto=None, theme=None, tint=0.0, type='rgb', bgColor=
Parameters:
rgb='00000000', indexed=None, auto=None, theme=None, tint=0.0, type='rgb'
>>> # 枠線
>>> ws['a1'].border

Parameters:
outline=True, diagonalUp=False, diagonalDown=False, start=None, end=None, left=
Parameters:
style=None, color=None, right=
Parameters:
style=None, color=None, top=
Parameters:
style=None, color=None, bottom=
Parameters:
style=None, color=None, diagonal=
Parameters:
style=None, color=None, vertical=None, horizontal=None
>>> # 位置
>>> ws['a1'].alignment

Parameters:
horizontal=None, vertical=None, textRotation=0, wrapText=None, shrinkToFit=None, indent=0.0, relativeIndent=0.0, justifyLastLine=None, readingOrder=0.0
>>> # 文字列フォーマット
>>> ws['a1'].number_format
'General'

このままだとなんのこっちゃですね。個別に見ていきます。

フォント

文字のフォントを指定する場合は Font クラスを使います。

ws = wb.create_sheet('font')

from openpyxl.styles.fonts import Font
font = Font(
    name='Arial',  # フォント名
    b=True,  # 太字
    i=True,  # 斜体
    strike=True,  # 打ち消し線
    sz=15,  # 文字サイズ
)
ws['a1'].font = font
ws['a1'].value = 'フォント適用したセルだよ'

出力結果

色

文字色を変えるには Font クラスと Color クラスを使います。

ws = wb.create_sheet('color')

from openpyxl.styles.fonts import Font
from openpyxl.styles.colors import Color
color1 = Color(rgb='ffff0000')
color2 = Color(rgb='ff00ff00')
color3 = Color(rgb='ff0000ff')

ws['a1'].value = 'あ'
ws['a1'].font = Font(color=color1)
ws['a2'].value = 'い'
ws['a2'].font = Font(color=color2)
ws['a3'].value = 'う'
ws['a3'].font = Font(color=color3)

背景色を変えるには PatternFill クラスを使います。(他のやり方もあるかな？)

from openpyxl.styles.fills import PatternFill

ws['b1'].fill = PatternFill(patternType='solid', start_color='ffff0000', end_color='ffff0000')
ws['b2'].fill = PatternFill(patternType='solid', start_color='ff00ff00', end_color='ff0000ff')
ws['b3'].fill = PatternFill(patternType='lightGray', start_color='ff00ff00', end_color='ff0000ff')
ws['b4'].fill = PatternFill(patternType='lightGray', start_color='ff00ff00', end_color='ff00ff00')
ws['b5'].fill = PatternFill(patternType='lightGray', start_color='88ff0000', end_color='88ff0000')

正直 start_color と end_color の関係については詳しく分かってませんが、スルーします。

出力結果

位置

セル内の文字を右寄せにしたり、下寄せにしたりなどは Alignment クラスを使います。

ws = wb.create_sheet('alignment')

from openpyxl.styles.alignment import Alignment

(ws['a1'].value, ws['a2'].value, ws['a3'].value, ws['a4'].value, ws['a5'].value,
 ws['a6'].value, ws['a7'].value, ws['a8'].value, ws['a9'].value, ws['a10'].value, ws['a11'].value) = (
  'd1', 'd2', 'd3', 'd4', 'd5', 'd6', 'd7', 'd8', 'd9',
  'd1000000000000000', 'd1111111111111111'
)

# horizontal には 'left', 'fill', 'centerContinuous', 'center', 'right', 'general', 'justify', 'distributed' のいずれかを指定
ws['a1'].alignment = Alignment(horizontal='right')
# vertical には 'bottom', 'center', 'top', 'justify', 'distributed' のいずれかを指定
ws['a2'].alignment = Alignment(vertical='top')
# 文字の向きを90度傾ける。Excelのバージョンによってはうまく確認できないかも
ws['a3'].alignment = Alignment(textRotation=90)
ws['a4'].alignment = Alignment(shrinkToFit=True)
ws['a5'].alignment = Alignment(indent=5)
ws['a6'].alignment = Alignment(relativeIndent=1.23)
ws['a7'].alignment = Alignment(justifyLastLine=False)
ws['a8'].alignment = Alignment(justifyLastLine=True)
ws['a9'].alignment = Alignment(readingOrder=5)
ws['a10'].alignment = Alignment(wrap_text=True)
ws['a11'].alignment = Alignment(wrap_text=False)

出力結果

Excel Online だとうまく表示できない項目が結構あります。文字の傾きとかもうまく表示できなかった。

枠線

枠線は線自体を表現する Side クラスと4辺をまとめあげる Border クラスを使って表現します。

ws = wb.create_sheet('border')

from openpyxl.styles import borders
ws['A1'].border = borders.Border(bottom=borders.Side(style=borders.BORDER_THICK, color='000000'))
ws['A2'].border = borders.Border(right=borders.Side(style=borders.BORDER_DOTTED, color='ff0000'))
ws['B2'].border = borders.Border(top=borders.Side(style=borders.BORDER_HAIR, color='00ff00'),
                                 right=borders.Side(style=borders.BORDER_DASHDOT, color='0000ff'),
                                 bottom=borders.Side(style=borders.BORDER_NONE, color='ffff00')
                                 )

ちなみにボーダーに使える定数はこんな感じです。

>>> [s for s in dir(borders) if s.startswith('BORDER_')]
['BORDER_DASHDOT',
 'BORDER_DASHDOTDOT',
 'BORDER_DASHED',
 'BORDER_DOTTED',
 'BORDER_DOUBLE',
 'BORDER_HAIR',
 'BORDER_MEDIUM',
 'BORDER_MEDIUMDASHDOT',
 'BORDER_MEDIUMDASHDOTDOT',
 'BORDER_MEDIUMDASHED',
 'BORDER_NONE',
 'BORDER_SLANTDASHDOT',
 'BORDER_THICK',
 'BORDER_THIN']

出力結果

文字列フォーマット

文字列フォーマットはセルの number_format 列に文字列を指定するだけです。結構簡単。

ws = wb.create_sheet('string_format')

from openpyxl.styles import numbers
# ビルトインフォーマット
numbers.BUILTIN_FORMATS
# 定数はこんな感じ
[s for s in dir(numbers) if s.startswith('FORMAT_')]

# 適当に指定してみる
ws['a1'].value = 11111111
ws['a1'].number_format = numbers.FORMAT_GENERAL
ws['a2'].value = 22222222
ws['a2'].number_format = numbers.FORMAT_NUMBER_COMMA_SEPARATED2
ws['a3'].value = 333333
ws['a3'].number_format = numbers.FORMAT_DATE_YYMMDD
ws['a4'].value = 44444444
ws['a4'].number_format = numbers.FORMAT_PERCENTAGE
ws['a5'].value = 55555555
ws['a5'].number_format = numbers.FORMAT_TEXT

出力結果

フォーマットに指定できる定数はこんな感じです

>>> [s for s in dir(numbers) if s.startswith('FORMAT_')]
['FORMAT_CURRENCY_EUR_SIMPLE',
 'FORMAT_CURRENCY_USD',
 'FORMAT_CURRENCY_USD_SIMPLE',
 'FORMAT_DATE_DATETIME',
 'FORMAT_DATE_DDMMYY',
 'FORMAT_DATE_DMMINUS',
 'FORMAT_DATE_DMYMINUS',
 'FORMAT_DATE_DMYSLASH',
 'FORMAT_DATE_MYMINUS',
 'FORMAT_DATE_TIME1',
 'FORMAT_DATE_TIME2',
 'FORMAT_DATE_TIME3',
 'FORMAT_DATE_TIME4',
 'FORMAT_DATE_TIME5',
 'FORMAT_DATE_TIME6',
 'FORMAT_DATE_TIME7',
 'FORMAT_DATE_TIME8',
 'FORMAT_DATE_TIMEDELTA',
 'FORMAT_DATE_XLSX14',
 'FORMAT_DATE_XLSX15',
 'FORMAT_DATE_XLSX16',
 'FORMAT_DATE_XLSX17',
 'FORMAT_DATE_XLSX22',
 'FORMAT_DATE_YYMMDD',
 'FORMAT_DATE_YYMMDDSLASH',
 'FORMAT_DATE_YYYYMMDD2',
 'FORMAT_GENERAL',
 'FORMAT_NUMBER',
 'FORMAT_NUMBER_00',
 'FORMAT_NUMBER_COMMA_SEPARATED1',
 'FORMAT_NUMBER_COMMA_SEPARATED2',
 'FORMAT_PERCENTAGE',
 'FORMAT_PERCENTAGE_00',
 'FORMAT_TEXT']

NamedStyle

上で紹介した様々なスタイルをまとめて管理するためのクラスです。 2.4.x からは Style クラスからこちらに切り替わったようです。詳しくどこからかまでは追ってません。

NamedStyle というだけあって、その違いは名前をつけるか否かにあります。

ws = wb.create_sheet('named_style')

from openpyxl.styles import NamedStyle, Font, Border, Side, Alignment, PatternFill
h1 = NamedStyle(name="highlight1",
                        font=Font(bold=True, size=20),
                        border=Border(bottom=Side(style='thick', color='000000')))

h2 = NamedStyle(name="highlight2",
                        fill=PatternFill(patternType='solid', start_color='ffff0000', end_color='ffff0000'),
                        alignment=Alignment(horizontal='center'))

# とりあえず先にvalueを入れとく
ws['A1'].value, ws['B1'].value, ws['C1'].value = 'test1', 'test2', 'test3'

# 初回はstyle 属性にオブジェクトを代入する
ws['A1'].style = h1
# すでに登録済みのスタイルをオブジェクトで代入すると..
# ws['A2'].style = h1
# ValueError: Style highlight1 exists already
# 2回目以降は 名前を指定しないといけない
ws['B1'].style = 'highlight1'
# 予めワークブックに NamedStyle を登録しておける
wb.add_named_style(h2)
# 登録済みであれば最初から名前の指定でOK
ws['C1'].style = 'highlight2'

出力結果

リファレンス

http://openpyxl.readthedocs.io/en/default/styles.html#creating-a-named-style

というわけで名前は 超大事 です。

上ではhighlightとか名前をつけてますが、一つ一つ名前を設定するのがだるいとか機械的にスタイルを作らないといけないような場合は、連番、オブジェクトのID、UUIDあたりをつかえばいいと思います。

重要なのは、「異なるスタイルには異なる名前を設定すること」です。

warning

これは実際ハマったので共有しておきますが、nameを省略すると Normal という名前が自動設定されます。
Normal は 「スタイルなし」の「スタイル」 としてすでにWorkbookに登録済みです。スタイルが効かない場合は名前の設定し忘れを疑ってみてください。

wb.style_names 属性に登録済みのスタイル名が確認できます。

>>> wb.style_names
['Normal', 'highlight1', 'highlight2']

入力規則

セルに対して、入力可能な値を指定するには以下のようにします。

ws = wb.create_sheet('validation')
from openpyxl.worksheet.datavalidation import DataValidation

# "Dog", "Cat", "Bat" のいずれかの文字だけを許容
dv1 = DataValidation(type="list", formula1='"Dog,Cat,Bat"', allow_blank=True)
# 101以上の整数を許容
dv2 = DataValidation(type="whole", operator="greaterThan", formula1=100)
# 整数だけを許容
dv3 = DataValidation(type="whole")
# 数値だけを許容(小数もOK)
dv4 = DataValidation(type="decimal")
# 日付を許容。数値化された日付でもOKっぽい
dv5 = DataValidation(type="date")
# 0~5までの数値を許容(小数でもOK)
dv6 = DataValidation(type="decimal", operator="between", formula1=0, formula2=5)
# 15文字まで許容(16文字以上はだめ)
dv7 = DataValidation(type="textLength", operator="lessThanOrEqual", formula1=15)
# B9~B11に格納されてる値だけを許容
dv8 = DataValidation(type="list", formula1="Sheet!$B$9:$B$11")
ws['B9'], ws['B10'], ws['B11'] = 'spam', 'ham', 'egg'

# 適用するセルを追加する
dv1.add(ws['a1'])
dv2.add(ws['a2'])
dv3.add(ws['a3'])
dv4.add(ws['a4'])
dv5.add(ws['a5'])
dv6.add(ws['a6'])
dv7.add(ws['a7'])
dv8.add(ws['a8'])

# メッセージとかいじりたい場合は以下のようにする
dv8.error = 'エラーの詳細'
dv8.errorTitle = 'エラータイトル'
dv8.prompt = 'プロンプトテキスト'
dv8.promptTitle = 'プロンプトのタイトル'

# シートに追加しないと使えない
for dv in [dv1, dv2, dv3, dv4, dv5, dv6, dv7, dv8]: ws.add_data_validation(dv)

シートに追加したDataValidationがどこのセルからも参照されていないと不正なファイルと判断されるようです。 (Excel Online で閲覧した場合はそうなったけど環境によるかも)

リファレンス

http://openpyxl.readthedocs.io/en/default/validation.html

条件付き書式

ws = wb.create_sheet('formatting')

from openpyxl.formatting.rule import Rule, ColorScaleRule, CellIsRule, FormulaRule, ColorScale, FormatObject, IconSet
from openpyxl.styles import Color, PatternFill

ws.conditional_formatting.add('A1:A10', ColorScaleRule(start_type='min', start_color='FF0000', end_type='max', end_color='0000FF'))
ws.conditional_formatting.add('B1:B10', CellIsRule(operator='lessThan', formula=['5'], stopIfTrue=True, fill=PatternFill(start_color='FF0000', end_color='FF0000', fill_type='solid')))
ws.conditional_formatting.add('C1:C10', FormulaRule(formula=['ISBLANK(D1)'], stopIfTrue=True, fill=PatternFill(start_color='00FFFF', end_color='00FFFF', fill_type='solid')))

# カラースケールにしきい値を設けたい場合
cs = ColorScale(
  # 今回は50という数値を中間値として設定する
  cfvo=[FormatObject(type='min'), FormatObject(type='num', val=50), FormatObject(type='max')],
  # 色は 赤, 緑, 青とする。緑が閾値となる
  color=[Color('FF0000'), Color('00FF00'), Color('0000FF')]
)
ws.conditional_formatting.add('F1:F10', Rule(type='colorScale', colorScale=cs))

# アイコンを表示する
iconset1 = IconSet(
  iconSet='3TrafficLights1',
  cfvo=[FormatObject(type='percent', val=0), FormatObject(type='percent', val=33), FormatObject(type='percent', val=67)],
  showValue=None, percent=None, reverse=None)

ws.conditional_formatting.add('G1:G10', Rule(type='iconSet', iconSet=iconset1))

iconset2 = IconSet(
  iconSet='5Arrows',
  cfvo=[FormatObject(type='percent', val=0), FormatObject(type='percent', val=25), FormatObject(type='percent', val=50), FormatObject(type='percent', val=75), FormatObject(type='percent', val=100)],
  showValue=None, percent=None, reverse=None)

ws.conditional_formatting.add('H1:H10', Rule(type='iconSet', iconSet=iconset2))

for i, c in enumerate(ws['A1:A10']): c[0].value = i

for i, c in enumerate(ws['B1:B10']): c[0].value = i

ws['D1'].value, ws['D3'].value, ws['D5'].value, ws['D7'].value, ws['D9'].value = (
  1, 'a', 'b', '-', 0
)

for i, c in enumerate(ws['G1:G9'], start=1): c[0].value = i * 10

ws['G10'].value = 500

for i, c in enumerate(ws['G1:G10'], start=1): c[0].value = i * 10

for i, c in enumerate(ws['H1:H10'], start=1): c[0].value = i * 10

出力結果

リファレンス

http://openpyxl.readthedocs.io/en/default/formatting.html

アイコン

アイコンセットは以下の種類があるようです。
- 3Arrows
- 3ArrowsGray
- 3Flags
- 3TrafficLights1
- 3TrafficLights2
- 3Signs
- 3Symbols
- 3Symbols2
- 4Arrows
- 4ArrowsGray
- 4RedToBlack
- 4Rating
- 4TrafficLights
- 5Arrows
- 5ArrowsGray
- 5Rating
- 5Quarters

しきい値より小さい値が入ってるセルは外れ値があっても背景色が潰れていませんね。(潰れるという表現が正しいかは自信ない)

セルにコメントをつけるには Comment クラスを使います。とっても簡単。

ws = wb.create_sheet('comment')

from openpyxl.comments import Comment
ws['a1'].comment = Comment('コメント', 'コメ主')

出力結果

リファレンス

http://openpyxl.readthedocs.io/en/default/comments.html

おまけ

openpyxl.utils.cell module にいろいろと便利な関数が用意されているようです。 (thx flag_boy)

たとえば、列番号とアルファベットを変換する関数は以下のように使えます。

>>> from openpyxl.utils import get_column_letter, column_index_from_string
>>> get_column_letter(20)
'T'
>>> column_index_from_string('T')
20

疲れました。あとは自分の目でたしかみてみろ！