Oracle SQLPlus [Electronic resources] : The Definitive Guide, 2nd Edition نسخه متنی

9.4 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:

Enable HTML output using the SET MARKUP command.

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

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

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 Internet Explorer will
recognize it as an Excel file. Users will see a spreadsheet in their
browser and can save the file to their local disk using File Save As. They may never
even realize they aren't dealing with a true, native
Excel
file.