Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2051
  • Last Modified:

Add alternating row styles to XSLT for Excel

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
bickes0724
Asked:
bickes0724
  • 8
  • 4
  • 2
2 Solutions
 
Noggin182Commented:
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
 
Geert BormansCommented:
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
 
Geert BormansCommented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
bickes0724Author Commented:
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
 
Noggin182Commented:
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
 
Geert BormansCommented:
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
 
Geert BormansCommented:
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
 
bickes0724Author Commented:
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
 
Geert BormansCommented:
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
 
bickes0724Author Commented:
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
 
bickes0724Author Commented:
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
 
Geert BormansCommented:
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
 
Geert BormansCommented:
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
 
Geert BormansCommented:
Fully tested and working as expected
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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