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