473,570 Members | 2,937 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

XSL: selecting columns in Excel XML

Question: How to select columns from Excel-generated XML when some cells
are empty.

I've found examples where rows are to be selected, but I can't seem to
extrapolate from that to selecting columns when some cells are empty. Is
there a way to use the ss:Index to account for the missing <Cell elements?

Thank you for any suggestions.

=============== =====
XML input
=============== =====

<?xml version="1.0"?>
<Workbook xmlns="urn:sche mas-microsoft-com:office:spre adsheet"
xmlns:o="urn:sc hemas-microsoft-com:office:offi ce"
xmlns:x="urn:sc hemas-microsoft-com:office:exce l"
xmlns:ss="urn:s chemas-microsoft-com:office:spre adsheet"
xmlns:html="htt p://www.w3.org/TR/REC-html40">
<DocumentProper ties xmlns="urn:sche mas-microsoft-com:office:offi ce">
<Author>Boss</Author>
<LastAuthor>Bos s</LastAuthor>
<Created>2004-11-06T23:49:56Z</Created>
<LastSaved>20 04-11-06T23:50:53Z</LastSaved>
<Version>10.662 6</Version>
</DocumentPropert ies>
<OfficeDocument Settings xmlns="urn:sche mas-microsoft-com:office:offi ce">
<DownloadCompon ents/>
<LocationOfComp onents HRef="/"/>
</OfficeDocumentS ettings>
<ExcelWorkboo k xmlns="urn:sche mas-microsoft-com:office:exce l">
<WindowHeight>1 3680</WindowHeight>
<WindowWidth>17 100</WindowWidth>
<WindowTopX>240 </WindowTopX>
<WindowTopY>1 5</WindowTopY>
<ProtectStructu re>False</ProtectStructur e>
<ProtectWindows >False</ProtectWindows>
</ExcelWorkbook>
<Styles>
<Style ss:ID="Default" ss:Name="Normal ">
<Alignment ss:Vertical="Bo ttom"/>
<Borders/>
<Font/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
<Style ss:ID="s21">
<Font x:Family="Swiss " ss:Bold="1"/>
</Style>
</Styles>
<Worksheet ss:Name="Sheet1 ">
<Table ss:ExpandedColu mnCount="7" ss:ExpandedRowC ount="4"
x:FullColumns=" 1"
x:FullRows="1">
<Row>
<Cell ss:StyleID="s21 "><Data ss:Type="String ">Rate</Data></Cell>
<Cell><Data ss:Type="String ">Price</Data></Cell>
<Cell><Data ss:Type="String ">Rounded</Data></Cell>
<Cell><Data ss:Type="String ">AOT adj.</Data></Cell>
<Cell><Data ss:Type="String ">Total</Data></Cell>
<Cell><Data ss:Type="String ">Florida</Data></Cell>
<Cell><Data ss:Type="String ">TX Retail</Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type="Number ">4</Data></Cell>
<Cell><Data ss:Type="Number ">66</Data></Cell>
<Cell><Data ss:Type="Number ">99</Data></Cell>
<Cell ss:Index="5"><D ata ss:Type="Number ">5.125</Data></Cell>
<Cell><Data ss:Type="Number ">8.375</Data></Cell>
<Cell><Data ss:Type="Number ">8.625</Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type="Number ">4.125</Data></Cell>
<Cell><Data ss:Type="Number ">77</Data></Cell>
<Cell><Data ss:Type="Number ">22</Data></Cell>
<Cell ss:Index="5"><D ata ss:Type="Number ">5.125</Data></Cell>
<Cell><Data ss:Type="Number ">8.375</Data></Cell>
<Cell><Data ss:Type="Number ">8.625</Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type="Number ">4.25</Data></Cell>
<Cell><Data ss:Type="Number ">88</Data></Cell>
<Cell><Data ss:Type="Number ">98</Data></Cell>
<Cell><Data ss:Type="Number ">0.25</Data></Cell>
<Cell><Data ss:Type="Number ">1.75</Data></Cell>
<Cell><Data ss:Type="Number ">5</Data></Cell>
<Cell><Data ss:Type="Number ">5.25</Data></Cell>
</Row>
</Table>
<WorksheetOptio ns xmlns="urn:sche mas-microsoft-com:office:exce l">
<Selected/>
<Panes>
<Pane>
<Number>3</Number>
<ActiveRow>10 </ActiveRow>
<ActiveCol>2</ActiveCol>
</Pane>
</Panes>
<ProtectObjects >False</ProtectObjects>
<ProtectScenari os>False</ProtectScenario s>
</WorksheetOption s>
</Worksheet>
</Workbook>

=============== ======
XSL stylesheet
=============== ======
<?xml version='1.0'?>
<xsl:styleshe et xmlns:xsl="http ://www.w3.org/1999/XSL/Transform"
version="1.0"
xmlns:ss="urn:s chemas-microsoft-com:office:spre adsheet"
xmlns:o="urn:sc hemas-microsoft-com:office:offi ce"
xmlns:x="urn:sc hemas-microsoft-com:office:exce l"

exclude-result-prefixes=" ss x o"

<xsl:output encoding="ISO-8859-1" indent="yes" />

<xsl:variable name="root" select="/" />
<!-- -->
<!-- =============== =============== =============== ========== -->
<!-- =============== =============== =============== ========== -->
<xsl:template match="/">
<xsl:apply-templates />

</xsl:template>

<xsl:variable name="TFM">${TF M}</xsl:variable>
<xsl:variable name="DAT">${DA T}\</xsl:variable>
<xsl:variable name="GRA">${GR A}</xsl:variable>

<!-- =============== =============== =============== ========== -->

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

<xsl:template match="Workshee t">
<xsl:apply-templates />
</xsl:template>

<!-- =============== =============== =============== ========== -->

<xsl:template match="ss:Table ">
<Table>

<xsl:for-each select="./ss:Row[position() = 1]/ss:Cell">
<xsl:variable name="vColID" select="positio n()"/>
<xsl:choose>
<xsl:when test="position( ) &gt; 5">

<Branch>
<xsl:attribut e name="Name"><xs l:value-of select="."/></xsl:attribute>
<xsl:attribut e name="ColID"><x sl:value-of select="$vColID "/></xsl:attribute>

<Rates>
<xsl:for-each
select="//ss:Workbook/ss:Worksheet[@ss:Name='Sheet 1']/ss:Table/ss:Row[position()
&gt; 1]">
<Rate>
<xsl:attribut e name="vRateID"> <xsl:value-of select="positio n()"/>
</xsl:attribute>
<xsl:attribut e name="vRate"><x sl:value-of
select="./ss:Cell/ss:Data"/></xsl:attribute>

<xsl:attribut e name="vPrice">< xsl:value-of
select="./ss:Cell[$vColID]/ss:Data"/></xsl:attribute>

</Rate>
<xsl:variable name="vRateID" select="positio n()"/>
</xsl:for-each>

</Rates>

</Branch></xsl:when>
</xsl:choose>
</xsl:for-each>
</Table>
</xsl:template>
<!-- =============== =============== =============== ========== -->
<!-- =============== =============== =============== ========== -->
<xsl:template match="text()" />

</xsl:stylesheet>

=============== ========
Output generated
=============== ========

<?xml version="1.0" encoding="ISO-8859-1"?>
<Table>
<Branch Name="Florida" ColID="6">
<Rates>
<Rate vRateID="1" vRate="4" vPrice="8.625"/>
<Rate vRateID="2" vRate="4.125" vPrice="8.625"/>
<Rate vRateID="3" vRate="4.25" vPrice="5"/>
</Rates>
</Branch>
<Branch Name="TX Retail" ColID="7">
<Rates>
<Rate vRateID="1" vRate="4" vPrice=""/>
<Rate vRateID="2" vRate="4.125" vPrice=""/>
<Rate vRateID="3" vRate="4.25" vPrice="5.25"/>
</Rates>
</Branch>
</Table>

=============== ========
Output intended
=============== ========
<?xml version="1.0" encoding="ISO-8859-1"?>
<Table>
<Branch Name="Florida" ColID="6">
<Rates>
<Rate vRateID="1" vRate="4" vPrice="8.375"/>
<Rate vRateID="2" vRate="4.125" vPrice="8.375"/>
<Rate vRateID="3" vRate="4.25" vPrice="5"/>
</Rates>
</Branch>
<Branch Name="TX Retail" ColID="7">
<Rates>
<Rate vRateID="1" vRate="4" vPrice="8.625"/>
<Rate vRateID="2" vRate="4.125" vPrice="8.625"/>
<Rate vRateID="3" vRate="4.25" vPrice="5.25"/>
</Rates>
</Branch>
</Table>

Jul 20 '05 #1
5 3344
No suggestions? Or is my question confusing, in which case I can try to
elaborate a little more.

Thx

Axial wrote:
Question: How to select columns from Excel-generated XML when some cells
are empty.

I've found examples where rows are to be selected, but I can't seem to
extrapolate from that to selecting columns when some cells are empty. Is
there a way to use the ss:Index to account for the missing <Cell elements?

Thank you for any suggestions.


Jul 20 '05 #2
> No suggestions? Or is my question confusing, in which case I can try to
elaborate a little more.


Hi,

Your question was cristal-clear. The solution only proved to be quite difficult.
I have here one type of solution. It uses result tree fragments, so you'd have to declare xsl version 1.1 (which is actually depricated). Tested with Saxon.

use this to recall the price out of column $vColID:

<xsl:attribut e name="vPrice">
<xsl:call-template name="FetchRow" >
<xsl:with-param name="index" select="$vColID "/>
<xsl:with-param name="row"><xsl :copy-of select="*"/></xsl:with-param>
</xsl:call-template>
</xsl:attribute>

This template must be included:

<xsl:template name="FetchRow" >
<xsl:param name="index"/>
<xsl:param name="row"/>
<xsl:param name="offset" select="0"/>
<xsl:variable name="count">
<xsl:choose>
<xsl:when test="$row/ss:Cell[1]/@ss:Index">
<xsl:value-of select="$row/ss:Cell[1]/@ss:Index"/>
</xsl:when>
<xsl:otherwis e>
<xsl:value-of select="$offset + 1"/>
</xsl:otherwise>
</xsl:choose>
</xsl:variable>
<xsl:if test="$count = $index"><xsl:va lue-of select="$row/ss:Cell[1]/ss:Data"/></xsl:if>
<xsl:if test="$count &lt; $index">
<xsl:call-template name="FetchRow" >
<xsl:with-param name="index" select="$index"/>
<xsl:with-param name="row"><xsl :copy-of select="$row/ss:Cell[position() != 1]"/></xsl:with-param>
<xsl:with-param name="offset">< xsl:copy-of select="$count"/></xsl:with-param>
</xsl:call-template>
</xsl:if>
</xsl:template>

regards,
--
Joris Gillis (http://www.ticalc.org/cgi-bin/acct-v...i?userid=38041)
Ceterum censeo XML omnibus esse utendum
Jul 20 '05 #3
It would make more sense if that template 'FetchRow' was called 'FetchColumn' :)

--
Joris Gillis (http://www.ticalc.org/cgi-bin/acct-v...i?userid=38041)
Ceterum censeo XML omnibus esse utendum
Jul 20 '05 #4
Joris, thank you for your solution. Under any template name ;-) it would
do exactly what I need. Unfortunately I have to use 1.0 and there isn't
a node-set() or nodeset() extension function. After looking at your
example I can certainly see how nodesets are so valuable a concept.

Joris Gillis wrote:
It would make more sense if that template 'FetchRow' was called
'FetchColumn' :)


Jul 20 '05 #5
I'll run two separate transforms, so that I can use a 1.1 parser for the
first pass, and go back to the 1.0 for the rest of the job.

Thank you again for your assistance.

Joris Gillis wrote:
It would make more sense if that template 'FetchRow' was called
'FetchColumn' :)


Jul 20 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
4936
by: Beowulf | last post by:
Hi, I have an XML file generated by a third party (and therefore unchangable) program. 1st line in it is <?xml version="1.0" encoding="us-ascii"?> and down in the depths of the xml I have a element <FirstName>Françoise</FirstName> I have an xsl file I've created to attempt to export this xml to CSV.
3
4608
by: Joakim Olesen | last post by:
Hi I have an issue with an asp page that reads from an Excel file. I read the file using OLEDB. I create a new Excel file using "MS Excel 2003" and put the following data into a sheet called "Ark1" (2 columns and 5 rows): 1234 reer 1234 feb 4467 heerh 4467 123123
3
1128
by: jl | last post by:
Hi everybody! I have an interesting problem - and so far, I haven't been able to solve it. Maybe somebody else has an idea... I have a source xml structure that comes from a legacy system. It is somewhat similar to a database structure: <data> <fieldnames>
0
1507
by: rahulash | last post by:
I have a JSP showing data retreived from a database. This JSP is styled based on an XSL. The database returns data like student name, course, student remarks. I intend to add a checkbox which when checked would show student remarks and when unchecked would hide them. To do this I have made changes to the stylesheet. This is how the...
2
1961
by: shapper | last post by:
Hello, I am for days trying to apply a XSL transformation to a XML file and display the result in a the browser. I am using Asp.Net 2.0. Please, could someone post just a simple code example, either C# or VB.NET, that I can try. Thank You Very Much,
0
999
by: chat10 | last post by:
I need values on one of the resulting columns to be formatted with preceeding zeros (eg "00002"). And in the XSL transformation I used <xsl:variable name="NAccountNo" select="AccountNo" /> - <TD> <xsl:value-of select="format-number($NAccountNo, "000")" /> </TD> and since this did not work i tried <xsl:variable name="NAccountNo"...
2
5181
budigila
by: budigila | last post by:
Hiya peeps, Okies, I have been trying to work this out for a while now to no avail... I am a beginner to this whole coding thing but have made great strides in my project. Basically what I am trying to do is to convert an .xml using a .xsl to and using a java script to output it into a readable format within excel. I have been able to...
4
3612
by: ShadowLocke | last post by:
Hi, Im not very experienced with XSL, this is my first run in with it. I found an XSL file that converts a dataset to an excel readable format. The problem I have is when the dataset has a null value for a column, it does not add an empty element for the column. Is there any way I can detect this? Excel.xsl: <xsl:stylesheet...
0
1469
by: zamba | last post by:
hi im new with xsl and i have to process a xsl with a lot of xml (about 50.000) the transform is slow ..and i want to optimize my xsl . here is the example so if anybody could give some advices to me i will appreciatte : <?xml version="1.0" encoding="UTF-8"?> <xsl:stylesheet xmlns:fox="http://xml.apache.org/fop/extensions"...
0
7726
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7636
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7948
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
1
7700
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
6329
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
0
3682
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3671
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1238
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
971
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.