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




SPOOL current_employees.xls

SELECT employee_id,




FROM employee

WHERE employee_termination_date IS NULL;




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.


Introduction to SQL*Plus

Command-Line SQL*Plus

Browser-Based SQL*Plus

A Lightning SQL Tutorial

Generating Reports with SQL*Plus

Creating HTML Reports

Advanced Reports

Writing SQL*Plus Scripts

Extracting and Loading Data

Exploring Your Database

Advanced Scripting

Tuning and Timing

The Product User Profile

Customizing Your SQL*Plus Environment

Appendix A. SQL*Plus Command Reference

Appendix B. SQL*Plus Format Elements

Oracle SQL Plus The Definitive Guide, 2nd Edition
Oracle SQL*Plus: The Definitive Guide (Definitive Guides)
ISBN: 0596007469
EAN: 2147483647
Year: N/A
Pages: 151 © 2008-2020.
If you may any questions please contact us: