|
midrange.com
Jake,
My suggestions.
Create a new physical file that will contain all the fields you want to
download (it doesn't have to be just one field or comma seperated).
In this DDS of the new file, use the ALIAS tag to rename the fields to
something more meaning full. i.e. ALIAS(EMPLOYEE_NAME) (When
downloaded to Excel from Excel, this will be pulled in as the column
heading.
In Excel, (you may need to add in the "iSeries Access Data Transfer" if
so see below) Click on Data, Then Transfer Data From iSeries.
On the Transfer Request window, Select Create New, and include the
headings. Press Ok,
Put in the server name (ip address). Click Next
Then specify the phyiscal file you want to download. Click Next
Click Next.
Save the transfer request, click finish to save it.
iSeries Access Data Transfer Excel Add-in
Tools --> Add-in
On the Add-ins window, click browse and navigate to C:\Program
Files\IBM\Client Access\Shared
Select the file cwbtfxla.xll, Press OK
iSeries Access Data Transfer now appears as an available Add-ins.
Make sure it's checked and press OK
Now, Transfer Data From iSeries should appear in the Data menu.
Whenever there's a write to the detailed specs of the output, place a write
to the new physical file too. If cwbtfxla.xll isn't in the same location
as mine, then you may just need to search your computer for it. If you
still can't find it, don't ask me. :))
Michael Schutte
Work 614-492-7419
email michael_schutte@xxxxxxxxxxxx
"Jake M"
<jakeroc@xxxxxxxx
m> To
Sent by: "RPG programming on the AS400 /
rpg400-l-bounces@ iSeries" <rpg400-l@xxxxxxxxxxxx>
midrange.com cc
Subject
04/10/2006 09:42 Re: Printing a report in MS excel.
AM
Please respond to
RPG programming
on the AS400 /
iSeries
<rpg400-l@midrang
e.com>
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.
>
>
--
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.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
