please find the below full code
Code: Select all
H DFTACTGRP(*NO)
H OPTION(*SRCSTMT: *NODEBUGIO: *NOSHOWCPY)
H THREAD(*SERIALIZE)
H BNDDIR('HSSF')
*--------------------------------------------------------------------
D/copy qsysinc/qrpglesrc,jni
D/copy *libl/qrpglesrc,HSSF_H
*
*
D RVATXLSK PR ExtPgm('RVATXLSK')
D from_datein 6s 0
D to_datein 6s 0
D email_in 2000
*
D EMLCVTADDR PR ExtPgm('EMLCVTADDR')
D 200A const
D 2000A const
*
D wCmd s 1024 varying
D QCMDEXC PR ExtPgm('QCMDEXC')
D 1024 const
D 15p 5 const
*
*
D CreateCellStyles...
D PR
*
D wb s like(HSSFWorkbook)
d sheet s like(HSSFSheet)
d row s like(HSSFRow)
d cell s like(HSSFCell)
D Numeric s like(HSSFCellStyle)
D Curramt s like(HSSFCellStyle)
D Curreur s like(HSSFCellStyle)
D Text s like(HSSFCellStyle)
D Dates s like(HSSFCellStyle)
D ColHeading s like(HSSFCellStyle)
*
D ROWHN S 10I 0
D rowNum S 10I 0
D date S d
D rowNum2 S 10I 0
D rowNum3 S 10I 0
D rowNum4 S 10I 0
D rowNum5 S 10I 0
D rowNum6 S 10I 0
D cellNum S 5I 0
D xlsFile S 1024A INZ('ReportKildare.xls
Difsroot S 5A INZ('/tmp/')
D workfile S 10A INZ('NONE')
*
D sname1 S 15A INZ('Kildare_VAT')
D sname2 S 20A INZ('Run Parameters')
*
D ifsFile S 1024A varying
D outSTMF S 1024A
D valueColumns S 10I 0 DIM(2)
D subTotalColumns...
D S 10I 0 DIM(2)
D outlineLevel S 5I 0 INZ(3)
D lastRowNum S 10I 0
D dataExists S N
D Subject s 60a
D Message s 100a
D EmailIn s 200a inz(*blanks)
D EmailOut s 2000a inz(*blanks)
D EMAILAD s 30A inz(*blanks)
d dirname s 1024a varying
d eucountry s n
d personal s n
d nl s n
d vat s n
D SDS
D pgmpgm 1 10
D pgmLibrary 81 90
D UserEmail 254 263
D jobNumber 264 269
D PGMNME *PROC
*--------------------------------------------------------------------
D TemplateDirect C CONST('/excel/templates/')
*-----------------------------------------------------------------
D invdata_ds ds inz
D orinv 7p 0
D sthts# 10s 0
D country 2
D dkcntr 2
D amount 11p 2
D amount_USD 11p 2
D units 7p 0
d ordst 1
d kilos 5i 0
D NullInds s 5i 0 dim(11)
D NullValue s like(NullInds) inz(-1)
*------------------------------------------------------------
/free
hssf_begin_object_group(100);
Exsr Createxls;
Exsr CreateSheet1;
Exsr CloseXls;
Exsr SendXls;
hssf_end_object_group();
*inlr=*on;
//-----------------------------------------------------------
// Create recv listing
//-----------------------------------------------------------
Begsr CreateSheet1;
sheet = HSSF_newSheet(wb:sname1);
EXSR $WRTHDR;
//you can use a simple file insted of this join query**
EXEC SQL declare R1 cursor for
select
a.orinv, a.sthts#, a.country, a.dkcntr,
sum(a.orprii*a.orotq) as amount,
Case When sum(a.orprii*a.orotq)=0
Then sum(a.orpri*a.orotq) Else 0 End as amount_USD,
sum(a.orotq) as units,
a.ordst, sum(round((a.orotq/b.totalunits*b.inwgh*.45),0)) as kilos
from dpbi017 a
inner join dpbi018 b
on a.orinv = b.orinv and a.ordso = b.ordso
where (a.ordso in ('KIL','RIE')) Or
(a.ordso in ('EAM') And a.ordsh='5501')
group by a.orinv, a.sthts#, a.country, a.dkcntr, a.ordst;
EXEC SQL open R1;
EXEC SQL fetch next from R1 into :invdata_ds :NullInds;
dow %subst(sqlstt:1:2)='00' or %subst(sqlstt:1:2)='01';
if ordst = 'R';
amount = amount * -1;
amount_USD = amount_USD * -1;
endif;
exsr processdetail;
EXEC SQL fetch next from R1 into :invdata_ds :NullInds;
enddo;
EXEC SQL close R1;
endsr;
//=====================================================
begsr processdetail;
exsr WriteXls;
Endsr;
//----------------------------------------------------
Begsr Createxls;
wb = new_HSSFWorkbook();
CreateCellStyles();
ROWHN = 0;
rownum = 0;
Endsr;
//-----------------------------------------------------
Begsr Writexls;
RowNum=RowNum+1;
row=HSSFSheet_createRow(sheet:Rownum);
hssf_num (row:0:orinv:Numeric);
hssf_text (row:1:%subst(%char(sthts#):1:8):Text);
hssf_text (row:2:'NL':Text);
hssf_text (row:3:dkcntr:Text);
hssf_text (row:4:'1':Text);
hssf_text (row:5:'1':Text);
hssf_num (row:6:amount:curreur);
hssf_num (row:7:amount_USD:curramt);
hssf_text (row:8:'DDP':Text);
// weight
if kilos < 1;
kilos = 1;
endif;
hssf_num (row:9:kilos:Numeric);
hssf_num (row:10:units:Numeric);
Endsr;
//-------------------------------------------------------
Begsr CloseXls;
sheet = HSSF_newSheet(wb:sname2);
row=HSSFSheet_createRow(sheet:ROWHN);
hssf_text (row: 0:'From date:':Text);
date = %date(from_datein:*ymd);
hssf_date (row:1:date:Dates);
ROWHN += 1;
row=HSSFSheet_createRow(sheet:ROWHN);
hssf_text (row: 0:'To date:':Text);
date = %date(to_datein:*ymd);
hssf_date (row:1:date:Dates);
HSSF_save(wb:'/tmp/ReportKildare.xls');
Endsr;
//-----------------------------------------------------------;
Begsr Sendxls;
IFSFILE = ifsroot + xlsfile;
Subject = 'Kildare VAT Report';
EmailIn=email_in;
EmailAD=emailin;
emlcvtaddr(EmailIn: EmailOut);
If RowNum >0 or RowNum2 >0;
Message = 'Kildare VAT Report';
wcmd='SNDSMTPEMM RCP((''' + %trim(EMAILAD) +
''')) SUBJECT(''' + %trimr(Subject) +
''') NOTE(''' + %trimr(Message) +
''') ATTACH((''' + %trimr(IFSfile) +
''' *PLAIN *TXT)) ' ;
QCMDEXC(wCmd: %len(wCmd));
Else;
Message = 'Kildare Vat Report +
Has No Records';
wcmd='SNDSMTPEMM RCP((''' + %trim(EMAILAD) +
''')) SUBJECT(''' + %trimr(Subject) +
''') NOTE(''' + %trimr(Message) +
''') ' ;
QCMDEXC(wCmd: %len(wCmd));
Endif;
Endsr;
//-----------------------------------------------------------;
Begsr $WRTHDR;
HSSFSheet_setColumnWidth( sheet: 0: 10 * 256 );
HSSFSheet_setColumnWidth( sheet: 1: 11 * 256 );
HSSFSheet_setColumnWidth( sheet: 2: 24 * 256 );
HSSFSheet_setColumnWidth( sheet: 3: 19 * 256 );
HSSFSheet_setColumnWidth( sheet: 4: 24 * 256 );
HSSFSheet_setColumnWidth( sheet: 5: 17 * 256 );
HSSFSheet_setColumnWidth( sheet: 6: 17 * 256 );
HSSFSheet_setColumnWidth( sheet: 7: 17 * 256 );
HSSFSheet_setColumnWidth( sheet: 8: 12 * 256 );
HSSFSheet_setColumnWidth( sheet: 9: 8 * 256 );
HSSFSheet_setColumnWidth( sheet:10: 7 * 256 );
row = HSSFSheet_createRow(sheet: ROWHN);
hssf_text(row: 0:'Invoice#':ColHeading);
hssf_text(row: 1:'Commodity':ColHeading);
hssf_text(row: 2:'Country of Destination':ColHeading);
hssf_text(row: 3:'Country of Origin':ColHeading);
hssf_text(row: 4:'Mode of Transportayion':ColHeading);
hssf_text(row: 5:'Nature of Trans':ColHeading);
hssf_text(row: 6:'Invoice value Ð':ColHeading);
hssf_text(row: 7:'Invoice value $':ColHeading);
hssf_text(row: 8:' Dlvy Terms':ColHeading);
hssf_text(row: 9:'Weight':ColHeading);
hssf_text(row:10:'Units':ColHeading);
Endsr;
/end-free
//-----------------------------------------------------------;
P CreateCellStyles...
P B
D CreateCellStyles...
D PI
D new_StringC PR like(jString)
D EXTPROC(*JAVA
D :'java.lang.String'
D :*CONSTRUCTOR)
D create_from 16383c VARYING const
D DOLLAR C U'0024'
D EURO C U'20AC'
D ChFont s like(HSSFFont)
D DataFmt s like(HSSFDataFormat)
D TempStr s like(jString)
D NumFmt s 5I 0
D DateFmt s 5I 0
/free
ColHeading = HSSFWorkbook_createCellStyle(wb);
ChFont = HSSFWorkbook_createFont(wb);
HSSFFont_setBoldweight(ChFont: BOLDWEIGHT_BOLD);
HSSFCellStyle_setFont(ColHeading: ChFont);
HSSFCellStyle_setAlignment(ColHeading: ALIGN_CENTER);
HSSFCellStyle_setBorderBottom(ColHeading: BORDER_THIN);
Text = HSSFWorkbook_createCellStyle(wb);
HSSFCellStyle_setAlignment(Text: ALIGN_LEFT);
Numeric = HSSFWorkbook_createCellStyle(wb);
Curramt = HSSFWorkbook_createCellStyle(wb);
DataFmt = HSSFWorkbook_createDataFormat(wb);
TempStr = new_String('$#,##0.00');
NumFmt = HSSFDataFormat_getFormat(DataFmt: TempStr);
HSSFCellStyle_setDataFormat(Curramt: NumFmt);
Curreur = HSSFWorkbook_createCellStyle(wb);
DataFmt = HSSFWorkbook_createDataFormat(wb);
TempStr = new_String(EURO + %ucs2('##,##0.00'));
NumFmt = HSSFDataFormat_getFormat(DataFmt: TempStr);
HSSFCellStyle_setDataFormat(Curreur: NumFmt);
Dates = HSSFWorkbook_createCellStyle(wb);
DataFmt = HSSFWorkbook_createDataFormat(wb);
TempStr = new_String('m/d/yy');
DateFmt = HSSFDataFormat_getFormat(DataFmt: TempStr);
HSSFCellStyle_setDataFormat(Dates: DateFmt);
/end-free
P E