複数のExcelファイルのデータを１つのJSON形式ファイルとして書込みしてみます

はじめに

AWS SDK for Python(boto3) を使って、プログラムをほぼ変更せずに Cloudian と S3 へのアクセスが可能となります。　クレデンシャル情報の定義を変更するだけで、オブジェクトストレージをハイブリッド（オンプレミス：Cloudian、AWS：S3）で使いたい方への参考になればと、、、

概要

複数の同一書式のExcelファイルのデータをJSON形式に変換し、１ファイル１レコードとして、オブジェクトストレージCloudian/S3 に１つのファイルとして書込む Python プログラムです。

そのExcelファイルのフォルダ（Path）と変換後のJSONファイルをパラメータで指定します。同一書式の申請書等の複数のExcelファイルを一括してJSON形式への変換を想定し、なんちゃってRPAとして使えるかもと想定しております。変換するデータ項目についてはプログラム内の「OrderedDict」を参照ください。

パラメータは以下の３種類となります。
--rpath ： 複数Excelファイルが存在するディレクトリ（Path）を指定
--wfile ： JSON変換データを書き込むファイル名を指定
--mode ： 変換データの出力先の指定　lo：ローカルへの出力、 s3：Cloudian/S3への出力（デフォルト：lo）

プログラム実行時に、パラメータ「-h」を指定することにより表示されるヘルプも参照ください。

実行環境

macOS Big Sur 11.1
python 3.8.3

クレデンシャル情報の定義

今回はクレデンシャル情報を .zshenv に定義してプログラムを実行しています。接続先に合わせて定義ください。

# AWS S3
export AWS_ACCESS_KEY_ID=xxxxxxxxxxxxx
export AWS_SECRET_ACCESS_KEY=yyyyyyyyyyyyyyyyy
export AWS_DEFAULT_REGION=ap-northeast-1

# Cloudian
#export AWS_ACCESS_KEY_ID=aaaaaaaaaaaaaaaaaa
#export AWS_SECRET_ACCESS_KEY=bbbbbbbbbbbbbbbbbbbb
#export AWS_DEFAULT_REGION=pic

実行プログラム

Cloudianへデータ出力する場合は endpoint_url を記載ください（プログラム内を参照ください）。

ManyExcelSample-openpyxl.py

import json
import time
from datetime import date, datetime
from collections import OrderedDict
import argparse
import string
import boto3
import pprint
import sys
import openpyxl
import pathlib

BUCKET_NAME = 'boto3-cloudian'

# 複数のExcelファイルの取得
def excels_to_json(rpath) :

    xlsx_files = list(pathlib.Path(rpath).glob('*.xlsx'))   # xlsxファイル群のリスト作成
    many_excel_list = []

    for i in xlsx_files:
        rfile = '%s%s' % (rpath, i.name)                    # パス＋xlsxファイル
        many_excel_list.append([excel_to_json(rfile)])      # JSONデータの作成

    return many_excel_list, len(xlsx_files)


# 対象Excelファイルから送信JSONデータの作成
def excel_to_json(rfile) :

    excel_rfile = openpyxl.load_workbook(rfile)                                 # Excelのロード
    sheet = excel_rfile['申請書']                                                # ExcelのSheet名

    excel_list = OrderedDict({
        "file_name": rfile,                                                     # 変換Excelファイル名
        "time": generate_time(),                                                # データ変換時間
        "manage_id": sheet.cell(row = 5, column = 4).value,                     # 管理ID
        "date": json_trans_date(sheet.cell(row = 8, column = 4).value),         # 申込日
        "class": sheet.cell(row = 12, column = 4).value,                        # 申込み区分
        "product": sheet.cell(row = 13, column = 4).value,                      # 対象製品
        "from_date": json_trans_date(sheet.cell(row = 14, column = 4).value),   # 期間（From）
        "to_date": json_trans_date(sheet.cell(row = 14, column = 6).value),     # 期間（To）
        "comp": sheet.cell(row = 17, column = 4).value,                         # 会社名
        "dept": sheet.cell(row = 18, column = 4).value,                         # 部署
        "name": sheet.cell(row = 19, column = 4).value,                         # 担当者
        "tel": sheet.cell(row = 20, column = 4).value,                          # 電話番号
        "email": sheet.cell(row = 21, column = 4).value,                        # メールアドレス
        "guide": sheet.cell(row = 24, column = 4).value,                        # 案内有無
    })

    return excel_list


# データ生成時間
def generate_time():
    dt_time = datetime.now()
    gtime = json_trans_date(dt_time)
    return gtime

# date, datetimeの変換関数
def json_trans_date(obj):
    # 日付型を文字列に変換
    if isinstance(obj, (datetime, date)):
        return obj.isoformat()
    # 上記以外は対象外.
    raise TypeError ("Type %s not serializable" % type(obj))


# メイン（ローカル出力用）
def lo_openpyxl(rpath, wfile) :
    print('Excelファイルと同じディレクトリにJSONファイルを生成')
    excel_dict, count = excels_to_json(rpath)
    pprint.pprint(excel_dict)

    with open(wfile, mode = 'w', encoding = 'utf-8') as f:
        f.write(json.dumps(excel_dict, ensure_ascii = False, indent = 4))
    
    return count


# メイン（Cloudian/S3 出力用）
def s3_openpyxl(rpath, wfile) :
    print('Cloudian/S3 にJSONファイルを生成')
    excel_dict, count = excels_to_json(rpath)
    
    # client = boto3.client('s3', endpoint_url='http://s3-pic.networld.local')    # Cloudianへのアクセス時
    client = boto3.client('s3')                                                 # S3へのアクセス時
    client.put_object(
        Bucket=BUCKET_NAME,
        Key=wfile,
        Body=json.dumps(excel_dict, ensure_ascii = False, indent = 4)
    )
    return count


if __name__ == '__main__':
    parser = argparse.ArgumentParser(description='複数のExcelデータファイルをopenpyxlを使用して、１つのJSONデータファイルを生成')
    parser.add_argument('--rpath', type=str, default='./', help='複数Excelファイルが存在するディレクトリ（Path）を指定')
    parser.add_argument('--wfile', type=str, help='JSON変換データを書き込むファイル名を指定')
    parser.add_argument('--mode', type=str, default='lo', help='lo（変換データをローカル出力）／ s3（変換データをCloudian/S3出力）')
    args = parser.parse_args()

    if not args.wfile:
        print("\n\r 書き込むJSONファイルを指定ください \n\r")
        sys.exit()

    start = time.time()

    if (args.mode == 's3'): 
        count = s3_openpyxl(args.rpath, args.wfile)
    else :
        count = lo_openpyxl(args.rpath, args.wfile)

    convert_time = time.time() - start

    print("")
    print(f"データ変換Excelファイル数:{count}")
    print("データ変換時間:{0}".format(convert_time) + " [sec]")
    print("")

複数の同一書式のExcelファイル

今回、２つの同一書式Excelファイルを用意しています（何かの申請書を想定）。

$ ls -al *.xlsx
-rwxrwxrwx@ 1 hoge  staff  14012 12 30 14:27 001-ABC_SampleForm.xlsx*
-rwxrwxrwx@ 1 hoge  staff  14007 12 30 14:27 002-XYZ_SampleForm.xlsx*

プログラムの実行

最初にヘルプを表示してみます。

$ python ManyExcelSample-openpyxl.py -h            
usage: ManyExcelSample-openpyxl.py [-h] [--rpath RPATH] [--wfile WFILE] [--mode MODE]

複数のExcelデータファイルをopenpyxlを使用して、１つのJSONデータファイルを生成

optional arguments:
  -h, --help     show this help message and exit
  --rpath RPATH  複数Excelファイルが存在するディレクトリ（Path）を指定
  --wfile WFILE  JSON変換データを書き込むファイル名を指定
  --mode MODE    lo（変換データをローカル出力）／ s3（変換データをCloudian/S3出力）

次に、プログラムと同一フォルダにある複数のExcelファイルのダータをLocalのJSONファイル出力してみます。

$ python ManyExcelSample-openpyxl.py --wfile abc.json
　　　　　：
   　出力内容は割愛
　　　　　：
データ変換Excelファイル数:2
データ変換時間:0.03844475746154785 [sec]

作成されたJSONデータを確認してみます。

$ cat abc.json
[
    [
        {
            "file_name": "./002-XYZ_SampleForm.xlsx",
            "time": "2021-01-22T14:40:01.478800",
            "manage_id": "BJ74-456789-02",
            "date": "2020-12-27T00:00:00",
            "class": "新規",
            "product": "Azure",
            "from_date": "2021-01-01T00:00:00",
            "to_date": "2021-03-31T00:00:00",
            "comp": "XYZ, Inc.",
            "dept": "IoTソリューション部",
            "name": "磯野　カツオ",
            "tel": "06-333-4567",
            "email": "[e-mail]",
            "guide": "ご案内を希望します"
        }
    ],
    [
        {
            "file_name": "./001-ABC_SampleForm.xlsx",
            "time": "2021-01-22T14:40:01.494818",
            "manage_id": "BJ64-123456-01",
            "date": "2020-12-28T00:00:00",
            "class": "新規",
            "product": "VMware",
            "from_date": "2020-12-28T00:00:00",
            "to_date": "2021-12-28T00:00:00",
            "comp": "ABC, Corp.",
            "dept": "技術本部",
            "name": "山田　太郎",
            "tel": "03-1111-2222",
            "email": "[e-mail]",
            "guide": "不要"
        }
    ]
]

今度は、それらのExcelファイルデータを Cloudian/S3 にJSONファイル出力してみます。

$ python ManyExcelSample-openpyxl.py --wfile abc.json --mode s3

データ変換Excelファイル数:2
データ変換時間:0.2519500255584717 [sec]

参考情報

以下の情報を参考にさせていただきました。感謝いたします。

pythonでExcelのデータをJSONに変換する

ExcelファイルをJSONに一括変換【Python】

まとめ

今回は、AWS SDK for Python(boto3) を使って、オブジェクトストレージ Cloudian / S3 へ Excelデータ を JSONデータに変換し保存することを確認できました。

こちらの記事 も参照ください。