openpyxl備忘録(Excel書式設定)

pandasのDataframeをto_excelでいったんExcelにして次にopenpyxlで書式設定をするという流れで行きたいと思います。
基本形はこちらです。

import pandas as pd
import openpyxl
from openpyxl.styles.borders import Border, Side

ae=pd.read_excel("Adverse Event.xlsx",sheet_name="AE")

###################################
#この中でさまざまなデータ加工を行う
##################################

ae.to_excel("adverse event2.xlsx",sheet_name="AE",index=False)


#上で作ったExcelをopenpyxlとして読み込む
wb=openpyxl.load_workbook("Adverse Event2.xlsx")
ws=wb["AE"]

###################################
#この中でさまざまな書式設定を行う
##################################

wb.save("Adverse Event3.xlsx")

今はこのようにpandasで作られたExcelの姿そのままです。openpyxlは元の書式を継承できるのでその点ではpandasより優れています。

まずは罫線を引いてみる

openpyxl.stylesライブラリのBorderクラスとSideクラスを使います。
Sideクラスで、線の種類(style)と色(color)を定義します。ここでは4種類作ってみます。

line1=Side(style='thin', color="00000000")#極細実線黒
line2=Side(style='medium', color="00FF0000")#普通の実線赤
line3=Side(style='thick', color="0000FF00")#極太の実緑線
line4=Side(style='mediumDashed', color="000000FF")#太い破線青

borderクラスで、上下右左それぞれの書式を定義します。

border=Border(top=line1,bottom=line2,left=line3,right=line4)

B3にこれを設定します。

ws["B3"].border=border

このようになります。

全体に罫線をかける

先ほどのline1の定義をborderに設定します。
ws(ワークシート)を行ごとに呼び出し、さらにそれをセル単位でループさせて1セルごとに罫線をつけていきます。

line1=Side(style='thin', color="00000000")#極細実線黒

border=Border(top=line1,bottom=line1,left=line1,right=line1)

for row in ws:
    for cell in row:
        ws[cell.coordinate].border = border

うまくいきました。coordinateというのはExcelのセル番地を数値表記から記号表記に変換する関数です。(1,1) --> (A1)のような変換です。

Window枠の固定

Window枠の固定は簡単です。

ws.freeze_panes = 'B2'   

B2で固定すると常にA列と1行目が固定されます。

B1とすればA列が固定、A2とすれば1行目が固定されます。

フォント設定

全体的にフォントを変えてみます。フォント設定にはFontクラスが必要となります。

from openpyxl.styles import Font

変化がわかりやすいように、"g_えんぴつ楷書-教漢版"に変えます。

 #フォント 
font = Font(name='g_えんぴつ楷書-教漢版')

# write in sheet
for row in ws:
    for cell in row:
        ws[cell.coordinate].font = font

for loopは罫線でもやっているので、実際にはその中で一緒に回します。

文字書式

文字色を変えるには、先程のfontの定義に、color="色"を指定します。
ちなみにカラーコードはこちらを参照します。
カラーコード表

 #フォント 
font = Font(name='g_えんぴつ楷書-教漢版', color='FF0000')

タイトル行と表の最初の列だけ別の書式にしてみます。
font1に書式を全部乗せしてみました。
太字、サイズ変更、斜体、アンダーラインを追加しています。

 #フォント 
font1 = Font(name='g_えんぴつ楷書-教漢版', color='000000',bold=True,size=18, italic=True,underline="double")
font2 = Font(name='g_えんぴつ楷書-教漢版', color='FF0000')

# フォントを表に適用
for row in ws:
    for cell in row:
        if cell.row==ws.min_row or cell.column==ws.min_column:
            ws[cell.coordinate].font = font1
        else:
            ws[cell.coordinate].font = font2

背景色

次に背景色を変えます。PatternFillというクラスをimportします。

from openpyxl.styles import PatternFill

そしてさきほどと同じようにLoopして適用します。

# 背景色を表に適用
for row in ws:
    for cell in row:
        ws[cell.coordinate].fill = fill

こんな感じに黄色くなりました。

次に1行飛ばしで色を塗ってみます。

簡単に行くと思ったら意外と苦労しました。結局はベタにlistを作るのが一番シンプルなのではないかという結論に達しました。
for i in range(ws.min_row+1, ws.max_row+1, 2):で一個飛ばしのリストを作ります。

# 列の行ごとに背景色を設定（1行飛ばし）
#1行飛ばしのリストを作る
row_list=[]
for i in range(ws.min_row+1, ws.max_row+1, 2):
    row_list.append(i)

for list in row_list:
    for row in ws.iter_rows():
        for cell in row:
            if cell.row == list:#listにあったら色付け
                cell.fill = PatternFill(fgColor='FFFF00',bgColor="FFFF00", fill_type = "solid")

いったんここまでを整理

長くなったのでいったんここまでを整理します。
pandasで加工し、いったんExcelに保存。それをopenpyxlで開いて、書式を設定していきます。
各種定義を行い、それらをCell単位で適用させていくという流れです。

import pandas as pd
import openpyxl
from openpyxl.styles.borders import Border, Side
from openpyxl.styles import Font
from openpyxl.styles import PatternFill

ae=pd.read_excel("Adverse Event.xlsx",sheet_name="AE")

###################################################
#この中でpandasを使って、さまざまなデータ加工を行う
##################################################
#いったんExcelに保存
ae.to_excel("adverse event2.xlsx",sheet_name="AE",index=False)


#上で保存Excelをopenpyxlとして読み込む
wb=openpyxl.load_workbook("Adverse Event2.xlsx")
ws=wb["AE"]

###################################
#さまざまな書式設定を行う
##################################

#Window枠固定        
ws.freeze_panes = 'B2' 

#########
#各種定義
#########

 #罫線 line1=Side(style='thin', color="00000000")#極細実線黒
border=Border(top=line1,bottom=line1,left=line1,right=line1)

 #フォント font1 = Font(name='g_えんぴつ楷書-教漢版', color='000000',bold=True,size=18, italic=True,underline="double")
font2 = Font(name='g_えんぴつ楷書-教漢版', color='FF0000')

#背景色とパターンの定義
fill = PatternFill(patternType='solid', fgColor='FFFF00')



for row in ws:
    for cell in row:
        #罫線を引く
        ws[cell.coordinate].border = border
        
        #フォント設定
        if cell.row==ws.min_row or cell.column==ws.min_column:
            ws[cell.coordinate].font = font1
        else:
            ws[cell.coordinate].font = font2


# 列の行ごとに背景色を設定（1行飛ばし）
#1行飛ばしのリストを作る
row_list=[]
for i in range(ws.min_row+1, ws.max_row+1, 2):
    row_list.append(i)

for list in row_list:
    for row in ws.iter_rows():
        for cell in row:
            if cell.row == list:#listにあったら色付け
                cell.fill = PatternFill(fgColor='FFFF00',bgColor="FFFF00", fill_type = "solid")

wb.save("Adverse Event3.xlsx")

一回ファイル保存しなければいけないという点が難点ではありますが、pandasはデータ加工に優れ、openpyxlは書式設定に優れているので、両方を活用することにしました。システム開発ではよくない方法ですが、業務改善が目的であれば、「できればOK」のポリシーでよいです。