The LNCTOXLS command is designed to transfer data from the AS/400 into an Excel sheet.
LNCTOXLS command requires the following parameters:
Parameters
|
Workbook used as template |
TOXLS |
|
|
Workbook Folder/Directory |
XLSPTH |
*DFT |
|
Data source type |
CPYSRC |
*FILE, *QRY, *SQL, *NONE |
|
File name |
FROMFILE |
|
|
Library |
|
Nom, *LIBL, *CURLIB |
|
File member |
FROMMBR |
Nom, *FIRST |
|
Columns selection |
COLUMNS |
*ALL |
|
|
|
|
|
Data types conversions |
TYPECAST |
*NONE |
|
Name of Excel sheet |
TOSHEET |
*ONLY |
|
Replace/Add/Insert rows |
TONAME |
*NONE |
|
|
XLSOPT |
*REPLACE, *ADD, *INSERT |
|
Output type |
OUTPUT |
|
|
Saved file name |
SAVDOC |
*NONE |
|
|
|
|
|
|
|
|
|
Afficher le document |
SHOWDOC |
*YES, *NO |
|
LAUNCHER server name |
EXESRV |
*DFT |
|
Ending options |
ENDOPT |
*ALL, *APP, *DOC, *CON, *NONE |
|
Ajuster largeur des colonnes |
AUTOFIT |
*YES, *NO |
|
Appliquer Format aux cellules |
FMTCELLS |
*YES, *NO |
|
Insérer entêtes de colonnes |
ADDCOLH |
*NONE, *COLHDG, *NAME, ALIAS |
|
Appliquer Format automatique |
AUTOFMT |
*NONE |
Details
Excel workbook name (TOXLS)
Specifies the excel workbook name to use as a model.
This parameter is mandatory.
Possible values are:
name-of-Excel-workbook
Designates an Excel workbook name.
*CURRENT
The command will use the last workbook opened by LAUNCHER Office.
*NEW
The command will use a new empty workbook.
Workbook access path (XLSPTH)
Speficies the acces path for opening the Excel workbook.
Possible values are:
*DFT
The special value *DFT means that the default LAUNCHER will be used for this parameter.
To display or change the default value, select the XLSPTH keyword (WRKLNCDFT command).
*NONE
Means that the full path is given in the TOXLS parameter.
Path
The full access path to find the workbook.
Copy Datasource (CPYSRC)
Specifies the type of the data source for the mail merge.
The possible values are:
*FILE
The data to transfer into the workbook are in database file (LF or PF).
*QRY
The data to transfer into the workbook is a query (object type *QRYDFN).
*SQL
The data to transfer into the workbook comes from a SQL request.
Database File (FROMFILE)
Specifies the name of the file to use to get the data.
This parameter is used when parameter CPYSRC is set to *FILE.
The possible values for library are :
*LIBL
The library list is searched to find the library where the file is located.
*CURLIB
Library-name
Enter the name of the library that contains the file.
File member (FROMMBR)
Enter the name of the member that contains the data.
This parameter is used when parameter MRGTYPE is set to *FILE.
Possible values are :
*FIRST
The first member of the database file contains the data.
Member-name
Enter the name of the member that contains the data.
SQL request (FROMSQL)
Enter the SQL request to retrieve the records from the AS/400 database.
This parameter is used when parameter MRGTYPE is set to *SQL.
Query (FROMQRY)
Specifies the name of the query to use to get the data.
This parameter is used when parameter MRGTYPE is set to *QRY.
Query-name
Enter the name of the query.
The possible library values are :
*LIBL
The library list is searched to find the library where the query file is located.
Library-name
Enter the name of the library that contains the query file.
Name of the Excel sheet (TOSHEET)
Specifies the name of the Excel sheet used in this command.
The possible values are:
*ONLY
The *ONLY special value means that the command will use the first available
and active sheet of the Excel workbook.
Remarque : Prefer this option when using a one-sheet Excel workbook.
*AUTOCRT
The *AUTOCRT special value means that the command will insert a new sheet
into the workbook and transfer the data into it.
Excel-sheet-name
Specifies a named destination sheet.
Name of excel result (TONAME)
Specifies the name of the new range created by the command.
A name can be a standard string or a range with the following form:
$Columnletter$linenumber
Example : TONAME($D$3) designates the cell of column 4, line 3.
Add / Replace or Insert rows (XLSOPT)
Specifies how to add the records in the Excel workbook.
Possible values are :
*REPLACE
Replace all records with the new ones.
*ADD
Simply add the records into the sheet.
*INSERT
Rows are inserted to receive the new data from the data source.
Parameter ENTIRER indicates if entire rows are inserted or not.
*RESIZE
The new data replaces the old data.
The area in the workbook is resized.
Lines will be deleted or inserted.
*MAP
Column values in the data source are assigned to cells
with the same names in the Excel table.
Only the first recording of the data source is used.
Output device (OUTPUT)
Specifies to view or print the result.
Possible values are:
*
Shows the result.
Prints the result into the default printer of the workstation.
Saved file name (SAVDOC)
Specifies the name of the final saved workbook.
Notice : If there is no access path, the file will be saved into the default saving directory.
Possible values are:
*NONE
The result will not be saved.
Excel-filename
The result will be saved into a specified name.
Workbook saving path (SAVFLR)
Speficies the saving folder for the final result.
Possible values are:
*DFT
The default value from the file LNCDFTP, for the keyword XLSSAV will be used.
Folder_name
The path to the folder or directory is specified.
Notice : The character '\' must not be typed at the end of the string.
*NONE
Specifies that the parameter SAVDOC contains the directory path.
Saved file format (SAVFMT)
Specifies in the saving format.
Show document (SHOWDOC)
Specifies to show the result or not.
Possible values are:
*NO
The result is not shown.
*YES
The result is shown.
Launcher server to use (EXESRV)
Specifies the name or IP address of the PC to use to build the document.
Possible values are :
*DFT
The value from the keyword XLSSRV in file LNCDFTP is used.
*DEV
The data transfer is performed on the PC where the terminal for the current job is located.
*CURRENT
When *CURRENT is specified, the current job must be already connected to a LAUNCHER Server on a PC.
That connection will be used.
IP address or name
The data transfer is performed on the specified PC.
Notice : The LAUNCHER server application must be running on the PC.
Ending Option (ENDOPT)
Specifies what object needs to be closed at the end of the process.
Possible values are :
*ALL
The following elements are closed: Excel workbook, Excel application,
LAUNCHER Connection between the job and the PC.
The next LNCTOXLS command will not be able to use the value *CURRENT
for any parameter.
*APP
The following elements are closed: Excel workbook, Excel application.
The LAUNCHER Connection between the job and the PC is still active.
The next LNCTOXLS command will be able to use the value
*CURRENT for the parameter EXESRV.
*DOC
The following elements are closed: Excel workbook.
The Excel application, and the LAUNCHER Connection are still active.
The next LNCTOXLS command will be able to use the value *CURRENT for the parameter EXESRV.
*CON
Only the LAUNCHER Connection between the job and the PC is closed.
Excel and the document remain in memory.
*NONE
The connection, Excel, and the document are left active.
The next LNCTOXLS command will be able to use the value *CURRENT for the parameters EXESRV and DOC.
Adjust the columns width (AUTOFIT)
Adjust the column width with the size of the contents.
Possible values are:
*YES
Adjust the column width with the maximum size of values.
*NO
Do not change the column width.
Apply an automatic format (AUTOFMT)
Set the automatic format to apply to the destination area.
Possible values are:
*NONE
No automatic format is applied.
Name
The specified format is applied.
Read the Excel technical guides to know the existing automatic formats.
Set the cells format (FMTCELLS)
Indicates if the format of the cell is to be set depending on the column data type of the database.
Possible values are :
*YES
The format of the cell is set depending on the database data types.
*NO
The format is set by Excel depending on the type of value.
Insert column headings (ADDCOLH)
Insert the column headings or field names in the first row.
Possible values are:
*NONE
No column heading inserted.
*NAME
The field names are inserted as the first row.
*COLHDG
The column heading from the database are inserted.
*ALIAS
The aliases (long names) are inserted.
Insert entire rows (ENTIRER)
If XLSOPT is set to value *INSERT, this option indicates if entire rows are inserted or not.
Possible values are :
*YES
Entire rows are inserted to receive the new data.
*NO
The cells behind the range selected (parameter TONAME) are moved dows.
Example
LNCTOXLS TOXLS(MODELE.XLSX) +
XLSPTH('%LNCDIR%\SAMPLES') CPYSRC(*SQL) +
FROMSQL('SELECT * FROM SP_ORD') +
TOSHEET(FEUIL1) +
TONAME($A$2) XLSOPT(*ADD) +
SAVDOC(R_RESULTATS) +
SAVFLR('%LNCDIR%\SAMPLES') ENDOPT(*DOC) +
AUTOFIT(*YES)