【Python×Excel】openpyxlでセルに書式(配置・保護・表示形式・スタイル)設定する方法【後編】

Japanese | English

今回も引き続き、Pythonから「Excel」を操作する「openpyxl」ライブラリを紹介していきます。

前回(連載3回)の記事では、セルに書式設定をする方法として「フォント」「塗りつぶし」「罫線」を解説しました。前回の記事は、こちらにになりますので参考にして下さい。

【Python×Excel】openpyxlでセルに書式設定(フォント・塗り潰し・罫線)する方法【前編】

「openpyxlライブラリ」によって、セルにフォントや塗り潰し(ハッチング)、罫線といった書式(スタイル)設定を適用する方法を取り上げます。それぞれの書式は、専用のオブジェクトで定義・管理されます。クラスの詳細仕様とともに図解で解説します。

www.shibutan-bloomers.com

2021.05.30

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

さらに、ユーザが複数の書式を組合わせて独自の書式スタイル(Custom Style)を定義・登録する方法も取り上げます。もちろん、openpyxlには、あらかじめ定義済みのスタイル(Built-in Style)も用意されていますので、それらの活用例も紹介します。

その他、本連載【Python×Excel】のコンテンツ構成はこちらのようになっていますので、参考にして下さい。

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

それでは、次節より「書式設定に関するオブジェクトの定義方法」ついて詳しく解説していきます。

1. セルに書式を設定する

本節では、セル(Cellオブジェクト)に書式を設定する方法を解説します。

openpyxlには書式設定に関連するクラスが多数用意されており、Excelの「セルの書式設定ダイアログ」(図２)にあるほぼ全ての項目に対応ができます。

openpyxlを使ってセル(Cellオブジェクト)に書式設定するにあたって、あらかじめ理解しておく前提条件についてまとめると次のようになります。

➁の適用範囲について補足します。以下の公式ドキュメントにもあるように、あらかじめExcelで作成されたデータ範囲に対しては、一度にまとめて書式を適用することができます。

Styles can also applied to columns and rows but note that this applies only to cells created (in Excel) after the file is closed. If you want to apply styles to entire rows and columns then you must apply the style to each cell yourself. This is a restriction of the file format:

引用元：https://openpyxl.readthedocs.io/en/stable/styles.html#applying-styles

以降では、書式クラスからオブジェクトを生成(コンストラクタ)する際に指定する引数条件と規定オプションの解説をしていきます。

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

セル内の値(数値や文字)の配置揃えに関する設定(水平方向の揃え・垂直方向の位置・折り返しなど)は、Alignmentクラス から生成するAlignmentオブジェクトで定義・管理されます。

それを対象のCellオブジェクトの alignmentプロパティ で設定することで、対応できます。引数にはさまざまなものがありますが、主なものを示します。

セルの書式設定ウィンドウ（配置Tab）で設定することができる項目はほぼカバーされています。図3は、それぞれの引数がどのUI項目に対応しているかを対比しています。

引数horizontal（水平方向の揃え）や 引数:vertical (高さ方向の位置)の設定値は、以下のオプション定数の中から選択します。(図4)

その他、回転や表示形式(文字サイズの自動調整・セル内折り返し)などを指定できる、

引数:text_rotation、引数:wrap_text、引数:shrink_to_fit などがあります。各引数は、複数組み合わせて指定することも可能です。

【SAMPLE(1)】

ここで、Alignmentオブジェクト による、セルの配置・揃えの設定例をサンプルコードで確認してみましょう。<List1>

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

[A]では、引数:horizontal にオプション定数を指定してセル内の水平揃え位置を変更しています。

[A]-➁の‘justify’, ‘distributed’のように全体表示の指定もできます。

[B]では、引数:vertical にオプション定数を指定して垂直方向の位置を調整しています。

そして[C]では 引数:textRotation, 引数:shrinkToFit, 引数:wrapText にてそれぞれ、文字列の回転角度と自動調整(シュリンク・セル内折り返し)の指定をしています。

※[Ａ]などの記号は、コード内のコメントに対応しています。

このプログラムで読み込むExcelブックは以下からダウンロードできます。

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

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

# [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)の設定をした結果となります。

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

このプログラムの実行結果は以下からダウンロードできます。

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

セルごとに編集の可否を設定する(保護する)ことができます。セルを保護するためには、stylesモジュールの Protectionクラス から生成する Protectionオブジェクト で定義・管理します。

適用するには、対象のCellオブジェクトの protectionプロパティ にて設定します。

セルの書式設定ウィンドウ（保護Tab）で設定することができる項目に対応します。図6は、UI項目との対応関係を示しています。(説明の必要はありませんが、、)

なお、Protectionオブジェクト でセルの保護の設定を行ったとしても、上位レイヤーである Worksheetオブジェクト もしくは、Workbookオブジェクト の保護機能を有効にしない限り設定は反映されませんので注意してください。

図7はシートの保護ウィンドウ(校閲タブ->保護グループ->シートの保護)です。この中の「シートとロックされたセルの内容を保護する(C)」にチェックを入れることで保護が適用されます。

シートレイヤーの保護設定については、worksheet.protectionモジュールが提供する、SheetProtectionクラス で定義します。本記事では、解説を割愛していますので、必要に応じて以下の公式ドキュメントを参照、もしくは手動での設定をお願いします。(このあとのサンプルコードでも解説します。)

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

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

【SAMPLE(2)】

ここで、「Protectionオブジェクト」による、セルを保護する具体例をサンプルコード<List2>で確認してみます。

先述したとおり、セルの保護を有効にするには上位レイヤーにあたる「ブック」もしくは「シート」の保護も有効にする必要があります。

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

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

そして、最後に[C]で、セルの書式設定(保護)を定義した Protectionオブジェクト を各々のCellオブジェクトに適用させます。「Protectionオブジェクト」の引数lockedは “False” (デフォルトはTrue：有効)とし保護を解除しています。

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セルの範囲は指定通りに保護対象から外れ編集できることが確認できました。その他のセルは、デフォルトで保護が有効になっているので編集されません。

このプログラムの実行結果は以下からダウンロードできます。

1.3 表示形式(Number_Format)の設定

ユーザが定義した表示形式をセルに設定できます。これまでの書式設定の方法とは異なり専用のクラスからオブジェクトを定義・生成する必要はありません。

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

表示形式は、以下のような表記記号を組合わせて自由にカスタマイズができます。

また、図9の「ユーザ定義」にあらかじめ定義されている、表示記号一覧は「styles.numbersモジュール」内のコードで図11にように辞書形式で定義されています。

キー(Key)であるインデックスを、builtin_format_code() の引数に指定することで、各書式にアクセスできます。実際の使い方は、この後のサンプルコードで確認します。

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

https://openpyxl.readthedocs.io/en/stable/_modules/openpyxl/styles/numbers.html#NumberFormat

【SAMPLE(3)】

それでは、表示形式「Number_Format」の適用例をサンプルコード<List3>で確認してみましょう。

表示形式は、[A]のようにユーザーが表記記号を組合わせた文字列で、適用させることができます。(このあたりはExcelの操作と同様です。)あるいは、[B]のように、あらかじめ組込まれた表示形式のスタイル一覧(図11)から選択し、「builtin_format_code()」を経由して設定することもできます。

その他、「styles.numbersモジュール」には、表示形式に関するいくつかのメソッドが提供されています。 たとえば、組込み書式の可否を確認する is_builtin()や日付書式型の可否を確認するis_date_format() 、日時の型を確認する is_datetime() などのメソッドがあり、冒頭でインポートしています。[C]

このプログラムで読み込むExcelブックは以下からダウンロードできます。

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

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

以上が、個別の「セルの書式」設定に関する解説になります。以降、第2節からは、「スタイル(Style)」による書式の適用例を紹介します。

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

ここまで、セルの書式設定として「フォント」「塗りつぶし」「罫線」などを個別に設定する方法について解説してきました。Excelには、セルの書式・装飾に関して「スタイルを適用する」という考えかたがあります。

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

一度、スタイル(“デザインの型”)を定義してしまえば、そのスタイルは何度でも再利用ができます。つまり、書式設定のオブジェクトの定義・取得を都度、繰り返す必要はなくなります。

スタイルには、「組込みスタイル」と「ユーザ定義スタイル」の2通りがあります。

2.1 組込みスタイル(Builtin-Style)の適用

はじめに、Excel内であらかじめ定義されている 「組込みスタイル(Builtin-Style)」を適用する方法についてです。組込みスタイルは、Cellオブジェクトの styleプロパティ に「‘スタイルの定義名’」(文字列)を設定することで適用できます。

“スタイルの定義名”の部分には、図13のようなカテゴリ分けされた文字列から選択できます。(Excelのホームタブ -> スタイルグループのスタイル一覧から確認できます)

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

スタイルには、組込みスタイル(Builtin-Style)だけではなく、ユーザーが独自にスタイルの書式を定義(カスタマイズ)できる「ユーザー定義スタイル」があります。

「ユーザー定義スタイル」は、次の書式をもつ NamedStyleクラス から、オブジェクトを定義・生成する必要があります。

引数:name には、追加するスタイル名を指定します、その他、「font」「fill」などの引数には、これまでに解説してきた、個別の書式オブジェクト(Font/Fillオブジェクト)を渡します。引数指定を省略した場合は、“None”つまりデフォルト値が継承されます。

NamedStyleオブジェクトは、「組込みスタイル」同様に styleプロパティ で適用します。

「ユーザ定義スタイル」は、図13のようにExcel UIのスタイル一覧に(ホームタブ -> スタイルグループ)に登録もできます。登録は、Workbookオブジェクトの add_named_style()メソッド で行います。

これにより、「ユーザ定義スタイル」を「組込みスタイル」のように、スタイル名で扱うことができるようになります。

【SAMPLE(4)】

それでは、「ユーザ定義スタイル」を作成してセルに適用する例をサンプルコードで<List4>で確認してみましょう。

コードの概要について解説します。

[A]では、NamedStyleオブジェクトを定義しています。引数:name には、スタイル名 “SampleStyle”を設定し、その他の引数(font, fill, alignment)には、個々の書式オブジェクトを渡します。

[B]では、[A]のNamedStyleオブジェクトを、UIのスタイル一覧に登録するために、add_named_style() を実行します。

そして[C]の styleプロパティ で対象セルにユーザー定義スタイルを設定します。既に[B]で登録済みですので、「スタイル名 (“SampleStyle”)」での指定ができますし「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の実行結果は図15のようになりました。

新しく定義したスタイルが一覧に登録・表示され、“B2”“B4”セルにスタイルを適用できました。

このプログラムの実行結果は以下からダウンロードできます。

以上が、セルにスタイルを適用する方法の解説となります。

3. まとめ

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

今回は、Excelを操作する「openpyxl」ライブラリを取り上げ、セルに書式設定(配置・保護・表示形式)をする方法、さらに各種書式をまとめて管理できる「スタイル」について紹介しました。

シートやセルの見栄えを整えることは、大変重要です。今回と前回記事で紹介したセルの書式設定に関する操作は必ず行うことになるでしょう。ぜひ、今回の記事内容をご理解頂き活用してみて下さい。

また、書式設定に関するクラスには、今回紹介しきれなかったオプショナル引数やメソッド・属性など機能がたくさんあります。公式ドキュメント「openpyxl.stylesモジュール」の頁を参考にして目当ての機能がないか探してみてください。

最後に、この記事の要点をまとめます。

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

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

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

【Python×Excel】openpyxlで条件付き書式(データバー・アイコンセット・カラースケール)を設定する方法【前編】

「openpyxlライブラリ」にて、Excelの「条件付き書式」を操作する方法を解説します。本記事では「データバー・アイコンセット・カラースケール」の適用例を図解を用いて紹介します。

www.shibutan-bloomers.com

2021.06.20

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

Japanese | English