An Excel-Specific HTML Hack | Extracting and Loading Data

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

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.