PythonでExcelを動かしてみよう #Python

##はじめに
SLPのアドベントカレンダー2020年12月17日の記事です
他の人の記事はこちらから見ることが出来ます
https://adventar.org/calendars/5402
何を作ろうか探している時にpythonでExcelを自動化という記事を見つけやってみようと思いました

##準備（インストール）
まずpyrhonをダウンロードします
私が説明するよりうまく説明されているサイトまたはyoutube動画があるのでそちらを参考にしてください
（今回エディタはVSCode（Visual studio code)を使用しています）
・【Python超入門コース】03.環境構築 for Windows
https://youtu.be/Cs7friXdqdM
・Visual Studio Code を使用して Python 初心者向けの開発環境をセットアップする
<https://docs.microsoft.com/ja-jp/learn/modules/python-install-vscode/

pythonの環境設定が出来たら次は今回使う追加ライブラリをインストールします
今回インストールする追加ライブラリは以下のものです
・pandas
・openpyxl
コマンドプロンプトに以下のように入力します

pip install pandas

pip install openpyxl

これで準備は完了です

##pyrhonを使ってExcelファイル内に書き込む
pythonを使ってExcelファイルを作りセルの中に書き込んでサンプルを作ります

excel sample.py

import openpyxl as px
import random

#　excelのシートの中のA1,B1,C1,D1にDay,Product,Place,Feeと書き込む
wb = px.Workbook()
ws = wb.active
sheet = wb.worksheets[0]
ws.title = 'sheet_1'
ws['A1'] = '月日'
ws['B1'] = '製品'
ws['C1'] = '支店'
ws['D1'] = '利益'

# B列に製品1と製品2のどちらかをランダム生成する
for k in range(2, 501):
    a = ('製品1', '製品2')
    ws['B'+ str(k)] = random.choice(a)

# C列に企業１と企業2のどちらかをランダム生成する
for m in range(2, 501):
    b = ('支店1', '支店2')
    ws['C'+ str(m)] = random.choice(b)

# D列に80～155の範囲の値の中から数字をランダムに生成する
for n in range(2, 501):
    ws['D'+ str(n)] = random.randint(10000, 100000)

#ランダムに生成する月日の範囲を2020-1-1～2020―12-17と決める
from datetime import timedelta 
from datetime import date
from random import randrange
import random
start_date = date(2020, 1, 1)
end_date = date(2020, 12, 17)

time_between_dates = end_date - start_date
days_between_dates = time_between_dates.days
random_number_of_days = random.randrange(days_between_dates)
random_date = start_date + timedelta(days=random_number_of_days)

 #A列に2020―1-1～2020―12―17の範囲でランダムに生成する
for p in range(2, 500):
    start_date = date(2020, 1, 1)
    end_date = date(2020, 12, 17)

    time_between_dates = end_date - start_date
    days_between_dates = time_between_dates.days
    random_number_of_days = random.randrange(days_between_dates)
    random_date = start_date + timedelta(days=random_number_of_days)

    ws['A'+ str(p)] = (random_date)
    #セルの大きさ調整
    sheet.column_dimensions['A'].width = 13
    
#変更内容を保存する   
wb.save('sample1.xlsx')

これを実行するとこのようなExcelファイルが生成されます

（今回作ったサンプルファイルは値をランダムに生成にしているので値は変わってきます）
次はこのサンプルファイルを使って操作していきます

##Excelファイルを種類ごとに分割、グラフの生成
前回の項目で作ったサンプルファイルを商品ごとにファイルを分割して、折れ線グラフを生成していきます

excel test.py

import pandas as pd
import openpyxl as px
from datetime import datetime
import os
 #Excelファイルを取り出す
Filepath = os.path.abspath('sample1.xlsx') 
 #Excelファイルをpandasで読み込む
df = pd.read_excel(Filepath)  
 #年-月-日から年-月に表示変更
df['月日'] = pd.to_datetime(df['月日']).dt.strftime("%Y-%m")
#それぞれの列で重複削除したリストを作成
D = list(df['月日'].unique()) 
P = list(df['製品'].unique())
F = list(df['利益'].unique())
Pl = list(df['支店'].unique())
 #現在の日付を取得
now = datetime.now()
hiduke = now.strftime('%Y-%m-%d')
 #製品ごとに分けたデータのシートを生成
for products in P: 
    filtered = df[df['製品'] == f'{products}']
    sales = pd.pivot_table(df, index=filtered['月日'], columns='支店', values='利益', aggfunc='sum', fill_value=0)
    #ファイル名（{hiduke}には現在の日付{products}には商品名）
    filepath = f'{hiduke}_{products}.xlsx' 
    sales.to_excel(filepath, sheet_name='月別総合利益', startrow=3)
    wb = px.load_workbook(filepath)
    #シート名の取得
    ws = wb['月別総合利益'] 
    
    #A1のセルに「{products}_売上」と記入
    ws.cell(row=1, column=1).value = f'{products}_売上' 
    #フォント設定
    ws.cell(row=1, column=1).font = px.styles.Font(size=12, bold=True) 
    #A2のセルに「月次売り上げ」と記入
    ws.cell(row=2, column=1).value = '月次売り上げ' 
    #フォント設定
    ws.cell(row=2, column=1).font = px.styles.Font(size=12, bold=True)

    #折れ線グラフの生成
    chart = px.chart.LineChart() 
    data = px.chart.Reference(ws, min_col=2, max_col=len(Pl)+1, min_row=4, max_row=len(D)+4)
    categories = px.chart.Reference(ws, min_col=1, max_col=1, min_row=5, max_row=len(D)+4)
    chart.add_data(data, titles_from_data=True)
    chart.set_categories(categories)
     #グラフ表示の大きさ、グラフの題名、グラフの単位を記入する
    chart.style = 14 
    chart.title = '製品売上'
    chart.y_axis.title = '円'
    chart.x_axis.title = '年月'
    chart.height = 9
    chart.width = 16
    #グラフの生成
    ws.add_chart(chart, "G2")
     #各ファイルの保存
    wb.save(filepath)

実行すると2020-12-17_製品1という名前ののExcelファイルと2020-12-17_製品2という名前のExcelファイルが生成されます。
（ファイル名の日付は生成した日にちですので生成される日付によって変わります）
二つのファイルの内容は以下のようになっています

また
chart = px.chart.LineChart()
の部分を
chart = px.chart.BarChart()
に変えると棒グラフが生成され
chart = px.chart.PieChart()
に変えると円グラフ¹が生成されるといった感じで1行変えるだけで色々なグラフを生成することが出来ます。

円グラフの場合x軸やy軸といった概念がないのでy軸の単位表示を意味するchart.y_axis.titleやx軸の単位表示を意味するchart.y_axis.titleを除かなければいけません
##まとめ
いかがだったでしょうか？
pythonを使うと実際Excelで操作するより早く正確な操作が出来て日常的にExcelを使用する人にとってとても便利だと思います
###最後に
pythonを初めて本格的に使ったことによりかなり苦戦しました（汗）
特に日付のランダム生成のところやグラフの生成のデータ取得の部分を理解するところに制作の大半の時間を掛けたと思います
今回はデータ分割とグラフ生成のみしかできなかったのでもっと効率よくなるようにしてみようと思います
####参考文献
PythonでOpenPyXLを使いExcelのグラフを作成する〜グラフのつくりかた編〜
https://tonari-it.com/python-openpyxl-graph-2/
Pythonでエクセルを読み込みPandasで解析してグラフ作成を自動化
https://fastclassinfo.com/entry/python_excel_charts/
datetime --- 基本的な日付型および時間型
https://docs.python.org/ja/3/library/datetime.html#aware-and-naive-objects
など ↩