diff options
Diffstat (limited to 'filter/source/xslt/export/spreadsheetml/formular.xsl')
| -rw-r--r-- | filter/source/xslt/export/spreadsheetml/formular.xsl | 640 |
1 files changed, 640 insertions, 0 deletions
diff --git a/filter/source/xslt/export/spreadsheetml/formular.xsl b/filter/source/xslt/export/spreadsheetml/formular.xsl new file mode 100644 index 000000000000..8134412ec8d9 --- /dev/null +++ b/filter/source/xslt/export/spreadsheetml/formular.xsl @@ -0,0 +1,640 @@ +<?xml version="1.0" encoding="UTF-8"?> +<!-- + + DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS FILE HEADER. + + Copyright 2000, 2010 Oracle and/or its affiliates. + + OpenOffice.org - a multi-platform office productivity suite + + This file is part of OpenOffice.org. + + OpenOffice.org is free software: you can redistribute it and/or modify + it under the terms of the GNU Lesser General Public License version 3 + only, as published by the Free Software Foundation. + + OpenOffice.org is distributed in the hope that it will be useful, + but WITHOUT ANY WARRANTY; without even the implied warranty of + MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + GNU Lesser General Public License version 3 for more details + (a copy is included in the LICENSE file that accompanied this code). + + You should have received a copy of the GNU Lesser General Public License + version 3 along with OpenOffice.org. If not, see + <http://www.openoffice.org/license.html> + for a copy of the LGPLv3 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, '(')) < + 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) > 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> |