add hyperlink to excel cell via xslt

excel, xml, xslt

Solution

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 "../")

Problem

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> ```

Original source