Solved

Add alternating row styles to XSLT for Excel

Posted on 2010-09-02
14
1,883 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
Independent Software Vendors: 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!

 

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
 

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

Industry Leaders: 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!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Array problem 6 47
Formula fix for vlookup and iserror 3 23
Slicers by Groups in Excel 7 17
Shade a cell based on date range 3 4
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.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

685 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