XLPARSE2 CHANGE LOG 2023-08-23 - Pgm XLSCONVERT is now created with USRPRF(*OWNER). 2023-04-26 - Fixed CL module XXXDFTCCSI . 2023-03-07 - Checks have been added to make sure that the utility runs with a supported job CCSID. 2022-11-25 - Small bug (if command DLTSPLF fails, ...) fixed in pgm XLSTABLE2. 2022-10-01 - Now saved for V7R1 instead of V7R2. 2022-09-14 - In command XLSTABLE, the selected value of parameter DATEFMT provides now the correct format for the generated database date fields. - In command XLSCONVERT, parameter DATEFMT is no longer provided. The format of all date fields is now *ISO. 2022-09-12 - Command XLSTABLE is now supporting a maximum length of 20,000 characters for spreasheet text cells. 2022-09-08 - Just updated stream file /xlparse2/pdf/XLPARSE2_Developer_Guide.pdf . 2022-09-05 - In commands XLSCONVERT and XLSTABLE new parameter FRCNUMCOLS allows to identify spreadsheet columns where text-data cells must be interpreted as number-data cells. Such spreadsheet columns would then result to numeric fields in the generated database file record format. 2022-06-28 - Updated db file JAVATABLE to support V7R5. 2022-06-11 - JDK14 and JDK15 java products no longer allowed for V6R1. - Fixed the way numeric spreadsheet cells data are converted to text field data, when a spreadsheet column contains both text and numeric cells. dd22-04-14 - It has been found that Scott Klement's XLPARSER4 service program used in XLPARSE2 can retrieve just the first 256 columns (cell) of a row, the following cells are ignored. 2021-11-19 - In commands XLSCONVERT and XLSTABLE, when SBMJOB(*YES) specified, new parameter JOBQ may be used to assign the job queue for the synchronized submitted job. - In command XLSCONVERT fixed problem with SHEETNBR(*ALL). 2021-07-21 - Fixed handling of XLSTABLE parameter SHEETNAME when SBMJOB(*YES). 2021-07-17 - Fixed documentation about paramerter SHEETNAME in commands XLSCONVERT and XLSTABLE. 2021-06-10 - In pgm XLSTABLE2 fixed subroutine FixNumToChar. It was cutting off decimal digits of pseudo numeric data. 2021-06-01 - Java products Java Developer Kit 1.4 (option 5106) and Java Developer Kit 1.5 (option 5107) marked as non-POI compatible on V6R1. 2021-04-06 - Excel spreasheets with CCSID 1208 (UTF-8) are no longer rejected. 2021-03-14 - Updated WEB page https://www.easy400.net:1220/xlparse2/html/javatable.html for V7R2. - Fixed pgm JAVASET to properly handle the Java Product selected by the user (via command XLPARSE2/JAVASEL -> file XLPARSE2DT/JAVATABLES). 2021-02-16 - Pgm XLSTABLE2: fixed a security problem in module XXXMBRRECS. 2021-02-13 - Pgm XLSTABLE2: added some controls to check the number of records loaded by command CPYFRMIMPF. 2021-01-15 - Updated delimiters in command XLSTABLE. 2020-12-29 - Program DOCELLNAME provides an example about creating an Excel cell name from its row and column numbers. - Added a "Question and Answers" page to the online manual. 2020-10-23 - Fixed error from command XLSTABLE when submitting a synchronized job. 2020-08-01 - The Change Log can now be displayed as an HTML page from the online manual. 2020-04-24 - In pgm TABLEXLS2 fixed the case when a cell is found as numeric but its column is then set to character. 2020-04-23 - Fixed default value of parameter SHEETNAME in command XLSTABLE. It was *SHEET instead of *SHEETNBR . 2020-04-06 - (HSSFCGI) In module XLPARSERJP, procedure xlparsejp_workbook() has been added two more optional procedure pointer parameters, so that calling program may also receive control on start and end of each spreadsheet row. 2020-03-24 - (HSSFCGI) Fixed an error in module XLPARSERJP of procedure xlparsejp_workbook(). 2020-03-10 - Added parameter SHEETNAME to commands XLSCONVERT and XLSTABLE. This parameter, if srvpgm HSSFCGI/XLPARSERJP available, allows to parse an Excel spreadsheet even when service program XLPARSE2/XLPARSER4 fails with message "no significant cells detected". Note that this SHEETNAME feature requires service program HSSFCGI/XLPARSERJP. In other words, you must have also a rather recent version of library HSSFCGI. 2019-12-23 - Major redesign: commandS XLSTABLE and XLSTABLE2 are now using the same program, the XLSTABLE2 program. The XLSTABLE2 program executes without needing the RPG compiler, provides better performance and more flexibility. XLSTABLE2 program is now supporting also date and time fields. 2019-12-17 - New XLSTABLE and XLSTABLE2 parameters RCDFMT and RCDFLDNMS provide entries for user-specified record format name and user-specified field names. 2019-12-13 - When in commands XLSTABLE and XLSTABLE2 -- the columns of the last spreasheet heading row contain field names, and -- parameter FLDNAMES is set to *COLHDG, the record format fields of the generated physical file are assigned those names. 2019-11-18 - Updated file JAVATABLE for V7R3 and V7R4. 2019-08-30 - Increased performance of XLSTABLE2 for multisheet workbooks. Just one worksheet is processed, the same as in XLSTABLE. 2019-06-17 - Install procedure: directory /xlparse2/java removed before been restored. 2019-04-10 - New command JVMSTARTUP allows jobs to execute also other Java applications besides XLPARSE2. See page http://www.easy400.net/xlparse2/jvmstartup.htm . 2018-11-09 - Maximum length of retrieved character cell data raised from 500 to 20 thousands. 2018-09-18 - In pgm XLSCONVERT improved the control on the CCSID of the input Excel stream file. 2018-08-13 - Greatly simplified the set up of the environment for the Java Virtual Machine. - Fixed the XLSTABLE/2 case where a numeric column contains some blank entry (because of zero suppression); that column was erroneously interpreted as a character column. 2018-07-12 - XLSTABLE was working only for the first spreadsheet of a workbook. Now can regularly process any spreadsheet of a workbook. 2018-07-11 - No-operated references to options of product 5770JV1 which were detected as not compatible with HSSFCGI and XLPARSE2. 2018-06-26 - Some new features for CGI program UPLANDRUN (Submit a XLSTABLE or XLSTABLE2 job from a WEB browser): -- Parameters TIMECOL and TIMESEP (see 2017-07-27) are now supported -- The WEB user is now enabled to inquiry about the status of the submitted XLSTABLE/XLSTABLE2 batch job and to display its joblog if something goes wrong. 2018-03-26 - Monitor added to command XLSCONVERT in programs XLSTABLE and XLSTABLE2. 2018-01-04 - XLSTABLE2 non longer needs special authority *ALLOBJ for the job user profile. 2017-09-10 - Updated pgm XLSCONVERT to accept also spreadsheets in ASCI CCSID 850 and 858. 2017-09-10 - Commands XLSCONVERT, XLSTABLE and XLSTABLE2 are now featuring a new parameter, SBMJOB. This parameter allows these commands to be executed in synchronized batch jobs, so that the JVM started by these commands does not conflict with any JVM in the current job. 2017-09-09 - Improved the ability of a CL program, executing command XLSTABLE, to monitor for escape messages returned from this command. See CL test programs MONITORCL and ESCAPETEST in XLPARSE2/QCLSRC. 2017-07-27 - Commands XLSCONVERT, XLSTABLE and XLSTABLE2 feature two new parameters: -- TIMECOL, to declare the spreadsheets columns containing time cells -- TIMESEP, to declare the time separator to be used. 2017-06-09 - XLSCONVERT- Extended the acceptable range of Excel file CCSIDs. 2017-05-22 - In XLSTABLE2 fixed a problem when the appropriate "decimal point" is a comma. 2017-03-20 - V7R3 enabled. 2017-01-01 - Program XLSCONVERT now checks spreadsheet for ASCII CCSID. 2016-12-16 - Fixed XLPARSER4 procedure "hssf_xls2timestamp()" (Convert an Excel timestamp to ISO format) 2016-12-05 - Fixed Java set up in module XLBPROC of service program XLPARSER4. 2016-10-12 - Trailing print control characters (LineFeed, NewLine, CarriageReturn) are now removed from values of character cells (modules: XLSCONV_2, XLSCONV_6). 2016-04-28 - Pgm XLSCONVERT added some code to handle some formulas referring to cells in other sheets. - Pgm XLSCONVERT will now totally ignore cells not belonging to the selected spreadsheet. 2016-04-16 - Pgm XLSTABLE2: fixed the case where a numeric cell value should be transferred to a d.b. character field. 2016-03-26 - Pgm XLSTABLE: updated the way a numeric cell value is reformatted to fit into a character field. 2016-03-26 - Changed my mind on XLPARSE2 activity groups. --XLPARSER4, XLSCONVERT, XLSTABLE, XLSTABLE2 and GETCELLS must all be in the same activity group (XLPARSE2) --The other programs may be in ACTGRP(*CALLER). The above works both in the interactive mode and in the CGI environment. 2016-02-17 - Use command XLAPSE2/COMPILE XLSACTGRP(XLPARSE2) to generate all programs and service programs with activity group XLPARSE2 instead of *CALLER . 2016-02-25 - Got rid of ACTGRP(XLPARSE2). All pgms and srvpgm now in ACTGRP(*CALLER). 2016-02-17 - In command XLSCONVERT, new parameter SHEETNBR allows to select the workbook spreadsheet to be converted. - Fixed sample pgm GETCELLS. 2015-12-21 - Fixed pgm XLSTABLE2: it was converting all spreadsheets in a workbook, not just the selected one. 2015-11-09 - Provided suggestions for proper use of DATECOL parameter in commands XLSCONVERT, XLSTABLE and XLSTABLE2. 2015-08-12 - In pgm XLSCONVERT, set more tight controls on JDK prerequisites. - Added command DSPENVVAR. 2015-07-02 - Added parameter RPLNULLVAL(*FLDDFT) to command CPYFRMIMPF in program XLSTABLE2. 2015-05-22 - Solved a problem where XLSCONVERT was converting no cells at all. The solution was to have pgm XLSCONVERT and srvpgm XLPARSER4 in the same NAMED activation group. 2015-05-19 - My way to assign environment variable JAVA_HOME. 2015-05-15 - Module XLBPROC gave up assigning environment variable JAVA_HOME. Will take what is available. 2015-05-11 - In pgm XLSTABLES, fixed incorrect round-up of last decimal digit in numeric fields. 2015-03-26 - Commands XLSTABLE and XLSTABLE2, and CGI program UPDANDRUN are now suggesting to use file QDDSSRC in library XLPARSE2DT, thus avoiding authority concerns. Note - Library QSYSINC is not needed to install and run this utility. 2014-11-04 - Fixed a problem with XLSTABLE2 in handling null values. 2014-11-03 - A new version of command XLSTABLE has been made available and carries the name XLSTABLE2. XLSTABLE2 can be run in absence of the ILE RPG compiler and provides more flexibility on the record format fields of the physical file receiving data from the EXCEL spreadsheet. For more information see page http://www.easy400.net/xlparse2/html/page4.htm . 2014-10-14 - A free trial version (file "xlpr6try.zip") has been made available for V6R1 and subsequent releases. A free trial version (file "xlprstry.zip") has been made available for V5R3 only. These versions are "object code only" and can be used for 30 days. 2014-10-01 - Module /XLBPROC: updated the way to set environment variable JAVA_HOME. 2014-06-24 - Command XLSTABLE now enabled to process - besides stream files - also distributed stream files. 2014-06-06 - In d.b. files XLSOUTF and XLSWRKF the maximum length for all the fields in a row was extended from 10 to 20 k-byte. 2014-03-07 - XLSTABLE used to allow enlargments (via DDS) of character fields in the target physical database file, so to convert spreadsheets containing larger data by specifying CRTFILE(*NO) on the XLSTABLE command (see section 3 at page http://www.easy400.net:1220/xlparse2/html/page3.htm ). In order to properly work, however, this required command XLSTABLE CRTFILE(*NO) be run from a new job. This requirement has now been dropped, one can re-run XLSTABLE CRTFILE(*NO) without changing job. 2014-01-01 - Fixed pgm XLSTABLE. It must now be created with activation group *CALLER instead of *NEW. - Fixed CGI program UPLANDRUN. Now it always submits a batch job to run command XLSTABLE. 2013-12-28 - Updated srvpgm HSSFCGIX for V7R1 5761JV1 product options. Fully tested on V5R3 and V6R1. 2013-04-05 - Activation groups of service programs XLPARSER4 and GPPARSER4 set to *caller. 2013-03-30 - Fixed an OVRPRTF command in pgm XLSTABLE. 2013-03-02 - Fixed program UPLANDRUN. - Replaced service program GPSRVPGM2 with latest version of CGIDEV2/CGISRVPGM2. 2013-01-08 - Command XLSTABLE may create a physical file from DDS and a printout is made available just in case errors occur. This spool file is now held, to prevent from being printed on a local user printer. 2012-09-18 - More XLSTABLE support for numeric cells conversion to character when column headers are not skipped ( HLINES(0) ). 2012-09-12 - Fixed bug in pgm XLSTABLE- A wrong CRTPF command was being executed. - Fixed bug in pgm XLSTABLE- The following was occurring in columns marked as character-type: a numeric cell with no decimal digits was left-aligned instead of right, minus sign was also lost. 2012-09-10 - Supporting more data per spreadsheet row (see physical files XLSOUTF and XLSWRKF). 2012-08-30 - Updated pgm.s XLSCONV_2 and XLSCONV_6 to support parsing of extremely large spreadsheets. 2012-07-14 - Library CGIDEV2 is no longer a prerequisite for executing CGI program UPLANDRUN. 2012-06-23 - In module GPRANDOM, call to API CEERAN0 was replaced by a call to API QC3GenPRNs (the same as it was done on module CGIDEV2/XXXRANDOM on April 18, 2012) 2012-06-21 - Fixed module XLBPROC (compile error) of srvpgm XLPARSE2/XLPARSE_4. 2012-06-07 - Added control on authorization to command DSPSFWRSC. 2012-05-16 - Functional improvements to CGI program UPLANDRUN. See page http://www.easy400.net/xlparse2/html/page4.htm . 2012-05-15 - Improved *ESCAPE message processing in commands XLSCONVERT and XLSTABLE. - Added sample CGI program UPLANDRUN (Upload a spreadsheet from PC and run XLSTABLE for it). 2012-03-22 - More diagnostic messages if pgm errors during cell processing. 2012-03-19 - In command XLSTABLE, parameter DATECOL now defaults to *NONE instead of *AUTO. - In program XLSTABLE, fixed local subprocedure SqueezeDta. 2012-03-02 - Special authority *JOBCTL no longer needed to run command XLSCONVERT. 2012-01-12 - Added parameter MBROPT(*ADD/*REPLACE) to command XLSTABLE. 2011-06-15 - In command XLSCONVERT changed default DATECOL(*AUTO) to DATECOL(*NONE) . 2011-01-24 - Changed the way Java is started for V6R1 and V7R1. See subproc SetJava15 in mbr XLBPROC . 2010-12-05 - Spreadsheet cells containing a date and a non-zero time value are now converted to a char 26 field containing a timestamp-formatted value. 2010-10-21 - Fixed some wrong /copy statements in QRPGLESRC1 members XLPDEMO and XLSCONV_2 . 2010-10-15 - XLPARSE2 can now run from V5R3 up to V6R1 and (hope) over. 2010-10-11 - Numeric input fiels can now be received into database character fields. 2010-10-09 - The XLSTABLE target database file can now be a multimember file, and a target member can be specified. 2010-04-15 - Added controls on receiving database fields. The length of a receiving database character field can now be longer than the size of the corresponding spreadsheet column. 2010-03-22 - Command ADDENVVAR requires the user profile to have *JOBCTL special authority. Without this special authority, the ADDENVVAR commands issued from pgm XLSCONVERT are not executed. This makes XLSCONVERT to fail. Added a monitor routine in XLSCONVERT to check current user profile special authorities. 2010-03-19 - Program XLSTABLE. Changed the way the decimal dot is replaced by a decimal comma. - Program XLSCONVERT. Made some aggiustements on the "FormulaCell" internal subprocedure. - Fixed parameter JAVAVER in commands XLSCONVERT and XLSTABLE. 2010-03-18 - Announcing XLPARSE2 as successor of XLPARSE