Pythonで複数のエクセルファイルにグラフを追加

エクセル+Pythonでこんなことができますよ、と見せたら某会社から、こんなこともできますか？と言われたので今回は、それを実現してみます。複数のフォルダ（会社別）にあるエクセルファイルを読み込んで、そのエクセル内に売上データに従ってグラフを描くというものです。もちろん手作業でもできますが、会社が複数（多数）ある場合は、かなりの手間になります。100社あれば、それらの売上報告のグラフ生成が自動化できれば、かなりの時間削減になります。

これまでと同様に処理するエクセルデータと実行するPythonスクリプトは同じフォルダ（デスクトップ上にあるsampleフォルダ）にあるものとします。また、保存先のエクセルファイルもsampleフォルダになります。もし、保存先を変えたい場合はスクリプト（プログラム）中のファイルのファイル名（ファイルパス）を変更してください。

あと、処理するデータ（エクセルファイルの内容）は以下のようになっています。最初、下図のような形式で処理していたのですが、どうもうまくいかなかったので上図のようなデータにしています。openpyxlのドキュメントを眺めたりしたのですが挫折しました(後でAIに聞けばよかったのだと思い、CopilotとChatGPT4oでやってみました。結果は最後に載せておきます)。元のデータが処理しにくい形式の場合、一旦テンポラリとなるファイルやシートなどにデータをコピーし、それに対して処理するというのも良いかと思います。

• 上図

• 下図

売上データから棒グラフを描く

　まず、1社だけの場合からやってみます。ファイル名は2023.xlsxと固定することにします。いわゆる決めうち・ハードコーディングというやつですが、まずは動くところからやりましょう。これまでと同様にimportでopenpyxlライブラリを読み込むのに加えて以下のようにグラフ（棒グラフのみ）を描くためのライブラリも読み込ませておきます。

from openpyxl.chart import BarChart, Reference    

　棒グラフを描く場合、どの範囲のデータを棒グラフにするのかを指定する必要があります。グラフの範囲はReference()の最初のパラメーターにワークシート、二番目のパラメーターに参照範囲を指定します。range_string='2023!B2:D13'とするとシート名2023のB2からD13の範囲が対象になります。この範囲指定を変えれば好きな部分をグラフ化できます。

data = Reference(ws, range_string='2023!B2:D13')

参照範囲を決めたら次は棒グラフを生成します。以下のようにすると棒グラフが生成されます。ただし、この段階では生成されるだけで表示されません。

chart = BarChart()
chart.add_data(data)

棒グラフをワークシートに表示するには以下のようにします。

ws.add_chart(chart)

ここまでをまとめると以下のようになります。以下のプログラムは生成した棒グラフとともに2023g1.xlsという名前のファイルで保存されます。
コマンドラインから実行すると図のような結果になります。

import openpyxl
from openpyxl.chart import BarChart, Reference
wb = openpyxl.load_workbook('2023.xlsx')
ws = wb.worksheets[0]
data = Reference(ws, range_string='2023!B2:D13')
chart = BarChart()
chart.add_data(data)
ws.add_chart(chart)
wb.save('2023g1.xlsx')

棒グラフのスタイルやタイトルなどを設定する

　棒グラフが表示されたら次にグラフのスタイルとタイトルを変更してみます。グラフのスタイルはstyleに番号で指定します。スタイルは結構数多くあるので、いろいろ番号を入力して試してみるとよいでしょう。番号を変えて自動生成するようにプログラムを変更してみるとよいかもしれません。
　棒グラフのタイトルはtitleに文字列を指定します。ここまでをまとめると以下のようなプログラムになります。
　コマンドラインから実行すると図のような結果になります。

import openpyxl
from openpyxl.chart import BarChart, Reference
wb = openpyxl.load_workbook('2023.xlsx')
ws = wb.worksheets[0]
data = Reference(ws, range_string='2023!B2:D13')
chart = BarChart()
chart.style = 33
chart.title = '2023年 商品別売上'
chart.add_data(data)
ws.add_chart(chart,'A15')
wb.save('2023g2.xlsx')

X軸とY軸のタイトルを変更することもできます。この場合はx_axis.title、y_axis.titleに文字列を指定します。凡例を表示したくない場合hlegendにNoneを設定します。 プログラムは以下のようになります。コマンドラインから実行すると図のような結果になります。

import openpyxl
from openpyxl.chart import BarChart, Reference
wb = openpyxl.load_workbook('2023.xlsx')
ws = wb.worksheets[0]
data = Reference(ws, range_string='2023!B2:D13')
chart = BarChart()
chart.style = 33
chart.title = '2023年 商品別売上'
chart.x_axis.title = '月別'
chart.y_axis.title = '個数'
chart.legend = None
chart.add_data(data)
ws.add_chart(chart,'A15')
wb.save('2023g3.xlsx')

グラフの横幅、縦幅を指定するにはwidthとheightに数値を指定します。棒グラフ間の間隔を指定するにはgapWidthに間隔を示す数値を指定します。なお、以後、1列のみ（商品A）のみの棒グラフを表示するように変更しています。
プログラムは以下のようになります。棒グラフに限らず円グラフや折れ線グラフなど様々な形状にすることができます。今回の目的は複数の会社のエクセルファイルからグラフを自動生成することなので、グラフ生成に関してはここで終わります。

import openpyxl
from openpyxl.chart import BarChart, Reference
wb = openpyxl.load_workbook('2023.xlsx')
ws = wb.worksheets[0]
data = Reference(ws, range_string='2023!B2:B13')
chart = BarChart()
chart.style = 33
chart.title = '2023年 商品A売上'
chart.x_axis.title = '月別'
chart.y_axis.title = '個数'
chart.legend = None
chart.gapWidth = 20
chart.width = 12
chart.height = 12
chart.add_data(data)
ws.add_chart(chart,'A15')
wb.save('2023g4.xlsx')

コマンドラインから処理するファイルを指定する

　次にコマンドラインから処理するファイルと保存するファイル名を指定します。これまでと同様にコマンドラインからのデータを受け取るにはimport sysを指定し、sys.argv[1]でコマンドラインからのパラメーター（文字列）を受け取ります。
　先ほどのグラフを描かせるプログラムを変更すると以下のようになります。コマンドラインからは処理するエクセルファイル名（ファイルパス名）、その後に保存するファイル名（ファイルパス名）を指定します。
　以下のプログラムを実行すると2023.xlsxファイルを処理して2023g5.xlsxという名前のファイルで保存します。2023g5.xlsxを開くと棒グラフ付きのエクセルファイルになっています。

import sys
import openpyxl
from openpyxl.chart import BarChart, Reference
wb = openpyxl.load_workbook(sys.argv[1])
ws = wb.worksheets[0]
data = Reference(ws, range_string='2023!B2:B13')
chart = BarChart()
chart.style = 33
chart.title = '2023年 商品A売上'
chart.x_axis.title = '月別'
chart.y_axis.title = '個数'
chart.legend = None
chart.gapWidth = 20
chart.width = 12
chart.height = 12
chart.add_data(data)
ws.add_chart(chart,'A15')
wb.save(sys.argv[2])

複数のディレクトリ内にあるエクセルファイルをまとめて処理する

　それでは、いよいよ本番（？）です。複数のディレクトリ内にあるエクセルファイルをまとめて処理することにします。一般的なプログラムでは再帰処理を使いますが、UNIXコマンドでは非常に都合のいいコマンドがあります。それはfindコマンドです。findコマンドは以前にも取り上げているので詳しくは以下の記事を参照してください。

なんとなくコマンド 第24回 ファイルの検索（UNIX系OS）
https://news.mynavi.jp/techplus/article/natonakucommand-24/

まず、対象ディレクトリを変更します。sampleディレクトリ内にあるcompanyディレクトリに移動します。cd companyとすればカレントディレクトリが変更されます。このcompanyディレクトリ内にはA,B,Cディレクトリがあります。その中に2023.xlsxファイルがあります。この部分のファイル名は固定しています。

findコマンドでサブディレクトリ内にある2023.xlsxファイルを検索するには以下のように指定します。

find . -name '2023.xlsx'

findだとサブディレクトリ内も簡単に検索してくれるので便利です。あとは-execでプログラムを実行するだけです。が、心配な場合はechoを使ってチェックするとよいかもしれません。以下のようにするとechoにfindで検索された2023.xlsxのファイルパスが出力されます。

find . -name '2023.xlsx' -exec echo {} \;

今回処理するPythonのプログラムは処理するファイル名と保存するファイル名を指定します。つまり2つのパラメーターが必要になります。パラメーターが2つある場合は{}を2つ書きます。試しに以下のようにするとechoにfindで検索された2023.xlsxのファイルパスが2つ横並びで出力されます。

find . -name '2023.xlsx' -exec echo {} {} \;

次に実行するPythonプログラムをcompanyディレクトリ内に入れておきます。入れたら以下のコマンドを実行します。なお、以下のコマンドを実行すると元のファイルに上書き保存するので注意してください。もし、元データを変更したくない場合は、あらかじめディレクトリごとコピーして別の場所に保存しておいてください。

find . -name '2023.xlsx' -exec python eg5.py {} {} \;

2番目のパラメーターを変えれば別名で保存することもできます。以下のようにするとグラフ生成されたファイル名が2023.xlsxg.xlsxという名前でエクセルファイルが保存されます。

find . -name '2023.xlsx' -exec python eg5.py {} {}g.xlsx \;

Pythonプログラムを改良する

　「いや、そうじゃない。そのファイル名じゃない」という声が聞こえてきそうです。確かに先ほどの方法ではイマイチなファイル名です。そこで、今度は2023g.xlsxのようなファイル名にしてみます。この場合、findコマンドでファイル名ではなくディレクトリを検索するようにします。ディレクトリ検索の場合は-type dを指定します。以下のようにコマンドを入力するとディレクトリパスが表示されます。

find . -type d

ただし、このままディレクトリパスをPythonプログラムに渡すとエラーになってしまう場合があります。例えばカレントディレクトリにも2023.xlsxファイルがないとPythonプログラム側でエラーになってしまいます。
ここで、どちら側で対処するのがよいか？という問題が出てきます。存在しないファイルにコマンド側で対処するか、それともPython側で対処するか？ということです。今回はPython側のプログラムで対処することにします。多分、その方が楽だと思われるからです。というのもPythonにはファイルが存在するか調べる機能があるからです。Pythonではosモジュールにos.path.isfile()というのがあり、ファイルが存在すればTrue、そうでなければFlaseになります。今回はこれを利用します。
先ほどまでのPythonプログラムを修正します。追加行は2行だけですが、変更にともなうインデント（字下げ）には注意してください。実際のプログラムは以下のようになります。

import sys
import os
import openpyxl
from openpyxl.chart import BarChart, Reference
if os.path.isfile(sys.argv[1]):
  wb = openpyxl.load_workbook(sys.argv[1])
  ws = wb.worksheets[0]
  data = Reference(ws, range_string='2023!B2:B13')
  chart = BarChart()
  chart.style = 33
  chart.title = '2023年 商品A売上'
  chart.x_axis.title = '月別'
  chart.y_axis.title = '個数'
  chart.legend = None
  chart.gapWidth = 20
  chart.width = 12
  chart.height = 12
  chart.add_data(data)
  ws.add_chart(chart,'A15')
  wb.save(sys.argv[2])

プログラムを作ったらテストしてみましょう。まず、存在しないファイル名を指定します。実行すると特にエラーなく終了しています。問題なさそうです。

python eg6.py mynavi  

次に実際に存在するファイルを指定してみます。companyディレクトリ内にあるAディレクトリ内の2023.xlsxファイルを指定してみます。出力するファイルは2023g.xlsxとします。実行すると問題なく新たなファイルが作成され、棒グラフも生成されているのが確認できます。

python eg6.py A/2023.xlsx A/2023g.xlsx

それでは次にfindコマンドでテストしてみます。まずpythonプログラムを実行する前にechoでテストします。ファイル名の部分は引用符で囲んでおきます。以下のコマンドを実行すると期待通りの出力になっています。

find . -type d -exec echo "{}/2023.xlsx" "{}/2023g.xlsx" \;

あとはechoをpythonのプログラムを処理するように置き換えるだけです。以下のコマンドを実行すると指定した2023.xlsxファイルを処理し結果を2023g.xlsxファイルとして保存します。

find . -type d -exec python eg6.py "{}/2023.xlsx" "{}/2023g.xlsx" \;

より複雑な場合はシェルスクリプトを利用する方法もあります。また、Python側でプログラムを変更·工夫することで、いろいろ応用できるのではないかと思います。Windowsの場合はWSL2を使えば同じように処理することができるはずです。

冒頭に書きましたが、AIを使ってプログラムの悩みを質問してみました。AIに問題のあるPythonプログラムを提示して修正してもらいます。まずはCopilot。以下のような回答でした。生成されたプログラムは動作し図のような結果になりました。

質問：
Pythonでopenpyxlを使って以下のコードでグラフ化するとうまく表示されません

import openpyxl
from openpyxl.chart import BarChart, Reference
wb = openpyxl.load_workbook('t2023.xlsx')
ws = wb.worksheets[0]
data = Reference(ws, range_string='t2023!B2:M4')
chart = BarChart()
chart.add_data(data)
ws.add_chart(chart)
wb.save('t2023g1.xlsx')

Copilot

おっしゃる通り、openpyxlを使ってExcelワークシートにグラフを追加する際には、いくつかの設定が必要です。以下の修正を加えてみてください。

グラフの種類を指定するために、chart.typeを設定します。バー（縦棒）グラフを作成する場合は、chart.type = "col"とします。
データ範囲を正確に指定するために、Referenceオブジェクトを作成する際に、min_col、min_row、max_col、およびmax_rowを指定します。
以下は修正後のコードです。

import openpyxl
from openpyxl.chart import BarChart, Reference

# ワークブックを読み込む
wb = openpyxl.load_workbook('t2023.xlsx')
ws = wb.worksheets[0]

# データ範囲を指定
data = Reference(ws, min_col=2, min_row=1, max_row=4, max_col=13)

# バー（縦棒）グラフを作成
chart = BarChart()
chart.add_data(data)
ws.add_chart(chart, 'E5')

# ワークブックを保存
wb.save('t2023g1.xlsx')