XSLT Excel to xml locate a cell

anterior · October 5th, 2014, 09:24 PM

I'm attempting to select 1 cell only when converting Excel to xml via XSLT.
I can't seem to get the syntax to work for me.
The Excel sheet has headings on row 11 and the data flows from row 12 and on.
I'm trying to select data from 1 cell in the header section (G7) to apply to my conversion.
The rest of the template works ok but I need to refer to this cell in the "for-each" element which loads the data rows from 12.
How do I format the syntax to select data from 1 cell.

<ChargeAccount>
<xsl:value-of select="Cell[@FieldNo = G7]">
</ChargeAccount>

Hope someone can help!
Thankyou

samjudson · October 6th, 2014, 03:52 AM

We are not familiar with the XML format of Excel files, so perhaps post a snippet of the XML with the data you are trying to select.

If G7 is a string then you likely would have to enclose it in single quotes though.

anterior · October 6th, 2014, 05:16 AM

Thankyou for replying.
Sorry if I've been unclear, I'll try to elaborate!
The source data is in an normal .xls Excel sheet and it contains an Invoice.
I'm trying to extract the data from Excel using an xslt to create an xml for import into another system.
There are always 11 header rows. A ChargeAccount number is stored in the Excel Sheet at G7.
I am selecting data from row 12 to however many rows there are, and need to add the value in G7 to the data.
Everything else works ok, I'm trying to add the value in G7 to the rows with the xslt.

Code:

xmlns:xsl="http://www.w3.org/1999/XSL/Transform">  
<xsl:output method="xml" indent="yes" />  
<xsl:param name="Database" />  
<xsl:param name="FS" />  
<xsl:template match="/">    

<InvoiceImport>   
<xsl:attribute name="Database"><xsl:value-of select="$Database" /></xsl:attribute>     
<xsl:attribute name="FS"><xsl:value-of select="$FS" /></xsl:attribute>  

<!-- I think I need to find the G7 value here-->

<LineItems><xsl:for-each select="//Row[position() &gt; 11]"> 

<InvoiceLines>
<!--I need to use it here ? -->
<ChargeAccount><xsl:value-of select="G7 value ?????" /></ChargeAccount>
<CustomerNo><xsl:value-of select="Cell[@FieldNo = 7]" /></CustomerNo>
<InvoiceDate><xsl:value-of select="Cell[@FieldNo = 6]" /></InvoiceDate>    
<Reference><xsl:value-of select="Cell[@FieldNo = 8]" /></Reference>    
<Service><xsl:value-of select="Cell[@FieldNo = 4]" /></Service>  
<NetCharge><xsl:value-of select="Cell[@FieldNo = 27]" /></NetCharge>    
<TaxCharge><xsl:value-of select="Cell[@FieldNo = 29]" /></TaxCharge>  
</InvoiceLines>
</LineItems>
</InvoiceImport>

samjudson · October 6th, 2014, 07:59 AM

I asked what an Excel XML file looked like, and you have posted your XSLT. Still waiting on a snippet of XML...

anterior · October 6th, 2014, 08:27 AM

There is no Excel xml involved it's a straight Excel .xls file and converting it to an xml via the xslt.

I hope I'm on the right track here.

The end xml would be something like:

<ChargeAccount>1234</ChargeAccount>
<CustomerNo>ade4556</CustomerNo>
<InvoiceDate>2014-10-06</InvoiceDate>
<Reference>PR66y</Reference>
<Service>Comprehensive</Service>
<NetCharge>85.00</NetCharge>
<TaxCharge>8.5</TaxCharge>

<ChargeAccount>1234</ChargeAccount>
<CustomerNo>bgh7436</CustomerNo>
<InvoiceDate>2014-10-06</InvoiceDate>
<Reference>Michael</Reference>
<Service>Level3</Service>
<NetCharge>45.00</NetCharge>
<TaxCharge>4.5</TaxCharge>

It's the constant ChargeAccount in the Excel (G7) file I'm trying to add to the xml.

samjudson · October 6th, 2014, 08:39 AM

XSLT can't process raw Excel files, so there must be something in between converting them to XML.

You are using things like "//Row" and "Cell" which will be XML elements in the XML input to the XSLT file. If you don't know how that is being generated or what it looks like then it will be very hard for anyone to help.