How to Preserve line breaks from XML in XSLT for Excel

Dear Experts,

I have 2 columns that contain recipe ingredients and recipe instructions. When the form is submitted, I preserve the user's line breaks, but when the data is displayed in Excel, They are disregarded. How can I preserve them???
bickes0724Asked:
Who is Participating?
 
kmartin7Connect With a Mentor Commented:
Try this:

<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
                      xmlns:html="http://www.w3.org/TR/REC-html40" exclude-result-prefixes="#default">
                          <xsl:output indent="yes"/>

      <xsl:template match="/">
            <Workbook >
                  <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
                        <Author>Kurt D Martin</Author>
                        <LastAuthor>Kurt D Martin</LastAuthor>
                        <Created>2010-09-02T16:36:46Z</Created>
                        <Company>United States Postal Service</Company>
                        <Version>11.9999</Version>
                  </DocumentProperties>
                  <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
                        <WindowHeight>10830</WindowHeight>
                        <WindowWidth>18105</WindowWidth>
                        <WindowTopX>0</WindowTopX>
                        <WindowTopY>60</WindowTopY>
                        <ProtectStructure>False</ProtectStructure>
                        <ProtectWindows>False</ProtectWindows>
                  </ExcelWorkbook>
                  <Styles>
                        <Style ss:ID="Default" ss:Name="Normal">
                              <Alignment ss:Vertical="Bottom" ss:WrapText="1"/>
                              <Borders/>
                              <Font/>
                              <Interior/>
                              <NumberFormat/>
                              <Protection/>
                        </Style>
                        <Style ss:ID="s22">
                              <Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
                              <Font x:Family="Swiss" ss:Bold="1"/>
                        </Style>
                        <Style ss:ID="s23">
                                 <Alignment ss:Vertical="Bottom" ss:WrapText="1"/>
                          </Style>
                  </Styles>
                  <Worksheet ss:Name="Sheet1">
                        <Table ss:ExpandedColumnCount="2" ss:ExpandedRowCount="100" x:FullColumns="1" x:FullRows="1">
                              <Column ss:AutoFitWidth="0" ss:Width="181.5"/>
                              <Column ss:AutoFitWidth="0" ss:Width="327"/>
                              <Row>
                                    <Cell ss:StyleID="s22">
                                          <Data ss:Type="String">Ingredients</Data>
                                    </Cell>
                                    <Cell ss:StyleID="s22">
                                          <Data ss:Type="String">Instructions</Data>
                                    </Cell>
                              </Row>
                              <xsl:apply-templates/>
                        </Table>
                        <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
                              <Selected/>
                              <Panes>
                                    <Pane>
                                          <Number>3</Number>
                                          <RangeSelection>R1C1:R1C2</RangeSelection>
                                    </Pane>
                              </Panes>
                              <ProtectObjects>False</ProtectObjects>
                              <ProtectScenarios>False</ProtectScenarios>
                        </WorksheetOptions>
                  </Worksheet>
                  <Worksheet ss:Name="Sheet2">
                        <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
                              <ProtectObjects>False</ProtectObjects>
                              <ProtectScenarios>False</ProtectScenarios>
                        </WorksheetOptions>
                  </Worksheet>
                  <Worksheet ss:Name="Sheet3">
                        <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
                              <ProtectObjects>False</ProtectObjects>
                              <ProtectScenarios>False</ProtectScenarios>
                        </WorksheetOptions>
                  </Worksheet>
            </Workbook>
      </xsl:template>

      <xsl:template match="FormPost">
            <Row>
                  <Cell>
                        <Data ss:Type="String">
                              <xsl:apply-templates select="Ingredients"/>
                        </Data>
                  </Cell>
                  <Cell>
                        <Data ss:Type="String">
                              <xsl:apply-templates select="Instructions"/>
                        </Data>
                  </Cell>
            </Row>
      </xsl:template>

      <xsl:template match="Ingredients">
            <xsl:apply-templates select="text()"/>
      </xsl:template>

      <xsl:template match="Instructions">
            <xsl:apply-templates select="text()"/>
      </xsl:template>

      <xsl:template match="text()">
            <xsl:call-template name="break"/>
      </xsl:template>

      <xsl:template name="break">
            <xsl:param name="text" select="."/>
            <xsl:choose>
                  <xsl:when test="contains($text, '&#xA;')">
                        <xsl:value-of select="substring-before($text, '&#xA;')"/><xsl:text disable-output-escaping="yes"><![CDATA[&#10;]]></xsl:text>
                        <xsl:call-template name="break">
                              <xsl:with-param name="text" select="substring-after($text,'&#10;')"/>
                        </xsl:call-template>
                  </xsl:when>
                  <xsl:otherwise>
                        <xsl:value-of select="$text"/>
                  </xsl:otherwise>
            </xsl:choose>
      </xsl:template>
</xsl:stylesheet>
0
 
kmartin7Commented:
Can you give me an example of the text string that is output from your database? Specifically one that shows a line break.
0
 
bickes0724Author Commented:
Here is a screenshot of the XML in the database... Is this what you need?
XML-in-database.png
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Geert BormansInformation ArchitectCommented:
Well, you need two things for that

1. Your data needs to be in a cell with datatype "string"
   <Row ss:Height="25.5" ss:StyleID="s25">
    <Cell><Data ss:Type="String">c&#10;d</Data></Cell>

2. wrapping needs to be set to "1" in the style
  <Style ss:ID="s25" ss:Parent="s20">
   <Alignment ss:Vertical="Bottom" ss:WrapText="1"/>
   <Font/>
  </Style>

Note that I have a "&#10;" in the XML, but that is basically not different from a newline, so just keeping the XML as is, with the newline should be sufficient
You would be on the save side if you just generated a &#10; from every newline, but I don't expect that to be necessary

Be aware that you asp xml parser does tricky thingw when parsing XML with newlines :-(
0
 
Geert BormansInformation ArchitectCommented:
mmh, seems like trying to force a &#10; instead of the original newline is advisable
0
 
kmartin7Commented:
The key in the Excel XSLT is the following:

<Style ss:ID="Default" ss:Name="Normal">
                              <Alignment ss:Vertical="Bottom" ss:WrapText="1"/>
                              <Borders/>
                              <Font/>
                              <Interior/>
                              <NumberFormat/>
                              <Protection/>
                        </Style>

You can set the numeric entity &#10; all day long in Excel but if ss:WrapText isn't set to "1", it won't wrap.
0
 
Geert BormansInformation ArchitectCommented:
as I said no?

2. wrapping needs to be set to "1" in the style
  <Style ss:ID="s25" ss:Parent="s20">
   <Alignment ss:Vertical="Bottom" ss:WrapText="1"/>

:-)
0
 
kmartin7Commented:
Forgive me, but I didn't read your post, Geert. Too busy with lunch right now. :)
0
 
bickes0724Author Commented:
That works beautifully!!!! Last question, is there any way to make the XSLT dynamic in adding that style? Meaning, do not specifically name the fields, it will find and parse all fields that contain line break?. Right now this is used for one form, but later it will be used for all form submissions on all of our sites.
0
 
Geert BormansInformation ArchitectCommented:
no problem kurt, lunch deserves attention

@bickes,
I would like to point out that the first and correct answer was in my post ID:33588772
Kurt gave you the full working code (based on an earlier question I assume, since the source XML format doesn't ring a bell),
but the heart of the problem was solved in my post
I don't care about the points, but some visible credit is always nice.
It is EE policy to accept the first correct answer to the original question. If you want to reward later comments or earlier important comments,
you can always split points easily over questions. Please do so in future.
No need to break open the question now and re-assign, I don't care that much, just wanted to point out the possibility. I just had a need for being educational :-)

About your follow up. Question is closed, essentialy this belongs in a follow up question of its own

But to answer it here...
yes, you can add the style to any cell.
From every style you use, you could make a wrapping variant
eg.
<Style ss:ID="s25" ss:Parent="s20">
   <Alignment ss:Vertical="Bottom" ss:WrapText="1"/>
   <Font/>
  </Style>
style s25 is a wrapping variant of style s20
then you can test using contains
"contains(., '&#10;')" to decide between s20 or s25

or you can set the rows to wrapping by default, I don't think it hurts if there are no newlines
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.