Turning an Excel table into CSS

This is my very ugly, very formatted, table to test my translation skills, first into HTML and now into CSS. Every cell has at least two formats that have a counterpart in CSS, and the table runs through all options at some point.

Ugly Table

Row(5) has nine different fonts, at assorted alignments, from left to right:

  • Courier New
  • Time New Roman
  • Verdana
  • Comic Sans MS
  • Georgia
  • Tahoma
  • Trebuchet MS
  • Arial Black
  • Impact

I think Microsoft Office puts those fonts on every machine. A1:H5 are the colors of the old/original color pallate. This is my CSS rendering of the double-ugly table.

  A B C D E F G H I
1 1 2 3 4 5 6 7 8 9
2 2 4 6 8 10 12 14 16 18
3 3 #N/A 9 12 15 18 21 24 27
4 4 8.00E+00 12 16 20 24 28.000 32 36
5 The quick brown fox jumps over the lazy dog!
6 6 12 18 24 30 36 42 48  
7 7 14 21 28 35 42 49 56 63
8 8 16 24 32 40 48 56 64 72
9 9 18 27 36 45 54 63 72 81
10 10 20 ⅞ 30 40 50 60 70 80 90

Tricks were required to capture conditional formatting, which is not contained in a cell’s styling. From here, Chip Pearson showed me the way. The mental picture I formed was of conditional formatting floating in a horizontal plane above the spreadsheet, hiding the “regular” formatting below it. Reverse engineering the way cell formats interact with conditional formats was not fun, but the result should be near to what you see in your spreadsheet. Several cells in Row(7) are conditionally formatted. Browsers can screw this up. The bottom border of E10 is a dotted red line. It’s set as xlHairline in the spreadsheet. The HTML properly specifies it as a dotted red line, and outside of WordPress, that’s what I see. Just not here. Similarly, I’ve specified the column widths and their max-widths. WordPress doesn’t seem to care, and spreads the table 100%. My impression is that inline styles win, but not always on WordPress, I guess.

The graphic is about 70KB and the table about 57KB. While the graphic will be always be around 70KB, the table will shrink as formatting is lessened. For the 120 cells above, which includes the heading cells, that’s about 486 bytes per cell. To speed the concatenation up, I implemented (the late) Nate Oliver’s buffer concept, with a buffer for 106 characters, or room for over 2,000 excessively formatted cells.

The MakeCSSTable() procedure looks at the selected cells from left to right, top to bottom, and aligns each cell’s attributes with CSS. Except for the optional case of not capturing the headers, only the bottom and right borders are captured (the left and top being captured from a previous cell). When no-headers are chosen, all four borders are captured for the top row and left column. Someone please speakup if it’s documented what the layout or z-order for Excel’s cells is, and how their borders overlay. I think I got close to right, but since the browsers may do it differently, I’m not sure. If you change your gridline settings, MakeCSSTable() follows along.

My initial approach gave a CSS style for each attribute on each edge. As I got smarter, I grouped the the attributes into shortcuts. Not every Excel attribute has a CSS counterpart. For instance, diagonal borders do not exist in CSS, CSS only offers one kind of underlining, CSS3 adds an outline font but browsers don’t handle it yet (see Cell(D6)—it’s in the macro though for when they do), and Excel has more dashed/dotted line styles than CSS. Predominately dashed styles align to a “dashed” CSS style, with a similar arrangement for dotted lines. Things I stumbled upon in doing this: Column widths are a pixel measurement (based on the Normal-style font size) and row heights are a point measurement. For small Excel font sizes (<11) I specified a CSS use of a value of “small” in one line’s code. Since this is outside what Excel calculated, it can throw the column widths off. You can see the difference in the G5 cells.

This little table:

  A B
17 1 2000
18 3000 4
19 5 38 ⅞

Looks like this underneath:

  A B
17 1 2000
18 3000 4
19 5 38 ⅞

The CSS_Tablemaker module is 700+ lines of code, including the whitespace, so it’s available here as a spreadsheet file. It comes complete with the MakeCSSTable(), AddToBuffer(), MakeFracs(), and HexColor() routines, and as much of Chip’s code as I used. The absolutely stunning tables above are included, no charge. The header details are module constants you can adjust. The output is dumped to the clipboard to paste where you want.

… mrt

4 thoughts on “Turning an Excel table into CSS

  1. Very impressive….but given the fact that Excel’s Publish to Web does all of this, what’s the point ?

  2. M-

    Thanks, I guess. Excel’s Publish to Web doesn’t do all this. If you dump the spreadsheet as a webpage, there are several differences. Here’s the ones I saw:

    1. No Header row and column option. The webpage doesn’t bother.
    2. Likewise no gridline representation. The macro follows your setting.
    3. No Shadow font style. See cells E6 and C10 above.
    4. The double line-style looks like (but it’s not) thick.
    5. No hairline or thick. Only thin and medium.
    6. No weighting to the dashed lines.
    7. No regard to superscript font settings.
    8. Doesn’t capture empty cells, ie. Row(11)
    9. Doesn’t evaluate fractions.

    There’s probably more, but those are the ones that jumped at me. Excel creates a class for each cell when they’re different, and I create an inline style for each.

    My goal was a tool that moves a table (usually as a representation of a spreadsheet) to the web as you see it. Excel’s classes are more than generic CSS. Here’s an example from the dump.

    	mso-pattern:black none;}

    … mrt

  3. Sorry about that Michael…you didn’t mention that !
    I had always looked at the voluminous CSS code that MSFT was cutting and thought to myself: “wow, it handles every condition”.
    Was that assessment ever so wrong !
    Very impressive work…was this tested with 2007 or 2010 ?

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.