InterExcel User Guide
Contents
1. nte rFo ri Output Management amp Archiving For IBM Midrange Servers J Administrati p m minis n ion Layout EG Conversion definitions LE x S DEMO N S E Conditional lines Column No in Excel CES DETAIL Start list with Search Clear Create Delete amp e HEAD1 C Column No Frompos Topos Stylenumber Datatypeincell M J HEAD2 HEAD3 1 2 2 E 6 e HEAD4 SEI TOTALL x 6 17 7 N EJ DEMO2 1 4 19 49 6 A J 18507 e J IS508 5 50 55 7 N 015750 6 0 RAIN70P1 1 6 59 69 7 N amp 7 R4LB30P1 LZ Z 70 78 7 A H O R4LB42 amp CJ ZHELP r1 8 79 95 7 N Under Conversion definitions you see the conversion definitions as first level of branches DEMO DEMO2 S507 Inside the DEMO conversion definition you see the conditioned lines DETAIL1 HEAD1 HEAD2 Within each conditioned line you see the layout of the line Above the layout of the line DETAIL1 is shown showing you the layout of cells starting with 2 and ending with 8 To open an element you can either click the element in the tree structure or the left or click the element on the list on the right of the screen InterForm A S 23 When you select option 3 Work with Conversion definitions after the INTERXEL INTERXEL command for the first time you will see this Work with Conversion definitions HEDUPD1 Position to Conversion definition Type options press Enter 2 Change 3 Copy 4 Delete 5 Display 7 Rename 8 Conditions Conv Sp2. CONUPD1 Line From To Description Type Line Line InterForm A S 27 A conditioned line or you might call it a line description looks like this Change Conditioned line CONUPD2 Conversion definition DEMO Line Name DETAIL1 Description Detail line Type D123HFX Y D From LENS abe oy Nus Ns a 10 Fo Le Gy cx GE CT 30 Row heights v vw 12 Extension in same XLS row N Row placement A Row number Condition If left blank all lines in the interval will be used From To Comp Pos Pos N lt gt Compare text 65 65 y F1 Help F3 Exit F12 Cancel The line type defines the kind of line you define here D Detail line This can be found on any page and it will always be included in the final output if selected A detail line only to be searched for on page 1 of the spooled file A detail line only to be searched for from page 2 until the end of the spooled file 3 A detail line only found on the last page of the spooled file N H A header line The header lines found on page 1 of the spooled file are included in the excel output while the header lines on the subsequent pages are not included in the output F Footer line The footer lines from the last page of the spooled file are included in the output The others are not That makes sure that line totals and the like is not making a mess of the output X Extension line This line is an extension of a previous line If th
3. Restore of the InterXEL directory Inside the interxel library you can find a save file named XELIFS This contains a saved interxel directory and subdirectories Restore the interxel directory with this command RST DEV qsys lib interxel lib xelifs file OBJ interxel 6 User s Manual for InterExcel400 That completes the installation of InterExcel400 Now proceed with Configuring InterExcel400 below Installation on PCs To use the full potential of InterExcel400 you need to setup the PCs that are going to interactively select a spooled file and view it as an excel spreadsheet A way to do this is to run the command INTERXEL INTERXEL and select option 30 Install PC tools This will list the things to do listed below 1 2 and 3 only need to be done once for the whole machine 1 Make sure that there is an INTERXEL directory on the iSeries If there is none you can restore it with RST DEV qsys lib interxel lib xelifs file OBJ interxel 2 Make sure that you have typed in the IP address or name of the iSeries in option 12 Configuration of InterExcel400 on the InterExcel400 main menu 3 Add a share on the iSeries of INTERXEL with the name of INTERXEL If you are running InterForm400 you can do that with the command APF3812 ADDFILSHR SHARE INTERXEL OBJ interxel TEXT INTERXEL 4 Map a Network drive on the PC in question to INTERXEL using the name of the iSeries stated in 12 Configuration of In
4. Conversion definitions C Font Weicome to InterExcel S E Spooled fil H styl Select option in left panel InterExcel400 Interword Notice the tree structure on the left You can simply click on the and left to the branches to open and close any subtree InterForm A S 45 InterExcel Windows Internet Explorer nterFo rm Output Management amp Archiving For IBM Midrange Servers 2 C3 Administration L Configuration LJ Create demo spooled file LJ Export Output queue Spooled files ee mpm Output queue library LJ License LJ Upgrade User profile ru sere Form type D Kse D KSEA 3 53 Conversion definitions Search Clear DEMO DEMO2 C 15507 ab edid Display spooled file Format as xls L 157590 ig 5 RAIN7OP1 Name User User data Status Copies Priority Output queue 2 R4LB30P1 C R4642 G P1403 KSE GEDRUCKT HELD 1 5 ABFALL C3 ZHELP L Fonts S Sealed filer gt EDTPRT KSE APFEDTPRT READY AFC INPUTI LJ Styles GT02221P KSE GT0222R READY 1 5 AFC_INPUTL EDTPRT KSE APFEDTPRT READY AFC INPUTI QPRINT KSE PRTCTL READY AFC_INPUTL APF3812 KSE R4CLSUROO_ READY i 1 AFC_INPUTL APF3812 KSE R4CLSUROO_ READY 1 AFC_INPUTL APF3812 KSE R4CLSUROO_ READY 1 AFC_INPUTL APF3812 k RSCLSUROO_ READY 1 AFC INPUT1 APF3812 L R4CLSUROO READY 1 i AFC_INPUTL The functionality and setting up InterExcel400 is described elsewhere in the manual It is in essence w
5. The license code of InterExcel400 can be found and change via INTERXEL INTERXEL and option 10 Work with license information Work with InterExcel400 license code Type in the values and press Enter Serial No 65BD1BB Enddate 20061121 YYYYMMDD Max Nbr users 050 License code NBTJ9MFB Version 001015 Valid License YES F3 Exit F12 Cancel When you receive a license code it must be delivered with an end date and a maximum number of users If the end date is blank it is a permanent license The same values must be entered with the license code for it to be accepted A user profile is considered to be a user of InterExcel400 and is counted in the list of users if the user profile is using either of the commands INTERXEL SPL2XEL or INTERXEL WRKOUTQXEL or if a spooled file owned by a user profile is handled by InterExcel400 converted into a stream file The license code is also depending on the version of InterExcel400 i e the first 3 digits of the version 001 above InterForm A S 41 Appendix A The Web Interface If you are not running InterExcel400 on an older iSeries and using InterExcel400 intensively with many users using InterExcel400 in parallel then you should consider to activate the web interface of InterExcel400 The web interface runs java programs on the System i and that can run slow if the System i is not up to date The web interface requires that the user profile WEBINTER
6. UNCs What are intranet settings Advanced ox And finally you can add the current site as a safe intranet site InterForm A S 47 Local intranet 48 Users Manual for InterExcel400 Index Configuring InterExcel400 9 Contact information 0 0 2 Convert spooled file to Excel IniBatch exte a e edes 35 Interactively llle eese 33 Convert spooled file to Text sss 33 convert text to numeric Automatic serana ta k RE wie peT eaa 31 Manually 00000000000 ee 39 Defining Gell Styles i dn een tees ele des 21 Conditioned lines 00 27 Conversion definitions 23 oec 19 Layout of cells llis eese 30 Display texts across cells 40 Import wizard 2 2 0 0 0 000 39 Initial test a coi oeste RE RU seede 13 Installation On PCS ena REC eer ene F Restore directory 000 6 Restore library ss dec eR 5 Introduction to InterExcel400 16 Licerise code an setae sce ies trece tenens 41 License Information 0 41 Setup of conversion 005 17 SBE2XEIL i oa saga satan odes hasta Cars 35 Support information 20005 2 Table of Contents 000 0 4 Text Import Wizard n nanana anaana 39 Web Server Configuration aaea aoaaa aeaa 42 Eliminate sign on request 43 Introduc
7. Work with Cell styles 3 Work with Conversion definitions These are described below using this spooled file as an example 18 User s Manual for InterExcel400 1 Work with fonts Here you make sure that you have the fonts you need for the spooled file After a new install you will already find a few font to help you get started Work with fonts Position EO amp i o x xx Font number Type options press Enter 2 Change 3 Copy 4 Delete 5 Display Font Font Font Opt Nbr Name Size B I 1 Arial 8 N N 2 Arial 10 N N 3 Arial 11 N N 4 Arial 12 N N 5 Arial 14 N N 6 Arial 16 N N L 7 Arial 20 N N 8 Arial 24 N N 9 Arial 30 N N E 11 Arial Bold 8 Y N _ 12 Arial Bold 10 Y N 13 Arial Bold 11 Y N Fl Help F3 Exit F5 Refresh F6 Create F12 Cancel 2222222222224 FNTUPD1 More This list is limited to 99 different fonts You can delete and change the predefined fonts as you please just remember that this might change the demo example in this manual To add a new font just press F6 and type the name as it is displayed in your PC applications InterExcel400 does not need to know the font name so you can even use special corporate font as long at it is placed on the PC that open the file The abbreviations B and U are short for Bold Italic and Underline The size of the font is measured as on your standard PC applications In the web interface you view edit the font list by opening the font tree on
8. can also format spooled files into excel files via a browser session by clicking Format as xls below 3 InterExcel Windows Internet Explorer 3 el isa 192 168 250 11 File Edit View Favorites Tools Help s r Favorites xig Apache Tomcat 5 5 20 Err hitp 192 168 250 e Copy and restore spoolfiles Certificate Error Naviga amp InterExcel nte rFo rm Output Management amp Archiving For IBM Midrang O Administration w Conversion definitions Spooled files S O Fonts Spooled files Output queue INTERXEL amp CJ Styles Output queue library User profile KSE Form type Search Clear Display spooled file Name User User data Status Pages Copie amp EDTPRT Kse APFEDTPRT sreaDy 32 1 EDTPRT Kse APFEDTPRT sreADY 128 1 Refer to appendix A on page 42 for information of the included web server Now InterExcel400 is ready to format your own spooled files 14 User s Manual for InterExcel400 Watch the movie You have now read the book or a part of it so how about watching a movie about it If you like to get a bit of inspiration you can watch a small video via this link http download interform400 com shares Newsletter InterExcel400 InterExcel400 swf InterForm A S 15 InterExcel400 introduction InterExcel400 was made to make a fast and easy formatting of reports that some use a lot of time to manually format For the end users the new additions
9. excel is called 1 column B is 2 and so on To column Merge cells f you want to merge several cells into one e g to make room for a large header line then you can state the en column here Above we merge cell 2 to 7 into one cell also called cell A to G Style number A style number previously created The cell style defines the font and borders for the cell Use F4 to list the existing styles You can also leave it blank same as 0 and use the style of a template excel file if used Min Column width The minimum column width The minimum width of this column when this cell is used The column width will be adjusted to fit the largest value of the min column width in all cells in this column Auto adjust of the width is not possible at the moment The unit of measure is approximately around 1 mm when printing on A3 So 25 is around 1 Inch Data type in cell The normal data type to use is A for alphanumeric This can be used for normal text If you however wants to be able to use a numeric value to do further calculations in excel after InterExcel400 have made the excel file you need to make sure that Excel recognizes the cell contents as numeric That can be done either manually as described on page 39 or by defining the field as N numeric If you state the field to be numeric the following fields should also be filled out Thousand separator The sign that InterExcel400 should look for as thousand separator
10. i to start restoring the INTERXEL library Now continue by restoring the interXEL directory as described below 2 Alternative Restore the InterExcel400 library with FTP Use this description to restore the INTERXEL library if the description above cannot be used For restoring the InterExcel library with FTP you first place the interxel savf file not too far from the root of a drive on your PC then do this The order of the actual ftp commands is important 1 Open a DOS session on your PC 2 Type ftp followed by the ip address of your as400 type your user profile and password when prompted 3 Type lcd c temp or wherever you have placed the interxel savf file 4 Type cd qgpl to temporarily place the save file in the qgpl library 5 Type bin to change to binary transfer 6 Type quote site namefmt 1 Response should be Now using naming format as aay 7 Transfer the file with put interxel savf 8 Type quit to exit the ftp session If this transfer should create a PF file on the host instead of a SAVF you should create the save file on the host before retrying the transfer You create the save file with this command CRTSAVF FILE QGPL INTERXEL When the transfer is done you restore the interxel library with the command RSTLIB SAVLIB INTERXEL DEV SAVF SAVF QGPL INTERXEL After that you can delete the temporary save file DLTF QGPL INTERXEL Now continue by restoring the interXEL directory as described below
11. in the spooled file You can leave it blank if no thousand separator is used Decimal separator If a sign for decimal point is used in the spooled file it must be stated here otherwise leave it blank Negative value indicator If the numeric value can be negative you here state how InterExcel400 can determine that A without would be the normal value to use The whole position interval is scanned for this InterForm A S 31 Below you can see how the layout links the spooled file and excel output for one of the cells in a detail line MESSE SE TE TENNE TIE TE e LE LIE NESTE NE TETT Work with Data Column Type Width aT Sales Report Rul Deparmjnt Remote Monitoring Tan i my da Tory vantiy sit Prke Discowad Total Price TG s n i uu 32 User s Manual for InterExcel400 The INTERXEL WRKOUTQXEL Command This command offers the possibility for end users to quickly convert a spooled file into an Excel or text file and open it immediately on the PC It looks very much like the OS400 WRKOUTQ www InterExcel400 com Work with Output Queue SPLUPD1 User Data Sts Pages Copies Form type Pty The additions to the normal WRKOUTQ command are these options 10 Open in Excel This opens the marked spooled file as an unformatted text file in Excel Blank lin 11 Format as xls This will search all conversion definitions in InterExcel400 to find a conversion definition that fits the sp
12. rubber band 2 7896 0 23688 16 SEM 1400003 4x1 32 inch braun rubber band 4 546 10 10918 AT SEM 5400012 4x1 8 inch green rubber band 4 64 15 2554 18 SEM 6400006 4x1 16 inch blue rubber band d 3354 0 13416 19 PDC 7400050 5x1 2 inch violet rubber band 2 546 0 1638 20 PDC 8400050 5x1 2 inch grey rubber band 4 654 0 1308 KSE 4100003 1x1 32 inch yellow rubber band 2 234 2096 467 5 PDC 2100006 1x1 16 inch red rubber band 3 1235 0 3705 BJE 100006 1x1 16 inch black rubber band 2 1565 0 3130 BJE 1200003 2x1 32 inch braun rubber band 4 2564 0 10256 BJE 5300012 3x1 8 inch green rubber band 4 13 0 52 26 PDC 6300006 3x1 16 inch blue rubber band 3 24365 0 73095 1 2 PDC 7100050 1x1 2 inch violet rubber band 2 654 0 1308 28 SEM 8400050 4x1 2 inch grey rubber band 4 3456 0 13824 29 SEM 9400050 4x1 2_inch white rubber band 2 5646 0 11292 30 PDC 400006 4x1 15 inch black rubber band 2 7896 0 23688 31 SEM 1400003 4x1 32 inch braun rubber band 4 546 1096 1091 8 32 SEM 5400012 4x1 8 inch green rubber band 4 64 15 255 4 33 SEM 6400006 4x1 16 inch blue rubber band 3 3354 0 13416 34 PDC 7400050 5x1 2 inch violet rubber band 2 546 0 1638 35 PDC 8400050 5x1 2 inch grey rubber band 4 E54 0 1308 3 T317 053 60 pial 6 As you can see you can add graphics to your output but you can also add formulas and charts via a predefined template Formulas will be updated via Ctrl F9 Alternatively you
13. the start up program These commands could be added to start up the web server please note that the start up should always be submitted as the command changes som environment variables of the current job SBMJOB CMD CALL PGM INTERXEL STRTOMSVRC JOB STRWEBSVR JOBQ QSYS QSYSNOMAX MONMSG MSGID CPF0000 Remember MONMSG to ignore any errors so the start up process will proceed independent of this submit If you want to submit an automatic end of the web server you can do that with this command 44 Users Manual for InterExcel400 SBMJOB CMD CALL PGMCINTERXEL ENDTOMSVRC JOB STRWEBSVR JOBQ QSYS QSYSNOMAX MONMSG MSGID CPF0000 Using the web interface To use the web interface you simply start up your web browser and type this as the address http lt ip gt lt port gt Where lt ip gt is exchanged with the ip address of the System i and lt port gt is exchanged with the port number specified above e g like so http 192 168 250 11 8800 Now you will be prompted for a user profile and password to access the System i in a new window After that you will see this Go File Edt View Fe gele Sg iy Favorites gig Apache Tomcat 5 5 20 Err i http 192 168 250 Copy and restore spoolfiles Certificate Error Navigation amp amp e inerexcel Gr OG deo Page Safety Tose Q InterForm Output Management amp Archiving For BM Midrange Servers Administration
14. 06 1x1 16 inch black rubber band 2 1 565 00 0 BJE 1200003 2x1 32 inch braun rubber band 4 2 564 00 0 BJE 5300012 3x1 8 inch green rubber band 4 13 00 0 PDC 6300006 3x1 16 inch blue rubber band 3 24 365 00 PDC 7100050 1x1 2 inch violet rubber band 2 654 00 SEM 8400050 4x1 2 inch grey rubber band 4 3 456 00 0 SEM 9400050 4x1 2 inch white rubber band 2 5 646 00 PDC 400006 4x1 16 inch black rubber band 2 7 896 00 0 SEM 1400003 4x1 32 inch braun rubber band 4 546 00 10 More F3 Exit F12 Cancel F19 Left F20 Right F24 More keys You may notice that the lines as well as the Transport and lines are missing in the Excel spreadsheet simply because no conditioned line is grabbing those lines When you have defined a conditioned line you use option 8 Layout to tell InterExcel400 how the relevant line is to placed in the cells in the current row of the output Read below to see how to do that InterForm A S 29 Define the Layout of cells After you have defined a conversion definition you define at least one conditioned line and within each of these you define one or more layouts You reach this from a command line by executing interxel interxel and selecting 3 Work with Conversion definitions 8 Conditions and 8 Layout Work with Cell Layouts LAYUPD1 Conversion definition DEMO Line Name es Yan el MS DETAIL1 Line type D123 HF D Position to Column No in Excel outp
15. Delete 5 Display Style Font Opt Nbr Description Nbr 1 Top Header with underline size 16 16 o 2 Second Header size 14 14 _ 3 Small headings Bold size 10 12 4 Small heading size 10 2 x 5 Small heading with T B Border size 10 12 6 Detail left adj size 10 2 7 Detail right adjust size 10 2 8 Total Right adj T B Borders size 10 12 End Fl Help F3 Exit F5 Refresh F6 Create Fll View 2 F12 Cancel Up to 99 cell styles can be defined A cell style looks like this Change cell style STYUPD2 Style number 1 D seoription s x 0v vxo Top Header with underline size 16 Font number 16 F4 Border Thickness Top 0 0 No border 1 Thin 2 Thick Border Thickness Left 0 Border Thickness Right 0 Border Thickness Bottom 2 Horizontal align LCR L Vertical align TB C J C Fl Help F3 Exit F4 Prompt F12 Cancel It has a number that you can refer to later and a description to help you to remember what it is for The font number points out the font to use for this style The border thickness can be 0 1 or 2 which denotes the width of a border O No border 1 Thin border 2 Thich border If you use a higher number the border will be dotted in a special manner The horizontal alignment can be L Left aligned C Centered R Right aligned InterForm A S 21 The vertical alignment can be T Top aligned C Centered B Bottom aligned J Justified Refer to the Excel Help for a description of that When you hav
16. Exit F4 Prompt F24 More keys SELECT F12 Cancel WRKSPLFXEL CURRENT Name CURRENT ALL ALL Name ALL LIBL Name LIBL ALL Form type ALL STD ALL User data ALL Bottom F13 How to use this display On this command you can limit the spooled files shown to be the ones that are owned by a special user AND that is placed on a special output queue where the WRKSPLF command only lets you combine the user with a printer device The extra options and the actual list of spooled files looks and runs exactly like the WRKOUTQXEL command above 34 Users Manual for InterExcel400 The Command SPL2XEL Spooled file to Excel The command SPL2XEL can be used for converting SCS spooled files into Excel or text files The command looks like this Spooled file to Excel conv SPL2XEL Type choices press Enter Spooled file FILE a Name Job name JOB Te ees Name User Name Job number SE 2 BE 000000 999999 Spooled file number SPLNBR o 000000 999999 Use Conv def Format output FORMAT YES YES NO Open in i OPEN NONE _ NONE EXCEL WORD Output type oe te Je Se OUTPUT XLS XLS TXT RTF Codepage if no conv def CODEPAGE JOB _ Character value Replace if no conv def REPLACE NO YES NO Excel file incl path OUTFILE DEFAULT DEFAULT TEMP Character value The fields are these Spooled file Job name User Job nu
17. Find Eu ee g EE mE E E Et EN S EE ERAS A et E E E E I Sales Report Rubber Band Inc Department Remote Monitoring User Smith Date 16 10 2006 Period 4th Quarter Time 14 20 55 Sales Rep Art Description Quantify Unit Price Discount KSE 4100003 1x1 32 inch yellow rubber band 2 234 00 20 PDC 2100006 1x1 16 inch red rubber band 3 1 235 00 0 BJE 100006 1x1 16 inch black rubber band 2 1 565 00 0 BJE 1200003 2x1 32 inch braun rubber band 4 2 564 00 0 BJE 5300012 3x1 8 inch green rubber band 4 13 00 0 PDC 6300006 3x1 16 inch blue rubber band 3 24 365 00 0 PDC 7100050 1x1 2 inch violet rubber band 2 654 00 0 SEM 8400050 4x1 2 inch grey rubber band 4 3 456 00 0 SEM 9400050 4x1 2 inch white rubber band 2 5 646 00 0 More F3 Exit F12 Cancel F19 Left F20 Right F24 More keys Into this spreadsheet A B e D E F G H Sales Report Rubber Band Inc jeep a i n ea e i e o lolo Department Remote Monitoring User Smith Date 16 10 2006 Period Ath Quarter Time 14 20 55 Sales Rep Art Description Quanti Unit Price Discount Total Price KSE 4100003 1x1 32 inch yellow rubber band 2 234 20 457 5 PDC 2100005 1x1 15 inch red rubber band 3 1235 0 3705 BJE 100006 1x1 16 inch black rubber band gt 1565 0 3130 BJE 1200003 2x1 32 inch braun rubber band 4 2564 0 10256 BJE 5300012 3x1 8 inch green rubber band 4 13r 0 52 PDC 6300006 3x1 15 inch blue rubber band 3 24365 0 73095 PDC 7100050 1x1 2 inch violet rubbe
18. InterExcel400 Manual version 2012 InterExcel400 eForms amp Intelligent Document Management for System i i5 iSeries amp AS 400 www interform400 com InterForm A S Hyldalvej 11 DK 9541 Suldrup Denmark tel 45 98 37 80 17 fax 45 98 37 87 99 e mail support interform400 com sales interform400 com web www interform400 com 2 Users Manual for InterExcel400 Document XEL 001 010 Users Manual for InterExcel400 V011 M044 Printed in Denmark 2012 No part of this guide may be photocopied or reproduced in any way except where noted without the written consent of Interform A S All the names in this manual are fictional and any resemblance to existing people or firms is purely coincidental Copyright Interform A S 2009 All rights reserved AS 400 is a registered trademark of the IBM Corporation HP PCL is a trademark of Hewlett Packard All other trademarks mentioned in this document are registered trademarks of their respective owners InterForm A S 3 Table of Contents Table of Contents hee ee lo xa ri ee Pe REX Rak ER 4 How to upgradelinstall InterExcel400 ele n BB 5 Upgrading InterExcel400 0 00020 cee 5 Installing InterExcel400 0 0 00 00 eee eee 5 1 Restore InterExcel400 library by use of RSTLIBPC 5 2 Alternative Restore the InterExcel400 library with FTP 6 Restore of the InterXEL direc
19. PC Remember to state the whole path including the file name e g C interxel temp myfile xls You can also use variables V1 V4 as a part of the path or file name If you use DEFAULT the files will be generated in C interxel temp with the file name file followed by a rolling number from 0 to 9 with the extension xIs of course When you are done setting up the conversion definition you select option 8 Conditions for the conversion definition to define the different types of lines in the spooled file 26 User s Manual for InterExcel400 Conditioned lines If you select option 8 Conditions for the DEMO conversion definition you will see this Work with Conditioned lines CONUPD1 Conversion definition DEMO Position tO x ox ox Line Name Type options press Enter 2 Change 3 Copy 4 Delete 5 Display 7 Rename 8 Layout Line Line From To From To Opt Name Description Type Line Line Pos Pos DETAILL Detail line D 10 30 65 65 E HEADI Top Header line H 2 2 0 0 E HEAD2 Second header H 3 3 0 0 HEAD3 Small header line H 4 6 0 0 M HEAD4 Column heading H 8 8 0 0 TOTAL1 Report total F 10 30 55 65 End Fl Help F3 Exit F5 Refresh F6 Create Fll View 2 F12 Cancel The definitions you see above has been made for the InterExcel400 demo spooled file Here you can see how the line definitions are linked to the output Er LN ET Iz EHI J Ir amp T I Sales Report Rul n Rinio Monitoring Sois T da
20. Type options press Enter 2 Change 3 Copy 4 Delete 5 Display From To Output Style Data Column Opt Pos Pos Column No Nbr Type Width 1 5 2 6 A 20 6 17 3 7 N 20 19 49 4 6 A 20 u 50 55 5 7 N 20 u 59 69 6 7 N 20 u 70 78 7 7 A 20 u T9 95 8 7 N 20 End Fl Help F3 Exit F5 Refresh F6 Create F12 Cancel Above we are looking at the layouts defined for the DETAIL1 conditioned line in the DEMO conversion definition created for the InterExcel400 demo spooled file A layout is defined like this Change Cell Layout LAYUPD2 Conversion definition DEMO Line Name Er HEAD1 Type D123 HFX Y H From position in splf 1 To position in splf 40 Column No in Excel output 2 To column Merge cells 7 Style number uH F4 Min Column width 20 Measured approx in mm Data type in cell N A A N Numeric Thousand separator in splf Decimal separator in splf Negative value indicator Only used for N Numeric Only used for N Numeric A Alpha numeric Only used for N Numeric F1 Help F3 Exit F12 Cancel From To position This is the interval of positions in the current spooled file line to be placed in this cell in the excel spreadsheet InterExcel400 will remove any leading and trailing blanks from the marked interval when copying it to excel 30 Users Manual for InterExcel400 Column No in Excel output The column number where the spooled file data is placed This is the cell number counted from the left so column A in
21. are simply these 2 new functions 1 A command INTERXEL SPL2XEL spooled file to Excel that will convert and format an SCS spooled file into a spreadsheet in the IFS This could e g be submitted to batch or e g called from an InterForm400 AFC user program The resulting Excel spreadsheet can either be directly opened on a connected PC for interactive jobs or be placed e g in batch directly in the IFS with the use of any PC 2 Acommand INTERXEL WRKOUTQXEL work with output queue InterExcel version that looks and works almost as the OS400 WRKOUTQ but with a few extra options to convert a spooled file As an extra help any user can in the green screen interface set the preferred output queue with calling these commands from the InterExcel400 Main Menu via option Conversion of spooled files can also be done via the included web server Refer to Appendix A on page 42 for details The spooled files can be converted in these ways a The full conversion where you setup rules setting up spooled files lines and positions and how they should be presented in the final Excel file output This is the real core of InterExcel400 This is done either via the SPL2XEL command or via option 11 in WRKOUTQXEL The resulting excel file can be placed in the IFS using a path that could include information from the spooled file and or spooled file attributes b The WRKOUTQXEL command also offers the options to convert a simple SCS spooled file
22. cel400 is delivered as a zipped save file Use e g Winzip to extract the Interxel savf file from the interxel zip file Here are 2 ways to restore the INTERXEL library that is inside the save file onto your System i 1 Restore InterExcel400 library by use of RSTLIBPC You can restore the INTERXEL library via the PC tool that is included in the distribution This requires that Java Runtime Environment is installed on the PC That is required in order to be able to convert spooled files to formatted excel files interactively anyway If this is missing you can download the latest version from here http java com en download manual jsp Just unzip all the files in the distributed zip file into the same directory and then do this 1 Double click on the RSTLIBPC bat file This will start a DOS window and shortly after this Restore Library from PC version 1 2 www interform400 com n te ame r m Output Management amp Archiving For IBM Midrange Servers Please Note Only New Libraries can be Restored InterForm A S 5 2 State the identification probably IP address of your System i your user id and your password for the System i click on Open Save File on PC and select the INTERXEL SAVF file 3 If you want to restore the INTERXEL library into INTERXEL and you probably will you can keep the field Restore to Library blank if not state the library you want to restore to 4 Finally click Restore on System
23. d If the row is lower than the rows previously processed then InterExcel400 will move down and place the line here If the row is already used then the line will be placed in the next free row Condition The condition can be left blank to simply use this line definition for the whole line interval above You can also state a condition to which the lines must fit in order to be considered here Use the comparisons Equal to Not equal to Greater than Less than AV Z So in the example above we search lines 10 to 30 for a in position 65 This is how we identify a detail line The problem is that the Grand total of the spooled file could also be found on any of these lines so that is why we have included a condition here The TOTAL1 line is also conditioned to select only the line with Grand Total in the spooled file Please notice that spooled file lines that are not selected by any conditioned line in InterExcel400 are not copied to the final output The top of page 2 in the SCS demo spooled file looks like this Display Spooled File FELS 2 oe a kt DEMO Page Line 2 7 Control e Columns T g Find Sat ie RUS oterkts vate PE ARE Puen chorea ARE AE ele Sie Prep sis e Verri sos ve cem ses DEE EG re ve oe Sales Rep Art Description Quantify Unit Price Discount Transport KSE 4100003 1x1 32 inch yellow rubber band 2 234 00 20 PDC 2100006 1x1 16 inch red rubber band 5 1 235 00 0 BJE 1000
24. e Web Interface 2 n lm 42 Prerequisites iusso hes eec ae quee ee del teed aS cda cepa a s 42 Configure the web server ee 42 Using tokens to eliminate a sign on request 2 43 Starting and stopping the web server liliis 44 Using the web interface 0 000000 een 45 Changing the security settings in MS Explorer llle 46 Index ses sce es Pee ee ee ee a a ee ee eee a 49 4 User s Manual for InterExcel400 How to upgrade install InterExcel400 Upgrading InterExcel400 If you already have installed InterExcel400 and want to upgrade to a newer version then you will first need to go through the steps below If you are running InterExcel400 version 001144 or newer you will first need to end the web server and end journaling This is done by entering InterExcel400 with the command INTERXEL INTERXEL select 80 Administration followed by 17 Prepare for upgrade rename of library and select 1 Prepare for upgrade do the above Next you should rename the existing INTERXEL library to e g INTERXELOL do a normal installation as described below and after the installation you enter the new INTERXEL library with the command INTERXEL INTERXEL and select option 80 Administration followed by 14 Upgrade InterExcel400 Copy resources from old Now confirm the copy and state the name of the old library e g INTERXELOL Installing InterExcel400 InterEx
25. e conditioned line triggered by the last spooled file line had a Y in the field Extension in same XLS row this line will be placed in the same row in the output excel spreadsheet Each of the cells in this line will be placed in the first free cell to the right of the previous line Y Fixed Extension line This works very simular to the X line above except that a Y line will fill out fixed cells counted from the left This will use the column number stated in the layout inside this conditioned line From To line The interval of lines in the spooled file to be considered for this kind of line Row height The height of the row in excel This should be about the same as the largest font size used in this row Extension in same XLS row If you insert a Y here then you tell InterExcel that this conditioned line can be linked with following X og Y lines with the same line name which may insert cells in the same row as this 28 User s Manual for InterExcel400 Row placement The placement of the current line in the excel file Keep this blank to simply use the next available row in the excel file This indicates that you want to skip rows counted from the row that was last inserted Type the number of rows to skip in the field Row number Row number 1 will give you one empty row A Absolute positioning This will place the spooled file line in a fixed row in the excel output State the row number to tell where it is to be place
26. e defined the necessary fonts and cell styles you can go on to define the spooled files to be converted in the next option on the InterXEL Main Menu Below you see the web interface If you want to work with the styles you simply open the Styles tree on the left Click either left or right on an existing style to change one or click the Create icon on the top to create a new style nte rFo rm Output Management amp Archiving For IBM Midrange Servers KSE 001144 E Administration Styles print Help amp 5 Conversion definitions Y a 1 Creste Delete 1 100f14 P M p 2 Second Header size 14 No Description Font No Top Left Right Bottom Horiz align Vert align L 3 Small headings Bold size 10 O 4 Small heading size 10 Hc s f a gp cientos ang iniit Soner underline 1s 9 4 se centre el Edit size size 16 LJ 6 Detail left adj size 10 Second verd LJ 7 Detail right adjust size 10 o 2 Header size 14 0 0 0 O Left adjust feeb 2 Edit I 8 Total Right adj 1 8 Borders size 2 10 ng D 9 Detail center adjust size 10 headings g a Q 0 Leftadjust e Edi D WENNS cape Bold size 10 10 Arial Bold 11 left adjust Small 11 Arial Bold 12 left adjust 4 heading size 2 0 0 0 0 Left adjust per 2 Edit D 12 Arial Bold 11 Right adjust 10 D 13 Arial 12 Bold right adjust heading with Z 5 12 1 o P lit 14 Small headings Bo
27. exists and that the user has a password which can be unknown by InterExcel400 When the first user enters the InterExcel400 menu this user is created if it does not exist Prerequisites Apart from installation of InterExcel400 it is required that the System i is running Java Runtime Environment version 1 5 or newer in order to use the web interface Configure the web server First you need to configure the web server In order to configure the server you go into InterExcel400 with the command INTERXEL INTERXEL and select option 80 Administration followed by 16 Work with Tomcat web server which will get you to this Screen Work with Web servers WSR100D Type options press Enter 1 Start server 2 Change 4 End server 5 Display 8 Display log Opt Description Status Library Inter Excel IDLE INTERXEL End F3 Exit F5 Refresh F10 Configure control ports Fll View 2 F12 Cancel You can here select option 2 Change to verify and change the ports used 42 User s Manual for InterExcel400 Change Web server WSR110D Description Inter Excel Sign on required YES YES NO TCP IP port x 4 4 Hs kg 8800 1 65535 TCP IP control port s o s 18800 10000 65535 F3 Exit F12 Cancel These are the default values If you set Sign on required to NO then you will need to use tokens with the browser sessions as described below The TCP IP port is the port to use when you use the browser interface You acce
28. g History Ctrl Shift Del Favorites sig Apach SE Apacy orate Browsing CtrleShiftep interexcel Reopen Last Browsing Session J InPrivate Filtering Ctrl Shift F InPrivate Filtering Settings n t e r Pop up Blocker SmartScreen Filter Manage Add ons Administration Compatibility View amp E Conversion definitions Compatibility View Settings la Fonts b o this Feed 1 Spooled files wen O Styles Windows Update Developer Tools Windows Messenger Diagnose Connection Problems Opret Foretrukken p den mobile enhed Blog This in Windows Live Writer Internet Options General Privacy Content Connections Programs Advanced Select a zone to view g hange security settings Restricted sites Local intranet This zone is for all websites that are found on your intranet Security level For this zone Custom Custom settings To change the settings click Custom level To use the recommended settings click Default level Custom level Default level Reset all zones to default level Now click the Advanced button in the new window below Local intranet Use the settings below to define which websites are included in the local intranet zone J automatically detect intranet network Include all local intranet sites not listed in other zones v Include all sites that bypass the proxy server L Include all network paths
29. gn up for online storage or connect to network server Cancel Select Tools and Map Network drive to do that 12 User s Manual for InterExcel400 Initial testing running the first time After a first time installation you might want to test that InterExcel400 is running OK and you have setup it up correctly To help you to do that InterExcel400 is delivered with a demo spooled file and a demo setup that fits with the spooled file Go to the InterExcel400 Main Menu with the command interxel interxel and select 80 Administration and then 13 Restore InterExcel400 demo spooled file Select an output queue where to place the spooled file and press Enter You can now view the restored spooled file Try the command INTERXEL WRKOUTQXEL and use the output queue as argument or use option 5 Work with an output queue on the InterExcel400 Main Menu www InterExcel400 com Work with Output Queue SPLUPD1 User Data Sts Pages Copies Form type Pty The spooled file is named DEMO with the user data InterExcel Now first try option 10 12 and 13 for this spooled file These options will convert the SCS spooled file into a PC file and start up an application to show you the result If this works you have a confirmation that InterExcel400 can communicate with your PC You can try options 10 12 and 13 for any SCS spooled file Next try option 11 Format as xls for the DEMO spooled file only This will look into a demo setup shi
30. into a xIs for opening in Excel txt or rtf document and open the file on the PC application that is associated with the extension These conversions are at the moment only offered on this command interactively When formatting a spooled file you can e g run the SPL2XEL command that retrieves many spooled file attributes and decides for what conversion definition to use This will determine the layout and name of the resulting excel spreadsheet For the administrators of InterExcel400 another command is InterXEL InterXEL This gets you to the main menu of InterExcel400 In the following chapter we will go through the InterXEL menu in details 16 User s Manual for InterExcel400 Setting up conversion of a spooled file In order to illustrate the steps of setting up InterExcel to convert a specific spooled file into an Excel spreadsheet we are going to refer to the InterExcel400 demo spooled file To print out a copy of this spooled file you type INTERXEL INTERXEL on a command line and select option 20 Restore InterExcel400 demo spooled file Here you type in the output queue on to which you want to restore the spooled file It is a good idea to select an output queue where it is not immediately printed out so you can view it during the setup Below we will refer to the conversion of the InterExcel400 demo spooled file looking like this Display Spooled File File DEMO Page Line 1 2 Control Columns 1 78
31. ixel200 system ixell copied bat 001106 bat ar jar 00 00 1106DB bat 1107 bat l bat l bat 1107DB bat 00 If the files are not copied you should go through the list above again Refer to page 11 for information of how to setup a share and mapping a network drive You also need to have Java Runtime Environment Version 1 3 or later installed If you InterForm A S 7 already are able to run the InterForm400 graphical designer on the same PC you have it installed You can download it for free from this site http java sun com javase downloads index jsp Scroll down to find link to download only Java Runtime Environment 8 User s Manual for InterExcel400 Configuring InterExcel400 To configure InterExcel400 you execute this command INTERXEL INTERXEL The first time you enter this command you may see the screen below Create WEBINTER user profile In order to use the InterExcel400 web server a special user profile is needed The user profile WEBINTER must exist in order to run the web server It is found that this user profile does not exist so it will now be created Press ENTER to create the user profile now The user profile is created with a random password F3 Exit ENTER Create the WEBINTER user profile F12 Cancel The user profile WEBINTER is needed for running the web server Press Enter to let InterExcel400 create it or press F3 F12 to ignore this Please notice that a random
32. lanment File View Help an Servers a TCP IP iSeries Access 3 3 sa DNS IBM iSeries Support for Windows Network Neighborh 222 2 1 247 TCP IP Server Name Li Status Descri a BOOTP DHCP Relay Agent Stopped Bootp ia BOOTP Stopped Bootp Ws Basic Operations Started DDM E Work Management Stopped DHCP Stopped Routel J iSeries NetServer 222 2 1 247 fo iSeries NetServer Windows Administration Fo RADIUS NAS m i Explore Enterprise Identity Mapping Ta WebFacing Internet Ha System Debug IT Printer security fa Management Central ia DLFM Fa Virtual Private Networ FocIMom fo ASFTomcat Left click on the machine Network TCP IP Then double click on iSeries Netserver and right click on Shared object and select New File Use interxel as Share name and interxel as Path name After you have made a share it is a VERY good idea to map a network drive In MS Explorer to this share to make sure that you have access InterForm A S 11 ifs on 222 2 1 247 5 Qe Q S JO search e Folders Ea Address Folders Map Network Drive 3 Desktop Windows can help you connect ta a shared network folder B My and assign a drive letter to the connection so that you can E Nm access the Folder using My Computer gt hue Specify the drive letter for the connection and the folder that you want to connect to Browse Si
33. ld Centre 2 T 8 Border 1 Leftadjust Edit size 10 size 10 Detail left Teik adj sini 10 2 0 0 0 0 Left adjust Edit Detail right 2 adjust size 2 o o 0 O Rightadjust 2 Edit 10 Total Right O s otis SL iie 12 2 0 0 2 Right adjust 2l Edit 10 Detail center va Centre jt 8 adjust size 2 0 o o 9 Sdiust Edit i2 dos 13 Left adjust 2 Edit 22 User s Manual for InterExcel400 3 Work with Conversion definitions Conversion definitions is the setup that determines how a specific spooled file should be formatted into an excel spreadsheet if you activate this conversion The conversion definition consists of 3 layers InterExcel400 Spooled file Excel spreadsheet File name 1 Conversion definition Spooled file type ID 2 Conditioned lines Spooled file line 3 Layout Spooled file positions This means that a conversion definition identifies the spooled file and decides for an output file name Each conversion definition consists of one or many conditioned lines that identifies what kind of line each spooled file line is and thus decides for what row to use Each conditioned line consists of one or many layout definitions that takes spooled file text from relevant positions in the given line and tells in which cells the text is to be placed The tree structure is clearly visible on the web interface InterExcel Windows Internet Explorer Oc v 192 168 250 11 qe Favorites GS InterExcel
34. lf Opt Definition Description Form type Name Er DEMO InterExcel400 demo spool file STD DEMO Us DEMO2 InterExcel400 demo spool file DEMO2 DEMO _ IS 507 FMIS 18507 IS508 FMIS IS508 _ IS750 FMIS IS750 R4IN70P1 Demo for XPPS Infor splf R4IN70P1 e R4LB30P1 Demo for XPPS Infor splf R4LB30P1 S R4LB42 Demo for XPPS Infor splf R4LB42P2 ZHELP Help to define conversion def QSYSPRT End Fl Help F3 Exit F5 Refresh F6 Create Fll View 2 F12 Cancel This lists the different kinds of spooled files that are known by InterExcel400 Here we use the conversion definition named DEMO It looks like this Selecting option 2 Change Change Conversion definition Conversion definition Description Form type Save attr Job Name a Spooled file name Printer file Program Output Excel file name of Splf Y N DEMO HEDUPD2 InterExcel400 demo spool file Printer file Library Program Library interxel temp V1 V2 V3 xls Spooled file codepage Variables for file name WI VZ 3 V4 Fl Help F3 Exit Replace if file exist Y N Y Splf Atr From To or blank pos pos Line 1 12 2 22 35 5 22 33 6 F12 Cancel Conversion definiton name is used when InterExcel400 is searched for a fit with a spooled file The conversion definition are searched in alphabetical order but normally that is not important The spooled file attributes Form type save attr Job name spooled file name printer file pri
35. ly include a part of the spooled file attribute InterForm A S 25 When you press Enter you see the other fields on a conversion definition Change Conversion definition HEDUPD2 Template xls file demo xls Use template styles Y N N Page Range Brom page xw 1 Blank means page 1 To Page he ee ae es END Blank or END means last splf page Local file on PC C INTERXEL TEMP MYFILE XLS Delete original spool file NO Fl Help F3 Exit F12 Cancel Template xIs file If you want to insert the spooled file data into an existing excel file then you state the existing file here It is to be placed in the InterXEL Templates directory in the IFS This template can have predefined graphics formulas and charts to help illustrate the data found Use template styles If you use a template you can chose if the styles fonts borders formats etc should override the styles of your conversion definition State Y to do that If you state N the styles defined in InterExcel400 will override the styles found in the template However if you have stated 0 as the style in a layout the template style will still be used Page Range If you do not want to convert the whole spooled file into excel you can state a page range to use here instead Local file on PC If you convert your spooled files into excel via option 11 you can here state an alternative name for the temporary files generated on your
36. mber and Spooled file number identifies the spooled file to be converted FORMAT OPEN OUTPUT CODEPAGE REPLACE OUTFILE YES indicates that the spooled file must be linked with an InterExcel400 Conversion definition and converted into a formatted excel spreadsheet according to this definition NO means that the output will be unformatted text in a file with the extension indicated in the OUTPUT field NONE indicates that the resulting file will not be opened on a connected PC You must select NONE for any batch jobs EXCEL indicates that the resulting file is to be opened in Excel WORD means that the converted spooled file should be converted into a file with the extension denoted in OUTPUT and the PC application associated with this extension should open the file The type and extension of the resulting file XLS TXT and RTF are supported values The codepage in which the spooled file is written JOB will use the codepage DFTCCSID of the current job State if InterExcel400 can replace the old file if a file with the same name already exists The name of the stream file to be created DEFAULT can be used if you select FORMAT YES This will use the path specified in the conversion definition that fits the spooled file TEMP means that only a temporary output file will be created This can be used for interactive jobs where you want to open the resulting file but not necessarily place it in the IFS of the iSe
37. mp JOBNAME SPLNBR amp FILNBR ENDPGM You can copy the member called funkt 8 from the source file apf3812 apisrc and remove any unnecessary lines from the copy and just add the INTERXEL SPL2XEL command Please go through the parameters of the SPL2XEL command to make sure all the parameters are set correctly 2 Compile the program you have just created Make sure that the compiled program is not placed in the INTERXEL or APF3812 library as that can cause a problem the next time you upgrade either InterForm400 or InterExcel400 3 The final step sets your changes into production Please note that it takes a bit longer to create Excel files directly on the iSeries compared to normal InterForm400 functions so it might be an idea to use a dedicated output queue for this To setup InterForm400 for this you setup the output queue in auto forms control like this InterForm A S 37 Update AFC functions attached to output queues AFC305D Queue INTERXEL Library QUSRSYS Seqnbr Funct Form type Save Jobname Filename Device file Program 0001 8 DEMO Call program FUNKT 8XEL library APF3812 User profile AFCOPER 0002 6 Hold Spooled File Seqnbr Function 19 8 Program call Program name AFCSPL2XEL Name LIDLALyP gs AS ote APF3812 Name LIBL User profile HE AFCOPER AFCOPER OWNER F3 Exit F13 Fold Unfold Fll Delete F12 Cancel In this example we have chosen to only convert spooled files with the name DEMO int
38. n insert it here Alternatively you can state it per user on the user setup 10 User s Manual for InterExcel400 Debug Keep temporary files in the IFS If InterExcel400 does not react as you expect it can be an idea to temporarily change this into Y you may be asked to do so from your local support This will leave any temporary files in the IFS and QTEMP and run a special bat file for conversion so you can view the process of the used bat file Remember to change this to N when done to avoid excessive disk usage Please refer to Appendix A on page 42 for details of setting up the integrated web server Sharing the InterXEL directory In order to make it possible to convert and view spooled files directly from the WRKOUTQXEL command you need to share the interxel directly as the temporary files for that is placed in interxel temp If you are running InterForm400 you can share the interxel directory with this command APF3812 ADDFILSHR SHARE INTERXEL OBJ interxel TEXT Interxel directory Alternatively you can share it via iSeries Navigator like this iSeries Navigator 5 b e I d Environment My Connections 4 BY Management Central 222 2 1 247 E My Connections Configuration and Service E kg Network 12 TCP IP Configuration h Remote Access Services User Defined f IP Policies gg Users and Groups li Databases S File Systems Backup x BY annlication Deve
39. not blank password is needed If the WEBINTER user exists then this screen is not shown You will then see the InterExcel400 Main Menu www InterExcel400 com Select an option 1 Work 2 Work 3 Work 4 Work 5 Work 6 Open E with with with with with fonts Cell styles Conversion definitions spooled files an output queue a spooled file in Excel Install PC tools 9 Output queue to use 80 Administration 90 Sign Selection F3 Exit off Copyright of InterForm A S 2008 Main Menu Version 001133 WRKSPLFXEL WRKOUTOXEL SPL2XEL F12 Cancel www interform400 com InterForm A S 9 You do not need to add the INTERXEL library to your library list Adding the INTERXEL library to the system library list is not recommended as this may cause problems with future upgrades If the license code have run out or is invalid you will get a warning when entering the command Now enter option 80 Administration followed by 12 Configuration of InterExcel400 InterExcel400 Configuration Merge overprint lines in spooled files Y Create directories for output files if necessary Y N Stop with error message IP address of AS400 or Netserver name 192 168 151 3 Run Host Java program under QSHELL Y N Y Default PC drive to use on clients C Debug Keep temporary files in the IFS N Y N F3 Exit F12 Cancel Change the option
40. nter file library program and program library are all used to identify the spooled file If any are filled out they must fit exactly with the spooled file if this conversion definition is to be selected for a requesting spooled file The Output Excel file name defines where the resulting excel spreadsheet is to be created when using the SPL2XEL command This can later be overridden when converting a specific spooled file in the SPL2XEL command As a part of the name you can use up to 24 Users Manual for InterExcel400 4 variables defined below The values of the variables will be substituted for V1 V4 in the file name and path Note that directories in the path can be created if necessary depending on the configuration values of InterExcel400 Replace if file exists Y N determines if InterExcel400 can replace existing files or not If you state N and a file with the same name already exist you will get an error message Spooled file codepage Leave this field empty if the spooled file is in the same codepage as the current job If not type the codepage to use for the conversion from EBCDIC into ASCII Variables for file name The variables V1 V4 can be defined either as spooled file text as above leaving the splf Atr Field empty or any of the spooled file attributes FORMTYPE JOBNAME USER JOBNUMBER SPLFNUMBER SPLFNAME USRDTA USRDFNDTA You can even combine the spooled file attributes with the from pos and to pos fields to on
41. o Excel spreadsheets This selection should fit the definitions in InterExcel400 so the spooled files are recognized by InterExcel400 and a fitting conversion definition can be found This fits the InterExcel400 demo spooled file You can consider to use either AFCOPER or OWNER as the user profile for the program call The user profile stated here will own the created excel spreadsheet and should have authority to create the files in the IFS 38 User s Manual for InterExcel400 Tips and tricks for Excel and InterExcel400 Below are listed a few tips and tricks that might come in handy when using InterExcel400 and or Excel Activating the Text import wizard in Excel Some of your users might want to convert a spooled files into excel ad hoc i e they want to create an excel spreadsheet for a spooled file for which no conversion definition have been defined The simple way to do that is from the INTERXEL W RKOUTQXEL command and select option 10 Open in Excel That will not break up the file into nice columns but you can be prompted for how it should be broken up by simply closing the file and then reopening it from Excel by selecting File and selecting the temporary file like this Ei Microsoft Excel fl File Edit view Insert Format Tools Data window j Ctrl 0 Print CtrH P 1 cilinterxel tempifile xls When you then reopen the file the Text import wizard will start After selecting next you can drag
42. olbar Select the range of cells that contain the text numbers On the Edit menu click Paste Special click Multiply and then click OK Displaying large texts across cells If you want to distribute one value or cell across many cells you can do that in the layout Here we are distributing the header across cells from 2 to 7 Change Cell Layout Conversion definition DEMO Line Name NE NE HEAD1 Type D123HFX Y H From position in splf 1 To position in splf 40 Column No in Excel output 2 Style number 1 Min Column width 20 Data type in cell N A b A Thousand separator in splf Decimal separator in splf Negative value indicator Fl Help F3 Exit To column Merge F4 Measured approx A Alpha numeric Only used for N Only used for N Only used for N Using charts with the spooled file data LAYUPD2 cells 7 in mm N Numeric Numeric Numeric Numeric F12 Cancel You can define charts in your template to present your spooled file data in a manner that is easier to read as you know a picture tells more than a thousand words You just define the chart outside the area where the spooled file data is inserted Refer to the template R4LB30P1 xIs in interxel templates and refer to the conversion definition with the same name This has been predefined for the Infor XPPS spooled file with the same name 40 User s Manual for InterExcel400 License code information
43. ooled file attributes If found it will convert the spooled file to a formattet Excel spreadsheet and open it 12 Open as txt Opens the spooled file unformatted as a txt file in the application associated with txt 13 Open as rtf Opens the spooled file unformatted as a rtf file in the application associated with rtf The limitations compared to the normal OS400 WRKOUTQ are these a The output queue contents is not updated unless you exit and reenter the command b Option 7 Messages is not yet supported c F17 top and F18 bottom are not supported yet but you can position to a spooled file number in the list counted from the top d There is no command line e F5 Refresh does not work yet Notice When you open a spooled file with any of the options 10 13 the PC application will open a temporary file The same temporary file will be reused for the same interactive job meaning that you will may get an object lock error if you try to convert a new spooled file before closing the temporary file you opened earlier InterForm A S 33 The INTERXEL WRKSPLFXEL Command Just like WRKOUTQXEL above gives you an extended version of the WRKOUTQ command the WRKSPLFXEL command is an extended version of WRKSPLF One difference is the parameters on the command Type choices Work with Spooled Files press Enter Select files for User Wu EM Output queue Output queue Library Form type ep ed User data F3
44. orking in the same manner for both green screen and browser sessions except that the tree structure of the conversion definitions conditioned lines and layouts can be clearly seen in the browser interface Above we have clicked Spooled files on the left listing the spooled files of the current user Now you can type in a search criteria on the upper right if you e g want only to see spooled files on a specific output queue Currently the option Display spooled file has not been implemented in the Browser interface yet If you want to format a spooled file into an Excel file you mark the spooled file and click the Format as xls icon on the top If nothing happens or if a browser window quickly flash and end then you might need to adjust the security settings of your browser to allow your System i to output the file to your browser Below it is shown how to do that in MS Internet Explorer Changing the security settings in MS Explorer If you see a flashing new window when you try to format a spooled file as an Excel file via the browser then it is very likely that your security settings are restricting this Here is a one way to allow the System i to open the excel file on your PC In MS Explorer you select Tools and Internet Options 46 User s Manual for InterExcel400 2 InterExcel Windows Internet Explorer Go w http 192 168 250 11 8800 File Edit View Favorites 5 m Delete Browsin
45. ound when calling the program InterForm A S 43 Here is an example http 192 168 250 11 8800 user KSE amp token 5kodcEI5RZIz2J7 X8Npzxy2psVLKSbgz Here the user profile KSE is running the browser session with the token 5kodcEIBRZIz2J7 X8Npzxy2psVLKSbgz Starting and stopping the web server You can start and stop the web server by entering InterExcel400 with the command INTERXEL INTERXEL and selecting option 80 Administration followed by 16 Work with Tomcat web server which will get you to this screen Work with Web servers WSR100D Type options press Enter 1 Start server 2 Change 4 End server 5 Display 8 Display log Opt Description Status Library Inter Excel IDLE INTERXEL End F3 Exit F5 Refresh F10 Configure control ports F1l View 2 F12 Cancel If you select option 1 Start server you will submit a job called STRWEBSVR to the job queue QSYSNOMAX which will normally mean that the job runs in subsystem QSYSWRK After a few seconds the status should change to ACTIVE If not you can check the joblog of the submitted job e g with the command WRKJOB STRWEBSVR If you select option 4 End server a job called ENDWEBSVR is submitted like above The web server will then end after a few seconds You would probably want the web server to start as a part of the IPL If so you can add the commands below at the end of the start up program keeping in mind that ONLY a system responsible should change
46. pped with InterExcel400 to determine how to format the spooled file when converting it into an Excel spreadsheet and then open the converted spreadsheet This takes longer the first time as there are some initial files to be converted If you work a lot in the green screen InterExcel400 Menu you can set your preferred output queue via option 9 Output queue to use Here you state your personal output queue and this will from now on be suggested when you select option 4 and 5 on the InterExcel400 Main Menu InterForm A S 13 The final Excel spreadsheet should look like this A B C D E F G H l K L 1 Sales Report Rubber Band Inc E Department Remote Monitoring ES User Smith Date 16 10 2006 4 Period 4th Quarter Time 14 20 55 5 Sales Rep Art Description Quanti Unit Price Discount Total Price Eeg KSE 1 moai inch yellow ruber band m 23 20 4575 7 InterForm Ba PDC 2100006 1x1 16 inch red rubber band 3 1235 0 3705 8 BJE 100006 1x1 16 inch black rubber band 2 1585 096 3130 Boal BJE 1200003 2x1 32 inch braun rubber band 4 2564 0 10256 10 BJE 5300012 3x1 8 inch green rubber band 4 13 0 52 AT PDC 6300006 3x1 16 inch blue rubber band 3 24365 0 73095 12 PDC 7100050 1x1 2 inch violet rubber band 2 654 0 1308 43 SEM 8400050 4x1 2 inch grey rubber band 4 3456 0 13824 14 SEM 9400050 4x1 2_inch white rubber band 2 5646 0 11292 18 PDC 400006 4 1 16 inch black
47. r band 2 654 0 1308 SEM 8400050 4x1 2 inch grey rubber band 4 3456 0 13824 SEM 9400050 4x1 2 inch white rubber band 2 5646 0 11292 PDC 400006 4x1 16 inch black rubber band 2 7896 0 23688 SEM 1400003 4x1 32 inch braun rubber band 4 546 10 1091 8 SEM 5400012 4x1 8 inch green rubber band 4 64 15 2554 SEM 6400006 4x1 15 inch blue rubber band 3 3354 0 13416 PDC 7400050 5x1 2 inch violet rubber band 2 546 0 1638 PDC 8400050 5x1 2 inch grey rubber band 4 654 0 1308 KSE 4100003 1x1 32 inch yellow rubber band 2 234 20 467 6 PDC 2100006 1x1 15 inch red rubber band 3 1235 0 3705 BJE 100006 1x1 16 inch black rubber band 2 1565 0 3130 BJE 1200003 2x1 32 inch braun rubber band 4 2564 0 10256 BJE 5300012 3x1 8 inch green rubber band 4 137 0 52 PDC 6300006 3x1 15 inch blue rubber band 3 24365 0 73095 PDC 7100050 1x1 2 inch violet rubber band 2 654 0 1308 SEM 8400050 4x1 2 inch grey rubber band 4 3456 0 13824 SEM 9400050 4x1 2 inch white rubber band 2 5646 0 11292 PDC 400006 4x1 16 inch black rubber band 2 7896 0 23688 SEM 1400003 4x1 32 inch braun rubber band 4 546 10 1091 8 SEM 5400012 4x1 8 inch green rubber band 4 B4 15 2554 SEM 6400006 4x1 15 inch blue rubber band 3354 0 13416 PDC 7400050 5x1 2 inch violet rubber band 2 546 0 1638 PDC 8400050 5x1 2 inch grey rubber band 4 654 0 1308 317 053 60 InterForm A S 17 The setup contains of these 3 options 1 Work with fonts 2
48. ries InterForm A S 35 The speed of the SPL2XEL command varies a lot when you run it with FORMAT YES If you combine format YES with a path or DEFAULT as OUTFILE the conversion into an excel spreadsheet is done 100 on the iSeries with a java program This takes considerably longer than if you combine format YES with outfile TEMP as this will run the java program on the connected PC that is to open the excel file Please note that using TEMP as outfile reuses the same temporary file so in order to avoid any object lock you need to close the previously opened file on the PC 36 Users Manual for InterExcel400 Running InterExcel400 with InterForm400 InterExcel400 can be used without InterForm400 but if needed it can easily be setup with InterForm400 You can simply use the demo program delivered with InterExcel400 It is INTERXEL AFCSPL2XLS which is prepared like below and the owner is QSECOFR The source of this program can be found in INTERXEL APISRC These are the steps you can use 1 Create this source member to be used via a option 8 Call Program in auto forms control in InterForm400 PGM amp JOBNAME amp USER amp JOBNBR amp FILNAM amp FILNBR amp OUTQ amp OUTQLIB DCL amp JOBNAME CHAR 10 DCL amp USER CHAR 10 DCL amp JOBNBR CHAR 6 DCL amp FILNAM CHAR 10 DCL amp FILNBR CHAR 6 DCL amp OUTQ CHAR 10 DCL amp OUTOLIB CHAR 10 INTERXEL SPL2XEL FILE amp FILNAM JOB amp JOBNBR amp USER a
49. s to fit your installation Merge overprint lines in spooled files This option is default activated and should be left like this if you are in doubt if there are any spooled files to be handled by InterExcel400 that are using overprinting e g for printing in bold InterExcel400 will not handle spooled files using overprinting correctly if this is disabled Create directories for output files if necessary When you setup InterExcel400 to create Excel spreadsheets in the IFS e g by using variables in the path you would perhaps want InterExcel any directory need to fit the path specified If you want to restrict InterExcel400 from creating directories state N IP address of AS400 or Netserver name InterExcel400 can startup a PC application to view a marked spooled file as e g an Excel spreadsheet from the INTERXEL W RKOUTQXEL command In order to do so you need here to type the identification of the iSeries as seen from the PC The IP address can be used in most cases Do not type any delimiters around the IP address Run Host Java program under QSHELL If you convert spooled files to excel via the command interxel spl2xel e g in batch then InterExcel400 will normally run the java program under QSHELL If this is a problem you can change the default Y into N Default PC drive to use on clients Default InterExcel400 will use the C drive on the local PCs If you want to use a different drive for all users you ca
50. ss InterExcel400 by e g typing this in the browser http 192 168 250 11 8800 Where you of course exchange 192 168 250 11 with the IP address or identification of your system i If you find that the web server does not behave as it should you can use option 8 Display log to check the log of the web server for any errors Using tokens to eliminate a sign on request If you do not want to sign on specifying NO for Sign on required above then you will need to use tokens instead First you need to get a token You can do that via program that is calling the command INTERXEL GETWEBTKN Get web token This program should call the command with a variable defined as CHAR alphanumeric which must be at least 32 characters long The variable will contain the token value when returning from the command The command GETWEBTKN can only be called from a program The program must run under the same user as the user that should use the token You can e g call this CL program PGM DCL amp TOKEN CHAR 32 INTERXEL GETWEBTKN TOKEN amp TOKEN DMPCLPGM ENDPGM It will generate a spooled file containing value of the token in the variable TOKEN Now you can use the token to access InterExcel400 via a browser by specifying this as the address http system port user usrprf amp token token Where usrprf is the user profile that has run the program above and the one running the browser session and the last token is the value f
51. terExcel400 You do that in the MS Explorer on the PC 5 Install Java Runtime Environment JRE on the PC Download from http java sun com javase downloads index jsp 6 Copy the INTERXEL directory from the iSeries onto the C drive This can be done via the INTERXEL INTERXEL command and option 30 Install PC tools When you press ENTER it will copy the INTERXEL directory onto the PC It will start a DOS session and it should look like this for a successful execution NN NN NN NN NN NN NNI NN NN NN NN NN NN NN NNI NN NN NN 92 1 92 1 92 1 92 41 92 1 92 1 92 1 9241 92 1 92 1 92 1 92 41 92 1 92 1 92 1 9241 92 1 9241 Press 18 any 68 1 68 1 68 1 68 1 68 1 68 1 68 1 68 1 68 1 68 1 68 1 68 1 68 1 68 1 68 1 68 1 68 1 68 1 PL On O1 Ot Ot Ol O1 Ori Gi CO Or OT O1 Ot Ot Ol Fh le s key to return 3 interxel 3 interxel 3 interxel 3 interxel 3 interxel 3 interxel 3 interxel 3 interxel 3 interxel 3 interxel 3 interxel 3 interxel 3 interxel 3 interxel 3 interxel 3 interxel 3 interxel 3 interxel system LICE NSE system PCinstall bat system QSHELL output system dummy txt NsystemNiXELOO1V106 jar system ixel system ixell 2 bat 3 bat system ixel system ixel system poi j system poi3 system xelS1 ry TXT system xelfnt TXT system ixell system ixell system ixel300 system
52. the left and then you can click on the font to change either on the left or right KSE 001144 nte rFo rm Output Management amp Archiving For IBM Midrange Servers H E Administration 3 Conversion definitions Fonte S Fonts arial 8 Description D Arial 10 Size D Arial 11 L Arial 12 Start list with L3 Arial 14 D Arial 16 Search Clear L Arial 20 L Arial 24 D Arial 30 D Arial Bold 8 LJ arial Bold 10 Create Delete D Arial Bold 11 D Arial Bold 12 Nbr Description Size Bold Italic Underline D Arial Bold 14 i GE D Arial Bold 16 L3 Arial Bold 20 Arial 19 3 Arial Bold 24 O Arial Bold 30 3 Arial 11 D Courier New 8 C Courier New 20 A MM ag D Courier New 11 5 Adal i4 D Courier New 12 L Courier New 14 a amp Arial 16 L Courier New 16 3 Courier New 20 m Z Arial 20 L Courier New 24 3 Courier New 30 E AN et D Courier New Sold 8 q 3 Arial 30 3 Courier New Bold 10 L Courier New Bold 11 O ii Arial Sold 8 v L Courier New Bold 12 1 10 of 36 y p InterForm A S 19 When you are sure that you have the fonts you need you can proceed to work with the cell styles See below 20 Users Manual for InterExcel400 2 Work with Cell styles Select this option to define various way to format the spreadsheet cells Work with cell styles STYUPD1 Position EO uwew br 3 Style number Type options press Enter 2 Change 3 Copy 4
53. the lines below to state how the columns should be split up Text Import Wizard Step 2 of 3 This screen lets you set field widths column breaks Lines with arrows signify a column break To CREATE a break line click at the desired position To DELETE a break line double click on the line To MOVE a break line click and drag it Data preview 10 20 30 40 50 60 PF1124 Information 02 10 06 07 13 11 258280 29 subsystem IF400 in IF400 PF1164 i 05 10 06 08 48 40 243824 Job 155214 gt Converting text into numeric fields in excel When formatting a spooled file into an excel spreadsheet you can define cells to be numeric if the text inside is numeric but for some situations you might want manually to tell excel to consider text in a cell to be numeric This might be the case in these situations a The cells are marked with the warning The number in this cell is formatted as text or preceded by an apostophe InterForm A S 39 b You want to execute a numeric formula e g SUM on the relevant cells This can manually be fixed in this manner Quote from help in Excel 1 Select a blank cell that you know has the General number format If you are not sure of the cell format click Cells on the Format menu and then click the Number tab In the Category box click General and then click OK In the cell type 1 and then press ENTER Click the cell and then click Copy on the Standard to
54. tion esee 42 MS Explorer setup 024 46 Start and Stop seca eed ws 44 Using the web interface 45 USING TOKENS icv tea tete tees 43 Wizard ioc iE na ete hes espoir eae 39 WRKOUTQXEL sssseeeeee eee 33 WRKSPLFXEL 2er mamme 34 InterForm A S 49
55. tory llle 6 Installation on PCS naana auaa aanne r Configuring InterExcel400 l l avrunde 9 Sharing the InterXEL directory lille 11 Initial testing running the first time ln 13 Watch the MOVIC usc ese eer emm eode Ee pg XR ded on de Doa eee oe E at 15 InterExcel400 introduction llle I th 16 Setting up conversion of a spooled file lle n enen 17 1 Work with fonts uus apu ks gx m orci XR an caca a RR CC RC TR e C died 19 2 Work with Cell styles eaii a ach na ut CRI na dnt ar e RR a cR eds ch 21 3 Work with Conversion definitions cler 23 Conditioned lines cts oss eek aoe RR ree E ma a xe RR bee C 2r Define the Layout of cells nnana anaana 30 The INTERXEL WRKOUTQXEL Command eee 33 The INTERXEL WRKSPLFXEL Command celere 34 The Command SPL2XEL Spooled file to Excel Ls 35 Running InterExcel400 with InterForm400 llle 37 Tips and tricks for Excel and InterExcel400 leen 39 Activating the Text import wizard in Excel llle 39 Converting text into numeric fields in excel llle 39 Displaying large texts across cells 0 00000 cee ee ee 40 Using charts with the spooled file data lille 40 License code information llle hn 41 Appendix A Th
Download Pdf Manuals
Related Search