【Python】【Excel】openpyxlを使ってPythonからExcelシートを操作する（テストシートを例に）

ソフトの試験はいろいろあるかと思いますが、ターミナル（ネットワークとか組み込みならシリアルとか）からログを抽出してそれを見て判定するというやり方が私の経験上では多かったです。で、試験結果はエクセルにするという感じですね。さすが安心のエクセルだぜぃみたいな・・

ログの解析・判定はどのスクリプト言語も得意と言って良いかと思われます。で後は結果を記入となるのですが、これをどうしたものかと思ってググるとPythonではopenpyxlというライブラリがあって、これで操作が出来ます。また、私の場合ではopenpyxlで全て生成という事まではしなくても良い状況です。フォーマットとかデザインはGUIでフツーにやって、必要なデータをスクリプトな人に書いてもらうという関係で行こうかなと。

今回そういう事をやるにはどうすれば良いのか、軽く調べてサンプルも作ったので、備忘の意味も含めて晒してみます。エクセル操作を行おうとする方に何らか参考になってもらえれば幸いです。

環境など

エクセルを使いますので、Windowsを利用して今回は行いました。私は以下の環境で動作を確認しています。

• OSはWindows7/WIndows10(共に64bit）
• Pythonは 2.7.13(win32)
• openpyxl-2.4.8

openpyxlの基本操作

基本操作については以下の公式ざっと見るだけでもかなり参考になるのではないでしょうか。
https://openpyxl.readthedocs.io/en/default/

また、Qiitaの以下記事が大変参考になりました。ありがとうございます。
http://qiita.com/tftf/items/07e4332293c2c59799d1

ざっと評価した感じでは、シートの選択、RowやColomnの情報取得、逆に文字列を書き込むぐらいであれば、まぁそう違和感なく操作できました。

インストールは今回のように文字列やるぐらいなら定番の pip install openpyxl　で行けます。イメージファイルも扱いたいとかになると、インストール方法が変わるみたいですので上に貼った公式URLを参照された方がよろしいかと思います。

また、文字列を扱う場合は 使う側でu"文字列"、"char"という感じで使い分ける方が良いのかもしれません（ここらは状況などでも異なるかと思いますが、今回はこれベースで進めています）。

サンプル－こんなエクセルでやってみた

今回はこんなシートを例にやってみました。

これを元にログを解析して試験結果を判定し、その結果を記入したい訳です。要は「ColumnGの適切な場所に結果を追記する」をやります。後ほどサンプルコードを晒しますが、サンプルコードを実行しますと、

のように表示されるようにします（closeの正常系に失敗とかひでーなというツッコミもありますがｗ）

もちろんRowとColumnを直接指定すれば2行で終わる話になるのですが、もう（だけ）少し工夫が入ったコードにしています。

エクセル操作（test_excel）クラスを作ってみた

今回は「APIと試験内容」でどの結果に入れるかを決めるという考え方をコードにしてみました。これを行うために、以下のAPIを提供するtest_excelクラスというコードを書いてみました。以下です。

#!/usr/bin/env python
# -*- coding: shift-jis -*-

# エクセル関連の操作API

import sys
import openpyxl as px

#
# ここらのパラメータは資料に合わせて適当に変更して下さい。
#

#COLUMNなめる時にどこまでなめるか
EXCEL_END_OF_COLUMN   = "Z"

#TITLE探す時に上からどこまで探すか。
EXCEL_END_OF_ROW_TITLE = 20

#アイテム探す時に上からどこまで探すか。
EXCEL_END_OF_ROW_ITEM = 30

class TestExcel:

    # クラス生成時にエクセルファイル名を指定します。
    def __init__(self, filename):
        self.filename = filename
        self.workbook = px.load_workbook(filename)
        self.sheet = None

    def err_print(self, text):
        print "\r\nERROR!:", text, "\r\n"

    # select_nameのシートを処理対象として選択します。
    def select_sheet(self, select_name):
        sheetnames = self.workbook.get_sheet_names()
        for name in sheetnames:
            sheet_name = name
            if select_name == sheet_name:
                self.sheet = self.workbook[name]
                print "%s selected." % select_name
                return True
        self.err_print("%s not found." % select_name)
        return False

    # nameで指定された名前のあるcolumn(A,B,...)を戻します。
    def get_column(self, name):
        row_list =  [int(i) for i in range(1, EXCEL_END_OF_ROW_TITLE)]
        column_list = [chr(i) for i in range(ord('A'), ord(EXCEL_END_OF_COLUMN)+1)]
        for row in row_list:
            for column in column_list:
                pos = column + str(row)
                value = self.sheet[pos].value
                if value == name:
                    return column
        self.err_print( "%s not found." % name)
        return None

    # 複数のrow(1,2,3...)を結合して作成した項目の範囲を取得します。
    # 戻り値は、最小、最大+1 です。
    def get_multi_row_data(self, row_title, row_name):
        hit = False
        column = self.get_column(row_title)
        if column == None:
            self.err_print( "%s not found." % row_name)
            return None, None
        row_list =  [int(i) for i in range(1, EXCEL_END_OF_ROW_ITEM)]
        for row in row_list:
            pos = column + str(row)
            value = self.sheet[pos].value
            if value != None:
                if hit == True:
                    api_max = row
                    return api_min, api_max
            if value == row_name and hit == False:
                hit = True
                api_min = row
        if hit == True:
            api_max = row
            return api_min, api_max
        else:
            self.err_print( "%s not found." % row_name)
            return None, None

    # 指定したrow, colomnにvalueのデータを書き込みます。
    def write(self, colomn, row, value):
        self.sheet[colomn + str(row)] = value

    # 元のエクセルファイルを源氏亜更新します。
    def save(self):
        self.workbook.save(self.filename)

#EOF

各APIの挙動は以下の通りです。

API	動作
err_print	エラーメッセージの表示。内部利用を想定しています
select_sheet	処理対象のシートを選択します
get_column	指定された名前のあるColumn（A,B,...）を戻します。これは表の上にある項目名のColumn取得を意識しています。
get_multi_row_data	複数のRowが結合されている場合、その範囲を算出します。サンプルでいうとAPIがこれに当てはまります
write	指定したRow, Columnに文字列を書き込みます。
save	エクセルファイルの更新をします（これしないとエクセルに変更が反映されません）

パラメータは以下の役割です。まぁ適当にやってますごめんなさいです。EXCEL_END_OF_ROW_ITEMなんかは本来はもっと下までやる感じになるかと思います。

パラメータ	役割
EXCEL_END_OF_COLUMN	Colomnつまり横方向舐める際にどこまで追跡するかの指定
EXCEL_END_OF_ROW_TITLE	get_columnで探す際に一番上からどこまで探すか
EXCEL_END_OF_ROW_ITEM	get_multi_row_dataで探す際に一番上からどこまで探すか

筆者がコーディング下手なのをさっぴいても、平易な処理なので、公式とか引用した記事のURLとコードを比較すればある程度処理は分かるかと思います。この中では、get_multi_row_dataがかなり酷いですが、要は上から舐めて、ヒットした場所が最小、でフラグ立てて次にNoneじゃなくなるまで回して、Noneでなくなる（あるいは最後までNoneだった）場所が最大としている感じです。
補足：結合した場合は、2番目以降はNoneになるみたいです。

この辺りはシートにはあまり依存しない感じで使えるのかなーとか思ってます。

エクセル操作アプリを書いてみた

test_excelを利用して、サンプルのエクセルを操作するコードは以下のようになります。

#!/usr/bin/env python
# -*- coding: shift-jis -*-

# test_excel.pyを使った、該当エクセルの結果記入アプリ

from datetime import datetime
from test_excel import TestExcel

# 表の項目名、試験するAPIの名前、試験用の名前とColumn(A,B,C,..)を入れると
# 該当項目のRow（1,2,3..）を戻します。
def get_test_row(excel, test_koumokumei, test_apiname, siken_naiyou_column, siken_naiyou_name):
    api_min, api_max = excel.get_multi_row_data(test_koumokumei, test_apiname)
    if api_min == None:
        return None
    for row in range(api_min, api_max):
        pos = siken_naiyou_column + str(row)
        value = excel.sheet[pos].value
        if value == siken_naiyou_name:
            return row
    print("\r\nerror::%s found. but %s not found.\n\n" % (test_apiname, siken_naiyou_name))
    return None

#
# エクセル操作のサンプル
#　　文字列操作を伴う場合、"ascii"、u"テスト"のように呼び出し側で使い分ける想定です。
#
if __name__ == "__main__":

    filename = "API試験サンプル.xlsx"

    # excel初期化
    excel = TestExcel(filename)

    # シート選択
    excel.select_sheet(u"API試験")

    # 項目に対応するcolomnを検出
    naiyou_column = excel.get_column(u"試験内容")
    print "試験内容 colomn =", naiyou_column
    kekka_column = excel.get_column(u"結果")
    print "結果 colomn =", kekka_column

    # 試験情報を取り出し、結果を記載(1)
    test_koumokumei = "API"
    test_api = "sample_open(char *name)"
    test_name = u"リオープン"
    test_row = get_test_row(excel, test_koumokumei, test_api, naiyou_column, test_name)
    result = u"成功"
    excel.write(kekka_column, test_row, result)
    print "API:", test_api, " 試験内容:", test_name, " =", test_row, " 結果:", result

    # 試験情報を取り出し、結果を記載(2)
    test_api = "sample_close(void)"
    test_name = u"正常系"
    test_row = get_test_row(excel,test_koumokumei, test_api, naiyou_column, test_name)
    result = u"失敗"
    excel.write(kekka_column, test_row, result)
    print "API:", test_api, " 試験内容:", test_name, " =", test_row, " 結果:", result

    #更新
    excel.save()

    print "complete."

#EOF

今回の場合、API名と試験内容から該当の場所を探すという処理が必要になります。Columnはtest_excelのget_columnですぐに持ってこられますが、RowはAPI名と試験名のANDが必要となります。それをget_test_row関数に処理させています。処理的には以下の感じです。

• まずはAPI名に対応するRowの上限下限をget_multi_row_dataで取得します
• 次に出てきたRowをforで舐めて、与えられた「試験内容」のColomnとRowを組み合わせて値を取得
• 試験項目名と比較して一致したら、そのRowを戻す

で、あとはRowとColumnを指定して、Writeすれば良いでしょという考え方です。

メインでは「API試験サンプル.xlsx」のシート「API試験」を処理するように記述されています。上に図で示した表がそこに書かれている事を想定しています。で、以下の処理しています。

• sample_open(char *name)、リオープンの結果を「成功」に
• sample_close(void)、正常系の結果を「失敗」に

実際にこのpythonを実行しますと、以下のようなメッセージが出て、エクセルに結果が反映されます。

API試験 selected.
試験内容 colomn = D
結果 colomn = G
API: sample_open(char *name)  試験内容: リオープン  = 4  結果: 成功
API: sample_close(void)  試験内容: 正常系  = 7  結果: 失敗
complete.

という事で、API名と試験内容を指定すれば目的の場所が分かるようになりました。舐めて探していますので、エクセルの配置を多少変更しても対応できる感じです。ログ判定は他のスクリプト同様、Pythonの得意とする所ですので、今回のtest_excelクラスと組み合わせることで、記入まで勝手にやってくれるようになる（はず）。

ライセンスについて

以下使わせて戴きました。素晴らしいソフトウェアを提供して下さり、ありがとうございます。

• （一応書きます…）上記のコードはパブリックドメインとします。著作権を主張するほどのコードではないっつーことで。ただ当然ですが使用した際の損害は誰も請け負ってくれません。そこだけ注意で。
• Python自体はPSF (Python Software Foundation)ライセンスです。
• ↑の情報はWikipediaのPythonがソースです。
• openpyxlのライセンスは以下のLICENCE.rstに記載があります。MITなようですね。 https://bitbucket.org/openpyxl/openpyxl/src

以上です。