HSSFCGI CHANGE LOG 2012/05/02 - In commands XLSGEN and XLSXGEN maximum number of styles raised from 545 to 10051. 2012/04/30 - In commands XLSGEN and XLSXGEN fixed a bug when attempting to generate more cell styles than supported. 2012/03/20 - In command TABLEXLS, when parameter SHEETNAME contains a given name, the sheet names in the workbook used to be added a sequence number. Now, this sequence number (1) is dropped for the first sheet name. 2012/03/09 - In command TABLEXLS, parameter SHEETNAME extended from 8 to 30 characters. 2012/01/15 - Removed the 2011/12/15 restriction "TABLEXLS SBMJOB(*YES) not supported if IGNFLDS or LINKFLDS". 2011/12/16 - Ad-hoc generated programs now can just read (no longer update) database files. That is required by SQL views. 2011/12/15 - TABLEXLS SBMJOB(*YES) not supported if IGNFLDS or LINKFLDS. - Removed "ovrdbf" and "dltovr" commands. 2011/12/05 - Parameter DEC-POS supported also for quick formulas HSUM, VSUM, HAVG and VAVG. 2011/12/02 - In TABLEXLS fixed the editing of some numeric fields with decimal positions. 2011/12/01 - Provided parameter DEC-POS for formulas. Example: < data type="formula" dec-pos="3" > . 2011/11/30 - Fixed the case of multiple consecutive %LF's. 2011/11/29 - Improved number of decimal digits and cell width for < data type="formula" ... > . 2011/11/25 - Provided support for field data type "G" (UCS-2 graphic fields). 2011/11/24 - Files HSSFCGIDTA/ADHOCCTL1 and HSSFCGIDTA/FLDLSTCTL now opened with RCDFMTLCK((... *EXCL)) WAITFILE(32767). 2011/11/21 - Added controls on user profile authorization to access files specified in parameter FROMFILE of command TABLEXLS. 2011/11/19 - Fixed command TABLEXLS (some parameters were no longer visible). - Provided some *PRV messages for TABLEXLS SBMJOB(*YES). 2011/11/19 - Provided an extra page for further ALTCOLHDG documentation. - Provided raise up of XLSGEN/XLSXGEN messages at top of program stack when pgm TABLEXLS in execution. 2011/11/08 - New parameter ALTCOLHDG has been added to command TABLEXLS. In this parameter you may mention a stream file containing an XLSGEN script that provides your own worksheet column headings for the first database file mentioned in parameter FROMFILE. See page http://www.easy400.net/hssfcgi/html/page4.htm . 2011/11/05 - Some fixes in recovery code. 2011/10/27 - Full recovery feature now available with parameter RECOVERY in commands XLSGEN, XLSXGEN and TABLEXLS. Especially useful when facing JVM memory problems during generation of large worksheets. See page http://www.easy400.net/hssfcgi/html/page2.htm#largewks . 2011/10/12 - Added code to XLSGEN and XLSXGEN to provide for saving the generated worksheets before a failure occurs. This allows for partial results in case of memory problems. - Provided command DSPERRS for quick diagnosis after XLSGEN/XLSXGEN failure. 2011/10/10 - XLS (MS Office 2003) and XLSX (MS Office 2010) worksheets have some limits (number of rows, number of cells). See: - http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP005199291.aspx - http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP010342495.aspx Whenever program XLSGEN or XLSXGEN reaches one of those limits, it ends the current worksheet and starts a new one. 1992910/05 - When a formula is not accepted by POI, srvpgm HSSFR4 receives an exception and POI is no longer available until the program returns with *LR *on. In such situations, it was very difficult for the user to know the name of the cell containing the wrong formula which raised the POI exception. Some special messages have been added to commands XLSGEN and XLSXGEN, so that the user now can know the name of the cell and the wrong formula which stopped the process. 2011/09/29 - Fixed a bug in pgm TABLEXLS (executing command XLSGEN). 2011/09/23 - Fixed a bug by which VSPAN/HSPAN-ed cells "booked" status was incorrectly cleared, thus not displaying the cell in a row following the last VSPAN/HSPAN-ed cell. 2011/08/30 - A new parameter ("anchor") has been added to the data type PICTURE. It establishes the behavior of a picture when - the cell is resized - a new column or a new row is added to the worksheet For more information, see page http://www.easy400.net/hssfcgi/html/page2.htm on topic "Picture resizing and relocation". 2011/08/12 - In XLSGEN, date cells containing a date before January 1 1900, are converted to Excel text cells instead of being converted to Excel date cells. This is due to the fact that in Excel dates are represented as the number of days since January 1, 1900 and date cells created with a lower value display value ########## . 2011/07/16 - Commands XLSGEN, XLSXGEN and TABLEXLS have been added parameter LOG. This parameter allows to generate a log file in library QTEMP, where all the generated cells are logged. If the process abnormally terminates, by displaying this file one can easily tell what was the last cell successfully generated. - VSUM, HSUM, VAVG and HAVG formulas can now be requested on previous formula cells. - In XLSGEN language, tag < cell > features a new keyword, COLUMN-MAXWIDTH="number_of_pixel". It may be used to limit the width of a spreadsheet column when the data of its cells cause an excessive column width. - When an XLSX spreadsheet is generated, controls are now active to limit to 30 chars the spreadsheet names. 2011/07/11 - Added parameter SHEETNAME to command TABLEXLS. 2011/07/05 - Fixed bug in XLSGEN_COM/XLSXGEN local subprocedure SaveColWidth. 2011/06/28 - Fixed error in XLSGEN/XLSXGEN: when XLSGEN < data > starting by "%LF ", local subprocedure SaveColWidth would fail after being called from local subprocedure SetLineFeeds. 2011/06/10 - Implemented new features to control cell borders: 1-Commands XLSGEN, XLSXGEN and TABLEXLS are now supporting new parameter CELLBORDER which allows to generate cell borders for all cells, or just for cells displaying a foreground or a background color different from the default values. For more information, see page http://www.easy400.net/hssfcgi/html/page2.htm . 2-In the XLSGEN language, the cell tag now supports the keyword BORDER < CELL BORDER > This is the way to add borders to selected cells. For more information, see page http://www.easy400.net/hssfcgi/html/page4.htm . 2011/05/04 - In command TABLEXLS, parameter LNKFLDS (hyperlink fields) now supports single value *SELECT. This allows the user to select from a list the field pairs making up hyperlinks. - In command TABLEXLS, parameter IGNFLDS (fields to be omitted) now supports single value *SELECT. This allows the user to select from a list the field names to be omitted in the spreadsheet. 2011/05/01 - Some new keywords added to XLSGEN language tag < WORKSHEET >. These keyword are PRINT-HEADER, PRINT-ORIENTATION and PRINT-SCALE. See page http://www.easy400.net/hssfcgi/html/page2.htm . The old XLSGEN tag < HEADER > is still supported but is now deprecated. 2011/04/29 - All spreadsheet cells are now given thin borders (they show up also on printouts). - Changed XLSGEN/XLSXGEN checks on Java configuration. 2011/04/20 - Added command WEBXLSGEN, see page http://www.easy400.net/hssfcgi/html/page3.htm . 2011/04/18 - New parameter ADDTO(*YES) in commands TABLEXLS, XLSGEN and XLSXGEN allows to add new worksheets to an existing workbook. 2011/04/15 - Added support for database null capable fields. 2011/04/07 - Added subprocedure "Get_HSSFCGI_Java()" to service program HSSFCGIX. This subprocedure allows to retrieve the Java environment variables needed by commands XLSGEN, XLSXGEN and TABLEXLS. This allows the Java environment variables to be setup by a program before that a JVM is started. See page http://www.easy400.net/hssfcgi/html/page3.htm#3 . 2011/04/06 - A new parameter SBMJOB has been added to commands XLSGEN, XLSXGEN and TABLEXLS. When SBMJOB(*YES), the entire Java process is executed in a submitted batch job. This allows to avoid conflicts with existing JVM settings in the current job. 2011/03/31 - Added keyword PAGE-BREAK to tag ROW. It causes a print page break before this row. 2011/03/08 - Fixed an error in XLSGEN_COM and in XLSXGEN. When more than 32767 cells, formula-cells would bump out the program. 2011/03/01 - Fixed an error in the HTTP directives ( < LocationMatch ... > directive ). 2011/02/25 - Qualified database file names not supported by the RPG compiler. Error message from TABLEXLS. 2011/02/21 - TABLEXLS and the CGI pgm WEBTABLEXL are now supporting multiple database files. 2011/02/20 - TABLEXLS and the CGI pgm WEBTABLEXL are now supporting multimember database files. - Parameter HEADER in command XLSGEN/XLSXGEN now allows to define a print header. 2011/02/18 - Hyperlink cells can now be generated also for spreadsheets built from command TABLEXLS and from the WEB-CGI program WEBTABLEXL. 2011/02/16 - XLSGEN control statement < header >...< /header > can now be used to generate print header and footer. 2011/02/15 - Frozen rows are now repeated on all print pages. 2011/02/14 - Added HYPERLYNK support in Formula. - Refined the computation of column widths. - Fixed the case where < /worksheet > is omitted. 2011/02/12 - Added keywords UNDERLINE and ITALIC for the XML-like < data ... > statement. 2011/02/10 - Added keyword CELL-WIDTH for the XML-like < cell ... > statement. It allows to size a cell width to a given number of pixels. Example: < cell cell-width="200" > . Maximum cell width is 889 pixel. 2011/01/24 - Changed the way Java is started for V6R1 and V7R1. See subproc SetJava15 in mbr HSSFCGIX . 2010/12/06 - A POI procedure "freeze pane" has been used to keep steady the spreadsheet initial rows. This is very useful when scrolling spreahsheets with a large number of rows. This feature is handled via parameter FROZEN in commands XLSGEN, XLSXGEN and TABLEXLS. CGI program WEBTABLEXL now invokes command TABLEXLS with parameter FROZEN(1). 2010/11/28 - In the previous releases of HSSFCGI, a cell spanning over multiple rows used to force the next row after the last row spanned. That did not allow to have other cells - at the right and left side of the spanning cell - except for the first spanned row. In today release you can have a cell spanning over multiple rows AND surrounded by other cells in all the spanned rows. This can be done by specifying OVERLAY next to the keyword VSPAN. See http://www.easy400.net/hssfcgi/html/page2.htm#figure5 . See also the sample XLSGEN script in stream file /hssfcgi/tmp/zzz.xml . 2010/11/27 - Fixed the error where a cell text was displayed in bold characters when the cell text was containing the word " bold". 2010/11/09 - Programs XLSGEN and XLSXGEN were missing the appropriate style for numeric cell with 6 decimal positions. 2010/10/18 - Added the following types for right alignement: TEXTR, LARGETEXTR, SMALLTEXTR. Added keyword BOLD. 2010/10/16 - If during XLSGEN/XLSXGEN process library HSSFCGI is added to the library list, it is then removed from the library list as soon as it is no longer needed. 2010/10/07 - Fixed a problem that was not allowing XLSGEN to work with OS/400 V5R4. 2010/10/06 - Fixed the problem with more than 32767 cells. - Changed the way Java products level is retrieved. 2010/10/04 - Fixed management of large and small text fonts in XLSGEN/XLSXGEN. 2010/10/02 - If OS release >= V4R4, and Java 1.5 available, Java 1.5 (i/of Java 1.4) is now used to run command XLSGEN (Java 1.4 no longer needed if Java 1.5 available). In this case, the XLSGEN based on HSSFR_2 is run. 2010/10/01 - In commands XLSGEN, XLSXGEN, TABLEXLS and CGI program WEBTABLEXL, parameter DFTCELLCOL can be used to specify the cell default font and background color. Besides, THE XLSGEN language is now supporting keywords (f-color and b-color) to define font and background colors at cell level. 2010/09/20 - In commands XLSGEN, XLSXGEN, TABLEXLS and CGI program WEBTABLEXL, parameter DECPOS(*NOC) can now be used to suppress thousand delimiters in spreadsheet numeric cells. 2010/09/16 - Enabled CGI program WEBTABLEXL to support background colors in the column headers of the generated spreadsheet. 2010/09/15 - Commands XLSGEN, XLSXGEN and TABLEXLS have been added a new parameter, HDGCOLOR, which can be used to set the background color of the column heading cells of the generated spreadsheet. 2010/09/10 - In the XML-like XLSGEN input stream file numeric values can now have been edited with edit code J, L, N or P. 2010/09/09 - Fixed bug in XLSGEN and XLSGEN: they used to assign one extra decimal position to negative numbers with at least one decimal position. 2010/09/06 - In programs XLSGEN and XLSXGEN removed the limit of 16Mb size for the input XLM stream file. 2010/07/28 - Added parameter IGNFLDS (fields to be ignored) to command TABLEXLS. 2010/07/23 - Improved documentation about DATE fields. See page http://www.easy400.net/hssfcgi/html/page4.htm#dateflds 2010/07/22 - You may now specify TABLEXLS TOXLS(*NONE). This can be useful to review the generated XML-like stream file before processing it with command XLSGEN or XLSXGEN. - Some changes in date formats for spreadsheet date cells: FORMAT PREVIOUS EDITING CURRENT EDITING MDY mm/dd/yyyy mm/dd/yy DMY dd/mm/yyyy dd/mm/yy DMYY dd/mm/yyyy YMD yyyy/mm/dd yy/mm/dd YYMD yyyy/mm/dd ISO yyyy-mm-dd yyyy-mm-dd USA mm/dd/yyyy mm/dd/yyyy EUR dd.mm.yyyy dd.mm.yyyy JIS yyyy-mm-dd yyyy-mm-dd JUL yyyy-ddd 2010/07/21 - Added parameter CVTDATE to commands XLSGEN, XLSXGEN and TABLEXLS. This parameter may be used to assign the same format to all the date cells in the spreadsheet. - Support for database date fields (data-type L, several date formats) added to command TABLEXLS. 2010/07/16 - Representation of signed and packed fields with a value lower than zero was wrong; now fixed. 2010/07/14 - Added directives for HTTP instance HSSFCGI. 2010/07/13 - Parameter DECPOS(*AUTO) in commands TABLEXLS, XLSGEN and XLSXGEN will display the original decimal positions in all numeric spreadsheet cells. - In module HSSFXGIX, subprocedure RtvJavaLvl, fixed a bug due to the fact that starting with V7R1, the IBM Java directory is no longer '/QIBM/ProdData/Java400', but is then '/QOpenSys/QIBM/ProdData/JavaVM'. 2010/07/12 - Added support for XLSX Excel spreadsheets (Windows 2007, POI 3.6). - Fixed odd characters in numeric fields (due to CCSID 65535). 2010/07/07 - Parameter DECPOS(*AUTO) supported for command TABLEXLS. - Fixed HTTP directive. - Fixed the case where a character field contains character "<" or character ">". 2010/07/06 - Parameter DECPOS (Decimal positions) added to commands XLSGEN and TABLEXLS. 2010/07/05 - New command TABLEXLS allows to generate XLS Excel spreadsheets from any database file. 2010/06/15 - Diagnostic messages now issued whenever a VSUM/HSUM/VAVG/HAVG formula is missing operators. 2010/06/14 - Supported any valid EXCEL formulas. Besides VSUM an HSUM, two more commands have been added for generating formulas: VAVG and HAVG. - Family-Accounting (FAMACC) CGI sample program has been added. 2010/05/11 - Fix to pgm XLSGEN. It was not closing the .xls stream file after saving the workbook to it. As a result the .xls file could not be copied. Suggested by Richard Davenport, Dallas, TX. 2010/01/26 - POI 3.2 Java classes are back. 2010/01/05 - POI 3.2 Java classes replaced by POI 3.6 ones. 2009/10/13 - Provided support for characters '<' and '>' within a text cell, by using the corresponding HTML entities. 2009/04/21 - First release