xsl-list
[Top] [All Lists]

RE: [xsl] Sorting Problem with Xml and Xsl

2007-03-13 09:40:57
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(_at_)onebox(_dot_)com - email



-----Original Message-----
From:     Ram Shan <hellodck(_at_)hotmail(_dot_)com>
Sent:     Tue, 13 Mar 2007 09:54:50 -0600
To:       xsl-list(_at_)lists(_dot_)mulberrytech(_dot_)com
Subject:  RE: RE: RE: [xsl] 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(_at_)onebox(_dot_)com
Reply-To: xsl-list(_at_)lists(_dot_)mulberrytech(_dot_)com
To: xsl-list(_at_)lists(_dot_)mulberrytech(_dot_)com
Subject: RE: RE: RE: [xsl] 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(_at_)onebox(_dot_)com - email



-----Original Message-----
From:     Ram Shan <hellodck(_at_)hotmail(_dot_)com>
Sent:     Tue, 13 Mar 2007 09:26:44 -0600
To:       xsl-list(_at_)lists(_dot_)mulberrytech(_dot_)com
Subject:  RE: RE: [xsl] 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(_at_)onebox(_dot_)com
Reply-To: xsl-list(_at_)lists(_dot_)mulberrytech(_dot_)com
To: xsl-list(_at_)lists(_dot_)mulberrytech(_dot_)com
Subject: RE: RE: [xsl] 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(_at_)onebox(_dot_)com - email



-----Original Message-----
From:     Ram Shan <hellodck(_at_)hotmail(_dot_)com>
Sent:     Mon, 12 Mar 2007 15:06:51 -0600
To:       xsl-list(_at_)lists(_dot_)mulberrytech(_dot_)com
Subject:  RE: [xsl] 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(_at_)onebox(_dot_)com
Reply-To: xsl-list(_at_)lists(_dot_)mulberrytech(_dot_)com
To: xsl-list(_at_)lists(_dot_)mulberrytech(_dot_)com
Subject: RE: [xsl] 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(_at_)onebox(_dot_)com - email



-----Original Message-----
From:     Ram Shan <hellodck(_at_)hotmail(_dot_)com>
Sent:     Mon, 12 Mar 2007 13:27:04 -0600
To:       xsl-list(_at_)lists(_dot_)mulberrytech(_dot_)com
Subject:  [xsl] 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


--~------------------------------------------------------------------
XSL-List info and archive:  http://www.mulberrytech.com/xsl/xsl-list
To unsubscribe, go to: http://lists.mulberrytech.com/xsl-list/
or e-mail: 
<mailto:xsl-list-unsubscribe(_at_)lists(_dot_)mulberrytech(_dot_)com>
--~--




--~------------------------------------------------------------------
XSL-List info and archive:  http://www.mulberrytech.com/xsl/xsl-list
To unsubscribe, go to: http://lists.mulberrytech.com/xsl-list/
or e-mail: 
<mailto:xsl-list-unsubscribe(_at_)lists(_dot_)mulberrytech(_dot_)com>
--~--


_________________________________________________________________
Find a local pizza place, movie theater, and more?.then map the best 
route!
http://maps.live.com/?icid=hmtag1&FORM=MGAC01


--~------------------------------------------------------------------
XSL-List info and archive:  http://www.mulberrytech.com/xsl/xsl-list
To unsubscribe, go to: http://lists.mulberrytech.com/xsl-list/
or e-mail: 
<mailto:xsl-list-unsubscribe(_at_)lists(_dot_)mulberrytech(_dot_)com>
--~--




--~------------------------------------------------------------------
XSL-List info and archive:  http://www.mulberrytech.com/xsl/xsl-list
To unsubscribe, go to: http://lists.mulberrytech.com/xsl-list/
or e-mail: 
<mailto:xsl-list-unsubscribe(_at_)lists(_dot_)mulberrytech(_dot_)com>
--~--


_________________________________________________________________
Find a local pizza place, movie theater, and more?.then map the best route!
http://maps.live.com/?icid=hmtag1&FORM=MGAC01


--~------------------------------------------------------------------
XSL-List info and archive:  http://www.mulberrytech.com/xsl/xsl-list
To unsubscribe, go to: http://lists.mulberrytech.com/xsl-list/
or e-mail: 
<mailto:xsl-list-unsubscribe(_at_)lists(_dot_)mulberrytech(_dot_)com>
--~--




--~------------------------------------------------------------------
XSL-List info and archive:  http://www.mulberrytech.com/xsl/xsl-list
To unsubscribe, go to: http://lists.mulberrytech.com/xsl-list/
or e-mail: 
<mailto:xsl-list-unsubscribe(_at_)lists(_dot_)mulberrytech(_dot_)com>
--~--


_________________________________________________________________
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


--~------------------------------------------------------------------
XSL-List info and archive:  http://www.mulberrytech.com/xsl/xsl-list
To unsubscribe, go to: http://lists.mulberrytech.com/xsl-list/
or e-mail: <mailto:xsl-list-unsubscribe(_at_)lists(_dot_)mulberrytech(_dot_)com>
--~--




--~------------------------------------------------------------------
XSL-List info and archive:  http://www.mulberrytech.com/xsl/xsl-list
To unsubscribe, go to: http://lists.mulberrytech.com/xsl-list/
or e-mail: <mailto:xsl-list-unsubscribe(_at_)lists(_dot_)mulberrytech(_dot_)com>
--~--