How to embed interactive spreadsheets on a web page

Abstract

This article shows how to embed an interactive spreadsheet on a web page and how to interact with this sheet via JavaScript. There are three candidates: Excel online, Google Sheets and interactive DIY HTML tables.

Excel Online

Extension

In the next version, it should be possible to dected the range of the data pasted into the Excel sheet (dynamic, not static range definition).

<div id="myExcelDiv" style="width: 402px; height: 346px"></div>

<script type="text/javascript" src="https://r.office.microsoft.com/r/rlidExcelWLJS?v=1&kip=1"></script>

<script type="text/javascript">

    /*

        * This code uses the Microsoft Office Excel Javascript object model to programmatically insert the

        * Excel Web App into a div with id=myExcelDiv. The full API is documented at

        * https://msdn.microsoft.com/en-US/library/hh315812.aspx. There you can find out how to programmatically get

        * values from your Excel file and how to use the rest of the object model.

        */

 

    // Use this file token to reference EmbeddedInteractiveSpreadsheet.xlsx in Excel's APIs

    var fileToken = "SDF80E794682ADBC11!58785/-572386758882378735/t=0&s=0&v=!ADrtLGSoGuwCZto";

 

    // run the Excel load handler on page load

    if (window.attachEvent) {

        window.attachEvent("onload", loadEwaOnPageLoad);

    } else {

        window.addEventListener("DOMContentLoaded", loadEwaOnPageLoad, false);

    }

 

    function loadEwaOnPageLoad() {

        var props = {

            uiOptions: {

                showDownloadButton: false,

                selectedCell: "'Sheet1'!A1",

                showParametersTaskPane: false

            },

            interactivityOptions: {

                allowTypingAndFormulaEntry: true,

                allowParameterModification: false,

                allowSorting: false,

                allowFiltering: false,

                allowPivotTableInteractivity: false

            }

        };

 

        Ewa.EwaControl.loadEwaAsync(fileToken, "myExcelDiv", props, onEwaLoaded);

    }

               

    // Add code here to interact with the embedded Excel web app.

    // Find out more at https://msdn.microsoft.com/en-US/library/hh315812.aspx.

    var ewa = null;

 

    function onEwaLoaded(asyncResult) {

        if (asyncResult.getSucceeded()) {

            // Use the AsyncResult.getEwaControl() method to get a reference to the EwaControl object

            ewa = Ewa.EwaControl.getInstances().getItem(0);

            //console.log(ewa.getActiveWorkbook());

 

            var mewa = asyncResult.getEwaControl();

 

        }

        else {

            alert("Async operation failed!");

        }

        // ...

    }

 

    function onGoButtonClick() {

 

        // index 0 based

        // Ewa.Workbook.getRange(parentName, firstRow, firstColumn, rowCount, columnCount);

        var range = ewa.getActiveWorkbook().getRange("Sheet1", 4, 2, 6, 1);

 

        // Get values from range.

        range.getValuesAsync(Ewa.ValuesFormat.Unformatted, getRangeValues, range);

    }

 

    function getRangeValues(asyncResult) {

        console.log(Ewa.AsyncErrorCode.Successful);

        //console.log(ewa.AsyncErrorCode.Successful);

        console.log(asyncResult.getReturnValue());

        // Get the value from asyncResult if the asynchronous operation was successful.

        if (asyncResult.getCode() == 0) {

            // Get range from user context.

            var range = asyncResult.getUserContext();

 

            // Get the array of range values from asyncResult.

            var values = asyncResult.getReturnValue();

 

            // Display range coordinates in A1 notation and associated values.

            var output = "Values from range" + range.getAddressA1() + "\n";

            output = output + "********\n";

 

            // Loop through the array of range values.

            for (var i = 0; i < values.length; i++) {

                for (var j = 0; j < values[i].length; j++) {

                    output = output + values[i][j] + '\n';

                }

            }

 

            output = output + "********";

 

            // Display each value in the array returned by getValuesAsync.

            //alert(output);

            $('#xl-data').val(output);

        }

        else {

            alert('Operation failed with error message ' + asyncResult.getDescription() + '.');

        }

    }

 

</script>

Google Sheets

It is possible to embed google sheets that display static content:

<iframe style="width: 400px; height: 350px;" src="https://docs.google.com/spreadsheets/d/1_-UUTpFksb522iaAlwgLGMilAmo8ekh3abYhoz80Qt4/pubhtml?gid=0&amp;single=true&amp;widget=true&amp;headers=true"></iframe>

Full Screen mode

Furthermore, it is possible to embed google sheets with compact controls or full screen mode:

<iframe style="width: 400px; height: 350px;" src="https://docs.google.com/spreadsheets/d/1_-UUTpFksb522iaAlwgLGMilAmo8ekh3abYhoz80Qt4/edit#gid=0"></iframe>

To programmatically interact with embedded Google sheets is not an easy task. It is thinkable to write Google Apps Script code and deploy it as API executable or to interact with SpreadSheet or GViz API.

HTML Table (contenteditable)

Product Q1 Q2 Q3 Q4
Apples 115 120 102 125
Oranges 115 120 102 125
This is absolute basic layout with contenteditable=true attribute. The yellow cells can be modified. Content editable could be toggled with a button. It is unclear how to manage cell overflow...

Sources

[1] Using the Excel Services JavaScript API to Work with Embedded Excel Workbooks
[2] Ewa namespace
[3] Ewa.Workbook Object


Dieter Neumann