iEXL -- Green is great but sometimes only color will do …

1. 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

2. 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.

3. 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.

4. iEXL. Excel Spreadsheet Generation Lets Get Started !!!! • Create a simple file with output that is stored on the Iseries.

5. iEXL. A simple file example – Create a file i.e. AS400 DDS code DDS for file IDSTEST

6. Edit your Library List Add the library IDDEXL directly after QTEMP.

7. 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.

8. Spreadsheet Directory

9. Vfile Command

10. Resulting Spreadsheet The result is a basic download of data. Some formatting Such as decimal places and negatives are applied as Standard.

11. The iEXL Defaults • The iEXL command is the basis for running all spreadsheet

12. The iEXL Command

13. The iEXL Command

14. The iEXL Command

15. The iEXL Command

16. Now Lets Enhance the Previous Example !!! Start the iEXL Menu. Enter GO IDSMAIN from a command line.

17. The iEXL Menu Select option 1 Select option 1

18. Using iEXL and it’s attributesThe 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.

19. The iEXL Menu Select option 1 Select Option 1 Select option 1

20. Spreadsheet Definition

21. 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.

22. 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.

23. The iEXL Menu Select option 1 Select Option 1 and select a spreadsheet Select option 1

24. Using iEXL and it’s attributes Press F6 to add an entry. Then locate the IDSTEST entry and selection option 5.

25. Select Spreadsheet Attributes Select option 5

26. Using iEXL and it’s attributes Press F15 for Sheet Names

27. Using iEXL and it’s attributes Press F6 to add a new entry. After creating the new entry press F12.

28. Using iEXL and it’s attributes Press F13 to add column headings

29. Using iEXL and it’s attributes Press F6 and add all your column headings.

30. Using iEXL and it’s attributes

31. Using iEXL and it’s attributes • Run the iEXL command again making sure you enter the SPN parameter.

32. Using iEXL and it’s attributes

33. Using iEXL and it’s attributes There are now column Headings. Sheet name has been changed.

34. 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 2To 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.

35. 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.

36. 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.

37. 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.

38. Using iEXL and it’s attributes Press F6 to create the above edit codes. Press F12 to return to the previous screen.

39. 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

40. 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

41. 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.

42. Using iEXL and it’s attributes Run the command again

43. Using iEXL and it’s attributes The edit codes have now been Applied to the data.

44. 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.

45. Using iEXL and Server Jobs

46. Using iEXL and Server Jobs

47. Using iEXL and Server Jobs

48. Using iEXL and Server Jobs Server job name has been set.

49. Using iEXL and Server Jobs Note the SRVJOB parameter has been set to ‘Y’

50. Using iEXL and Server Jobs The iEXL subsystem has been started and the IDSTESTSRV job is running.