Pythonでエクセル表作成【進捗管理表編】

はじめに

自分用に簡易な進捗管理表を作成してて、ふと思いました。
・・・これ毎回作るのめんどくさくない？？

てなわけで、pythonの勉強がてらに起動したらちゃっちゃと表を作成してくれるコードを書いてみました。

↓出来上がりはこんな感じになります

では部分ごとに説明していきます。

ファイル作成

import openpyxl.utils
import os

from openpyxl.styles import PatternFill
from openpyxl.styles.borders import Border, Side
from openpyxl.formatting.rule import FormulaRule

wb = openpyxl.Workbook()
sheet = wb.active
sheet.title = '進捗管理表'

まずはエクセルファイルを作成します。
openpyxl.Workbook()で新規ファイルを作成します。activeは現在選択中のシートを対象にしています。ファイル立ち上げ時なのでsheet1を選んでいる状態です。titleで選択中のシートの名称を変更できます。

セルのサイズ設定

def cellwidth(start, end, wide):
    for length in range(start, end):
        alpha = sheet.cell(row=1, column=length).coordinate[:-1]
        sheet.column_dimensions['{}'.format(alpha)].width = wide


cellwidth(1, 7, 8.38)
cellwidth(2, 3, 17.38)
cellwidth(8, 9, 0.38)
cellwidth(9, 335, 4.88)

for rows in range(1, 100):
    sheet.row_dimensions[rows].height = 19

colomn_dimensions['指定column'].widthで列の幅, row_dimensions['指定row'].heightで行の高さを設定できます。
各々指定したかったので関数にしてfor文を使用しました。
columnがわかりずらいですがA=1,B=2,C=3・・・と変換されています。
（例：cell(row=1, column=2)だったらB1）
指定columnはアルファベットを入れたかったため、cell(row= , column= ).coordinateでセルの位置を取得し、[:-1]で頭から最後-1文字を取得しています。（例：セルのAAA1だったらAAAを取得する)
行幅は今回一律にしているので関数にしていません。

枠線

side1 = Side(style='thin', color='000000')
side2 = Side(style='double', color='000000')
side3 = Side(style='hair', color='000000')

for rows in sheet.iter_rows(min_row=1, min_col=1, max_row=100, max_col=9):
    for cell in rows:
        if cell.row == 5:
            cell.border = Border(right=side1, bottom=side2)
        else:
            cell.border = Border(top=None, right=side1, bottom=None, left=None)

for rows in sheet.iter_rows(min_row=1, min_col=9, max_row=100, max_col=335):
    for cell in rows:
        if cell.row == 5:
            cell.border = Border(right=side3, bottom=side2)
        else:
            cell.border = Border(top=side3, right=side3, bottom=side3, left=side3)

Side(style='枠線のスタイル', color='色')で枠線を定義します。
Border(top= , right= ,bottom= , left= )でセルの上下左右の枠線を設定できます。
ここではfor文を使ってmin～maxで指定した範囲それぞれのセルに枠線を設定してます。
5行目だけはの枠の下に線を入れたかったのでif文で分岐させています。
topとbottom、rightとleftを別の種類の線にしてしまうと、隣接した部分が後から変更された枠線で上書きされてしまうので注意が必要です。

数値や式の挿入

startYear = 2020
startMonth = 3
startDay = 1

sheet["B5"].value = "タスク"
sheet["C4"].value = "着手日"
sheet["C5"].value = "予定"
sheet["D5"].value = "実績"
sheet["E4"].value = "完了日"
sheet["E5"].value = "予定"
sheet["F5"].value = "実績"
sheet["G5"].value = "備考"
sheet["I2"].value = startYear
sheet["J2"].value = "年"
sheet["I3"].value = startMonth
sheet["J3"].value = "月"

sheet["I4"].number_format = "d"
sheet["I4"].value = '=DATE(I2, I3, {})'.format(startDay)
sheet["I5"].number_format = "aaa"
sheet["I5"].value = '=I4'

for rows in sheet.iter_rows(min_row=4, min_col=10, max_row=5, max_col=334):
    for cell in rows:
        if cell.row == 4:
            cell.number_format = 'd'
            cell.value = '={}+1'.format(sheet.cell(row=cell.row, column=cell.column-1).coordinate)
        else:
            cell.number_format = 'aaa'
            cell.value = '={}'.format(sheet.cell(row=cell.row-1, column=cell.column).coordinate)

for rows in sheet.iter_rows(min_row=3, min_col=11, max_row=3, max_col=334):
    for cell in rows:
        cell.number_format = "m"
        cell.value = '=IF(DAY({0})=1, {1},"")'.format(sheet.cell(column=cell.column, row=cell.row + 1).coordinate,
                                                      sheet.cell(column=cell.column, row=cell.row + 1).coordinate)

セル.valueで指定セルに値が入ります。式を入れたい場合は、'=数式'で。
単なる計算式を入れてしまうと計算結果が入るだけなので気を付ける必要がありますね。
あとnumber_formatで表示形式の変更が出来ます。表示形式はエクセルで実際に見てみるか、print(セル.number_format)で調べるといいと思います。
ちなみに、dなら日付、aaaは曜日です。
始まり月を決めてそこから日付を足していって、1日だったら月も表示するようにしています。
これでざくっと11ヶ月分の月、日、曜日を入れています。

条件付き書式

def colorMake(types, start, end):
    return PatternFill(fill_type=types, start_color=start, end_color=end)

grayfill = colorMake('solid', 'd3d3d3', 'd3d3d3')
goldfill = colorMake('solid', 'ffd700', 'ffd700')
bluefill = colorMake('solid', '1e90ff', '1e90ff')

for rows in sheet.iter_rows(min_row=6, min_col=2, max_row=100, max_col=7):
    for cell in rows:
        cell.number_format = "m/d"
sheet.conditional_formatting.add('C6:G100', FormulaRule(formula=['NOT($F6="")'], stopIfTrue=True, fill=grayfill))

sheet.conditional_formatting.add('I4:ME100', FormulaRule(formula=['OR(WEEKDAY(I$5)=1, WEEKDAY(I$5)=7)'], stopIfTrue=True, fill=grayfill))

sheet.conditional_formatting.add('I6:ME100', FormulaRule(formula=['AND($D6<=I$4, $F6>=I$4)'], stopIfTrue=True, fill=goldfill))
sheet.conditional_formatting.add('I6:ME100', FormulaRule(formula=['AND($C6<=I$4, $E6>=I$4)'], stopIfTrue=True, fill=bluefill))

選択シート.conditional_formatting.add('適用セル範囲', FormulaRule(formula=[適用条件], stopIfTrue=True or False, fill=色))で条件を記述順に設定できます。
これめっちゃ躓いたんですが、formula=[適用条件]で条件式の中身は=で始めてはいけません。エラーは出ないけど、エクセルに条件付き書式が設定されてなくて、なんで？？？ってなります。
stopIfTrueは条件付き書式の「条件を満たす場合停止」です。エクセル使いこんでないとそもそも何それ？って感じですが・・・
あとfillで条件を満たした場合のセルの色を設定してます。
PatternFill(filltype=塗りつぶしパターン, start_color= , end_color= )
グラデーション使わないならstart_colorとend_colorは同じでいいでしょう。

保存

desktop_path = os.getenv("HOMEDRIVE") + os.getenv("HOMEPATH") + "\\Desktop"
wb.save(desktop_path + '\\進捗管理表.xlsx')

最後です。
ワークブック.save(ファイル名)で保存できます。これがないとどれだけ作り込んでも保存されないので注意してください。
今回は、デスクトップまでの絶対パスを指定して保存しています。ファイル名だけだったらカレントディレクトリに保存されます。

終わりに

今回はセルの範囲とかが固定値すぎるので、変数用のpropatyファイルとか作って、そこの数値いじれば好きにカスタマイズ出来るようにすれば自由度が上がりそうです。
色々な要素が詰まってたので結構勉強になりました。（小並感）

全体コード

import openpyxl.utils
import os

from openpyxl.styles import PatternFill
from openpyxl.styles.borders import Border, Side
from openpyxl.formatting.rule import FormulaRule

wb = openpyxl.Workbook()
sheet = wb.active
sheet.title = '進捗管理表'


def cellwidth(start, end, wide):
    for length in range(start, end):
        alpha = sheet.cell(row=1, column=length).coordinate[:-1]
        sheet.column_dimensions['{}'.format(alpha)].width = wide


cellwidth(1, 7, 8.38)
cellwidth(2, 3, 17.38)
cellwidth(8, 9, 0.38)
cellwidth(9, 335, 4.88)

for rows in range(1, 100):
    sheet.row_dimensions[rows].height = 19

side1 = Side(style='thin', color='000000')
side2 = Side(style='double', color='000000')
side3 = Side(style='hair', color='000000')

for rows in sheet.iter_rows(min_row=1, min_col=1, max_row=100, max_col=9):
    for cell in rows:
        if cell.row == 5:
            cell.border = Border(right=side1, bottom=side2)
        else:
            cell.border = Border(top=None, right=side1, bottom=None, left=None)

for rows in sheet.iter_rows(min_row=1, min_col=9, max_row=100, max_col=335):
    for cell in rows:
        if cell.row == 5:
            cell.border = Border(right=side3, bottom=side2)
        else:
            cell.border = Border(top=side3, right=side3, bottom=side3, left=side3)

startYear = 2020
startMonth = 2
startDay = 1

sheet["B5"].value = "タスク"
sheet["C4"].value = "着手日"
sheet["C5"].value = "予定"
sheet["D5"].value = "実績"
sheet["E4"].value = "完了日"
sheet["E5"].value = "予定"
sheet["F5"].value = "実績"
sheet["G5"].value = "備考"
sheet["I2"].value = startYear
sheet["J2"].value = "年"
sheet["I3"].value = startMonth
sheet["J3"].value = "月"

sheet["I4"].number_format = "d"
sheet["I4"].value = '=DATE(I2, I3, {})'.format(startDay)
sheet["I5"].number_format = "aaa"
sheet["I5"].value = '=I4'

for rows in sheet.iter_rows(min_row=4, min_col=10, max_row=5, max_col=334):
    for cell in rows:
        if cell.row == 4:
            cell.number_format = 'd'
            cell.value = '={}+1'.format(sheet.cell(row=cell.row, column=cell.column-1).coordinate)
        else:
            cell.number_format = 'aaa'
            cell.value = '={}'.format(sheet.cell(row=cell.row-1, column=cell.column).coordinate)

for rows in sheet.iter_rows(min_row=3, min_col=11, max_row=3, max_col=334):
    for cell in rows:
        cell.number_format = "m"
        cell.value = '=IF(DAY({0})=1, {1},"")'.format(sheet.cell(column=cell.column, row=cell.row + 1).coordinate,
                                                      sheet.cell(column=cell.column, row=cell.row + 1).coordinate)


def colorMake(types, start, end):
    return PatternFill(fill_type=types, start_color=start, end_color=end)


grayfill = colorMake('solid', 'd3d3d3', 'd3d3d3')
goldfill = colorMake('solid', 'ffd700', 'ffd700')
bluefill = colorMake('solid', '1e90ff', '1e90ff')

for rows in sheet.iter_rows(min_row=6, min_col=2, max_row=100, max_col=7):
    for cell in rows:
        cell.number_format = "m/d"
sheet.conditional_formatting.add('C6:G100', FormulaRule(formula=['NOT($F6="")'], stopIfTrue=True, fill=grayfill))

sheet.conditional_formatting.add('I4:ME100', FormulaRule(formula=['OR(WEEKDAY(I$5)=1, WEEKDAY(I$5)=7)'], stopIfTrue=True, fill=grayfill))

sheet.conditional_formatting.add('I6:ME100', FormulaRule(formula=['AND($D6<=I$4, $F6>=I$4)'], stopIfTrue=True, fill=goldfill))
sheet.conditional_formatting.add('I6:ME100', FormulaRule(formula=['AND($C6<=I$4, $E6>=I$4)'], stopIfTrue=True, fill=bluefill))

desktop_path = os.getenv("HOMEDRIVE") + os.getenv("HOMEPATH") + "\\Desktop"
wb.save(desktop_path + '\\進捗管理表.xlsx')