Solved

How to Preserve line breaks from XML in XSLT for Excel

Posted on 2010-09-02
10
1,095 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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
HTML5 has deprecated a few of the older ways of showing media as well as offering up a new way to create games and animations. Audio, video, and canvas are just a few of the adjustments made between XHTML and HTML5. As we learned in our last micr…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

746 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

12 Experts available now in Live!

Get 1:1 Help Now