Syntax:
GATEWAY EXPORT XLSW 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 +
|SHEET_INDEX value +
|SHEET_NAME value +
|EXTRA_SHEETS value +
|ACTIVE_INDEX value +
|RAW_GEN value +
|STYLE value +
|DETECT_FORMULA value +
|SHOW_PROGRESS value +
|MESSAGES value +
|ACTION value +
|OVERWRITE_SHEET 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 Workbook format. The file extension must be xls or xlsx.
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
ACTIVE_INDEX
Defines the active sheet when the worksheet is opened in Excel. The value is an integer-based index (e.g. 1, 2, etc.).
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
EXTRA_SHEETS
Specifies whether blank sheets (Sheet2, Sheet3) are automatically included in the exported workbook. 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.
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
OVERWRITE_SHEET
Specifies an existing sheet with the same name will be overwritten with the new dataset. The default is set to OFF. Available Parameters: ON / OFF
PASSWORD
Specifies the password for the Excel file. MS Excel is required in order to use the PASSWORD parameter. 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.
RAW_GEN
Specifies the program to generate the workbook. When ON, R:BASE will generate the workbook, which performs the export process much faster. When OFF, Excel will generate the workbook as native MS output. When R:BASE creates the output, only the most important content is placed in the file, where the workbook is intended to mimic Excel output. The R:BASE output can be considered non-native, yet less bloated. If the PASSWORD parameter is specified, Excel is always used to generate the workbook, regardless of the RAW_GEN value. The default is set to OFF. Available Parameters: ON / OFF
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.
SHEET_INDEX
Specifies the workbook sheet/tab to place the exported data. The index value begins with 1. It is recommended to use 1 based incrementing values for multiple exports to separate sheets of a workbook. If the desired output is to display the sheet captions as: [May][June][July], the code should be written as:
GATEWAY ... SHEET_NAME May|SHEET_INDEX 1
GATEWAY ... SHEET_NAME June|SHEET_INDEX 2
GATEWAY ... SHEET_NAME July|SHEET_INDEX 3
SHEET_NAME
Assigns a custom text label to the workbook sheet. The specified name cannot exceed 31 characters, have the same name as another sheet, set as a blank value, or contain special characters (backward slash, forward slash, question mark, asterisk, left bracket, right bracket, colon).
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 XLSW D:\DataStore\Employee.XLS +
SELECT * FROM Employee WHERE EmpStatus = 'Active' +
OPTION COL_NAMES ON +
|TAB_NAME EMPLOYEES +
|HEADER_TEXT 'Employee Information' +
|HEADER_TEXT 'September' +
|FOOTER_TEXT ' ' +
|FOOTER_TEXT 'Prepared by Jane Sullivan' +
|STYLE ZEBRA +
|SHEET_NAME Employee Info +
|SHEET_INDEX 2 +
|ACTIVE_INDEX 1 +
|SHOW_PROGRESS ON +
|MERGE_DATA ON +
|RAW_GEN ON +
|ACTION OPENVIEW
GATEWAY EXPORT XLSW 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 XLSW D:\DataStore\Contacts.XLSX +
SELECT * FROM Contacts +
OPTION BLANK_IF_ZERO ON|MESSAGES OFF
GATEWAY EXPORT XLSW DataCalculation.XLSX +
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 XLSW BonusRate2018.xlsx +
SELECT * FROM BonusRate OPTION PASSWORD ApplePie
Notes:
•Use this option to export data to a MS Excel workbook. The process uses the MS Excel application as OLE server unless the RAW_GEN parameter is set to ON, or the PASSWORD parameter is specified. If the process uses a password, MS Excel is required.
•When providing a file name to export to, you will be required to specify the complete path.
