1<?xml version="1.0" encoding="UTF-8"?> 2<!--*********************************************************** 3 * 4 * Licensed to the Apache Software Foundation (ASF) under one 5 * or more contributor license agreements. See the NOTICE file 6 * distributed with this work for additional information 7 * regarding copyright ownership. The ASF licenses this file 8 * to you under the Apache License, Version 2.0 (the 9 * "License"); you may not use this file except in compliance 10 * with the License. You may obtain a copy of the License at 11 * 12 * http://www.apache.org/licenses/LICENSE-2.0 13 * 14 * Unless required by applicable law or agreed to in writing, 15 * software distributed under the License is distributed on an 16 * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY 17 * KIND, either express or implied. See the License for the 18 * specific language governing permissions and limitations 19 * under the License. 20 * 21 ***********************************************************--> 22 23 24<xsl:stylesheet version="1.0" 25 xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 26 xmlns:chart="urn:oasis:names:tc:opendocument:xmlns:chart:1.0" 27 xmlns:config="urn:oasis:names:tc:opendocument:xmlns:config:1.0" 28 xmlns:dc="http://purl.org/dc/elements/1.1/" 29 xmlns:dom="http://www.w3.org/2001/xml-events" 30 xmlns:dr3d="urn:oasis:names:tc:opendocument:xmlns:dr3d:1.0" 31 xmlns:draw="urn:oasis:names:tc:opendocument:xmlns:drawing:1.0" 32 xmlns:fo="urn:oasis:names:tc:opendocument:xmlns:xsl-fo-compatible:1.0" 33 xmlns:form="urn:oasis:names:tc:opendocument:xmlns:form:1.0" 34 xmlns:math="http://www.w3.org/1998/Math/MathML" 35 xmlns:meta="urn:oasis:names:tc:opendocument:xmlns:meta:1.0" 36 xmlns:number="urn:oasis:names:tc:opendocument:xmlns:datastyle:1.0" 37 xmlns:office="urn:oasis:names:tc:opendocument:xmlns:office:1.0" 38 xmlns:ooo="http://openoffice.org/2004/office" 39 xmlns:oooc="http://openoffice.org/2004/calc" 40 xmlns:ooow="http://openoffice.org/2004/writer" 41 xmlns:script="urn:oasis:names:tc:opendocument:xmlns:script:1.0" 42 xmlns:style="urn:oasis:names:tc:opendocument:xmlns:style:1.0" 43 xmlns:svg="urn:oasis:names:tc:opendocument:xmlns:svg-compatible:1.0" 44 xmlns:table="urn:oasis:names:tc:opendocument:xmlns:table:1.0" 45 xmlns:text="urn:oasis:names:tc:opendocument:xmlns:text:1.0" 46 xmlns:xlink="http://www.w3.org/1999/xlink" 47 xmlns:xt="http://www.jclark.com/xt" 48 xmlns:common="http://exslt.org/common" 49 xmlns:xalan="http://xml.apache.org/xalan" 50 xmlns:of="urn:oasis:names:tc:opendocument:xmlns:of:1.2" 51 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" 52 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 of"> 53 54 55 <!-- Mapping @table:formula to @ss:Formula translating the expression syntax --> 56 <xsl:template match="@table:formula"> 57 <xsl:param name="calculatedCellPosition" /> 58 <xsl:param name="calculatedRowPosition" /> 59 60 <xsl:attribute name="ss:Formula"> 61 <xsl:call-template name="translate-formular-expression"> 62 <xsl:with-param name="rowPos" select="$calculatedRowPosition" /> 63 <xsl:with-param name="columnPos" select="$calculatedCellPosition" /> 64 <xsl:with-param name="expression" select="." /> 65 </xsl:call-template> 66 </xsl:attribute> 67 </xsl:template> 68 69 70 <!-- Translate OOOC formula expressions of table cells to spreadsheetml expression 71 72 For example: 73 "oooc:=ROUNDDOWN(123.321;2)" 74 to "=ROUNDDOWN(123.321,2)" 75 "oooc:=([.B2]-[.C2])" 76 to "=(RC[-2]-RC[-1])" 77 "oooc:=DCOUNTA([.E14:.F21];[.F14];[.H14:.I15])" 78 to "=DCOUNTA(R[-17]C[3]:R[-10]C[4],R[-17]C[4],R[-17]C[6]:R[-16]C[7])" --> 79 <xsl:template name="translate-formular-expression"> 80 <!-- return position or range for formula or other --> 81 <xsl:param name="rowPos" /> <!-- the position in row (vertical) of cell --> 82 <xsl:param name="columnPos" /> <!-- the position in column (horizontal of cell) --> 83 <xsl:param name="expression" /> <!-- the expression string to be converted --> 84 85 <xsl:choose> 86 <xsl:when test="$expression != ''"> 87 <xsl:choose> 88 <!-- OASIS Open Document XML formular expressions --> 89 <xsl:when test="starts-with($expression,'of:')"> 90 <!-- ODF >= 1.2: giving out the '=', which will be removed with 'of:=' to enable recursive string parsing --> 91 <xsl:text>=</xsl:text> 92 <xsl:call-template name="function-parameter-mapping"> 93 <xsl:with-param name="rowPos" select="$rowPos" /> 94 <xsl:with-param name="columnPos" select="$columnPos" /> 95 <!-- 1) remove 'of:=' prefix and exchange ';' with ',' --> 96 <xsl:with-param name="expression" select="translate(substring($expression,5),';',',')"/> 97 </xsl:call-template> 98 </xsl:when> 99 <xsl:when test="starts-with($expression,'oooc:')"> 100 <!-- ODF < 1.2: giving out the '=', which will be removed with 'oooc:=' to enable recursive string parsing --> 101 <xsl:text>=</xsl:text> 102 <xsl:call-template name="function-parameter-mapping"> 103 <xsl:with-param name="rowPos" select="$rowPos" /> 104 <xsl:with-param name="columnPos" select="$columnPos" /> 105 <!-- 1) remove 'oooc:=' prefix and exchange ';' with ',' --> 106 <xsl:with-param name="expression" select="translate(substring($expression,7),';',',')"/> 107 </xsl:call-template> 108 </xsl:when> 109 <xsl:otherwise> 110 <xsl:value-of select="$expression" /> 111 </xsl:otherwise> 112 </xsl:choose> 113 </xsl:when> 114 <xsl:otherwise> 115 <xsl:value-of select="$expression" /> 116 </xsl:otherwise> 117 </xsl:choose> 118 </xsl:template> 119 120 <!-- As the function API of our Office and MS Office show differences in the argumentlists, 121 - sometimes the last parameter have to be neglected 122 - sometimes a default have to be added 123 these exchanges have to be done as well --> 124 <xsl:template name="function-parameter-mapping"> 125 <xsl:param name="rowPos" /> <!-- the position in row (vertical of cell) --> 126 <xsl:param name="columnPos" /> <!-- the position in column (horizontal of cell) --> 127 <xsl:param name="expression" /> <!-- expression to be exchanged --> 128 129 <!-- Choose if the expression contains one of the function, which might need changes --> 130 <xsl:choose> 131 <!-- if not contain one of the functions, which need parameter mapping --> 132 <xsl:when test="not(contains($expression, 'ADDRESS(') or 133 contains($expression, 'CEILING(') or 134 contains($expression, 'FLOOR(') or 135 contains($expression, 'IF(') or 136 contains($expression, 'ROUND('))"> 137 <!-- simply translate possily exisiting column & row references --> 138 <xsl:call-template name="translate-oooc-expression"> 139 <xsl:with-param name="rowPos" select="$rowPos" /> 140 <xsl:with-param name="columnPos" select="$columnPos" /> 141 <xsl:with-param name="expression" select="$expression"/> 142 </xsl:call-template> 143 </xsl:when> 144 <!-- functions to be mapped --> 145 <xsl:otherwise> 146 <xsl:variable name="functionPrefix" select="substring-before($expression, '(')" /> 147 <xsl:variable name="expressionSuffix" select="substring-after($expression, '(')" /> 148 149 <!-- translate in case the expression contains row/cell references aside of the function name --> 150 <xsl:call-template name="translate-oooc-expression"> 151 <xsl:with-param name="rowPos" select="$rowPos" /> 152 <xsl:with-param name="columnPos" select="$columnPos" /> 153 <xsl:with-param name="expression" select="$functionPrefix"/> 154 </xsl:call-template> 155 <!-- Prefix do not include the bracket --> 156 <xsl:text>(</xsl:text> 157 <xsl:choose> 158 <xsl:when test="not(contains($functionPrefix, 'ADDRESS') or 159 contains($functionPrefix, 'CEILING') or 160 contains($functionPrefix, 'FLOOR') or 161 (contains($functionPrefix, 'IF') and not( 162 contains($functionPrefix, 'COUNTIF') or 163 contains($functionPrefix, 'SUMIF'))) or 164 contains($functionPrefix, 'ROUND'))"> 165 <xsl:call-template name="function-parameter-mapping"> 166 <xsl:with-param name="rowPos" select="$rowPos" /> 167 <xsl:with-param name="columnPos" select="$columnPos" /> 168 <xsl:with-param name="expression" select="$expressionSuffix"/> 169 </xsl:call-template> 170 </xsl:when> 171 <xsl:otherwise> 172 <xsl:choose> 173 <xsl:when test="contains($functionPrefix, 'ADDRESS')"> 174 <xsl:call-template name="find-parameters"> 175 <xsl:with-param name="rowPos" select="$rowPos" /> 176 <xsl:with-param name="columnPos" select="$columnPos" /> 177 <xsl:with-param name="expressionSuffix" select="$expressionSuffix"/> 178 <xsl:with-param name="parameterRemoval" select="4" /> 179 </xsl:call-template> 180 </xsl:when> 181 <xsl:when test="contains($functionPrefix, 'CEILING') or 182 contains($functionPrefix, 'FLOOR')"> 183 <xsl:call-template name="find-parameters"> 184 <xsl:with-param name="rowPos" select="$rowPos" /> 185 <xsl:with-param name="columnPos" select="$columnPos" /> 186 <xsl:with-param name="expressionSuffix" select="$expressionSuffix"/> 187 <xsl:with-param name="parameterRemoval" select="3" /> 188 </xsl:call-template> 189 </xsl:when> 190 <xsl:when test="contains($functionPrefix, 'IF')"> 191 <xsl:if test="not(contains($functionPrefix, 'COUNTIF') or 192 contains($functionPrefix, 'SUMIF'))"> 193 <xsl:call-template name="find-parameters"> 194 <xsl:with-param name="rowPos" select="$rowPos" /> 195 <xsl:with-param name="columnPos" select="$columnPos" /> 196 <xsl:with-param name="expressionSuffix" select="$expressionSuffix"/> 197 <xsl:with-param name="parameterAddition" select="'true'" /> 198 <xsl:with-param name="additonAfterLastParameter" select="2" /> 199 </xsl:call-template> 200 </xsl:if> 201 </xsl:when> 202 <xsl:when test="contains($functionPrefix, 'ROUND')"> 203 <xsl:call-template name="find-parameters"> 204 <xsl:with-param name="rowPos" select="$rowPos" /> 205 <xsl:with-param name="columnPos" select="$columnPos" /> 206 <xsl:with-param name="expressionSuffix" select="$expressionSuffix"/> 207 <xsl:with-param name="parameterAddition" select="'null'" /> 208 <xsl:with-param name="additonAfterLastParameter" select="1" /> 209 </xsl:call-template> 210 </xsl:when> 211 </xsl:choose> 212 </xsl:otherwise> 213 </xsl:choose> 214 </xsl:otherwise> 215 </xsl:choose> 216 </xsl:template> 217 218 <!-- Each parameter of the argumentlist have to be determined. 219 Due to the low level string functionality in XSLT it becomes a clumsy task --> 220 <xsl:template name="find-parameters"> 221 <!-- used for mapping of row/column reference --> 222 <xsl:param name="rowPos" /> <!-- the position in row (vertical of cell) --> 223 <xsl:param name="columnPos" /> <!-- the position in column (horizontal of cell) --> 224 <!-- used for mapping of parameter --> 225 <xsl:param name="parameterRemoval" /> 226 <xsl:param name="parameterAddition" /> 227 <xsl:param name="additonAfterLastParameter" /> 228 <!-- used as helper to find a parameter --> 229 <xsl:param name="expressionSuffix" /> 230 <xsl:param name="parameterNumber" select="1" /> 231 232 <xsl:variable name="parameter"> 233 <xsl:call-template name="getParameter"> 234 <xsl:with-param name="expressionSuffix" select="$expressionSuffix"/> 235 </xsl:call-template> 236 </xsl:variable> 237 238 <xsl:choose> 239 <!-- if it is not the last parameter --> 240 <xsl:when test="starts-with(substring-after($expressionSuffix, $parameter), ',')"> 241 <!-- searches the argument for functions to be mapped --> 242 <xsl:if test="not($parameterRemoval = $parameterNumber)"> 243 <xsl:call-template name="function-parameter-mapping"> 244 <xsl:with-param name="rowPos" select="$rowPos" /> 245 <xsl:with-param name="columnPos" select="$columnPos" /> 246 <xsl:with-param name="expression"> 247 <xsl:choose> 248 <!-- in case a character will be removed the preceding won't make a comma --> 249 <xsl:when test="$parameterRemoval = ($parameterNumber + 1)"> 250 <xsl:value-of select="$parameter" /> 251 </xsl:when> 252 <xsl:otherwise> 253 <xsl:value-of select="concat($parameter, ',')" /> 254 </xsl:otherwise> 255 </xsl:choose> 256 </xsl:with-param> 257 </xsl:call-template> 258 </xsl:if> 259 <!-- searches for the next parameter --> 260 <xsl:call-template name="find-parameters"> 261 <xsl:with-param name="rowPos" select="$rowPos" /> 262 <xsl:with-param name="columnPos" select="$columnPos" /> 263 <xsl:with-param name="expressionSuffix" select="substring-after(substring-after($expressionSuffix, $parameter),',')"/> 264 <xsl:with-param name="parameterAddition" select="$parameterAddition" /> 265 <xsl:with-param name="parameterRemoval" select="$parameterRemoval" /> 266 <xsl:with-param name="additonAfterLastParameter" select="$additonAfterLastParameter" /> 267 <xsl:with-param name="parameterNumber" select="$parameterNumber + 1" /> 268 </xsl:call-template> 269 </xsl:when> 270 <xsl:otherwise> 271 <!-- the last parameter --> 272 <xsl:choose> 273 <xsl:when test="$parameterRemoval = $parameterNumber"> 274 <!-- searches the rest of the expression for functions to be mapped --> 275 <xsl:call-template name="function-parameter-mapping"> 276 <xsl:with-param name="rowPos" select="$rowPos" /> 277 <xsl:with-param name="columnPos" select="$columnPos" /> 278 <xsl:with-param name="expression" select="substring-after($expressionSuffix, $parameter)"/> 279 </xsl:call-template> 280 </xsl:when> 281 <xsl:when test="$parameterAddition and ($parameterNumber = $additonAfterLastParameter)"> 282 <!-- searches the rest of the expression for functions to be mapped --> 283 <xsl:call-template name="function-parameter-mapping"> 284 <xsl:with-param name="rowPos" select="$rowPos" /> 285 <xsl:with-param name="columnPos" select="$columnPos" /> 286 <xsl:with-param name="expression" select="$parameter" /> 287 </xsl:call-template> 288 <!-- searches last parameter and additional parameters for functions to be mapped --> 289 <xsl:call-template name="function-parameter-mapping"> 290 <xsl:with-param name="rowPos" select="$rowPos" /> 291 <xsl:with-param name="columnPos" select="$columnPos" /> 292 <!-- for the final parameter the latter substring is the ')' --> 293 <xsl:with-param name="expression" select="concat(',', $parameterAddition, substring-after($expressionSuffix, $parameter))"/> 294 </xsl:call-template> 295 </xsl:when> 296 <xsl:otherwise> 297 <!-- searches the argument for functions to be mapped --> 298 <xsl:call-template name="function-parameter-mapping"> 299 <xsl:with-param name="rowPos" select="$rowPos" /> 300 <xsl:with-param name="columnPos" select="$columnPos" /> 301 <xsl:with-param name="expression" select="$parameter" /> 302 </xsl:call-template> 303 <!-- searches the rest of the expression for functions to be mapped --> 304 <xsl:call-template name="function-parameter-mapping"> 305 <xsl:with-param name="rowPos" select="$rowPos" /> 306 <xsl:with-param name="columnPos" select="$columnPos" /> 307 <xsl:with-param name="expression" select="substring-after($expressionSuffix, $parameter)"/> 308 </xsl:call-template> 309 </xsl:otherwise> 310 </xsl:choose> 311 </xsl:otherwise> 312 </xsl:choose> 313 </xsl:template> 314 315 <xsl:template name="getParameter"> 316 <xsl:param name="closingBracketCount" select="0" /> 317 <xsl:param name="openingBracketCount" select="0" /> 318 <xsl:param name="expressionSuffix" /> 319 <xsl:param name="parameterCandidate"> 320 <xsl:choose> 321 <!-- if there are multiple parameter --> 322 <xsl:when test="contains(substring-before($expressionSuffix, ')'), ',')"> 323 <xsl:value-of select="substring-before($expressionSuffix, ',')"/> 324 </xsl:when> 325 <xsl:otherwise> 326 <xsl:value-of select="substring-before($expressionSuffix, ')')"/> 327 </xsl:otherwise> 328 </xsl:choose> 329 </xsl:param> 330 <xsl:param name="earlierCandidate" select="$parameterCandidate" /> 331 332 <xsl:choose> 333 <xsl:when test="contains($parameterCandidate, '(') or contains($parameterCandidate, ')')"> 334 <xsl:choose> 335 <!-- contains only closing bracket(s) --> 336 <xsl:when test="contains($parameterCandidate, '(') and not(contains($parameterCandidate, ')'))"> 337 <xsl:call-template name="getParameter"> 338 <xsl:with-param name="openingBracketCount" select="$openingBracketCount + 1" /> 339 <xsl:with-param name="closingBracketCount" select="$closingBracketCount" /> 340 <xsl:with-param name="parameterCandidate" select="substring-after($parameterCandidate, '(')" /> 341 <xsl:with-param name="earlierCandidate" select="$earlierCandidate" /> 342 <xsl:with-param name="expressionSuffix" select="$expressionSuffix"/> 343 </xsl:call-template> 344 </xsl:when> 345 <!-- contains only opening bracket(s) --> 346 <xsl:when test="not(contains($parameterCandidate, '(')) and contains($parameterCandidate, ')')"> 347 <xsl:call-template name="getParameter"> 348 <xsl:with-param name="openingBracketCount" select="$openingBracketCount" /> 349 <xsl:with-param name="closingBracketCount" select="$closingBracketCount + 1" /> 350 <xsl:with-param name="parameterCandidate" select="substring-after($parameterCandidate, ')')" /> 351 <xsl:with-param name="earlierCandidate" select="$earlierCandidate" /> 352 <xsl:with-param name="expressionSuffix" select="$expressionSuffix"/> 353 </xsl:call-template> 354 </xsl:when> 355 <xsl:otherwise> 356 <xsl:choose> 357 <xsl:when test="string-length(substring-before($parameterCandidate, '(')) < 358 string-length(substring-before($parameterCandidate, ')'))"> 359 <xsl:call-template name="getParameter"> 360 <xsl:with-param name="openingBracketCount" select="$openingBracketCount + 1" /> 361 <xsl:with-param name="closingBracketCount" select="$closingBracketCount" /> 362 <xsl:with-param name="parameterCandidate" select="substring-after($parameterCandidate, '(')" /> 363 <xsl:with-param name="earlierCandidate" select="$earlierCandidate" /> 364 <xsl:with-param name="expressionSuffix" select="$expressionSuffix"/> 365 </xsl:call-template> 366 </xsl:when> 367 <xsl:otherwise> 368 <xsl:call-template name="getParameter"> 369 <xsl:with-param name="openingBracketCount" select="$openingBracketCount" /> 370 <xsl:with-param name="closingBracketCount" select="$closingBracketCount + 1" /> 371 <xsl:with-param name="parameterCandidate" select="substring-after($parameterCandidate, ')')" /> 372 <xsl:with-param name="earlierCandidate" select="$earlierCandidate" /> 373 <xsl:with-param name="expressionSuffix" select="$expressionSuffix"/> 374 </xsl:call-template> 375 </xsl:otherwise> 376 </xsl:choose> 377 </xsl:otherwise> 378 </xsl:choose> 379 </xsl:when> 380 <xsl:otherwise> 381 <xsl:choose> 382 <xsl:when test="$openingBracketCount = $closingBracketCount"> 383 <xsl:value-of select="$earlierCandidate" /> 384 </xsl:when> 385 <xsl:otherwise> 386 <xsl:value-of select="$earlierCandidate" /> 387 <xsl:variable name="parameterCandidate2"> 388 <xsl:variable name="formularAfterCandidate" select="substring-after($expressionSuffix, $earlierCandidate)" /> 389 <xsl:variable name="parameterTillBracket" select="concat(substring-before($formularAfterCandidate,')'),')')" /> 390 <xsl:variable name="parameterTillComma" select="substring-before(substring-after($expressionSuffix, $parameterTillBracket),',')" /> 391 <xsl:choose> 392 <xsl:when test="string-length($parameterTillComma) > 0 and 393 not(contains($parameterTillComma, '('))"> 394 <xsl:choose> 395 <xsl:when test="starts-with($formularAfterCandidate, ',')"> 396 <xsl:value-of select="concat(',',substring-before(substring-after($formularAfterCandidate,','),','))"/> 397 </xsl:when> 398 <xsl:otherwise> 399 <xsl:value-of select="substring-before($formularAfterCandidate,',')"/> 400 </xsl:otherwise> 401 </xsl:choose> 402 </xsl:when> 403 <xsl:otherwise> 404 <xsl:value-of select="$parameterTillBracket"/> 405 </xsl:otherwise> 406 </xsl:choose> 407 </xsl:variable> 408 <xsl:call-template name="getParameter"> 409 <xsl:with-param name="closingBracketCount" select="$closingBracketCount" /> 410 <xsl:with-param name="openingBracketCount" select="$openingBracketCount" /> 411 <xsl:with-param name="parameterCandidate" select="$parameterCandidate2" /> 412 <xsl:with-param name="earlierCandidate" select="$parameterCandidate2" /> 413 <xsl:with-param name="expressionSuffix" select="$expressionSuffix" /> 414 </xsl:call-template> 415 </xsl:otherwise> 416 </xsl:choose> 417 </xsl:otherwise> 418 </xsl:choose> 419 </xsl:template> 420 421 <!-- Mapping table-cell definitions by exchangomg all table cell definitions: 422 a) a pair of cells e.g. "[.E14:.F21]" to "R[-17]C[3]:R[-10]C[4]" 423 b) a single cell e.g. "[.F14]" to "R[-17]"--> 424 <xsl:template name="translate-oooc-expression"> 425 <xsl:param name="rowPos" /> <!-- the position in row (vertical of cell) --> 426 <xsl:param name="columnPos" /> <!-- the position in column (horizontal of cell) --> 427 <xsl:param name="expression" /> <!-- expression to be exchanged --> 428 429 <xsl:choose> 430 <xsl:when test="contains($expression, '[')"> 431 432 <!-- Giving out the part before '[.' --> 433 <xsl:value-of select="substring-before($expression, '[')" /> 434 435 <!-- Mapping cell definitions 436 1) a pair of cells e.g. "[.E14:.F21]" to "R[-17]C[3]:R[-10]C[4]" 437 2) a single cell e.g. "[.F14]" to "R[-17]"--> 438 <xsl:variable name="remainingExpression" select="substring-after($expression, '[')"/> 439 <xsl:choose> 440 <xsl:when test="contains(substring-before($remainingExpression, ']'), ':')"> 441 <xsl:call-template name="translate-cell-expression"> 442 <xsl:with-param name="rowPos" select="$rowPos" /> 443 <xsl:with-param name="columnPos" select="$columnPos" /> 444 <xsl:with-param name="expression" select="substring-before($remainingExpression, ':')" /> 445 </xsl:call-template> 446 <xsl:value-of select="':'" /> 447 <xsl:call-template name="translate-cell-expression"> 448 <xsl:with-param name="rowPos" select="$rowPos" /> 449 <xsl:with-param name="columnPos" select="$columnPos" /> 450 <xsl:with-param name="expression" select="substring-after(substring-before($remainingExpression, ']'), ':')" /> 451 </xsl:call-template> 452 </xsl:when> 453 <xsl:otherwise> 454 <xsl:call-template name="translate-cell-expression"> 455 <xsl:with-param name="rowPos" select="$rowPos" /> 456 <xsl:with-param name="columnPos" select="$columnPos" /> 457 <xsl:with-param name="expression" select="substring-before($remainingExpression, ']')" /> 458 </xsl:call-template> 459 </xsl:otherwise> 460 </xsl:choose> 461 <xsl:call-template name="translate-oooc-expression"> 462 <xsl:with-param name="rowPos" select="$rowPos" /> 463 <xsl:with-param name="columnPos" select="$columnPos" /> 464 <xsl:with-param name="expression" select="substring-after($remainingExpression,']')"/> 465 </xsl:call-template> 466 </xsl:when> 467 <xsl:otherwise> 468 <!-- Giving out the remaining part --> 469 <xsl:value-of select="$expression" /> 470 </xsl:otherwise> 471 </xsl:choose> 472 </xsl:template> 473 474 475 <!-- A cell expression has usually starts with a '.' otherwise it references to a sheet --> 476 <xsl:template name="translate-cell-expression"> 477 <xsl:param name="rowPos" /> <!-- the vertical position of the current cell --> 478 <xsl:param name="columnPos" /> <!-- the horizontal position of the current cell --> 479 <xsl:param name="targetRowPos" select="0"/> <!-- the vertical position of the target cell --> 480 <xsl:param name="targetColumnPos" select="0"/> <!-- the horizontal position of the target cell --> 481 <xsl:param name="charPos" select="0"/> <!-- current column position (needed for multiplying) --> 482 <xsl:param name="digitPos" select="0"/> <!-- current row position (needed for multiplying) --> 483 <xsl:param name="expression" /> <!-- expression to be parsed by character --> 484 <xsl:param name="isRow" select="true()"/> <!-- the string (e.g. $D39 is parsed character per character from the back, 485 first the row, later the column is parsed --> 486 487 <xsl:choose> 488 <xsl:when test="starts-with($expression, '.')"> 489 <xsl:variable name="expLength" select="string-length($expression)" /> 490 <xsl:choose> 491 <!-- parsing from the end, till only the '.' remains --> 492 <xsl:when test="$expLength != 1"> 493 <xsl:variable name="token" select="substring($expression, $expLength)" /> 494 <xsl:choose> 495 <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'"> 496 <xsl:variable name="multiplier"> 497 <xsl:call-template name="calculate-square-numbers"> 498 <xsl:with-param name="base" select="10" /> 499 <xsl:with-param name="exponent" select="$digitPos"/> 500 </xsl:call-template> 501 </xsl:variable> 502 <xsl:call-template name="translate-cell-expression"> 503 <xsl:with-param name="columnPos" select="$columnPos" /> 504 <xsl:with-param name="rowPos" select="$rowPos" /> 505 <xsl:with-param name="targetColumnPos" select="$targetColumnPos" /> 506 <xsl:with-param name="targetRowPos" select="$targetRowPos + $multiplier * $token" /> 507 <xsl:with-param name="digitPos" select="$digitPos + 1" /> 508 <xsl:with-param name="charPos" select="$charPos" /> 509 <!-- removing the last character--> 510 <xsl:with-param name="expression" select="substring($expression, 1, $expLength - 1)" /> 511 <xsl:with-param name="isRow" select="true()" /> 512 </xsl:call-template> 513 </xsl:when> 514 <xsl:when test="$token = '$'"> 515 <xsl:choose> 516 <!-- if this is the first '$' after '.' (column--> 517 <xsl:when test="$expLength = 2"> 518 <xsl:text>C</xsl:text><xsl:value-of select="$targetColumnPos"/> 519 </xsl:when> 520 <xsl:otherwise> 521 <xsl:text>R</xsl:text><xsl:value-of select="$targetRowPos"/> 522 <xsl:call-template name="translate-cell-expression"> 523 <xsl:with-param name="columnPos" select="$columnPos" /> 524 <xsl:with-param name="rowPos" select="$rowPos" /> 525 <xsl:with-param name="targetColumnPos" select="$targetColumnPos" /> 526 <xsl:with-param name="targetRowPos" select="$targetRowPos" /> 527 <xsl:with-param name="charPos" select="$charPos" /> 528 <!-- removing the last character--> 529 <xsl:with-param name="expression" select="substring($expression, 1, $expLength - 1)" /> 530 <xsl:with-param name="isRow" select="false()" /> 531 </xsl:call-template> 532 </xsl:otherwise> 533 </xsl:choose> 534 </xsl:when> 535 <!-- in case of a letter --> 536 <xsl:otherwise> 537 <xsl:if test="$isRow"> 538 <xsl:text>R</xsl:text> 539 <xsl:if test="$targetRowPos != $rowPos"> 540 <xsl:text>[</xsl:text><xsl:value-of select="$targetRowPos - $rowPos"/><xsl:text>]</xsl:text> 541 </xsl:if> 542 </xsl:if> 543 <xsl:variable name="multiplier"> 544 <xsl:call-template name="calculate-square-numbers"> 545 <xsl:with-param name="base" select="26" /> 546 <xsl:with-param name="exponent" select="$charPos"/> 547 </xsl:call-template> 548 </xsl:variable> 549 <xsl:variable name="tokenNumber"> 550 <xsl:call-template name="character-to-number"> 551 <xsl:with-param name="character" select="$token" /> 552 </xsl:call-template> 553 </xsl:variable> 554 555 <xsl:call-template name="translate-cell-expression"> 556 <xsl:with-param name="columnPos" select="$columnPos" /> 557 <xsl:with-param name="rowPos" select="$rowPos" /> 558 <xsl:with-param name="targetColumnPos" select="$targetColumnPos + $multiplier * $tokenNumber" /> 559 <xsl:with-param name="targetRowPos" select="$targetRowPos" /> 560 <xsl:with-param name="digitPos" select="$digitPos" /> 561 <xsl:with-param name="charPos" select="$charPos + 1" /> 562 <!-- removing the last character--> 563 <xsl:with-param name="expression" select="substring($expression, 1, $expLength - 1)" /> 564 <xsl:with-param name="isRow" select="false()" /> 565 </xsl:call-template> 566 </xsl:otherwise> 567 </xsl:choose> 568 </xsl:when> 569 <xsl:otherwise> 570 <xsl:text>C</xsl:text> 571 <xsl:if test="$targetColumnPos != $columnPos"> 572 <xsl:text>[</xsl:text><xsl:value-of select="$targetColumnPos - $columnPos"/><xsl:text>]</xsl:text> 573 </xsl:if> 574 </xsl:otherwise> 575 </xsl:choose> 576 </xsl:when> 577 <xsl:otherwise> 578 <xsl:variable name="sheetName" select="substring-before($expression, '.')" /> 579 <xsl:value-of select="$sheetName"/><xsl:text>!</xsl:text> 580 <xsl:call-template name="translate-cell-expression"> 581 <xsl:with-param name="rowPos" select="$rowPos" /> 582 <xsl:with-param name="columnPos" select="$columnPos" /> 583 <xsl:with-param name="expression" select="substring-after($expression, $sheetName)" /> 584 </xsl:call-template> 585 </xsl:otherwise> 586 </xsl:choose> 587 </xsl:template> 588 589 590 <xsl:template name="calculate-square-numbers"> 591 <xsl:param name="base" /> 592 <xsl:param name="exponent" /> 593 <xsl:param name="return" select="1" /> 594 595 <xsl:choose> 596 <xsl:when test="$exponent > '1'"> 597 <xsl:call-template name="calculate-square-numbers"> 598 <xsl:with-param name="base" select="$base" /> 599 <xsl:with-param name="exponent" select="$exponent - 1"/> 600 <xsl:with-param name="return" select="$return * $base" /> 601 </xsl:call-template> 602 </xsl:when> 603 <xsl:when test="$exponent = '1'"> 604 <xsl:value-of select="$return * $base"/> 605 </xsl:when> 606 <!-- if exponent is equal '0' --> 607 <xsl:otherwise> 608 <xsl:value-of select="1"/> 609 </xsl:otherwise> 610 </xsl:choose> 611 </xsl:template> 612 613 614 <xsl:template name="character-to-number"> 615 <xsl:param name="character" /> 616 <xsl:choose> 617 <xsl:when test="$character = 'A'">1</xsl:when> 618 <xsl:when test="$character = 'B'">2</xsl:when> 619 <xsl:when test="$character = 'C'">3</xsl:when> 620 <xsl:when test="$character = 'D'">4</xsl:when> 621 <xsl:when test="$character = 'E'">5</xsl:when> 622 <xsl:when test="$character = 'F'">6</xsl:when> 623 <xsl:when test="$character = 'G'">7</xsl:when> 624 <xsl:when test="$character = 'H'">8</xsl:when> 625 <xsl:when test="$character = 'I'">9</xsl:when> 626 <xsl:when test="$character = 'J'">10</xsl:when> 627 <xsl:when test="$character = 'K'">11</xsl:when> 628 <xsl:when test="$character = 'L'">12</xsl:when> 629 <xsl:when test="$character = 'M'">13</xsl:when> 630 <xsl:when test="$character = 'N'">14</xsl:when> 631 <xsl:when test="$character = 'O'">15</xsl:when> 632 <xsl:when test="$character = 'P'">16</xsl:when> 633 <xsl:when test="$character = 'Q'">17</xsl:when> 634 <xsl:when test="$character = 'R'">18</xsl:when> 635 <xsl:when test="$character = 'S'">19</xsl:when> 636 <xsl:when test="$character = 'T'">20</xsl:when> 637 <xsl:when test="$character = 'U'">21</xsl:when> 638 <xsl:when test="$character = 'V'">22</xsl:when> 639 <xsl:when test="$character = 'W'">23</xsl:when> 640 <xsl:when test="$character = 'X'">24</xsl:when> 641 <xsl:when test="$character = 'Y'">25</xsl:when> 642 <xsl:when test="$character = 'Z'">26</xsl:when> 643 <xsl:otherwise/> 644 </xsl:choose> 645 </xsl:template> 646 647</xsl:stylesheet> 648