Python と openpyxl で Excel を簡単に自動操作してみた！
2020.08.12
目次
openpyxl とは？
「openpyxl」は、Excel を自動で操作してくれる Python のモジュールです。
「ブックの新規作成」「シートの追加」「セルへの入力」「グラフ作成」など…。
めんどくさい Excel 操作を、Python で組み立てることができてしまいます。
もし、あなたが Excel を利用した定型業務をしているのであれば、ぜひ openpyxl の導入を検討してみてください。
今まで膨大な時間がかかっていた作業が、Python のプログラムを実行するだけで終わってしまうかもしれません！
こちらの記事もオススメ！
openpyxl の導入方法
openpyxl は、Python のモジュールなので、お馴染みの pip コマンドを使ってインストールします。
1 | pip install openpyxl |
上記をインストールした後は、プログラム内から以下のようにモジュールを読み込みます。
1 | import openpyxl |
これで、openpyxl を利用する準備が整いました！
openpyxl の使用上の注意
セルへの書き込みは型を意識する
数字を文字型としてセルへ書き込むと、Excel でも文字として扱われてしまうため、グラフ化などに影響が出ます。
そのため、Excel のセルへ値を書き込む際には、型を意識して書き込んだ方がトラブルを回避できます。
Excel の旧ファイル形式は扱えない
Excel2003 まで使われていた拡張子「xls」形式のファイルは、残念ながら openpyxl では使えません。
Excel で一度「xls」ファイルを開いてから、手動で「xlsx」へ変換する必要があります。
今回の実装例で利用する openpyxl の関数
今回利用する openpyxl の関数は、以下となります。
新しい Excel ファイルのオブジェクトを作成する
デフォルトで「Sheet」という名前のシートが作成されます。
1 | excel = openpyxl.Workbook() |
操作対象のシートオブジェクトを作成する
このシートオブジェクトで、「cell 関数」を利用していきます。
1 | sheet = excel['シート名'] |
Excel のセルへ値を入力する
行と列は、数字を使って指定します。
行・列ともに「1」から指定可能で、「1」は1行目および A 列目を意味します。
1 | sheet.cell(”行", "列" ,"セルへ入力したい値") |
折れ線グラフのオブジェクトを生成する
「棒グラフ」や「円グラフ」など、他のグラフオブジェクトを生成したい場合は、 LineChart 部分を変更してください。
1 | chart = openpyxl.chart.LineChart() |
折れ線グラフで使用するデータ範囲のオブジェクトを生成する
以下の場合、グラフのデータ範囲は Excel 表記で「データのあるシート!$最小列$最小行:$最大列$最大行」となります。
1 | data = openpyxl.chart.Reference("データのあるシートオブジェクト", min_col="最小列", min_row="最小行", max_col="最大列", max_row=”最大行”) |
折れ線グラフのオブジェクトにデータを登録する
上記で生成したデータ範囲をグラフオブジェクトに渡します。
titles_from_data オプションを有効にすることで、データ範囲の内、1行目をラベルに使用します。
1 | chart.add_data("データ範囲のオブジェクト", titles_from_data=True) |
グラフをシートオブジェクトへ貼り付ける
貼り付けるセルの箇所は、「A1」など Excel のセル名で指定可能です。
1 | sheet.add_chart(”貼り付けるグラフオブジェクト”, "貼り付けるセル") |
新しいシートのオブジェクトを作成する
最初に作成した Excel ファイルオブジェクトに、新しいシートを作成します。
1 | excel.create_sheet('シート名') |
Excel ファイルを保存する
Excel ファイルに名前をつけて保存します。
1 | excel.save('ファイル名') |
他にも様々な機能がありますので、詳細なマニュアルは、以下のオフィシャルドキュメントを参照ください。
【openpyxlオフィシャルドキュメント】
https://openpyxl.readthedocs.io/en/stable/index.html
Python ＋ openpyxl でサクッと自動化完成！
今回の実装例では、Linux のリソース状況を調査するコマンド「vmstat」のログをエクセルに転記して、CPU 使用率のグラフを作成します。
処理の流れ
- vmstat のデータを、カンマ区切りのデータとしてリストに取り込む
- リストを1つずつ Excel のセルへ転記する
- 新しいシートを作成する
- CPU 関連データの折れ線グラフを貼り付ける
- ファイル名をつけて Excel ブックを保存する
コード
上記作業を Python + openpyxl を使って自動化すると、以下のようになります。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 | import openpyxl import re #行数を格納する変数を1で初期化 row = 1 #新しいEXCELファイルのオブジェクトを作成 excel = openpyxl.Workbook() #シートオブジェクトとして上記のEXCELファイルオブジェクト内のシート「Sheet」を選択。 sheet = excel['Sheet'] #vmstatのログファイルをUTF-8として変数logへ格納 log = open("sample.txt", "r", encoding="utf_8") #logの内容を一行ずつ読み込み、以下の文字列編集を実施 #一番左の空白を削除 #空白の一回以上の連続を「,」に置換 #一番右の不要な「,」を削除 #「,」を区切り文字としてline変数にリストとして代入 for line in log: line = line.lstrip(" ") line = re.sub(r"\s+", ",", line) line = line.rstrip(",") line = line.split(",") #data変数にline内のログを１行ずつ読み込み格納 #colum変数にlineリストから読み込んだ回数を0から順番に格納する。 for colum, data in enumerate(line): colum += 1 #1行目と２行目は文字列としてセルに値を入力する。 if row <= 2: sheet.cell(row, colum, data) #３行目以降は数値型としてセルに値を入力する。 else: sheet.cell(row, colum, int(data)) #次の行に入力するためにrowをインクリメントする。 row += 1 #折れ線グラフのオブジェクトを作成する。 chart = openpyxl.chart.LineChart() #折れ線グラフのタイトルを設定する。 chart.title = "CPU Usage" #折れ線グラフのスタイルを数字で指定する。 chart.style = 2 #折れ線グラフのX軸のタイトルを指定する。 chart.x_axis.title = 'Time' #折れ線グラフのY軸のタイトルを指定する。 chart.y_axis.title = 'Percentage' #折れ線グラフのサイズ（縦）を指定する。 chart.height = 15 #折れ線グラフのサイズ（横）を指定する。 chart.width = 40 #折れ線グラフのデータ範囲としてCPUに関連するN列(13)～R列(17)、2行目(タイトルとして利用)～最終行を選択。 data = openpyxl.chart.Reference(sheet, min_col=13, min_row=2, max_col=17, max_row=sheet.max_row) #上記のデータを折れ線グラフのオブジェクトに格納する。 #titles_from_dataを有効にすることで、データ範囲の１行目をタイトルとして利用 chart.add_data(data, titles_from_data=True) #新しいシート「Sheet1」を作成する。 excel.create_sheet('Sheet1') #現在のシートを「Sheet1」にする。 sheet = excel['Sheet1'] #Sheet1のA1セルへ折れ線グラフを貼り付ける。 sheet.add_chart(chart, "A1") #ファイル名を「test.xlsx」としてEXCELファイルを保存する。 excel.save('test.xlsx') |
サンプルデータ
上記、実装例のプログラムで使用した vmstat のサンプルデータは以下です。
（※クリックすると、ダウンロートが実行されます）
このプログラムを実行すると、以下の Excel ファイルが保存されます。
（※クリックすると、ダウンロートが実行されます）
実装結果
作成した Excel ファイルを確認すると、シート名「Sheet」には以下のようなデータが読み込まれています。
シート名「Sheet1」には、折れ線グラフが出力されています。
1秒毎に vmstat を取得したので、167秒目から CPU 使用率が上がったことがわかります。
さいごに
Python ＋ openpyxl の使い方はご理解いただけましたか？
Excel の操作を Python で実装できるので、様々な自動化が期待できます。
今回のように、システムが出力したテキストファイルを Excel へ転機してグラフ化する場合、どんなにテキストファイルが大量になっても、openpyxl を使えば一瞬でグラフを作ることができます。
その他にも、Excel ファイル同士の転記を行う業務や、スクレイピングと組み合わせて WEB の情報を Excel に転記する業務などの自動化が可能です。
皆さんを煩わせる面倒な Excel 業務は、Python ＋ openpyxl でサクッと自動化しちゃいましょう！
(株)ライトコードは、WEB・アプリ・ゲーム開発に強い、ITが大好きな会社です。
Pythonでのシステム開発依頼・お見積もりはこちらまでお願いします。
また、Pythonが得意なエンジニアを積極採用中です！詳しくはこちらをご覧ください。
※現在、多数のお問合せを頂いており、返信に、多少お時間を頂く場合がございます。
こちらの記事もオススメ！
ライトコードよりお知らせ
にゃんこ師匠
ミツオカにゃんこ師匠ミツオカにゃんこ師匠ミツオカ一緒に働いてくれる仲間を募集しております！
ライトコードでは、仲間を募集しております！
当社のモットーは「好きなことを仕事にするエンジニア集団」「エンジニアによるエンジニアのための会社」。エンジニアであるあなたの「やってみたいこと」を全力で応援する会社です。
また、ライトコードは現在、急成長中！だからこそ、あなたにお任せしたいやりがいのあるお仕事は沢山あります。「コアメンバー」として活躍してくれる、あなたからのご応募をお待ちしております！
なお、ご応募の前に、「話しだけ聞いてみたい」「社内の雰囲気を知りたい」という方はこちらをご覧ください。
ライトコードでは一緒に働いていただける方を募集しております！
採用情報はこちら書いた人はこんな人
関連記事
採用情報
\ あの有名サービスに参画!? /
バックエンドエンジニア
\ クリエイティブの最前線 /
フロントエンドエンジニア
\ 世界はお前の手の中に・・・ /
モバイルエンジニア
\ サービスの守り神！ /
インフラエンジニア
