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".
