Can anyone advise what's the best way to go about my scenario here.

I basically have a database (an excel spreadsheet) and want to save as HTML (for putting on a web site) but one of the columns has 'Conditional Formatting'.

I do know that with HTML being a markup language only [not a programming language] that it can't handle this, but on the other hand I don't really want to learn javascript or php as I don't think I'll really need to (then again who knows?).

The conditional format only applies to one column and it changes the text colour depending whether the text is over 20 characters in length.

Can anyone help?

For such a simple scenario.... one column with a simple text color change attribute, I would look into php or javascript for just that one section. I used to have a website where I used simple javascript for putting the date on the bottom of the page and simple image changes on mouseover. Alas, I don't remember much from those quick forays into Javascript- but it won't be too involved of a process for you.

How about using Excel (if possible, or load with Open-Office) to save the spreadsheet as HTML?
--- rod.

I tried that already, usin both Excel and OOo and it doesn't work

I have used OO to do what you are asking on several occasions, and it worked fine for me. Specifically, what aspect of the procedure did not work? Perhaps there is a simple second step that can be used to patch your output. Is this a 'dynamic' document, in the sense that the content is changing based on run-time parameters, or does the document get served as a static page? If the latter, how often does it need to be updated? It might be simple enough to fix it with a text editor, if the required changes are simple and/or infrequent.
--- rod.

The Conditional Formatting carries over but it doesn't appear as a table.



I got the spreadsheet to appear as a table using the Concatenate function in Excel as explined here: http://designintellection.com/using-...les-and-forms/
(presumably OOo can do this as well) but the Conditional Formatting doesn't carry over when I do this.

So presumably I need to do some kind of 'if' statement in Excel/OOo?

Okay, yes, I see that now. On the simple test that I tried, it seemed to use HTML styles to distinguish the various cell content. There are different styles named and attached to the table cells, but all of the styles have identical values. Perhaps a small sed/awk/perl script can be used to post-process the HTML, and apply appropriate values to the styles used.
--- rod.

Can you save as an html table without colors?
If you can, could you paste (with the html code)
here an excerpt, say 2 or 3 typical rows
and could you indicate which column is to have a different
color according to its text length
so I could have a look and see what are the possibilities

Yes, I can save to a table without colours by using the CONCATENATE function in Excel.

Here is a small sample of my table:

Hope this helps....

I take it, it's the 4th column that should be colored if
the text is more that 20 characters.

Off the bat, I'd say it should be possible to do it:
1. save as html without colors
2. write a stylesheet 'style.css' where you'd have something like
...
.long { color: #ff0000; }
...
or whatever color you want
write a sed script and run it on the html file and output
a new html file that includes the stylesheet and for every
entry '<td>...</td>' (in the 4th column) that has 20 characters or
more, replace '<td>' by '<td class=long>'

I'll play with it a bit and will get back to you

ok, here we go:

copy and paste the following into a file that you can
name 'seder.sed'
######### cut and paste below this line #######
#!/bin/sed -f

:loop
N
$!b loop

s?<[tT][dD][^>]*>?<td>?g
s?</[tT][dD]>?</td>?g
s?<[tT][rR][^>]*>?<tr>?g
s?</[tT][rR]>?</tr>?g
s?<[ \t\n]*/[ \t\n]*[hH][eE][aA][dD][^>]*>?</head>?

s?<td>[ \t\n\r]*?<td>?g
s?[ \t\n\r]*</td>?</td>?g

#this shouldn't be present
#but if there are, replace with the appropriate
#html code
s?±?__PLUSMINUS__?g
s?÷?__DIVISION__?g
s?¢?__CENT__?g
s?£?__POUND__?g

s?<tr>?±?g
s?</tr>?÷?g
s?<td>?¢?g
s?</td>?£?g

s?\(±[ \t\r\n]*¢[^£]*£[ \t\n\r]*¢[^£]*£[ \t\n\r]*¢[^£]*£[ \t\n\r]*\)¢\([^£]\{20,\}£\)?\1<td class=long>\2?g

s?±?<tr>?g
s?÷?</tr>?g
s?¢?<td>?g
s?£?</td>?g

s?[ \t\r\n]*\(</head>\)?\
<style type=text/css>\
.long { color: #ff0000; }\
</style>\n\1?

######### end of copy ###############

now, make this script executable:
chmod +x seder.sed
and run it against your html file and redirect the
output to your new colorized html file:

./seder.sed your_input.html > output.html

note:
I'm too lazy to look up the relevant code for
± ÷ ¢ £
should they be present in your html file (they
shouldn't, but if they are, you should replace
__PLUSMINUS__
__DIVISION__
__CENT__
__POUND__
by the appropriate &#.... code)

It was actually the 2nd column, but thanks a million!

I'll give it a shot and let you know how I get on.

You definitely want to use styles in your finished HTML output: that's precisely what styles are "for." I agree with the notion of having Microsoft Excel do all of the work that it can do, then, if necessary, post-processing the file that it produced.

I would suggest exploring what Excel can do very thoroughly, because this is a fairly common request and Excel ought to be able to do it entirely on its own ... it just might not be entirely obvious at first blush. "It usually pays to look deeper." Microsoft's programmers tend to take the approach of, "get it done, more-or-less, if the user pushes the big red button ... but also provide more-sophisticated options to those who bother to look for them." (It's a pretty smart strategy.)

ok, let me know if you figured how to change the script accordingly

that's the line you need to edit in your sed script:

s?\(±[ \t\r\n]*¢[^£]*£[ \t\n\r]*¢[^£]*£[ \t\n\r]*¢[^£]*£[ \t\n\r]*\)¢\([^£]\{20,\}£\)?\1<td class=long>\2?g

You've got me there - I'm lost! Can you post the line so it's the 2nd column that it changes?

Thanks again

ok, replace the line I mentionned by this one:

########## copy and paste

s?\(±[ \t\r\n]*¢[^£]*£[ \t\n\r]*\)¢\([^£]\{20,\}£[ \t\n\r]*¢[^£]*£[ \t\n\r]*¢[^£]*£[ \t\n\r]*\)?\1<td class
=long>\2?g

############ end of copy

By the way the script excepts a well-formed html file,
that is, if a tag is opened (e.g., <td>\) it expected
the corresponding closing tag (</td>\)

With a few lines of more code, it should be possible
to first fix the html file and then do the
replacements.

Let me know how it went