1. はじめに
みなさん初めまして。株式会社電業でシステム担当をしている竹本です。
2023年1月にIBM Powerサロンで講演させて頂きましたので、会社紹介や私のプロファイルについてはそちらをご覧下さい。
中小企業ではご存じの通り、システムへの投資のための予算を捻出することがなかなか難しいという現場があります。それでも私は、システムを活用して現場の負担を少なくして働きやすい環境を作りたいと、今まで培った自分の知っている限りのIT知識でいろんな取り組みをしてきました。サロンでお話した自動倉庫とIBMiの連携もそのひとつです。ところが、その他の事例についてもぜひ紹介してほしいとの要望を頂き、ブログでの連載という形で発信していく事になりました。
この記事を読んで頂くとおわかりになると思いますが、ソリューションを購入しなくても、ちょっとした工夫を行う事で自分たちでいろんなことができるはずです。
私は中小企業のシステム担当として自分でできることはやれば良い、という信条があります。従いまして私の持っている知識、今までやってきた事例を公開して、是非参考にして頂ければと思っております。この連載はみなさんのお役にたてばと思い記載しています。
かなり技術寄りの記事になりますが、ご興味がありましたら一読頂ければ幸いです。
今回はIBMiで作成されたcsvのデータをExcelファイルに変換するpythonスプリクトの紹介になります。
きっかけはIBMiのデータをExcelで利用したいという声がありツールを検討しましたが、いずれも導入コスト+年間保守料金が高価である為、コストメリットが合わなかったことです。月数回レベルの利用回数ではツールを導入するメリットがありませんし、一部のデータはプログラミング処理が必要で、ツールでは対応できない内容もありました。
しかしながら、現場の声を聞いて検討した結果、高機能版は不要で限定されたものでも十分に活用できるのではないかと判断し内作する事にしました。検証版ではcsv作成まではRPG, excelへの変換はvb.netで構築しましたが最低限の機能しか実装できず諦めた機能もありました。(目的は達成しました)
時間が経過するにつれ.NetFrameWork4.6の保守サポート停止があり、本稼働環境で使用するとなるとVisual Studioが高価であるため、別の手段を検討していました。
2022年からIBMiでpythonが稼働する情報が多くなり調べた結果、pythonでも構築可能ではないかと判断しました。Pythonは過去にシステム開発で経験がありましたので、Excel出力にチャレンジする事にしました。
尚、開発にあたって下記項目を要件としました。
- 全てフリーソフトを利用する(ランニングコストを含めて費用が掛からないにように考慮する)
- Excelで表示される項目を見易くするため、エンドユーザーが見易い形にする。(1行置きに編みかけ表示及び列幅自動調整及び表示編集形式)
- Excelのピボット集計を可能とする(定型化できる集計は人が操作しなくても良い:テスト実装)
- Excelのピボット集計されたデータのグラフ化を可能とする(より視覚性を向上させる:テスト実装)
- 複数のCSVファイルを1つの.xlsxファイル(複数シート)にまとめることを可能とする。(ファイルを複数作成せず1つのブックにまとめる)
- 生成したxlsxファイルの取得方法をメール及びftpで送信可能とする。(ftp送信の場合、出力ファイル名に日本語を使用しないで下さい。)
今回IBM i 上で稼働する事を目指しPASE上のpython3.9で作成しました。
尚、データ抽出部分は固定フォーマットのSQL-RPGⅢもしくはILE-SQLRPGで作成しています。(RPGで作成する事で長期間ソースコードの変更なしで運用可)
また、ピボット集計とグラフはテスト実装(動くかの確認)ですので、本番で使用すると機能不足があるかもしれません。この点は今後の検討内容です。
(コラム)
ここまで作っていて、今更ですがコードページに関する問題などがありWebアプリとして作成した方が楽だったと思います。
過去からIBMiを使用されている企業はWeb環境設定をしなくても動作する環境が一番便利ではないかと思い開発を継続しました。
ただし、Excel変換のメインはpythonで動作しますので、pythonのWebフレームワークに取り込む事は問題ないかと思います。
2. 環境
No. | 項目 | 内容 | 備考 |
---|---|---|---|
1 | ハードウェア | Power10 (9105-41B) 1 Core | – |
2 | OS | IBMi V7.5 TR 3 CCSID 5026 : 日本語カタカナ | – |
3 | Python | 3.9 | Windows環境ではPython3.10でも動作します。 |
4 | openpyxl | Pythonのパッケージ:Excelファイル操作 | – |
5 | Pandas | Pythonのパッケージ:CSVファイル操作 | – |
6 | 5250エミュレータ | ACS 1.1.9.3 | ホストコードページ=930 |
※pythonスプリクト関連はzipファイルに圧縮していますので、解凍する為にツールが必要です。
- Windowsでzipファイルを解凍しWindowsからIBMiへftpで転送
- zipファイルをIFSにFTPで転送後に解凍する場合はunzipが必要です。
※PythonスプリクトはWindows環境でも動作します。(動作確認バージョンは上記の通り、3.9です。)
3. 概要
3.1 全体構成
- 5250画面にて選択条件入力
抽出条件から、SQL文をQTEMP/SQLWORK作成し、CLのパラメータとしてIDと画面に表示した出力件数を渡します。 - DEF出力
Excelの書式を設定する情報をフィールド選択からカラム出力順に読込み、DEFファイル(QTEMP/CSVTXT)を出力します。 また、CLに渡されたIDよりExcel設定ファイルを参照してテーブルスタイル情報をDEFに出力します。 - CSV
QTEMP/SQLWORKに出力されたSQL文を読込み、SQLを実行します。
SQLから商品コードを取出し、商品マスターデータを取得します。取得した商品マスターデータはフィールド選択ファイルに従ってCSVデータを出力します。フィールド選択ファイルはカラム(フィールド)出力順でCSVデータ(QTEMP/CSVTXT2)を出力します。 CSVヘッダー(タイトル)はフィールド選択ファイルの情報を使用してCSVの1行目に出力します。 - IFSへのデータ転送
今回のサンプルでは、FTPを使用してIFS上にDEF及びCSVをテキスト転送します。
※コード変換はFTPが行ってくれます。 - Python実行するシェルスクリプトを実行
QP2SHELLにてPASE環境のシェルスクリプトを実行します。このシェルからPythonスクリプトを実行し.xlsxファイルを生成します。
以上が概要の流れです。
(サンプルプログラム)
以下で説明するサンプルプログラム(ibm_i_EXCEL_LIBRARY_V7R3M0.savf、Pythonスクリプト、サンプルデータ)をこちらからダウンロードいただけます。
・1~4まではEXCELLIBライブラリーに作成しておりソース及びプログラムはSAVLIBでibm_i_EXCEL_LIBRARY_V7R3M0.savfに保管しています。
No | ファイル名 | タイプ | 属性 | ファイル内容 | 備考 |
---|---|---|---|---|---|
1 | QRPGLESRC | *FILE | PF-SRC | ILERPG ソースファイル |
|
2 | QCLLESRC | *FILE | PF-SRC | ILECL ソースファイル |
|
3 | QDSPSRC | *FILE | PF-SRC | 画面 ソースファイル |
|
4 | QDDSSRC | *FILE | PF-SRC | 物理・論理 ソースファイル |
|
5 | QCMDSRC | *FILE | PF-SRC | コマンド ソースファイル |
|
6 | XSNDFTP | *PGM | RPGLE | FTP送信プログラム(本体) | 実態はSTRTCPFTPでFTP転送 |
7 | XSNDFTPC | *PGM | CLLE | FTP送信プログラム(CL) | |
8 | XSNDFTP | *CMD | FTP送信プログラム(コマンド) | ||
9 | CHKHIMEDR3 | *PGM | CLLE | /home/<USER>/excelディレクトリーチェック | ディレクトリーが存在しなければ作成する |
10 | CVTCSVEXCL | *PGM | CLLE | DEF及びCSVファイル転送及びCSV->EXCEL変換実行 | |
11 | CSVEXCELC | *PGM | CLLE | PASE環境pythonスクリプトの実行 | run_csvExcel2.shを実行 |
12 | CSVEXCEL | *CMD | CSVEXCELCのコマンド | ||
13 | SAMPLE01 | *PGM | RPGLE | サンプル01プログラム | |
14 | SAMPLE01C | *PGM | CLLE | サンプル01プログラム起動 | |
15 | SAMPLE01EX | *PGM | CLLE | サンプル01プログラムからCALLされるCL | このCLでIFSへの転送及びEXCEL変換を起動 |
16 | UTLCATKNJ | *PGM | RPGLE | 2つの漢字フィールド結合プログラム | |
17 | UTLCHKSTS | *PGM | RPGLE | IFS上のステータスファイルチェック | パラメータで結果を返す。 |
18 | UTLCNVEBC | *PGM | RPGLE | 5026<->5035間のコード変換 | |
19 | UTLGETEXCL | *PGM | RPGLE | EXCEL用パラメータ取得 | EXCELのみ |
20 | UTLGETPRM | *PGM | RPGLE | パラメータ取得 | EXCEL以外 |
21 | UTLHSTLOG | *PGM | RPGLE | 実行履歴出力 | 実行履歴をCSVHSTLOGに出力 |
22 | UTLOUTDEF | *PGM | RPGLE | DEFファイルデータ出力 | |
23 | UTLSEPKNJ | *PGM | RPGLE | 漢字フィールド分割 | 前述しています。 |
24 | UTLTRMKNJ | *PGM | RPGLE | 漢字フィールド後ろ空白削除 | |
25 | UTLXMLESC | *PGM | RPGLE | XML文字エスケープ処理 | |
26 | XCHKFTP | *PGM | RPGLE | FTP実行結果チェック | |
27 | XCSVMSGF | *FILE | CSV->EXCELメッセージファイル | ||
28 | CSVCOL | *FILE | PF-DTA | EXCELフィールド選択ファイル | |
29 | CSVCOLL1 | *FILE | LF | EXCELフィールド選択論理ファイル | |
30 | CSVEDT | *FILE | PF-DTA | EXCEL表示編集形式設定ファイル | |
31 | CSVEXCLPRM | *FILE | PF-DTA | パラメータファイル(EXCEL用) | |
32 | CSVHSTLOG | *FILE | PF-DTA | 実行履歴ファイル | |
33 | CSVPARM | *FILE | PF-DTA | パラメータファイル(EXCEL以外) | |
34 | CSVTXT | *FILE | PF-DTA | XMLテキストファイル用(DEF) | レコード長:256バイト |
35 | CSVTXT2 | *FILE | PF-DTA | CSVテキストファイル用 | レコード長:450バイト |
36 | CSVTXT3 | *FILE | PF-DTA | XMLテキストファイル用(BND) | レコード長:256バイト |
37 | CXHNMP | *FILE | PF-DTA | サンプル用商品マスター | |
38 | SAMPLE01D | *FILE | DSPF | サンプル01用表示装置ファイル | |
39 | SQLWORK | *FILE | PF-DTA | SQL文ワークファイル | |
40 | CSVHSTLOG | *CMD | 実行履歴コマンド | ||
41 | UTLCSVESC | *PGM | RPGLE | CSVエスケープ処理 2024.04.08追加 |
ダブルクォート文字エスケープ処理 |
42 | UTLCSVESCD | *CMD | CSVエスケープ処理コマンド 2024.04.08追加 |
ダブルクォート文字エスケープ処理 | |
43 | TSTCSVESCD | *FILE | DSPF | CSVエスケープ処理テスト画面 2024.04.08追加 |
ダブルクォート文字エスケープ処理テスト |
44 | TSTCSVESC | *PGM | RPGLE | CSVエスケープ処理テスト 2024.04.08追加 |
ダブルクォート文字エスケープ処理テスト |
・5のシェルスクリプト及びpythonスクリプトはIBM i_pythonスクリプト.zipに圧縮しています。
No | ファイル名 | ファイル内容 | 配置先 |
---|---|---|---|
1 | config.ini | CSV→Excelファイル変換設定ファイル | プログラムディレクトリー(例:/opt/excel) |
2 | csvExcel_log_conf.json | ログ設定ファイル(pythonの実行ログ) | 同上 |
3 | mail_body.txt | メールテンプレート | 同上 |
4 | csvExcel.py | メインプログラム | 同上 |
5 | csvExceConst.py | 定数定義 | 同上 |
6 | csvExcelImportXml.py | CSV及びXMLインポート | 同上 |
7 | csvExcelUtil.py | ユーティリティ | 同上 |
8 | csvPivot.py | ピボットテーブル及びグラフ作成 | 同上 |
9 | chk_home_dir.sh | /homeディレクトリーチェック | 同上 |
10 | run_csvExcel2.sh | CSV->Excel実行シェル(内部でrun_csvExcel.shを実行) | 同上 |
11 | run_csvExcel.sh | CSV->Excel実行シェル(実行本体) | 同上 |
3-2.実行結果画面
3-2-1.ピボット集計・グラフ付き変換
3-2-2.複数のCSVを1つのEXCELファイルに変換
3-2-3.拡大図(複数のCSVファイルを1つのExcelファイルにまとめたもの)
3-2-4.拡大図(ピボット集計・グラフ描画)
3-3.プログラム概要
- 1つのCSVファイルをExcel(.xlsx)ファイルに変換もしくは、複数のCSVファイルをExcel(.xlsx)ファイルに変換します。
- CSVファイルが1つの場合は、Excelファイル変換時にピボット集計およびグラフ描画ができます。(※CSVファイルを複数まとめる場合はピボットおよびグラフは不可)
- 変換したExcel(.xlsx)ファイルはメール送信もしくはftpサーバーへ転送します。
- 図中の.DEFファイルは、CSVファイル変換時のExcelの表示形式やピボット集計及びグラフ生成方法が記述されたファイルです。
- 1つのCSVファイルに対し1つのDEFファイルが必要です。
- BNDファイルは、複数のCSVファイルを1つのExcelファイルにまとめる場合に使用します。(複数CSVファイルはシートで分かれます。)
弊社では、SQL-RPGⅢ、ILE-SQLRPGⅣで開発したプログラムでDEF及びCSVファイルを作成しています。下記画面がそのプログラム画面の一例になります。
下記画面は弊社で使用している得意先マスターEXCEL作成ですが他に商品、在庫等々があります。
データ(DEFファイルおよびCSVファイル)をQTEMPに出力し、XSNDFTPコマンドでIFS上のファイルへ転送します。詳細は後述します。(EBCDIC->JISコード変換および漢字コード変換もFTPで行います。この為、XSNDFTPコマンドで転送したファイルのコード変換は不要です。)
4. 機能
CSVファイル(シフトJIS)からExcel(.xlsx)ファイルへの変換
No | 機能 | 内容 | 備考 |
---|---|---|---|
1 | ピボット集計・グラフ描画 | 集計する縦軸、横軸、集計項目を記述する事でピボット集計が可能。 集計した結果のグラフ表示が可能。 ※別シートに作成します。 |
複数のCSVをExcelファイル変換する場合はピボット集計、グラフ描画不可 ※シート数が多くなるため制約とした。 |
2 | 書式設定 | テーブルスタイル及び数字項目表示形式を自動設定 | 1行置きに網掛けが行われる。データが見やすい。 |
3 | 列幅・フォント設定 | データ変換時にフォントやセル列幅を設定 | 変換後のデータは見やすい。 |
4 | 複数CSVファイルを変換 | 複数のCSVファイルを1つのExcelファイルに変換 | CSVファイルはシートで分かれます。 |
5 | 生成したExcelの送信 | SMTPもしくはFTP送信 | config,iniに記述します。 |
6 | エラーログファイル(python) | 指定した場所にログファイルを生成 | エラー時対応に使用します。 |
※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で条件を生成します。
②商品名漢字検索 この漢字検索は空白で文字列を最大5つまで分割しWHERE句のLIKE 条件をANDで生成します。分割した文字列の末尾に%を自動付与します。2番目以降の分割した文字には文字列の先頭に%を自動付与します。
これは商品名に寸法や仕様が含まれている場合に使用すると便利です。この機能をサンプルとして実装したのは、弊社社内システムで実装し評価が良かったからです。
上記2つの条件からSQL文を生成し実行します。(→STEP2へ)
5.2. (STEP2) 検索結果画面
STEP1で入力された条件でSQLを生成し、出力したデータを1データ1行で画面に表示します。
▲ 1画面目
▲ 2画面目
①キーボードのF11を押すと、1画面で表示できなかったフィールドが表示されます。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ファイル変換を実行します。
上記②は、甘く見ていていた事を非常に後悔しました。
PASE環境はAIX(いわゆるUNIX環境)ですので、英大文字小文字を区別します。この英大文字小文字は5035ベースのものですので、5026環境で使える英小文字ではありません。また5250のホストコードページの設定や実行しているジョブのCCCSIDにも影響されます。
最初、簡単に作成できると思い込んでおり、英小文字が文字化けした原因と解決方法に非常に悩み、時間もかかりました。
本来は、CCSIDを5035かその発展系である1399にすべきですが、変更する労力に比べると得られるメリットが少ない事と、5577エミュレーションプリンターで1399で新たに使用可能となった漢字が正しく印刷できるかの検証があるため見送ったのです。
2024年現在、プリンターメーカーに問い合せても5577関連では不明な事が多い状態です。5577エミュレーションの場合、旧JIS(1978)、新JIS(1980)の設定しかない事が多く1980以降に追加された漢字がどうなるか不明です。
③ EXCELファイルの起動
- サンプル01では、PC (Windows10,11)にバッチファイルを導入する必要があります。(CSVEXCELFTP.BAT)
- このバッチファイルをSTRPCCMDで呼ぶ事により生成したEXCELファイルをFTPにてPCにダウンロードし、EXCELを実行します。
- この機能は、本来、実装を予定していませんでしたが、前述した通り変換後のEXCELファイルが最後に起動されると、分かりやすいと考えたからです。
上記③も、甘く見ていていた事を後悔しました。
CSV->EXCEL変換は、PASE環境でPythonを実行しますので、実行エラーは当然ありえる話です。
では、Pythonで発生したエラーをどうやってRPGやCLで把握するかになります。当初はRTVJOBAでRTNCDEがあるから使えるのではと思い、深く調査せずにCLでPASE環境のPythonを実行するシェルを、QP2SHELLでCALLした後に発覚しました。シェルでは正しくリターンコードを返しているのですが、このリターンコードはRPG及びCLでは取得できません。QP2SHELLのAPIも調査しましたが取得できませんでした。リターンコードはRPGやCLに戻してよ、と本当に言いたくなりました。結局、Pythonスクリプト終了時に成功・不成功情報をステータスファイル(テキストファイル)に出力するように変更しました。ILERPGでこのファイルを直接読込み、ASCII->EBCDIC変換後、*ENTRYのパラメータにセットしCLでエラー処理を実行するようにしました。
※「IBM i 7.5 プログラミング」の「IBM PASE for i」の最後に、PASE環境のsystemコマンドでリターンコードをCL変数に設定できると記述がありました。今回は、IBM i →PASEの流れで動作しています。systemコマンドPASE環境からCLを呼び出しますので、欲しい仕様ではありませんでした。
次回からは、実際のサンプルプログラムの流れについて、考慮点も交えてご説明してまいります。
(サンプルプログラムと注意事項)
当記事のサンプルプログラムをダウンロードいただけます。本サンプルプログラムは、学習・参考目的で提供されております。ダウンロードは可能ですが、動作保証は致しかねます。環境や条件により、正常に動作しない場合がありますので、予めご了承ください。また、使用に際しては自己責任で行っていただきますようお願い申し上げます。
サンプルプログラムのダウンロードはこちらから!
dengyo_1_dl
日本アイ・ビー・エム株式会社が毎月開催しているIBM Powerユーザーのための自由な語り場「IBM Power Salon」(月1回、第二水曜日の朝9時から開催)をご存じでしょうか?
2023年1月11日に開催された第14回の、株式会社 電業様による「惜しみなく共有しちゃいます、根っからのエンジニアが語る、IBM i 内製化のリアル」では、既存のIBM i 環境を見事に活用し、自作でIBM i と自動倉庫をつなげDXを実現された素晴らしい事例が披露されました。
ビデオはこちら→https://video.ibm.com/recorded/132452956
とはいえ、1時間の講演時間では語りきれなかった詳細は、きっと他のIBM i ユーザーの方にも参考になるはず!ということで、株式会社電業 総務部 竹本伸明様に“内製DX”について具体的にご説明いただきます。読めばきっと、「IBM i でここまでできるんだ!」と目から鱗が落ちるはずです。