事務DX推進の必須知識 Python&Excel連携のツボを伝授

はじめに

先日、仕事で、「CSV/Excelデータを元に業務用ワークシート用EXCELを生成する」ということをしました。業務の全体工数はざっくり100人日程度のかなりガチなタスクです。
この規模のEXCELを作る場合、既存データを書き換えられなようにするとか、決めた選択肢以外選べないようにするとか、そうした加工をExcelに対してかけないと、後工程でデータがきたなくなって大変です。
基本的に、すべての加工はopenpyxlでできるのですが、具体的な関数の呼び出し方が意外に難しく、というかネットにまともな情報が出ていなく、かなりの試行錯誤が必要だった訳です。
その時の苦労の結果得られた知識を資料&コードサンプルとしてまとめたものが、この記事ということになります。

サンプルプログラムでやっていること

元データ

こんな形のCSVです。
bank-marketingデータセットで、いくつかの項目を間引いたものになります。

年齢,職業,婚姻,学歴,平均残高,住宅ローン,個人ローン
58,management,married,tertiary,2143,True,False
44,technician,single,secondary,29,True,False
33,entrepreneur,married,secondary,2,True,True
47,blue-collar,married,,1506,True,False
33,,single,,1,False,False
35,management,married,tertiary,231,True,False
28,management,single,tertiary,447,True,True
42,entrepreneur,divorced,tertiary,2,True,False
58,retired,married,primary,121,True,False
(以下略)

加工後Excel

加工後のExcelはこんな感じです。 (生成後に人間による入力もされた状態)

実物のリンクはこちら　Excel ファイルリンク

特徴をまとめると次のような形になります。

A列からG列　

• 上記のCSVデータがそのまま転記されています
• これらのデータは変更不可です
• タイトル行は最初からフィルター設定されており、項目値で絞り込み可能です

H列とI列

• 入力可能です
• 入力値はあらかじめ決められた値しか設定できません。ドロップダウンリストは下のような感じ

 

 

J列
制約なく、自由な形式で入力できます。

M1, O1, Q1セル
計算式が設定されており、データ件数や、I列の入力状況で、値が自動的に変化します。

この仕組みを作る上で苦労した実装時のポイントをまとめると次のようになります。
以下の記事は、このポイントに沿って説明します。

1. テンプレート利用で見た目きれいなExcel作成
2. セルロック機能は「シートロック」+「セル編集許可」設定で実装
3. 特定値入力の徹底はshowErrorMessageとerrorStyleで
4. 要素数不定の計算はvalue値で計算式を定義することで実現

実装編

以下で紹介するプログラムは、全量を下記リンクで公開しています。

1. テンプレート利用で見た目きれいなExcel作成

ご存じのとおり、PandasデータフレームのデータをExcelにしたい場合、1行to_excel関数を呼び出せばOKです。
しかし、こうやって作られたExcelは、タイトル行の属性なし、行ごとの個別幅設定なしの、無味乾燥なExcelで、人間がこれを使ってインプット作業をするのはちょっと辛いです。
そういう時に活躍するのが、これから紹介する方法です。

実装のアウトラインは
(1) テンプレートになるExcelを作成先Excelにファイルとしてコピー
(2) データ本体は、別データフレームとしてPythonに読み込んでおく
(3) データフレームの値は、二重ループを回して要素単位でコピー
となります。具体的な実装コードは以下に示します。

# テンプレートExcelのダウンロード
url1 = 'https://github.com/makaishi2/sample-data/raw/master/data/bank-marketing-template.xlsx'
!wget $url1

# コピー対象データ読み込み
url2 = 'https://github.com/makaishi2/sample-data/raw/master/data/bank-marketing-v2.csv'
df = pd.read_csv(url2)

# 各種変数設定
rows = df.shape[0]
cols = df.shape[1]
max_row = rows + 1

# ファイルコピー
!cp bank-marketing-template.xlsx bank-marketing-record.xlsx

# openpyxlでExcelオープン
fn = 'bank-marketing-record.xlsx'

# コピー後のExcelオープン
from openpyxl import load_workbook
wb = load_workbook(fn)

# シート選択(1シートしかないのでこれでOK)
ws = wb.active

# セル値のコピー
for row in range(rows):
    for col in range(cols):
        ws.cell(row=row+2, column=col+1).value = df.iloc[row, col]

# ファイル保存
wb.save(fn)

2. セルロック機能は「シートロック」+「セル編集許可」設定で実装

ここが、今回のExcelを作るときに最も苦労した点です。わかってみると、理解に苦しんだのはAPIの細かい仕様ではなかったです。
Excelではそもそも「特定のセルで入力ロックをかける」という機能はありません。このことを実現するには、

(1) まずシート全体を入力不可にする
(2) その上で入力の可能性のあるセルには「入力可能」の設定をする

ということをやればいいのでした。このポイントさえおさえれば実装コードは簡単です。例えば、以下のようになります。

from openpyxl.styles import Protection

# ワークシート全体にロックをかける
ws.protection.enable()
# フィルターは認める
ws.protection.autoFilter = False

# 特定セルに対しロック解除
unlock_cells = ws[f'H2:J{max_row}']
for row in unlock_cells:
    for cell in row:
        cell.protection = Protection(locked=False)

unlock_cells = ws[f'A1:J1']
for row in unlock_cells:
    for cell in row:
        cell.protection = Protection(locked=False)

3. 特定値入力の徹底はshowErrorMessageとerrorStyleで

ここもとても苦労した箇所です。
DataValidationを使った実装サンプルはネットにたくさん出ています。これらのサンプルを使うと確かに、入力候補のリスクは表示されるのですが、直接入力で別の値を入れると、それはそれで受け付けてしまうのです。これでは、一番重要な値の縛りを確実にすることができません。
いろいろ調べてわかったのは、下記のコードにすればいいということです。

from openpyxl.worksheet.datavalidation import DataValidation

dv1 = DataValidation(
    type="list",
    formula1='"1. 見込みあり,2. 優先度低,3. 見込みなし"',
    showErrorMessage=True,
    errorStyle="stop",
)

dv2 = DataValidation(
    type="list",
    formula1='"1. 成功,2. 失敗"',
    showErrorMessage=True,
    errorStyle="stop",
)

# 見込み
dv1.add(f'H2:H{max_row}')
ws.add_data_validation(dv1)

# 成功
dv2.add(f'I2:I{max_row}')
ws.add_data_validation(dv2)

ポイントは DataValidationインスタンスの初期化でshowErrorMessageとerrorStyleの2つのパラメータを指定することでした。この設定をすることでキー直接入力の場合であっても他の値の入力を一切受け付けなくなります。

4. 要素数不定の計算はvalue値で計算式を定義することで実現

テンプレートのExcelを用意してそこに値をコピーする手法を使えば、計算式も元のテンプレートに含めることは可能です。
しかし、そ場合に解決しないといけないのが、コピー元データの行数が不定の場合に計算式の下限値をどうするかです。
この問題の解決策はとてもシンプルです。計算式は、対象セルのvalueプロパティに「=」ではじめる文字列を設定しておけば、そのまま動いてくれるのです。以下に実装サンプルを示します。

# 全体件数
ws['M1'].value = f'=counta(A2:A{max_row})'

#　成功件数
ws['O1'].value = f'=countif(I2:I{max_row},"1. 成功")'

#　失敗件数
ws['Q1'].value = f'=countif(I2:I{max_row},"2. 失敗")'

5. おまけ その他の実装

openpyxlを利用すれば、Excelに罫線を設定することも簡単に可能です。
この実装は特に苦労はしなかったですが、参考までに示しておきます。

from openpyxl.styles.borders import Border, Side
# 罫線設定用
side = Side(style='thin', color='000000')
border_all = Border(top=side, bottom=side, left=side, right=side)

for row in range(rows+1):
    for col in range(cols+3):
        ws.cell(row=row+1, column=col+1).border = border_all
     

おわりに

今回の一連のタスクを通じて感じたことは「 たかがExcel、されどExcel」ということです。
Excelは使い勝手の良さという観点では圧倒的な優位性を持っています。
一方で、「エラーチェックなどは甘いので業務でガチには利用できないのではないか」
それが今まで私がExcelに対して持っていた印象です。
やってみてわかったことは、工夫すれば完璧な入力チェックは普通にできるということ。
つまり、この見た目の欠点を補うことができれば 「最強のインプットツール」としての可能性を持っていたのです。
今回の記事ではそれを実現するためのエッセンスを記載したつもりです。
この点をマスターしたあなたは、明日から自分の職場で「事務系DX推進」の中心人物となれることは必須です。

この記事を活用して、皆様が活躍させることを期待して、結びとさせていただきます。