Syntax:
GATEWAY EXPORT XLS filename.xls +
SELECT clause +
OPTION COL_NAMES value +
|TAB_NAME value +
|NAMED_NUM value1 value2 +
|NAMED_TEXT value1 value2 +
|HEADER_TEXT value +
|FOOTER_TEXT value +
|LAYOUT value +
|STYLE value +
|DETECT_FORMULA value +
|SHOW_PROGRESS value +
|MESSAGES value +
|ACTION value +
|PASSWORD value +
|PASSWORD_TO_OPEN value +
|BLANK_IF_NULL value +
|BLANK_IF_ZERO value +
|NAMED_NUM value +
|NAMED_TEXT value +
|WRAP_TEXT value +
|MERGE_DATA value +
|ROWS_PER_FILE value +
|SPECIFICATION_FILE_NAME filename +
|DATASET_FIELD_ORDER value +
|NO_UNICODE_CONVERSION value +
|ADD_UTF8_BOM value
filename.xls
This parameter defines the file name of the target file with exported data using the MS Excel Spreadsheet format. The file extension must be xls.
SELECT clause
Use the SELECT command to identify rows of data from a table or view.
OPTION
ACTION
Defines the default action which will be processed after export is complete, for example, to open the target export file in view/edit mode or send this exported file by email as attachment. Available Parameter: OPENVIEW
ADD_UTF8_BOM
Specifies whether to add a UTF8 BOM to an exported UTF8 string. Available Parameters: ON / OFF
BLANK_IF_NULL
If ON, will export NULL values as blank values. If OFF, will export NULL values as the database NULL setting. The default is set to OFF. Available Parameters: ON / OFF
BLANK_IF_ZERO
If ON, will export zero values as blank values. If OFF, will export zero values as zero. The default is set to OFF. Available Parameters: ON / OFF
COL_NAMES
If ON, will add a first row with column names. If OFF, will only export data. Available Parameters: ON / OFF
DATASET_FIELD_ORDER
Specifies the export will follow the SELECT clause column order, rather than the field order in the specification file, when a specification file is used as an OPTION parameter. The default is set to OFF. Available Parameters: ON / OFF
DETECT_FORMULA
Specifies that when equal characters exist in table values, which are not actual formulas, formula detection is disabled. The default is set to ON. Available Parameters: ON / OFF
FOOTER_TEXT
The parameter specifies text that will appear as a footer at the bottom of the exported data. Multiple footers can be specified by repeating the parameter. An approximate 250 character limit exists for a spreadsheet footer. If the specified footer exceeds the limit, R:BASE will determine the best length that Excel can accept and truncate the data.
HEADER_TEXT
The parameter specifies text that will appear as a header on top of the exported data. Multiple headers can be specified by repeating the parameter. An approximate 250 character limit exists for a spreadsheet header. If the specified header exceeds the limit, R:BASE will determine the best length that Excel can accept and truncate the data.
LAYOUT
Specifies the layout in which the exported data is arranged. The columnar option arranges columns across the top. Reversed columnar arranges columns along the left side. The tabular option arranges data that looks like a table. Available Parameters: COLUMNAR / REVERSED_COLUMNAR / TABULAR
MESSAGES
If ON, during export process some messages will be displayed such as -ERROR- messages, if any. If OFF, the entire export process will be in "silent" mode. Available Parameters: ON / OFF
MERGE_DATA
If ON, the exported data will be added to the specified file, if exists. The default is set to OFF. Available Parameters: ON / OFF
NAMED_NUM
Specifies to pass a number formula to the Excel Names Manager. A formula name and value is passed with the parameter. The parameter can be repeated if multiple number are to be added.
NAMED_TEXT
Specifies to pass a text formula to the Excel Names Manager. A formula name and value is passed with the parameter. The parameter can be repeated if multiple number are to be added.
NO_UNICODE_CONVERSION
Exports Unicode data as a UTF8 string to fit in the target field, ensuring no data is lost. If OFF, the Unicode data will be converted to ANSI and characters from source that is higher than 0x127 will become ‘?’. As not all target formats support Unicode and this field is for cases when source is Unicode and destination is normal text field. Available Parameters: ON / OFF
PASSWORD
Specifies the password for the Excel file. Passwords are case sensitive.
PASSWORD_TO_OPEN
Specifies the password for an existing password protected spreadsheet file, to allow appending to the file with the MERGE_DATA option. Passwords are case sensitive.
ROWS_PER_FILE
Specifies if the records are broken up into separate files based on the quantity of records in each file. If this parameter is not zero, multiple files may be generated.
SHOW_PROGRESS
Indicates whether to display status dialog during export process. Set SHOW_PROGRESS to ON if you wish to show an animated status dialog during the export process. Set SHOW_PROGRESS to OFF if you wish to export data without animated status dialog. Available Parameters: ON / OFF
SPECIFICATION_FILE_NAME
This parameter is used to load a prepared specification with all pre-defined settings for exporting. The export must be performed once using the graphic user interface "Export Wizard" and saved using the "Specifications.." button. The specifications will be stored in a file with the .RGW file extension. The .RGW file acts as a set of default values. Any value in the .RGW file will be overwritten by the same parameter specifically assigned by an OPTION parameter. Since the input file name is required in the GATEWAY command, the input file name in the .RGW will always be overwritten.
STYLE
This option adds background color to exported data.
Available Parameters:
NORMAL
PRICELIST
MSMONEY
BRICK
DESERT
EGGPLANT
LILAC
MAPLE
MARINE
ROSE
SPRUCE
WHEAT
ZEBRA
TAB_NAME
If defined, will be used as the label for the first tab on MS Excel Spreadsheet. The specified name cannot exceed 31 characters, have the same name as another tab, set as a blank value, or contain special characters (backward slash, forward slash, question mark, asterisk, left bracket, right bracket, colon).
WRAP_TEXT
Specifies to wrap long text values within the output of the spreadsheet.
Examples:
GATEWAY EXPORT XLS D:\DataStore\Employee.XLS +
SELECT * FROM Employee WHERE EmpStatus = 'Active' +
OPTION COL_NAMES ON +
|HEADER_TEXT 'Employee Information' +
|HEADER_TEXT 'September' +
|FOOTER_TEXT ' ' +
|FOOTER_TEXT 'Prepared by Jane Sullivan' +
|SHOW_PROGRESS ON +
|ACTION OPENVIEW
GATEWAY EXPORT XLS Customers_1203.XLS +
SELECT +
Company AS `Company Name`, +
CustID AS `Customer ID`, +
CustAddress AS `Company Address`, +
(CustCity+','&CustState&CustZip) AS `City, State, and Zip Code` +
FROM Customer ORDER by Company +
OPTION COL_NAMES ON +
|TAB_NAME Customer List
GATEWAY EXPORT XLS D:\DataStore\Contacts.XLS +
SELECT * FROM Contacts +
OPTION BLANK_IF_ZERO ON|MESSAGES OFF|STYLE SPRUCE
GATEWAY EXPORT XLS DataCalculation.XLS +
SELECT * FROM DiscountRates +
OPTION COL_NAMES ON +
|TAB_NAME SalesDiscounts +
|NAMED_NUM MinDiscount 0.01 +
|NAMED_NUM MaxDiscount 0.05 +
|NAMED_TEXT DiscountSuffix Disc
GATEWAY EXPORT XLS BonusRate2018.xls +
SELECT * FROM BonusRate OPTION PASSWORD ApplePie
GATEWAY EXPORT XLS StaffList.xls +
SELECT DepartmentID,HireDate,LastName FROM Staff +
ORDER BY DepartmentID ASC, LastName ASC +
OPTION TAB_NAME Staff +
|SPECIFICATION_FILE_NAME StaffExp.rgw +
|DATASET_FIELD_ORDER ON
Note:
Use this option to export data to a MS Excel spreadsheet. The process does not use the MS Excel application as OLE server unless the password option is specified. If the process uses a password, MS Excel is required.
