How to mimic Excel download?

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • kj
    #1

    How to mimic Excel download?




    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.
    Tags: None
    • gimme_this_gimme_that@yahoo.com
      #2
      Re: How to mimic Excel download?

      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="JavaS cript">
      <!--
      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_for m" 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_r eport();"/>
      </form>
      </body>
      </html>

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

      <head>

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

      <script language="JavaS cript">
      var emmetts_1 = "";
      var emmetts_2 = "";
      var qs = window.location .search.substri ng(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.Applicati on\")\n";
      scr += "objXL.Visi ble = True\n";
      scr += "Set objwb = objXL.Workbooks .Add\n";
      scr += "Set objws = objwb.Sheets.Ad d\n";
      scr += populate_worksh eet();
      return scr;
      }

      function populate_worksh eet() {
      da = "data = Array (\"Hello\",\"Wo rld\")\n";
      da += "objws.Range(ob jws.Cells(1,1), objws.Cells(1,2 )).Value = data\n";
      da += "data = Array (\"Emmetts1\",\ ""+emmetts_1+"\ ")\n";
      da += "objws.Range(ob jws.Cells(2,1), objws.Cells(2,2 )).Value = data\n";
      da += "data = Array (\"Emmetts2\",\ ""+ emmetts_2 +"\")\n";

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


      // alert(build_wor kbook());
      </script>

      <script language="VBScr ipt">
      Function CreateReport
      Command = build_workbook( )
      Set ms = CreateObject("m sscriptcontrol. scriptcontrol")
      ms.language = "vbscript"
      ms.AllowUI = True
      ms.ExecuteState ment Command
      Window.Close
      End Function

      CreateReport
      </script>

      <body bgcolor="lightb lue">
      Excel Publish Example
      </body>


      Vist h2.html and click "View Report".




        Comment

        • kj
          #3
          Re: How to mimic Excel download?


          In <d4a284f3-7f35-4741-b4f3-d38784db95d9@u3 6g2000prf.googl egroups.com"gim me_this_gimme_t hat@yahoo.com" <gimme_this_gim me_that@yahoo.c omwrites:
          >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.

            Comment

            Previous template Next
            Working...