投稿/コメントを表示します。

openpyxl で学ぶ python 変数の扱い方
重複しないリスト(辞書のキー)を作る

荏隈 直樹さんの投稿

(投稿ID: 5455)

"名称リスト"と"取引先ごとの明細"をいっぺんに書き出すコードを作成しました。
from pathlib import Path

import openpyxl

base_file_name = "slimmed_dict"
base_path = Path(__file__).resolve().parent
file_path = base_path / 'data' / f'{base_file_name}.xlsx'
save_path = base_path / 'data' / f'{base_file_name}_result.xlsx'

wb = openpyxl.load_workbook(file_path)
ws = wb["main"]

company_dict = {}
total_price = 0
for row in ws["A2:G" + str(ws.max_row)]:
    detail_dict = {
        "id": row[0].value,
        "name": row[1].value,
        "date": row[2].value,
        "fiscal": row[3].value,
        "doc": row[4].value,
        "detail": row[5].value,
        "price": row[6].value,
        "合計額": total_price,
    }
    if row[1].value not in company_dict:
        total_price = row[6].value
        detail_dict["合計額"] = total_price
        temp_companyname = row[1].value
    else:
        total_price += row[6].value
        detail_dict["合計額"] = total_price
    company_dict[temp_companyname] = detail_dict

ws_name = wb["名称リスト"]
ws_detail = wb["取引先ごとの明細"]
idx = 2
for company, transact in company_dict.items():
    ws_name["A" + str(idx)].value = company
    ws_name["B" + str(idx)].value = transact["合計額"]
    ws_detail["A" + str(idx)].value = company
    ws_detail["B" + str(idx)].value = transact["id"]
    ws_detail["C" + str(idx)].value = transact["date"]
    ws_detail["D" + str(idx)].value = transact["合計額"]
    ws_detail["E" + str(idx)].value = transact["detail"]
    ws_detail["F" + str(idx)].value = transact["fiscal"]
    ws_detail["G" + str(idx)].value = transact["doc"]
    idx += 1


wb.save(save_path)
wb.close()

print("完了しました")

2023/02/24 16:33