ExcelReadServiceの例

LANSA Integrator

ExcelReadServiceの例


このプログラム例では以下のステップを実行します。

1.   ExcelReadServiceのロードに必要な一連の呼び出しを行います。

2.    注文行データが入っているExcelワークブック・ファイルを開き、そのファイルをJSMインスタンス・フォルダーの XLREAD.XLSファイルに書き込み、ファイルを閉じます。この簡単な例では、データはRPGプログラムでコード化されたコンパイル時の配列データからのものですが、同様にデータベースからでも、別のLANSA Integratorサービス呼び出しからさまざまな形式で受け取られたものでも構いません。

3.   サービスをアンロードしてJSMサーバーとの接続を閉じます。

詳細については、例のコメントとコードを参照してください。

このアプリケーションを機能させるには、以下の3つのステップが必要です。

1.     構造XMLを作成する

ExcelReadServiceのWRITEコマンドを呼び出すと、ワークブックに書き込まれる注文行の項目を含む複数のオカレンス・データ構造が渡されます。これが機能するには、LANSA Integratorサービスで構造の特性を把握する必要があります。そのためには、構造を記述するXMLファイルを提供します。

この例の場合、2つの構造が使用されます。一方の構造は、列見出しの1つのセルを一度に書き込む場合に使用され、もう一方の構造は、注文行データを書き込む場合に使用されます。必要なXMLは以下に示されています。このXMLをインストールするには、以下のステップを実行する必要があります。

a.   JSMサーバーのJSMインスタンス・フォルダーでstructureフォルダーを探します。

b.   XLCell.xmlXLOrderLine.xmlというファイルを作成します。

c.   XLCell.xmlファイルをテキスト・エディタで編集し、以下のxmlを貼り付けます。

<?xml version="1.0" encoding="UTF-8"?>

<rdml:structure xmlns:rdml="http://www.lansa.com/2000/XML/Function">

   <rdml:field name="XLCELL" type="A" length="256"  />

</rdml:structure>

d.   XLOrderLine.xmlファイルをテキスト・エディタで編集し、以下のxmlを貼り付けます。

<?xml version="1.0" encoding="UTF-8"?>

<rdml:structure xmlns:rdml="http://www.lansa.com/2000/XML/Function">

   <rdml:field name="LINENUM" type="S" length="7"  />

   <rdml:field name="PARTNUM" type="A" length="7"  />

   <rdml:field name="PARTDSC" type="A" length="30" />

   <rdml:field name="PARTAMT" type="S" length="9" decimal="2" />

   <rdml:field name="PARTQTY" type="S" length="7"  />

</rdml:structure>                                                  

構造XMLで使用されるフィールド名が、RPGプログラムで使用される変数名に一致する必要はありません。重要なのは、名前ではなく注文、タイプ、長さになります。

2.     構造XMLをJSMサーバーに登録する

プログラム例が、シンボル名XL.XLCellXL.XLOrderLineで上記の構造XMLを参照するには、WRITEコマンドのSERVICE_STRUCTUREキーワードにこれらのシンボル名を指定します。

これらのシンボル名と、ステップ1で作成された構造XMLファイルの実際の名前および場所とのリンクをJSMサーバーに提供する必要があります。このためには、以下のステップを実行してください。

a.   JSMサーバーのJSMインスタンス・フォルダーでsystemフォルダーを探します。

b.   structure.propertiesファイルをテキスト・エディタで編集し、以下のエントリーを貼り付けます(新しいエントリーが単体で個別の行に記述されるようにします)。

structure.XL.XLCell=structure/XLCell.xml

structure.XL.XLOrderLine=structure/XLOrderLine.xml 

c.   変更内容を保存します。

d.   JSMサーバー・インスタンスを再起動または更新します([Java Service Managerの更新」を参照)。

3.     ILE RPGプログラム例を作成して実行する

以下のソースをコピーしてソース・ファイル・メンバーに貼り付けます。

プログラムを作成するには、CRTRPGMODコマンドとCRTPGMコマンドを使用する必要があります。必ずソース・メンバーに指定したパラメータ値を使用します。

      *************************************************                 

      * EXCELREAD: example in RPG ILE of using the LANSA Integrator     

      *            ExcelReadService to write an Excel workbook.         

      *                                                                 

      * Note:    This is an example program containing only             

      *          rudimentary exception handling                         

      *                                                                 

      * To create this program you must execute the following commands, 

      * supplying the indicated parameter values and any others that are

      * necessary in your installation:                                 

      *                                                                 

      *   CRTRPGMOD MODULE(<modlib>/EXCELREAD)                          

      *             SRCFILE(<srclib>/<srcfil>)                          

      *                                                                 

      *   CRTPGM    PGM(<pgmlib>/EXCELREAD)                             

      *             MODULE(<modlib>/EXCELREAD)                          

      *             BNDSRVPGM(<jsmpgmlib>/DCXS882X)                     

      *             ACTGRP(*CALLER)                                     

      *************************************************                 

                                                                         

      * Path of the Excel workbook created by this program               

      * - because no folder path is specified, the file will be created  

      *   in the JSM instance folder by default                          

                                                                         

     d xlfilepath      c                   const('XLREAD.XLS')           

                                                                         

      * Declare variables for the JSM calls                              

                                                                         

     d jsmsrv          s             50a   inz(*blanks)                  

     d jsmsts          s             20a   inz(*blanks)                  

     d jsmmsg          s            255a   inz(*blanks)                  

     d jsmcmd          s            255a   inz(*blanks)                  

     d bytelength      s             10i 0 inz(*zero)                    

                                                                         

      * Declare structure to send order line data to be written to the   

      * Excel workbook:                                                  

      * - in this simple example, the data comes from the compile-time   

      *   array data, but it could equally well have come from a database

      *   or received through another LANSA Integrator service call      

      * NB: This MUST match the structure xml provided to the JSM Server! 

                                                                          

     d xllist          ds                  occurs(xlocur) based(xllistptr)

     d  linenum                       7s 0                                

     d  partnum                       7a                                  

     d  partdsc                      30a                                  

     d  partamt                       9s 2                                

     d  partqty                       7s 0                                

                                                                          

     d xlocur          c                   const(2)                       

     d xlsize          c                   const(%size(xllist))           

                                                                          

      * Declare the compile-time array that provides the data for         

      * this simple example                                               

                                                                          

     d xldata          s             60a   dim(xlocur) perrcd(1) ctdata   

                                                                          

      * Completion messages                                               

                                                                          

     d CompMsg01       c                   'JSMOPEN call completed.'      

     d CompMsg02       c                   '  SERVICE_LOAD call completed.'  

     d CompMsg10       c                   '  OPEN call completed.'          

     d CompMsg20       c                   '  WRITE call completed.'         

     d CompMsg30       c                   '  CLOSE call completed.'         

     d CompMsg98       c                   '  SERVICE_UNLOAD call completed.'

     d CompMsg99       c                   'JSMCLOSE call completed.'        

                                                                             

      * Procedure prototypes                                                 

                                                                             

     d WriteCell       pr                                                    

     d  wcR1C1                       10a   value                             

     d  wcCell                      256a   value                             

                                                                             

     d CheckResult     pr                                                    

     d  crjsts                             const like(jsmsts)                

     d  crjmsg                             const like(jsmmsg)                

                                                                             

     d SendMessage     pr                                                    

     d  smText                      512a   VALUE                             

     d  smType                       10a   VALUE                             

                                                                             

      * Prototypes for the JSM calls                                         

                                                                             

      /COPY QRPGLESRC,JSM_PROC.H                                             

                                                                             

      * Open a connection to the default JSM server                          

      * - because the server parameter is blank, details of the default      

      *   JSM server are obtained from the data area JSMCLTDTA on IBM i    

      *   or from the file jsmcltdta.txt on other supported platforms)       

                                                                             

     c                   callp     p_jsmopen(jsmsrv:jsmsts:jsmmsg)           

     c                   callp     CheckResult(jsmsts:jsmmsg)                

     c                   callp     SendMessage(CompMsg01:'*COMP')            

                                                                             

      * Load the ExcelReadService                                            

      * - this example explicitly turns tracing on, overriding the           

      *   settings in the manager.properties file                            

                                                                             

     c                   eval      jsmcmd = 'SERVICE_LOAD'                   

     c                             + ' SERVICE(EXCELREADSERVICE) TRACE(*YES)'

     c                   callp     p_jsmcmd(jsmcmd:jsmsts:jsmmsg)      

     c                   callp     CheckResult(jsmsts:jsmmsg)          

     c                   callp     SendMessage(CompMsg02:'*COMP')      

                                                                       

      * Open the Excel workbook for writing                            

                                                                       

     c                   eval      jsmcmd = 'OPEN'                     

     c                             + ' FILE(' + %trim(xlfilepath) + ')'

     c                             + ' MODE(*WRITE) REPLACE(*YES)'     

     c                   callp     p_jsmcmd(jsmcmd:jsmsts:jsmmsg)      

     c                   callp     CheckResult(jsmsts:jsmmsg)          

     c                   callp     SendMessage(CompMsg10:'*COMP')      

                                                                       

      * Write some column headings                                     

                                                                       

     c                   callp     WriteCell('3,3':'Line Number')      

     c                   callp     WriteCell('3,4':'Part Number')      

     c                   callp     WriteCell('3,5':'Part Description') 

     c                   callp     WriteCell('3,6':'Unit Amount')      

     c                   callp     WriteCell('3,7':'Unit Quantity')    

                                                                                

      * Populate the list to be written to the Excel workbook                   

      * - in this simple example, the data comes from the compile-time          

      *   array data, but it could equally well have come from a database       

      *   or received through another LANSA Integrator service call             

                                                                                

     c                   eval      xllistptr = %addr(xldata)                    

                                                                                

      * Write the list of items from the compile-time array to the specified    

      * position in the specified worksheet:                                    

      * - this passes the multiple occurrence data structure                    

      *   (xllist) containing the items                                         

      * - the structure is described to the ExcelReadService by the             

      *   structure XML identified by the SERVICE_STRUCTURE keyword - there     

      *   must be a matching entry in the structure.properties file and a       

      *   corresponding structure XML file                                      

                                                                                

      * NOTE: this call uses the JSMCMDX api in order to be able to send        

      *       variable data (in this case the structure/list)              

                                                                         

     c                   eval      jsmcmd = 'WRITE'                      

     c                             + ' SHEET(ORDERLINES) R1C1(5,3)'      

     c                             + ' SERVICE_STRUCTURE(XL.XLOrderLine)'

     c                             + ' COUNT('  + %char(xlocur) + ')'    

     c                             + ' OCCURS(' + %char(xlocur) + ')'    

     c                             + ' SIZE('   + %char(xlsize) + ')'    

                                                                         

     c                   eval      bytelength = xlocur * xlsize          

                                                                         

     c                   callp     p_jsmcmdx(jsmcmd:xllist:bytelength:   

     c                                       jsmsts:jsmmsg)              

     c                   callp     CheckResult(jsmsts:jsmmsg)            

     c                   callp     SendMessage(CompMsg20:'*COMP')        

                                                                         

      * Close the Excel workbook                                         

                                                                         

     c                   eval      jsmcmd = 'CLOSE'                      

     c                   callp     p_jsmcmd(jsmcmd:jsmsts:jsmmsg)        

     c                   callp     CheckResult(jsmsts:jsmmsg)            

     c                   callp     SendMessage(CompMsg30:'*COMP') 

                                                                  

      * Unload the ExcelReadService                               

                                                                  

     c                   eval      jsmcmd = 'SERVICE_UNLOAD'      

     c                   callp     p_jsmcmd(jsmcmd:jsmsts:jsmmsg) 

     c                   callp     CheckResult(jsmsts:jsmmsg)     

     c                   callp     SendMessage(CompMsg98:'*COMP') 

                                                                  

      * Close the connection to the JSM server and finish         

                                                                  

     c                   callp     p_jsmclose(jsmsts:jsmmsg)      

     c                   callp     CheckResult(jsmsts:jsmmsg)     

     c                   callp     SendMessage(CompMsg99:'*COMP') 

     c                   eval      *inlr = *on                    

     c                   return                                   

                                                                  

      *************************************************           

      * Procedure to write a single cell at a time to the workbook

      *************************************************           

     p WriteCell       b                                                  

     d WriteCell       pi                                                 

     d  wcR1C1                       10a   value                          

     d  wcCell                      256a   value                          

                                                                          

     c                   eval      jsmcmd = 'WRITE'                       

     c                             + ' R1C1(' + %trim(wcR1C1) + ')'       

     c                             + ' SHEET(ORDERLINES)'                 

     c                             + ' SERVICE_STRUCTURE(XL.XLCell)'      

     c                             + ' COUNT(1) OCCURS(1)'                

     c                             + ' SIZE(' + %char(%size(wcCell)) + ')'

                                                                          

     c                   eval      bytelength = %size(wcCell)             

                                                                          

     c                   callp     p_jsmcmdx(jsmcmd:wcCell:bytelength:    

     c                                       jsmsts:jsmmsg)               

     c                   callp     CheckResult(jsmsts:jsmmsg)             

     c                   callp     SendMessage(CompMsg20:'*COMP')         

                                                                          

     p WriteCell       e                                                  

                                                                         

      *************************************************                  

      * Procedure to check the result of a Java Service Manager call     

      *************************************************                  

     p CheckResult     b                                                 

     d CheckResult     pi                                                

     d  crjsts                             const like(jsmsts)            

     d  crjmsg                             const like(jsmmsg)            

     d  crText         s            512a                                 

     d  crMsg1         c                   const('JSM Status : ')        

     d  crMsg2         c                   const('JSM Message: ')        

     d  crMsg3         c                   const('JSM Service error has +

     d                                            occurred')             

     c                   if        crjsts <> 'OK'                        

     c                   eval      crText = crMsg1 + crjsts              

     c                   callp     SendMessage(crText:'*DIAG')           

     c                   eval      crText = crMsg2 + crjmsg              

     c                   callp     SendMessage(crText:'*DIAG')           

     c                   callp     SendMessage(crMsg3:'*ESCAPE')         

     c                   endif                                           

     p CheckResult     e                                              

                                                                      

      *************************************************               

      * Procedure to send a program message                           

      *************************************************               

     p SendMessage     b                                              

                                                                      

     d SendMessage     pi                                             

     d smText                       512a   VALUE                      

     d smMsgT                        10a   VALUE                      

                                                                      

     d smMsgI          s              7a   inz('CPF9897')             

     d smMsgF          s             20a   inz('QCPFMSG   *LIBL     ')

     d smDtaL          s             10i 0 inz(%size(smText))         

     d smStkE          s             10a   inz('*')                   

     d smStkC          s             10i 0 inz(1)                     

     d smMsgK          s              4a                              

     d smErrC          s             10i 0 inz(0)                     

                                                                      

     c                   if        smMsgT = '*ESCAPE'                 

     c                   eval      smMsgI = 'CPF9898'       

     c                   endif                              

                                                            

     c                   call      'QMHSNDPM'               

     c                   parm                    smMsgI     

     c                   parm                    smMsgF     

     c                   parm                    smText     

     c                   parm                    smDtaL     

     c                   parm                    smMsgT     

     c                   parm                    smStkE     

     c                   parm                    smStkC     

     c                   parm                    smMsgK     

     c                   parm                    smErrC     

                                                            

     p                 e                                    

**CTDATA xldata                                             

0000001123    Gasket Paper                  0000009950000010

0000002456    Gasket polymer glue           0000013270000005