• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1281
  • Last Modified:

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???
0
bickes0724
Asked:
bickes0724
  • 4
  • 4
  • 2
1 Solution
 
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
 
Geert BormansCommented:
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Geert BormansCommented:
mmh, seems like trying to force a &#10; instead of the original newline is advisable
0
 
kmartin7Commented:
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:
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 BormansCommented:
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 BormansCommented:
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now