2006.01.05 04:28 AM

Round-tripping Excel's SpreadsheetML

Update 11/30/2006

Wow. Can't believe it's been nearly a year since I wrote this. I'm back because reader marc was kind enough to report a problem php's parser was having with the first stylesheet below (see the comments for marc's original report). After getting up to speed with php 5.1.2 (the version marc tested) and 5.2.0 (the latest as of this writing), I was able to reproduce the problem with both versions. Based on the results, and past history (detailed in the post below), I was sure the problem was with the reverse axis node-set evaluation in the GetCellCol template. And sure enough, it was. Turns out that php, like .NET, but unlike MSXML, evaluates position()-based predicates on reverse axis node-sets derived from node-set variables in the wrong direction - down instead of up. I only added the node-set variables in the GetCellRow and GetCellCol templates to make the code cleaner, so it was no problem taking them out. Given all the trouble they've caused, I probably should have done it a long time ago. Now it's done and I've updated the stylesheet listing below and the one linked to without the line numbers (you might have to hard refresh your browser to get the latest one).

I've now successfully tested (at least the first stylesheet) with MSXML 4.0, MSXML 5.0, .NET 1.1, .NET 2.0, php 5.1.2, and php 5.2.0. And reader Andy Elvey previously tested with Saxon 8.6.1 (see the 1/23/2006 update below). Still, be careful and run your own tests.

Thanks again, marc.

Update 1/23/2006

Oops. See the comments. Andy Elvey kindly pointed out that Saxon had a problem with the first stylesheet below, which transforms SpreadsheetML into my simple XML. In my reply, I suggested it might be caused by my adding text nodes to elements prior to also adding attributes to the same elements, which is contrary to the XSL spec, which says:

All attributes must be applied before children are added to the element.

I suggested switching the order of each xsl:if $checking test with their following xsl:attribute line, but then I later realized that, if indeed the problem is what I suspect, this wouldn't work, as many of the templates include multiple attributes and text node-producing $checking tests, all of which are evaluated in the context of an element. In order to work, the xsl:attribute and xsl:if $checking tests would have to be made mutually exclusive using xsl:choose. So, that's what I did. I updated the listing here, and the one linked to without the line numbers.

What I find most interesting about this is that MSXML 4.0, which is the only parser I've tested all this with, has no problem with the original code. Go figure. Btw, I still haven't tested this with Saxon, but did verify MSXML produces the exact same results as before.

Thanks Andy.


In an earlier post, I described how to use XSLT to simplify Excel's SpreadsheetML XML, mostly to illustrate the calculation of actual rows and columns from SpreadsheetML's more difficult offset-based values.

In a later post, at the request of a reader, I explored how one might author spreadsheets using a form of XML simpler than SpreadsheetML and then turn that simpler XML into useful SpreadsheetML using XSLT.

What I didn't do in the second post was try to turn the XML resulting from the first post back into SpreadsheetML using XSLT. In fact, a large part of the second post was spent explaining how the XML generated in the first post was too simple to be useful as SpreadsheetML.

Well, another reader recently asked whether I ever went back to the first post and updated its XSLT to produce (from SpreadsheetML) the XML described in the second post. If I had, the reader was interested in having it, as the XML in the second post included useful cell information not found in the first post's XML, like formulas and data types. In addition, if I had, folks would have been able to round-trip their SpreadsheetML, presumably with some manipulation of the simpler XML somewhere along the way.

That's a good idea. I wish I'd have thought of it, but I didn't. In fact, until it was brought to my attention, it didn't even occur to me that it might be useful. However, now that it has been brought to my attention, I'm delivering the goods.

Below are two new stylesheets. The first stylesheet turns SpreadsheetML into a simple form of XML very much like the one described in the second earlier post. And the second stylesheet turns that simple form of XML back into SpreadsheetML. The simple form of XML produced by the first stylesheet doesn't exactly match the XML from my earlier second post because I wanted to add support for more styling information without increasing the XML's complexity. To do this, I opted to treat the styling values as attributes of the style element instead of expanding the number of child elements. In addition, I added support for retaining named ranges. I also standardized on an id attribute for capturing element identifiers (these were previously mixed as both id and name attributes).

When evaluating my simple spreadsheet XML, please keep in mind that its primary goal is to simplify manual and programmatic spreadsheet authoring by eliminating a number of constraints and annoyances found in SpreadsheetML. For instance, it eliminates SpreadsheetML's Styles and Names container elements so that style and name elements can be placed anywhere within their respective book and sheet elements. It doesn't require cells to appear in proper row and column order within a sheet, which can greatly simplify programmatic creation of new spreadsheets. And it eliminates SpreadsheetML's many namespaces in order to ease subsequent manipulations of the XML.

Of course, this simplification comes at the expense of features. My simple spreadsheet XML doesn't support all of the styling features found in SpreadsheetML (though you can add more if you like). It doesn't retain named styles. It doesn't retain row or column based cell-spans (though the stylesheet below that converts SpreadsheetML to my spreadsheet XML does take them into consideration when calculating rows and columns). It doesn't support partial in-cell styling of contents using HTML, as found in SpreadsheetML. And it doesn't support many of the advanced Excel features that can be expressed in SpreadsheetML, like pivot tables, conditional formatting, window object settings, and data retrieval.

One more thing to mention before showing the code. There were a couple of comments made on my first post suggesting that the logic I used to calculate columns from SpreadsheetML was flawed. After much debugging I determined that they were correct, there was indeed a flaw. However, it was not the one they reported. First ajr wrote:

Has anyone had a problem with the column attribute? It seems to be incorrect for some cells w/o an explicit index. I'm not sure but it might involve cells that follow empty (not appearing the the xml) cells.

Then jc wrote:

this code will fail for all cells lacking an ss:Index that are preceded by two or more missing cells

for instance, the input cells (two missing cells, each followed by a cell with an ss:Index)...

1,2,3,4,5,6,,8,,10,11,12

will result in output cells with indices...

1,2,3,4,5,6,8,10,10,11

i haven't tested fully, but in practice i think the output index count is one too low for each one or more contiguous cells with an ss:Index attribute

And then jc followed up with this:

i did a little searching/experimenting, i'm working on a modified version of the original sheet so this may not be the best/complete answer, but for my usage i it seems to resolve the problem if the column index calculation in the third xsl:when uses this...

count(preceding-sibling::*) - count(preceding-sibling::*[@ss:Index][1]/preceding-sibling::*) + preceding-sibling::*[@ss:Index][1]/@ss:Index

...instead of...

($prevCells[@ss:Index][position()=1]/@ss:Index) + ((count($prevCells) + 1) - (count($prevCells[@ss:Index][position()=1]/preceding-sibling::ss:Cell) + 1)) + sum($prevCells/@ss:MergeAcross)

btw, the calculation came from here...

http://www.biglist.com/lists/xsl-list/archives/200508/msg00538.html

I compared my logic and Dimitre Novatchev's logic (he's the fellow who authored the logic that jc quotes above from the biglist.com thread) and ran a series of tests. I found that our logic matched in all cases except one, and in that one case mine was correct. What Mr. Novatchev's logic does is count all the cells preceding the current one, subtract from it the number of cells found to precede the first cell preceding the current cell found to have an explicit Index attribute, and then adds back to this the Index value found in the first cell preceding the current cell found to have an explicit Index. My logic does essentially the same thing, but in a slightly different order, and with the aid of a variable representing the cells preceding the current one (the variable $prevCells is shorthand for preceding-sibling::ss:Cell; note that Mr. Novatchev uses the bit looser preceding-sibling::* - I prefer to be more explicit). In short, my logic takes the Index value from the first cell found to have one preceding the current cell and adds to it the difference between the count of all the previous cells and the number of cells preceding the first cell preceding the current cell found to have an explicit Index (with some unnecessary +1's tossed in) and then adds to this the count of all merged cells. So, the only real difference between my logic and Mr. Novatchev's is in the consideration for merged cells - I consider them, he doesn't, and that's the only situation that I found that our results differ.

So why the problem report by jc and ajr? The only thing I can think might be a problem is if they are trying to use .NET's XslTransform to run my logic. As I pointed out in this post, .NET suffers from a couple of XPath problems: it can't handle node-set variables and template parameters followed by two or more predicates in an XPath expression, and position()-based filters in predicates against reverse axis node-sets are evaluated incorrectly (the node-set evaluated is not reversed). Good old MSXML doesn't have these problems, and its the only parser I've successfully tested these transformations against. Mr. Novatchev's logic doesn't use a node-set variable, and so doesn't suffer from the first problem, and he uses the simpler node position syntax ([x]), which I think may avoid the position()-based problem on a reverse axis node-set (I haven't confirmed this, but will). If this is case, then my new logic should work in .NET, assuming they eliminate the use of a variable to represent the preceding siblings, as I also reverted to the simpler node position syntax. Hopefully I'll get a chance to test all this out soon.

Now, regarding that flaw I mentioned above. While testing my logic against Mr. Novatchev's, I discovered that my consideration for merged cells was unfortunately incorrect. I should not have included the count of merged cells occurring before the first preceding cell found to have an explicit Index. Doing so resulted in too many cells being considered, as the Index already accounts for any preceding merged cells. Here's my new logic after the fix, and after eliminating the use of position() and removal of the extraneous +1's:

($prevCells[@ss:Index][1]/@ss:Index) + 
(count($prevCells) - count($prevCells[@ss:Index][1]/preceding-sibling::ss:Cell)) +
sum(($prevCells[@ss:Index][1]/following-sibling::ss:Cell | $prevCells[@ss:Index][1])/@ss:MergeAcross)

Now, finally, the code.

The following stylesheet (available here without line numbers) will convert SpreadsheetML to my simple spreadsheet XML:

  1 <?xml version="1.0"?>
  2 <!--
  3 This product may incorporate intellectual property owned by Microsoft Corporation. The terms 
  4 and conditions upon which Microsoft is licensing such intellectual property may be found at 
  5 http://msdn.microsoft.com/library/en-us/odcXMLRef/html/odcXMLRefLegalNotice.asp.
  6 -->
  7 <xsl:stylesheet version="1.0"
  8                 xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
  9                 xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
 10                 exclude-result-prefixes="ss">
 11 
 12   <xsl:output method="xml" indent="yes" encoding="UTF-8"/>
 13   
 14   <xsl:template match="/ss:Workbook[1]">
 15     <book>    
 16       <xsl:apply-templates select="ss:Styles/ss:Style"/>
 17       <xsl:apply-templates select="ss:Names/ss:NamedRange"/>
 18       <xsl:apply-templates select="ss:Worksheet"/>
 19     </book>
 20   </xsl:template>
 21   
 22   <!-- Worksheets -->
 23   
 24   <xsl:template match="ss:Worksheet">
 25     <sheet>
 26       <xsl:attribute name="id"><xsl:value-of select="@ss:Name"/></xsl:attribute>
 27       <xsl:apply-templates select="ss:Names/ss:NamedRange"/>
 28       <xsl:apply-templates select="ss:Table/ss:Row/ss:Cell[./ss:Data]"/>
 29     </sheet>
 30   </xsl:template>
 31   
 32   <xsl:template match="ss:Worksheet/ss:Table/ss:Row/ss:Cell">  
 33     <cell>
 34       <xsl:attribute name="row"><xsl:call-template name="GetCellRow"/></xsl:attribute>
 35       <xsl:attribute name="col"><xsl:call-template name="GetCellCol"/></xsl:attribute>
 36       <xsl:attribute name="type"><xsl:value-of select="ss:Data/@ss:Type"/></xsl:attribute>
 37       <xsl:apply-templates select="@ss:StyleID"/>
 38       <xsl:if test="@ss:Formula">
 39         <xsl:attribute name="formula"><xsl:value-of select="@ss:Formula"/></xsl:attribute>
 40       </xsl:if>
 41       <xsl:value-of select="ss:Data"/>
 42     </cell>
 43   </xsl:template>
 44   
 45   <xsl:template name="GetCellRow">
 46     <xsl:choose>
 47       <xsl:when test="../@ss:Index">
 48         <xsl:value-of select="../@ss:Index"/>
 49       </xsl:when>
 50       <xsl:when test="count(../preceding-sibling::ss:Row) = 0">
 51         <xsl:value-of select="1"/>
 52       </xsl:when>
 53       <xsl:when test="count(../preceding-sibling::ss:Row[@ss:Index]) > 0">
 54         <xsl:value-of select="(../preceding-sibling::ss:Row[@ss:Index][1]/@ss:Index) + 
((count(../preceding-sibling::ss:Row) + 1) - 
(count(../preceding-sibling::ss:Row[@ss:Index][1]/preceding-sibling::ss:Row) + 1))"/>
 55       </xsl:when>
 56       <xsl:otherwise>
 57         <xsl:value-of select="count(../preceding-sibling::ss:Row) + 1"/>
 58       </xsl:otherwise>
 59     </xsl:choose>              
 60   </xsl:template>
 61   
 62   <xsl:template name="GetCellCol">
 63     <xsl:choose>
 64       <xsl:when test="@ss:Index">
 65         <xsl:value-of select="@ss:Index"/>
 66       </xsl:when>
 67       <xsl:when test="count(preceding-sibling::ss:Cell) = 0">
 68         <xsl:value-of select="1"/>
 69       </xsl:when>
 70       <xsl:when test="count(preceding-sibling::ss:Cell[@ss:Index]) > 0">
 71         <xsl:value-of select="(preceding-sibling::ss:Cell[@ss:Index][1]/@ss:Index) + (count(preceding-sibling::ss:Cell) - 
count(preceding-sibling::ss:Cell[@ss:Index][1]/preceding-sibling::ss:Cell)) + 
sum((preceding-sibling::ss:Cell[@ss:Index][1]/following-sibling::ss:Cell | 
preceding-sibling::ss:Cell[@ss:Index][1])/@ss:MergeAcross)"/>
 72       </xsl:when>
 73       <xsl:otherwise>
 74         <xsl:value-of select="count(preceding-sibling::ss:Cell) + 1 + sum(preceding-sibling::ss:Cell/@ss:MergeAcross)"/>
 75       </xsl:otherwise>
 76     </xsl:choose>              
 77   </xsl:template>
 78   
 79   <!-- NamedRanges -->
 80   
 81   <xsl:template match="ss:Names/ss:NamedRange">
 82     <name>
 83       <xsl:attribute name="id"><xsl:value-of select="@ss:Name"/></xsl:attribute>
 84       <xsl:attribute name="formula"><xsl:value-of select="@ss:RefersTo"/></xsl:attribute>    
 85     </name>
 86   </xsl:template>
 87   
 88   <!-- Styles -->
 89 
 90   <xsl:template match="@ss:StyleID">
 91     <xsl:apply-templates select="/ss:Workbook/ss:Styles/ss:Style[@ss:ID = current()]" mode="check">
 92       <xsl:with-param name="return" select="'id'"/>
 93     </xsl:apply-templates>
 94   </xsl:template>
 95 
 96   <xsl:template match="ss:Styles/ss:Style">
 97     <xsl:apply-templates select="." mode="check">
 98       <xsl:with-param name="return" select="'node'"/>
 99     </xsl:apply-templates>
100   </xsl:template>
101 
102   <xsl:template match="ss:Styles/ss:Style" mode="check">
103     <xsl:param name="return"/>
104     <xsl:variable name="styleAttribs">
105       <xsl:apply-templates select="." mode="attribs"><xsl:with-param name="checking" select="true()"/></xsl:apply-templates>
106     </xsl:variable>
107     <xsl:if test="boolean(string($styleAttribs))">
108       <xsl:choose>
109         <xsl:when test="$return = 'id'"><xsl:apply-templates select="." mode="id"/></xsl:when>
110         <xsl:when test="$return = 'node'"><xsl:apply-templates select="." mode="node"/></xsl:when>
111       </xsl:choose>
112     </xsl:if>
113   </xsl:template>
114 
115   <xsl:template match="ss:Styles/ss:Style" mode="id">
116     <xsl:attribute name="style"><xsl:value-of select="@ss:ID"/></xsl:attribute>        
117   </xsl:template>
118 
119   <xsl:template match="ss:Styles/ss:Style" mode="node">
120     <style>
121       <xsl:if test="@ss:ID != 'Default'">
122         <xsl:attribute name="id"><xsl:value-of select="@ss:ID"/></xsl:attribute>
123       </xsl:if>
124       <xsl:apply-templates select="." mode="attribs"/>
125     </style>
126   </xsl:template>
127 
128   <xsl:template match="ss:Styles/ss:Style" mode="attribs">
129     <xsl:param name="checking" select="false()"/>
130     <xsl:apply-templates select="ss:Font"><xsl:with-param name="checking" select="$checking"/></xsl:apply-templates>
131     <xsl:apply-templates select="ss:Interior"><xsl:with-param name="checking" select="$checking"/></xsl:apply-templates>
132     <xsl:apply-templates select="ss:Alignment"><xsl:with-param name="checking" select="$checking"/></xsl:apply-templates>
133     <xsl:apply-templates select="ss:NumberFormat"><xsl:with-param name="checking" select="$checking"/></xsl:apply-templates>
134   </xsl:template>
135 
136   <xsl:template match="ss:Styles/ss:Style/ss:Font">
137     <xsl:param name="checking" select="false()"/>
138     <xsl:if test="@ss:FontName">
139       <xsl:choose>
140         <xsl:when test="$checking">1</xsl:when>
141         <xsl:otherwise><xsl:attribute name="font"><xsl:value-of select="@ss:FontName"/></xsl:attribute></xsl:otherwise>
142       </xsl:choose>
143     </xsl:if>
144     <xsl:if test="@ss:Size">
145       <xsl:choose>
146         <xsl:when test="$checking">1</xsl:when>
147         <xsl:otherwise><xsl:attribute name="size"><xsl:value-of select="@ss:Size"/></xsl:attribute></xsl:otherwise>
148       </xsl:choose>
149     </xsl:if>
150     <xsl:if test="@ss:Bold = 1">
151       <xsl:choose>
152         <xsl:when test="$checking">1</xsl:when>
153         <xsl:otherwise><xsl:attribute name="bold">1</xsl:attribute></xsl:otherwise>
154       </xsl:choose>
155     </xsl:if>
156     <xsl:if test="@ss:Italic = 1">
157       <xsl:choose>
158         <xsl:when test="$checking">1</xsl:when>
159         <xsl:otherwise><xsl:attribute name="italic">1</xsl:attribute></xsl:otherwise>
160       </xsl:choose>
161     </xsl:if>
162     <!-- Ignoring different types of underlining. -->
163     <xsl:if test="@ss:Underline"> 
164       <xsl:choose>
165         <xsl:when test="$checking">1</xsl:when>
166         <xsl:otherwise><xsl:attribute name="underline">1</xsl:attribute></xsl:otherwise>
167       </xsl:choose>
168     </xsl:if>
169     <xsl:if test="@ss:Color">
170       <xsl:choose>
171         <xsl:when test="$checking">1</xsl:when>
172         <xsl:otherwise><xsl:attribute name="color"><xsl:value-of select="@ss:Color"/></xsl:attribute></xsl:otherwise>
173       </xsl:choose>
174     </xsl:if>
175   </xsl:template>
176 
177   <xsl:template match="ss:Styles/ss:Style/ss:Interior">
178     <xsl:param name="checking" select="false()"/>
179     <!-- Ignoring white, and patterns and pattern colors. -->
180     <xsl:if test="@ss:Color and (@ss:Color != '#FFFFFF')"> 
181       <xsl:choose>
182         <xsl:when test="$checking">1</xsl:when>
183         <xsl:otherwise><xsl:attribute name="background"><xsl:value-of select="@ss:Color"/></xsl:attribute></xsl:otherwise>
184       </xsl:choose>
185     </xsl:if>
186   </xsl:template>
187 
188   <xsl:template match="ss:Styles/ss:Style/ss:Alignment">
189     <xsl:param name="checking" select="false()"/>
190     <xsl:if test="@ss:Horizontal">
191       <xsl:choose>
192         <xsl:when test="$checking">1</xsl:when>
193         <xsl:otherwise><xsl:attribute name="align"><xsl:value-of select="@ss:Horizontal"/></xsl:attribute></xsl:otherwise>
194       </xsl:choose>
195     </xsl:if>
196     <xsl:if test="@ss:Vertical">
197       <xsl:choose>
198         <xsl:when test="$checking">1</xsl:when>
199         <xsl:otherwise><xsl:attribute name="valign"><xsl:value-of select="@ss:Vertical"/></xsl:attribute></xsl:otherwise>
200       </xsl:choose>
201     </xsl:if>
202     <xsl:if test="@ss:WrapText = 1">
203       <xsl:choose>
204         <xsl:when test="$checking">1</xsl:when>
205         <xsl:otherwise><xsl:attribute name="wrap">1</xsl:attribute></xsl:otherwise>
206       </xsl:choose>
207     </xsl:if>
208   </xsl:template>
209   
210   <xsl:template match="ss:Styles/ss:Style/ss:NumberFormat">
211     <xsl:param name="checking" select="false()"/>
212     <xsl:if test="@ss:Format">
213       <xsl:choose>
214         <xsl:when test="$checking">1</xsl:when>
215         <xsl:otherwise><xsl:attribute name="format"><xsl:value-of select="@ss:Format"/></xsl:attribute></xsl:otherwise>
216       </xsl:choose>
217     </xsl:if>
218   </xsl:template>
219   
220 </xsl:stylesheet>

There are a few things worth noting about this stylesheet:

  • It ignores cells that have no data.
  • The style handling templates turned out to be more complicated than expected, as they go to a lot of trouble to avoid defining style elements that don't contain values important to my simple XML.
  • Unlike the stylesheet in my first post, this one avoids coding (for-loops, etc.), opting instead for a more functional template-matching style. I think it makes it clearer.

The following stylesheet (available here without line numbers) will convert my simple spreadsheet XML to SpreadsheetML:

  1 <?xml version="1.0"?>
  2 <!--
  3 This product may incorporate intellectual property owned by Microsoft Corporation. The terms 
  4 and conditions upon which Microsoft is licensing such intellectual property may be found at 
  5 http://msdn.microsoft.com/library/en-us/odcXMLRef/html/odcXMLRefLegalNotice.asp.
  6 -->
  7 <xsl:stylesheet version="1.0"
  8                 xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
  9                 xmlns="urn:schemas-microsoft-com:office:spreadsheet"
 10                 xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
 11 
 12   <xsl:output method="xml" indent="yes" encoding="UTF-8"/>
 13 
 14   <xsl:template match="/">
 15     <xsl:processing-instruction name="mso-application">
 16       <xsl:text>progid="Excel.Sheet"</xsl:text>
 17     </xsl:processing-instruction>
 18     <xsl:apply-templates select="/book[1]"/>
 19   </xsl:template>
 20 
 21   <xsl:template match="/book">
 22     <Workbook>
 23     
 24       <xsl:if test="style">
 25         <Styles>
 26           <xsl:apply-templates select="style[not(@id)][1]"/>
 27           <xsl:apply-templates select="style[@id]">
 28             <xsl:sort select="@id" data-type="text"/>
 29           </xsl:apply-templates>
 30         </Styles>
 31       </xsl:if>
 32 
 33       <xsl:if test="name">
 34         <Names>
 35           <xsl:apply-templates select="name">
 36             <xsl:sort select="@id" data-type="text"/>
 37           </xsl:apply-templates>
 38         </Names>
 39       </xsl:if>
 40 
 41       <xsl:apply-templates select="sheet"/>
 42 
 43     </Workbook>
 44   </xsl:template>
 45   
 46   <!-- Styles -->
 47   
 48   <xsl:template match="book/style">
 49     <Style>      
 50       <xsl:attribute name="ss:ID">
 51         <xsl:choose>
 52           <xsl:when test="not(@id)"><xsl:value-of select="'Default'"/></xsl:when>
 53           <xsl:otherwise><xsl:value-of select="@id"/></xsl:otherwise>
 54         </xsl:choose>
 55       </xsl:attribute>
 56       <xsl:if test="not(@id)">
 57         <xsl:attribute name="ss:Name" namespace="urn:schemas-microsoft-com:office:spreadsheet">
<xsl:value-of select="'Normal'"/></xsl:attribute>      
 58       </xsl:if>
 59       <xsl:apply-templates select="." mode="font"/>
 60       <xsl:apply-templates select="." mode="interior"/>
 61       <xsl:apply-templates select="." mode="alignment"/>
 62       <xsl:apply-templates select="." mode="numberformat"/>
 63     </Style>
 64   </xsl:template>
 65   
 66   <xsl:template match="book/style" mode="font">
 67     <xsl:if test="@font | @size | @bold | @italic | @underline | @color">
 68       <Font>
 69         <xsl:if test="@font">
 70           <xsl:attribute name="ss:FontName"><xsl:value-of select="@font"/></xsl:attribute>
 71         </xsl:if>
 72         <xsl:if test="@size">
 73           <xsl:attribute name="ss:Size"><xsl:value-of select="@size"/></xsl:attribute>
 74         </xsl:if>
 75         <xsl:if test="@bold = 1">
 76           <xsl:attribute name="ss:Bold">1</xsl:attribute>
 77         </xsl:if>
 78         <xsl:if test="@italic = 1">
 79           <xsl:attribute name="ss:Italic">1</xsl:attribute>
 80         </xsl:if>
 81         <xsl:if test="@underline = 1">
 82           <xsl:attribute name="ss:Underline">Single</xsl:attribute>
 83         </xsl:if>
 84         <xsl:if test="@color">
 85           <xsl:attribute name="ss:Color"><xsl:value-of select="@color"/></xsl:attribute>
 86         </xsl:if>
 87       </Font>
 88     </xsl:if>
 89   </xsl:template>
 90   
 91   <xsl:template match="book/style" mode="interior">
 92     <xsl:if test="@background">
 93       <Interior>
 94         <xsl:attribute name="ss:Color"><xsl:value-of select="@background"/></xsl:attribute>
 95         <xsl:attribute name="ss:Pattern">Solid</xsl:attribute>
 96       </Interior>
 97     </xsl:if>
 98   </xsl:template>
 99       
100   <xsl:template match="book/style" mode="alignment">
101     <xsl:if test="@align | @valign | @wrap">
102       <Alignment>
103         <xsl:if test="@align">
104           <xsl:attribute name="ss:Horizontal"><xsl:value-of select="@align"/></xsl:attribute>
105         </xsl:if>
106         <xsl:if test="@valign">
107           <xsl:attribute name="ss:Vertical"><xsl:value-of select="@valign"/></xsl:attribute>
108         </xsl:if>
109         <xsl:if test="@wrap = 1">
110           <xsl:attribute name="ss:WrapText">1</xsl:attribute>
111         </xsl:if>
112       </Alignment>
113     </xsl:if>
114   </xsl:template>
115 
116   <xsl:template match="book/style" mode="numberformat">
117     <xsl:if test="@format">
118       <NumberFormat>
119         <xsl:attribute name="ss:Format"><xsl:value-of select="@format"/></xsl:attribute>
120       </NumberFormat>
121     </xsl:if>
122   </xsl:template>
123   
124   <!-- NamedRanges -->
125   
126   <xsl:template match="name">
127     <NamedRange>
128       <xsl:attribute name="ss:Name"><xsl:value-of select="@id"/></xsl:attribute>
129       <xsl:attribute name="ss:RefersTo"><xsl:value-of select="@formula"/></xsl:attribute>
130     </NamedRange>
131   </xsl:template>
132   
133   <!-- Worksheets -->
134   
135   <xsl:template match="sheet">
136     <Worksheet>
137       <xsl:attribute name="ss:Name"><xsl:value-of select="@id"/></xsl:attribute>      
138       <xsl:if test="name">
139         <Names>
140           <xsl:apply-templates select="name">
141             <xsl:sort select="@id" data-type="text"/>
142           </xsl:apply-templates>
143         </Names>
144       </xsl:if>
145       <Table>
146         <xsl:apply-templates select="cell" mode="rows">
147           <xsl:sort select="@row" data-type="number"/>
148         </xsl:apply-templates>
149       </Table>
150     </Worksheet>
151   </xsl:template>
152   
153   <xsl:template match="cell" mode="rows">
154     <xsl:if test="count(preceding-sibling::cell[@row = current()/@row]) = 0">
155       <Row>
156         <xsl:attribute name="ss:Index"><xsl:value-of select="@row"/></xsl:attribute>
157         <xsl:apply-templates select="/book/sheet[@id = current()/parent::sheet/@id]/cell[@row = current()/@row]" mode="cols">
158           <xsl:sort select="@col" data-type="number"/>
159         </xsl:apply-templates>
160       </Row>
161     </xsl:if>
162   </xsl:template>
163   
164   <xsl:template match="cell" mode="cols">
165     <Cell>
166       <xsl:attribute name="ss:Index"><xsl:value-of select="@col"/></xsl:attribute>      
167       <xsl:if test="@formula">
168         <xsl:attribute name="ss:Formula"><xsl:value-of select="@formula"/></xsl:attribute>
169       </xsl:if>      
170       <xsl:if test="@style">
171         <xsl:attribute name="ss:StyleID"><xsl:value-of select="@style"/></xsl:attribute>
172       </xsl:if>
173       <Data>
174         <xsl:if test="@type">
175           <xsl:attribute name="ss:Type"><xsl:value-of select="@type"/></xsl:attribute>
176         </xsl:if>
177         <xsl:value-of select="."/>      
178       </Data>    
179     </Cell>
180   </xsl:template>
181 
182 </xsl:stylesheet>
183 

These are free for the taking. However, if you discover something wrong with them or add anything useful to them, I would really like for you to come back and share it with us. You don't have to, of course, but I'll love you that much more if you do. ;)

Also, as is always the case here, these aren't expected to be perfect. There are no warranties. Use them at your own risk.

In case you're wondering what the XML looks like, here's an example. Let's start with the SpreadsheetML:

<?xml version="1.0" ?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:o="urn:schemas-microsoft-com:office:office"
  xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
  xmlns:html="http://www.w3.org/TR/REC-html40">
  <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
    <LastAuthor>Eric W. Bachtal</LastAuthor>
    <LastSaved>2006-01-05T13:57:52Z</LastSaved>
    <Version>11.6360</Version>
  </DocumentProperties>
  <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
    <WindowHeight>10005</WindowHeight>
    <WindowWidth>10005</WindowWidth>
    <WindowTopX>120</WindowTopX>
    <WindowTopY>135</WindowTopY>
    <ProtectStructure>False</ProtectStructure>
    <ProtectWindows>False</ProtectWindows>
  </ExcelWorkbook>
  <Styles>
    <Style ss:ID="Default" ss:Name="Normal">
      <Font ss:FontName="Verdana" ss:Size="8" />
    </Style>
    <Style ss:ID="s35" ss:Name="MyStyle">
      <Alignment ss:Vertical="Bottom" />
      <Borders />
      <Font ss:FontName="Times New Roman" x:Family="Roman" ss:Bold="1" />
      <Interior />
      <NumberFormat />
      <Protection />
    </Style>
    <Style ss:ID="s21">
      <Font ss:FontName="Tahoma" ss:Bold="1" />
      <NumberFormat ss:Format="&quot;$&quot;#,##0.00" />
    </Style>
    <Style ss:ID="s22">
      <Font ss:FontName="Verdana" x:Family="Swiss" ss:Size="12" ss:Color="#FFFFFF" ss:Bold="1" />
      <Interior ss:Color="#3366FF" ss:Pattern="Solid" />
    </Style>
    <Style ss:ID="s23">
      <Font ss:FontName="Verdana" x:Family="Swiss" ss:Size="12" ss:Color="#FFFFFF" ss:Bold="1" />
      <Interior ss:Color="#3366FF" ss:Pattern="Solid" />
      <NumberFormat ss:Format="Short Date" />
    </Style>
    <Style ss:ID="s24">
      <Font ss:FontName="Verdana" x:Family="Swiss" ss:Size="12" ss:Color="#FFFFFF" ss:Bold="1" />
      <Interior ss:Color="#3366FF" ss:Pattern="Solid" />
      <NumberFormat ss:Format="General Date" />
    </Style>
    <Style ss:ID="s26">
      <Alignment ss:Horizontal="Center" ss:Vertical="Bottom" />
    </Style>
    <Style ss:ID="s27">
      <Font ss:FontName="Verdana" x:Family="Swiss" ss:Size="8" ss:Bold="1" ss:Italic="1" ss:Underline="Single" />
    </Style>
    <Style ss:ID="s28">
      <Font ss:FontName="Verdana" ss:Size="8" ss:Underline="Single" />
    </Style>
    <Style ss:ID="s29">
      <Font ss:FontName="Verdana" ss:Bold="1" />
    </Style>
    <Style ss:ID="s31">
      <Font ss:FontName="Verdana" x:Family="Swiss" ss:Size="8" ss:Bold="1" />
      <Interior ss:Color="#FFFF00" ss:Pattern="Solid" />
    </Style>
  </Styles>
  <Names>
    <NamedRange ss:Name="mycol" ss:RefersTo="=COLUMN()" />
    <NamedRange ss:Name="myrow" ss:RefersTo="=ROW()" />
  </Names>
  <Worksheet ss:Name="Sheet1">
    <Names>
      <NamedRange ss:Name="mycolplusrow" ss:RefersTo="=Sheet1!R9C7+Sheet1!R10C7" />
    </Names>
    <Table ss:ExpandedColumnCount="7" ss:ExpandedRowCount="12" x:FullColumns="1" x:FullRows="1"
      ss:DefaultRowHeight="10.5">
      <Column ss:AutoFitWidth="0" ss:Width="62.25" />
      <Column ss:Width="73.5" />
      <Column ss:Width="116.25" />
      <Column ss:Width="48.75" ss:Span="1" />
      <Row ss:AutoFitHeight="0" ss:Height="12.75">
        <Cell>
          <Data ss:Type="Number">1</Data>
        </Cell>
        <Cell ss:Formula="=R1C1+10">
          <Data ss:Type="Number">11</Data>
        </Cell>
        <Cell ss:Formula="=R1C1+20">
          <Data ss:Type="Number">21</Data>
        </Cell>
        <Cell ss:StyleID="s21" ss:Formula="=SUM(R1C1:R1C3)">
          <Data ss:Type="Number">33</Data>
        </Cell>
      </Row>
      <Row ss:AutoFitHeight="0">
        <Cell>
          <Data ss:Type="Boolean">1</Data>
        </Cell>
        <Cell ss:Index="6" ss:StyleID="s31" />
        <Cell ss:StyleID="s31" />
      </Row>
      <Row ss:AutoFitHeight="0" ss:Height="15">
        <Cell ss:StyleID="s22">
          <Data ss:Type="Number">38353.579340277778</Data>
        </Cell>
        <Cell ss:StyleID="s23">
          <Data ss:Type="DateTime">2005-01-02T13:54:15.000</Data>
        </Cell>
        <Cell ss:StyleID="s24">
          <Data ss:Type="DateTime">2005-01-03T13:54:15.000</Data>
        </Cell>
      </Row>
      <Row ss:AutoFitHeight="0">
        <Cell>
          <Data ss:Type="String">abc</Data>
        </Cell>
      </Row>
      <Row ss:AutoFitHeight="0" ss:Span="2" />
      <Row ss:Index="8" ss:AutoFitHeight="0">
        <Cell ss:Index="2" ss:MergeAcross="1" ss:MergeDown="2" ss:StyleID="s26" />
      </Row>
      <Row ss:AutoFitHeight="0">
        <Cell>
          <Data ss:Type="String">pre-merge</Data>
        </Cell>
        <Cell ss:Index="4">
          <Data ss:Type="String">post-merge</Data>
        </Cell>
        <Cell ss:Index="7" ss:StyleID="s27" ss:Formula="=myrow">
          <Data ss:Type="Number">9</Data>
          <NamedCell ss:Name="mycolplusrow" />
        </Cell>
      </Row>
      <Row ss:AutoFitHeight="0">
        <Cell ss:Index="5">
          <Data ss:Type="Error">#N/A</Data>
        </Cell>
        <Cell ss:Index="7" ss:StyleID="s27" ss:Formula="=mycol">
          <Data ss:Type="Number">7</Data>
          <NamedCell ss:Name="mycolplusrow" />
        </Cell>
      </Row>
      <Row ss:AutoFitHeight="0" />
      <Row ss:AutoFitHeight="0">
        <Cell ss:Index="2" ss:StyleID="s35" />
        <Cell ss:Index="7" ss:StyleID="s28" ss:Formula="=mycolplusrow">
          <Data ss:Type="Number">16</Data>
        </Cell>
      </Row>
    </Table>
    <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
      <Unsynced />
      <Print>
        <ValidPrinterInfo />
        <HorizontalResolution>600</HorizontalResolution>
        <VerticalResolution>600</VerticalResolution>
      </Print>
      <Selected />
      <ProtectObjects>False</ProtectObjects>
      <ProtectScenarios>False</ProtectScenarios>
    </WorksheetOptions>
  </Worksheet>
  <Worksheet ss:Name="Sheet2">
    <Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1" x:FullColumns="1" x:FullRows="1"
      ss:DefaultRowHeight="10.5">
      <Row ss:AutoFitHeight="0" ss:Height="12.75">
        <Cell ss:StyleID="s29" ss:Formula="=Sheet1!R1C1 &amp; &quot; plus text&quot;">
          <Data ss:Type="String">1 plus text</Data>
        </Cell>
      </Row>
    </Table>
    <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
      <Unsynced />
      <ProtectObjects>False</ProtectObjects>
      <ProtectScenarios>False</ProtectScenarios>
    </WorksheetOptions>
  </Worksheet>
</Workbook>

The first stylesheet above converts this into the following simple spreadsheet XML:

<?xml version="1.0" ?>
<book>
  <style font="Verdana" size="8"></style>
  <style id="s35" font="Times New Roman" bold="1" valign="Bottom"></style>
  <style id="s21" font="Tahoma" bold="1" format="&quot;$&quot;#,##0.00"></style>
  <style id="s22" font="Verdana" size="12" bold="1" color="#FFFFFF" background="#3366FF"></style>
  <style id="s23" font="Verdana" size="12" bold="1" color="#FFFFFF" background="#3366FF" format="Short Date"></style>
  <style id="s24" font="Verdana" size="12" bold="1" color="#FFFFFF" background="#3366FF" format="General Date"></style>
  <style id="s26" align="Center" valign="Bottom"></style>
  <style id="s27" font="Verdana" size="8" bold="1" italic="1" underline="1"></style>
  <style id="s28" font="Verdana" size="8" underline="1"></style>
  <style id="s29" font="Verdana" bold="1"></style>
  <style id="s31" font="Verdana" size="8" bold="1" background="#FFFF00"></style>
  <name id="mycol" formula="=COLUMN()" />
  <name id="myrow" formula="=ROW()" />
  <sheet id="Sheet1">
    <name id="mycolplusrow" formula="=Sheet1!R9C7+Sheet1!R10C7" />
    <cell row="1" col="1" type="Number">1</cell>
    <cell row="1" col="2" type="Number" formula="=R1C1+10">11</cell>
    <cell row="1" col="3" type="Number" formula="=R1C1+20">21</cell>
    <cell row="1" col="4" type="Number" style="s21" formula="=SUM(R1C1:R1C3)">33</cell>
    <cell row="2" col="1" type="Boolean">1</cell>
    <cell row="3" col="1" type="Number" style="s22">38353.579340277778</cell>
    <cell row="3" col="2" type="DateTime" style="s23">2005-01-02T13:54:15.000</cell>
    <cell row="3" col="3" type="DateTime" style="s24">2005-01-03T13:54:15.000</cell>
    <cell row="4" col="1" type="String">abc</cell>
    <cell row="9" col="1" type="String">pre-merge</cell>
    <cell row="9" col="4" type="String">post-merge</cell>
    <cell row="9" col="7" type="Number" style="s27" formula="=myrow">9</cell>
    <cell row="10" col="5" type="Error">#N/A</cell>
    <cell row="10" col="7" type="Number" style="s27" formula="=mycol">7</cell>
    <cell row="12" col="7" type="Number" style="s28" formula="=mycolplusrow">16</cell>
  </sheet>
  <sheet id="Sheet2">
    <cell row="1" col="1" type="String" style="s29" formula="=Sheet1!R1C1 &amp; &quot; plus text&quot;">1 plus text</cell>
  </sheet>
</book>

And the second stylesheet above converts this back into the following SpreadsheetML:

<?xml version="1.0" ?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
  <Styles>
    <Style ss:ID="Default" ss:Name="Normal">
      <Font ss:FontName="Verdana" ss:Size="8" />
    </Style>
    <Style ss:ID="s21">
      <Font ss:FontName="Tahoma" ss:Bold="1" />
      <NumberFormat ss:Format="&quot;$&quot;#,##0.00" />
    </Style>
    <Style ss:ID="s22">
      <Font ss:FontName="Verdana" ss:Size="12" ss:Bold="1" ss:Color="#FFFFFF" />
      <Interior ss:Color="#3366FF" ss:Pattern="Solid" />
    </Style>
    <Style ss:ID="s23">
      <Font ss:FontName="Verdana" ss:Size="12" ss:Bold="1" ss:Color="#FFFFFF" />
      <Interior ss:Color="#3366FF" ss:Pattern="Solid" />
      <NumberFormat ss:Format="Short Date" />
    </Style>
    <Style ss:ID="s24">
      <Font ss:FontName="Verdana" ss:Size="12" ss:Bold="1" ss:Color="#FFFFFF" />
      <Interior ss:Color="#3366FF" ss:Pattern="Solid" />
      <NumberFormat ss:Format="General Date" />
    </Style>
    <Style ss:ID="s26">
      <Alignment ss:Horizontal="Center" ss:Vertical="Bottom" />
    </Style>
    <Style ss:ID="s27">
      <Font ss:FontName="Verdana" ss:Size="8" ss:Bold="1" ss:Italic="1" ss:Underline="Single" />
    </Style>
    <Style ss:ID="s28">
      <Font ss:FontName="Verdana" ss:Size="8" ss:Underline="Single" />
    </Style>
    <Style ss:ID="s29">
      <Font ss:FontName="Verdana" ss:Bold="1" />
    </Style>
    <Style ss:ID="s31">
      <Font ss:FontName="Verdana" ss:Size="8" ss:Bold="1" />
      <Interior ss:Color="#FFFF00" ss:Pattern="Solid" />
    </Style>
    <Style ss:ID="s35">
      <Font ss:FontName="Times New Roman" ss:Bold="1" />
      <Alignment ss:Vertical="Bottom" />
    </Style>
  </Styles>
  <Names>
    <NamedRange ss:Name="mycol" ss:RefersTo="=COLUMN()" />
    <NamedRange ss:Name="myrow" ss:RefersTo="=ROW()" />
  </Names>
  <Worksheet ss:Name="Sheet1">
    <Names>
      <NamedRange ss:Name="mycolplusrow" ss:RefersTo="=Sheet1!R9C7+Sheet1!R10C7" />
    </Names>
    <Table>
      <Row ss:Index="1">
        <Cell ss:Index="1">
          <Data ss:Type="Number">1</Data>
        </Cell>
        <Cell ss:Index="2" ss:Formula="=R1C1+10">
          <Data ss:Type="Number">11</Data>
        </Cell>
        <Cell ss:Index="3" ss:Formula="=R1C1+20">
          <Data ss:Type="Number">21</Data>
        </Cell>
        <Cell ss:Index="4" ss:Formula="=SUM(R1C1:R1C3)" ss:StyleID="s21">
          <Data ss:Type="Number">33</Data>
        </Cell>
      </Row>
      <Row ss:Index="2">
        <Cell ss:Index="1">
          <Data ss:Type="Boolean">1</Data>
        </Cell>
      </Row>
      <Row ss:Index="3">
        <Cell ss:Index="1" ss:StyleID="s22">
          <Data ss:Type="Number">38353.579340277778</Data>
        </Cell>
        <Cell ss:Index="2" ss:StyleID="s23">
          <Data ss:Type="DateTime">2005-01-02T13:54:15.000</Data>
        </Cell>
        <Cell ss:Index="3" ss:StyleID="s24">
          <Data ss:Type="DateTime">2005-01-03T13:54:15.000</Data>
        </Cell>
      </Row>
      <Row ss:Index="4">
        <Cell ss:Index="1">
          <Data ss:Type="String">abc</Data>
        </Cell>
      </Row>
      <Row ss:Index="9">
        <Cell ss:Index="1">
          <Data ss:Type="String">pre-merge</Data>
        </Cell>
        <Cell ss:Index="4">
          <Data ss:Type="String">post-merge</Data>
        </Cell>
        <Cell ss:Index="7" ss:Formula="=myrow" ss:StyleID="s27">
          <Data ss:Type="Number">9</Data>
        </Cell>
      </Row>
      <Row ss:Index="10">
        <Cell ss:Index="5">
          <Data ss:Type="Error">#N/A</Data>
        </Cell>
        <Cell ss:Index="7" ss:Formula="=mycol" ss:StyleID="s27">
          <Data ss:Type="Number">7</Data>
        </Cell>
      </Row>
      <Row ss:Index="12">
        <Cell ss:Index="7" ss:Formula="=mycolplusrow" ss:StyleID="s28">
          <Data ss:Type="Number">16</Data>
        </Cell>
      </Row>
    </Table>
  </Worksheet>
  <Worksheet ss:Name="Sheet2">
    <Table>
      <Row ss:Index="1">
        <Cell ss:Index="1" ss:Formula="=Sheet1!R1C1 &amp; &quot; plus text&quot;" ss:StyleID="s29">
          <Data ss:Type="String">1 plus text</Data>
        </Cell>
      </Row>
    </Table>
  </Worksheet>
</Workbook>

Enjoy.


Comments

This is the longest post in the world!!!!!!!!!!

I'll read it all latter!!!!! looks good though!!!!

my what a long post!!!!!!!!!!!!

p.s this craptcha thing sucks asssss

ross | 2006.01.05 08:49 AM

Ross,

Thanks for the enthusiasm! And I agree, the craptcha sucks enormously.

ewbi.develops | 2006.01.05 02:47 PM

Hi Eric, I'm back again, and wow! **Well done** with your amazing stylesheets here!

I haven't yet got around to using these at my work, but I'm very keen to at least try them at home here. Should be great fun!

Thanks *very much* for your awesome work here! Bye for now, and have a great 2006!
- Andy


Andy Elvey | 2006.01.11 11:12 PM

Hi Andy,

Thanks for the feedback. I hope you have a great 2006 as well!

ewbi.develops | 2006.01.12 12:16 AM

Hi again Eric!

I tried your simpleXML-to-ExcelXML (and vice-versa). Oh, BTW, I used the Saxon parser to do this - SaxonB-8.6.1, to be exact. Oh, and I used exactly your documents above to do this (to make things simpler and to be on the safe side ... :-) )

The simpleXML->ExcelXML transform went perfectly! That resulted in a nice output file in ExcelXML format.

However, I came across a problem in the reverse transformation. Going from ExcelXML to
your SimpleXML, Saxon gave the following error

andy@2[saxonb8-6-1]$ java -jar saxon8.jar Excel_spreadsheetML.xml Excel_to_simple_spreadsheet_xml.xsl
Warning: Running an XSLT 1.0 stylesheet with an XSLT 2.0 processor
Error at attribute constructor font="{...}" on line 142 of file:/home/andy/saxonb8-6-1/Excel_to_simple_spreadsheet_xml.xsl:
XTDE0420: Cannot create an attribute node (font) whose parent is a document node
Transformation failed: Run-time errors were reported
andy@2[saxonb8-6-1]$

Hmmmm .... a bit of a puzzle ....

So was wondering - is it possible for you to just have a quick look at this, to see what is happening? My gut feeling is that it seems like a not-too-difficult problem. Then again, I may be wrong ;-) Oh, and don't spend too long - I'm really just "poking around" at present. If this transform is used at my work, it'll be a fair way in the future (a number of months away, if at all).

If you like, I can email you the exact documents I used (they were just cut-and-pasted from this page).

**Very many thanks** again for your really-cool XML stuff - you do an awesome blog!
Bye for now -
- Andy


Andy Elvey | 2006.01.16 10:29 PM

Hi Andy,

Thanks for checking this and reporting your findings. I think that's an "oops". It appears (though I've not confirmed it by installing and checking Saxon directly) that the trick I'm using to pre-check that source Style elements contain values I'm interested in is causing a problem for Saxon, though not for MSXML. Specifically, the XSL spec says that:

"All attributes must be applied before children are added to the element."

Unfortunately, when $checking is true, I'm adding a text node before adding the attribute. So, until I can get this updated in the post, anywhere you see me adding a text node of "1" to an element before adding an attribute to the same element, just switch it around the other way - attribute, then text node.

Thanks again for taking the time to check this!

ewbi.develops | 2006.01.17 12:07 AM

Hi Eric -

Sure, no problem - I was happy to give this a test-drive! :-) ....

Thanks very much for your fix, too. Very well explained, and I can understand why the spec says that. If it didn't, a program could create (say) nodes 1 to 10, then it might jump back and add a few attibutes to node 7, then create a few more nodes etc .....

So, the standard seems to be saying that you've got to be "finished" with a node (and its attributes) before child nodes can be added to it.
Interesting indeed, so I've learned something today ;-)

Bye for now -
- Andy

Andy Elvey | 2006.01.17 09:28 PM

Hi Eric -

I have some very good news. I've just used Saxon to test your revised staylesheet (the one which transforms SpreadsheetML into simple XML), and it works perfectly! So, the "round-trip" is complete :-).

Many thanks for your work on this - it's been a **really** interesting learning experience for me! Bye for now -

- Andy


Andy Elvey | 2006.02.11 02:24 PM

Yeah! Thanks, Andy.

ewbi.develops | 2006.02.13 11:33 AM


i found a bug

my oroginal spreedsheat have :

[Row]
[Cell][Data ss:Type="Number"]85009[/Data][/Cell]
[Cell][Data ss:Type="String"]My Dog[/Data][/Cell]
[Cell][Data ss:Type="String"]j[/Data][/Cell]
[Cell][Data ss:Type="String"]frfr,ncnc,refr[/Data][/Cell]
[Cell ss:Index="8"][Data ss:Type="String"]I PLAY[/Data][/Cell]
[Cell][Data ss:Type="String"]PREMIUM[/Data][/Cell]
[Cell ss:Index="12"][Data ss:Type="String"]Nourris-le[/Data][/Cell]
[Cell ss:Index="15"][Data ss:Type="String"]C[/Data][/Cell]
[Cell][Data ss:Type="String"]aud[/Data][/Cell]
[Cell][Data ss:Type="Number"]0[/Data][/Cell]
[Cell][Data ss:Type="String"]_reflexion[/Data][/Cell]
[/Row]


when translated i got :


[cell row="3" col="1" type="Number"]85009[/cell]
[cell row="3" col="2" type="String"]My Dog[/cell]
[cell row="3" col="3" type="String"]j[/cell]
[cell row="3" col="4" type="String"]frfr,ncnc,refr[/cell]
[cell row="3" col="8" type="String"]I PLAY[/cell]
[cell row="3" col="9" type="String"]PREMIUM[/cell]
[cell row="3" col="12" type="String"]Nourris-le[/cell]
[cell row="3" col="15" type="String"]C[/cell]
[cell row="3" col="12" type="String"]aud[/cell]
[cell row="3" col="13" type="Number"]0[/cell]
[cell row="3" col="14" type="String"]_reflexion[/cell]

2 values for row 3 col 12 :'(

marc | 2006.11.29 07:34 AM

Hi marc,

Thanks for taking the time to report this. What XML parser /XSL engine (name, version) did you use to produce this result?

ewbi.develops | 2006.11.29 08:22 AM


Hi,

$xml = new DOMDocument;
$xml->load($file_name);

$xsl = new DOMDocument;
$xsl->load('convert.xsl');

$proc = new XSLTProcessor;
$proc->importStyleSheet($xsl);

$result = $proc->transformToXML($xml)

i'm using : php 5.1.2, php5-xsl, libxslt1.1


in my previous post i replaced all angle brackets with square brackets;

Regards.

marc | 2006.11.30 01:03 AM

Thanks, marc. I'm not a php developer, so please bear with me. I really would like to find out where this is going wrong, as compared to MSXML, .NET, and Saxon, which get it right. Are you using version 5.2.0-7 of php5-xsl?

ewbi.develops | 2006.11.30 04:10 AM

Also, I can't find a php 5.1.2 download anywhere, only the newer 5.2.0, which does include some XML reader/writer changes. Have you tried reproducing the problem with 5.2.0? Can you point me to a 5.1.2 download?

ewbi.develops | 2006.11.30 04:22 AM

there : http://www.php.net/releases.php

marc | 2006.11.30 05:25 AM

marc, I was able to reproduce the issue with both PHP 5.1.2 and 5.2.0. I'll take a look at the XSLT to see what part is giving it fits.

ewbi.develops | 2006.11.30 12:40 PM

Okay, marc, I think I've got it. See the update to the post above and let me know if you have any additional problems.

Good luck.

ewbi.develops | 2006.11.30 02:13 PM

Hi,

Great job, it's fully working now !

Thanks a lot.

marc | 2006.12.01 01:00 AM

How can I extract data for one row as one string?

Thuthuy | 2007.07.16 08:35 PM

Thank you, I just wanted to drop you a note saying that this entry helped me tremendously. Thank you for sharing!

Scott Gartner | 2008.01.30 08:01 PM

Hi Eric, I've been googling the whole day without success trying to find a stylesheet capable of making SpreadsheetMLs out off XSL-FO reports.
Your impressing work is the only piece of XSLT that I could use so far. Have you seen such a StyleSheet around?
Im making a reporting engine, as a symfony 1.4 module, that renders a FO description of the report, suitable for conversion to PDF, HTML, FO, and I got stuck with XLS.
Thanks! even though I didn't worked it out yet, your work is a great start point!
And thanks also for sharing it with us, I have NO xp with XSLT and you are really helping me out!

Juan Manuel Fernandez | 2011.05.05 04:08 PM

Juan, thanks for the kind words. I'm afraid I haven't seen any stylesheets around that do what you describe. In fact, I haven't worked with SpreadsheetML now in a couple of years. Lots of folks switching now to the OpenXML format introduced with Office 2007. Good luck!

ewbi.develops | 2011.05.06 09:59 AM


TrackBack

TrackBack URL:  http://www.typepad.com/services/trackback/6a00d8341c7bd453ef00d8346ff6b753ef

Listed below are links to weblogs that reference Round-tripping Excel's SpreadsheetML: