Generating HTML from the Spreadsheet Component
On the surface, generating HTML from the Spreadsheet component that you can return to the client browser seems as easy as writing the HTMLData property, just as we did in Chapter 8. However, it is not quite that easy, and there will be many times that you actually will want to send back more basic HTML so that older browsers can display the results.
In Chapter 8, I used the HTMLData property as a way to get the persistence stream for the Spreadsheet control and write that stream to a database table. Later on, I could retrieve that stream and reload the Spreadsheet control by setting the property. In this case, I would want the Spreadsheet component to write a cell value only once instead of writing both a full precision, unformatted version and the formatted display version. By writing the full precision value only, the text stream is smaller; therefore, it transmits over the network more quickly and takes up less space in the database.
In the Loan Calculation solution, I actually want the display value and not the full precision, unformatted version of the value. Unfortunately, we did not have time in this version of the components to implement two properties, one that returns the full precision values and one that returns the display values, so only the former is available. This makes it easy to save and reload the Spreadsheet component but much harder to render its contents as HTML that a browser can display. We hope to offer both properties in the next version.
However, the code to write a range as an HTML table is included in this solution, and I will walk you through it in this section. You can use this code in your own solutions whenever you need to get an HTML representation of a range in the Spreadsheet control. The method, WriteRange in Default.asp, begins like this:
Sub WriteRange(rngOut, fUseCSS) ' Local variables Dim rngRow ' Current row Dim rngCell ' Current cell Dim rngMerge ' Merged cell range Dim cxTblWidth ' Width of the output table ' Begin by writing the open table tag Response.Write "<table cellspacing=0 cellpadding=2 " ' If using CSS formatting, use a fixed table layout ' and emit the <col> tags. The fixed table layout ' increases perceived rendering speed dramatically since the ' browser doesn't have to parse and calculate the entire ' table before beginning to render it. If fUseCSS Then Response.Write " border=0 " cxTblWidth = 0 For Each rngCell In rngOut.Columns cxTblWidth = cxTblWidth + rngCell.ColumnWidth Next 'rngCell Response.Write " style=""table-layout:fixed;width:" & _ cxTblWidth & """>" For Each rngCell In rngOut.Columns Response.Write "<col width=" & rngCell.ColumnWidth & ">" Next 'rng Else ' Not using CSS, so just use a 1-weight border Response.Write "border=1>" End If Response.Flush |
This method accepts the range to write and a flag indicating whether the function should use CSS attributes for formatting. I start by writing the first part of the <table> tag, specifying a cellspacing of 0 and a cellpadding of 2. (Cellspacing is the number of pixels between cells, and cellpadding is the number of pixels in the margin between a cell wall and its content.) Then I check the fUseCSS flag and write the rest of the <table> tag accordingly.
If the function is asked to use CSS, it performs a few tasks that dramatically speed up rendering in Internet Explorer. First it sets the borders explicitly to 0. Internet Explorer seems to default the borders to 0, while Netscape Navigator seems to default them to 1—when I explicitly set them to 0, the results look identical in both browsers. Next I quickly calculate the total width of all the columns in the output range and write it as the overall table width. I also use the "table-layout:fixed" style setting, which tells the browser that it should use a fixed width for the table, and I write column width values for each column as <col> tags. If the browser knows that the table will be a certain width and that all columns will have a fixed width, it can begin to display the content as soon as it gets the first row. If the table is not fixed, the browser must first parse the entire table and determine how large to make the table based on all the content. Since a spreadsheet has fixed column widths, you can give the browser a hint that the table will be fixed. This will dramatically increase the speed at which the browser loads and displays the results.
If the function is not supposed to use CSS, I instead set the borders explicitly to 1. When formatting without CSS, you cannot control individual cell borders within a table. Instead, you can control only the size of all cell borders for the entire table. Since the mortgage spreadsheet does use cell borders to denote various sections, I chose to turn borders on for the whole table in the non-CSS case so that there is some semblance of cell borders. If you do not like that, you can change this to read "border=0". After writing the starting <table> tag, I call the Flush method on the Response object to send the start of the table to the client browser.
The WriteRange method continues like this:
|
The basic plan for writing a range is to walk row-by-row, cell-by-cell and write <tr> and <td> tags for each row and cell, respectively. This block of code is the start of the outer row loop and uses the For Each syntax over the Range object's Rows collection. For each row, it writes a <tr> tag, formatting it with an explicit row height if fUseCSS is True. I add 2 to the row height here because the output looks a little less cramped with an extra 2 pixels per row.
Within each row, the code continues:
|
I use the For Each syntax again to loop over all cells within the current row. For each cell, I first check whether the cell is merged. (A cell is merged if it is anywhere within a merged cell range.) The owner cell of a merged range is the upper-left cell, and this is the cell that actually defines the value for the merged range. If the current cell is part of a merged area but not the owner cell, I can ignore it because I would have already written a <td> tag with the appropriate rowspan and colspan attributes to cover this current cell. This is one critical difference between the Spreadsheet control and HTML tables. In the Spreadsheet control, the individual cells in a merged range still exist, though they have no value. In HTML tables, there is only one <td> tag for the merged area, and you indicate how big it should be by specifying the rowspan and colspan attributes.
After dealing with the merged cell case, the code continues as follows:
|
This code block is executed when fUseCSS is True, and it writes a rather lengthy style attribute for the <td> tag. This code uses a number of helper functions to translate between Spreadsheet control settings or constants and those defined by the CSS standard. I will not describe all these functions in detail here, but I do want to discuss one that is rather tricky.
The Spreadsheet control natively expresses colors as RGB values, just as most other Windows programs do. RGB stands for Red Green Blue, which describes the physical layout of the various bits in the RGB value (red value at the lowest position, then green value, then blue value). For example, the color pure red has the decimal value 255 and the hexadecimal value 0000FF. The color pure blue has the decimal value 16711680 and the hexadecimal value FF0000. However, HTML colors are laid out in exactly the opposite manner (BGR). The color pure red has the hexadecimal value FF0000, and the color pure blue has the hexadecimal value 0000FF. When writing <td> tag background colors, you must write an HTML color value, so you need to translate between the two. Fortunately, the mapping is fairly easy, and the following function, RGB2HTML, converts between the two:
Function RGB2HTML(rgb) RGB2HTML = Hex(rgb) ' Hex returns a single zero for black ' Change it to six zeros if it is ' just one zero If RGB2HTML = "0" Then RGB2HTML = "#000000" Else ' If not zero, we have to convert between RGB values ' as hexadecimal strings and the way HTML colors are represented ' HTML colors are laid out as BGR instead of RGB, so this ' line flips the first two and last two hexadecimal digits RGB2HTML = "#" & Mid(RGB2HTML, 5) & Mid(RGB2HTML, 3, 2) & _ Left(RGB2HTML, 2) End If End Function 'RGB2HTML() |
This function simply uses the Hex function to convert the RGB value to a hexadecimal string and then uses the Mid and Left functions to swap the first two (red) and last two (blue) characters.
If the method was instructed to not use CSS for formatting, the WriteRange method continues with this block of code:
|
This is similar to the previous code, except that it uses <font>, <b>, and <i> tags to encode the cell formatting information. Note that I also have to translate between explicit font point sizes and the HTML font size value, which is on a scale from 1 to 7. This doesn't guarantee a purely accurate rendering at the client, but it is as close as HTML can get.
The WriteRange method continues by writing the current cell's formatted display text:
|
Note that I write a symbol if the cell's text is blank. If I do not, Internet Explorer will not draw the cell border properly. The symbol is decoded by the browser as a nonbreaking space, so the user sees only a space and not the character string.
The WriteRange method continues by writing the closing </b> and </i> tags if fUseCSS is False and then ends the loops like so:
|
I periodically flush the content I have written back to the client browser. After ending a row, if the row number is evenly divisible by 10 (that is, every 10 rows), I call the Flush method of the Response object. At the end of this method, I write the closing </table> tag and exit the method. The code that calls this method will call the final Flush method to send back the last part of the table.
Another Approach to Generating HTML for a RangeAlthough the HTMLData property does not include the display value in between the starting <td> and ending </td> tags, the Spreadsheet control will include the display value in HTML that it generates when you copy a range of cells to the clipboard. It does this so that a user can copy some cells and paste them into an HTML document as a table, but you can also take advantage of this programmatically. The Range object has a Copy method that copies the range's contents to the clipboard in the CF_HTML clipboard format. Although VBScript does not provide a function to read from the clipboard, you can retrieve the generated HTML from the clipboard with the help of a custom-written COM object that accesses the clipboard for you.
However, the HTML generated by the Spreadsheet control relies heavily on CSS formatting, so you might still want to use something like the WriteRange function I just discussed. When you write the range yourself, you have total control over how the content is generated and can add formatting not present in the spreadsheet, such as alternating background colors in a long table of numbers.
EAN: 2147483647
Pages: 111