RE: Printing a report in MS excel. -- RPG400-L

There's an example of doing this in RPG IV at this URL.
http://www.rpgiv.com/cgi-rpg/viewsrc?FILE=QRPGLESRC&LIB=RPGLAB&MBR=CGI2EXCEL

It uses xTools to output to the browser and open Excel. If you're not using MS
Internet Explorer (I use FireFox) it may not open Excel.
-Bob Cozzi
www.RPGxTools.com
RPG xTools - Enjoy programming again.


-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On
Behalf Of Jake M
Sent: Monday, April 10, 2006 8:43 AM
To: RPG programming on the AS400 / iSeries
Subject: Re: Printing a report in MS excel.

Pro's,
Good morning. I appreciate all the responses. I am not using any kind of SQL
in this, so I do not know how useful some of the suggested packages would
be. I am very comfortable with HTML so I can go that route but I have never
done this before. All I am trying to do is to redirect the output of this
program into a .xls file instead of creating a report like it does right now
via O - Specs.I am attaching my code so that somebody can point me in the
right direction. I really appreciate all the help.

********************************************************************************
*****************************
      H DftActGrp(*NO)
     H BndDir('MYBNDDIR')

     Ftaxacum8  iF   E           K DISK
     Fchtinvz   iF   E           K DISK
     FQSYSPRT   O    F  132        PRINTER OFLIND(*INOF)
     C*


      /COPY QPROTOSRC,EDITOOLTST
     Dfutdat           s               d   datfmt(*YMD)
     Dtestdt           s               d   datfmt(*Ymd)
     Derror            s              3
     Dwk_htnum         s              8
     Ddate8            s              8
     Ddate8n           s              8  0
     Dsv_htnum         s                   like(ainvc)
     Dsv_ainvc         s                   like(ainvc)
     Din_sacol         s                   like(sacol)
     Din_sucol         s                   like(sucol)
     Din_socol         s                   like(socol)
     Din_tax           s                   like(socol)
     Dtaxable          s                   like(sactl)
     Dnontax           s                   like(sactl)
     Dtl_tax           s                   like(sucol)
     Dtl_taxable       s                   like(sactl)
     Dtl_nontax        s                   like(sactl)
     Din_invtot        s                   like(htgds)
     Din_invtax        s                   like(httax)
     Dtl_invtot        s                   like(htgds)
     Dtl_invtax        s              9  2
     Dbase             s                   like(sactl)
     Dshipstate        S                   like(htscty)


     D  cstzip         S             70
     D  city           S             35
     D  ste            S              2
     D  zip            S              9
     D  err                           1
     D  pass           S              1
     D   result        S             52






     C*
     C     *entry        plist
     C                   parm                    date8
     C*
     C                   move      date8         date8n
     C*
      /free
         // process by chtmst file (in date order)
          chain (date8n) chtinvz;
          dow %found(chtinvz) and not %eof(chtinvz);
                             in_Invtot = htgds + htfrt + htmff5;
                             in_invtax = httax;
                             tl_invtot += in_invtot;
                             tl_invtax += in_invtax;
                             cstzip = htscty;
                             result =
ctstjsttst(cstzip:city:ste:zip:pass:err);
              // access taxacum8 and start loading information
              sv_htnum = htnum;
              chain (sv_htnum) taxacum8;
              dow %found(taxacum8) and not %eof(taxacum8);
               // accumulate taxes
                in_sacol += sacol;
                in_sucol += sucol;
                in_socol += socol;
               // get taxable amount
                 if  sactl<> 0 and taxable = 0;
                   taxable = sactl;
                 ENDIF;
                 if  suctl<> 0 and taxable = 0;
                   taxable = suctl;
                 ENDIF;
                 if  soctl<> 0 and taxable = 0;
                   taxable = soctl;
                 ENDIF;
               // get non-taxable amount
                 if  santl<> 0 and nontax = 0;
                   nontax = santl;
                 ENDIF;
                 if  suntl<> 0 and nontax = 0;
                   nontax = suntl;
                 ENDIF;
                 if  sontl<> 0 and nontax = 0;
                   nontax = sontl;
                 ENDIF;
              reade (sv_htnum) taxacum8;
              enddo;

              // output print information

                        tl_tax += in_sacol + in_sucol + in_socol;
                        if taxable = nontax;
                        tl_taxable += taxable;
                        clear nontax;
                        else;
                        tl_taxable += taxable;
                        tl_nontax += nontax;
                        ENDIF;
                        in_tax = in_sacol + in_sucol + in_socol;
                        base = taxable + nontax;

                         if in_invtot <> base or
                            in_tax <> in_invtax;
                             Error = 'YES';
                             except prnt;
                         else;
                             error = 'NO ';
                         ENDIF;

                        clear in_sacol;
                        clear in_sucol;
                        clear in_socol;
                        clear taxable;
                        clear nontax;
                        clear in_invtot;
                        clear in_invtax;

          reade (date8n) chtinvz;
          ENDdo;

                        tl_tax += in_sacol + in_sucol + in_socol;
                        tl_taxable += taxable;
                        tl_nontax += nontax;
                        in_tax = in_sacol + in_sucol + in_socol;

                         if in_invtot <> base or
                            in_tax <> in_invtax;
                             Error = 'YES';
                             except prnt;
                         else;
                             error = 'NO ';
                         ENDIF;
                        except lrprnt;
           *inlr = *on;

      /end-free
     OQSYSPRT   H    1P                     3 01
     O         OR    OF
     O                       PAGE          Z     75
     O                                           70 'PAGE'
     O          H    1P                     2
     O         OR    OF

     O                                           10 'invoice '
     O                                           20 'state'
     O                                           30 'tax'
     O                                           50 'taxable'
     O                                           70 'non-taxable'
     O                                           95 'Mst-gds amt'
     O                                          115 'mst-tax'
     O          E            PRNT           1
     O                       htsltp               4
     O                       cs21sb               5
     O                       ste                 10
     O                       htnum               21
     O                       in_tax        J     35
     O                       taxable       J     55
     O                       nontax        J     75
     O                       in_invtot     J     95
     O                       in_invtax     J    115
     O                       error              125
     O          E            lrPRNT         1
     O                       tl_tax        J     35
     O                       tl_taxable    J     55
     O                       tl_nontax     J     75
     O                       tl_invtot     J     95
     O                       tl_invtax     J    115
     O*
********************************************************************************
*****************************

Cheers,

Jake.

On 4/8/06, Tony Carolla <carolla@xxxxxxxxx> wrote:
>
> Another option is for HTML coders primarily, and does not create Excel
> files
> with the same richness as the java methods.  If you know HTML, it might be
> easier though.  You could write the data to an HTML file in the IFS, using
> HTML tables.  This is done pretty easily with data structures with the
> table
> tags and such preinitialized in the data structures.
>
> When you create the file in the IFS, give it an extension of '.XLS'.  Even
> though it is an HTML file, Excel will open it and display it as though it
> were an Excel file.  You can even embed formulas in the table cells, and
> they will build real Excel formulas.
>
> On 4/7/06, Jake M <jakeroc@xxxxxxxxx> wrote:
> >
> > All;
> >
> > I have a RPGLE program which produces a report. Is there any way I can
> > produce the report in MS Excel  rather than a  report using O - Specs. I
> > could go the java route and use jxl or hssf as an API but I do not want
> to
> > rewrite the whole code in java again. Any suggestions/input will be much
> > appreciated.
> >
> > Cheers,
> >
> > Jake.
> > --
> > This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing
> list
> > To post a message email: RPG400-L@xxxxxxxxxxxx
> > To subscribe, unsubscribe, or change list options,
> > visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> > or email: RPG400-L-request@xxxxxxxxxxxx
> > Before posting, please take a moment to review the archives
> > at http://archive.midrange.com/rpg400-l.
> >
> >
>
>
> --
> "Enter any 11-digit prime number to continue..."
> "In Hebrew SQL, how do you use right() and left()?..." - Random Thought
> "If all you have is a hammer, all your problems begin to look like nails"
> --
> This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
> To post a message email: RPG400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/rpg400-l.
>
>