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

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

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

online-excel

インストールは 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)'

openpyxl-cell

ちなみに数式をパースするための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')

openpyxl-cell-width

スタイルの適用

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

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

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

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

>>> # フォント
>>> ws['a1'].font
<openpyxl.styles.fonts.Font object>
Parameters:
name='Calibri', charset=None, family=2.0, b=False, i=False, strike=None, outline=None, shadow=None, condense=None, color=<openpyxl.styles.colors.Color object>
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
<openpyxl.styles.fills.PatternFill object>
Parameters:
patternType=None, fgColor=<openpyxl.styles.colors.Color object>
Parameters:
rgb='00000000', indexed=None, auto=None, theme=None, tint=0.0, type='rgb', bgColor=<openpyxl.styles.colors.Color object>
Parameters:
rgb='00000000', indexed=None, auto=None, theme=None, tint=0.0, type='rgb'
>>> # 枠線
>>> ws['a1'].border
<openpyxl.styles.borders.Border object>
Parameters:
outline=True, diagonalUp=False, diagonalDown=False, start=None, end=None, left=<openpyxl.styles.borders.Side object>
Parameters:
style=None, color=None, right=<openpyxl.styles.borders.Side object>
Parameters:
style=None, color=None, top=<openpyxl.styles.borders.Side object>
Parameters:
style=None, color=None, bottom=<openpyxl.styles.borders.Side object>
Parameters:
style=None, color=None, diagonal=<openpyxl.styles.borders.Side object>
Parameters:
style=None, color=None, vertical=None, horizontal=None
>>> # 位置
>>> ws['a1'].alignment
<openpyxl.styles.alignment.Alignment object>
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 = 'フォント適用したセルだよ'

openpyxl-font

文字色を変えるには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の関係については詳しく分かってませんが、スルーします。

openpyxl-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)

openpyxl-alignment

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']

openpyxl-border

文字列フォーマット

文字列フォーマットはセルの 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

openpyxl-string_format

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

>>> [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']

ソースコードだとこの辺です。詳しく知りたい人は見てみると良いでしょう。
https://bitbucket.org/openpyxl/openpyxl/src/0f8537998f95c9d8d44913c2edb367516b799d4a/openpyxl/styles/numbers.py?at=default&fileviewer=file-view-default

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'

openpyxl-named_style

というわけで名前は超大事です。
上ではhighlightとか名前をつけてますが、一つ一つ名前を設定するのがだるいとか機械的にスタイルを作らないといけないような場合は、連番、オブジェクトのID、UUIDあたりをつかえばいいと思います。重要なのは、「異なるスタイルには異なる名前を設定すること」です。
これは実際ハマったので共有しておきますが、nameを省略すると「Normal」という名前が自動設定されます。「Normal」は「スタイルなし」の「スタイル」としてすでにWorkbookに登録済みです。スタイルが効かない場合は名前の設定し忘れを疑ってみてください。

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

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

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

入力規則

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

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

openpyxl-formatting

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

他に使えるアイコンセットは以下があるようです。

  • 3Arrows
  • 3ArrowsGray
  • 3Flags
  • 3TrafficLights1
  • 3TrafficLights2
  • 3Signs
  • 3Symbols
  • 3Symbols2
  • 4Arrows
  • 4ArrowsGray
  • 4RedToBlack
  • 4Rating
  • 4TrafficLights
  • 5Arrows
  • 5ArrowsGray
  • 5Rating
  • 5Quarters

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

コメント

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

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

openpyxl-comment

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

おまけ

openpyxl-cell-width

列をアルファベットに変換するのって場合によって結構面倒だったりするのでこんな感じの変換用関数作ると便利かもしれませんね。

26進数ではないので注意してください。26進数だと Z(26) のあと BA(27) になりますが、エクセルでは AA がきます。

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