SQL*XL online help

The Explicit Column Formatting feature of SQL*XL is the implementation of SQL*Plus like commands with which the resulset data can be explicitly formatted. These commands can be issued in the SQL dialog.

The commands supported in SQL*XL are:

column <column_name> format <format_spec>;
column <column_name> format <cell_reference>;
column <column_name> heading <heading_text>;

where:

column_name :		name of any table column or column alias
format_spec :		Excel format string (e.g. "0000" or "###,##0.00")
cell_reference :		reference to a single Excel cell. e.g. A5, Sheet2!B6, [Book2]Sheet3!D1
heading_text :		text for use as column header

To clarify the use of the above commands please consider the following examples.

Example 1:

column empno format 0000;

Whenever a query is executed in which the column name empno is used (like select * from emp) the data in the emp column will be formatted as 4 digits using leading zeroes.

Example 2:

column test format "£ ###,##0.00";
select 1 test from dual;
select 1000 test from dual;
select 12345.67 test from dual;

Output of these command (executed in the SQL dialog) is as follows:

£ 1.00
£ 1,000.00
£ 12,345.67

Example 3:

column empno heading "Employee Number";

This command will apply the string Employee Number as column header whenever a query is executed that involves the column empno.

There is one important difference with the SQL*Plus command column <column_name> format <format_spec> when it is used in
SQL*XL or in SQL*Plus itself. It is the format . In SQL*Plus you use the Oracle format specifiers whereas in SQL*XL you use the Excel format specifiers. In the list below a number of the formatting specifiers that can be used in SQL*XL are shown:

format specifier	data	formatted data
##.##	1.2	1.2
00.00	1.2	01.20
a 00.00 b	1.2	a 01.20 b

There are more format options possible. Refer to the Excel help file (number formats) to find out about the other formats.

In order to make life easy a bit SQL*XL supports an alternative syntax to the column ... format ... command:

column <column_name> format <cell_reference>

Examples:

column empno format D10;
column deptno format Sheet3!B12;
column sal format [book1]Sheet2!C4;

Using this syntax one can format an example cell. SQL*XL will automatically only copy the cell's formatting and not copy the sample data entered.

See also:

• Non SQL commands
• SQL dialog
• Features
• Dialogs
• Menu and Toolbar Items
• SQL*XL Home