OpenPyXLでExcelファイルに書き込む その3
今回でOpenPyxlを使った書き込みの最終にします。
スタイル設定などを書きます。
前回まで
OpenPyXLを使ったExcelの読み書き
OpenPyXLでExcelファイルを読み取る - よちよちpython
OpenPyXLでExcelファイルに書き込む その1 - よちよちpython
OpenPyXLでExcelファイルに書き込む その2 - よちよちpython
参考
OpenPyXL公式
openpyxl - A Python library to read/write Excel 2010 xlsx/xlsm files — openpyxl 2.6.2 documentation
作業環境
Qpython(python3.6)
QPyNotebook
termux(Android用ターミナルアプリ)
Python 3.7
vim 8.1(エディタ)
Pydroid3
python3.7
JupyterNotebook
目次
列幅指定設定
列幅を数字で指定し拡げたり狭めたり出来ます。
column_dimensions['列'].width = 列幅数字 を使います。
列幅の数字の単位は文字数だそうです。
シートには下の画像のようにデータが入っています。
import openpyxl file_name = "sample.xlsx" wb = openpyxl.load_workbook(file_name) sheet = wb.worksheets[0] sheet.column_dimensions['A'].width = 20 wb.save(file_name)
↑ Qpythonを使って実行。
A列の幅を指定したのに全ての列幅が同じように広がってしまいました。
Qpythonでは上手く行きませんでした。
上のコードで、列幅の数字を「20」としましたので20文字の幅になる筈。
A列には数字が入っています。20文字ほど入りそうではありますが、C列を見ると漢字とひらがなが20文字も入る幅ではないですね。
アルファベットの文字数が幅の基準にしてあるとかでしょうか。
日本語を入れた列幅の調整には一工夫いりそう。
↓ Pydroid3で実行してGoogleスプレッドシートで確認
Pydroid3ではちゃんとA列のみが幅調整されています。
行幅指定設定
行の幅は row_dimensions[行].height = 行幅数字 で調整できる。
行幅の数字の単位はポイントです。
sheet.row_dimensions[1].height = 60
Qpythonで実行しましたが、こちらも上手く行かず。
Android版ExcelもGoogleスプレッドシートも変化なし。
ならば今度は、ターミナルアプリtermuxとPythonを使って実行します。
vimでコードを書いて
termux上のpythonで実行後、スプレッドシートで確認。
今度は上手く行きました。
ちなみに、データの入っている範囲を知りたい場合は、
- 最終行 は
sheet.max_row - 最終列は
sheet.max_column
で取得できる。(「sheet」はシートオブジェクト。)
範囲を同じ幅にしたければ、ループで回して調整するのが定石か。 知らんけど…
行・列幅の自動調整
行と列の幅を自動で調整してくれる関数やメソッドは無さそうなので、作る必要がありそうです。
横着してリンクで済ます魂胆。
上のページのコードと同じものがstackoverflowのやり取りの中に書いてありましたので参考にされているのかも知れません。
こちらの手順としては大まかに、
- シートから列のリストを取得しfor文で各セルごとに処理
- 最大幅の初期値を0に設定
セルごとにfor文で処理
- セルの文字数をlenで取得し、初期値を超えればそれに2を足し、更にそれを1.2倍した数値を最大幅として置き換えを繰り返し、最終的に調整列幅とする。
column_dimensions["列"].width=調整列幅に代入
ちょっくら拝借致して写経し実行します。
columnを数字からアルファベットに変換するなど手直しして実行しました。画像なし。
上の列幅指定設定でも書きましたが、モジュールが日本語の幅を考慮されていない等の理由かどうかは分かりませんが、単純に文字数を数えて列幅調整をしても表示が隠れてしまう列が出ます。
セルの値に日本語などを含む場合は文字数のカウントを2倍にするとか、そういう操作がいるかも。
実装なし。
【スタイル】
セルや文字などの外観を決める設定を行います。
スタイルには次のようなものがあります。
- フォントサイズ、色、下線などを設定するフォント
- 塗りつぶしパターンまたは色のグラデーションを設定
- セルに境界線を設定するための境界線
- セルアライメント
- 保護
など。
フォントの指定
フォントの設定をするにはopenpyxl.styles.Font()を使います。
デフォルトでは次のような設定になっています。
font = openpyxl.styles.Font(name='Calibri', size=11, bold=False, italic=False, vertAlign=None, underline='none', strike=False, color='FF000000')
設定を変えたい所だけ書き換えれば、文字を太字にしたり色を変えたりできます。
セルA1を太字に変えてみます。
# セルA1を設定に書き換え sheet["A1"].font = openpyxl.styles.Font(bold=True) # 保存 wb.save(file_name)
太字に変更できました。
今度はセルB2の文字の色を変えます。
# 色の設定 font_color = openpyxl.styles.Font(color='ff0000ff') # セルB2を設定に書き換え sheet["B2"].font = font_color # 保存 wb.save(file_name)
セルB2の文字が青に変わりました。
下記を実行しますと、セルA1のフォントの設定が出力されます。
# セルA1の現在の設定 sheet["a1"].font
<openpyxl.styles.fonts.Font object>
Parameters:
name=None, charset=None, family=None, b=True, i=False, strike=None, outline=None, shadow=None, condense=None, color=None, extend=None, sz=None, u=None, vertAlign=None, scheme=None
セルの塗りつぶし
PatternFillはセルを塗りつぶして背景色を変えることが出来ます。
詳しくはこちら
公式 openpyxl.styles.fillsモジュール
適当に変えてみます。
文が長いのでimportの仕方も変えます。
from openpyxl.styles import PatternFill my_fill = PatternFill(patternType="darkHorizontal", start_color='FFFFFF08', end_color='FF000000') sheet["C3"].fill = my_fill wb.save(file_name)
↓ Excel。タイガースみたいになったw
設定が効かないパターンが他にもあるかも。
罫線
罫線、セルの枠線を設定します。
上下左右や線のタイプ、太さ、色など設定も豊富。
変数my_borderの中身は公式ドキュメントのコピペを一部書き換えまして、
これも適当に変えてみます。
from openpyxl.styles.borders import Border,Side my_border = Border(left=Side(border_style="thin", color='FF00FFFF'), right=Side(border_style="thin", color='FF00FFFF'), top=Side(border_style="thin", color='FF00FFFF'), bottom=Side(border_style="thin", color='FF00FFFF'), diagonal=Side(border_style=None, color='FF000000'), diagonal_direction=0, outline=Side(border_style=None, color='FF000000'), vertical=Side(border_style=None, color='FF000000'), horizontal=Side(border_style=None, color='FF000000') ) sheet["D4"].border = my_border wb.save(file_name)
セルD4が細い青枠で囲まれました。
セルA1の罫線が現在どのようになっているか確認してみます。
sheet["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
うおっ、たくさん。
罫線の設定だけでもこれだけある。
文字の表示位置と折り返し
alignmentでセルの折り返しや、上下左右の文字位置の設定が出来ます。
horizontalが左寄り、中央、右寄り。
verticalが上、中、下。
wrap_textで折り返し。
など。
セルE2を折り返してみる。
from openpyxl.styles import Alignment my_alignment=Alignment(horizontal='general', vertical='bottom', text_rotation=0, wrap_text=True, shrink_to_fit=False, indent=0) sheet["E2"].alignment = my_alignment wb.save(file_name)
セルE2が折り返されました。
それにより2行目の幅が自動的に拡がりました。
数字表示形式
number_formatで数値をテキストや日付、パーセント表示、小数点の位置を変えたりするときに使います。
詳しくはこちら
openpyxl公式 openpyxl.styles.numbers
セルH3の数字の表示形式を三桁こどにカンマを打つように変えてみます。
from openpyxl.styles import numbers sheet["H3"].number_format = '#,##0' wb.save(file_name)
セルH3に指定通りのカンマが入りました。 G列は数字が左寄せになっています。テキスト形式の数字です。 そちらも試しましたが入りませんでした。
テキスト形式にしたい時、既にモジュール側で準備してある'FORMAT_TEXT'を使うとできます。
セルH4をテキストに変えてみます。
sheet["h4"].number_format = numbers.FORMAT_TEXT
wb.save(file_name)
セルH4が左寄せに変わりました。 確認してみる。
# セルH4の形式 print(sheet['h4'].number_format) print("＝＝＝＝") # セルH3の形式は？ print(sheet['h3'].number_format)
@
＝＝＝＝
General
セルH4が@、セルH3はG/標準になっています。
おわりに
元々Excelの設定項目や機能が豊富なので、Pythonでそれをやるにも沢山の項目を押さえないといけません。しかし切りがないので、この辺りで一旦OpenPyxlによる書き込みは終了します。
新しいことをおぼえましたら追々追加していきます。
ありがとうございました。
