Solved

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

Posted on 2004-08-31
11
238 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
 
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

707 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now