[XSL-LIST Mailing List Archive Home] [By Thread] [By Date] [Recent Entries] [Reply To This Message]

RE: Sorting Problem with Xml and Xsl

Subject: RE: Sorting Problem with Xml and Xsl
From: cknell@xxxxxxxxxx
Date: Tue, 13 Mar 2007 16:24:27 -0400
RE:  Sorting Problem with Xml and Xsl
You can use XSLT 2.0 or the node-set() extension function from MSXSLT to create a variable holding all the s:Data elements.

For details on the node-set() function: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconsupportformsxslnode-setfunction.asp

For Saxon .NET and XSLT 2.0: http://saxon.sourceforge.net/

I used Saxon-J and XSLT 2.0. This stylesheet gives the output you want.
<?xml version="1.0"?>
<xsl:stylesheet version="2.0" 
  xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
  xmlns:x="urn:schemas-microsoft-com:office:excel" 
  xmlns:o="urn:schemas-microsoft-com:office:office" 
  xmlns:s="urn:schemas-microsoft-com:office:spreadsheet">
  
  <xsl:strip-space elements="*" />
  
  <xsl:output method="xml" indent="yes" encoding="UTF-8" />
  
  
  <xsl:variable name="ordered-data-values">
  <list>
    <xsl:for-each select="/s:Workbook/s:Worksheet//s:Table/s:Row/s:Cell/s:Data">
      <xsl:sort select="." />
      <value><xsl:copy-of select="." /></value>
     </xsl:for-each>
  </list>
  </xsl:variable>
  
  
  <xsl:template match="/s:Workbook">
    <s:Workbook xmlns:x="urn:schemas-microsoft-com:office:excel" 
      xmlns:o="urn:schemas-microsoft-com:office:office" 
      xmlns:s="urn:schemas-microsoft-com:office:spreadsheet">
      <xsl:apply-templates />
    </s:Workbook>
  </xsl:template>

  <xsl:template match="s:Worksheet">
    <xsl:variable name="row-count" select="count(s:Table/s:Row)" />
    <xsl:variable name="first-row-index" select="count(preceding-sibling::s:Worksheet/s:Table/s:Row)" />

    <s:Worksheet>
      <xsl:copy-of select="@*" />
      <s:Table>
        <xsl:for-each select="$ordered-data-values/list/value">
          <xsl:if test="position() &gt; $first-row-index and position() &lt;= ($row-count + $first-row-index)">
            <s:Row>
              <s:Cell>
                <s:Data s:Type="String"><xsl:value-of select="." /></s:Data>
              </s:Cell>
            </s:Row>
          </xsl:if>
        </xsl:for-each>
      </s:Table>
    </s:Worksheet>

  </xsl:template>

</xsl:stylesheet>

-- 
Charles Knell
cknell@xxxxxxxxxx - email



-----Original Message-----
From:     Ram Shan <hellodck@xxxxxxxxxxx>
Sent:     Tue, 13 Mar 2007 11:22:09 -0600
To:       xsl-list@xxxxxxxxxxxxxxxxxxxxxx
Subject:  RE:  Sorting Problem with Xml and Xsl

Thanks for taking time to check on this. As per your example, I would like 
to see this output

Worksheet 1, has these values:
A
A
B
Worksheet 2 has these values:
C
D
F
F
G
M
Worksheet 3 has these values:
O
P
W
X



---------------------
1. as you can see from above, the total no.of elements in each worksheet 
remain the same. It just that they are sorted in alpabhatical order.
2. As for grouping , they would need to be grouped by first character of the 
string...If the first character is same, then go for second char
3.As for duplicates, If the string in one row is exactly equal to other ( 
from above example, F), need to have both the strings in the sorted list

Thanks


>From: cknell@xxxxxxxxxx
>Reply-To: xsl-list@xxxxxxxxxxxxxxxxxxxxxx
>To: xsl-list@xxxxxxxxxxxxxxxxxxxxxx
>Subject: RE:  Sorting Problem with Xml and Xsl
>Date: Tue, 13 Mar 2007 12:05:07 -0400
>
>Suppose you have a workbook with three worksheets:
>
>Worksheet 1, has these values:
>W
>A
>D
>
>Worksheet 2 has these values:
>F
>C
>G
>P
>O
>M
>
>Worksheet 3 has these values:
>X
>B
>A
>F
>
>Please show the grouping (which values go on which worksheet) and the order 
>you want in the output. Please state how you want to handle duplicate 
>values.
>--
>Charles Knell
>cknell@xxxxxxxxxx - email
>
>
>
>-----Original Message-----
>From:     Ram Shan <hellodck@xxxxxxxxxxx>
>Sent:     Tue, 13 Mar 2007 09:54:50 -0600
>To:       xsl-list@xxxxxxxxxxxxxxxxxxxxxx
>Subject:  RE: RE: RE:  Sorting Problem with Xml and Xsl
>
>
> >So you have several worksheets.
>Yes
> >How many rows do you have on each input worksheet?
>Can be variable...(I am trying to sort the excel spreadsheet by converting
>excel to Xml and trying to
>sort on the column)...So, It depends on how many rows the user creates
>
> >How many rows do you want on each of the output worksheets?
>Should be same as the input xml (# of rows inside the <worksheet><table>
>node should be same as output generated xml using xsl
> >Do all worksheets have the same number of rows?
>No..they can be different since each worksheet can have different rows in 
>MS
>excel
> >Do you want all rows output on a single worksheet?
>No, I want the exact same structure as the input xml (in terms of 
>worksheets
>and rows...the only differnence would be they needed to be sorted accross
>all the worksheets by one column...for this example, I assure, there is 
>only
>one column in MS excel spreadsheet)
>
>
>
>
> >From: cknell@xxxxxxxxxx
> >Reply-To: xsl-list@xxxxxxxxxxxxxxxxxxxxxx
> >To: xsl-list@xxxxxxxxxxxxxxxxxxxxxx
> >Subject: RE: RE: RE:  Sorting Problem with Xml and Xsl
> >Date: Tue, 13 Mar 2007 11:41:41 -0400
> >
> >So you have several worksheets.
> >
> >How many rows do you have on each input worksheet?
>
>
>
> >How many rows do you want on each of the output worksheets?
> >Do all worksheets have the same number of rows?
> >Do you want all rows output on a single worksheet?
> >--
> >Charles Knell
> >cknell@xxxxxxxxxx - email
> >
> >
> >
> >-----Original Message-----
> >From:     Ram Shan <hellodck@xxxxxxxxxxx>
> >Sent:     Tue, 13 Mar 2007 09:26:44 -0600
> >To:       xsl-list@xxxxxxxxxxxxxxxxxxxxxx
> >Subject:  RE: RE:  Sorting Problem with Xml and Xsl
> >
> >I guess I should have explained a little better. I want I want to sort 
>all
> >the elements with in ALL worksheets (NOT JUST BY EACH worksheet 's Cell
> >values). please check the example
> >
> >Consider this input XML:
> >-------------------------------
> ><?xml-stylesheet  type="text/xsl" href="excelsort.xsl" ?>
> ><?mso-application progid='Excel.Sheet'?>
> ><s:Workbook xmlns:s="urn:schemas-microsoft-com:office:spreadsheet"
> >xmlns:x="urn:schemas-microsoft-com:office:excel"
> >xmlns:o="urn:schemas-microsoft-com:office:office">
> >   <s:Worksheet s:Name="Sample1">
> >     <s:Table>
> >       <s:Row>
> >         <s:Cell>
> >           <s:Data s:Type="String">B</s:Data>
> >         </s:Cell>
> >       </s:Row>
> >       <s:Row>
> >         <s:Cell>
> >           <s:Data s:Type="String">C</s:Data>
> >         </s:Cell>
> >       </s:Row>
> >     </s:Table>
> >   </s:Worksheet>
> >   <s:Worksheet s:Name="Sample2">
> >     <s:Table>
> >       <s:Row>
> >         <s:Cell>
> >           <s:Data s:Type="String">D</s:Data>
> >         </s:Cell>
> >       </s:Row>
> >       <s:Row>
> >         <s:Cell>
> >           <s:Data s:Type="String">A</s:Data>
> >         </s:Cell>
> >       </s:Row>
> >     </s:Table>
> >   </s:Worksheet>
> ></s:Workbook>
> >--------------------------
> >The output I wanted is:
> ><?xml-stylesheet  type="text/xsl" href="excelsort.xsl" ?>
> ><?mso-application progid='Excel.Sheet'?>
> ><s:Workbook xmlns:s="urn:schemas-microsoft-com:office:spreadsheet"
> >xmlns:x="urn:schemas-microsoft-com:office:excel"
> >xmlns:o="urn:schemas-microsoft-com:office:office">
> >   <s:Worksheet s:Name="Sample1">
> >     <s:Table>
> >       <s:Row>
> >         <s:Cell>
> >           <s:Data s:Type="String">A</s:Data>
> >         </s:Cell>
> >       </s:Row>
> >       <s:Row>
> >         <s:Cell>
> >           <s:Data s:Type="String">B</s:Data>
> >         </s:Cell>
> >       </s:Row>
> >     </s:Table>
> >   </s:Worksheet>
> >   <s:Worksheet s:Name="Sample2">
> >     <s:Table>
> >       <s:Row>
> >         <s:Cell>
> >           <s:Data s:Type="String">C</s:Data>
> >         </s:Cell>
> >       </s:Row>
> >       <s:Row>
> >         <s:Cell>
> >           <s:Data s:Type="String">D</s:Data>
> >         </s:Cell>
> >       </s:Row>
> >     </s:Table>
> >   </s:Worksheet>
> ></s:Workbook>
> >------------------------------
> >Please NOTE: the text in <s:Data s:Type="String"></s:Data> for each of 
>the
> >worksheets re-ordered and need to be sorted accross all worksheets in
> >workbook. Yes, the one you posted does WORK...but only WITH IN worksheet
> >nodes. Thanks for your time. Appreciate your help
> >
> >Thanks
> >Ram
> >
> >
> >
> >
> > >From: cknell@xxxxxxxxxx
> > >Reply-To: xsl-list@xxxxxxxxxxxxxxxxxxxxxx
> > >To: xsl-list@xxxxxxxxxxxxxxxxxxxxxx
> > >Subject: RE: RE:  Sorting Problem with Xml and Xsl
> > >Date: Tue, 13 Mar 2007 09:59:34 -0400
> > >
> > >With exception of
> > >1) omitting the s:Name attribute on the s:Worksheet element,
> > >2) omitting this processing instruction: <?mso-application
> > >progid='Excel.Sheet'?>
> > >
> > >the stylesheet I posted produces exactly what you gave as the desired
> > >output.
> > >
> > >If you add this:
> > >
> > >     <xsl:template match="s:Worksheet">
> > >       <s:Worksheet>
> > >       	<xsl:copy-of select="@*" /> <--- Add this element
> > >
> > >you will get a copy of any attributes for the s:Worksheet element in 
>the
> > >output.
> > >
> > >Each worksheet gets its rows sorted based on the string value of the
> >s:Data
> > >elements.
> > >
> > >If this is not what you want, please state explicitly what it is that 
>you
> > >want, or show an example of the output you want. The output generated 
>by
> > >the stylesheet I posted does match what you have already given as the
> > >desired output, so perhaps you should examine the sample output closely
> >to
> > >determine if you have stated the requirement correctly.
> > >
> > >
> > >--
> > >Charles Knell
> > >cknell@xxxxxxxxxx - email
> > >
> > >
> > >
> > >-----Original Message-----
> > >From:     Ram Shan <hellodck@xxxxxxxxxxx>
> > >Sent:     Mon, 12 Mar 2007 15:06:51 -0600
> > >To:       xsl-list@xxxxxxxxxxxxxxxxxxxxxx
> > >Subject:  RE:  Sorting Problem with Xml and Xsl
> > >
> > >Thanks for the quick response. Please note that I am a newbie to xsl 
>and
> > >just trying to get a feel of it. But, with the solution you posted, it
> > >sorts
> > >with in the worksheet nodes only. I want to sort all the  <s:Data
> > >s:Type="String"></s:Data> elements text with in all the worksheets, so
> >for
> > >example
> > >my input file
> > >-------------------
> > ><?xml version='1.0'?>
> > ><?mso-application progid='Excel.Sheet'?>
> > ><s:Workbook xmlns:x="urn:schemas-microsoft-com:office:excel"
> > >xmlns:o="urn:schemas-microsoft-com:office:office"
> > >xmlns:s="urn:schemas-microsoft-com:office:spreadsheet">
> > >   <s:Worksheet s:Name="Sample1">
> > >     <s:Table>
> > >       <s:Row>
> > >         <s:Cell>
> > >           <s:Data s:Type="String">RJTGOVBVHX</s:Data>
> > >         </s:Cell>
> > >       </s:Row>
> > >       <s:Row>
> > >         <s:Cell>
> > >           <s:Data s:Type="String">IEXGTQYSBQ</s:Data>
> > >         </s:Cell>
> > >       </s:Row>
> > >     </s:Table>
> > >   </s:Worksheet>
> > >   <s:Worksheet s:Name="Sample2">
> > >     <s:Table>
> > >       <s:Row>
> > >         <s:Cell>
> > >           <s:Data s:Type="String">WUGRDFHDTV</s:Data>
> > >         </s:Cell>
> > >       </s:Row>
> > >       <s:Row>
> > >         <s:Cell>
> > >           <s:Data s:Type="String">PQFWLWQYQW</s:Data>
> > >         </s:Cell>
> > >       </s:Row>
> > >     </s:Table>
> > >   </s:Worksheet>
> > ></s:Workbook>
> > >
> > >the output that I want to show is
> > >--------------------------------------------
> > ><?xml version='1.0'?>
> > ><?mso-application progid='Excel.Sheet'?>
> > ><s:Workbook xmlns:x="urn:schemas-microsoft-com:office:excel"
> > >xmlns:o="urn:schemas-microsoft-com:office:office"
> > >xmlns:s="urn:schemas-microsoft-com:office:spreadsheet">
> > >   <s:Worksheet s:Name="Sample1">
> > >     <s:Table>
> > >       <s:Row>
> > >         <s:Cell>
> > >           <s:Data s:Type="String">IEXGTQYSBQ</s:Data>
> > >         </s:Cell>
> > >       </s:Row>
> > >       <s:Row>
> > >         <s:Cell>
> > >           <s:Data s:Type="String">PQFWLWQYQW</s:Data>
> > >         </s:Cell>
> > >       </s:Row>
> > >     </s:Table>
> > >   </s:Worksheet>
> > >   <s:Worksheet s:Name="Sample2">
> > >     <s:Table>
> > >       <s:Row>
> > >         <s:Cell>
> > >           <s:Data s:Type="String">RJTGOVBVHX</s:Data>
> > >         </s:Cell>
> > >       </s:Row>
> > >       <s:Row>
> > >         <s:Cell>
> > >           <s:Data s:Type="String">WUGRDFHDTV</s:Data>
> > >         </s:Cell>
> > >       </s:Row>
> > >     </s:Table>
> > >   </s:Worksheet>
> > ></s:Workbook>
> > >
> > >
> > >Thanks
> > >Ram
> > >
> > >
> > > >From: cknell@xxxxxxxxxx
> > > >Reply-To: xsl-list@xxxxxxxxxxxxxxxxxxxxxx
> > > >To: xsl-list@xxxxxxxxxxxxxxxxxxxxxx
> > > >Subject: RE:  Sorting Problem with Xml and Xsl
> > > >Date: Mon, 12 Mar 2007 16:25:12 -0400
> > > >
> > > >Your stylesheet doesn't match your input document. You left out 
>several
> > > >layers of nested elements.
> > > >
> > > ><?xml version="1.0"?>
> > > ><xsl:stylesheet version="1.0"
> > > >   xmlns:s="urn:schemas-microsoft-com:office:spreadsheet"
> > > >   xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
> > > >   <xsl:strip-space elements="*" />
> > > >   <xsl:output method="xml" indent="yes" encoding="UTF-16" />
> > > >
> > > >     <xsl:template match="/">
> > > >         <xsl:apply-templates />
> > > >     </xsl:template>
> > > >
> > > >     <xsl:template match="s:Workbook">
> > > >       <s:Workbook xmlns:x="urn:schemas-microsoft-com:office:excel"
> > > >           xmlns:o="urn:schemas-microsoft-com:office:office"
> > > >           xmlns:s="urn:schemas-microsoft-com:office:spreadsheet">
> > > >         <xsl:apply-templates />
> > > >       </s:Workbook>
> > > >     </xsl:template>
> > > >
> > > >     <xsl:template match="s:Worksheet">
> > > >       <s:Worksheet>
> > > >         <xsl:apply-templates />
> > > >       </s:Worksheet>
> > > >     </xsl:template>
> > > >
> > > >     <xsl:template match="s:Table">
> > > >       <s:Table>
> > > >         <xsl:for-each select="s:Row">
> > > >           <xsl:sort select="s:Cell/s:Data" />
> > > >           <xsl:copy-of select="." />
> > > >         </xsl:for-each>
> > > >       </s:Table>
> > > >     </xsl:template>
> > > >
> > > ></xsl:stylesheet>
> > > >--
> > > >Charles Knell
> > > >cknell@xxxxxxxxxx - email
> > > >
> > > >
> > > >
> > > >-----Original Message-----
> > > >From:     Ram Shan <hellodck@xxxxxxxxxxx>
> > > >Sent:     Mon, 12 Mar 2007 13:27:04 -0600
> > > >To:       xsl-list@xxxxxxxxxxxxxxxxxxxxxx
> > > >Subject:   Sorting Problem with Xml and Xsl
> > > >
> > > >I can not get this sorting to work. any help is greatly appreciated. 
>I
> >am
> > > >using MSXml parser and c# to transform. Thanks
> > > >Here is Sample XML
> > > >-------------
> > > ><?xml-stylesheet  type="text/xsl" href="rowextrated2.xsl" ?>
> > > ><?mso-application progid='Excel.Sheet'?>
> > > ><s:Workbook xmlns:x="urn:schemas-microsoft-com:office:excel"
> > > >xmlns:o="urn:schemas-microsoft-com:office:office"
> > > >xmlns:s="urn:schemas-microsoft-com:office:spreadsheet">
> > > >   <s:Worksheet s:Name="Sample1">
> > > >     <s:Table>
> > > >       <s:Row>
> > > >         <s:Cell>
> > > >           <s:Data s:Type="String">Maa</s:Data>
> > > >         </s:Cell>
> > > >       </s:Row>
> > > >       <s:Row>
> > > >         <s:Cell>
> > > >           <s:Data s:Type="String">Baa</s:Data>
> > > >         </s:Cell>
> > > >       </s:Row>
> > > >       <s:Row>
> > > >         <s:Cell>
> > > >           <s:Data s:Type="String">Aaa</s:Data>
> > > >         </s:Cell>
> > > >       </s:Row>
> > > >     </s:Table>
> > > >   </s:Worksheet>
> > > >   <s:Worksheet s:Name="Sample2">
> > > >     <s:Table>
> > > >       <s:Row>
> > > >         <s:Cell>
> > > >           <s:Data s:Type="String">Caa</s:Data>
> > > >         </s:Cell>
> > > >       </s:Row>
> > > >       <s:Row>
> > > >         <s:Cell>
> > > >           <s:Data s:Type="String">Qaa</s:Data>
> > > >         </s:Cell>
> > > >       </s:Row>
> > > >       <s:Row>
> > > >         <s:Cell>
> > > >           <s:Data s:Type="String">Zaa</s:Data>
> > > >         </s:Cell>
> > > >       </s:Row>
> > > >     </s:Table>
> > > >   </s:Worksheet>
> > > ></s:Workbook>
> > > >
> > > >
> > > >--------------My Xsl looks like this. But the problem is it does not
> >take
> > > >get me the sorted out . please check below
> > > ><?xml version="1.0" encoding="utf-8" ?>
> > > ><xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
> > > >xmlns:s="urn:schemas-microsoft-com:office:spreadsheet" version="1.0">
> > > >   <xsl:output method="xml" indent="yes" encoding="utf-16"/>
> > > >   <xsl:template match="s:Workbook">
> > > >     <xsl:copy>
> > > >       <xsl:apply-templates>
> > > >         <xsl:sort data-type="text" select="s:Cell/s:Data"
> > > >case-order="lower-first" />
> > > >       </xsl:apply-templates>
> > > >     </xsl:copy>
> > > >   </xsl:template>
> > > >
> > > >   <xsl:template match="*">
> > > >     <xsl:copy>
> > > >       <xsl:apply-templates/>
> > > >     </xsl:copy>
> > > >   </xsl:template>
> > > ></xsl:stylesheet>
> > > >
> > > >
> > > >---Desired output
> > > >
> > > >
> > > ><?xml-stylesheet  type="text/xsl" href="rowextrated2.xsl" ?>
> > > ><?mso-application progid='Excel.Sheet'?>
> > > ><s:Workbook xmlns:x="urn:schemas-microsoft-com:office:excel"
> > > >xmlns:o="urn:schemas-microsoft-com:office:office"
> > > >xmlns:s="urn:schemas-microsoft-com:office:spreadsheet">
> > > >   <s:Worksheet s:Name="Sample1">
> > > >     <s:Table>
> > > >       <s:Row>
> > > >         <s:Cell>
> > > >           <s:Data s:Type="String">Aaa</s:Data>
> > > >         </s:Cell>
> > > >       </s:Row>
> > > >       <s:Row>
> > > >         <s:Cell>
> > > >           <s:Data s:Type="String">Baa</s:Data>
> > > >         </s:Cell>
> > > >       </s:Row>
> > > >       <s:Row>
> > > >         <s:Cell>
> > > >           <s:Data s:Type="String">Caa</s:Data>
> > > >         </s:Cell>
> > > >       </s:Row>
> > > >     </s:Table>
> > > >   </s:Worksheet>
> > > >   <s:Worksheet s:Name="Sample2">
> > > >     <s:Table>
> > > >       <s:Row>
> > > >         <s:Cell>
> > > >           <s:Data s:Type="String">Maa</s:Data>
> > > >         </s:Cell>
> > > >       </s:Row>
> > > >       <s:Row>
> > > >         <s:Cell>
> > > >           <s:Data s:Type="String">Qaa</s:Data>
> > > >         </s:Cell>
> > > >       </s:Row>
> > > >       <s:Row>
> > > >         <s:Cell>
> > > >           <s:Data s:Type="String">Zaa</s:Data>
> > > >         </s:Cell>
> > > >       </s:Row>
> > > >     </s:Table>
> > > >   </s:Worksheet>
> > > ></s:Workbook>
> > > >
> > > >_________________________________________________________________
> > > >Play Flexicon: the crossword game that feeds your brain. PLAY now for
> > > >FREE. 
> > > >   
>http://zone.msn.com/en/flexicon/default.htm?icid=flexicon_hmtagline
> > > >
> > >
> > >_________________________________________________________________
> > >Find a local pizza place, movie theater, and more?.then map the best
> >route!
> > >http://maps.live.com/?icid=hmtag1&FORM=MGAC01
> > >
> >
> >_________________________________________________________________
> >Find a local pizza place, movie theater, and more?.then map the best 
>route!
> >http://maps.live.com/?icid=hmtag1&FORM=MGAC01
> >
>
>_________________________________________________________________
>The average US Credit Score is 675. The cost to see yours: $0 by Experian.
>http://www.freecreditreport.com/pm/default.aspx?sc=660600&bcd=EMAILFOOTERAVERAGE
>

_________________________________________________________________
Rates near 39yr lows!  $430K Loan for $1,399/mo - Paying Too Much? Calculate 
new payment 
http://www.lowermybills.com/lre/index.jsp?sourceid=lmb-9632-18226&moid=7581

Current Thread

PURCHASE STYLUS STUDIO ONLINE TODAY!

Purchasing Stylus Studio from our online shop is Easy, Secure and Value Priced!

Buy Stylus Studio Now

Download The World's Best XML IDE!

Accelerate XML development with our award-winning XML IDE - Download a free trial today!

Don't miss another message! Subscribe to this list today.
Email
First Name
Last Name
Company
Subscribe in XML format
RSS 2.0
Atom 0.3
Site Map | Privacy Policy | Terms of Use | Trademarks
Free Stylus Studio XML Training:
W3C Member
Stylus Studio® and DataDirect XQuery ™are products from DataDirect Technologies, is a registered trademark of Progress Software Corporation, in the U.S. and other countries. © 2004-2013 All Rights Reserved.