*====================================================================
* RPG ILE HSSFCGI/GOCATALOG
*
* CRTBNDRPG HSSFCGI/GOCATALOG DFTACTGRP(*NO) ACTGRP(*NEW) DBGVIEW(*SOURCE)
*
* This program demonstrates a simple technique to avoid picture distorsion when one or more
* columns of an Excel spreadsheet contain images.
*
* The techniques is as follow:
* - Document in a database file
* the width W (pixel) and
* the height H (pixel)
* of each picture
* (see what has been done for database file HSSFCGI/CATALOG)
* - In the XLSGEN script, assign the same width AW (points) to all the pictures
* in a given column (this width doesn't have to match the widths of the real pictures)
* Example for AW=200 :
* |
*
*
* /hssfcgi/catalog/1.jpg
*
* |
* - In your program compute the height H (points) to be assigned to each picture as follow:
* H = ((AW * H) / W) * 0.75
* - Assign the height H to the row containing this picture.
* Example for H=100 :
*
*
*====================================================================
/copy HSSFCGI/qrpglesrc,hspecs
/copy HSSFCGI/qrpglesrc,hspecsbnd
FCATALOG if e k disk usropn
F extfile('HSSFCGI/CATALOG')
/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')
* Indicators for GetHtmlIfsMult subprocedure
D IfsMultIndicators...
D ds
D NoErrors n
D NameTooLong n
D NotAccessible n
D NoFilesUsable n
D DupSections n
D FileIsEmpty n
* ------- Parameters controlling picture distorsion
* 1- Assigned picture width (points)
D pictureWidth c 200
* 2- Picture height correction factor
D HfixFactor c 0.75
* ------- Computed picture height (points)
D pictureHeight s 10i 0
D rowNumber s 10i 0
D rowHeight s 5i 0
D xmlStmf s 100 inz('/hssfcgi/tmp/catalog.txt')
D xlsStmf s 100 inz('/hssfcgi/tmp/catalog.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
*====================================================================
* Main line
*====================================================================
/free
// Set no Debug for max performance
setNoDebug(*on);
// Load external XML-like script, suited for XLSGEN command
IfsMultIndicators = getHtmlIfsMult(%trim(exthtml):'');
// The row number must be the same as shown on the spreadsheet
rowNumber=0;
// 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 item
exsr List;
// End current 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) XLSGENRPT(*NO)');
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/GOCATALOG executed successfully.';
sndpgmmsg('*PRV':'GOCATALOG':'CPF9897':'QCPFMSG':'QSYS':
'*INFO':msgdta);
msgdta='HSSFCGI installation was validated.';
sndpgmmsg('*PRV':'GOCATALOG':'CPF9897':'QCPFMSG':'QSYS':
'*INFO':msgdta);
endif;
// Back to caller
*inlr=*on;
return;
/end-free
*====================================================================
* Start a worksheet
*====================================================================
/free
Begsr StrWorkSheet;
updhtmlvar('sheetname':'catalog');
updhtmlvar('worksheetkeywords':
'print-header="Catalog" +
print-orientation="landscape" +
print-scale="80"');
wrtsection('startsheet');
endsr;
/end-free
*====================================================================
* Write title row
*====================================================================
/free
Begsr WriteTitle;
updhtmlvar('rowkeywords':'row-height="60"');
wrtsection('startrow');
rowNumber+=1; //Number or rows so far
updhtmlvar('cellkeywords':'hspan="4"');
wrtsection('startcell');
updhtmlvar('datatype':'LargeTextC');
updhtmlvar('datakeywords':'valign="center" +
b-color="Lavender"');
wrtsection('startdata');
updhtmlvar('data':'PRODUCT CATALOG');
wrtsection('data');
wrtsection('enddata');
wrtsection('endcell');
wrtsection('endrow');
updhtmlvar('rowkeywords':' ');
endsr;
/end-free
*====================================================================
* Write column headings row
*====================================================================
/free
Begsr WriteColHeads;
updhtmlvar('cellkeywords':' ');
wrtsection('startrow');
rowNumber+=1; //Number or rows so far
colHeading(' ');
colHeading('Item No.');
colHeading('Description');
colHeading('Price (USD)');
wrtsection('endrow');
endsr;
/end-free
*====================================================================
* Write a row for each item
*====================================================================
/free
Begsr List;
if not %open(CATALOG);
open CATALOG;
else;
close CATALOG;
open CATALOG;
endif;
read ctgrcd;
dow not %eof;
exsr WrtListRow; //write a list row
read ctgrcd;
enddo;
close CATALOG;
endsr;
/end-free
*====================================================================
* Write a list row
*====================================================================
/free
Begsr WrtListRow;
// Compute the picture height
// ((cell-width * real_picture_height) / real_picture_width) * correction_factor
pictureHeight=((pictureWidth * ctgimgH ) / ctgimgW) * HfixFactor;
// Assign to the row the same height as the picture computed height
rowHeight=pictureHeight;
// 1- Start the row
updhtmlvar('rowkeywords':
'row-height="'+%trim(%char(rowHeight))+'"');
wrtsection('startrow'); //
rowNumber+=1; //Number or rows so far
// 2- Cell containing the picture
updhtmlvar('cellkeywords':' ');
wrtsection('startcell'); //|
updhtmlvar('datatype':'picture');
updhtmlvar('datakeywords':
'width="' + %trim(%char(pictureWidth)) + '"');
wrtsection('startdata'); //
updhtmlvar('data':%trim(ctgimg)); //image path & file
wrtsection('data'); //data value
wrtsection('enddata'); //
wrtsection('endcell'); // |
// 3- Cell containing the item identifier
updhtmlvar('cellkeywords':' ');
wrtsection('startcell'); //
updhtmlvar('datatype':'largetext');
updhtmlvar('datakeywords':'valign="center"');
wrtsection('startdata'); //
updhtmlvar('data':ctgid);
wrtsection('data'); //data value
wrtsection('enddata'); //
wrtsection('endcell'); // |
// 3- Cell containing the item description
updhtmlvar('cellkeywords':' ');
wrtsection('startcell'); //
updhtmlvar('datatype':'text');
updhtmlvar('datakeywords':'valign="center" wrap');
wrtsection('startdata'); //
updhtmlvar('data':ctgdescr);
wrtsection('data'); //data value
wrtsection('enddata'); //
wrtsection('endcell'); // |
// 5- Cell containing the item price
updhtmlvar('cellkeywords':' ');
wrtsection('startcell'); //
updhtmlvar('datatype':'largenumber');
updhtmlvar('datakeywords':'valign="center"');
wrtsection('startdata'); //
updhtmlvar('data':%editc(ctgprice:'J'));
wrtsection('data'); //data value
wrtsection('enddata'); //
wrtsection('endcell'); // |
// 6- End the row
wrtsection('endrow'); //
endsr;
/end-free
*====================================================================
* Write a column heading
*====================================================================
P ColHeading b
D ColHeading pi
D heading 50 const varying
/free
if heading='Description';
updhtmlvar('cellkeywords':'cell-width="300"');
else;
updhtmlvar('cellkeywords':' ');
endif;
wrtsection('startcell');
updhtmlvar('datatype':'Column-Header');
updhtmlvar('datakeywords':' ');
wrtsection('startdata');
updhtmlvar('data':heading);
wrtsection('data');
wrtsection('enddata');
wrtsection('endcell');
/end-free
P ColHeading e