Using HTML in Excel

It is a little known fact that HTML can be read directly by Microsoft Excel. This is a useful technique for using textual output of a program to be read both by a universal front end (i.e. a browser) and by Microsoft Excel for further processing. Such a technique can be used to great advantage in applications such as PeopleSoft as output in HTML can be written by SQR.

Excel can understand only a limited number of tags, but this does not limit its usefulness. For a file to be processed by Excel, the file would mainly consist of tables, and this is the HTML that Excel does best. In addition to the standard HTML <TABLE> <TR> <TD> style tags, Excel actually has its own attributes to allow the insertion of formulae, pivottables and filters. This is where this technique really comes alive! Imagine creating a report that allows items to be filtered and totals to be recalculated. The results of a query are very often read into a spreadsheet, various formatting and formulae applied, before presentation. This technique allows for the formatting of the spreadsheet to be created automatically, saving the time used in creating this each time.

As a kind of working example, the following link is to an HTML page that describes some of the attributes Excel understands. Try reading it in your browser, with Excel and then having a peak at the source.

Excel tag table with examples

A mock report - try altering the projections in Excel.

Hints and Tips

There are some "gotchas" that are worth knowing about in this process (these are Excel 97):

  • If you place a FILTER on one column of a table, then that filter will be displayed on all columns in that table.
  • Formulae row numbers count from zero rather than from one (as in the spreadsheet). When the formulae are displayed in the spreadsheet they will appear to have one added to their row numbers (C1 in HTML becomes C2 in the spreadsheet).
  • In cells where you place formulae, you must supply a "placeholder" value, otherwise the cell appears blank in the speadsheet.
  • If browsers as well as spreadsheets are in use to make the formula "placeholder" value the result of the calculations when the report is created. See the mock report - above.
  • If your formulae need double quotes, you need to use the HTML &quot;

Computing Articles