Pythonのopenpyxlを使ってExcelファイルを読んでみる

久しぶりの技術ネタ...です

今、AnsibleインベントリファイルとしてExcelファイルを使えるようにするための、ダイナミックインベントリスクリプトを作っていたりします。
このダイナミックインベントリスクリプトをPythonで作っているのですが、ここでExcelファイルにアクセスするためのライブラリであるopenpyxlを使っています。
openpyxl - A Python library to read/write Excel 2010 xlsx/xlsm files — openpyxl 2.5.0 documentation

今回は、このopenpyxlを使って、Excelファイルから値を読み込む方法について簡単に書いてみます。
※このopenpyxl、Excelファイルの生成から変更など色々できるのですが、今回は参照機能しか使ってないので...

openpyxlのインストール

まずは、pipを使ってopenpyxlをインストールします。
今回、Python3.6を使っているので、3.6のpipを使っています。

$ sudo pip3.6 install openpyxl

私が3.6環境で開発しているだけなので、pip3.6を使っていますが、各自の環境にあったpipを使ってください。

Excelファイルを読みこんでみる

それでは、実際にExcelファイルを読み込んでみます。
今回読み込んでみるExcelファイルの内容は、以下の通り...

今回読み込むサンプルExcelファイル

文字列、数値、日付、Boolean、未入力セルを含んだものにしています。

Excelファイルの読み込み処理

まずは、Excelファイルの読み込みと、Book内のシート名一覧の取得コードです。

import openpyxl as px

# Excelファイルを読み込み、Bookオブジェクトを生成
workbook = px.load_workbook('sample.xlsx', data_only=True)

load_workbook関数でBookを読み込みます。
戻り値はBookオブジェクトになります。

data_only=Trueはデータ値のみを取得するという設定で、セルに記述している数式は読み込まなくなります。
data_only=Falseにすると、セルに記述された数式（SUM(B2:B4) など）がそのまま出力されます。
このため、openpyxlを使って値を読み込ませる際は、数式を計算結果に置き換えてから読み込ませる必要があります。

次は、Bookオブジェクトのsheetnamesから、Bookに含まれるSheetの名称一覧を取得しています。

# book内のsheet名称一覧を取得する
sheet_names = workbook.sheetnames
# sheet名称一覧 表示
print (sheet_names)

このsheetnamesで取得したSheet名称一覧の内容は、以下のようになります。

['sample', 'Sheet2', 'Sheet3']

Bookに含まれるSheetを取得する際には、Sheet名を指定して取得するのですが、ハードコーディングでSheet名称を指定するより、この方法で取得したSheet名称一覧を使う方が柔軟性が上がると思います :-)

Sheetの読み込みとCell情報の取得

次は、BookオブジェクトからSheetを取得します。

# sheetの読み込み
sheet = workbook[sheet_names[0]]

Bookオブジェクトを辞書に見立てて、[シート名称]と記述することで、目的のSheetオブジェクトを取得することができます。
ここでは、前に取得したSheet名称一覧の先頭にあるSheet名称を使って取得しています。

次は、上記で取得したSheetオブジェクトから、イテレーションを使ってCellを取得するコードです。

# sheet内のcell情報の取得
for row in sheet:
    for cell in row:
        # cellのデータ取得
        # cellの行番号取得（1オリジン）
        row_pos = cell.row
        # cellの列番号取得（1オリジン）
        column_pos = cell.col_idx
        # Excel表記のcellの列番号（A,B...）
        column = cell.column
        # cellのデータ型
        cell_type = cell.data_type
        # cellのデータ
        value = cell.value

        print ('row_pos=%d column_pos=%d column=%s cell_type=%s value=%s' %
                 (row_pos, column_pos, column, cell_type, value))

Pythonではお約束の、for xx in yy を使って、Sheetオブジェクトからrowを取得し、そのrowオブジェクトからイテレーションでCellオブジェクトを取得します。
取得したCellオブジェクトから、Cellの位置や、Cellに含まれているデータの型やデータそのものを取得しています。
取得したデータをprintで出力した結果は、以下の通り。

row_pos=1 column_pos=1 column=A cell_type=s value=text
row_pos=1 column_pos=2 column=B cell_type=s value=整数
row_pos=1 column_pos=3 column=C cell_type=s value=実数
row_pos=1 column_pos=4 column=D cell_type=s value=datetime
row_pos=1 column_pos=5 column=E cell_type=s value=boolean
row_pos=2 column_pos=1 column=A cell_type=s value=data001
row_pos=2 column_pos=2 column=B cell_type=n value=10
row_pos=2 column_pos=3 column=C cell_type=n value=10.52
row_pos=2 column_pos=4 column=D cell_type=d value=2018-05-02 00:00:00
row_pos=2 column_pos=5 column=E cell_type=b value=True
row_pos=3 column_pos=1 column=A cell_type=s value=data002
row_pos=3 column_pos=2 column=B cell_type=n value=21
row_pos=3 column_pos=3 column=C cell_type=n value=29.39
row_pos=3 column_pos=4 column=D cell_type=d value=2018-10-05 10:00:00
row_pos=3 column_pos=5 column=E cell_type=b value=False
row_pos=4 column_pos=1 column=A cell_type=s value=testdata
row_pos=4 column_pos=2 column=B cell_type=n value=None
row_pos=4 column_pos=3 column=C cell_type=n value=None
row_pos=4 column_pos=4 column=D cell_type=n value=None
row_pos=4 column_pos=5 column=E cell_type=n value=None

取得したデータは、Excelのセルの型に対応する、Pythonのデータ型の値として取得することができています。
実際のCell内でのデータ型は、cell.data_typeで取得することができます。
文字列は’s’、booleanは'b'、日時型は'd'という文字が返ってきます。
数値とデータなしでは’n’が返ってきます。ちょっとこれはわかりにくいなぁ...
このCellの型の内容や変数についての詳細は、以下のドキュメントに詳しく書かれています。
openpyxl.cell.cell module — openpyxl 2.5.0 documentation

そんな訳で、openpyxlのファイル参照について簡単に説明してみました。
運用とかで、ネットワーク機器やサーバの構成情報をExcelファイルで管理しているケースってよくあるので、そのExcelファイルから、運用で使うスクリプトが使いやすい文字形式（JSONやYAML）に変換する際に重宝するのではないかと思っています。
VBAで記述する方法もあるのですが、使い慣れたPythonスクリプトの方が便利なので...orz

それでは