[Python] openpyxlでExcelの作業を自動化する

スポンサーリンク

この記事ではPythonでExcelファイルを操作できるopenpyxlを簡単に紹介します。
Excelの作業を自動化するツールとしてExcel VBAがよく候補になります。
Pythonでもopenpyxlというライブラリを用いれば、Excelの作業を自動化することができます。PythonでExcelの作業を自動化するメリットとして、Pythonのデータ処理に長けた強力なライブラリ等の多くのライブラリを利用できる点やPythonの強力な表記方法を利用できる点があります。また、Pythonのプログラミング言語として学びやすく、実用面としても用途によっては十分にVBAの代わりができます。

この記事で、利用しているOS・Python・openpyxlのバージョンは以下の通りになります。

  • Windows 10
  • Python 3.8.5
  • openpyxl 3.0.5

 

スポンサーリンク

openpyxlのインストール

pip installでopenpyxlを指定することで簡単にインストールできます。

 

 

 

Excelファイルの作成

ワークブックの作成はopenpyxlからWorkbookをインポートし、Workbook()でWorkbookオブジェクトを作成できます。
Workbookオブジェクトからcreate_sheet()を用いることでワークシートの作成できます。
また、Workbookオブジェクトのcopy_worksheet()を用いることでワークシートをコピーできます。
Worksheetオブジェクトのtitleプロパティを変更することでシート名を変更できます。

Workbookオブジェクトのsave()を用いることで、Excelファイルの保存ができます。

以下のコードはopenpyxlのチュートリアルとほぼ同じになります。

 

実行すると以下のようにワークシートが作成されます。

 

また、print文では以下のように、ワークシートの名前が出力されます。

 

 

 

データの挿入

ワークシートのセルへのデータ挿入はいくつか方法があります。

例えば、Worksheetオブジェクトの変数wsに対して、
* ws["A1"]のようなExcelのように指定して代入する方法
* ws.cell(行の位置,列の位置,値)で値をセットする方法
があります。

 

データが挿入されたExcelファイルは以下のようになります。

 

 

 

データのロード

既存のExcelファイルをロードするにはload_workbookをインポートしてから、Excelファイルのファイルパスを指定します。

 

ロードされるExcelファイルは以下のようなものを利用します。

 

Excelファイルがロードされると、print文で以下のように出力されます。

 

 

 

画像の挿入

画像の挿入をしたい場合は、openpyxl.drawing.imageからImageをインポートします。
Image()で挿入したい画像のファイルパスを指定します。画像の大きさはheightやwidthのプロパティで変更できます(px単位)。
その後に、Worksheetオブジェクトからadd_image()で画像を挿入できます。

 

実行すると以下のような画像が挿入されたExcelファイルを作成できます。

 

 

 

グラフの挿入

グラフを挿入したい場合はopenpyxl.chartから利用したいグラフをインポートします。
ここでは棒グラフを使用するため、BarChartをインポートします。
また、範囲を参照するためにReferenceもインポートします。

Reference(range_string=)を利用するとworksheet名から始まるExcelで利用する範囲の文字列で、範囲の参照を行うことができます。
Reference(worksheet=ws,min_col=,max_col,min_row,max_row)を利用するとWorksheetオブジェクトで範囲における列の最小と最大の列番号と行の最小と最大の行番号を指定して、範囲の参照を行うことができます。(表の中で番号はそれぞれ1から始まります。1行目の番号は1、1列目の番号は1)

その後、グラフオブジェクトのadd_data()で値を入れ、Worksheetオブジェクトのadd_chart()で指定した場所にグラフを入れることができます。

グラフが挿入されたExcelファイルは以下のようになります。

 

 

 

openpyxlでPandasを利用

Pythonには、データ処理のライブラリとして、Pandasがあります。openpyxlではPandasのデータフレームを利用することができます。

pandasのインストールは以下のコマンドでできます。

Pandasを利用するためにpandasをインポートします。
pandasのデータフレームをExcelの行に変換するために、openpyxl.utils.dataframeからdataframe_to_rowsをインポートします。

ここでは、dataframe_to_rows()でヘッダー行の後に空行が出てくるのが気になったため、if(row != [None])で空行を抜く処理を入れています。

さらにopenpyxlでセルのスタイルで"Pandas"というスタイルがあるため、ヘッダー行とインデックス列を"Pandas"というスタイルに変更しています。

 

実行してExcelファイルを作成すると以下のようになります。

 

 

 

openpyxlで簡単なアンケートデータの集計を行なってみる

もう少し実用的な例として、アンケートデータ集計のExcelファイルを作成したいと思います。
入力データとして、以下のような性別と年齢と都道府県があるcsvファイルを用意して、

後から、選択肢が4つある、3つのアンケートにランダムに答えたと仮定したデータをつけ足して、アンケートデータとします。

出力するExcelファイルには、

  • アンケートデータのシート
  • アンケート1の集計結果のシート
  • アンケート2の集計結果のシート
  • アンケート3の集計結果のシート

を作成します。

アンケートの集計結果として、

  • それぞれの選択肢の回答数
  • 年齢別の回答数の数
  • 年齢別の選択肢の回答数
  • 男女ごとの回答数
  • 男女別の選択肢の回答数
  • 都道府県別の回答数
  • 都道府県別の選択肢の回答数

をそれぞれ表にして、さらにグラフにしたものとします。

まず、上の内容の実装例として、ソースコードの全体を紹介して、そのあとで部分的に解説を行いたいと思います。
コーディングが拙い部分もあると思いますが、ご了承ください。
また、出力結果のグラフも環境によってはグラフ同士が重なったりと、表示がうまくいかない場合もあるかもしれません。

出力結果として出来上がるExcelファイルは以下のようになります。

アンケートデータ

 

集計結果の図(一部)

 

集計結果の図(全体図)

 

 

 

それぞれの機能の集計結果の計算方法

アンケートの集計結果で、内容と上記のソースコードの関数での対応は以下のようになります。

  • それぞれの選択肢の回答数 answer_count_graph()
  • 年齢別の回答人数 answer_age_graph()
  • 年齢別の選択肢の回答数 answer_age_count()
  • 男女別の回答人数 gender_graph()
  • 男女別の選択肢の回答数 gender_count_graph()
  • 都道府県別の回答人数 prefectures_graph()
  • 都道府県別の選択肢の回答数 prefectures_count_graph()

それぞれの集計結果の計算方法は主にPandasのDataFrameを用いて計算しています。
データフレームに比較演算子を用いると以下のような結果が得られます。

これを特定の列で用いると以下のようなDataFrameが得られ、

さらに、このboolean列にDataFrame.sum()を用いると

Trueの数を得られます。また、複数の条件で行いたい場合は、条件を'&'(andの意味)や'|'(orの意味)や'~'(notの意味)を用いることができます。

 

基本的にこの方法で集計結果を得られます。
年齢別の回答人数(answer_age_graph)では少し、変わった計算の仕方をしています。

 

これはまず、年齢の区切りの配列(変数名がおかしい気がしますが気にしないで)を持ち、

最初のデータと最後のデータはそれぞれ通常の方法で、集計結果を出し、

中間にある方法は、Pythonの内包表記で、さらに2つの要素を取り出して、それぞれの年齢の比較を行っています。
以下は2つの要素を取り出して、どのような値が出力されるかの様子です。

 

 

 

表の記入方法について

表は辞書型のデータ構造を利用して、記入されます。表のデータ構造は

のように保存されます。

こうすることで、表の記入を行うときにループで記入を行うことができます。

 

 

 

グラフの目盛りのオートスケール計算

グラフのオートスケールの計算は検索してもなかなか出てこなかったので、自作したものになります。

グラフの目盛りの計算は、グラフでの最小値と最大値で求めます。グラフの最小値と最大値はグラフの幅を計算するために用います。
今回の例では、グラフの最小値は0で固定されているので、グラフの最大値を表のデータ構造に保存するときに変数(max_v)に記憶しておきます。

グラフの目盛りには1, 2, 5の倍数の数字を使った目盛りを利用するといいらしいので、目盛りに1, 2, 5の倍数を用いた計算方法を考えます。
ここで、例えば、グラフの目盛りを付ける場合、最低でも(グラフの幅/2)の部分でグラフの目盛りをつけたいです。
グラフの幅10のグラフに対して、(グラフの幅/2)は5となり、5のところにつけられそうです。
グラフの幅9のグラフに対して、(グラフの幅/2)は4(小数切り捨て)となり、1,2,5のどれかを使うとすると2が妥当と考えます。
同様に続けて、3のグラフに対して、(グラフの幅/2)は1(小数切り捨て)となり、1が妥当と考えます。

この結果で、

  • 一番上位の桁が1の場合は、5の倍数を
  • 一番上位の桁が2,3の場合は、1の倍数を
  • それ以外は2の倍数

になります。

グラフの目盛りに使う数字はおおまかに決まったので、次はその数字の桁数について考えます。扱う数字は正の整数で、とりあえず1000という数字について考えてみます。
1000につける目盛りは500にしたいので、5に100倍した数値になるだろうと考えます。
(グラフの幅/2)は500で、3桁あります。10のべき乗を考えて、10の(グラフの幅/2)の桁数-1乗で桁数も良いだろうと考えます。
同様に900や300で考えても、その数値は目盛りとしては使えそうなのでプログラムで記述したものになります。

ただし、桁数の計算はプログラム上ではすべて、(グラフの幅/2)をして桁数を計算していますが、1, 2の倍数の目盛りを計算する場合は、グラフの幅の桁数は(グラフの幅/2)にしても桁が落ちることがないのでわざわざ(グラフの幅/2)を計算する必要はありません。

 

グラフの設定

今回の例で用いたグラフの設定は以下のような意味になります。

 

 

 

まとめ

アンケートデータの集計プログラムはcsvファイルを入力として、Excelファイルを出力できます。csvファイルから集計結果の表やグラフなども自動で作成できます。

このプログラムの改善点はコマンドライン引数から入力ファイルや出力ファイルを指定できるようにする等、多々ありますが、このようにPythonを使用してopenpyxlでのExcelの作業の自動化は十分効果的だと思います。

Excelの作業の自動化は基本的にVBAが選択肢になりますが、Pythonも検討する価値があると思います。Excelでフォームコントロールを使用したい場合はVBAが選択肢になると思いますが、単純な作業の自動化はVBAよりPythonの方が書きやすく、良いと感じます。

 

 

 

参考

openpyxlの公式サイト

Pandasの公式サイト