Solved

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

Posted on 2004-08-31
11
243 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

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…
Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

751 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