xref: /aoo42x/main/filter/source/xslt/export/spreadsheetml/formular.xsl (revision 1e519d8d)

<?xml version="1.0" encoding="UTF-8"?>
<!--***********************************************************
 *
 * Licensed to the Apache Software Foundation (ASF) under one
 * or more contributor license agreements.  See the NOTICE file
 * distributed with this work for additional information
 * regarding copyright ownership.  The ASF licenses this file
 * to you under the Apache License, Version 2.0 (the
 * "License"); you may not use this file except in compliance
 * with the License.  You may obtain a copy of the License at
 *
 *   http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing,
 * software distributed under the License is distributed on an
 * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
 * KIND, either express or implied.  See the License for the
 * specific language governing permissions and limitations
 * under the License.
 *
 ***********************************************************-->


<xsl:stylesheet version="1.0"
	xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
	xmlns:chart="urn:oasis:names:tc:opendocument:xmlns:chart:1.0"
	xmlns:config="urn:oasis:names:tc:opendocument:xmlns:config:1.0"
	xmlns:dc="http://purl.org/dc/elements/1.1/"
	xmlns:dom="http://www.w3.org/2001/xml-events"
	xmlns:dr3d="urn:oasis:names:tc:opendocument:xmlns:dr3d:1.0"
	xmlns:draw="urn:oasis:names:tc:opendocument:xmlns:drawing:1.0"
	xmlns:fo="urn:oasis:names:tc:opendocument:xmlns:xsl-fo-compatible:1.0"
	xmlns:form="urn:oasis:names:tc:opendocument:xmlns:form:1.0"
	xmlns:math="http://www.w3.org/1998/Math/MathML"
	xmlns:meta="urn:oasis:names:tc:opendocument:xmlns:meta:1.0"
	xmlns:number="urn:oasis:names:tc:opendocument:xmlns:datastyle:1.0"
	xmlns:office="urn:oasis:names:tc:opendocument:xmlns:office:1.0"
	xmlns:ooo="http://openoffice.org/2004/office"
	xmlns:oooc="http://openoffice.org/2004/calc"
	xmlns:ooow="http://openoffice.org/2004/writer"
	xmlns:script="urn:oasis:names:tc:opendocument:xmlns:script:1.0"
	xmlns:style="urn:oasis:names:tc:opendocument:xmlns:style:1.0"
	xmlns:svg="urn:oasis:names:tc:opendocument:xmlns:svg-compatible:1.0"
	xmlns:table="urn:oasis:names:tc:opendocument:xmlns:table:1.0"
	xmlns:text="urn:oasis:names:tc:opendocument:xmlns:text:1.0"
	xmlns:xlink="http://www.w3.org/1999/xlink"
	xmlns:xt="http://www.jclark.com/xt"
	xmlns:common="http://exslt.org/common"
	xmlns:xalan="http://xml.apache.org/xalan"
	xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:c="urn:schemas-microsoft-com:office:component:spreadsheet" xmlns:html="http://www.w3.org/TR/REC-html40" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x2="http://schemas.microsoft.com/office/excel/2003/xml" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	exclude-result-prefixes="chart config dc dom dr3d draw fo form math meta number office ooo oooc ooow script style svg table text xlink xt common xalan">


	<!-- Mapping @table:formula to @ss:Formula translating the expression syntax -->
	<xsl:template match="@table:formula">
		<xsl:param name="calculatedCellPosition" />
		<xsl:param name="calculatedRowPosition" />

		<xsl:attribute name="ss:Formula">
			<xsl:call-template name="translate-formular-expression">
				<xsl:with-param name="rowPos"    select="$calculatedRowPosition" />
				<xsl:with-param name="columnPos" select="$calculatedCellPosition" />
				<xsl:with-param name="expression" select="." />
			</xsl:call-template>
		</xsl:attribute>
	</xsl:template>


	<!-- Translate OOOC formula expressions of table cells to spreadsheetml expression

		For example:
			"oooc:=ROUNDDOWN(123.321;2)"
				to "=ROUNDDOWN(123.321,2)"
			"oooc:=([.B2]-[.C2])"
				to  "=(RC[-2]-RC[-1])"
			"oooc:=DCOUNTA([.E14:.F21];[.F14];[.H14:.I15])"
				to  "=DCOUNTA(R[-17]C[3]:R[-10]C[4],R[-17]C[4],R[-17]C[6]:R[-16]C[7])"   -->
	<xsl:template name="translate-formular-expression">
		<!--  return position or range for formula or other -->
		<xsl:param name="rowPos" /> <!-- the position in row (vertical) of cell -->
		<xsl:param name="columnPos" /> <!-- the position in column (horizontal of cell) -->
		<xsl:param name="expression" /> <!-- the expression string to be converted  -->

		<xsl:choose>
			<xsl:when test="$expression != ''">
				<xsl:choose>
					<!-- OASIS Open Document XML formular expressions  -->
					<xsl:when test="starts-with($expression,'oooc:')">
						<!-- giving out the '=', which will be removed with 'oooc:=' to enable recursive string parsing  -->
						<xsl:text>=</xsl:text>
						<xsl:call-template name="function-parameter-mapping">
							<xsl:with-param name="rowPos" select="$rowPos" />
							<xsl:with-param name="columnPos" select="$columnPos" />
							<!-- 1) remove 'oooc:=' prefix and exchange ';' with ',' -->
							<xsl:with-param name="expression" select="translate(substring($expression,7),';',',')"/>
						</xsl:call-template>
					</xsl:when>
					<xsl:otherwise>
						<xsl:value-of select="$expression" />
					</xsl:otherwise>
				</xsl:choose>
			</xsl:when>
			<xsl:otherwise>
				<xsl:value-of select="$expression" />
			</xsl:otherwise>
		</xsl:choose>
	</xsl:template>

	<!-- As the function API of our Office and MS Office show differences in the argumentlists,
			-	sometimes the last parameter have to be neglected
			-	sometimes a default have to be added
		these exchanges have to be done as well -->
	<xsl:template name="function-parameter-mapping">
		<xsl:param name="rowPos" /> <!-- the position in row (vertical of cell) -->
		<xsl:param name="columnPos" /> <!-- the position in column (horizontal of cell) -->
		<xsl:param name="expression" /> <!-- expression to be exchanged -->

		<!-- Choose if the expression contains one of the function, which might need changes -->
		<xsl:choose>
			<!-- if not contain one of the functions, which need parameter mapping -->
			<xsl:when test="not(contains($expression, 'ADDRESS(') or
								contains($expression, 'CEILING(') or
								contains($expression, 'FLOOR(') or
								contains($expression, 'IF(') or
								contains($expression, 'ROUND('))">
				<!-- simply translate possily exisiting column & row references -->
				<xsl:call-template name="translate-oooc-expression">
					<xsl:with-param name="rowPos" select="$rowPos" />
					<xsl:with-param name="columnPos" select="$columnPos" />
					<xsl:with-param name="expression" select="$expression"/>
				</xsl:call-template>
			</xsl:when>
			<!-- functions to be mapped -->
			<xsl:otherwise>
				<xsl:variable name="functionPrefix" select="substring-before($expression, '(')" />
				<xsl:variable name="expressionSuffix" select="substring-after($expression, '(')" />

				<!-- translate in case the expression contains row/cell references aside of the function name -->
				<xsl:call-template name="translate-oooc-expression">
					<xsl:with-param name="rowPos" select="$rowPos" />
					<xsl:with-param name="columnPos" select="$columnPos" />
					<xsl:with-param name="expression" select="$functionPrefix"/>
				</xsl:call-template>
				<!-- Prefix do not include the bracket -->
				<xsl:text>(</xsl:text>
				<xsl:choose>
					<xsl:when test="not(contains($functionPrefix, 'ADDRESS') or
										contains($functionPrefix, 'CEILING') or
										contains($functionPrefix, 'FLOOR') or
										(contains($functionPrefix, 'IF') and not(
											contains($functionPrefix, 'COUNTIF') or
											contains($functionPrefix, 'SUMIF'))) or
										contains($functionPrefix, 'ROUND'))">
						<xsl:call-template name="function-parameter-mapping">
							<xsl:with-param name="rowPos" select="$rowPos" />
							<xsl:with-param name="columnPos" select="$columnPos" />
							<xsl:with-param name="expression" select="$expressionSuffix"/>
						</xsl:call-template>
					</xsl:when>
					<xsl:otherwise>
						<xsl:choose>
							<xsl:when test="contains($functionPrefix, 'ADDRESS')">
								<xsl:call-template name="find-parameters">
									<xsl:with-param name="rowPos" select="$rowPos" />
									<xsl:with-param name="columnPos" select="$columnPos" />
									<xsl:with-param name="expressionSuffix" select="$expressionSuffix"/>
									<xsl:with-param name="parameterRemoval" select="4" />
								</xsl:call-template>
							</xsl:when>
							<xsl:when test="contains($functionPrefix, 'CEILING') or
											contains($functionPrefix, 'FLOOR')">
								<xsl:call-template name="find-parameters">
									<xsl:with-param name="rowPos" select="$rowPos" />
									<xsl:with-param name="columnPos" select="$columnPos" />
									<xsl:with-param name="expressionSuffix" select="$expressionSuffix"/>
									<xsl:with-param name="parameterRemoval" select="3" />
								</xsl:call-template>
							</xsl:when>
							<xsl:when test="contains($functionPrefix, 'IF')">
								<xsl:if test="not(contains($functionPrefix, 'COUNTIF') or
												  contains($functionPrefix, 'SUMIF'))">
									<xsl:call-template name="find-parameters">
										<xsl:with-param name="rowPos" select="$rowPos" />
										<xsl:with-param name="columnPos" select="$columnPos" />
										<xsl:with-param name="expressionSuffix" select="$expressionSuffix"/>
										<xsl:with-param name="parameterAddition" select="'true'" />
										<xsl:with-param name="additonAfterLastParameter" select="2" />
									</xsl:call-template>
								</xsl:if>
							</xsl:when>
							<xsl:when test="contains($functionPrefix, 'ROUND')">
								<xsl:call-template name="find-parameters">
									<xsl:with-param name="rowPos" select="$rowPos" />
									<xsl:with-param name="columnPos" select="$columnPos" />
									<xsl:with-param name="expressionSuffix" select="$expressionSuffix"/>
									<xsl:with-param name="parameterAddition" select="'null'" />
									<xsl:with-param name="additonAfterLastParameter" select="1" />
								</xsl:call-template>
							</xsl:when>
						</xsl:choose>
					</xsl:otherwise>
				</xsl:choose>
			</xsl:otherwise>
		</xsl:choose>
	</xsl:template>

	<!-- Each parameter of the argumentlist have to be determined.
	Due to the low level string functionlity in XSLT it becomes a clumsy task -->
	<xsl:template name="find-parameters">
		<!-- used for mapping of row/column reference  -->
		<xsl:param name="rowPos" /> <!-- the position in row (vertical of cell) -->
		<xsl:param name="columnPos" /> <!-- the position in column (horizontal of cell) -->
		<!-- used for mapping of parameter  -->
		<xsl:param name="parameterRemoval" />
		<xsl:param name="parameterAddition" />
		<xsl:param name="additonAfterLastParameter" />
		<!-- used as helper to find a parameter  -->
		<xsl:param name="expressionSuffix" />
		<xsl:param name="parameterNumber" select="1" />

		<xsl:variable name="parameter">
			<xsl:call-template name="getParameter">
				<xsl:with-param name="expressionSuffix" select="$expressionSuffix"/>
			</xsl:call-template>
		</xsl:variable>

		<xsl:choose>
			<!-- if it is not the last parameter -->
			<xsl:when test="starts-with(substring-after($expressionSuffix, $parameter), ',')">
				<!-- searches the argument for functions to be mapped -->
				<xsl:if test="not($parameterRemoval = $parameterNumber)">
					<xsl:call-template name="function-parameter-mapping">
						<xsl:with-param name="rowPos" select="$rowPos" />
						<xsl:with-param name="columnPos" select="$columnPos" />
						<xsl:with-param name="expression">
							<xsl:choose>
								<!-- in case a character will be removed the preceding won't make a comma -->
								<xsl:when test="$parameterRemoval = ($parameterNumber + 1)">
									<xsl:value-of select="$parameter" />
								</xsl:when>
								<xsl:otherwise>
									<xsl:value-of select="concat($parameter, ',')" />
								</xsl:otherwise>
							</xsl:choose>
						</xsl:with-param>
					</xsl:call-template>
				</xsl:if>
				<!-- searches for the next parameter -->
				<xsl:call-template name="find-parameters">
					<xsl:with-param name="rowPos" select="$rowPos" />
					<xsl:with-param name="columnPos" select="$columnPos" />
					<xsl:with-param name="expressionSuffix" select="substring-after(substring-after($expressionSuffix, $parameter),',')"/>
					<xsl:with-param name="parameterAddition" select="$parameterAddition" />
					<xsl:with-param name="parameterRemoval" select="$parameterRemoval" />
					<xsl:with-param name="additonAfterLastParameter" select="$additonAfterLastParameter" />
					<xsl:with-param name="parameterNumber" select="$parameterNumber + 1" />
				</xsl:call-template>
			</xsl:when>
			<xsl:otherwise>
				<!-- the last parameter -->
				<xsl:choose>
					<xsl:when test="$parameterRemoval = $parameterNumber">
						<!-- searches the rest of the expression for functions to be mapped -->
						<xsl:call-template name="function-parameter-mapping">
							<xsl:with-param name="rowPos" select="$rowPos" />
							<xsl:with-param name="columnPos" select="$columnPos" />
							<xsl:with-param name="expression" select="substring-after($expressionSuffix, $parameter)"/>
						</xsl:call-template>
					</xsl:when>
					<xsl:when test="$parameterAddition and ($parameterNumber  = $additonAfterLastParameter)">
						<!-- searches the rest of the expression for functions to be mapped -->
						<xsl:call-template name="function-parameter-mapping">
							<xsl:with-param name="rowPos" select="$rowPos" />
							<xsl:with-param name="columnPos" select="$columnPos" />
							<xsl:with-param name="expression" select="$parameter" />
						</xsl:call-template>
						<!-- searches last parameter and additional parameters for functions to be mapped -->
						<xsl:call-template name="function-parameter-mapping">
							<xsl:with-param name="rowPos" select="$rowPos" />
							<xsl:with-param name="columnPos" select="$columnPos" />
							<!-- for the final parameter the latter substring is the ')' -->
							<xsl:with-param name="expression" select="concat(',', $parameterAddition, substring-after($expressionSuffix, $parameter))"/>
						</xsl:call-template>
					</xsl:when>
					<xsl:otherwise>
						<!-- searches the argument for functions to be mapped -->
						<xsl:call-template name="function-parameter-mapping">
							<xsl:with-param name="rowPos" select="$rowPos" />
							<xsl:with-param name="columnPos" select="$columnPos" />
							<xsl:with-param name="expression" select="$parameter" />
						</xsl:call-template>
						<!-- searches the rest of the expression for functions to be mapped -->
						<xsl:call-template name="function-parameter-mapping">
							<xsl:with-param name="rowPos" select="$rowPos" />
							<xsl:with-param name="columnPos" select="$columnPos" />
							<xsl:with-param name="expression" select="substring-after($expressionSuffix, $parameter)"/>
						</xsl:call-template>
					</xsl:otherwise>
				</xsl:choose>
			</xsl:otherwise>
		</xsl:choose>
	</xsl:template>

	<xsl:template name="getParameter">
		<xsl:param name="closingBracketCount" select="0" />
		<xsl:param name="openingBracketCount" select="0" />
		<xsl:param name="expressionSuffix" />
		<xsl:param name="parameterCandidate">
			<xsl:choose>
				<!-- if there are multiple parameter -->
				<xsl:when test="contains(substring-before($expressionSuffix, ')'), ',')">
					<xsl:value-of select="substring-before($expressionSuffix, ',')"/>
				</xsl:when>
				<xsl:otherwise>
					<xsl:value-of select="substring-before($expressionSuffix, ')')"/>
				</xsl:otherwise>
			</xsl:choose>
		</xsl:param>
		<xsl:param name="earlierCandidate" select="$parameterCandidate" />

		<xsl:choose>
			<xsl:when test="contains($parameterCandidate, '(') or contains($parameterCandidate, ')')">
				<xsl:choose>
					<!-- contains only closing bracket(s) -->
					<xsl:when test="contains($parameterCandidate, '(') and not(contains($parameterCandidate, ')'))">
						<xsl:call-template name="getParameter">
							<xsl:with-param name="openingBracketCount" select="$openingBracketCount + 1" />
							<xsl:with-param name="closingBracketCount" select="$closingBracketCount" />
							<xsl:with-param name="parameterCandidate" select="substring-after($parameterCandidate, '(')" />
							<xsl:with-param name="earlierCandidate" select="$earlierCandidate" />
							<xsl:with-param name="expressionSuffix" select="$expressionSuffix"/>
						</xsl:call-template>
					</xsl:when>
					<!-- contains only opening bracket(s) -->
					<xsl:when test="not(contains($parameterCandidate, '(')) and contains($parameterCandidate, ')')">
						<xsl:call-template name="getParameter">
							<xsl:with-param name="openingBracketCount" select="$openingBracketCount" />
							<xsl:with-param name="closingBracketCount" select="$closingBracketCount + 1" />
							<xsl:with-param name="parameterCandidate" select="substring-after($parameterCandidate, ')')" />
							<xsl:with-param name="earlierCandidate" select="$earlierCandidate" />
							<xsl:with-param name="expressionSuffix" select="$expressionSuffix"/>
						</xsl:call-template>
					</xsl:when>
					<xsl:otherwise>
						<xsl:choose>
							<xsl:when test="string-length(substring-before($parameterCandidate, '(')) &lt;
											string-length(substring-before($parameterCandidate, ')'))">
								<xsl:call-template name="getParameter">
									<xsl:with-param name="openingBracketCount" select="$openingBracketCount + 1" />
									<xsl:with-param name="closingBracketCount" select="$closingBracketCount" />
									<xsl:with-param name="parameterCandidate" select="substring-after($parameterCandidate, '(')" />
									<xsl:with-param name="earlierCandidate" select="$earlierCandidate" />
									<xsl:with-param name="expressionSuffix" select="$expressionSuffix"/>
								</xsl:call-template>
							</xsl:when>
							<xsl:otherwise>
								<xsl:call-template name="getParameter">
									<xsl:with-param name="openingBracketCount" select="$openingBracketCount" />
									<xsl:with-param name="closingBracketCount" select="$closingBracketCount + 1" />
									<xsl:with-param name="parameterCandidate" select="substring-after($parameterCandidate, ')')" />
									<xsl:with-param name="earlierCandidate" select="$earlierCandidate" />
									<xsl:with-param name="expressionSuffix" select="$expressionSuffix"/>
								</xsl:call-template>
							</xsl:otherwise>
						</xsl:choose>
					</xsl:otherwise>
				</xsl:choose>
			</xsl:when>
			<xsl:otherwise>
				<xsl:choose>
					<xsl:when test="$openingBracketCount = $closingBracketCount">
						<xsl:value-of select="$earlierCandidate" />
					</xsl:when>
					<xsl:otherwise>
						<xsl:value-of select="$earlierCandidate" />
						<xsl:variable name="parameterCandidate2">
							<xsl:variable name="formularAfterCandidate" select="substring-after($expressionSuffix, $earlierCandidate)" />
							<xsl:variable name="parameterTillBracket" select="concat(substring-before($formularAfterCandidate,')'),')')" />
							<xsl:variable name="parameterTillComma" select="substring-before(substring-after($expressionSuffix, $parameterTillBracket),',')" />
							<xsl:choose>
								<xsl:when test="string-length($parameterTillComma) &gt; 0 and
												not(contains($parameterTillComma, '('))">
									<xsl:choose>
										<xsl:when test="starts-with($formularAfterCandidate, ',')">
											<xsl:value-of select="concat(',',substring-before(substring-after($formularAfterCandidate,','),','))"/>
										</xsl:when>
										<xsl:otherwise>
											<xsl:value-of select="substring-before($formularAfterCandidate,',')"/>
										</xsl:otherwise>
									</xsl:choose>
								</xsl:when>
								<xsl:otherwise>
									<xsl:value-of select="$parameterTillBracket"/>
								</xsl:otherwise>
							</xsl:choose>
						</xsl:variable>
						<xsl:call-template name="getParameter">
							<xsl:with-param name="closingBracketCount" select="$closingBracketCount" />
							<xsl:with-param name="openingBracketCount" select="$openingBracketCount" />
							<xsl:with-param name="parameterCandidate" select="$parameterCandidate2" />
							<xsl:with-param name="earlierCandidate" select="$parameterCandidate2" />
							<xsl:with-param name="expressionSuffix" select="$expressionSuffix" />
						</xsl:call-template>
					</xsl:otherwise>
				</xsl:choose>
			</xsl:otherwise>
		</xsl:choose>
	</xsl:template>

	<!-- Mapping table-cell definitions by exchangomg all table cell definitions:
		a) a pair of cells e.g. "[.E14:.F21]" to "R[-17]C[3]:R[-10]C[4]"
		b) a single cell e.g. "[.F14]" to "R[-17]"-->
	<xsl:template name="translate-oooc-expression">
		<xsl:param name="rowPos" /> <!-- the position in row (vertical of cell) -->
		<xsl:param name="columnPos" /> <!-- the position in column (horizontal of cell) -->
		<xsl:param name="expression" /> <!-- expression to be exchanged -->

		<xsl:choose>
			<xsl:when test="contains($expression, '[')">

				<!-- Giving out the part before '[.' -->
				<xsl:value-of select="substring-before($expression, '[')" />

				<!-- Mapping cell definitions
				1) a pair of cells e.g. "[.E14:.F21]" to "R[-17]C[3]:R[-10]C[4]"
				2) a single cell e.g. "[.F14]" to "R[-17]"-->
				<xsl:variable name="remainingExpression" select="substring-after($expression, '[')"/>
				<xsl:choose>
					<xsl:when test="contains(substring-before($remainingExpression, ']'), ':')">
						<xsl:call-template name="translate-cell-expression">
							<xsl:with-param name="rowPos" select="$rowPos" />
							<xsl:with-param name="columnPos" select="$columnPos" />
							<xsl:with-param name="expression" select="substring-before($remainingExpression, ':')" />
						</xsl:call-template>
						<xsl:value-of select="':'" />
						<xsl:call-template name="translate-cell-expression">
							<xsl:with-param name="rowPos" select="$rowPos" />
							<xsl:with-param name="columnPos" select="$columnPos" />
							<xsl:with-param name="expression" select="substring-after(substring-before($remainingExpression, ']'), ':')" />
						</xsl:call-template>
					</xsl:when>
					<xsl:otherwise>
						<xsl:call-template name="translate-cell-expression">
							<xsl:with-param name="rowPos" select="$rowPos" />
							<xsl:with-param name="columnPos" select="$columnPos" />
							<xsl:with-param name="expression" select="substring-before($remainingExpression, ']')" />
						</xsl:call-template>
					</xsl:otherwise>
				</xsl:choose>
				<xsl:call-template name="translate-oooc-expression">
					<xsl:with-param name="rowPos" select="$rowPos" />
					<xsl:with-param name="columnPos" select="$columnPos" />
					<xsl:with-param name="expression" select="substring-after($remainingExpression,']')"/>
				</xsl:call-template>
			</xsl:when>
			<xsl:otherwise>
				<!-- Giving out the remaining part -->
				<xsl:value-of select="$expression" />
			</xsl:otherwise>
		</xsl:choose>
	</xsl:template>


	<!-- A cell expression has usually starts with a '.' otherwise it references to a sheet  -->
	<xsl:template name="translate-cell-expression">
		<xsl:param name="rowPos" /> <!-- the vertical position of the current cell -->
		<xsl:param name="columnPos" /> <!-- the horizontal position of the current cell -->
		<xsl:param name="targetRowPos" select="0"/> <!-- the vertical position of the target cell -->
		<xsl:param name="targetColumnPos" select="0"/> <!-- the horizontal position of the target cell -->
		<xsl:param name="charPos" select="0"/> <!-- current column position (needed for multiplying) -->
		<xsl:param name="digitPos" select="0"/>  <!-- current row position (needed for multiplying) -->
		<xsl:param name="expression" /> <!-- expression to be parsed by character -->
		<xsl:param name="isRow" select="true()"/> <!-- the string (e.g. $D39 is parsed character per character from the back,
													   first the row, later the column is parsed -->

		<xsl:choose>
			<xsl:when test="starts-with($expression, '.')">
				<xsl:variable name="expLength" select="string-length($expression)" />
				<xsl:choose>
					<!-- parsing from the end, till only the '.' remains -->
					<xsl:when test="$expLength != 1">
						<xsl:variable name="token" select="substring($expression, $expLength)" />
						<xsl:choose>
							<xsl:when test="$token='0' or $token='1' or $token='2' or $token='3' or $token='4' or $token='5' or $token='6' or $token='7' or $token='8' or $token='9'">
								<xsl:variable name="multiplier">
									<xsl:call-template name="calculate-square-numbers">
										<xsl:with-param name="base" select="10" />
										<xsl:with-param name="exponent" select="$digitPos"/>
									</xsl:call-template>
								</xsl:variable>
								<xsl:call-template name="translate-cell-expression">
									<xsl:with-param name="columnPos" select="$columnPos" />
									<xsl:with-param name="rowPos" select="$rowPos" />
									<xsl:with-param name="targetColumnPos" select="$targetColumnPos" />
									<xsl:with-param name="targetRowPos" select="$targetRowPos + $multiplier * $token" />
									<xsl:with-param name="digitPos" select="$digitPos + 1" />
									<xsl:with-param name="charPos" select="$charPos" />
									<!-- removing the last character-->
									<xsl:with-param name="expression" select="substring($expression, 1, $expLength - 1)" />
									<xsl:with-param name="isRow" select="true()" />
								</xsl:call-template>
							</xsl:when>
							<xsl:when test="$token = '$'">
								<xsl:choose>
									<!-- if this is the first '$' after '.' (column-->
									<xsl:when test="$expLength = 2">
										<xsl:text>C</xsl:text><xsl:value-of select="$targetColumnPos"/>
									</xsl:when>
									<xsl:otherwise>
										<xsl:text>R</xsl:text><xsl:value-of select="$targetRowPos"/>
										<xsl:call-template name="translate-cell-expression">
											<xsl:with-param name="columnPos" select="$columnPos" />
											<xsl:with-param name="rowPos" select="$rowPos" />
											<xsl:with-param name="targetColumnPos" select="$targetColumnPos" />
											<xsl:with-param name="targetRowPos" select="$targetRowPos" />
											<xsl:with-param name="charPos" select="$charPos" />
											<!-- removing the last character-->
											<xsl:with-param name="expression" select="substring($expression, 1, $expLength - 1)" />
											<xsl:with-param name="isRow" select="false()" />
										</xsl:call-template>
									</xsl:otherwise>
								</xsl:choose>
							</xsl:when>
							<!-- in case of a letter -->
							<xsl:otherwise>
								<xsl:if test="$isRow">
									<xsl:text>R</xsl:text>
									<xsl:if test="$targetRowPos != $rowPos">
										<xsl:text>[</xsl:text><xsl:value-of select="$targetRowPos - $rowPos"/><xsl:text>]</xsl:text>
									</xsl:if>
								</xsl:if>
								<xsl:variable name="multiplier">
									<xsl:call-template name="calculate-square-numbers">
										<xsl:with-param name="base" select="26" />
										<xsl:with-param name="exponent" select="$charPos"/>
									</xsl:call-template>
								</xsl:variable>
								<xsl:variable name="tokenNumber">
									<xsl:call-template name="character-to-number">
										<xsl:with-param name="character" select="$token" />
									</xsl:call-template>
								</xsl:variable>

								<xsl:call-template name="translate-cell-expression">
									<xsl:with-param name="columnPos" select="$columnPos" />
									<xsl:with-param name="rowPos" select="$rowPos" />
									<xsl:with-param name="targetColumnPos" select="$targetColumnPos + $multiplier * $tokenNumber" />
									<xsl:with-param name="targetRowPos" select="$targetRowPos" />
									<xsl:with-param name="digitPos" select="$digitPos" />
									<xsl:with-param name="charPos" select="$charPos + 1" />
									<!-- removing the last character-->
									<xsl:with-param name="expression" select="substring($expression, 1, $expLength - 1)" />
									<xsl:with-param name="isRow" select="false()" />
								</xsl:call-template>
							</xsl:otherwise>
						</xsl:choose>
					</xsl:when>
					<xsl:otherwise>
						<xsl:text>C</xsl:text>
						<xsl:if test="$targetColumnPos != $columnPos">
							<xsl:text>[</xsl:text><xsl:value-of select="$targetColumnPos - $columnPos"/><xsl:text>]</xsl:text>
						</xsl:if>
					</xsl:otherwise>
				</xsl:choose>
			</xsl:when>
			<xsl:otherwise>
				<xsl:variable name="sheetName" select="substring-before($expression, '.')" />
				<xsl:value-of select="$sheetName"/><xsl:text>!</xsl:text>
				<xsl:call-template name="translate-cell-expression">
					<xsl:with-param name="rowPos" select="$rowPos" />
					<xsl:with-param name="columnPos" select="$columnPos" />
					<xsl:with-param name="expression" select="substring-after($expression, $sheetName)" />
				</xsl:call-template>
			</xsl:otherwise>
		</xsl:choose>
	</xsl:template>


	<xsl:template name="calculate-square-numbers">
		<xsl:param name="base" />
		<xsl:param name="exponent" />
		<xsl:param name="return" select="1" />

		<xsl:choose>
			<xsl:when test="$exponent > '1'">
				<xsl:call-template name="calculate-square-numbers">
					<xsl:with-param name="base" select="$base" />
					<xsl:with-param name="exponent" select="$exponent - 1"/>
					<xsl:with-param name="return" select="$return * $base" />
				</xsl:call-template>
			</xsl:when>
			<xsl:when test="$exponent = '1'">
				<xsl:value-of select="$return * $base"/>
			</xsl:when>
			<!-- if exponent is equal '0' -->
			<xsl:otherwise>
				<xsl:value-of select="1"/>
			</xsl:otherwise>
		</xsl:choose>
	</xsl:template>


	<xsl:template name="character-to-number">
		<xsl:param name="character" />
		<xsl:choose>
			<xsl:when test="$character = 'A'">1</xsl:when>
			<xsl:when test="$character = 'B'">2</xsl:when>
			<xsl:when test="$character = 'C'">3</xsl:when>
			<xsl:when test="$character = 'D'">4</xsl:when>
			<xsl:when test="$character = 'E'">5</xsl:when>
			<xsl:when test="$character = 'F'">6</xsl:when>
			<xsl:when test="$character = 'G'">7</xsl:when>
			<xsl:when test="$character = 'H'">8</xsl:when>
			<xsl:when test="$character = 'I'">9</xsl:when>
			<xsl:when test="$character = 'J'">10</xsl:when>
			<xsl:when test="$character = 'K'">11</xsl:when>
			<xsl:when test="$character = 'L'">12</xsl:when>
			<xsl:when test="$character = 'M'">13</xsl:when>
			<xsl:when test="$character = 'N'">14</xsl:when>
			<xsl:when test="$character = 'O'">15</xsl:when>
			<xsl:when test="$character = 'P'">16</xsl:when>
			<xsl:when test="$character = 'Q'">17</xsl:when>
			<xsl:when test="$character = 'R'">18</xsl:when>
			<xsl:when test="$character = 'S'">19</xsl:when>
			<xsl:when test="$character = 'T'">20</xsl:when>
			<xsl:when test="$character = 'U'">21</xsl:when>
			<xsl:when test="$character = 'V'">22</xsl:when>
			<xsl:when test="$character = 'W'">23</xsl:when>
			<xsl:when test="$character = 'X'">24</xsl:when>
			<xsl:when test="$character = 'Y'">25</xsl:when>
			<xsl:when test="$character = 'Z'">26</xsl:when>
			<xsl:otherwise/>
		</xsl:choose>
	</xsl:template>

</xsl:stylesheet>