Thursday, March 31, 2011
Creating Multi-Tab Excel Workbook with Headers Using GATEWAY EXPORT XLSW
Section: Un-Documented Features
Version: R:BASE eXtreme 9.1 (32/64)
Build..: 9.1.2.10214 and higher
Here's an example to create multi-tab Excel Workbook with Headers using
GATEWAY EXPORT XLSW options,
-- GATEWAY_Multi_Tab_EXPORT_XLS_XLSW.RMD
-- Author: A. Razzak Memon
-- Date Created: March 31, 2011
-- Last Updated:
IF (CVAL('DATABASE')) <> 'RRBYW17' OR (CVAL('DATABASE')) IS NULL THEN
CONNECT RRBYW17 IDENTIFIED BY NONE
ENDIF
CLEAR VARIABLES vCaption,vPause3Message,vPause4Message,vCheckExtension
SET VAR vCaption TEXT = 'Multi-Tab XLS/XLSX Workbook'
SET VARIABLE vPause3Message TEXT = NULL
SET VARIABLE vPause4Message TEXT = NULL
SET VARIABLE vCheckExtension TEXT = NULL
SET VARIABLE vFileName TEXT = NULL
SET VAR vPause3Message = +
((CHAR(013))+' Now Preparing Multi-Tab XLS/XLSX Workbook')
CLS
PAUSE 3 USING .vPause3Message +
CAPTION .vCaption +
ICON INFO +
OPTION MESSAGE_FONT_NAME Tahoma +
|MESSAGE_FONT_COLOR GREEN +
|MESSAGE_FONT_SIZE 11 +
|THEMENAME Vista CG
-- Delete previously created RRBYW17_Multi_Tab_WorkBook.xl??, if any
SET ERROR MESSAGE 2077 OFF
DELETE RRBYW17_Multi_Tab_WorkBook.xl??
SET ERROR MESSAGE 2077 ON
-- Define file name with appropriate file extension (.xls or .xlsx)
-- .xlsx for MS Office Excel 2007 or higher
PLUGIN RRegistry 'vCheckExtension|CHECK_KEY|HKCR|.xlsx|'
IF vCheckExtension = 'TRUE' THEN
SET VARIABLE vFileName = 'RRBYW17_Multi_Tab_WorkBook.xlsx'
ELSE
SET VARIABLE vFileName = 'RRBYW17_Multi_Tab_WorkBook.xls'
ENDIF
-- Create Tab 1 (Customers)
SET VAR vPause4Message = +
((CHAR(013))+' Now Adding Tab 1 - Customers')
PAUSE 4 USING .vPause4Message +
CAPTION .vCaption +
ICON INFO +
OPTION MESSAGE_FONT_NAME Tahoma +
|MESSAGE_FONT_COLOR GREEN +
|MESSAGE_FONT_SIZE 11 +
|THEMENAME Vista CG
GATEWAY EXPORT XLSW .vFileName +
SELECT CustID AS `Cust ID`, +
Company AS `Company Name`, +
CustAddress AS `Address`, +
(CustCity+','&CustState&CustZip) AS `City, State and Zip Code`, +
CustPhone AS `Phone Number` +
FROM Customer ORDER BY Company +
OPTION COL_NAMES ON +
|HEADER_TEXT Customers +
|SHEET_NAME Customers|SHEET_INDEX 1
-- Create Tab 2 (Invoice Headers)
SET VAR vPause4Message = +
((CHAR(013))+' Now Adding Tab 2 - Invoice Headers')
PAUSE 4 USING .vPause4Message +
CAPTION .vCaption +
ICON INFO +
OPTION MESSAGE_FONT_NAME Tahoma +
|MESSAGE_FONT_COLOR GREEN +
|MESSAGE_FONT_SIZE 11 +
|THEMENAME Vista CG
GATEWAY EXPORT XLSW .vFileName +
SELECT TransID AS `Invoice No`, +
CustID AS `Cust ID`, +
TransDate AS `Invoice Date`, +
NetAmount AS `Sub Total`, +
Freight, Tax, +
InvoiceTotal AS `Invoice Total` +
FROM InvoiceHeader ORDER BY TransID +
OPTION COL_NAMES ON +
|HEADER_TEXT Invoices +
|SHEET_NAME Invoices|SHEET_INDEX 2 +
|ACTION OPENVIEW
CLEAR VARIABLES vCaption,vPause%,vCheckExtension,vFileName
CLS
RETURN
Note:
. Notice the use of PLUGIN RRegistry Command to customize and automate
the file name extension (.xls or .xlsx).
. You can customize the column names with spaces by surrounding the names
with IDQUOTE character.
. Once the worksheet is opened, notice the header information in Row 1,
Cell 1 (A1), and the footer information in the last row, Cell 1.
. You may also notice the "Number Format" for Invoice No, Invoice Date,
Net Amount, Freight, Tax, and Invoice Total. All data types, such as
INTEGER (Number), DATE, and CURRENCY are now reflected accordingly
and not formatted as custom.
Have fun integrating these options in your R:BASE eXtreme 9.1 applications!
Very Best R:egards,
Razzak.
