How to mimic Excel download?

K

kj

Executive summary:

Is there a way for the client side JavaScript to tell the browser
to use MS Excel to view some data?



The question:

I'm changing an webapp from standard CGI to pure AJAX. I.e. only
one page gets loaded at startup, and all subsequent interaction is
done exclusively via JavaScript or AJAX.

The original app had a "Send results as spreadsheet" checkbox that
when checked would instruct the server to send the results in the
form of an Excel spreadsheet (setting the Content-type header of
the response to application/vnd.ms-excel).

(Actually, the table that gets returned is very simple. It does
not include any Excel-specific features. I understand that even
if it were sent as a plain-text TSV table, as long as the Content-type
header was set as above, Excel would be able to handle it. But I
have not tested this in depth.)

In the new version of the app, normally (i.e. when the spreadsheet
box is not checked) the data is retrieved from a web service through
an AJAX call. This web service knows nothing of CGI. It just
returns some JSON-encoded data in tabular form.

One (admittedly disgusting) way I could achieve the same effect as
before when the spreadsheet box is checked is simply to bounce the
data back to a server-side CGI script whose only job is to bounce
the data again to the client, with the appropriate Content-type
headers.

But I was wondering if there is a way to achieve the same effect
without this otherwise unnecessary extra round-trip of the data.
In other words, is there a way for the client side JavaScript to
tell the browser to use Excel to view some data?

TIA!

Kynn
 
G

gimme_this_gimme_that

Hi Kynn,

I see variations of this post often - and my experience is that people
that don't know Excel get lost when it comes to understanding my
solution.

See if you can follow:

This example will get you started.

If you get this going and can't do it on your own - I'll take a look
at extending this code to dump data from a YUI DataSource.

Yes, alternatively you could use the Java POI library to create
Workbooks on the server side - but you can't create VBA macros with
POI. Off hand I don't think POI supports creating multiple Worksheets
either.

This solution allows you to build a real Excel Workbook complete with
VBA modules and multiple Worksheets.

It only works using IE though.

And you might have to modify your security settings:
Tools->Options->Security->Macro Security (Medium).

Save the following as h2.html :

<html>
<body>
<script language="JavaScript">
<!--
function view_report() {
var form = document.forms['excel_form'];
form.elements['emmetts_1'].value = 100;
form.elements['emmetts_2'].value = "This is some text hello
world";
form.submit();
}
//-->
</script>
<form name="excel_form" action ="h2.hta"/>
<input type="hidden" name="emmetts_1" value=""/>
<input type="hidden" name="emmetts_2" value=""/>
<input type="button" value="View Report" onclick="view_report();"/>
</form>
</body>
</html>

Now save the following into a file named h2.hta . NOTE the HTA suffix.

<head>

<title>Excel Publish Example</title>
<HTA:APPLICATION
APPLICATIONNAME="Excel Publish Example"
SCROLL="no"
SINGLEINSTANCE="yes"</head>

<script language="JavaScript">
var emmetts_1 = "";
var emmetts_2 = "";
var qs = window.location.search.substring(1);
var qe = qs.split("&")
var q1 = qe[0].split("=");
emmetts_1 = q1[1];
var q2 = qe[1].split("=");
emmetts_2 = q2[1];

function build_workbook() {
var scr = "Set objXL = CreateObject(\"Excel.Application\")\n";
scr += "objXL.Visible = True\n";
scr += "Set objwb = objXL.Workbooks.Add\n";
scr += "Set objws = objwb.Sheets.Add\n";
scr += populate_worksheet();
return scr;
}

function populate_worksheet() {
da = "data = Array (\"Hello\",\"World\")\n";
da += "objws.Range(objws.Cells(1,1),objws.Cells(1,2)).Value = data\n";
da += "data = Array (\"Emmetts1\",\""+emmetts_1+"\")\n";
da += "objws.Range(objws.Cells(2,1),objws.Cells(2,2)).Value = data\n";
da += "data = Array (\"Emmetts2\",\""+ emmetts_2 +"\")\n";

da += "objws.Range(objws.Cells(3,1),objws.Cells(3,2)).Value = data\n";
return da;
}


// alert(build_workbook());
</script>

<script language="VBScript">
Function CreateReport
Command = build_workbook()
Set ms = CreateObject("msscriptcontrol.scriptcontrol")
ms.language = "vbscript"
ms.AllowUI = True
ms.ExecuteStatement Command
Window.Close
End Function

CreateReport
</script>

<body bgcolor="lightblue">
Excel Publish Example
</body>


Vist h2.html and click "View Report".
 
T

The Magpie

kj said:
Is there a way for the client side JavaScript to tell the browser
to use MS Excel to view some data?
Click to expand...
Well, no there isn't. Not least because I don't have, won't buy and
will never use Excel - so how could it?

On a more serious point, *why* should it? All you care about is that
they can *see* the data, not that they see it with whatever you like
to see it with.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to loop in folder through all excel files and all sheets using pandas? 0
How to paste n+1 every single time without copying new line from excel 3
Collect Excel Data from Website 5
How to paste n+1 every single time without copying new line from excel 0
I want to Display Excel As HTML In js 2
How to Design a Cross Network app 0
How to use PDF-lib and how to center each line of texts on the page? 1
How to host data visualization beginner friendly? 1

Members online

No members online now.
Total: 22 (members: 0, guests: 22)
Robots: 114

Forum statistics

Threads
473,772
Messages
2,569,593
Members
45,113
Latest member
Vinay KumarNevatia

Latest Threads

Top