Pythonライブラリ(openpyxl)によるExcelのセルの書式設定(配置・保護・表示形式)後編とスタイル設定【徹底解説】

スポンサーリンク
Python外部ライブラリ(openpyxl)➃ openpyxl

本記事ではMS-Office ExcelをPythonで操作するライブラリとして「openpyxl」の解説をしていきます。

前回(連載3回目)の記事では、前編(※)としてセルに「フォント」「塗りつぶし」「罫線」の書式(装飾)を施す方法について解説してきました。(※セルの書式設定に関しては機能が豊富に用意されており一つの記事にまとめることができませんでしたので前編・後編に分けて解説します。)

前回の記事は以下になりますので、参考にして下さい。


さて、セルに書式を設定する方法の後編となる今回は、セルに「配置」や「保護」、それから「表示形式」を適用する書式設定を解説していきます。

さらに、ユーザが複数の書式を組合わせて定義したカスタム書式をスタイルとしてExcelに登録して再利用する方法についても取り上げます。

もちろん、openpyxl(Excel)にはあらかじめ定義済みのスタイル(Builtin Style)が用意されていますので、それを活用する例も紹介します。

連載の目次(予定)は以下のようになっていますので参考にして下さい。

Part4_目次
図1 記事の解説予定範囲

この記事を読むことでopenpyxlによるExcel操作に関する、次のようなことが「できる・わかる」ようになりますので最後までお付き合いください。

➀. セルの書式設定手順をopenpyxlとExcelとで比較できる。

➁. Cellオブジェクトに書式を適用する方法が分かる。

➂. スタイルの適用方法(組込みスタイルとユーザ定義スタイル)が分かる。

本サイトでの紹介例は一例です。また、関数などの省略可能なオプション引数などについては割愛していますので、詳細や不明点などは必要に応じて公式サイトなどを参照してください。

記事内で動作確認した開発環境とバージョン情報は次のとおりとなります。異なる環境・バージョンのライブラリを使う際はこの点、ご留意ください。

・Python 3.7.6(64bit)

・openpyxl 3.0.3

・JupyterNotebook 6.0.3

・Microsoft Office Personal 2016

それでは、次節からは具体的な各種オブジェクトの扱い方について詳しく解説していきます。

スポンサーリンク

1. セルに書式(スタイル)を設定する

本節では個々のセル(Cellオブジェクト)にフォントなどの書式を設定する方法を解説します。

openpyxlを使うことでExcelの「セルの書式設定ダイアログ」にある項目のほぼ全てのことに対応することができます。(図2)

セルの書式ダイアログ(一覧)
図2 Excelのセルの書式設定ダイアログ

openpyxlを使ってセル(Cellオブジェクト)に書式設定する上での前提知識を簡単にまとめると次のようになります。

➀. 各書式設定用のクラスからオブジェクトを生成し、Cellオブジェクト配下の各種プロパティ(font/fill/arignmentなど)によって設定していきます。

➁. 複数のセル(Rangeオブジェクト・行/列)に一括で書式設定することは原則できません。個別セルに分解してから適用する必要があります。

➂. 結合セルに、書式を設定するには結合範囲の左上セル1つを指定します。

以降ではオブジェクト生成時(コンストラクタ)に指定する引数の書式とオプション定数について詳しく解説していきます。

1.1 配置(Alignmentオブジェクト)の設定

セル内の値(数値や文字)の配置に関する設定(水平・垂直位置・折り返し)は Alignmentクラス から Alignmentオブジェクト を次の書式で取得した後に、対象のCellオブジェクトの alignmentプロパティ にて設定します。

Alignmentオブジェクト

from openpyxl.styles import Alignmet

Alignment(horizontal, vertical, text_rotation, wrap_text, shrink_to_fit, indent)


引数: horizontal : 水平位置の指定(デフォルト:‘general’) オプション定数8種から選択

引数: vertical : 高さ位置の指定(デフォルト:’bottom‘) オプション定数5種から選択

引数: text_rotation : 文字の回転角度の指定(デフォルト:0) 0~180まで1度ステップで指定

引数: wrap_text : 折り返し有効(デフォルト:’False無効‘)

引数: shrink_to_fit : 文字の自動サイズ調整(デフォルト:’False無効‘)

引数: indent : インデントサイズ(デフォルト:0)

戻り値: Alignmentオブジェクト

※引数は全てオプショナル引数

Excelのセルの書式設定(配置タブ)とクラスの各引数との対応関係は図3のとおりです。Excelのほぼ全ての設定項目を操作することができます。

セルの書式ダイアログ(Alignment)
図3 セルの書式指定(配置)

引数 horizontal(水平位置)や引数 vertical (高さ位置)はオプション引数の中から選択することができます。(図4)

セルの書式ダイアログ(Alignment)_引数オプション
図4 horizontal引数とvertical引数のオプション定数

それでは、実際にセルにAlignmentオブジェクトを適用するコード例を<List1>に示します。

コードの概要は以下のとおりです。

[A]では 引数”horizontal”に定数を指定してセル内の水平位置を変更しています。

[B]では引数”vertical”に定数を指定して垂直方向の位置を調整しています。

そして[C]では引数”textRotation”, 引数”shrinkToFit”, 引数”wrapText”にてそれぞれ、文字の回転角度・全体表示(縮小・折り返し)の指定をしています。

※[A]などの識別記号はコード内のコメントに記しています。

各引数は単独で、もしくは組み合わせて指定します。

from openpyxl import load_workbook
from openpyxl.styles import Alignment    # Alignmentクラスをインポート

wb = load_workbook('styles_Alignment.xlsx') # Excelファイルの読込み
ws1 = wb.worksheets[0]    # ワークシート1
ws2 = wb.worksheets[1]    # ワークシート2

# [A]--------------------------------------------------------------
# 水平配置の設定➀

# "horizontal" 引数の適用例➀
ws1['C3'].alignment = Alignment(horizontal='right')  # 右揃え
ws1['C4'].alignment = Alignment(horizontal='center') # 中央揃え
ws1['C5'].alignment = Alignment(horizontal='left')   # 左揃え

# [A]--------------------------------------------------------------
# 水平配置の設定➁

# "horizontal" 引数の適用例②
ws1['G7'].alignment = Alignment(horizontal='justify')          # 全体表示折り返し(左揃え)
ws1['G8'].alignment = Alignment(horizontal='centerContinuous') # 全体表示(中央揃え)
ws1['G9'].alignment = Alignment(horizontal='general')          # 全体表示(左揃え)
ws1['G10'].alignment = Alignment(horizontal='fill')
ws1['G11'].alignment = Alignment(horizontal='distributed')     # 全体表示折り返し(中央揃え)

# [B]--------------------------------------------------------------
# 垂直配置の設定➀

# "vertical" 引数の適用例
ws2['C2'].alignment = Alignment(vertical='justify')      # 全体表示折り返し(左揃え)
ws2['C3'].alignment = Alignment(vertical='top')          # 全体表示(上揃え) 
ws2['C4'].alignment = Alignment(vertical='center')       # 全体表示(中央揃え)
ws2['C5'].alignment = Alignment(vertical='bottom')       # 全体表示(下揃え)
ws2['C6'].alignment = Alignment(vertical='distributed')  # 全体表示折り返し(左揃え)

# [C]--------------------------------------------------------------
# その他、配置に関する設定

# 角度の設定 "textRotation" 引数の適用例
ws2['F8'].alignment = Alignment(textRotation=25)
ws2['F9'].alignment = Alignment(textRotation=90)
ws2['F10'].alignment = Alignment(textRotation=180)

# 縮小して全体を表示する "shrinkToFit" 引数の適用例
ws2['F12'].alignment = Alignment(shrinkToFit=True)

# 折り返して全体を表示する "wrapText" 引数の適用例
ws2['F13'].alignment = Alignment(wrapText=True)


wb.save('styles_Alignment_apply.xlsx')

List1の実行結果は以下のようになりました。(図5)

青枠が水平位置(引数”horizontal”)、緑枠が垂直位置(引数”vertical”)の設定をした結果となります。

なお、行・列の幅は見やすくするために調整しています。自動調整はされませんので、手動もしくは別途コードを追加して調整してください。

Alignmentオブジェクトの適用例
図5 List5の実行結果

1.2 保護(Protectionオブジェクト)の設定

セルごとに編集の可否を設定する(保護する)ことができます。セルを保護するためにstylesモジュールの「Protectionクラス」が用意されています。次の書式のようにクラスから Protectionオブジェクト を生成し、対象のCellオブジェクトの「protectionプロパティ」にて設定します。

Protectionオブジェクト

from openpyxl.styles import Protection

Protection(locked, hidden)


引数: locked :編集の可否の設定(デフォルト:True[保護する])

引数: hidden :非表示にする設定(デフォルト:False[表示])

戻り値:Protectionオブジェクト

※引数は全てオプショナル引数

Excelのセルの書式設定(保護タブ)とクラスの各引数との対応関係は図6のとおりです。

(説明の必要はありませんが、、)

セルの書式ダイアログ(Protection)
図6 セルの書式設定(保護タブ)

なお、上記オブジェクトでセルの保護の設定を行ったとしても、上位のWorksheetオブジェクトもしくは、Workbookオブジェクトの保護機能を有効にしない限り設定は反映されませんので注意してください。(図7)このあとのサンプルコードでも解説します。

シートの保護ダイアログ
図7 シートの保護のダイアログ(校閲タブ->保護グループ->シートの保護)

worksheet.protectionモジュール に関しては本記事では解説しませんので適宜、公式ドキュメントを参照してください。

公式ドキュメント【Worksheet.protectionモジュール】

https://openpyxl.readthedocs.io/en/stable/protection.html#worksheet-protection

それでは、セルの編集を保護するコード例を<List2>に示します。

先述したとおり、セルの保護の切り替えを有効にるには上位モジュールにあたるブックもしくはシートの保護を有効にする必要があります。

ブックの保護は[A]のようにして「Workbookオブジェクトのsecurity.lockStructure属性」で設定することができます。(コード内ではコメントアウト)

シートの保護では[B]のようにして「Worksheetオブジェクトのprotection.enable()メソッドもしくはpassword属性」で設定します。(コードではenable()メソッドでシートに保護を掛けています。)

そして、最後にセルの書式設定の保護機能として[C]でProtectionオブジェクトをCellオブジェクトに適用させています。複数のセル範囲(Rangeオブジェクト)は個別セルに分解して適用させます。

from openpyxl import Workbook
from openpyxl.styles import Protection    # Protectionクラスをインポート

wb = Workbook()
ws = wb.active

# [A] -----------------------------------------------------------------------
# WorkBookのlockStructure属性でワークブック全体を保護する

# wb.security.lockStructure = True  # ブックの保護を有効(コメントアウト)


# [B] -----------------------------------------------------------------------
# WorkSheetのprotectionオブジェクトのenable()メソッド
# 若しくは、password()メソッドでワークシートの保護を有効にする

ws.protection.enable()              # シートの保護を有効
# ws.protection.password = '1234'   # password()メソッドだけでもシートの保護が可能(コメントアウト)


# [C] -----------------------------------------------------------------------
# [A]でブックを保護する、もしくは[B]でシートを保護した後にセルの
# 保護の設定が有効となる

# セルの範囲にProtectionオブジェクトを適用する場合は
# 個別セルに分解して適用設定をする
for row in ws['A1:B2']:
    for cell in row:
        # 引数lockedにFalse(保護解除)を指定して編集可能状態とする
        # デフォルト状態ではTrue(保護)となっている
        cell.protection = Protection(locked=False, hidden=False)

        
# 結合セル範囲にProtectionオブジェクトを適用する場合は
# セル範囲の左上のセルのみに設定する。
ws.merge_cells('A3:B4')
ws['A3'].protection = Protection(locked=False, hidden=False)



wb.save('styles_Protection.xlsx')

List2の実行結果は以下のようになりました。

A1~B4セルの範囲は指定通りに保護対象から外れ編集できることが確認できました。その他のセルはデフォルトで保護有効なので保護されています。

セルの保護のコード実行結果
図8 List2の実行結果

1.3 表示形式(Number_Formatオブジェクト)の設定

ユーザが定義した表示形式をセルに設定できます。

これまでの書式設定の方法とは異なり専用のクラスからオブジェクトを生成する必要はありません。

以下のように対象のCellオブジェクト配下のnumber_formatプロパティ に表示書式記号(@,#など)を文字列(‘記号文字列’)として設定することで適用します。 (図9)

セルの書式ダイアログ(表示形式)_number_formatプロパティ
図9 セルに表示形式(ユーザー定義)を設定

なお、表示形式には図9の青枠囲い部のように「数値」「通貨」「会計」などの項目がありますが、こちらは後述するstyleプロパティによるスタイル設定で対応可能となります。

また、表示形式には以下の記号(一部)を組合わせて自由にカスタマイズもできます。

セルの書式記号
図10 書式記号の一例

さらに補足しますと、図9のユーザ定義一覧に表示される表示記号例は「styles.numbersモジュール」内で辞書形式(dict型)でインデックスをキーとして定義されており、builtin_format_code()メソッドなどを使ってインデックスにより指定できます。使い方はこの後のサンプルコードで解説します。

styles.numbersモジュール ソースコード抜粋<公式ドキュメントより>

https://openpyxl.readthedocs.io/en/stable/_modules/openpyxl/styles/numbers.html#NumberFormat
number_format_組込み書式記号一覧
図11 組込み型 書式設定一覧

それでは、セルに表示形式を設定するコード例を<List3>に示します。

表示形式は[A]のようにユーザーが記号を組合わせて文字列をつくり、適用させることも、 [B]のようにあらかじめ組込まれている表示形式からインデックスにより選択することも可能です。(このあたりはExcelの操作と同様です。)

また、「styles.numbersモジュール」には、表示形式に関するいくつかのメソッドが提供されています。組込み書式の可否を確認する「is_builtin()メソッド」や日付書式型の可否を確認する「is_date_format()メソッド」,日時の型を確認する「is_datetime()メソッド」などがあります。

from openpyxl import load_workbook
from openpyxl.styles.numbers import builtin_format_code, builtin_format_id
from openpyxl.styles.numbers import is_builtin, is_date_format, is_datetime 

wb = load_workbook('styles_Number_Format.xlsx')
ws = wb.active

# [A] -----------------------------------------------------------------------
# 書式記号を組合わせてセルに表示形式を適用する
# 組込み書式記号にないユーザ独自文字列でも問題ない

ws['C4'].number_format = '##.##'
ws['C5'].number_format = '0000.???'
ws['C7'].number_format = 'yyyy'
ws['C8'].number_format = 'dddd'
ws['C10'].number_format = 'mm:ss'
ws['C12'].number_format = '[red]@"さん"'

# [B] -----------------------------------------------------------------------
# 既定の組込み書式フォーマットから選択して表示形式を適用する

print(builtin_format_code(0)) # >> General
ws['E4'].number_format = builtin_format_code(0)

print(builtin_format_code(1)) # >> 0
ws['E5'].number_format = builtin_format_code(1)

print(builtin_format_code(2)) # >> 0.00
ws['E6'].number_format = builtin_format_code(2)

# [C] -----------------------------------------------------------------------

# 数値フォーマットの識別IDを調べる
id = builtin_format_id('0.00')
print(id) # >>2

# 組込みフォーマットかどうかを確認する
print(is_builtin('#,##0')) # >> True  (組込まれている)
print(is_builtin('gggg'))  # >> False (組込まれてない)

# 日付フォーマットかどうかを確認する
print(is_date_format('mm-dd-yy'))  # >> True (日付)
print(is_date_format('0.00'))      # >> False (日付ではない)

# 日付時刻フォーマットかどうかを確認する
print(is_datetime('m/d/yy h:mm')) # >> datetime(日付時刻)
print(is_datetime('mm-dd-yy'))    # >> date(日付)
print(is_datetime('h:mm AM/PM'))  # >> time(時刻)
print(is_datetime('0.00'))        # >> None(日付・時刻のフォーマットではない)

wb.save('styles_Number_Format_apply.xlsx')

2. セルにスタイル(Styleオブジェクト)を適用する

Python_基本文法_内包表記

ここまで、セルの書式として「フォント」「塗りつぶし」「罫線」などセルの装飾や視覚効果に関して個別に設定するための方法、クラスやオブジェクトについての解説してきました。Excelにはもう一つ、装飾やデザインに関連した「スタイル」という考え方もあります。

スタイルとは、個々の書式設定を単体もしくは複数種類を組合わせて「目的のテーマ」にマッチようにアレンジした “いわゆるデザインの型” のことです。(図12)

スタイルの定義
図12 セルに適用するスタイルとは

一度、スタイル(“デザインの型”)を定義してしまえば、これまでに紹介した書式設定に関するクラスやオブジェクトを都度生成するといった面倒な実装を省くことができます。

2.1 組込みスタイルの適用

はじめに、Excelにあらかじめ用意されている スタイル(Builtin-Style) を適用する方法を解説します。

既定スタイルを適用するにはCellオブジェクト配下の「styleプロパティ」を使って、「‘スタイルの定義名’」(シングルクオーテーションで囲んだ文字列)を設定します。

Styleプロパティ

Cellオブジェクト.styleプロパティ = ‘スタイルの定義名’

‘スタイルの定義名’ :‘Normal’, ’Title’, ‘Comma’,・・・

既定スタイルを適用する場合に「スタイルの定義名」にどのような定義名文字列を設定したらよいかを図13にまとめました。

(Excelのホームタブ -> スタイルグループのスタイル一覧すべてに対応しています。)

セルの書式_組込みスタイル一覧_GUI
図13 スタイル(Builtin-Style)の定義名一覧

2.2 ユーザー定義スタイルの作成と適用

設定できるスタイルには、上記の既定スタイル(Builtin-Style)以外にも、ユーザーが独自にスタイルを定義・登録した、「ユーザー定義スタイル」があります。

この「ユーザー定義スタイル」を定義するには、新たに NamedStyleクラス から次の書式に従って NameStyleオブジェクト を生成します。

引数にはこれまでに解説してきた個々のフォントや罫線などのセルの書式に関連する各種オブジェクトを設定します。引数省略時にはデフォルト値が適用されます。

NamedStyleオブジェクト

from openpyxl.styles import NamedStyle

NamedStyle(name, font, fill, border, alignment, number_format, protection)


引数: name : 定義する書式名を設定する

引数: font : Fontオブジェクトでフォントを設定する

引数: fill : Pattern(Gradient)Fillオブジェクトで塗りつぶしの設定をする

引数: border : Borderオブジェクトで罫線の設定をする

引数: alignment : Alignmentオブジェクトで配置の設定をする

引数: number_format :表示形式記号を設定する

引数: protection :Protectionオブジェクトで保護の設定をする

戻り値:NamedStyleオブジェクト

※すべての引数はオプション引数扱いとなります。

スタイルの適用には先ほどの「styleプロパティ」を使います。

NamedStyleオブジェクトをそのままstyleプロパティで設定もできますが、スタイルを再利用したい場合などは、Exceのスタイル一覧(ホームタブ -> スタイルグループ)に登録もできます。(ここでの再利用とはプログラムの中でけでなく普段のExcelUI操作で利用できるという意味です。)

一覧への登録は、Workbookオブジェクトの add_named_style()メソッド にて行います。

では、ユーザー定義スタイルの作成と適用するコード例を<List4>に示します。

コードの概要は、まず[A]でNamedStyleクラスの引数(font,…)に、セルの書式に関連するオブジェクトを設定してNamedStyleオブジェクトを生成します。「引数name」には、Excelのスタイル一覧に登録(表示したい)するスタイル名を指定します。

[B]では[A]で定義したスタイルをExcelのスタイル一覧に登録するadd_named_style()メソッドを実行します。引数にはNamedStyleオブジェクトを指定します。

そして[C]のstyleプロパティで対象セルにユーザー定義スタイルを適用します。

[B]でスタイル一覧に登録済みですのでスタイル名で設定することもできますし、NamedStyleオブジェクトにて設定することもできます。

from openpyxl import Workbook

# NamedStyleクラスやその他セルの書式設定に関連するクラスをインポート
from openpyxl.styles import NamedStyle, Font, PatternFill, Alignment
from openpyxl.styles.colors import Color

wb = Workbook()
ws = wb.active

# [A] -----------------------------------------------------------------------
# 個々のセルの書式のオブジェクトを組合わせてユーザー定義スタイル
# NamedStyleオブジェクトを生成する

user_custum = NamedStyle( name='SampleStyle',
                          font=Font(name='Arial', size=14, color="FFFFFFFF"),
                          fill=PatternFill(patternType='solid', fgColor=Color(rgb='1e90ff')),
                          alignment=Alignment(horizontal='centerContinuous', vertical='bottom')
                         )

print(type(user_custum))
# >> <class 'openpyxl.styles.named_styles.NamedStyle'>

# [B] -----------------------------------------------------------------------
# Workbookオブジェクトの「add_named_style()メソッド」にてブックに
# 先に定義したスタイルを一覧に登録する
wb.add_named_style(user_custum)


# [C] -----------------------------------------------------------------------
# Cellオブジェクトのstyleプロパティで定義したスタイルを適用する

# スタイル名で設定できる(上でスタイル名を登録したことによる)
ws['B4'].style='SampleStyle'

# オブジェクトをそのまま設定することも勿論できる
ws['B2'].style=user_custum


wb.save('styles_NamedStyle.xlsx')

List4の実行結果は図14のようになりました。

ユーザー定義スタイルが登録され、セルに書式を適用することができました。

ユーザー定義スタイルの適用と登録
図14 List4の実行結果

3. まとめ

いかがでしたでしょうか?

引き続きExcelを操作する外部ライブラリ「openpyxl」を使用して、セルの書式設定と書式をスタイルとして登録する方法ついて解説してきました。

openpyxlが用意する関連クラスを使うことでExcelの設定ウィンドウででできる設定のほぼすべてのことに対応できることがお分かりになられたと思います。

今回ご紹介しきれなかったオプション引数や機能は、他にもまだたくさんあります。ぜひ、公式ドキュメント「openpyxl.stylesモジュール」の頁を参考にして目当ての機能がないか探してみてください。

ここまでの記事内容のポイントをまとめます。

➀. セルの配置や保護の設定には、AlignmentクラスProtectionクラスを用いる。

➁. セルに表示形式を設定するには、Cellオブジェクト. number_formatプロパティに表示形式記号を設定する。

➂. スタイルには、あらかじめExcelに組み込まれたスタイルとユーザー定義スタイルがある。後者は、セルの書式(オブジェクト)を組合わせたユーザー独自スタイルのことで、NamedStyleクラスで定義する。


さて、ここまでセルに施す書式一つひとつについて解説してきましたが、Excelには「特定の条件を満たしたセルがあった場合には、決められた書式を適用する」といったような便利な機能(ツール)があります。

Excelでは「条件付き書式」というグループにまとめられています。そこで次回は、セルの書式設定をより能動的に活用するこの「条件付き書式」について解説していきます。

リンク先はこちらになります。↓

最後までお読みいただきありがとうございました。

タイトルとURLをコピーしました