Mercurial > hg > ooxml

changeset 36:ae605b77d1e4

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
compute (but not use) master formula cells info, extend refs collection to include abs/reloc info
author Henry S. Thompson <ht@markup.co.uk>
date Tue, 25 Apr 2017 12:24:31 +0100
parents e500d7c18aad
children ac3cd8de7a10
files a2n.xsl notes.txt refs.xsl refs2.xsl visualise.xpl
diffstat 5 files changed, 121 insertions(+), 15 deletions(-) [+]
line wrap: on
line diff
--- a/a2n.xsl	Thu Apr 13 23:09:58 2017 +0100
+++ b/a2n.xsl	Tue Apr 25 12:24:31 2017 +0100
@@ -1,13 +1,42 @@
 <?xml version='1.0'?>
 <!DOCTYPE xsl:stylesheet SYSTEM "file:///C:/C64/home/ht/lib/xml/xsl.dtd" >
-<xsl:stylesheet xmlns:e="http://markup.co.uk/excel" xmlns:xs="http://www.w3.org/2001/XMLSchema" version="3.0">
-  <xsl:function name="e:a2n" as="xs:integer">
+<xsl:stylesheet xmlns:e="http://markup.co.uk/excel" xmlns:xs="http://www.w3.org/2001/XMLSchema" version="3.0" xmlns:xpf="http://www.w3.org/2005/xpath-functions" exclude-result-prefixes="e xpf xs" xmlns="http://markup.co.uk/excel">
+
+ <xsl:function name="e:a2n" as="xs:integer">
   <!-- Convert an alphabetic excel column 'index' into a number,
        a sort of base 26 + 1, since 'A' is 1 and 'AA' is 27 -->
-  <xsl:param name="aa" as="xs:string"/>
+  <xsl:param name="aa" as="xs:string" required="yes"/>
+ <xsl:variable name="codeBase" select="string-to-codepoints('A')-1"/>
   <xsl:value-of select="let $l := string-length($aa),
                         $lv := string-to-codepoints(substring($aa,$l))-$codeBase
                         return if ($l=1) then $lv
                                else $lv+(26*e:a2n(substring($aa,1,$l - 1)))"/>
  </xsl:function>
+ 
+ <xsl:function name="e:cr" as="element(e:s)">
+  <!-- Parse a single cell reference into left and right halves,
+       either e:a(bsolute) or e:r(elocatable) -->
+  <xsl:param name="ref" as="xs:string" required="yes"/>
+  <xsl:param name="row" as="xs:integer" required="yes"/>
+  <xsl:param name="col" as="xs:integer" required="yes"/>
+  <xsl:variable name="cr" select="analyze-string($ref,'([$]?)([A-Z]+)([$]?)([0-9]+)')"/>
+  <xsl:variable name="c" select="if ($cr//xpf:group[@nr=1][text()]) then 'a' else 'r'"/>
+  <xsl:variable name="cv" select="e:a2n($cr//xpf:group[@nr=2])"/>
+  <xsl:variable name="r" select="if ($cr//xpf:group[@nr=3][text()]) then 'a' else 'r'"/>
+  <xsl:variable name="rv" select="xs:integer($cr//xpf:group[@nr=4])"/>
+  <s r="{$ref}">
+   <xsl:element name="{$c}" namespace="http://markup.co.uk/excel">
+    <xsl:if test="$c='r'">
+     <xsl:attribute name="d"><xsl:value-of select="$cv - $col"/></xsl:attribute>
+    </xsl:if>
+    <xsl:value-of select="$cv"/>
+   </xsl:element>
+   <xsl:element name="{$r}" namespace="http://markup.co.uk/excel">
+    <xsl:if test="$r='r'">
+     <xsl:attribute name="d"><xsl:value-of select="$rv - $row"/></xsl:attribute>
+    </xsl:if>
+    <xsl:value-of select="$rv"/>
+   </xsl:element>
+  </s>  
+ </xsl:function>
 </xsl:stylesheet>
--- a/notes.txt	Thu Apr 13 23:09:58 2017 +0100
+++ b/notes.txt	Tue Apr 25 12:24:31 2017 +0100
@@ -66,16 +66,20 @@
  Solo local vars are recursively dereferenced
  The definition table is in workbook.xml definedNames/definedName[@name=$name]/.
   Sheet name to filename mapping for locals is in workbook.xml sheets/sheet[@name=$sname]/@sheetId
- Variables on l or r of ranges are just looked up: if they are complex
+ ??? Variables on l or r of ranges are just looked up: if they are complex
   no recursion is done: the _semantics_ of this case are not clear to
   me, need a real-life example... 
+ @@ Variables whose value is itself a range are not being handled
 -----------
 Switch to default namespace in order to reduce size and improve
 readability, and to elements instead of attributes DONE
 -----------
 Should put another step after refs.xsl to compute a map from
 distinct-values of all targets to all the cells which use them
-(likewise ranges) DONE. That really does mean we should move to elts for
+DONE.
+Likewise ranges @@
+
+That really does mean we should move to elts for
 each ref or range, since at this point we want to compute vector
 representation as well DONE, so we can identify projections
 
@@ -141,3 +145,15 @@
                                        highlighted cells are being
                                         labelled as cur, e.g. B61 in
                                         output of format.xsl FIXED
+-----------
+Need to rethink variable handling...
+Is all we really need a normalised formula computation:
+ 1) recursively replace variables;
+ 2) convert all simple refs to new CR string normal form: 
+    crnf ::= col row
+    col ::= abs | rel
+    row ::= abs | rel
+    abs ::= '\xAA' xs:positiveInteger
+    rel ::= '\xAE' ( ( '-' xs:positiveInteger ) | xs:nonNegativeInteger )
+
+    
--- a/refs.xsl	Thu Apr 13 23:09:58 2017 +0100
+++ b/refs.xsl	Tue Apr 25 12:24:31 2017 +0100
@@ -3,6 +3,8 @@
 <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="3.0" xmlns:s="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:e="http://markup.co.uk/excel" exclude-result-prefixes="xs s e xf" xmlns="http://markup.co.uk/excel" xmlns:xf="http://www.w3.org/2005/xpath-functions">
  <xsl:param name="sheet-number"/>
  <xsl:param name="xlDir"/>
+ 
+ <xsl:include href="a2n.xsl"/>
 
   <xsl:variable name="pat1">("[^"]*")|(\{[^}]+})|(,)|([^=\-+*/();:,.$&lt;>^!]+(?:\.[^=\-+*/();:,.$&lt;>^!]+)*\()|([)])|(^=|\()|((?:'[^']+')|(?:\[[0-9]+\][^!]*))|(\$?[A-Z]+\$?[0-9]+)|([a-zA-Z_\\][a-zA-Z0-9._]*)|(.)</xsl:variable>
  <xsl:param name="pat" select="$pat1"/><!-- xsl:param for refinement debugging by passing in the pattern -->
@@ -23,6 +25,9 @@
  <xsl:function name="e:tokenise" as="array(element(*)*)*">
   <!-- Tokenise a formula, recursively wrt variables -->
   <xsl:param name="formula" as="xs:string" required="yes"/>
+  <!-- The row and column number of the cell whence the formula came -->
+  <xsl:param name="row" required="yes" as="xs:int"/>
+  <xsl:param name="col" required="yes" as="xs:int"/>
   <xsl:sequence select="
     let $tokens := analyze-string($formula,$pat)/xf:match/xf:group
      return if ($tokens[@nr=(7,8,9)])
@@ -40,7 +45,8 @@
                             else (),
                   $defns := for $var in $vars return e:lookup($var),
                   $recur := for $sub in $defns 
-                              return if ($sub) then e:tokenise($sub) else (),
+                              return if ($sub) then e:tokenise($sub,$row,$col)
+                                               else (),
                   $singles := for $i in (1 to $n) return
                             let $t := $tokens[$i],
                                 $l := $tokens[$i - 1],
@@ -49,7 +55,7 @@
                                 not($l[@nr=10 and
                                        .=(':','!')]) and
                                 not($r[@nr=10 and .=':']))
-                             then e:single($t,false())
+                             then e:single($t,$row,$col,false())
                              else (),
                   $ranges := for $i in (1 to count($tokens)) return
                             let $t := $tokens[$i] return
@@ -58,8 +64,10 @@
                                        $tokens[$i - 2][@nr=10 and .='!'])])
                              then let $l := $tokens[$i - 1],
                                       $r := $tokens[$i + 1]
-                                      return e:range(e:single($l,false()),
-                                                     e:single($r,false()))
+                                      return e:range(e:single($l,
+                                                             $row,$col,false()),
+                                                     e:single($r,
+                                                             $row,$col,false()))
                              else (),
                   $externals := for $i in (1 to count($tokens)) return
                             let $t := $tokens[$i] return
@@ -67,12 +75,12 @@
                              then 
                               let $ext := $t!='[0]',
                                   $ref := e:single($tokens[$i + 2],
-                                                   $ext),
+                                                   $row,$col,$ext),
                                   $res := if ((($i+3) le $n) and
                                               $tokens[$i + 3][@nr=10 and .=':'])
                                            then e:range($ref,
                                                         e:single($tokens[$i+4],
-                                                                 $ext))
+                                                                $row,$col,$ext))
                                            else $ref return
                               if ($ext)
                                then e:external($t,$res)
@@ -86,18 +94,19 @@
  
  <xsl:function name="e:single" as="element(*)">
   <xsl:param name="group" as="element(xf:group)"/>
+  <xsl:param name="row" as="xs:integer"/>
+  <xsl:param name="col" as="xs:integer"/>
   <xsl:param name="external" as="xs:boolean"/>
   <xsl:variable name="val" select="if ($group/@nr=9) then e:lookup($group)
                                              else string($group)"/>
   <xsl:choose>
    <xsl:when test="count($val)>0 or not($external)">
-    <s><xsl:value-of select="$val"/></s>
+    <xsl:sequence select="e:cr($val,$row,$col)"/>
    </xsl:when>
    <xsl:otherwise>
     <v><xsl:value-of select="$group"/></v>
    </xsl:otherwise>
-  </xsl:choose>
-  
+  </xsl:choose>  
  </xsl:function>
  
  <xsl:function name="e:range" as="element(e:r)">
@@ -117,7 +126,8 @@
  </xsl:template>
  
  <xsl:template match="s:c[s:f]">
-  <xsl:variable name="tokens" select="e:tokenise(s:f/.)"/>
+  <xsl:variable name="cr" select="e:cr(@r,0,0)"/>
+  <xsl:variable name="tokens" select="e:tokenise(s:f/.,$cr/e:r[1],$cr/e:r[2])"/>
   <xsl:if test="@r='xxx'"><xsl:message><xsl:value-of select="s:f"/>|<xsl:value-of select="(analyze-string(s:f/.,$pat)/xf:match/xf:group)[3]/@nr"/></xsl:message>
   </xsl:if>
   <xsl:if test="count($tokens)>0">
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/refs2.xsl	Tue Apr 25 12:24:31 2017 +0100
@@ -0,0 +1,33 @@
+<?xml version='1.0'?>
+<!DOCTYPE doc SYSTEM "../../../lib/xml/xsl.dtd" >
+<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="3.0" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:e="http://markup.co.uk/excel" exclude-result-prefixes="xs e" xmlns="http://markup.co.uk/excel">
+ <!-- Invert the singleton references extracted from formulae by refs.xsl 
+      Input e:refs/e:ref/e:s  e:ref[@c] contains one or more e:s
+                                        for each singleton reference
+                                        to e:s/@r in c's formula
+      Output e:refs/e:i/e:r e:i[@r] contains one e:r for each cell which
+                                     references r -->
+ 
+ <xsl:include href="n2a.xsl"/>
+ <xsl:include href="a2n.xsl"/>
+ 
+ <xsl:key name="ref" match="e:ref" use="for $s in e:s return translate($s/@r,'$','')"/>
+ 
+ <xsl:template match="e:refs">
+  <xsl:variable name="refs" select="."/>
+  <xsl:variable name="invert">
+  <xsl:for-each select="distinct-values(
+                        for $s in e:ref/e:s return translate($s/@r,'$',''))">
+   <xsl:variable name="r" select="."/>
+   <i r="{$r}">
+    <xsl:for-each select="key('ref',$r,$refs)">
+     <r><xsl:value-of select="@c"/></r>
+    </xsl:for-each>
+   </i>
+  </xsl:for-each>
+ </xsl:variable>
+  <xsl:copy>
+   <xsl:copy-of select="$invert"/>
+  </xsl:copy>
+ </xsl:template>
+</xsl:stylesheet>
--- a/visualise.xpl	Thu Apr 13 23:09:58 2017 +0100
+++ b/visualise.xpl	Tue Apr 25 12:24:31 2017 +0100
@@ -52,6 +52,24 @@
   <p:with-param name="xlDir" select="$root"/>
  </p:xslt> 
  
+  <p:load name="ssm">
+  <p:with-option name="href" select="concat($elabDir,'/shared.xsl')"/>
+  <p:documentation>
+   <div>Detect and tabulate shared formalae master cells</div>
+  </p:documentation>
+ </p:load>
+
+ <p:xslt name="shared">
+   <p:input port="source">
+    <p:pipe step="vis" port="source"/>
+   </p:input>
+  <p:input port="stylesheet">
+   <p:pipe step="ssm" port="result"/>
+  </p:input>
+  <p:with-param name="elabDir" select="$elabDir"/>
+  <p:with-param name="xlDir" select="$root"/>
+ </p:xslt>
+
  <p:load name="ssr">
   <p:with-option name="href" select="concat($elabDir,'/refs.xsl')"/>
   <p:documentation>