*==================================================================== * RPG ILE HSSFCGI/XVSUMTRY * * CRTBNDRPG HSSFCGI/XVSUMTRY DFTACTGRP(*NO) ACTGRP(*NEW) DBGVIEW(*SOURCE) * *==================================================================== /copy HSSFCGI/qrpglesrc,hspecs /copy HSSFCGI/qrpglesrc,hspecsbnd FXVSUMCASE if e k disk usropn F extfile('HSSFCGI/XVSUMCASE') /copy HSSFCGI/qrpglesrc,prototypeb /copy HSSFCGI/qrpglesrc,hssfcgix_p /copy HSSFCGI/qrpglesrc,usec D ColHeading pr D heading 50 const varying D rc s 10i 0 D extHtml s 2000 inz('/hssfcgi/html/- D XLSGEN_template.txt') D IfsMultIndicators... D ds D NoErrors n D NameTooLong n D NotAccessible n D NoFilesUsable n D DupSections n D FileIsEmpty n D recordNbr 10i 0 D fammonthC s 2 D famdayC s 2 D data s 1000 D xmlStmf s 100 inz('/hssfcgi/tmp/xvsumtry.txt') D xlsStmf s 100 inz('/hssfcgi/tmp/xvsumtry.xls') D stmf800 s 800 varying D CodePage s 10i 0 inz(819) D MonthNbrC s 2 D port s 10 D ContentType s 100 * variables for procedure SndPgmMsg D pgmq s 5 D msgid s 7 D msgf s 10 D msgflib s 10 D msgdta s 512 * D XVCITFMprv s like(XVCITFM) D i s 10i 0 *==================================================================== * Main line *==================================================================== /free // Set no Debug for max performance setNoDebug(*on); // Open file, if needed exsr OpnF; // Load external XML-like script, suited for XLSGEN command IfsMultIndicators = getHtmlIfsMult(%trim(exthtml):''); // Start the workbook wrtsection('startbook'); // Start a worksheet exsr StrWorksheet; // Write title row exsr WriteTitle; // Write column headings row exsr WriteColHeads; // Write a row for each database record exsr WrtRecRows; // Write bottom figures (grand totals) exsr WrtGrdTot; // End worksheet wrtsection('endsheet'); // End the workbook wrtsection('endbook'); // Write the output buffer to an XLM stream file rc = WrtHtmlToStmf(%trim(xmlStmf):CodePage); rc = docmd('chgaut ''' + %trim(xmlStmf) + ''' USER(*PUBLIC) DTAAUT(*RWX) OBJAUT(*ALL)'); callp ClrHtmlBuffer(); // Use command HSSFCGI/XLSGEN to generate the XLS stream file rc = docmd('hssfcgi/xlsgen inpstmf(''' + %trim(xmlStmf) + ''') outstmf(''' + %trim(xlsStmf) + ''') + frozen(2) + hdgcolor(SEA_GREEN)'); rc = docmd('chgaut ''' + %trim(xlsStmf) + ''' USER(*PUBLIC) DTAAUT(*RWX) OBJAUT(*ALL)'); // If a CGI job, read and send the XLS stream file to the browser port=rtvenvvar('SERVER_PORT':qusec); if port<>' '; stmf800=%trim(xlsStmf); SndStmfToBrowser(stmf800:'XLS'); //subprocedure of srvpgm HSSFCGI/HSSFCGIX else; msgdta='Program HSSFCGI/FAMACC executed successfully.'; sndpgmmsg('*PRV':'FAMACC':'CPF9897':'QCPFMSG':'QSYS': '*INFO':msgdta); msgdta='HSSFCGI installation was validated.'; sndpgmmsg('*PRV':'FAMACC':'CPF9897':'QCPFMSG':'QSYS': '*INFO':msgdta); endif; // Back to caller *inlr=*on; return; /end-free *==================================================================== * Start a worksheet *==================================================================== /free begsr StrWorkSheet; updhtmlvar('sheetname':'XVSUM_example'); updhtmlvar('worksheetkeywords': 'print-header="XVSUM example" + print-orientation="landscape" + print-scale="80"'); wrtsection('startsheet'); endsr; /end-free *==================================================================== * Write title row *==================================================================== /free begsr WriteTitle; updhtmlvar('rowkeywords':' '); wrtsection('startrow'); updhtmlvar('cellkeywords':'hspan="7"'); wrtsection('startcell'); updhtmlvar('datatype':'LargeTextC'); updhtmlvar('datakeywords':' '); wrtsection('startdata'); updhtmlvar('data':'XVSUM Example'); wrtsection('data enddata endcell'); wrtsection('endrow'); endsr; /end-free *==================================================================== * Write column headings row *==================================================================== /free begsr WriteColHeads; updhtmlvar('cellkeywords':' '); wrtsection('startrow'); colHeading('Family'); colHeading('Item'); colHeading('1Q'); colHeading('2Q'); colHeading('3Q'); colHeading('4Q'); colHeading('Year total'); wrtsection('endrow'); endsr; /end-free *==================================================================== * Write a row for each database record *==================================================================== /free Begsr WrtRecRows; recordNbr=0; read xvcrcd; dow not %eof; recordNbr+=1; //Number of records read so far if recordNbr>1 and XVCITFMprv<>XVCITFM; //if item-family change exsr WrtVSUMrow; //write a row of VSUM cells endif; exsr WrtDataRow; //write a data row XVCITFMprv=XVCITFM; //save previous item-family read xvcrcd; enddo; exsr WrtVSUMrow; //write a row of VSUM cells for the last item-family endsr; /end-free *==================================================================== * Write a data row *==================================================================== /free Begsr WrtDataRow; //Start a new row wrtsection('startrow'); //write item family updhtmlvar('cellkeywords':'cell-width="50"'); wrtsection('startcell'); updhtmlvar('datatype':'text'); updhtmlvar('datakeywords':' '); wrtsection('startdata'); data=XVCITFM; //item family updhtmlvar('data':data); wrtsection('data enddata endcell'); //write item code updhtmlvar('cellkeywords':'cell-width="65"'); wrtsection('startcell'); updhtmlvar('datatype':'text'); updhtmlvar('datakeywords':' '); wrtsection('startdata'); data=XVCITCD; //item code updhtmlvar('data':data); wrtsection('data enddata endcell'); //write sales 1st quarter updhtmlvar('cellkeywords':'cell-width="50"'); wrtsection('startcell'); updhtmlvar('datatype':'number'); updhtmlvar('datakeywords':' '); wrtsection('startdata'); data=%editc(XVCITS1Q:'J'); //sales 1Q updhtmlvar('data':data); wrtsection('data enddata endcell'); //write sales 2nd quarter updhtmlvar('cellkeywords':'cell-width="50"'); wrtsection('startcell'); updhtmlvar('datatype':'number'); updhtmlvar('datakeywords':' '); wrtsection('startdata'); data=%editc(XVCITS2Q:'J'); //sales 2Q updhtmlvar('data':data); wrtsection('data enddata endcell'); //write sales 3rd quarter updhtmlvar('cellkeywords':'cell-width="50"'); wrtsection('startcell'); updhtmlvar('datatype':'number'); updhtmlvar('datakeywords':' '); wrtsection('startdata'); data=%editc(XVCITS3Q:'J'); //sales 3Q updhtmlvar('data':data); wrtsection('data enddata endcell'); //write sales 4th quarter updhtmlvar('cellkeywords':'cell-width="50"'); wrtsection('startcell'); updhtmlvar('datatype':'number'); updhtmlvar('datakeywords':' '); wrtsection('startdata'); data=%editc(XVCITS4Q:'J'); //sales 4Q updhtmlvar('data':data); wrtsection('data enddata endcell'); //Year total sales for this item updhtmlvar('cellkeywords':'cell-width="60"'); wrtsection('startcell'); updhtmlvar('datatype':'Formula'); updhtmlvar('datakeywords':'dec-pos="0"'); wrtsection('startdata'); data='HSUM store'; updhtmlvar('data':data); wrtsection('data enddata endcell'); //End this row wrtsection('endrow'); endsr; /end-free *==================================================================== * Write a row of VSUM cells for a given item-family *==================================================================== /free Begsr WrtVSUMrow; //Start a new row wrtsection('startrow'); //write "item-family subtotal" updhtmlvar('cellkeywords':'hspan="2" vspan="2"'); wrtsection('startcell'); updhtmlvar('datatype':'text'); updhtmlvar('datakeywords':'bold'); wrtsection('startdata'); data=%trim(XVCITFMprv) + ' subtotals' + '%LF (from VSUM)'; updhtmlvar('data':data); wrtsection('data enddata endcell'); //write 5 VSUM cells for i=1 to 5; updhtmlvar('cellkeywords':' '); //This cell will take only 1 slot wrtsection('startcell'); updhtmlvar('datatype':'formula'); updhtmlvar('datakeywords':'bold dec-pos="0"'); wrtsection('startdata'); data='VSUM'; updhtmlvar('data':data); wrtsection('data enddata endcell'); endfor; //End this row wrtsection('endrow'); endsr; /end-free *==================================================================== * Write bottom figures (grand totals) *==================================================================== /free Begsr WrtGrdTot; //Start a new row wrtsection('startrow'); //Write a blank cell updhtmlvar('cellkeywords':' '); wrtsection('startcell'); updhtmlvar('datatype':'text'); updhtmlvar('datakeywords':' '); wrtsection('startdata'); data=' '; updhtmlvar('data':data); wrtsection('data enddata endcell'); //End this row wrtsection('endrow'); //Start a new row wrtsection('startrow'); //write "grand totals" updhtmlvar('cellkeywords':'hspan="2" vspan="2"'); wrtsection('startcell'); updhtmlvar('datatype':'text'); updhtmlvar('datakeywords':'bold'); wrtsection('startdata'); data='Grand totals %LF (from XVSUM)'; updhtmlvar('data':data); wrtsection('data enddata endcell'); //write 5 XVSUM cells for i=1 to 5; if i<>5; updhtmlvar('cellkeywords':' '); else; updhtmlvar('cellkeywords':'border'); endif; wrtsection('startcell'); updhtmlvar('datatype':'formula'); updhtmlvar('datakeywords':'bold dec-pos="0"'); wrtsection('startdata'); data='XVSUM'; updhtmlvar('data':data); wrtsection('data enddata endcell'); endfor; //End this row wrtsection('endrow'); endsr; /end-free *==================================================================== * Open file, if needed *==================================================================== /free begsr OpnF; if not %open(XVSUMCASE); open XVSUMCASE; endif; endsr; /end-free *==================================================================== * Write a column heading *==================================================================== P ColHeading b D ColHeading pi D heading 50 const varying /free wrtsection('startcell'); updhtmlvar('datatype':'Column-Header'); updhtmlvar('datakeywords':' '); wrtsection('startdata'); updhtmlvar('data':heading); wrtsection('data enddata endcell'); /end-free P ColHeading e