Export XML to Excel spreadsheet using Javascript/classic-ASP

Question

I'm trying to export XML data from a web page to a spreadsheet using javascript. So far I've just tried the very simple:

Sub Export
    Response.ContentType = "application/vnd.ms-excel"
    <script>            
         var XML = document.getElementById("xmldata");              
         window.open(XML);
    </script>
End Sub

This tries to open the XML file as an excel spreadsheet, however it doesn't open because it has invalid characters. I've tracked down these characters and the main culprit tends to be the horizontal dash "–". There may be other invalid characters, but If I remove them manually, the XML file opens fine.

How would I go about formatting the XML content to remove or replace the invalid characters to display properly to excel? The XML sheet is built from fields in a database, should I format it as it's built or format it with javascript? I'm trying to find the simplest solution possible as I'm fairly new to web programming. Also, I'm using classic ASP/VBscript.

Answer 1

You have a mismatch in character encoding. Likely the client is assuming UTF-8 character encoding but you are sending from the server an ANSI encoding such as Windows-1252.

You need to ensure that the client knows that the server is sending Windows-1252 using Response.CharSet = "Windows-1252" in you server side code and including the following at the start of your xml:-

<?xml version="1.0" encoding="Windows-1252" ?>

Alternatively if you are using Response.Write to send the XML content you could use:-

 Response.Codepage = 65001
 Response.CharSet ="UTF-8"

Answer 2

Managed to solve it by getting the HTML table output from the XML data then putting that into Excel using ActiveX.

var x=listingTable.rows

var xls = new ActiveXObject("Excel.Application")
xls.visible = true
xls.Workbooks.Add

for (i = 0; i < x.length; i++)
{
    var y = x[i].cells

    for (j = 0; j < y.length; j++)
    {
        xls.Cells( i+1, j+1).Value = y[j].innerTex
    }
}