RSE 0007

Adding a Spreadsheet directive to Runestone Components

• Should provide basic formula support
• Should be able to load from text in the body of the directive or from a linked csv file
• Should have programmatic access to individual cells for auto-grading

Directive Syntax

.. spreadsheet:: uniqueid
    :numrows:  optional number for creating a blank sheet
    :numcols:  optional number for creating a blank sheet
    :fromcsv: csv file to insert into the body
    :fromurl: url to a csv to load at runtime

     value1, value2, =formula1, =formula2, value3,,
     value4, [value5-graded], =formula3, [=formula4-graded],,
     ...

Implementation

There are a number of javascript spreadsheet and data viewers available on github. It seems many of them lack functionality for functions out of the box, but jExcel seems active and well documented. I was able to put together a quick demo in raw html mode with a minimum fuss.

    <script src="https://cdnjs.cloudflare.com/ajax/libs/numeral.js/2.0.6/numeral.min.js"></script>
    <script src="../_static/excel-formula.min.js"></script>

    <script src="https://cdnjs.cloudflare.com/ajax/libs/jexcel/2.0.2/js/jquery.jexcel.js"></script>
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/jexcel/2.0.2/css/jquery.jexcel.min.css" type="text/css" />

    <div style="width: 600px; margin-right: auto; margin-left: auto;">
    <div id="my"></div>
    </div>

    <script>
        var data = [
        ['Cookie', 'Bowl1', 'Bowl2', 'Total'],
        ['Oreo',30,20,'=SUM(B2:C2)',],
        ['Chips Ahoy!',10,20,'=SUM(B3:C3)',],
        [,'=sum(B2:B3)','=sum(C2:C3)','=sum(d2:d3)',],
        [,,,,],
        ['P(Bowl1)','=B4/D4',,,],
        ['P(Bowl2)','=C4/D4',,,],
        ['P(Oreo)','=D2/D4',,,],
        ['P(ChipsAhoy)','=D3/D4',,,],
        [,,,,],
        ['P(Oreo | Bowl1)','=B2/B4',,,],
        ['P(Oreo | Bowl2)','=C2/C4',,,],
        [,,,,],
        ['P(ChipsAhoy | Bowl1)','=B3/B4',,,],
        ['P(chipsAhoy | Bowl2)','=C3/C4',,,],
        [,,,,],
        ['P(Bowl1 | Oreo)','=B2/D2',,,],
        ['P(Bow2 | Oreo)','=C2/D2',,,],
        ['P(Bow1 | ChipsAhoy)','=B3/D3',,,],
        ['P(Bowl2 | ChipsAhoy)','=C3/D3',,,],
        [,,,,]
        ]

        $('#my').jexcel({
        data:data,
        colWidths: [ 200, 80, 100, 100 ]
        });

    </script>

jExcel also includes lots of custom formatting options that we can figure out how to incorporate into the directive as we learn more and improve the implementation.