Linker IT Software
Order Home
menubar-top-links menubar-top-rechts

SQL*XL: Database to Excel bridge

Related LIT software: litLIB: Excel power functions pack encOffice: Protect your Excel file easy and safe

Home Products SQL*XL Help Features

Buy now

Download now

SQL*XL online help

 

SQL*XL: Explicit Column Formatting

Explicit Column Formatting

SQL*XL
home

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:

SQL*XL ribbon in Excel