Excel interpreting HTML and CR/LF

G

Guest

Hope this is the right newsgroup - just yell at me if not :

My ASP app allows users to save certain dynamic data as excel spreadsheets and the method I chose to accomplish this was to simply create an table in HTML with my data and tell the browser to use the excel mime type. Works great - even little functions like SUM that you can you inside Excel directly. My problem is there is a notes column in this dataset which contains line breaks for between paragraphs. When Excel interprets <br>, <p>, etc it automatically creates a new row. I need excel to honor those tags as it would if you were in the desktop application and used [alt] + [enter] to create a new line inside a single cell

Here's what I've tried so far
- vbscript's chr(10) and chr(13
- <br>, <p
- wrapping my text inside <p> text text </p> tag
- "# & 1 0 ;" and "# & 1 3 ;" w/o space

How can I tell Excel to not break CR/LF chars into a new row, and just stick with the table structure I give it? Any leads are much appreciated.
 
J

Jake Marx

Hi Steve,

You can use the style "mso-data-placement:same-cell" on the br elements to
tell Excel to keep them in the same cell. Here's an example:

<html>
<head>
<style type="text/css">
<!--
br {mso-data-placement:same-cell}
//-->
</style>
</head>
<body>
<table>
<tr>
<td>This is a test.<br><br>This is only a test.</td>
<td>test 2</td>
</tr>
<tr>
<td>This is test 3. <br><br><br> Notice the 2 line break</td>
<td>test 4</td>
</tr>
</table>
</body>
</html>


I figured this out by doing what I wanted in Excel, then looking at the
source code in the Script Editor (Alt+Shift+F11). I noticed the style
applied to the br elements, and that seemed to do the trick when I added it
to a simple HTML document. The browser will ignore styles like that, as
they are proprietary Office styles.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]
 
G

Guest

Jake thanks a million - works like a champ. It seems to not accept <p> tags even when I add the same style like what you had for the <br>. No worries - I'll just replace <p>'s with a couple of breaks instead for now unless you got another trick for that

Thanks again
Steve
 
J

Jake Marx

Hi Steve,

You're welcome - glad to be able to assist. No, I don't know of a way
offhand to format the <p> tags so the text will stay in the same cell.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Checking for CR/LF in text file. 6
import text-file - strings contain cr-lf 2
Can Word replace CR/LF with LF? 6
SpreadsheetML <ss:Data> using spaces and cr/lf 3
Reading XML & CR + LF 1
Looking for a macro to remove Line Feeds and Carriage Returns 3
How to ignore embedded linefeed in CSV quoted text fields? 1
writing excel data into word document 1

Top