Transcript AS400/iSeries/i5/IBM i on Power

iEXL -- Green is great but
sometimes only color will do …
 Simplify creation of spreadsheets, directly from the
Iseries
 Keep your cost down
 Leverage your existing knowledge base
 Create new reporting solutions
iEXL. System Background
 The iEXL product has been designed to simplify the creation of Excel spreadsheets
directly on the AS/400, ISeries, I5 or IBM I on Power (referred to as IBM I after).
 It’s purpose is to generate professional looking spreadsheets with very little effort. It can
also be used to simply download data from a file.
 The system uses Apache POI HSSF software to generate the spreadsheets.
 There is no need for further PC packages or database communications setup to use the
iEXL product.
 Create spreadsheets from RPG, COBOL, JAVA, QUERY, or SQL
 The system was designed to replicate IBM I spool file generation but is being used for
much more.
 iEXL is supplied as machine serial number level. No restrictions on number of users or
partitions.
 Already working on both European and North American systems.
iEXL. Excel Spreadsheet Generation
 Spreadsheets generated interactively on your system or …
 Spreadsheets stored direct to the IFS on your system.
 Spreadsheets emailed direct from your system to your users.
 Programmers create actual Excel spreadsheets that have colours, fonts,
images and many more attributes and controls.
 Users create actual spreadsheets for download/email.
 No need to retrain your staff just use their existing knowledge.
 No one or two members of staff specializing in one product.
iEXL. Excel Spreadsheet Generation
Lets Get Started !!!!
 Create a simple file with output that is stored on the
Iseries.
iEXL. A simple file example –
Create a file i.e. AS400 DDS code
DDS for file
IDSTEST
Edit your Library List
Add the library IDDEXL directly after
QTEMP.
Run the iEXL command
In this example the VFILE is set to ‘Y’. This parameter allows you to view the
Spreadsheet direct on your screen. You must have a drive mapped to the directory name
for this to work.
Spreadsheet Directory
Vfile Command
Resulting Spreadsheet
The result is a basic download of data. Some formatting
Such as decimal places and negatives are applied as
Standard.
The iEXL Defaults
 The iEXL command is the basis for running all
spreadsheet
The iEXL Command
The iEXL Command
The iEXL Command
The iEXL Command
Now Lets Enhance the Previous
Example !!!
Start the iEXL Menu.
Enter GO IDSMAIN from a command line.
The iEXL Menu
Select option 1
Select option 1
Using iEXL and it’s attributes
The First Steps
 The previous example has shown how to simply
download data from a file. Only basic formatting will
occur. To use the formatting options available to you
there are two stages.
Stage one consists of entering a spreadsheet name
associated with your file. This will allow options such a
sheet headers and footers, sheet names, column width
controls. To use this level of formatting you would
enter the command as follows.
The iEXL Menu
Select option 1
Select
Option
1
Select option
1
Spreadsheet Definition
Using iEXL and it’s attributes
Note that this time there is an entry in
the SPN parameter. For every file entered
an associated SPN can be entered.
Using iEXL and it’s attributes
 By entering a value in the SPN parameter it tells the
system to search for entries within the attributes files.
If entries are found they will then be applied to the
spreadsheet.
 Within this stage one example we will apply a sheet
name and some column headings .
From a command line enter ‘GO IDSMAIN’ and the
following will be displayed.
The iEXL Menu
Select option 1
Select Option 1 and select a
Select option 1
spreadsheet
Using iEXL and it’s attributes
Press F6 to add an entry. Then locate the IDSTEST entry and selection
option 5.
Select Spreadsheet Attributes
Select option 5
Using iEXL and it’s attributes
Press F15 for Sheet Names
Using iEXL and it’s attributes
Press F6 to add a new entry. After creating the
new entry press F12.
Using iEXL and it’s attributes
Press F13 to add column headings
Using iEXL and it’s attributes
Press F6 and add all your column headings.
Using iEXL and it’s attributes
Using iEXL and it’s attributes
 Run the iEXL command again making sure you enter
the SPN parameter.
Using iEXL and it’s attributes
Using iEXL and it’s attributes
There are now column
Headings.
Sheet name has been changed.
Using iEXL and it’s attributes
 The previous stage one example shows some of the options
available to you without any changes to the database file
IDSTEST.
 Stage 2
To apply more advanced attributes such as colours, fonts,
images, text etc attribute fields have to be added to the
database file.
This does not mean that you have to modify your actual
database files but you would need a work file that contains
the same data and the extra attribute fields.
Using iEXL and it’s attributes
 The system searches for file field text with specific values.
 To apply attributes to a complete row/record would require a
field with the text value set as TEXT('Atr Excel Rec'). This is case
specific.
 To apply attributes to a specific field would require a field with
the text value set as TEXT('Atr Excel Fld').This is case specific.
 As long as one of the above pieces of text are found within the
TEXT attribute the system will search for associated entries
within the attributes files.
Using iEXL and it’s attributes
The Field DSRPGR is
defined as a record
Level attribute field.
The Field DSNAMA and all
others are
defined as field level
attributes. Note the
attribute fields are defined
directly before each data
field.
Using iEXL and it’s attributes
 Go back into the iEXL menu system.
 From a command line enter GO IDSMAIN. Select
option 1, locate the IDSTEST spreadsheet enter, select
option 5.
 Press F11 to create edit codes.
Using iEXL and it’s attributes
Press F6 to create the above edit codes.
Press F12 to return to the previous screen.
Using iEXL and it’s attributes
Press F6 to create the following style code. The style is named ST2
And has a edit code number 2
Using iEXL and it’s attributes
Press F6 to create the following style code. The style is named ST4
And has a edit code number 1
Using iEXL and it’s attributes
The codes just created are then applied to the actual file. The attribute field DSABLA is
Populated with the value ST4. The attribute field DSCRLA is populated with a value of
Both ST2 and ST4.
Using iEXL and it’s attributes
Run the command again
Using iEXL and it’s attributes
The edit codes have now been
Applied to the data.
Using iEXL and Server Jobs
 In most cases all that is required is a single iEXL job to run,
generate a spreadsheet and end.
 Other cases may require the system to generate multiple
spreadsheets by being called multiple times. This can cause
system overheads such as JVM’s having to be created for
each spreadsheet. In this case iEXL server instances can be
used.
 By setting the server job name on a spreadsheet and setting
the SRVJOB parameter to ‘Y’ on the iEXL command , the
system will send all creation requests to a named job. That
job will remain active until no more requests have been
received for 1 hour.
Using iEXL and Server Jobs
Using iEXL and Server Jobs
Using iEXL and Server Jobs
Using iEXL and Server Jobs
Server job name has been set.
Using iEXL and Server Jobs
Note the SRVJOB parameter
has been set to ‘Y’
Using iEXL and Server Jobs
The iEXL subsystem has been started and the IDSTESTSRV job is running.
Using iEXL and Emails
 iEXL has an inbuilt system for the generation of emails.
 Emails are defined at user level. It two users wanted to email the
same spreadsheet both users have to be setup individually within
the system. This stops emails being sent by accident.
 Users are defined as internal, external or both. Internal users
cannot sent emails to addresses that have been defined as
external.
 Emails can be for individual recipients or distribution lists or
oneoffs.
 Email log files can be scanned for entries
Using iEXL and Emails
Take option 2 from the menu
Take option 2 from menu
Using iEXL and Emails
Create the user entry by using F6. Once created use option
5 to select the next level.
Using iEXL and Emails
Use F6 to create a distribution list header . Use option 5 to enter
email recipients.
Using iEXL and Emails
Use F6 to create a recipient entry.
Using iEXL and Emails
Enter the required distribution list in the DSTL and DSTLQ
parameters. The SNDEM parameter also needs to be set to ‘Y’.
Using iEXL and Emails
Option 5 from the menu gives you the ability to
Scan for Emails that have been issued.
Advanced Examples
Advanced Examples
iEXL. Excel spreadsheet Generation
 The examples of iEXL in this presentation only show the very
basics of what iEXL is capable of producing.
 It shows how the system searches file, field structures to apply
attributes to data.
 You can also think of the IDSTEST file used in this example as
the detail record format within a print file. You would then build
around the file with column headings, text etc.
 A a full user manual is available at iEXLSOFTWARE.COM.
 We can be contacted via the iEXLSOFTWARE.COM website,
Linkedin, Twitter, The Moore Consulting Group (US) or
[email protected]