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が得意なエンジニアを積極採用中です！詳しくはこちらをご覧ください。
※現在、多数のお問合せを頂いており、返信に、多少お時間を頂く場合がございます。
こちらの記事もオススメ！
書いた人はこんな人
- 「好きなことを仕事にするエンジニア集団」の（株）ライトコードです！
ライトコードは、福岡本社、東京オフィスの2拠点で事業展開するIT企業です。
現在は、国内を代表する大手IT企業を取引先にもち、ITシステムの受託事業が中心。
いずれも直取引で、月間PV数1億を超えるWebサービスのシステム開発・運営、インフラの構築・運用に携わっています。
システム開発依頼・お見積もりは大歓迎！
また、WEBエンジニアとモバイルエンジニアも積極採用中です！
ご応募をお待ちしております！
ITエンタメ2021.10.11【コンピュータ科学の父】アラン・チューリングってどんな人？- ITエンタメ2021.10.06世界最大手のネットショップAmazon社の歴史
- ITエンタメ2021.10.05ブログサービスの歴史を振り返る〜流行りのブログサービスまで〜
- ITエンタメ2021.10.04話題の5Gとは？なぜ速い？4Gとの違いなどを徹底解説
関連記事
採用情報
\ あの有名サービスに参画!? /
バックエンドエンジニア
\ クリエイティブの最前線 /
フロントエンドエンジニア
\ 世界を変える…！ /
Androidエンジニア
\ みんなが使うアプリを創る /
iOSエンジニア
