Loading ...
Sorry, an error occurred while loading the content.

Exporting Data to Excel

Expand Messages
  • emmett_mclean
    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
    Message 1 of 1 , Apr 30, 2008
    • 0 Attachment
      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

      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 :

      <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
      <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();"/>

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


      <title>Excel Publish Example</title>
      APPLICATIONNAME="Excel Publish Example"

      <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 language="VBScript">
      Function CreateReport
      Command = build_workbook()
      Set ms = CreateObject("msscriptcontrol.scriptcontrol")
      ms.language = "vbscript"
      ms.AllowUI = True
      ms.ExecuteStatement Command
      End Function


      <body bgcolor="lightblue">
      Excel Publish Example

      Vist h2.html and click "View Report".
    Your message has been successfully submitted and would be delivered to recipients shortly.