CODEWITHSUNDEEP
xmlexcelxslt
mossini
mossini

Reputation: 41

add hyperlink to excel cell via xslt

I have a xml file and a xslt stylesheet file.

How can i add a hyperlink only to a specific excel cell in the xslt file by specific xml field.

I have the following pseudocode but no idea how to set this in the xslt file.

if (xmlAttribute =="Beleg")
{   href = "http://www.xyz.de/beleg.php?beleg=$fieldvalue
}
else if (xmlAttribute == "Lieferant")
{  href = "http://www.xyz.de/lieferant.php?lieferant=$fieldvalue
}
else
{  // no link
}

Can somebody please help me.

Here my xml and xslt

XML:

<xml>
 <s:Schema id="RowsetSchema" xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882">
<s:ElementType name="row" content="eltOnly">
<s:AttributeType name="ART">
  <s:datatype dt:type="string" dt:maxLength="3" rs:maybenull="false" /> 
  </s:AttributeType>
<s:AttributeType name="BELEG">
  <s:datatype dt:type="string" dt:maxLength="63" rs:maybenull="false" /> 
  </s:AttributeType>
<s:AttributeType name="BELEGPOSITION">
  <s:datatype dt:type="i2" /> 
  </s:AttributeType>
<s:AttributeType name="BESTELLUNGSDATUM">
  <s:datatype dt:type="dateTime" /> 
  </s:AttributeType>
<s:AttributeType name="VERURSACHERARTIKEL">
  <s:datatype dt:type="string" dt:maxLength="22" rs:maybenull="false" /> 
  </s:AttributeType>
<s:AttributeType name="WIEDERBESCHAFFUNGSZEIT">
  <s:datatype dt:type="i2" /> 
  </s:AttributeType>
<s:AttributeType name="LIEFERANT">
  <s:datatype dt:type="string" dt:maxLength="15" /> 
  </s:AttributeType>
<s:AttributeType name="LIEFERANTENNAME">
  <s:datatype dt:type="string" dt:maxLength="63" /> 
  </s:AttributeType>
<s:AttributeType name="BEARBEITER">
  <s:datatype dt:type="string" dt:maxLength="50" /> 
  </s:AttributeType>
<s:AttributeType name="VERURSACHER">
  <s:datatype dt:type="string" dt:maxLength="19" /> 
  </s:AttributeType>
<s:AttributeType name="VERURSACHERPOSITION">
  <s:datatype dt:type="string" dt:maxLength="31" /> 
  </s:AttributeType>
<s:AttributeType name="ERSTELLER">
  <s:datatype dt:type="string" dt:maxLength="31" /> 
  </s:AttributeType>
<s:AttributeType name="ENDE">
  <s:datatype dt:type="dateTime" /> 
  </s:AttributeType>
<s:AttributeType name="BEDARFSTERMIN">
  <s:datatype dt:type="dateTime" /> 
  </s:AttributeType>
<s:AttributeType name="LIEFERTERMIN">
  <s:datatype dt:type="dateTime" /> 
  </s:AttributeType>
<s:AttributeType name="VERSPAETUNG">
  <s:datatype dt:type="int" /> 
  </s:AttributeType>
<s:AttributeType name="UNTERSCHREITUNG">
  <s:datatype dt:type="int" /> 
  </s:AttributeType>
  <s:extends type="rs:rowbase" /> 
  </s:ElementType>
  </s:Schema>
<rs:data xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema">
  <z:row ART="ZB" BELEG="982831" BELEGPOSITION="4" BESTELLUNGSDATUM="2012-08-27T00:00:00" VERURSACHERARTIKEL="A16" WIEDERBESCHAFFUNGSZEIT="28" LIEFERANT="123" LIEFERANTENNAME="XXXcOMPANY" BEARBEITER="HarryPotter" VERURSACHER="0.123" VERURSACHERPOSITION="470" ERSTELLER="Max Mustermann" ENDE="2012-12-20T10:10:00" BEDARFSTERMIN="2011-12-19T00:00:00" LIEFERTERMIN="2011-12-31T00:00:00" VERSPAETUNG="-36" UNTERSCHREITUNG="46" /> 
  <z:row ART="ZB" BELEG="982838" BELEGPOSITION="4" BESTELLUNGSDATUM="2012-08-27T00:00:00" VERURSACHERARTIKEL="B16" WIEDERBESCHAFFUNGSZEIT="28" LIEFERANT="134" LIEFERANTENNAME="XXXcOMPANY" BEARBEITER="HarryPotter" VERURSACHER="0.234" VERURSACHERPOSITION="472" ERSTELLER="Max Mustermann" ENDE="2012-12-20T10:10:00" BEDARFSTERMIN="2012-12-19T00:00:00" LIEFERTERMIN="2013-12-31T00:00:00" VERSPAETUNG="-376" UNTERSCHREITUNG="86" /> 
  </rs:data>
</xml>

XSLT specific part:

<Row>
<xsl:for-each select="../../s:Schema/s:ElementType/s:AttributeType">
<Cell>
<xsl:variable name="v" select="$x/@*[name()=current()/@name]" />
<xsl:choose>
<xsl:when test="string-length($v)=0">
</xsl:when>
<xsl:when test="s:datatype[@dt:type='dateTime']">
<Data ss:Type="DateTime">
<xsl:value-of select="$v" />
</Data>
</xsl:when>
<xsl:otherwise>
<Data ss:Type="String">
<xsl:value-of select="$v" />
</Data>
</xsl:otherwise>
</xsl:choose>
</Cell>
</xsl:for-each>
</Row>

Upvotes: 4

Views: 3675

Answers (1)

Tim C
Tim C

Reputation: 70638

If you want to find out how to do something in Excel Spreadsheet XML, then the best way to find out is ask Excel itself! Go into Excel, enter a URL in a cell and save it in XML Spreadsheet format. Then open the saved file in Notepad (or your text editor of choice). Hopefully, you should see something like this

<Cell ss:StyleID="s62" ss:HRef="http://stackoverflow.com/">
   <Data ss:Type="String">http://stackoverflow.com/</Data>
</Cell>

So, all you need to do in your XSLT is to ensure an ss:HRef attribute is added to the Cell element (before you create your Data element). Something like this

<xsl:otherwise>
   <xsl:choose>
      <xsl:when test="$attribute = 'Beleg'">
          <xsl:attribute name="HRef">
              <xsl:text>http://www.xyz.de/beleg.php?beleg=<xsl:text>
              <xsl:value-of select="$fieldvalue" />
          </xsl:attribute>
      </xsl:when>
   </xsl:choose>
   <Data ss:Type="String">
      <xsl:value-of select="$v" />
   </Data>
</xsl:otherwise>

Obviously you would have to ensure $attribute is set to the value of the attribute you wish to check, but it should give you the general idea.

EDIT: You may need to ensure the URL you set is an absolute address (beginning with "http://") and not a relative one (beginning with "../")

Upvotes: 5

Related Questions