【IBM Power Salon】株式会社 電業様
「根っからのエンジニアが語る IBM i 内製化のリアル」実践編～IBM i CSVファイルからExcelファイルへの変換 その1

1. はじめに

みなさん初めまして。株式会社電業でシステム担当をしている竹本です。
2023年1月にIBM Powerサロンで講演させて頂きましたので、会社紹介や私のプロファイルについてはそちらをご覧下さい。
中小企業ではご存じの通り、システムへの投資のための予算を捻出することがなかなか難しいという現場があります。それでも私は、システムを活用して現場の負担を少なくして働きやすい環境を作りたいと、今まで培った自分の知っている限りのIT知識でいろんな取り組みをしてきました。サロンでお話した自動倉庫とIBMｉの連携もそのひとつです。ところが、その他の事例についてもぜひ紹介してほしいとの要望を頂き、ブログでの連載という形で発信していく事になりました。

この記事を読んで頂くとおわかりになると思いますが、ソリューションを購入しなくても、ちょっとした工夫を行う事で自分たちでいろんなことができるはずです。
私は中小企業のシステム担当として自分でできることはやれば良い、という信条があります。従いまして私の持っている知識、今までやってきた事例を公開して、是非参考にして頂ければと思っております。この連載はみなさんのお役にたてばと思い記載しています。
かなり技術寄りの記事になりますが、ご興味がありましたら一読頂ければ幸いです。

今回はIBMｉで作成されたcsvのデータをExcelファイルに変換するpythonスプリクトの紹介になります。
きっかけはIBMｉのデータをExcelで利用したいという声がありツールを検討しましたが、いずれも導入コスト＋年間保守料金が高価である為、コストメリットが合わなかったことです。月数回レベルの利用回数ではツールを導入するメリットがありませんし、一部のデータはプログラミング処理が必要で、ツールでは対応できない内容もありました。

しかしながら、現場の声を聞いて検討した結果、高機能版は不要で限定されたものでも十分に活用できるのではないかと判断し内作する事にしました。検証版ではcsv作成まではRPG, excelへの変換はvb.netで構築しましたが最低限の機能しか実装できず諦めた機能もありました。（目的は達成しました）
時間が経過するにつれ.NetFrameWork4.6の保守サポート停止があり、本稼働環境で使用するとなるとVisual Studioが高価であるため、別の手段を検討していました。
2022年からIBMｉでpythonが稼働する情報が多くなり調べた結果、pythonでも構築可能ではないかと判断しました。Pythonは過去にシステム開発で経験がありましたので、Excel出力にチャレンジする事にしました。
尚、開発にあたって下記項目を要件としました。

1. 全てフリーソフトを利用する（ランニングコストを含めて費用が掛からないにように考慮する）
2. Excelで表示される項目を見易くするため、エンドユーザーが見易い形にする。（１行置きに編みかけ表示及び列幅自動調整及び表示編集形式）
3. Excelのピボット集計を可能とする（定型化できる集計は人が操作しなくても良い：テスト実装）
4. Excelのピボット集計されたデータのグラフ化を可能とする（より視覚性を向上させる：テスト実装）
5. 複数のCSVファイルを１つの.xlsxファイル（複数シート）にまとめることを可能とする。（ファイルを複数作成せず１つのブックにまとめる）
6. 生成したxlsxファイルの取得方法をメール及びftpで送信可能とする。（ftp送信の場合、出力ファイル名に日本語を使用しないで下さい。）

今回IBM i 上で稼働する事を目指しPASE上のpython3.9で作成しました。
尚、データ抽出部分は固定フォーマットのSQL-RPGⅢもしくはILE-SQLRPGで作成しています。（RPGで作成する事で長期間ソースコードの変更なしで運用可）
また、ピボット集計とグラフはテスト実装（動くかの確認）ですので、本番で使用すると機能不足があるかもしれません。この点は今後の検討内容です。

2. 環境

※pythonスプリクト関連はzipファイルに圧縮していますので、解凍する為にツールが必要です。

1. Windowsでzipファイルを解凍しWindowsからIBMｉへftpで転送
2. zipファイルをIFSにFTPで転送後に解凍する場合はunzipが必要です。

※PythonスプリクトはWindows環境でも動作します。（動作確認バージョンは上記の通り、3.9です。）

3. 概要

3.1 全体構成

1. 5250画面にて選択条件入力
   抽出条件から、SQL文をQTEMP/SQLWORK作成し、CLのパラメータとしてIDと画面に表示した出力件数を渡します。
2. DEF出力
   Excelの書式を設定する情報をフィールド選択からカラム出力順に読込み、DEFファイル（QTEMP/CSVTXT）を出力します。 また、CLに渡されたIDよりExcel設定ファイルを参照してテーブルスタイル情報をDEFに出力します。
3. CSV
   QTEMP/SQLWORKに出力されたSQL文を読込み、SQLを実行します。
   SQLから商品コードを取出し、商品マスターデータを取得します。取得した商品マスターデータはフィールド選択ファイルに従ってCSVデータを出力します。フィールド選択ファイルはカラム（フィールド）出力順でCSVデータ(QTEMP/CSVTXT2)を出力します。 CSVヘッダー（タイトル）はフィールド選択ファイルの情報を使用してCSVの１行目に出力します。
4. IFSへのデータ転送
   今回のサンプルでは、FTPを使用してIFS上にDEF及びCSVをテキスト転送します。
   ※コード変換はFTPが行ってくれます。
5. Python実行するシェルスクリプトを実行
   QP2SHELLにてPASE環境のシェルスクリプトを実行します。このシェルからPythonスクリプトを実行し.xlsxファイルを生成します。

以上が概要の流れです。

・１～4まではEXCELLIBライブラリーに作成しておりソース及びプログラムはSAVLIBでibm_i_EXCEL_LIBRARY_V7R3M0.savfに保管しています。

・5のシェルスクリプト及びpythonスクリプトはIBM i_pythonスクリプト.zipに圧縮しています。

3-2．実行結果画面

3-2-1．ピボット集計・グラフ付き変換

3-2-2．複数のCSVを１つのEXCELファイルに変換

3-2-3．拡大図（複数のCSVファイルを１つのExcelファイルにまとめたもの）

3-2-4．拡大図（ピボット集計・グラフ描画）

3-3．プログラム概要

• １つのCSVファイルをExcel(.xlsx)ファイルに変換もしくは、複数のCSVファイルをExcel(.xlsx)ファイルに変換します。
• CSVファイルが１つの場合は、Excelファイル変換時にピボット集計およびグラフ描画ができます。（※CSVファイルを複数まとめる場合はピボットおよびグラフは不可）
• 変換したExcel（.xlsx）ファイルはメール送信もしくはftpサーバーへ転送します。
• 図中の.DEFファイルは、CSVファイル変換時のExcelの表示形式やピボット集計及びグラフ生成方法が記述されたファイルです。
• １つのCSVファイルに対し１つのDEFファイルが必要です。
• BNDファイルは、複数のCSVファイルを１つのExcelファイルにまとめる場合に使用します。（複数CSVファイルはシートで分かれます。）

弊社では、SQL-RPGⅢ、ILE-SQLRPGⅣで開発したプログラムでDEF及びCSVファイルを作成しています。下記画面がそのプログラム画面の一例になります。
下記画面は弊社で使用している得意先マスターEXCEL作成ですが他に商品、在庫等々があります。

データ（DEFファイルおよびCSVファイル）をQTEMPに出力し、XSNDFTPコマンドでIFS上のファイルへ転送します。詳細は後述します。（EBCDIC->JISコード変換および漢字コード変換もFTPで行います。この為、XSNDFTPコマンドで転送したファイルのコード変換は不要です。）

4. 機能

CSVファイル（シフトJIS）からExcel（.xlsx）ファイルへの変換

※Excelプログラムは不要です。 ※文字コード: ANK:JIS, 漢字：Shift-JIS

※IBM i （Power10、1コア）Python3のExcelファイル変換の実行速度は52列×12,648行で約3分です。（ピボット集計あり、グラフ出力あり）

※SMBでのファイル送信も組込み可能（環境依存が大きい為、今回は組込まず。Pythonにはpysmbモジュールがあります。）

※PASE環境はAIX互換です。この為、文字コードはSJISがデフォルトです。（シェルを作成する場合に注意）

【重視した事】既存環境で動作する事を重視しました。これはCCSID＝5026で動作する事を意味します。

サンプルプログラムの流れ

この動画は一番シンプル（簡単）な機能のみで作成したものです。ピボット集計やグラフ描画は組込んでいません。
また、本来の仕様では実装する予定はなかったのですが、見て分かりやすくするため、変換したExcelファイルを起動するところまで行っています。

5.1.（STEP1）5250画面から検索条件を入力

条件入力は2項目あります。

①商品コード範囲検索 通常の範囲検索です。入力された値からSQLのWHERE句にBETWEENで条件を生成します。

②商品名漢字検索 この漢字検索は空白で文字列を最大５つまで分割しWHERE句のLIKE 条件をANDで生成します。分割した文字列の末尾に％を自動付与します。２番目以降の分割した文字には文字列の先頭に％を自動付与します。

これは商品名に寸法や仕様が含まれている場合に使用すると便利です。この機能をサンプルとして実装したのは、弊社社内システムで実装し評価が良かったからです。

上記２つの条件からSQL文を生成し実行します。（→STEP2へ）

5.2. (STEP2) 検索結果画面

STEP1で入力された条件でSQLを生成し、出力したデータを1データ1行で画面に表示します。

▲ 1画面目

▲ 2画面目

①キーボードのF11を押すと、１画面で表示できなかったフィールドが表示されます。F14を押すと生成されたSQLが画面に表示されます。（デバッグに便利でした）

②F9を押すとExcel生成を開始します。

• プログラムで生成したSQLはCSV生成プログラムで使用しますのでQTEMP/SQLWORKに保存しています。
• CALL命令でSAMPLE01EXを実行し、実行に必要なパラメータを渡します。（ID, ユーザーID、WS番号、日付、時刻、BNDファイル名、DEFファイル名、CSVファイル名）
• このCLでDEF及びCSVファイルを生成し、さらにCSVCVTEXCELをCALLします。
• CSVCVTEXCLにてIFSへのファイル転送及びpythonでのCSV->EXCELファイル変換を実行します。

③ EXCELファイルの起動

• サンプル01では、PC (Windows10,11)にバッチファイルを導入する必要があります。（CSVEXCELFTP.BAT）
• このバッチファイルをSTRPCCMDで呼ぶ事により生成したEXCELファイルをFTPにてPCにダウンロードし、EXCELを実行します。
• この機能は、本来、実装を予定していませんでしたが、前述した通り変換後のEXCELファイルが最後に起動されると、分かりやすいと考えたからです。

次回からは、実際のサンプルプログラムの流れについて、考慮点も交えてご説明してまいります。