Oracle SQL*Plus: The Definitive Guide, 2nd Edition

An Excel-Specific HTML Hack

If you're extracting data for someone to load into Microsoft Excel, you can apply an interesting technique whereby you take advantage of Excel's ability to read from an HTML table. The gist of this technique is as follows:

1. Enable HTML output using the SET MARKUP command.

2. Spool the output to a file with the extension .xls. It doesn't matter that the file contains HTML markup.

3. Double-click the file. This launches Excel because of the .xls extension, and Excel in turn converts the HTML table into a spreadsheet.

Example 9-10 presents a simple script to implement this technique. The output file is current_employees.xls. Double-clicking that file on a Windows system with Microsoft Excel installed produces the spreadsheet that you see in Figure 9-2. You don't need to adjust column widths. Cell titles are bold and nicely centered.

Example 9-10. Generating an HTML table that can be loaded into Microsoft Excel simply by double-clicking on the resulting file

SET MARKUP HTML ON
SET TERMOUT OFF
SET FEEDBACK OFF
SPOOL current_employees.xls
SELECT employee_id,
       employee_billing_rate
       employee_hire_date,
       employee_name
FROM employee
WHERE employee_termination_date IS NULL;
SPOOL OFF
EXIT

Figure 9-2. An HTML table converted by Microsoft Excel into a spreadsheet

If you're generating pages for a web site, you can serve up a file such as that generated by Example 9-10, and Microsoft ...