On Thu, 23 Oct 2003 13:17:46 -0600, Julie Miles <jm****@unibase .com> wrote:
[snip]
Simplest way is probably as follows. Insert new columns around the
existing columns: the first containing <TR><TD>, the last with
</TD></TR> and all the others with </TD><TD>. Obviously the Excel "fill
down" function is very handy. Save the file as text. That gets most of
the work done.
That's an ingenious suggestion! I think it will work for most of them -
there are some that have more complex structures (merged cells), but it
certainly gets rid of the majority of the grunt work. Thanks!
Julie
Several months ago I wrote an Excel function for this purpose: It can
handle merged cells and it reads the names(!) of Excel styles (not the
style definitions) and translates them into CSS class attributes.
To make it work, paste the following code into an Excel module (Fire the
VBA editor with Alt + F11, find your workook in one of the left windows,
right-click on it, then "Insert - Module" , doubleclick the newly created
module and paste the code).
If the first line of your Excel table stretches from A1 to D1, then in E1
you could use the formula =row2html(A1:D1 )
Copy the formula down to the last cell.
Cheers,
Joerg
Here comes the Excel Stuff:
Function row2html(DataRa nge As Object)
'Purpose: To mark-up a horizontal cell range as HTML <tr><td> elements
'How it works: Each cell contents enclosed with <td>..</td> tags. The
whole stuff with <tr>..</tr> tags.
'Special functions: Reads style names and converts them to class
attributes, translates merged cells into colspans.
'Applies to: Horizontal cell ranges
On Error Resume Next
Dim CellText As String
Application.Vol atile
txt = "<tr>"
'If DataRange.Merge Cells = True Then MsgBox "contains merged cells"
Colspan = 0
For Each cell In DataRange
ColSpanAttribut e = ""
If cell.MergeArea. Address <> cell.Address And ColSpanProcesse d =
True Then
'Do nothing if cell is part of a merged area and TD with
COLSPAN attribute has already been defined
'Ignore cell until last cell reached, then reset
ColSpanProcesse d (another merged area may follow and has to be processed
with a clean slate)
Colspan = Colspan - 1
If Colspan = 1 Then ColSpanProcesse d = False
Else
'Define COLSPAN if cell is part of merged area
If cell.MergeArea. Address <> cell.Address And ColSpanProcesse d
= False Then
Colspan = cell.MergeArea. Cells.Count
ColSpanAttribut e = " colspan=""" & Colspan & """"
ColSpanProcesse d = True
End If
CellText = cell.Value
'Add CSS class names, if cell contains named style
If cell.Style.Name = "Normal" Then
txt = txt & "<td" & ColSpanAttribut e & ">" & CellText &
"</td>"
Else
txt = txt & "<td class=""" & cell.Style.Name & """" &
ColSpanAttribut e & ">" & CellText & "</td>"
End If
End If
Next cell
txt = txt & "</tr>"
row2html = txt
End Function