RPG で Excel を出力するには
RPG で Excel を出力するには
「 他のファイル形式への変換 」で紹介したとおり、Chicago の CPYTOEXCL コマンドを使用すれば
このコマンドを実行するだけで DB2/400 データ・ベースを MS-Excel ブックに変換することができます。
さらに Chicago の POI というサービス・プログラムをバインドすれば、RPG プログラムや
COBOLプログラムからでも Excel に DB2/400データ・ベースの値を出力することもできます。
「 Excel にデータを変換 」 というと、これまでは BIFF ファイルに単純に変換するソリューションがありましたが、
- BIFF ファイルは Microsoft は廃止の方向にある。
- BIFF ファイルでは Excel 上での文字の色、背景色等のセルの属性を設定できない。
- 全体の置き換えであるのでユーザーが用意したテンプレートに更新することができない。
などの問題があり、BIFFファイルへ出力すること自体が古典的なレガシーな方法であり
将来を見据えたスマートな方法であるとは言えません。
これを解決するのが Chicago で提供される POI という名前のサービス・プログラム( *SRVPGM )です。
POI とは Jakarta-Apache Project の中のひとつである POI プロジェクトというオープン・ソースの
名前に由来しています。
Jakarta-Apache Project POI は Java で書かれたオープン・ソースのため IBM System i 上では
実行速度があまりにも遅いため ㈱オフィスクアトロが C/400 として書き直して System i に移植したのが
Chicago の POI というサービス・プログラム( *SRVPGM ) です。
POI というサービス・プログラムは柔軟であり、単にデータ・ベースの値だけでなく
Excel のセルの属性を設定して文字の色や背景色も RPG/COBOL によって設定することができます。
セル幅を最適化することもできます。
RPG/COBOL で Excel のひとつひとつのセルを自由に扱うことができるのが POI の魅力です。
それではここで POI を利用した RPG による Excel 出力の例をご紹介しましょう。
商品マスター （ QTRFIL/SHOHIN ） を読み取って Excel に出力
【解説】
RPGプログラム: EXCL001 は IFS 上に保管されている EXCELテンプレート( /AS400-NET.USR/FILE/BLANK.XLS) を
開いて、商品マスター( QTRFIL/SHOHIN )を 読み取って、その値を書き込み IFS 上の EXCELブック( /TEST/SHOHIN.XLS )
として出力します。
■ EXCL001: EXCELヘ出力 サンプル・ソース
0001.00 H DFTNAME(EXCL0OI) DATEDIT(*YMD/)
0002.00 F********** EXCEL へ RPG でデータを出力するサンプル *******************
0003.00 FSHOHIN IF E K DISK EXTFILE(SHOHIN_LIB)
0004.00 F**********************************************************************
0005.00 D HDR S 49A DIM(4) 小数
0006.00 D SHOHIN_LIB S 13 INZ('QTRFIL/SHOHIN')
0007.00 /COPY PANELWKR/QRPGLESRC,POI
0008.00 D EXCL_BOOK S 128A INZ('/AS400-NET.USR/FILE/-
0009.00 D BLANK.XLS')
0010.00 D SAVE_BOOK S 128A INZ('/TEST/SHOHIN.XLS')
0011.00 D RES S 9B 0 INZ
0012.00 D HED001 S 48A INZ(' 商品コード ')
0013.00 D HED002 S 48A INZ(' 商品名 ')
0014.00 D HED003 S 48A INZ(' 商品単価 ')
0015.00 D HED004 S 48A INZ(' 品種コード ')
0016.00 D NULL S 1A INZ(X'00')
0017.00 D SHEET_NUM S 9B 0 INZ(1)
0018.00 D ROW S 9B 0 INZ
0019.00 D COL S 9B 0 INZ
0020.00 D FGCOL S 9B 0 INZ(0)
0021.00 D BKCOL S 9B 0 INZ(0)
0022.00 D STR S 48A INZ
0023.00 D FLOAT S 8F INZ
0024.00 D ADDRCD S 5S 0
0025.00
0026.00 C*----------------------------------------------------+
0027.00 C *ENTRY PLIST |
0028.00 C PARM ADDRCD |
0029.00 C*----------------------------------------------------+
0030.00 C EXSR HEAD
0031.00 C DO *HIVAL
0032.00 C SETOFF 50
0033.00 C READ SHOHIN 50
0034.00 C 50 LEAVE
0035.00 C EXSR RECORD
0036.00 C END
0037.00 C EXSR WRITE
0038.00 C EXSR LRRTN
0039.00 C******************************************************
0040.00 C *INZSR BEGSR
0041.00 C******************************************************
0042.00 C* 初期サイクルのみの実行
0043.00 C Z-ADD 0 ADDRCD
0044.00 C* POI を初期化
0045.00 C EVAL RES = POI
0046.00 C IF RES = FALSE
0047.00 C MOVEL(P) POIMSG DSP40 40
0048.00 C DSP40 DSPLY ANS 1
0049.00 C SETON LR
0050.00 C RETURN
0051.00 C END
0052.00 C* 空の EXCEL ブックをオープンする
0053.00 C CAT X'00':0 EXCL_BOOK
0054.00 C EVAL RES = POI_WORKBOOK(%ADDR(EXCL_BOOK))
0055.00 C IF RES = FALSE
0056.00 C MOVEL(P) POIMSG DSP40 40
0057.00 C DSP40 DSPLY ANS 1
0058.00 C EXSR LRRTN
0059.00 C RETURN
0060.00 C END
0061.00 C*
0062.00 C HED001 CAT NULL:0 HDR(1)
0063.00 C HED002 CAT NULL:0 HDR(2)
0064.00 C HED003 CAT NULL:0 HDR(3)
0065.00 C HED004 CAT NULL:0 HDR(4)
0066.00 C ENDSR
0067.00 C******************************************************
0068.00 C HEAD BEGSR
0069.00 C******************************************************
0070.00 C EVAL ROW = 1
0071.00 C 1 DO 4 N 4 0
0072.00 C EVAL COL = N
0073.00 C CALLP SETVAL_STRCOL(SHEET_NUM:ROW:COL:
0074.00 C %ADDR(HDR(N)):FGCOL:BKCOL)
0075.00 C END
0076.00 C ENDSR
0077.00 C******************************************************
0078.00 C RECORD BEGSR
0079.00 C******************************************************
0080.00 C EVAL ROW = ROW + 1
0081.00 C*( SHCODE: 商品コード )
0082.00 C EVAL COL = 1
0083.00 C SHCODE CAT(P) X'00':0 STR
0084.00 C CALLP SETVAL_STR(SHEET_NUM:ROW:COL:%ADDR(STR))
0085.00 C*( SHNAME: 商品名 )
0086.00 C EVAL COL = 2
0087.00 C SHNAME CAT(P) X'00':0 STR
0088.00 C CALLP SETVAL_STR(SHEET_NUM:ROW:COL:%ADDR(STR))
0089.00 C*( SHTANK: 商品単価 )
0090.00 C EVAL COL = 3
0091.00 C EVAL FLOAT = SHTANK
0092.00 C CALLP SETVAL_RK(SHEET_NUM:ROW:COL:FLOAT)
0093.00 C*( SHSCOD: 品種コード )
0094.00 C EVAL COL = 4
0095.00 C SHSCOD CAT(P) X'00':0 STR
0096.00 C CALLP SETVAL_STR(SHEET_NUM:ROW:COL:%ADDR(STR))
0097.00 C EVAL ADDRCD = ADDRCD + 1
0098.00 C ENDSR
0099.00 C******************************************************
0100.00 C WRITE BEGSR
0101.00 C******************************************************
0102.00 C* 列幅を最適化する
0103.00 C 1 DO 4 N
0104.00 C EVAL COL = N
0105.00 C CALLP OPTIMIZE(SHEET_NUM:COL)
0106.00 C END
0107.00 C* EXCEL ブックとして保管する
0108.00 C CAT X'00':0 SAVE_BOOK
0109.00 C EVAL RES = WRITE_BOOK(%ADDR(SAVE_BOOK))
0110.00 C IF RES = FALSE
0111.00 C MOVEL POIMSG DSP40 40
0112.00 C DSP40 DSPLY ANS 1
0113.00 C SETON LR
0114.00 C RETURN
0115.00 C END
0116.00 C ENDSR
0117.00 C******************************************************
0118.00 C LRRTN BEGSR
0119.00 C******************************************************
0120.00 C CALLP POI_CLOSE
0121.00 C SETON LR
0122.00 C RETURN
0123.00 C ENDSR
【コンパイル】
CRTRPGMOD MODULE(QTEMP/EXCL001) SRCFILE(MYSRCLIB/QRPGLESRC) AUT(*ALL) CRTPGM PGM(MYLIB/EXCL001) MODULE(QTEMP/EXCL001) BNDSRVPGM((PANELWKR/POI)) ACTGRP(*NEW) AUT(*ALL)
【解説】
最初に
0044.00 C* POI を初期化 0045.00 C EVAL RES = POI
によって POI の実行環境を初期化します。
次に
0052.00 C* 空の EXCEL ブックをオープンする 0053.00 C CAT X'00':0 EXCL_BOOK 0054.00 C EVAL RES = POI_WORKBOOK(%ADDR(EXCL_BOOK))
によって EXCELブック( /AS400-Net.USR/FILE/BLANKS.XLS ) をオープンします。
0008.00 D EXCL_BOOK S 128A INZ('/AS400-NET.USR/FILE/-
0009.00 D BLANK.XLS')
EXCEL の最初の見出し行は
0067.00 C****************************************************** 0068.00 C HEAD BEGSR 0069.00 C****************************************************** 0070.00 C EVAL ROW = 1 0071.00 C 1 DO 4 N 4 0 0072.00 C EVAL COL = N 0073.00 C CALLP SETVAL_STRCOL(SHEET_NUM:ROW:COL: 0074.00 C %ADDR(HDR(N)):FGCOL:BKCOL) 0075.00 C END 0076.00 C ENDSR
によって出力されます。
SETVAL_STRCOL プロシージャーは
SETVAL_STRCOL ( シート番号: 行番号, 列番号, テキスト, 文字カラー, 背景色 ) として
セル毎に値や属性を設定することができます。
0031.00 C DO *HIVAL 0032.00 C SETOFF 50 0033.00 C READ SHOHIN 50 0034.00 C 50 LEAVE 0035.00 C EXSR RECORD 0036.00 C END
によって商品レコード( SHOHIN ) をすべて読み取りますが、各レコード毎に
0081.00 C*( SHCODE: 商品コード ) 0082.00 C EVAL COL = 1 0083.00 C SHCODE CAT(P) X'00':0 STR 0084.00 C CALLP SETVAL_STR(SHEET_NUM:ROW:COL:%ADDR(STR))
のようにして商品コード, 商品名, 商品単価, 品種コードの値を EXCELブックに書き込みます。
読み取りの最後には
0105.00 C CALLP OPTIMIZE(SHEET_NUM:COL)
によって列幅を最適化してから
0109.00 C EVAL RES = WRITE_BOOK(%ADDR(SAVE_BOOK))
によってEXCELブック( /TEST/SHOHIN.XLS )を出力する。
0010.00 D SAVE_BOOK S 128A INZ('/TEST/SHOHIN.XLS')
最後に
0120.00 C CALLP POI_CLOSE
によって POI環境をクローズして終了する。
このような POI のプロシージャーは
0007.00 /COPY PANELWKR/QRPGLESRC,POI
によってプロトタイプが定義されている。
POI 読み取り用プロシージャー一覧
| プロシージャー名 | 用途 |
|---|---|
POI | POI環境を初期設定する |
POI_CLOSE | POI環境をクローズする |
POI_WORKBOOK | POI を初期化して EXCEL ファイルを読み込む |
GET_MAXSHEET | シート数を取得する |
GET_SHEETNAME | シート名を取得する |
GET_MAXROW | 指定シートの最大行数を取得する |
GET_MAXCOL | 指定シートの指定行の最大カラム数を取得する |
GETVAL_RK | 指定カラムのデータを取得する |
GET_ATTR | 指定カラムのデータ属性を取得する |
GETVAL_DB | 指定カラムのデータを取得する |
GETVAL_STR | 指定カラムのデータを取得する |
GETVAL_NUM | 指定カラムのデータを取得する |
GETVAL_LG | 指定カラムのデータを取得する |
WRITE_BOOK | Workbook を指定ファイルに書き込む |
【まとめ】
POI によって Excel 出力のための操作をまとめると次のような流れになります。
Web表示するためのCLP
サンプル・プログラム EXCL001 を実行して結果をブラウザで表示させるためには
次のような CLP が必要となります。
■ CLPサンプル: EXCL001CL
0001.00 PGM
0002.00 /*-------------------------------------------------------------------*/
0003.00 /* EXCL001CL : EXCEL の出力サンプル CLP */
0004.00 /* */
0005.00 /* CRTCLMOD QTEMP/EXCL001CL SRCFILE(MYLIB/QCLLESRC) AUT(*ALL) */
0006.00 /* CRTPGM CGIBIN/EXCL001CL MODULE(QTEMP/EXCL001CL) */
0007.00 /* BNDSRVPGM(ASNET.COM/RPGENGINE5) ACTGRP(*NEW) AUT(*ALL)*/
0008.00 /* */
0009.00 /*-------------------------------------------------------------------*/
0010.00 DCL VAR(&MSG) TYPE(*CHAR) LEN(80)
0011.00 DCL VAR(&TYPE) TYPE(*CHAR) LEN(1)
0012.00 DCL VAR(&USER) TYPE(*CHAR) LEN(10) +
0013.00 VALUE('QTMHHTTP ')
0014.00 DCL VAR(&JOB) TYPE(*CHAR) LEN(10) +
0015.00 VALUE('AURORA_EGN')
0016.00 DCL VAR(&JOBNBR) TYPE(*CHAR) LEN(6)
0017.00 DCL VAR(&EXCL) TYPE(*CHAR) LEN(256) +
0018.00 VALUE('/TEST/SHOHIN.XLS')
0019.00 DCL VAR(&NULL) TYPE(*CHAR) LEN(1) VALUE(X'00')
0020.00 DCL VAR(&PDF_FILE) TYPE(*CHAR) LEN(48)
0021.00 DCL VAR(&RTN) TYPE(*CHAR) LEN(5)
0022.00 DCL VAR(&DATETIME) TYPE(*CHAR) LEN(20)
0023.00 DCL VAR(&DATE) TYPE(*CHAR) LEN(6)
0024.00 DCL VAR(&TIME) TYPE(*CHAR) LEN(6)
0025.00 DCL VAR(&HH) TYPE(*DEC) LEN(2 0)
0026.00 DCL VAR(&HHC) TYPE(*CHAR) LEN(2)
0027.00 DCL VAR(&MM) TYPE(*DEC) LEN(2 0)
0028.00 DCL VAR(&MMC) TYPE(*CHAR) LEN(2)
0029.00 DCL VAR(&SS) TYPE(*DEC) LEN(2 0)
0030.00 DCL VAR(&SSC) TYPE(*CHAR) LEN(2)
0031.00 DCL VAR(&DIR) TYPE(*CHAR) LEN(256)
0032.00 DCL VAR(&ADDRCD) TYPE(*CHAR) LEN(5)
0033.00 DCL VAR(&XLS) TYPE(*CHAR) LEN(11) +
0034.00 VALUE('XLS ')
0035.00 DCL VAR(&FILE) TYPE(*CHAR) LEN(128)
0036.00 MONMSG MSGID(CPF0000) EXEC(GOTO CMDLBL(ERROR))
0037.00
0038.00 /*-------------------------------------------------------------*/
0039.00 /*( 1 ) EXCL001 プログラムを呼びだして実行する */
0040.00 /*-------------------------------------------------------------*/
0041.00 CALL PGM(PANELWKR/EXCL001) PARM(&ADDRCD)
0042.00
0043.00 /*-------------------------------------------------------------*/
0044.00 /*( 2 ) 出力された EXCL をオープンする */
0045.00 /*-------------------------------------------------------------*/
0046.00 CALLPRC PRC(CONTENT_TYPE) PARM((&XLS *BYVAL) (&FILE +
0047.00 *BYVAL)) RTNVAL(&RTN)
0048.00 CALLPRC PRC(OPENHTML) PARM((&EXCL *BYVAL)) RTNVAL(&RTN)
0049.00
0050.00 /*-------------------------------------------------------------*/
0051.00 /*( 3 ) EXCL をブラウザに送出する */
0052.00 /*-------------------------------------------------------------*/
0053.00 CALLPRC PRC(WRITE)
0054.00
0055.00 /*-------------------------------------------------------------*/
0056.00 /*( 4 ) 10 分後に EXCL を削除するように SBMJOB */
0057.00 /*-------------------------------------------------------------*/
0058.00 CHGVAR VAR(&HH) VALUE(%SST(&TIME 1 2))
0059.00 CHGVAR VAR(&MM) VALUE(%SST(&TIME 3 2))
0060.00 CHGVAR VAR(&SS) VALUE(%SST(&TIME 5 2))
0061.00 CHGVAR VAR(&MM) VALUE(&MM + 10)
0062.00 IF COND(&MM >= 60) THEN(DO)
0063.00 CHGVAR VAR(&HH) VALUE(&HH + 1)
0064.00 CHGVAR VAR(&MM) VALUE(1)
0065.00 ENDDO
0066.00 CHGVAR VAR(&HHC) VALUE(&HH)
0067.00 CHGVAR VAR(&MMC) VALUE(&MM)
0068.00 CHGVAR VAR(&SSC) VALUE(&SS)
0069.00 CHGVAR VAR(&TIME) VALUE(&HHC *CAT &MMC *CAT &SSC)
0070.00 SBMJOB CMD(RMVLNK OBJLNK(&EXCL)) JOB(RMVLNK) +
0071.00 SCDDATE(&DATE) SCDTIME(&TIME) MSGQ(*NONE)
0072.00 RETURN
0073.00
0074.00 ERROR: RCVMSG MSGTYPE(*LAST) RMV(*NO) MSG(&MSG)
0075.00 IF COND(&TYPE *EQ '0') THEN(DO) /* バッチ */
0076.00 SNDPGMMSG MSG(&MSG) TOMSGQ(*SYSOPR) MSGTYPE(*COMP)
0077.00 ENDDO /* バッチ */
0078.00 ELSE CMD(DO) /* 対話式 */
0079.00 SNDPGMMSG MSG(&MSG) MSGTYPE(*DIAG)
0080.00 ENDDO /* 対話式 */
0081.00 ENDPGM
【解説】
最初に EXCL001 を
0038.00 /*-------------------------------------------------------------*/ 0039.00 /*( 1 ) EXCL001 プログラムを呼びだして実行する */ 0040.00 /*-------------------------------------------------------------*/ 0041.00 CALL PGM(PANELWKR/EXCL001) PARM(&ADDRCD)
によって実行してから
0043.00 /*-------------------------------------------------------------*/ 0044.00 /*( 2 ) 出力された EXCL をオープンする */ 0045.00 /*-------------------------------------------------------------*/ 0046.00 CALLPRC PRC(CONTENT_TYPE) PARM((&XLS *BYVAL) (&FILE + 0047.00 *BYVAL)) RTNVAL(&RTN) 0048.00 CALLPRC PRC(OPENHTML) PARM((&EXCL *BYVAL)) RTNVAL(&RTN)
で 表示するExcelブックをオープンすると同時に Content-Type に XLS を指定しています。
次に
0050.00 /*-------------------------------------------------------------*/ 0051.00 /*( 3 ) EXCL をブラウザに送出する */ 0052.00 /*-------------------------------------------------------------*/ 0053.00 CALLPRC PRC(WRITE)
で Excelブックをブラウザに送信してから
0070.00 SBMJOB CMD(RMVLNK OBJLNK(&EXCL)) JOB(RMVLNK) + 0071.00 SCDDATE(&DATE) SCDTIME(&TIME) MSGQ(*NONE)
によって現在の時刻より10分後に RMVLNK によって /TEST/SHOHIN.XLS を削除します。