Microsoft Excel 97 supports several HTML table tag extensions that let you use some of best features in Microsoft Excel when creating HTML tables. These extensions can be built into Web pages, especially those that result from CGI scripts or other server programs, and return data uniquely formatted for use in Microsoft Excel 97 without impacting users who do not have Microsoft Excel. Browsers ignore these tag extensions when they display the HTML tables.
The FORMULA tag extension is an attribute of TH and TD tags. It places a function or formula in a specific cell. This allows the data to be imported into Microsoft Excel in spreadsheet form, which can easily be changed and automatically updated. The syntax of the formula is the Microsoft Excel function syntax, and any Microsoft Excel function or formula can be used. Use A1 style references that refer only to the current table. If you include a FORMULA tag for a table element that has no value assigned to it, the cell imported into Microsoft Excel is empty.
For example, to subtract expenses from sales to get a profit figure, you could create a table with the following HTML tags:
The preceding example produces a table that looks the same in both Microsoft Excel and your browser, though in Microsoft Excel the contents of the Profit cell is actually =A2-B2:
By using formulas, you can change the data that comes from an HTML table, causing it to automatically recalculate results in Microsoft Excel that would be static in a browser.
When using Microsoft Excel built-in functions, be sure to insert an equal sign (=) in front of the function you want to use, as well as after the FORMULA tag, as shown in the following examples:
To use quotation marks within a function, put them in as “"” or the browser will interpret the quote as the end of the formula attribute. For example, if you are using the concatenate function to join two strings, in Microsoft Excel the function would be:
but the formula in HTML would be:
<TD FORMULA="=CONCATENATE("The ","Best")">The Best
Only English language formulas are supported. You cannot create formulas using the syntax or wording of any version of Microsoft Excel other than US English.
Figure 12: Custom number formats in the Format Cells dialog that can be used with the Style attribute.
Microsoft Excel imports numbers using the General format by default. However, you can format HTML numbers with other Microsoft Excel formats using the STYLE tag extension. The syntax for this in a table cell is:
where xxx is the format you want to use. To see sample formats in Microsoft Excel, click Cells on the Format menu, click the Number tab, and then select Custom in the Category list. The sample formats are in the Type list, as shown in Figure 12. For more details about creating your own number formats, see Microsoft Excel online Help.
You can use the STYLE tag extension to solve potential problems with numbers and foreign language versions of Microsoft Excel. For example, if Microsoft Excel receives the numbers “+3/4” or “-3/4” it treats them correctly as numbers. However, if it receives “3/4” (no plus or minus sign), then it treats the number as a date and enters “4-Mar” in the cell because “3/4” is a valid date entry. To prevent this, use the STYLE tag as shown:
As another example, European language versions of Microsoft Excel use a period separator as a date format, so in those languages Microsoft Excel changes “3.4” to a date, such as “3-Apr” in German. To prevent this from happening, use the STYLE tag extension as shown:
To include certain characters in style formatting that also have special meaning in HTML you must use aliases for those characters. The aliases are:
ASCII character HTML alias
For example, when using a multiple custom format in a cell style, Microsoft Excel displays it as:
# ??/??;-# ??/??;#
Because the semicolon would be interpreted in HTML incorrectly, substitute the alias [semicolon] as shown:
<TD STYLE="vnd.ms-excel.numberformat:# ??/??[semicolon]-# ??/??[semicolon]#">
In Microsoft Excel, the preceding example displays as a double-digit fraction for a positive number (# ??/??), a negative sign and a double-digit fraction for a negative number (-# ??/??), or nothing if it’s zero (#).
The FILTER tag extension causes Microsoft Excel to display AutoFilter drop-down buttons on the column headers. Selecting from the drop-down buttons allows the user to filter particular values from the complete table data. The table is displayed in its entirety in the browser.
FILTER tag extension can be used with TD, TH, TABLE, or TR tags. An example of the syntax is:
<TD FILTER=ALL>Store #
. . .
The FILTER tag extension accepts the values ALL, BLANKS, NONBLANKS, or a text string that matches any of the values in the column.
Using any of these values determines what is displayed when the table is first opened in Microsoft Excel, but the user can change the display by using the AutoFilter drop-down buttons on the heading row. For example, in Figure 13, all stores with nonblank dates in all divisions are displayed.
Figure 13: AutoFilter list in Microsoft Excel (Autofilt.htm) showing an HTML table imported with AutoFilter drop-down buttons using the Filter tag extension.
When creating an HTML table, you can use the CROSSTAB tag extension to generate a PivotTable when the data is brought into Microsoft Excel 97. This feature works only when opening the HTML document in Microsoft Excel, not when running a Web Query.
There are two tag extensions associated with the TABLE tag: CROSSTAB and CROSSTABGRAND. In addition, there are six tag extensions associated with the TH and TD tags: ROWFIELD, COLFIELD, DATAFIELD, PAGEFIELD, AGGREGATOR, and SUBTOTAL. The following discussion of these tag extensions refers to the sample file PivotTbl.htm:
<TABLE border CROSSTAB CROSSTABGRAND=ROWCOLUMN>
<TH ROWFIELD>Store #
<TH DATAFIELD AGGREGATOR="SUM">Units
. . .
The preceding example looks like the illustration in Figure 15 when the file is opened in Microsoft Excel.
Figure 14: PivotTable of list in Microsoft Excel (PivotTbl.htm) showing the results of using PivotTable tag extensions. The original table data is hidden; only the resulting PivotTable displayed.
An attribute of the TABLE tag, CROSSTAB instructs Microsoft Excel to display the data as a PivotTable. If this attribute is used, then ROWFIELD, COLFIELD, DATAFIELD, PAGEFIELD, AGGREGATOR, and SUBTOTAL tags must appear in the first row of the table definition as attributes of TH or TD.
This optional attribute CROSSTABGRAND works with CROSSTAB to designate if and how grand totals should be displayed. There are four possible values: NONE, ROW, COLUMN, or ROWCOLUMN (default).
ROWFIELD, COLFIELD, DATAFIELD, and PAGEFIELD
The ROWFIELD, COLFIELD, DATAFIELD, and PAGEFIELD attributes indicate how to treat a column of data in the resulting PivotTable. These attributes must be in the first data row of the table. Any columns without these attributes (PRODUCT and PRICE in the preceding example) are not displayed in the PivotTable. Refer to Figure 14 for the results of using these attributes.
The AGGREGATOR attribute lets you select an aggregator for the DATAFIELD, from the options listed in the following table.
SUM Default for numeric data fields.
COUNT Default for non-numeric data.
AVERAGE Average of the numbers in the data field.
MAX Maximum number in the data field.
MIN Minimum number in the data field.
PRODUCT Product of the numbers in the data field.
COUNTNUMS Number of rows with data.
STDDEV Estimate of the standard deviation of a population, where the underlying data is the sample.
STDDEVP Standard deviation of the population, where the underlying data is the entire population.
VAR Estimate of the variance of the population, where the underlying data is the sample.
VARP Variance of a population of data, where the underlying data is the entire population.
To display multiple aggregators for a single data field, use a comma-separated list as shown:
<TH DATAFIELD AGGREGATOR="SUM,AVERAGE">Sales
The SUBTOTAL attribute controls which row, column, and page field values are subtotaled. It applies to TH and TD tags with ROWFIELD, COLFIELD, and PAGEFIELD attributes. When SUBTOTAL is specified for a row, subtotal values are inserted in the PivotTable for each grouping of the field specified.
The options available for SUBTOTAL are the same as those for the AGGREGATOR attribute. See the table in the previous section for a list of them.
To display multiple subtotal values for a single data field, use a comma-separated list.