Solved

How to Preserve line breaks from XML in XSLT for Excel

Posted on 2010-09-02
10
1,111 Views
Last Modified: 2013-11-18
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
Comment
Question by:bickes0724
  • 4
  • 4
  • 2
10 Comments
 
LVL 11

Expert Comment

by:kmartin7
ID: 33588379
Can you give me an example of the text string that is output from your database? Specifically one that shows a line break.
0
 

Author Comment

by:bickes0724
ID: 33588444
Here is a screenshot of the XML in the database... Is this what you need?
XML-in-database.png
0
 
LVL 60

Expert Comment

by:Geert Bormans
ID: 33588772
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
 
LVL 60

Expert Comment

by:Geert Bormans
ID: 33588791
mmh, seems like trying to force a &#10; instead of the original newline is advisable
0
 
LVL 11

Accepted Solution

by:
kmartin7 earned 500 total points
ID: 33588885
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 11

Expert Comment

by:kmartin7
ID: 33588904
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
 
LVL 60

Expert Comment

by:Geert Bormans
ID: 33588932
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
 
LVL 11

Expert Comment

by:kmartin7
ID: 33589005
Forgive me, but I didn't read your post, Geert. Too busy with lunch right now. :)
0
 

Author Comment

by:bickes0724
ID: 33589105
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
 
LVL 60

Expert Comment

by:Geert Bormans
ID: 33590093
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Browsers only know CSS so your awesome SASS code needs to be translated into normal CSS. Here I'll try to explain what you should aim for in order to take full advantage of SASS.
Have you tried to learn about Unicode, UTF-8, and multibyte text encoding and all the articles are just too "academic" or too technical? This article aims to make the whole topic easy for just about anyone to understand.
The viewer will learn the basics of jQuery including how to code hide show and toggles. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now