Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2004-08-31
11
Medium Priority
?
245 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 2000 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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying 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

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…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

670 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