Save the initial state of a workbook

Question

I would like to save the initial state of a workbook (or an Excel application) in the beginning, so that I could always get back to it regardless of modification on the workbook by my Add-in.

I tried some following code in Home.js:

(function() {
    "use strict";

    Office.initialize = function(reason) {
        $(document).ready(function() {
            app.initialize();
            initial();
            $('#getInitial').click(getInitial);
        });
    };

    var ctxInitial;

    function initial () {
        ctxInitial = new Excel.RequestContext();
    }

    function getInitial() {
        Excel.run(function () { 
            var wSheetName = 'Sheet1';
            var worksheet = ctxInitial.workbook.worksheets.getItem(wSheetName);
            var usedRange = worksheet.getUsedRange();
            usedRange.load(["values"]);
            return ctxInitial.sync().then(function() {
                document.getElementById("area").value += usedRange.values.toString();
            });
        });
    }
})();

In the very beginning, my tests prints well the initial values of the worksheet. However, after some manual modification on some cell values, getInitial prints the current state of the worksheet rather than the initial values.

Does anyone know what's the best practice to realise this?

Answer 1

A context does not store workbook state (How could it? It's a JavaScript object that lives in a completely separate world from the document). A context is merely a pipeline (or, if you will, an accumulator of commands) of what actions to dispatch.

There is no real reason to hang on to a context object, except for using it as a way of creating two objects from the same context (e.g., range1.getIntersection(range2), since objects must be from the same context in order to interact). But beyond that, the context's life can (and generally should) be as quick as possible. That's why in Excel.run we always create a new context for you, and dispose of it at the end.

On a related note, and for the same reasoning, it makes no sense to do an Excel.run and NOT use the context that it provides (or use a different context, as you do in your example). You could just as easily run your code without an Excel.run, it gains you nothing to have it be in an Excel.run block if you're reusing an existing context (and note that you won't get the automatic object-tracking that you would have with a clean Excel.run).

Hope this helps!

~ Michael Zlatkovsky, developer on Office Extensibility team, MSFT