Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 246
  • Last Modified:

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

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
flavo
Asked:
flavo
  • 6
  • 5
1 Solution
 
gregoryyoungCommented:
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
 
gregoryyoungCommented:
this discusses an even shorter way http://support.microsoft.com/?kbid=300929
0
 
flavoAuthor Commented:
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
Technology Partners: 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!

 
gregoryyoungCommented:
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
 
flavoAuthor Commented:
It produced the exact same thing (did a compare with Textpad)
0
 
gregoryyoungCommented:
what is the output of the xsl transformation ?
0
 
flavoAuthor Commented:
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
 
gregoryyoungCommented:
<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
 
flavoAuthor Commented:
I see....

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

testing in office xp as we speak
0
 
flavoAuthor Commented:
Seems perfect!

Cheers!

Dave
0
 
gregoryyoungCommented:
glad to help ...

Let me know if you need anything else.

Cheers,

Greg
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now