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