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.
Row(5) has nine different fonts, at assorted alignments, from left to right:
- Courier New
- Time New Roman
- Comic Sans MS
- Trebuchet MS
- Arial Black
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.
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:
Looks like this underneath:
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.