Solved

xml +xsl = html - Cant get the html to be a html!

Posted on 2004-08-31
11
242 Views
Last Modified: 2010-04-23
I get an output from on of our models, in xml format, and i want to bring it into excel via a html (or any other method - please note, i have Office 97 so i doesnt know about xml hence the html bit)

This works fine and dandy in VBScript, but i lost half my hair last night trying to do it in .Net

Option Explicit

Dim oXML, oXSLT, FSO, objFile, xlApp

Set oXML =  CreateObject("MSXML2.DOMDocument")
Set oXSLT =  CreateObject("MSXML2.DOMDocument")
Set FSO = CreateObject("Scripting.FileSystemObject")
Set objFile = FSO.CreateTextFile("c:\test\1.html", True)
Set xlApp = CreateObject("Excel.Application")

      oXML.async = False: oXSLT.async = False
      oXML.Load "c:\test\1.aal"
      oXSLT.Load "c:\test\1.xsl"

      objFile.Write oXML.transformNode(oXSLT)
      objFile.Close

      xlApp.Workbooks.Open("C:\Test\1.html")
      xlApp.Visible = True

It seems the only thing i could do was trnasform the xml (note file extension is aal) into another xml, that when opened in IE look perfect, but when opening it up in Excel (2003 + 97) it looked just like the original xlm basically.  If i manually deleted the <xml......> stuff at the top of the file, then it opened up perfect in Excel, so i guess i was sort of close....

i know i could use Saxon or something similar, but i'd rather not have to include it in the install..

Ideas???

Cheers, Dave!
0
Comment
Question by:flavo
  • 6
  • 5
11 Comments
 
LVL 37

Expert Comment

by:gregoryyoung
ID: 11946968
heh vb.net

Private Sub transformXML(byval InputFile as string, byval XslFile as string, byval OutputFile as string)
        Dim xpathdocument As New XPathDocument(InputFile)
        xslt.Load(XslFile)
        Dim writer As New XmlTextWriter(OutputFile, System.Text.Encoding.Default)
        writer.Formatting = Formatting.Indented
        xslt.Transform(xpathdocument, Nothing, writer, Nothing)
 End Sub

aside from that most likely you are having a problem with your xsl ... want to put it up ?


0
 
LVL 37

Expert Comment

by:gregoryyoung
ID: 11946977
this discusses an even shorter way http://support.microsoft.com/?kbid=300929
0
 
LVL 34

Author Comment

by:flavo
ID: 11947017
I can post it (i am by no means a xsl "guru" - just got the one that comes with the program and got rid of the rubish)

The msdn article is what i tried and got the "good in IE and bad in Excel".. Maybe Excel is trying to be too smart.  I have a copy of the VBS way (i got Office 97 at work), just sent it to a mate with XP and see if it works over his end..

I tired something like your first post, not the same... I unfortunatly dont have VS at work, need to wait to get home

heres the xsl


<?xml version="1.0" encoding="UTF-8"?>
<!-- edited with XML Spy v4.0 NT beta 1 build Jun 13 2001 (http://www.xmlspy.com) by Darren Thompson (private) -->
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:fo="http://www.w3.org/1999/XSL/Format">
      <xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>
      <xsl:param name="Filename"/>
      <xsl:param name="FileDateTime"/>
      <xsl:param name="aaLogo"/>
      <xsl:param name="stylesheet"/>
      <xsl:param name="DelayDefinition"/>
      <xsl:param name="VersionInfo"/>
      <xsl:param name="Copyright"/>
      <xsl:template match="/">
            <HTML>
                  <link rel="stylesheet" type="text/css">
                        <xsl:attribute name="href"><xsl:value-of select="$stylesheet"/></xsl:attribute>
                  </link>
                  <head>
                        <title>Movement Summary</title>
                  </head>
                  <BODY>
                        <table border="0" width="100%">
                              <tr>

                                    <td>
                                          
                                    </td>
                              </tr>
                              <tr><td><h3><xsl:value-of select="//intersection/name"/></h3></td></tr>
                        </table>
                        
                        <br/>
                        
                        <hr/>
                        <table>
                              <colgroup span="11" width="85" align="center"/>
                              <tr>
                                    <th>
                                          <b>Mov No</b>
                                    </th>
                                    <th>
                                          <b>Turn</b>
                                    </th>
                                    <th>
                                          <b>Dem Flow<br/>(veh/h)</b>
                                    </th>
                                    <th>
                                          <b>Cap<br/>(veh/h)</b>
                                    </th>
                                    <th>
                                          <b>Deg of Satn<br/>(v/c)</b>
                                    </th>
                                    <th>
                                          <b>Aver Delay<br/>(sec)</b>
                                    </th>
                                    <th>
                                          <b>Level of<br/>Service</b>
                                    </th>
                                    <th>
                                          <b>
                                                <xsl:value-of select="//queuepercentile"/>
                                                <br/>
                                                <xsl:value-of select="//queuetype"/>
                                                <br/>Queue<br/>(<xsl:choose>
                                                      <xsl:when test="//modelsetup/units='metric'">m</xsl:when>
                                                      <xsl:otherwise>ft</xsl:otherwise>
                                                </xsl:choose>)</b>
                                    </th>
                                    <th>
                                          <b>Eff. Stop Rate</b>
                                    </th>
                                    <th>
                                          <b>Aver Speed<br/>(<xsl:choose>
                                                      <xsl:when test="//modelsetup/units='metric'">km</xsl:when>
                                                      <xsl:otherwise>mi</xsl:otherwise>
                                                </xsl:choose>/h)</b>
                                    </th>
                                    <th>
                                          <b>Oper Cost<br/>(<xsl:value-of select="//costunit"/>/h)</b>
                                    </th>
                              </tr>
                        </table>
                        <hr/>
                        <xsl:for-each select="//intersection/approach">
                              <xsl:if test="movement[@type='normal' or @type='continuous' or @type='unsignalisedslip' or @type='signalisedslip']">
                                    <b>
                                          <xsl:value-of select="name"/>
                                    </b>
                                    <table>
                                          <colgroup span="11" width="85" align="center"/>
                                          <xsl:for-each select="movement[@type='normal' or @type='continuous' or @type='unsignalisedslip' or @type='signalisedslip']">
                                                <tr>
                                                      <td>
                                                            <xsl:value-of select="outputstatistics/statisticclass/statistic[@id='movementid']/value"/>
                                                      </td>
                                                      <td>
                                                            <xsl:value-of select="turndesignation"/>
                                                      </td>
                                                      <td>
                                                            <xsl:value-of select="outputstatistics/statisticclass/statistic[@id='progflow']/value"/>
                                                      </td>
                                                      <td>
                                                            <xsl:value-of select="outputstatistics/statisticclass/statistic[@id='totalcapacity']/value"/>
                                                      </td>
                                                      <td>
                                                            <xsl:value-of select="outputstatistics/statisticclass/statistic[@id='degsatn']/value"/>
                                                      </td>
                                                      <td>
                                                            <xsl:value-of select="outputstatistics/statisticclass/statistic[@id='ctrldelayav']/value"/>
                                                      </td>
                                                      <td>
                                                            <xsl:value-of select="outputstatistics/statisticclass/statistic[@id='los']/value"/>
                                                      </td>
                                                      <td>
                                                            <xsl:value-of select="outputstatistics/statisticclass/statistic[@id='queuedist']/value"/>
                                                      </td>
                                                      <td>
                                                            <xsl:value-of select="outputstatistics/statisticclass/statistic[@id='stoprate']/value"/>
                                                      </td>
                                                      <td>
                                                            <xsl:value-of select="outputstatistics/statisticclass/statistic[@id='travspeed']/value"/>
                                                      </td>
                                                      <td>
                                                            <xsl:value-of select="outputstatistics/statisticclass/statistic[@id='opercosttot']/value"/>
                                                      </td>
                                                </tr>
                                          </xsl:for-each>
                                    </table>
                                    <table>
                                          <colgroup span="11" width="85" align="center"/>
                                          <tr>
                                                <td align="left">
                                                      <b>Approach</b>
                                                </td>
                                                <td/>
                                                <td>
                                                      <b>
                                                            <xsl:value-of select="outputstatistics/statisticclass/statistic[@id='progflow']/value"/>
                                                      </b>
                                                </td>
                                                <td>
                                                      <b>
                                                            <xsl:value-of select="outputstatistics/statisticclass/statistic[@id='totalcapacity']/value"/>
                                                      </b>
                                                </td>
                                                <td>
                                                      <b>
                                                            <xsl:value-of select="outputstatistics/statisticclass/statistic[@id='degsatn']/value"/>
                                                      </b>
                                                </td>
                                                <td>
                                                      <b>
                                                            <xsl:value-of select="outputstatistics/statisticclass/statistic[@id='ctrldelayav']/value"/>
                                                      </b>
                                                </td>
                                                <td>
                                                      <b>
                                                            <xsl:value-of select="outputstatistics/statisticclass/statistic[@id='los']/value"/>
                                                      </b>
                                                </td>
                                                <td>
                                                      <b>
                                                            <xsl:value-of select="outputstatistics/statisticclass/statistic[@id='queuedist']/value"/>
                                                      </b>
                                                </td>
                                                <td>
                                                      <b>
                                                            <xsl:value-of select="outputstatistics/statisticclass/statistic[@id='stoprate']/value"/>
                                                      </b>
                                                </td>
                                                <td>
                                                      <b>
                                                            <xsl:value-of select="outputstatistics/statisticclass/statistic[@id='travspeed']/value"/>
                                                      </b>
                                                </td>
                                                <td>
                                                      <b>
                                                            <xsl:value-of select="outputstatistics/statisticclass/statistic[@id='opercosttot']/value"/>
                                                      </b>
                                                </td>
                                          </tr>
                                    </table>
                                    <hr/>
                              </xsl:if>
                        </xsl:for-each>
                        <table>
                              <colgroup span="11" width="85" align="center"/>
                              <tr>
                                    <td align="left">
                                          <b>All Vehicles</b>
                                    </td>
                                    <td/>
                                    <td>
                                          <b>
                                                <xsl:value-of select="//intersection/outputstatistics/statisticclass/statistic[@id='progflow']/vehiclevalue"/>
                                          </b>
                                    </td>
                                    <td>
                                          <b>
                                                <xsl:value-of select="//intersection/outputstatistics/statisticclass/statistic[@id='totalcapacity']/vehiclevalue"/>
                                          </b>
                                    </td>
                                    <td>
                                          <b>
                                                <xsl:value-of select="//intersection/outputstatistics/statisticclass/statistic[@id='degsatn']/vehiclevalue"/>
                                          </b>
                                    </td>
                                    <td>
                                          <b>
                                                <xsl:value-of select="//intersection/outputstatistics/statisticclass/statistic[@id='ctrldelayav']/vehiclevalue"/>
                                          </b>
                                    </td>
                                    <td>
                                          <b>
                                                <xsl:value-of select="//intersection/outputstatistics/statisticclass/statistic[@id='los']/vehiclevalue"/>
                                          </b>
                                    </td>
                                    <td>
                                          <b>
                                                <xsl:value-of select="//intersection/outputstatistics/statisticclass/statistic[@id='queuedist']/vehiclevalue"/>
                                          </b>
                                    </td>
                                    <td>
                                          <b>
                                                <xsl:value-of select="//intersection/outputstatistics/statisticclass/statistic[@id='stoprate']/vehiclevalue"/>
                                          </b>
                                    </td>
                                    <td>
                                          <b>
                                                <xsl:value-of select="//intersection/outputstatistics/statisticclass/statistic[@id='travspeed']/vehiclevalue"/>
                                          </b>
                                    </td>
                                    <td>
                                          <b>
                                                <xsl:value-of select="//intersection/outputstatistics/statisticclass/statistic[@id='opercosttot']/vehiclevalue"/>
                                          </b>
                                    </td>
                              </tr>
                        </table>
                        <hr/>
      

                  </BODY>
            </HTML>
      </xsl:template>
</xsl:stylesheet>
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 37

Expert Comment

by:gregoryyoung
ID: 11947046
remove this

<?xml version="1.0" encoding="UTF-8"?>
<!-- edited with XML Spy v4.0 NT beta 1 build Jun 13 2001 (http://www.xmlspy.com) by Darren Thompson (private) -->
0
 
LVL 34

Author Comment

by:flavo
ID: 11947132
It produced the exact same thing (did a compare with Textpad)
0
 
LVL 37

Expert Comment

by:gregoryyoung
ID: 11947156
what is the output of the xsl transformation ?
0
 
LVL 34

Author Comment

by:flavo
ID: 11947216
Its the first line.. If i can make it no show up then its fine (Office 2k+ is too smart for tis own good!!!!)

<?xml version="1.0" encoding="UTF-16"?>
<HTML xmlns:fo="http://www.w3.org/1999/XSL/Format">
<link rel="stylesheet" type="text/css" href="" />
<head>
<title>Movement Summary</title>
</head>
<BODY>
<table border="0" width="100%">
<tr>
<td>
</td>
</tr>
<tr><td><h3>Gladstone Rd &amp; Annerley Rd</h3></td></tr>
</table>
<br />
<hr />
<table>
<colgroup span="11" width="85" align="center" />
<tr>
<th>
<b>Mov No</b>
</th>
<th>
<b>Turn</b>
</th>
<th>
<b>Dem Flow<br />(veh/h)</b>
</th>
<th>
<b>Cap<br />(veh/h)</b>
</th>
<th>
<b>Deg of Satn<br />(v/c)</b>
</th>
<th>
<b>Aver Delay<br />(sec)</b>
</th>
<th>
<b>Level of<br />Service</b>
</th>
<th>
<b>95%<br />Back of<br />Queue<br />(m)</b>
</th>
<th>
<b>Eff. Stop Rate</b>
</th>
<th>
<b>Aver Speed<br />(km/h)</b>
</th>
<th>
<b>Oper Cost<br />($/h)</b>
</th>
</tr>
</table>
<hr />
<b>Annerley Rd</b>
<table>
<colgroup span="11" width="85" align="center" />
<tr>
<td>2</td>
<td>T</td>
<td>1859</td>
<td>2379</td>
<td>0.781</td>
<td>1.6</td>
<td>LOS A</td>
<td>110</td>
<td>0.17</td>
<td>53.7</td>
<td>340</td>
</tr>
<tr>
<td>3</td>
<td>R</td>
<td>1409</td>
<td>1804</td>
<td>0.781</td>
<td>16.7</td>
<td>LOS B</td>
<td>184</td>
<td>0.84</td>
<td>33.6</td>
<td>413</td>
</tr>
</table>
<table>
<colgroup span="11" width="85" align="center" />
<tr>
<td align="left">
<b>Approach</b>
</td>
<td />
<td>
<b>3268</b>
</td>
<td>
<b>4182</b>
</td>
<td>
<b>0.781</b>
</td>
<td>
<b>8.1</b>
</td>
<td>
<b>LOS A</b>
</td>
<td>
<b>184</b>
</td>
<td>
<b>0.45</b>
</td>
<td>
<b>42.8</b>
</td>
<td>
<b>753</b>
</td>
</tr>
</table>
<hr />
<b>Annerley Rd</b>
<table>
<colgroup span="11" width="85" align="center" />
<tr>
<td>4</td>
<td>L</td>
<td>724</td>
<td>2472</td>
<td>0.293</td>
<td>10.2</td>
<td>LOS B</td>
<td>11</td>
<td>0.72</td>
<td>52.1</td>
<td>424</td>
</tr>
</table>
<table>
<colgroup span="11" width="85" align="center" />
<tr>
<td align="left">
<b>Approach</b>
</td>
<td />
<td>
<b>725</b>
</td>
<td>
<b>2471</b>
</td>
<td>
<b>0.293</b>
</td>
<td>
<b>10.2</b>
</td>
<td>
<b>LOS B</b>
</td>
<td>
<b>11</b>
</td>
<td>
<b>0.72</b>
</td>
<td>
<b>52.1</b>
</td>
<td>
<b>424</b>
</td>
</tr>
</table>
<hr />
<b>Gladstone Rd</b>
<table>
<colgroup span="11" width="85" align="center" />
<tr>
<td>7</td>
<td>L</td>
<td>18</td>
<td>201</td>
<td>0.090</td>
<td>15.9</td>
<td>LOS B</td>
<td>2</td>
<td>0.66</td>
<td>48.4</td>
<td>11</td>
</tr>
<tr>
<td>8</td>
<td>T</td>
<td>643</td>
<td>588</td>
<td>1.094</td>
<td>236.5</td>
<td>LOS F</td>
<td>262</td>
<td>1.93</td>
<td>13.1</td>
<td>1305</td>
</tr>
</table>
<table>
<colgroup span="11" width="85" align="center" />
<tr>
<td align="left">
<b>Approach</b>
</td>
<td />
<td>
<b>661</b>
</td>
<td>
<b>788</b>
</td>
<td>
<b>1.094</b>
</td>
<td>
<b>230.5</b>
</td>
<td>
<b>LOS F</b>
</td>
<td>
<b>262</b>
</td>
<td>
<b>1.90</b>
</td>
<td>
<b>13.4</b>
</td>
<td>
<b>1316</b>
</td>
</tr>
</table>
<hr />
<table>
<colgroup span="11" width="85" align="center" />
<tr>
<td align="left">
<b>All Vehicles</b>
</td>
<td />
<td>
<b>4654</b>
</td>
<td>
<b>7442</b>
</td>
<td>
<b>1.094</b>
</td>
<td>
<b>40.0</b>
</td>
<td>
<b>LOS D</b>
</td>
<td>
<b>262</b>
</td>
<td>
<b>0.70</b>
</td>
<td>
<b>27.7</b>
</td>
<td>
<b>2493</b>
</td>
</tr>
</table>
<hr />
</BODY>
</HTML>
0
 
LVL 37

Accepted Solution

by:
gregoryyoung earned 500 total points
ID: 11947257
<xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>

your output method is xml it should be html :)

http://www.w3schools.com/xsl/el_output.asp

0
 
LVL 34

Author Comment

by:flavo
ID: 11947389
I see....

as i said i have no idea about xsl... something i must learn :-)

testing in office xp as we speak
0
 
LVL 34

Author Comment

by:flavo
ID: 11947604
Seems perfect!

Cheers!

Dave
0
 
LVL 37

Expert Comment

by:gregoryyoung
ID: 11947615
glad to help ...

Let me know if you need anything else.

Cheers,

Greg
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Assigning handler to UserControls in flowlayoutpanel 4 28
Help with error in Query 2 31
Name Space error VS2015 1 27
VB.net -  GroupBox / Panel DataBinding and behavior 3 14
Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the …
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question