spreadsheet to HTML

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
I have a handful of cells in a Lotus spreadsheet that look something
like this (yes, I am aware that Lotus 1-2-3 is an antique, but I have
no choice in that particular matter):

Physio paid in 2010    user defined
Chiro paid in 2010    user defined
Physio auth in 2010    user defined
Chiro auth in 2010    user defined
total    @SUM(B1..B4)
Remaining    @SUM(800-B5)
Provider requesting visits at:    user defined
Number of visits available:    @ROUNDDOWN(B6/B7)

This is intended to calculate, based on an assumption of a shared
maximum yearly dollar limit of $800, how much a client may have left
in funds for his remaining treatment.

The calculation works perfectly in the spreadsheet; what I want to do
is have it total itself in an HTML page.  I can easily convert the
cells to HTML, but I don't know how to build a function into the
resulting web page that will add, subtract, and divied the necessary
figures, rather than just have them show up as a static display.

Does this make sense to anyone?

- Steve

Re: spreadsheet to HTML

On 22/10/10 19:01, stevedrost@hotmail.com wrote:
Quoted text here. Click to load it


You need to use html to lay out the page.

The page will contain form fields which can be used to enter data and
display results.

You can then either (a) submit the form to a server, which will do the
calculations and display the results (needs you to write a suitable
script for the server, and requires that the server support scripting)
or (b) use javascript to perform the calculations within the web page.


Denis McMahon

Re: spreadsheet to HTML

On 22/10/10 19:01, stevedrost@hotmail.com wrote:

Quoted text here. Click to load it

Is this what you want (uses javascript):



Denis McMahon

Re: spreadsheet to HTML

Denis McMahon wrote (in comp.infosystems.www.authoring.html):

Quoted text here. Click to load it

Not really.  Original relevant source code (slightly reformatted):

  <script type="text/javascript">
    function e(el) {
      return document.getElementById(el);
    function gf(el) {
      var v = parseFloat(e(el).value);
      if (isNaN(v)) return 0.0;
      return v;
    function recalc() {
      var pp10 = gf('pp10');
      var cp10 = gf('cp10');
      var pa10 = gf('pa10');
      var ca10 = gf('ca10');
      var tot = pp10 + cp10 + pa10 + ca10;
      e('tot').value = tot.toFixed(2);
      var rem = 800.0 - gf('tot');
      e('rem').value = rem.toFixed(2);
      var cpv = gf('cpv');
      if (cpv > 0) {
        var avail = Math.floor(rem/cpv);
        if (avail < 0) avail = '0';
        e('avail').value = avail.toFixed(2);
      else {
        e('avail').value = 'x.xx';
    <tr><td>Physio paid in 2010</td><td>$ <input id="pp10" size="7"
    <tr><td>Chiro paid in 2010</td><td>$ <input id="cp10" size="7"
    <tr><td>Physio auth in 2010</td><td>$ <input id="pa10" size="7"
    <tr><td>Chiro auth in 2010</td><td>$ <input id="ca10" size="7"
    <tr><td>total</td><td>$ <input id="tot" size="7"
    <tr><td>Remaining</td><td>$ <input id="rem" size="7"
    <tr><td>Provider requesting visits at:</td><td>$ <input id="cpv"
size="7" onchange="recalc();"></td></tr>
    <tr><td>Number of visits available:</td><td>$ <input id="avail" size="7"

That code works occasionally, but does so very inefficiently:

1. Number.prototype.toFixed() is borken in JScript (IE/MSHTML);
   a feature test and workaround is needed.

2. You do not need to use document.getElementById() if you use
   a FORM element, control names (that you need for the
   server-side fallback anyway), and the `elements' collection
   of the form which reference you can pass with `this' in
   the FORM element's event-handler attribute values (e.g.,
   onsubmit, onkeyup), or with `this.form' in the the control's
   event-handler attribute values (e.g., onchange).

3. The `change' event occurs only after the control has lost
   focus, and it does not bubble in *all* known DOM implementations.
   The `keyup' event, on the other hand, occurs immediately after
   the user has typed a character, and bubbles in all known DOM
   implementations.  Using that event instead of the `change' event
   would make the application more responsive.

4. It is inefficient and pointless to store the total (as a string)
   in the control value, then retrieve the control value and convert
   it back to number through gf('tot') for further computation.

5. It is inefficient, pointless, and harmful to maintenance
   to retrieve the reference to the same object more than
   one time; especially, it is very inefficient to have gf()
   retrieve the reference every time instead of only processing
   the passed primitive string value retrieved in a different way.

6. The relevant ECMAScript implementations only have one number
   type; 0.0 and 0 are the *same* value.

7. Depending on the application, rounding to a number of decimals
   must be applied not only to the displayed result, but also
   to the calculation, in order to avoid rounding errors.

8. The code

        var avail = Math.floor(rem/cpv);
        if (avail < 0) avail = '0';
        e('avail').value = avail.toFixed(2);

   will break the application (TypeError) if `avail' is assigned "0" before,
   since String instances (that the value of `avail' is converted to) do not
   have a toFixed() method, nor do they inherit one from their prototype;
   only Number instances do that.

So, to refactor (and optimize) this:

  <script type="text/javascript">
    function getFloat(v)
      var v = parseFloat(v);
      return isNaN(v) ? 0 : v;

    function recalc(form)
      var es = form.elements;
      var pp10 = getFloat(es['pp10'].value);
      var cp10 = getFloat(es['cp10'].value);
      var pa10 = getFloat(es['pa10'].value);
      var ca10 = getFloat(es['ca10'].value);

      /* `total' stores a _string_ after this */
      var total = (pp10 + cp10 + pa10 + ca10).toFixed(2);

      es['tot'].value = total;

       * Note the implicit conversion of `total' to number;
       * but `rem' stores a _string_ after this
      var rem = (800.0 - total).toFixed(2);

      e['rem'].value = rem;
      var ctrlAvail = es['avail'];
      var cpv = getFloat(es['cpv'].value);
      if (cpv > 0)
        /* Note the implicit conversion of `rem' to number */
        var avail = Math.floor(rem / cpv);

        if (avail < 0)
          avail = 0;

        ctrlAvail.value = avail.toFixed(2);
        ctrlAvail.value = 'x.xx';

  <!-- Three possibilities for triggering recalculation:
       submit, keyup, or change -->
  <form action="…"
        onsubmit="recalc(this); return false"
    <!-- or if you want to use the change event instead,
         onchange="recalc(this.form)" here
         (or add the listener dynamically) -->
    <input name="pp10" value="">
    <input type="submit" value="Recalculate">

Working around the toFixed() compatibility issue is left as an exercise to
the reader.  See also: <http://jibbering.com/faq/ .

Crosspost & Followup-To comp.lang.javascript

var bugRiddenCrashPronePieceOfJunk = (
    navigator.userAgent.indexOf('MSIE 5') != -1
    && navigator.userAgent.indexOf('Mac') != -1
)  // Plone, register_function.js:16

Re: spreadsheet to HTML

On Fri, 22 Oct 2010 11:01:38 -0700 (PDT), stevedrost@hotmail.com

Quoted text here. Click to load it

Just want to mention two systems on the sidelines that present
entire functioning spreadsheets through an HTML surface:

* Google Documents
* Microsoft SharePoint Server with Excel Services

There are probably several more.

Google Documents is free.

That said, it can certainly be individually programmed in


Site Timeline