?
Solved

How to Preserve line breaks from XML in XSLT for Excel

Posted on 2010-09-02
10
Medium Priority
?
1,222 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: 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!

 
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 2000 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
 
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa‚Ķ
Suggested Courses

801 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