Explicit Column Formatting |
|
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: