csvから稼働表を作成するためのスクリプト

kado_maker.py

import openpyxl as opx
import pandas as pd
import datetime as dt
import argparse


TEMPLATE_XLSX = "稼働表テンプレート.xlsx"
REGULAR_CLOSE_TIME = (dt.time(18, 30), dt.time(19, 0))
REST_TIME_REGULAR = dt.timedelta(hours=1)
REST_TIME_OVER = dt.timedelta(hours=1, minutes=30)
NAME = "中川恒"


def get_current_month():
    return str(dt.datetime.today().month) + "月"


def get_template_xlsx():
    return opx.load_workbook(TEMPLATE_XLSX)


def get_output_fname():
    return get_current_month() + "稼働表" + NAME + ".xlsx"


def str2datetime(str_array):
    return [dt.datetime.strptime(strt, '%H:%M:%S').time() for strt in str_array]


def parse_input_csv(input_csv):
    df = pd.read_csv(input_csv, engine="python")
    return str2datetime(list(df['出社時刻'])), str2datetime(list(df['退社時刻']))


#  NOTE: 9:00 より前に出社した場合のケースを考慮に入れる
def normalize_open_time(open_time):
    return [dt.time(10, 0) for _ in open_time]


def is_regular_close_time(t):
    return REGULAR_CLOSE_TIME[0] <= t <= REGULAR_CLOSE_TIME[1]


def normalize_close_time(close_time):
    return [REGULAR_CLOSE_TIME[0] if is_regular_close_time(t)
            else t
            for t in close_time]


def calc_rest_time(close_time):
    return [REST_TIME_REGULAR if t == REGULAR_CLOSE_TIME[0]
            else REST_TIME_OVER
            for t in close_time]


def write_col(ws, row_chr, col_start, col_end, data):
    for col, data_elem in zip(range(col_start, col_end + 1), data):
        key_str = row_chr + str(col)
        ws[key_str].value = data_elem


def write_to_xlsx(ws, open_time, close_time):
    ws['C4'] = dt.datetime.now().date()
    ws['C4'].number_format = 'yyyy年m'  # NOTE: もしかしたら不要かも

    row_chr_open = 'C'
    row_chr_close = 'D'
    row_chr_rest = 'E'
    col_idx_beg = 9
    col_idx_end = 39

    open_time = normalize_open_time(open_time)
    close_time = normalize_close_time(close_time)
    rest_time = calc_rest_time(close_time)

    write_col(ws, row_chr_open, col_idx_beg, col_idx_end, open_time)
    write_col(ws, row_chr_close, col_idx_beg, col_idx_end, close_time)
    write_col(ws, row_chr_rest, col_idx_beg, col_idx_end, rest_time)


def main(input_csv):
    start_time, end_time = parse_input_csv(input_csv)

    wb = get_template_xlsx()

    ws = wb.active
    ws.title = get_current_month()

    write_to_xlsx(ws, start_time, end_time)

    output_xlsx = get_output_fname()
    wb.save(output_xlsx)

    print(output_xlsx + "が作成されました。")


if __name__ == "__main__":
    parser = argparse.ArgumentParser(description="稼働表をインポートするスクリプト")
    parser.add_argument('-i', '--input', dest='input_csv', required=True, nargs=1,
                        type=str, metavar="input_csv", help="入力csvファイル")
    args = parser.parse_args()
    main(args.input_csv[0])