Jump to content


  • Posts

  • Joined

  • Last visited

Profile Information

  • Location

Subwayboy's Achievements


Newbie (1/7)



  1. Hi all, Just a general question really. I have an MS Excel 2007 workbook that is used as input for estimates of cost and prices. The workbook will export the data from the workbook to an xml file for further use by other teams and interested parties. There are several xslt files that are used to manipulate the data from the workbook into different formats for other users, i.e. management just receive high level data, client receives another set of data. Some xslt files produce a HTML summary, some produce xml files for use in other workbooks. Currently the xslt files are held in the same folder as the workbook and accessed when needed through VBA and MSXML to create the relevant files in required formats Can anyone suggest a good method of storage for the external xslt files please? I am trying to avoid the situation where as requests for different types of summary information from the workbook is required and another xslt is needed and users may miss the new xslt file that has been created. Can multiple xslt files be held in one file? Any best practices, thoughts or suggestions? Any pitfalls in storing many xslt stylesheets in one file? Thanks! Graham
  2. Fabulous! I have been struggling with apply-templates for many months now, suddenly it is all clear now thanks to your replies
  3. Many thanks. The effort value comes from the values between each month value in the original xml. So <Month_1>12</Month_1> would need to become <Effort>12</Effort> I have tried the following, <Effort><xsl:value-of select="../*[key('k1', local-name())]" /> </Effort> But do not get all the effort values pulled through to the correct month
  4. Hi all, I have been assigned a task to convert an xml output from an excel workbook into another xml structure. There are several elements in the xml file that need to be converted into one element. I do not have any flexibility in changing the structure of the output from the excel workbook XML File structure <Root><Row><ID>1</ID><Name>John</Name><Task>Task One</Task><Month_1>12</Month_1><Month_2>23</Month_2><Month_3>45</Month_3></Row><Row><ID>1</ID><Name>John</Name><Task>Task Two</Task><Month_2>7.5</Month_2><Month_3>4</Month_3></Row><Months><Month_1>2011-11</Month_1><Month_2>2011-12</Month_2><Month_3>2012-01</Month_3></Months></Root> The file structure of the converted file after applying an xslt should be in the format of <Root><Row ID="1"><Name>John</Name><Task>Task One</Task><Month ID="Month_1">2011-11</Month><Effort>12</Effort></Row><Row ID="1"><Name>John</Name><Task>Task One</Task><Month ID="Month_2">2011-12</Month><Effort>23</Effort></Row><Row ID="1"><Name>John</Name><Task>Task One</Task><Month ID="Month_3">2012-01</Month><Effort>45</Effort></Row><Row ID="1"><Name>John</Name><Task>Task Two</Task><Month ID="Month_2">2011-12</Month><Effort>7.5</Effort></Row><Row ID="1"><Name>John</Name><Task>Task Two</Task><Month ID="Month_3">2012-01</Month><Effort>4</Effort></Row></Root> The <Month> values in <Months> in the initial xml file could actually run from Month_1 thru to Month_18. I can get the ID, Name and Task elements repeating, but i am struggling with the xslt for the Month and Effort values. <?xml version="1.0" encoding="UTF-8"?> <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"><xsl:output method="xml" indent="yes" /><xsl:template match="/"><xsl:element name="Root"><xsl:for-each select="//Row"><xsl:element name="Row"><xsl:attribute name="ID"><xsl:value-of select="ID" /></xsl:attribute><xsl:element name="Name"><xsl:value-of select="Name" /></xsl:element><xsl:element name="Task"><xsl:value-of select="Task" /></xsl:element><!-- NEED SOMETHING HERE TO GET THE NAME OF THE NEXT FIELD AND LOOK UP THE VALUES FROM THE MONTHS NODESET --><xsl:for-each select="/Root/Months"> </xsl:for-each> </xsl:element></xsl:for-each></xsl:element></xsl:template></xsl:stylesheet> I am sure (I think) i need to use local-name() to look up the name of each element in the <Months> nodeset and check if there is an element with that name in each of the <Row> elements and then pull the value of the month into the month element in the new structure.......this is where i am struggling.... Many thanks all!
  5. Many thanks. I did not realise that single and double quotes could be swapped in this way. All is working fine now. Graham
  6. Hi all,I am working on a small dashboard type set of pages to be used in IE7(and only IE7). I am now stumbling on one piece of Javascript / XSLT(Version 1.0)I have a person name field and an associated select html element tofilter the list of names from the xml file. This filter works fineexcept for one person's name that contains and apostrophe. I havetried using replace() function to add in a ' and escaping with a\ but to no avail. All other name values filter fine, just this onename, and i'm sure there will be other names appearing with an ' in it....The error message states : "Expected token ']' found 'NAME' //Row[Name=O'-->Callaghan<--]Should i change the encoding on the xml file itself? The XML isgenerated from an MS Excel 2007 workbookMany thanksGrahamXML (data.xml) <?xml version="1.0" encoding="UTF-8" standalone="yes"?><Root><Row><Name>Graham</Name></Row><Row><Name>Graham</Name></Row><Row><Name>Graham</Name></Row><Row><Name>Graham</Name></Row><Row><Name>Smith</Name></Row><Row><Name>O'Callaghan</Name></Row></Root> XSL (list.xsl) <?xml version="1.0"?><xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"><xsl:output method="html" /><xsl:variable name="Rows" select="//Row" /><xsl:template match="//Root"><xsl:for-each select="$Rows"><p><xsl:value-of select="Name" /></p></xsl:for-each></xsl:template></xsl:stylesheet> HTML / Javascript <html><head><script language="javascript">function LoadXML(xmlname){var xmlDOCxmlDOC = new ActiveXObject("Microsoft.XMLDOM");xmlDOC.async = false;xmlDOC.load(xmlname);return(xmlDOC);}function TransformXML(data, xform){xml=LoadXML(data);xsl=LoadXML(xform);dataFields = xml.transformNode(xsl);document.getElementById("divContent").innerHTML=dataFields;}function FilterXML(sFilter){filterNode = list.XMLDocument.selectSingleNode("xsl:stylesheet/xsl:variable[@name='Rows']")filterNode.setAttribute("select", "//Row[Name='" + sFilter + "']")dataFields = xml.transformNode(list.XMLDocument)document.getElementById("divContent").innerHTML=dataFields;}</script></head><body onLoad="TransformXML('data.xml', 'list.xsl')"><xml id="list" src="list.xsl" /><select id="cboList" onChange="FilterXML(this.value)"><option>Select..</option><option value="Graham">Graham</option><option value="Smith">Smith</option><option value="O'Callaghan">O'Callaghan</option></select><div id="divContent"></div></body></html>
  • Create New...