Lotus Base
< Prev
Blog

2022-01-24

Pythonで複数のExcelファイルから図形に記載されたコメントを一括出力する

あらすじ

PM「プロジェクトのデリバリ品質評価のために、顧客から受けた設計書に対する指摘の件数を集計してくれる？」

あなたの参画するシステム構築プロジェクトでは、プログラムの設計書作成にExcelを利用している。顧客とのやり取りではExcelに吹き出しの図形が貼り付けられ、そこに指摘やコメントが書かれる。これまでやり取りした設計書の件数はのべ100件を超え、手作業でカウントするにはあまりにも膨大（そして退屈）である。

あなたはVBAの経験がほぼ皆無（そもそも書きたくない）であったが、幸いなことにPythonには比較的なじみがあった…

やること

Pythonを利用して、複数Excelファイルの図形（オートシェイプ）に記載されたコメントを一括でCSVに出力する。

出力イメージ
ファイル名コメント
program_1.xlsxここはダメです
program_1.xlsxこれはなぜですか
program_1.xlsx誤記？
program_2.xlsxこの条件は不要です
program_2.xlsxやる気ありますか？
処理概要
  1. ExcelをZip解凍し、図形の情報が定義されているXMLファイルを取得
  2. PythonのXML解析ライブラリを利用して、図形に記載された文字列を抽出
  3. ファイル名とテキストをセットにしてCSV出力し、ここまでの操作をファイルの件数回ループ

実行環境・動作前提

  • 実行環境はコマンドプロンプト (Windows10)
  • Python 3.8
  • unzip コマンドが使えること

フォルダ構成

  • main.py … メインプログラム
  • src … 処理対象Excelファイルを格納するディレクトリ
  • result … 結果出力用ディレクトリ
main.py
src
└ program_1.xlsx
└ program_2.xlsx
result
└ yyyymmddhhmmss.csv

ソースコード全文

実行は python3 main.py

main.py

# -*- coding: utf-8 -*-

import datetime
import glob
import os
import subprocess
import xml.etree.ElementTree as ET

# Office Open XMLのネームスペース
NS = {
    'xdr': 'http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing',
    'a': 'http://schemas.openxmlformats.org/drawingml/2006/main'
}
# 図形を表すタグの名称
ANCHOR_LIST = ['xdr:oneCellAnchor', 'xdr:twoCellAnchor', 'xdr:absoluteAnchor']

# 出力ファイルオープン
dt_now = datetime.datetime.now()
resultPath = 'result/%s.txt' % dt_now.strftime('%Y%m%d%H%M%S')
resultFile = open(resultPath, 'a', encoding='UTF-8')

# 処理対象Excelを取得
files = glob.glob("./src/*.xlsx")

for i, f in enumerate(files):
    # Excelをzip解凍（Linux環境の場合、コマンドを適宜編集してください）
    subprocess.call('unzip %s -d src\%s > nul' % (f, i), shell=True)
    filename = os.path.split(f)[1]
    # 図形（オートシェイプ）が定義されているファイルを取得
    drawings = glob.glob('./src/%s/xl/drawings/*.xml' % i)
    count = 0
    for d in drawings:
        tree = ET.parse(d)
        root = tree.getroot()
        for anchor in ANCHOR_LIST:
            # 図形（オートシェイプ）を抽出
            for autoshape in root.findall(anchor, NS):
                texts = []
                # 図形内のテキスト要素を抽出し、1行にまとめる
                for t in autoshape.findall('.//a:t', NS):
                    if t.text is not None:
                        texts.append(t.text)
                if len(texts) > 0:
                    textline = ''.join(texts)
                    count += 1
                    resultFile.write('"%s","%s"\n' % (filename, textline))
    print(filename)
    print('Count: %d' % count)
    # 解凍後のファイル群を削除（Linux環境の場合、コマンドを適宜編集してください）
    subprocess.call('rd /s /q src\%s' % i, shell=True)

# 出力ファイルクローズ
resultFile.close()

解説

ExcelをZip解凍し、図形の情報が定義されているXMLファイルを取得

ExcelをZip解凍したディレクトリの ABC.xlsx/xl/drawings/drawing[1].xml ([1]は連番) に図形の情報が定義されています。シートごとにファイルが分割されるため、取得できたファイルを１つずつ見ていく必要があります。

PythonのXML解析ライブラリを利用して、図形に記載された文字列を抽出

drawing*.xml の中の、以下のいずれかのタグに囲まれた要素が１つの図形要素を表します。

<xdr:oneCellAnchor>
<xdr:twoCellAnchor>
<xdr:absoluteAnchor>

図形要素内のテキストは、上記要素配下で <a:t> タグに囲まれています。１つの図形に複数の<a:t> タグが含まれる可能性があるため、全件取得したうえで結合します。

ある要素配下の特定タグの全件抽出は、関数 findall()と XPath構文 // を併用することで実現できます。詳しいXMLライブラリの使用方法はドキュメント (https://docs.python.org/ja/3/library/xml.etree.elementtree.html) を参照してください。

エピローグ

かくしてあなたは100件以上のExcelファイルから、図形テキストのみを一括出力することに成功した。しかし図形テキストには指摘以外の内容も含まれており、そこからの件数カウントは結局手作業で進めるしか無いのであった…。

参考

< Prev
Back to Top