2

I need to convert a excel spreadsheet saved in xml format using a stylesheet...but I keep getting hung up on the many namespaces ms uses. I need an XSLT stylesheet that allows per element passing (the default seems to pass ALL the text when I use a stylesheet that should otherwise only pass the element templates I am writing) of the input xml.

Here's the input xml:

http://apriority.dyndns.biz/im_cache/u_1/j_2390/2_9/tf/testexcel.xml

:I need to simply extract data from both sheets in columns, like:

Output:

Sheet 1 1) blah, blue , burn, baste, belly, belie, bestow, betrothed, bemoan 2) quack, quagmire, quick, quantum, quant 3) alimony, ashy, amber, absolute, astrology, alabaster, angry 4) cost, curry, candor, cabin, capability, castor, canada Sheet 2 1) 3 2) 32 3) 322 ....etc.

I have trouble with the stylesheet...something to do with the namespaces that MS uses in the input xml ...I seem to be getting in my output ALL of the data even if i only have one element template (say for extracting the rows of only the first sheet)...what stylesheet code will get the output above???

Here is the stylesheet I have so far:

http://apriority.dyndns.biz/im_cache/u_1/j_2390/2_9/tf/learningmap.xsl

File put on non https url so no virus risk plus I am using my real name, google me!

Here is the edited stylesheet after use of suggested code by Mads!

http://apriority.dyndns.biz/im_cache/u_1/j_2390/2_9/tf/learningmap_mod.xsl

I still have a problem where the actual text data is not being extracted in that follow up code, why am I not able to get the text data. I can output the first variable "snid" but all the text variables don't appear in the output even though I am selecting them and the original source xml has stuff in those entries. Any help on this new problem would be greatly appreciated!

Update Feb. 9:

I solved the issue with the mapping failure to the variables. It was a simple xpath mistake I was addressing nodes that didn't exist. There is a one to one mapping between the Cells and their rows so the variables should have extracted as Cell[k].Row[1]...Cell[k+1].Row[1]...etc.

The output transformation occurs as I need it to, thanks for the contributions toward the answer. This one is hard to judge which answer is accepted as both submissions were of aid but I'll have to give it to Mads Hanson this time. Thanks!

Improve this question
7
  • Could you post your existing stylesheets? Commented Feb 6, 2011 at 22:29
  • Everybody, be aware: When attempting to open the provided link IE issues the following: There is a problem with this website's security certificate. The security certificate presented by this website was issued for a different website's address. Security certificate problems may indicate an attempt to fool you or intercept any data you send to the server. We recommend that you close this webpage and do not continue to this website. Commented Feb 6, 2011 at 22:38
  • I have voted to C L O S E this question. It provides a link to a dangerous site, doesn't contain even the most essential information, such as an XML document, the form of the wanted output seems arbitrary general. Commented Feb 6, 2011 at 22:41
  • I'm also voting close because it isn't asking about any specific programming language. Commented Feb 7, 2011 at 0:22
  • whoahh...slow down guys this is a legitimate question...I couldn't get the xml code to embed in the original question so i just linked to it...you are right about my cert. I have to get that fixed. Is it possible to edit the text to add the code embedded?? Commented Feb 7, 2011 at 4:52

3 Answers 3

Reset to default
1
  • Namespaces are inherited from the parents, and the <Worksheet> element uses it without a namespace prefix, so it may not have been apparent that Worksheet, Data, Row, and Cell were all bound to the same namespace and needed ss: for each of the match criteria.
    • You had correctly declared the urn:schemas-microsoft-com:office:spreadsheet namespace, but were not using it to match all of the content.
  • In the template for ss:Worksheet you were not applying templates, so processing was stopping. I added an apply-templates for ss:Table/ss:Row
  • It looked as if you were creating text output
    • I changed the output method="xml" to method="text"
    • I replaced <br/> with &#xA; (a newline)

I have modified your stylesheet to produce the output that you had shown.

<?xml version="1.0"?> <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" > <xsl:output method="text"/> <xsl:template match="ss:Workbook"> <xsl:apply-templates select="ss:Worksheet"/> </xsl:template> <xsl:template match="ss:Worksheet"> <xsl:value-of select="@ss:Name"/> <xsl:text>:&#xA;</xsl:text> <xsl:apply-templates select="ss:Table/ss:Row" /> </xsl:template> <xsl:template match="ss:Row"> <xsl:apply-templates select="ss:Cell"/> </xsl:template> <xsl:template match="ss:Cell"> <xsl:apply-templates select="ss:Data"/> </xsl:template> <xsl:template match="ss:Data"> <xsl:value-of select="count(../preceding-sibling::ss:Cell) + 1"/> <xsl:text>)</xsl:text> <xsl:value-of select="."/> <xsl:text>&#xA;</xsl:text> </xsl:template> </xsl:stylesheet>
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Mads, that looks perfect! Question, can I change the output to xml without worrying about passing other junk...the xml I provided here was a test the actual one has lots of data on the second sheet that I want to only conditionally pass (I can figure those templates out now that you provided this excellent first start)..just want to make sure changing to xml won't change it. (I am not at a computer now where I can test the transform so that is why I am asking.) thanks again!
Yes, you can change the output to XML without too much trouble. However, for it to generate a well-formed XML document you will need to ensure that you have a document element(e.g. an element to wrap all of the content). For example, in this stylesheet you would create an element in the ss:Workbook template that surrounds the call to apply-templates for ss:Worksheet, like this <xsl:template match="ss:Workbook"><someElement><xsl:apply-templates select="ss:Worksheet"/></someElement></xsl:template>
1

XSLT version to produce XML output:

<?xml version="1.0"?> <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"> <xsl:output method="xml"/> <xsl:template match="/"> <Workbook> <xsl:for-each select="ss:Workbook/ss:Worksheet"> <Worksheet> <Name><xsl:value-of select="@ss:Name"/></Name> <xsl:for-each select="ss:Table/ss:Row"> <Row> <Number><xsl:number value="position()"/></Number> <Data><xsl:for-each select="ss:Cell"><xsl:value-of select="ss:Data"/><xsl:if test="not(position()=last())">, </xsl:if></xsl:for-each></Data> </Row> </xsl:for-each> </Worksheet> </xsl:for-each> </Workbook> </xsl:template> </xsl:stylesheet>

The required namespace declaration was copied over from the Excel XML file. Note that elements in the output such as Workbook don't belong to any namespace

Improve this answer

5 Comments

Great answer barrowc but I need an xslt stylesheet that does the transformation...that's what I am using not VBA. I didn't know how to reference those namespaces properly in the xslt to ensure per element handling (without forced extraction of all the subelement contents in the output). Any idea?
@David Saintloth I've added an XSLT version of my answer. Hope that produces the required transformation for you
Looks great barrowc! How would i modify it to make the output xml? I don't see an "output" template near the top. I am actually creating an intermediate xml format from the excel xml so the output needs to be xml.
@David Saintloth updated my answer again but I think that Mads Hansen's solution might be easier to work with in the long term
I updated the question with the final solution and accepted answer (Mads).
0

You'd try this good article Dive into SpreadsheetML (Part 2 of 2)

Anyway, very complex excel can generate very big xml files, so do some tests on real data before doing a full implementation

Improve this answer

1 Comment

It references doing the opposite of what I needed but thanks for the article link Daitangio!

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.