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 :
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 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.
Oracle SQL*Plus: The Definitive Guide (Definitive Guides)
Authors: Jonathan Gennick
Simiral book on Amazon