【Python】Excelへの画像挿入とサイズ調整の自動化

はじめに

データアナリティクス事業本部ビッグデータチームのkasamaです。
最近、私はよく単体試験のエビデンスとして画面キャプチャを貼り付けています。 大体同じエビデンス構成でありますが、毎回画像サイズの調整だとか画像を貼るセル位置の調整だとかを行っているので、 これをどうにか自動化できないか考えるようになりました。そんな時に、ExcelをPythonで操作できるライブラリopenpyxlがあることを知ったので、それを用いた自動化を行ってみたいと思います。

前提条件

• test_evidence.xlsxというExcelファイルを事前に作成し、そのファイルに新たにシートを作成、自動で画像を貼り付ける実装を行います。
• 試験項目は今回の検証用に簡易的なサンプルとして作成したものを活用します。

• 試験項目No.1というシートを作成し、1_前提条件、2_実行結果という項目に沿って画像を貼り付けます。

• 画像は検証用に任意のサイズの異なる画像を使用します。
• コメントについては作成したシートに自身で追記します。

実装

最初にopenpyxlライブラリをinstallします。

pip install openpyxl

実装はgithubにも格納しました。

22_image_to_excel

実際のディレクトリ構造になります。ポイントは、「1_前提条件」と「2_試験結果」というディレクトリ名になります。 preffixに数字をつけることで、昇順の順序を管理します。またこのディレクトリ名を項目として、Excelシートに記載します。 画像についてもディレクトリの中の画像ファイル名を昇順ソートしているため、過去から未来に向かって並ぶ順序になります。

.
├── 1_前提条件
│   ├── Screenshot 2023-06-17 at 9.07.09.png
│   └── Screenshot 2023-06-17 at 9.07.33.png
├── 2_試験結果
│   ├── Screenshot 2023-06-17 at 9.07.16.png
│   └── Screenshot 2023-06-17 at 9.07.40.png
├── README.md
├── image_to_excel.py
├── requirements.txt
└── test_evidence.xlsx

2 directories, 8 files

## image_to_excel.py
import os
import argparse
import openpyxl
from openpyxl.drawing.image import Image
from openpyxl.utils import get_column_letter


def main(excel_file_name, sheet_name): 
    # Check if the input file exists in the current directory
    if not os.path.isfile(excel_file_name):
        print(f"エラー： '{excel_file_name}' は存在しません。")
        exit()

    # Load the selected excel file
    workbook = openpyxl.load_workbook(excel_file_name)

    # Add a new sheet with the given name
    sheet = workbook.create_sheet(sheet_name)

    # Get all directories from the current directory
    directories = sorted([d for d in os.listdir() if os.path.isdir(d)])
    # Initialize the row in which the image will be pasted
    row = 2

    # Iterate through the directories and process the images
    for directory in directories:
        # Write the directory name in the first column
        sheet.cell(column=1, row=row).value = directory
        row += 3
        # Iterate through the image files in the directory
        for image_file in sorted(os.listdir(directory)):
            if image_file.lower().endswith(".png") or image_file.lower().endswith(".jpg"):

                img = Image(os.path.join(directory, image_file))

                # Get the original width and height of the image
                original_width = img.width
                original_height = img.height

                # Set the maximum width and height for resizing
                max_width = 800
                max_height = 800

                # Calculate the aspect ratio of the image
                aspect_ratio = original_width / original_height

                # Determine the final width and height while preserving the aspect ratio
                if aspect_ratio > 1:
                    final_width = max_width
                    final_height = max_width / aspect_ratio
                else:
                    final_width = max_height * aspect_ratio
                    final_height = max_height

                # Set the size of the image
                img.width = final_width
                img.height = final_height

                # Paste the image in the Excel sheet
                sheet.add_image(img, f"{get_column_letter(2)}{row}")

                # Increment the row index, leaving 3 rows gap between images
                row += int(final_height) // 20 + 3

    # Save the workbook
    workbook.save(excel_file_name)

    print("Insert Images to Excel")

if __name__ == '__main__':
    parser = argparse.ArgumentParser()
    parser.add_argument("excel_file_name", help="使用する既存のExcelファイル名")
    parser.add_argument("sheet_name", help="作成するシート名")
    args = parser.parse_args()
    main(args.excel_file_name, args.sheet_name)

この実装のポイントを4点に絞って解説します。

• argparseライブラリ:
  • コマンドライン引数として実行時にexcel_file_nameとsheet_nameを受け取ります。
• sorted():
  • sorted関数を使用して、ディレクトリや画像ファイルを昇順ソートしています。
• openpyxlライブラリ: Excelファイル（.xlsx）を操作するために使用します。
  • openpyxl.load_workbook(): 既存のExcelファイルの読み込みます。
  • workbook.create_sheet() : 新しいシート作成します。
  • sheet.cell(column,row).value: セルに特定のValueを書き込みます。
  • sheet.add_image(): 画像とセル位置を指定し、画像を挿入します。
  • get_column_letter(): 数値を引数として列名のアルファベットを返します。
• リサイズ処理:
  • max_widthとmax_heightの最大値はそれぞれ800としていますが、こちらは任意のサイズで問題ないです。
  • aspect_ratio: 元の画像の幅と高さの比率であるaspect_ratioが計算されます。これにより、画像の縮小/拡大においてaspect_ratioが保持されるようになります。
  • aspect_ratioが 1 よりも大きい場合(元の画像が横長の場合): 幅はmax_widthに設定し、高さは指定されたmax_widthをaspect_ratioで割った値を設定します。
  • aspect_ratioが 1 よりも小さい場合(元の画像が縦長の場合): 幅はmax_heightをaspect_ratioで掛けた値に設定し、高さはmax_heightを設定します。

aspect_ratio計算例

## aspect_ratioが 1 よりも大きい場合
aspect_ratio(2) = original_width(4) / original_height(2)
if aspect_ratio(2) > 1:
    final_width(800) = max_width(800)
    final_height(400) = max_width(800) / aspect_ratio(2)
else:
    final_width = max_height * aspect_ratio
    final_height = max_height

## aspect_ratioが 1 よりも小さい場合
aspect_ratio(0.5) = original_width(2) / original_height(4)
if aspect_ratio(0.5) > 1:
    final_width = max_width
    final_height = max_width / aspect_ratio
else:
    final_width(400) = max_height(800) * aspect_ratio(0.5)
    final_height(800) = max_height(800)

• コマンドライン引数（sys.argv,argparse）についての覚え書き
• Python×Excel】openpyxlの基本(導入からブック・シート・セルの使い方)【徹底解説】
• 【Python】エクセルに画像挿入する｜OpenPyXL基礎
• openpyxlのget_column_letter関数とは？
• アスペクト比とは？1分でわかる意味、計算、縦横比、横縦比、建築物との関係

実行

第一引数にファイル名を第二引数にシート名を指定し、実行します。

(blog_env) kasama.yoshiki@22_image_to_excel % python image_to_excel.py test_evidence.xlsx No.2
Insert Images to Excel
(blog_env) kasama.yoshiki@22_image_to_excel %

縦長の画像は縦長に、横長の画像は横長に出力されています。セル位置も綺麗に挿入されているので想定通りです。

最後に

今回は画像が少なかったため、手動でも良かったなとも思いましたが、試験項目数などが多くなるにつれてこのような自動化が意味を成すと考えています。このブログが少しでもお役に立つと幸いです。