469,289 Members | 2,330 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,289 developers. It's quick & easy.

How to mimic Excel download?

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


--
NOTE: In my address everything before the first period is backwards;
and the last period, and everything after it, should be discarded.
Jun 27 '08 #1
2 2323
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)).Va lue = data\n";
da += "data = Array (\"Emmetts1\",\""+emmetts_1+"\")\n";
da += "objws.Range(objws.Cells(2,1),objws.Cells(2,2)).Va lue = data\n";
da += "data = Array (\"Emmetts2\",\""+ emmetts_2 +"\")\n";

da += "objws.Range(objws.Cells(3,1),objws.Cells(3,2)).Va lue = 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".


Jun 27 '08 #2
kj

In <d4**********************************@u36g2000prf. googlegroups.com"gi*******************@yahoo.com" <gi*******************@yahoo.comwrites:
>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.
Thanks! I'll give it a go.

k.

--
NOTE: In my address everything before the first period is backwards;
and the last period, and everything after it, should be discarded.
Jun 27 '08 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

9 posts views Thread by jillandgordon | last post: by
4 posts views Thread by Lewis Edward Moten III | last post: by
4 posts views Thread by Vishal | last post: by
3 posts views Thread by Rik Moed | last post: by
1 post views Thread by George Homorozeanu | last post: by
reply views Thread by techusky | last post: by
6 posts views Thread by Eddie | last post: by
reply views Thread by =?Utf-8?B?ZGlzcGxheW5hbWU=?= | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.