Solved

Add alternating row styles to XSLT for Excel

Posted on 2010-09-02
14
1,824 Views
Last Modified: 2013-11-18
Dear Experts,

I would like to add alternating row styles to XSLT for Excel. I need a little help with the code below.

<Table x:FullColumns="1" x:FullRows="1">
        <Row>
          <xsl:choose>
            <xsl:when test="position() mod 2 = 1">
              *** Row background color/ styles here ***
            </xsl:when>
            <xsl:otherwise>
               *** Row background color/ styles here ***
            </xsl:otherwise>
          </xsl:choose>


Thank you for your time in advance!
0
Comment
Question by:bickes0724
  • 8
  • 4
  • 2
14 Comments
 
LVL 1

Expert Comment

by:Noggin182
ID: 33588094
Presuming you have a style sheet with "oddRow" and "evenRow" styles I think this should work

 <Row>
          <xsl:choose>
            <xsl:when test="position() mod 2 = 1">
              <xsl:attribute name="class">oddRow</xsl:attribute>
            </xsl:when>
            <xsl:otherwise>
              <xsl:attribute name="class">evenRow</xsl:attribute>
            </xsl:otherwise>
          </xsl:choose>

otherwise this:

 <Row>
          <xsl:choose>
            <xsl:when test="position() mod 2 = 1">
              <xsl:attribute name="style">color:red</xsl:attribute>
            </xsl:when>
            <xsl:otherwise>
              <xsl:attribute name="style">color:green</xsl:attribute>
            </xsl:otherwise>
          </xsl:choose>
0
 
LVL 60

Expert Comment

by:Geert Bormans
ID: 33588106
well honestly, the code you need is in your question
Inside the "when", you put the styles for teh odd rows,
inside the "otherwise" you put the styles for the even rows

so you need to be more specific of what exactly you need
0
 
LVL 60

Expert Comment

by:Geert Bormans
ID: 33588173
styles in Excell, in case you don't know are expressed early in the resulting excel,
and in the ss:style attribute you make a reference to them

In code this would mean

ss:StyleID="s16"
<Table x:FullColumns="1" x:FullRows="1">

        <Row>

         <xsl:attribute name="ss:styleID">

          <xsl:choose>

            <xsl:when test="position() mod 2 = 1">

             <xsl:value-of select="s16"/>

            </xsl:when>

            <xsl:otherwise>

             <xsl:value-of select="s20"/>

            </xsl:otherwise>

          </xsl:choose>

         </xsl:attribute>

Open in new window

0
 

Author Comment

by:bickes0724
ID: 33588200
My code is attached and it is not working. I am sure I missing something obvious, but I'm not sure what.
<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns="urn:schemas-microsoft-com:office:spreadsheet" 
                xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
                xmlns:msxsl="urn:schemas-microsoft-com:xslt" 
                xmlns:user="urn:my-scripts" 
                xmlns:o="urn:schemas-microsoft-com:office:office"
                xmlns:x="urn:schemas-microsoft-com:office:excel" 
                xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
  <xsl:output indent="yes"/>

  <xsl:template match="/">
    <Workbook 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">
      
        <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
          <WindowHeight>11000</WindowHeight>
          <WindowWidth>17000</WindowWidth>
          <WindowTopX>0</WindowTopX>
          <WindowTopY>0</WindowTopY>
          <ProtectStructure>False</ProtectStructure>
          <ProtectWindows>False</ProtectWindows>
        </ExcelWorkbook>
       
      <Styles>
        <Style ss:ID="Default" ss:Name="Normal">
          <Alignment ss:Horizontal="Left" ss:Vertical="Top" ss:WrapText="0" />
          <Borders/>
          <Font ss:FontName="Times New Roman" x:Family="Roman" ss:Size="10" />
          <Interior/>
          <NumberFormat/>
          <Protection/>
        </Style>
        <Style ss:ID="s21">
          <Font ss:Bold="1"/>
          <Alignment ss:Horizontal="Center" />
        </Style>
        <Style ss:ID="s22">
          <Interior ss:color="#707070"/>
        </Style>
      </Styles>
      <xsl:apply-templates/>
    </Workbook>
  </xsl:template>

  <xsl:template match="/*">
    <Worksheet>
      <xsl:attribute name="ss:Name">
        <xsl:value-of select="local-name(/*/*)"/>
      </xsl:attribute>
      <Table x:FullColumns="1" x:FullRows="1">
        <Row>
          <xsl:choose>
            <xsl:when test="position() mod 2 = 1">
              <xsl:attribute name="style">color:red</xsl:attribute>
            </xsl:when>
            <xsl:otherwise>
              <xsl:attribute name="style">color:green</xsl:attribute>
            </xsl:otherwise>
          </xsl:choose>

          <xsl:for-each select="*[position() = 1]/*">
            <Cell ss:StyleID="s21">
              <xsl:attribute name="bcolor"></xsl:attribute>
              <Data ss:Type="String">
                <xsl:value-of select="local-name()"/>
              </Data>
            </Cell>
          </xsl:for-each>
        </Row>
        <xsl:apply-templates/>
      </Table>
    </Worksheet>
  </xsl:template>

  <xsl:template match="/*/*">
    <Row>      
      <xsl:apply-templates/>
    </Row>
  </xsl:template>

  <xsl:template match="/*/*/*">
    <Cell>
      <Data ss:Type="String" >
        <xsl:value-of select="."/>
      </Data>
    </Cell>
    
  </xsl:template>  
  
</xsl:stylesheet>

Open in new window

0
 
LVL 1

Expert Comment

by:Noggin182
ID: 33588209
Sorry, I missed your were using Excel, I assumed HTML. My first comment will still work, just use the ss:StyleID instead of class as Gertone as suggested
0
 
LVL 60

Expert Comment

by:Geert Bormans
ID: 33588217
right before you open the worksheet elements you need a Styles element

you can simply hardcode that in your code

I have a simple example here for the definition of s16 and s20 as I used in my example.
Offcourse you need to make the style elements to your need.
Best way to do that is format two rows differently in an excel, save the style of the formatting, export the Excell to XML (spreadsheet XML) and copy the Styles section of the saved document into your XSLT
 <Styles>

  <Style ss:ID="Default" ss:Name="Normal">

   <Alignment ss:Vertical="Bottom"/>

   <Borders/>

   <Font/>

   <Interior/>

   <NumberFormat/>

   <Protection/>

  </Style>

  <Style ss:ID="s16" ss:Name="Comma">

   <NumberFormat

    ss:Format="_-* #,##0.00\ _€_-;\-* #,##0.00\ _€_-;_-* &quot;-&quot;??\ _€_-;_-@_-"/>

  </Style>

  <Style ss:ID="s20" ss:Name="Percent">

   <NumberFormat ss:Format="0%"/>

  </Style>

Open in new window

0
 
LVL 60

Expert Comment

by:Geert Bormans
ID: 33588338
about your code

1. forget the style stuff noggin suggested because that will not work, use style references instead.
Based on your example there are two styles you can use s21 and s22, then yuse my code

2. The first row of the table is static. You don't need to put the test code there, because it is not dynamic
you need to put the code for the attribute ss:styleID in the template

  <xsl:template match="/*/*">
    <Row>      
        <Row>
         <xsl:attribute name="ss:styleID">
          <xsl:choose>
            <xsl:when test="position() mod 2 = 1">
             <xsl:value-of select="s21"/>
            </xsl:when>
            <xsl:otherwise>
             <xsl:value-of select="s22"/>
            </xsl:otherwise>
          </xsl:choose>
         </xsl:attribute>
      <xsl:apply-templates/>
    </Row>
  </xsl:template>

3. If you use position() all sorts of nodes are counted.
For that you need to be very explicit in the apply templates.
In the template for "/*"
        </Row>
        <xsl:apply-templates/>
      </Table>
should be
        </Row>
        <xsl:apply-templates select="*"/>
      </Table>

0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:bickes0724
ID: 33588351
First off, Thank you for the help from both of you!
Is this what you mean? It is still not working for me....

<xsl:attribute name="ss:styleID">
            <xsl:choose>
              <xsl:when test="position() mod 2 = 1">
                <xsl:value-of select="s21"/>
              </xsl:when>
              <xsl:otherwise>
                <xsl:value-of select="s22"/>
              </xsl:otherwise>
            </xsl:choose>
          </xsl:attribute>
0
 
LVL 60

Expert Comment

by:Geert Bormans
ID: 33588668
yep, that is what I mean, it gives a different style to alternating rows. Off course those styles referenced by the style ID need to mean something.
Why don't you pass source XML and full XSLT, so I can have a look
0
 

Author Comment

by:bickes0724
ID: 33588810
This is the xslt:
<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns="urn:schemas-microsoft-com:office:spreadsheet" 
                xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
                xmlns:msxsl="urn:schemas-microsoft-com:xslt" 
                xmlns:user="urn:my-scripts" 
                xmlns:o="urn:schemas-microsoft-com:office:office"
                xmlns:x="urn:schemas-microsoft-com:office:excel" 
                xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
  <xsl:output indent="yes"/>

  <xsl:template match="/">
    <Workbook 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">
      
        <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
          <WindowHeight>11000</WindowHeight>
          <WindowWidth>17000</WindowWidth>
          <WindowTopX>0</WindowTopX>
          <WindowTopY>0</WindowTopY>
          <ProtectStructure>False</ProtectStructure>
          <ProtectWindows>False</ProtectWindows>
        </ExcelWorkbook>
       
      <Styles>
        <Style ss:ID="Default" ss:Name="Normal">
          <Alignment ss:Horizontal="Left" ss:Vertical="Top" ss:WrapText="0" />
          <Borders/>
          <Font ss:FontName="Times New Roman" x:Family="Roman" ss:Size="10" />
          <Interior/>
          <NumberFormat/>
          <Protection/>
        </Style>
        <Style ss:ID="s21">
          <Font ss:Bold="1"/>
          <Alignment ss:Horizontal="Center" />
        </Style>
        <Style ss:ID="s22">
          <Font ss:FontName="Times New Roman" x:Family="Roman" ss:Size="15" />
        </Style>
      </Styles>
      <xsl:apply-templates/>
    </Workbook>
  </xsl:template>

  <xsl:template match="/*">
    <Worksheet>
      <xsl:attribute name="ss:Name">
        <xsl:value-of select="local-name(/*/*)"/>
      </xsl:attribute>
      <Table x:FullColumns="1" x:FullRows="1">
        <Row>
          <xsl:attribute name="ss:styleID">
            <xsl:choose>
              <xsl:when test="position() mod 2 = 1">
                <xsl:value-of select="s21"/>
              </xsl:when>
              <xsl:otherwise>
                <xsl:value-of select="s22"/>
              </xsl:otherwise>
            </xsl:choose>
          </xsl:attribute>

          <xsl:for-each select="*[position() = 1]/*">
            <Cell ss:StyleID="s21">
              <xsl:attribute name="bcolor"></xsl:attribute>
              <Data ss:Type="String">
                <xsl:value-of select="local-name()"/>
              </Data>
            </Cell>
          </xsl:for-each>
        </Row>
        <xsl:apply-templates/>
      </Table>
    </Worksheet>
  </xsl:template>

  <xsl:template match="/*/*">
    <Row>      
      <xsl:apply-templates/>
    </Row>
  </xsl:template>

  <xsl:template match="/*/*/*">
    <Cell>
      <Data ss:Type="String" >
        <xsl:value-of select="."/>
      </Data>
    </Cell>
    
  </xsl:template>  
  
</xsl:stylesheet>

Open in new window

0
 

Author Comment

by:bickes0724
ID: 33588822
This is the XML i dynamically build based on a start and end date from the user. Sorry it is not formatted...
<FormPosts><FormPost><SubmitDate>2010/08/19 03:15 PM</SubmitDate><FirstName>Tom</FirstName><LastName>Jones</LastName><Address>234 North Ave</Address><City>Stow</City><State>OH</State><Zip>44778</Zip><Country>United States</Country><Phone>999 888 7777</Phone><Email>bic@pro.com</Email><Category>Main Dish</Category><RecipeName>Scrambled Eggs with Bacon</RecipeName><Ingredients>3 to 4 tablespoons grainy mustard\r\n1 to 2 tablespoon minced dill, plus a few sprigs for garnish (optional)\r\nKosher or sea salt\r\n1 tablespoon slightly crushed pink peppercorns\r\n4 6-ounce boneless, skinless salmon fillets, preferably at least 3/4 inch thick, pin bones removed\r\n4 slices streaky (fatty) bacon</Ingredients><Instructions>Position an oven rack 4 to 5 inches from the broiling element; preheat the broiler. Have ready a broiler pan with a rack and 4 long metal skewers.\r\n\r\nCombine the mustard, dill, salt and pink peppercorns in a small bowl, mixing well. Spread in equal amounts on the top (rounded) sides of the salmon fillets, pressing the mixture down slightly into the fish. Lay a slice of bacon across each fillet (fold into a V shape, if using center-cut fillets) and tuck the ends neatly underneath. Thread a skewer through the center of each fillet to secure the edges of the bacon. Place the fillets bacon side down on the broiler pan and broil for 4 minutes, until the salmon looks opaque, then use a spatula to carefully turn them over (leaving the skewers in place) and broil for 3 to 4 minutes, until the bacon is crisp. Remove the skewers and serve immediately.</Instructions><ServingSize>1 Fillet</ServingSize><Servings>4</Servings></FormPost><FormPost><SubmitDate>2010/08/19 03:15 PM</SubmitDate><FirstName>Br</FirstName><LastName>Is</LastName><Address>234 North Ave</Address><City>Stow</City><State>OH</State><Zip>44778</Zip><Country>United States</Country><Phone>999 888 7777</Phone><Email>bic@ro.com</Email><Category>Main Dish</Category><RecipeName>Scrambled Eggs with Bacon</RecipeName><Ingredients>3 to 4 tablespoons grainy mustard\r\n1 to 2 tablespoon minced dill, plus a few sprigs for garnish (optional)\r\nKosher or sea salt\r\n1 tablespoon slightly crushed pink peppercorns\r\n4 6-ounce boneless, skinless salmon fillets, preferably at least 3/4 inch thick, pin bones removed\r\n4 slices streaky (fatty) bacon</Ingredients><Instructions>Position an oven rack 4 to 5 inches from the broiling element; preheat the broiler. Have ready a broiler pan with a rack and 4 long metal skewers.\r\n\r\nCombine the mustard, dill, salt and pink peppercorns in a small bowl, mixing well. Spread in equal amounts on the top (rounded) sides of the salmon fillets, pressing the mixture down slightly into the fish. Lay a slice of bacon across each fillet (fold into a V shape, if using center-cut fillets) and tuck the ends neatly underneath. Thread a skewer through the center of each fillet to secure the edges of the bacon. Place the fillets bacon side down on the broiler pan and broil for 4 minutes, until the salmon looks opaque, then use a spatula to carefully turn them over (leaving the skewers in place) and broil for 3 to 4 minutes, until the bacon is crisp. Remove the skewers and serve immediately.</Instructions><ServingSize>1 Fillet</ServingSize><Servings>4</Servings></FormPost><FormPost><SubmitDate>2010/08/19 03:54 PM</SubmitDate><FirstName>don</FirstName><LastName>kes</LastName><Address>234 South Ave</Address><City>Stow</City><State>OH</State><Zip>44778</Zip><Country>United States</Country><Phone>999 888 7777</Phone><Email>bkes@po.com</Email><Category>Main Dish</Category><RecipeName>Scrambled Eggs with Bacon</RecipeName><Ingredients>3 to 4 tablespoons grainy mustard\r\n1 to 2 tablespoon minced dill, plus a few sprigs for garnish (optional)\r\nKosher or sea salt\r\n1 tablespoon slightly crushed pink peppercorns\r\n4 6-ounce boneless, skinless salmon fillets, preferably at least 3/4 inch thick, pin bones removed\r\n4 slices streaky (fatty) bacon</Ingredients><Instructions>Position an oven rack 4 to 5 inches from the broiling element; preheat the broiler. Have ready a broiler pan with a rack and 4 long metal skewers.\r\n\r\nCombine the mustard, dill, salt and pink peppercorns in a small bowl, mixing well. Spread in equal amounts on the top (rounded) sides of the salmon fillets, pressing the mixture down slightly into the fish. Lay a slice of bacon across each fillet (fold into a V shape, if using center-cut fillets) and tuck the ends neatly underneath. Thread a skewer through the center of each fillet to secure the edges of the bacon. Place the fillets bacon side down on the broiler pan and broil for 4 minutes, until the salmon looks opaque, then use a spatula to carefully turn them over (leaving the skewers in place) and broil for 3 to 4 minutes, until the bacon is crisp. Remove the skewers and serve immediately.</Instructions><ServingSize>1 Fillet</ServingSize><Servings>4</Servings></FormPost><FormPost><SubmitDate>2010/08/25 12:13 PM</SubmitDate><FirstName>Tom</FirstName><LastName>Puller</LastName><Address>9934 Roadster Dr</Address><City>Clinton</City><State>PA</State><Zip>77842</Zip><Country>United States</Country><Phone>(888) 999-7777</Phone><Email>bes@pri.com</Email><Category>Main Dish</Category><RecipeName>Bacon Explosion </RecipeName><Ingredients>2 pounds thick-cut sliced bacon\r\n\r\n1 1/2 pounds Italian sausage, casings removed\r\n\r\n3 tablespoons barbecue rub\r\n\r\n3/4 cup barbecue sauce.</Ingredients><Instructions>1.  Using 10 slices of bacon, weave a square lattice like that on top of a pie: first, place 5 bacon slices side by side on a large sheet of aluminum foil, parallel to one another, sides touching. Place another strip of bacon on one end, perpendicular to the other strips. Fold first, third and fifth bacon strips back over this new strip, then place another strip next to it, parallel to it. Unfold first, third and fifth strips; fold back second and fourth strips. Repeat with remaining bacon until all 10 strips are tightly woven.\r\n\r\n2. Preheat oven to 225 degrees or light a fire in an outdoor smoker. Place remaining bacon in a frying pan and cook until crisp. As it cooks, sprinkle bacon weave with 1 tablespoon barbecue rub. Evenly spread sausage on top of bacon lattice, pressing to outer edges.\r\n\r\n3. Crumble fried bacon into bite-size pieces. Sprinkle on top of sausage. Drizzle with 1/2 cup barbecue sauce and sprinkle with another tablespoon barbecue rub.\r\n\r\n4. Very carefully separate front edge of sausage layer from bacon weave and begin rolling sausage away from you. Bacon weave should stay where it was, flat. Press sausage roll to remove any air pockets and pinch together seams and ends.\r\n\r\n5. Roll sausage toward you, this time with bacon weave, until it is completely wrapped. Turn it so seam faces down. Roll should be about 2 to 3 inches thick. Sprinkle with remaining barbecue rub.\r\n\r\n6. Place roll on a baking sheet in oven or in smoker. Cook until internal temperature reaches 165 degrees on a meat thermometer, about 1 hour for each inch of thickness. When done, glaze roll with more sauce. To serve, slice into 1/4-\r\n\r\nto- 1/2-inch rounds.</Instructions><ServingSize>2 Rolls</ServingSize><Servings>10+</Servings></FormPost><FormPost><SubmitDate>2010/08/25 12:14 PM</SubmitDate><FirstName>Tom</FirstName><LastName>Puller</LastName><Address>9934 Roadster Dr</Address><City>Clinton</City><State>PA</State><Zip>77842</Zip><Country>United States</Country><Phone>(888) 999-7777</Phone><Email>es@pr.com</Email><Category>Main Dish</Category><RecipeName>Bacon Explosion </RecipeName><Ingredients>2 pounds thick-cut sliced bacon\r\n\r\n1 1/2 pounds Italian sausage, casings removed\r\n\r\n3 tablespoons barbecue rub\r\n\r\n3/4 cup barbecue sauce.</Ingredients><Instructions>1.  Using 10 slices of bacon, weave a square lattice like that on top of a pie: first, place 5 bacon slices side by side on a large sheet of aluminum foil, parallel to one another, sides touching. Place another strip of bacon on one end, perpendicular to the other strips. Fold first, third and fifth bacon strips back over this new strip, then place another strip next to it, parallel to it. Unfold first, third and fifth strips; fold back second and fourth strips. Repeat with remaining bacon until all 10 strips are tightly woven.\r\n\r\n2. Preheat oven to 225 degrees or light a fire in an outdoor smoker. Place remaining bacon in a frying pan and cook until crisp. As it cooks, sprinkle bacon weave with 1 tablespoon barbecue rub. Evenly spread sausage on top of bacon lattice, pressing to outer edges.\r\n\r\n3. Crumble fried bacon into bite-size pieces. Sprinkle on top of sausage. Drizzle with 1/2 cup barbecue sauce and sprinkle with another tablespoon barbecue rub.\r\n\r\n4. Very carefully separate front edge of sausage layer from bacon weave and begin rolling sausage away from you. Bacon weave should stay where it was, flat. Press sausage roll to remove any air pockets and pinch together seams and ends.\r\n\r\n5. Roll sausage toward you, this time with bacon weave, until it is completely wrapped. Turn it so seam faces down. Roll should be about 2 to 3 inches thick. Sprinkle with remaining barbecue rub.\r\n\r\n6. Place roll on a baking sheet in oven or in smoker. Cook until internal temperature reaches 165 degrees on a meat thermometer, about 1 hour for each inch of thickness. When done, glaze roll with more sauce. To serve, slice into 1/4-\r\nto- 1/2-inch rounds.</Instructions><ServingSize>2 Rolls</ServingSize><Servings>10+</Servings></FormPost><FormPost><SubmitDate>2010/08/25 05:17 PM</SubmitDate><FirstName>Tom </FirstName><LastName>Donald</LastName><Address>2389 Top St</Address><City>Wilshire</City><State>CA</State><Zip>78124</Zip><Country>United States</Country><Phone>777 666 5555</Phone><Email>bes@pimro.com</Email><Category>Appetizer/Sides/Entertaining</Category><RecipeName>Recipe Name</RecipeName><Ingredients>ing 1\r\ning 2</Ingredients><Instructions>inst 1. inst 2\r\ninst 3.</Instructions><ServingSize>5</ServingSize><Servings>5</Servings></FormPost><FormPost><SubmitDate>2010/08/30 09:09 AM</SubmitDate><FirstName>st</FirstName><LastName>ke</LastName><Address>asdf</Address><City>asdf</City><State>asdf</State><Zip>asdf</Zip><Country>asdf</Country><Phone>asdf</Phone><Email>s@po.com</Email><Category>Appetizer/Sides/Entertaining</Category><RecipeName>asdf</RecipeName><Ingredients>asdf</Ingredients><Instructions>asdf</Instructions><ServingSize>dd</ServingSize><Servings>dd</Servings></FormPost><FormPost><SubmitDate>2010/09/01 08:06 AM</SubmitDate><FirstName>q</FirstName><LastName>q</LastName><Address>q</Address><City>q</City><State>q</State><Zip>q</Zip><Country>q</Country><Phone>q</Phone><Email>kes@primo.com</Email><Category>Appetizer/Sides/Entertaining</Category><RecipeName>q</RecipeName><Ingredients>q</Ingredients><Instructions>q</Instructions><ServingSize>q</ServingSize><Servings>q</Servings></FormPost><FormPost><SubmitDate>2010/09/01 10:04 AM</SubmitDate><FirstName>e</FirstName><LastName>e</LastName><Address>e</Address><City>e</City><State>e</State><Zip>e</Zip><Country>e</Country><Phone>ee</Phone><Email>bs@pro.com</Email><Category>Appetizer/Sides/Entertaining</Category><RecipeName>e</RecipeName><Ingredients>e</Ingredients><Instructions>e</Instructions><ServingSize>e</ServingSize><Servings>e</Servings></FormPost><FormPost><SubmitDate>2010/09/01 10:29 AM</SubmitDate><FirstName>q</FirstName><LastName>q</LastName><Address>q</Address><City>q</City><State>q</State><Zip>q</Zip><Country>United States</Country><Phone>q</Phone><Email>bi@pro.com</Email><Category>Appetizer/Sides/Entertaining</Category><RecipeName>q</RecipeName><Ingredients>q</Ingredients><Instructions>q</Instructions><ServingSize>q</ServingSize><Servings>q</Servings></FormPost></FormPosts>

Open in new window

0
 
LVL 60

Assisted Solution

by:Geert Bormans
Geert Bormans earned 500 total points
ID: 33588846
Off course it doesn't work.
You simply did not follow any of my instructions
Try this
(and note that EE questions should result in you trying the stuff, not just us doing it all)
<?xml version="1.0"?>

<xsl:stylesheet version="1.0" xmlns="urn:schemas-microsoft-com:office:spreadsheet" 

 xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 

 xmlns:msxsl="urn:schemas-microsoft-com:xslt" 

 xmlns:user="urn:my-scripts" 

 xmlns:o="urn:schemas-microsoft-com:office:office"

 xmlns:x="urn:schemas-microsoft-com:office:excel" 

 xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">

 <xsl:output indent="yes"/>

 

 <xsl:template match="/">

  <Workbook 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">

   

   <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">

    <WindowHeight>11000</WindowHeight>

    <WindowWidth>17000</WindowWidth>

    <WindowTopX>0</WindowTopX>

    <WindowTopY>0</WindowTopY>

    <ProtectStructure>False</ProtectStructure>

    <ProtectWindows>False</ProtectWindows>

   </ExcelWorkbook>

   

   <Styles>

    <Style ss:ID="Default" ss:Name="Normal">

     <Alignment ss:Horizontal="Left" ss:Vertical="Top" ss:WrapText="0" />

     <Borders/>

     <Font ss:FontName="Times New Roman" x:Family="Roman" ss:Size="10" />

     <Interior/>

     <NumberFormat/>

     <Protection/>

    </Style>

    <Style ss:ID="s21">

     <Font ss:Bold="1"/>

     <Alignment ss:Horizontal="Center" />

    </Style>

    <Style ss:ID="s22">

     <Font ss:FontName="Times New Roman" x:Family="Roman" ss:Size="15" />

    </Style>

   </Styles>

   <xsl:apply-templates/>

  </Workbook>

 </xsl:template>

 

 <xsl:template match="/*">

  <Worksheet>

   <xsl:attribute name="ss:Name">

    <xsl:value-of select="local-name(/*/*)"/>

   </xsl:attribute>

   <Table x:FullColumns="1" x:FullRows="1">

    <Row>

     <xsl:for-each select="*[position() = 1]/*">

      <Cell ss:StyleID="s21">

       <xsl:attribute name="bcolor"></xsl:attribute>

       <Data ss:Type="String">

        <xsl:value-of select="local-name()"/>

       </Data>

      </Cell>

     </xsl:for-each>

    </Row>



    <xsl:apply-templates select="*"/>

   </Table>

  </Worksheet>

 </xsl:template>

 

 <xsl:template match="/*/*">

  <Row>      

   <xsl:attribute name="ss:styleID">

    <xsl:choose>

     <xsl:when test="position() mod 2 = 1">

      <xsl:value-of select="s21"/>

     </xsl:when>

     <xsl:otherwise>

      <xsl:value-of select="s22"/>

     </xsl:otherwise>

    </xsl:choose>

   </xsl:attribute>   

   <xsl:apply-templates/>

  </Row>

 </xsl:template>

 

 <xsl:template match="/*/*/*">

  <Cell>

   <Data ss:Type="String" >

    <xsl:value-of select="."/>

   </Data>

  </Cell>

  

 </xsl:template>  

 

</xsl:stylesheet>

Open in new window

0
 
LVL 60

Accepted Solution

by:
Geert Bormans earned 500 total points
ID: 33590970
I just found two typos in my code
ss:styleID should be ss:StyleID (uppercase S)
and I need quotes around the attribute values

here is your new template for /*/*
    <xsl:template match="/*/*">

        <Row>      

            <xsl:attribute name="ss:StyleID">

                <xsl:choose>

                    <xsl:when test="position() mod 2 = 1">

                        <xsl:value-of select="'s21'"/>

                    </xsl:when>

                    <xsl:otherwise>

                        <xsl:value-of select="'s22'"/>

                    </xsl:otherwise>

                </xsl:choose>

            </xsl:attribute>   

            <xsl:apply-templates/>

        </Row>

    </xsl:template>

    

Open in new window

0
 
LVL 60

Expert Comment

by:Geert Bormans
ID: 33590977
Fully tested and working as expected
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Excel Macro - Lookup-Highlight 4 25
cannot get subtotal to work 8 18
Gantt chart 2 16
Rearrange Macro 7 22
This article covers the basics of the Sass, which is a CSS extension language. You will learn about variables, mixins, and nesting.
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.
The viewer will receive an overview of the basics of CSS showing inline styles. In the head tags set up your style tags: (CODE) Reference the nav tag and set your properties.: (CODE) Set the reference for the UL element and styles for it to ensu…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

762 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

21 Experts available now in Live!

Get 1:1 Help Now