WorldSheet: A Shared Spreadsheet in the Cloud based on SignalR and Windows Azure

WorldSheet (http://worldsheet.net) [temporarily offline for migration] is a new sample I developed today that showcases SignalR combined with Windows Azure. It’s a shared spreadsheet in the cloud that multiple people can connect to and edit in real-time. It can be used on desktop browsers as well as modern tablets and smartphones.

To use worldsheet, simply browse to it (along with a few other people, or on several browsers/devices) and start editing: everyone should be seeing the same view. Cell value changes are passed on when you move off the cell via the tab key, an arrow key, or clicking elsewhere in the spreadsheet.

WorldSheet is an MVC4 application hosted in Windows Azure Compute that stores its data in the Windows Azure Cache Service. SignalR routes notifications of cell changes to all connected parties.

SignalR

SignalR is an async signaling library that can be called by .NET clients as well as JavaScript clients. For web applications, it’s very useful for pushing out notifications to web clients in a multi-user setting. Although it isn’t finished yet and has some limitations today, it’s powerful, reliable, and easy to use. Since real-time notifications are becoming more and more common in web work, a first-class library for handling them in ASP.NET is a welcome addition.

To get started with SignalR, I read Scott Hanselman’s introductory post on SignalR and was able to create an MVC4 application by following the instructions without too much trouble. SignalR is out on GitHub but you can install its pieces in Visual Studio using the Library Package Manager (nuget).

From what I’ve been reading, SignalR is coming along well but isn’t completed yet and has some limitations. It doesn’t appear it’s ready to work in a scale-out (farm) scenario yet but that’s coming.
In a web client, you can get SignalR initialized with just a little bit of JavaScript that also specifies a message processing function.

$(function () {
    // Proxy created on the fly
    var chat = $.connection.chat;

    // Declare a function on the chat hub so the server can invoke it

    chat.addMessage = function (message) {

        ...process received message...
    };

    $("#broadcast").click(function () {
        // Call the chat method on the server
    });

    // Start the connection
    $.connection.hub.start();
});

To send a message, you use code like this:

var chat = $.connection.chat;
chat.send("my message");

As you can see, SignalR is short and simple to use.

SignalR on Windows Azure

Having been able to see SignalR work I was eager to get it working on Windows Azure. It won’t do so out of the box, but Clemens Vasters of Microsoft has created a module that makes SignalR work via the Windows Azure Service Bus. Clemens’ article is essential reading to make this work and the SignalR.WindowsAzureServiceBus module is also on GitHub. Again, my understanding is that this isn’t working well yet in a multiple server instance configuration so I confined my experimentation to a single Windows Azure instance for the time being. That means, WorldSheet might occasionally not be available due to data center maintenance operations such as patching.

To use SignalR on Windows Azure, you need to add the SignalR.WindowsAzureServiceBus module to your solution, create a Service Bus namespace in the Windows Azure portal, and add some initialization code in your global.asax file’s Application_Start method.

protected void Application_Start()
{
    AreaRegistration.RegisterAllAreas();

    RegisterGlobalFilters(GlobalFilters.Filters);
    RegisterRoutes(RouteTable.Routes);

    BundleTable.Bundles.RegisterTemplateBundles();

    var config = AspNetHost.DependencyResolver.Resolve<IConfigurationManager>();
    config.ReconnectionTimeout = TimeSpan.FromSeconds(25);

    // TODO: specify your Serivce Bus access
    AspNetHost.DependencyResolver.UseWindowsAzureServiceBus("{namespace}","{account}", "{key}", "{appname}", 2);
}

My first attempts to get SignalR working on Windows Azure (working locally via the Windows Azure Simulation Environment) were disappointing: about one time in twenty the code would work, but most of the time I received TypeLoadExceptions on start-up that were difficult to make sense out of. The fact that it sometimes worked smacked of a race condition but I was having a hard time tracking it down. Success came when I realized that the application needs to reference the SignalR library in the SignalR.WindowsAzureServiceBus module, not the version of the library that nuget provides. Once that was in place, I had success every time I ran.

WorldSheet

Having gotten SignalR to be happy on Windows Azure, it was time to build something. Chat programs seem to be really overdone, so I wanted to come up with something else. I came up with the idea of a shared Excel-like spreadsheet experience that multiple people could use simultaneously. I spent most of today and tonight creating WorldSheet and it’s now live at http://worldsheet.net.

WorldSheet is wide open to anyone to use; if you want some semblance of privacy to a smaller group, you can change the tab name (initially ‘default’) to something else only your parties would know.

WorldSheet on the Client

When a user moves off of a cell, it causes an onBlur event that causes a JavaScript function to run that sends out the update via SignalR to other connected parties. The MVC server app is also notified with an Ajax call and updates the copy of the spreadsheet in the Windows Azure Cache Service. When a new user connects, they call the server to load the latest copy of the spreadsheet out of the cache.

function Cell_Changed(e) {
    if (e === undefined) return;
    var chat = $.connection.chat;
    var tab = $('#tabname').val();
    var id = e.id;
    var value = $('#' + id).val();
    chat.send(tab + "~" + id + '|' + value);
    Update(id, value);
}

To load a spreadsheet, the web client makes an Ajax call to the server specifying the tab name to load:

// Load a spreadsheet.

function Load() {
    var name = $('#tabname').val();
    if (name === '') {
        name = "default";
        $('#tabname').val(name);
    }

    var url = '/Home/Load/' + name + "/";

    var request = {
        name: name
    };

    $.ajax({
        type: "POST",
        url: url,
        cache: false,
        dataType: "json",
        data: JSON.stringify(request),
        contentType: "application/json; charset=utf-8",
        success: function (result) {
            if (result !== undefined && result !== null && result.status === "success") {

                ClearSheet();
                var chat = $.connection.chat;
                var tab = $('#tabname').val();
                chat.send(tab + "~*|" + result.data);

                var id;
                var value;
                var values = result.data.split('|');
                for (var i = 0; i < values.length; i += 2) {
                    id = values[i];
                    value = values[i + 1];
                    $('#' + id).val(value);
                }
            }
            else {
                alert('Sorry, the spreadsheet could not be retrieved from cloud cache');
            }
        },
        error: function (error) {
            alert("Sorry, communication with the server failed.");
        }
    });
}

To pass on a cell update, this function is called:

// Update cell in spreadsheet.

function Update(id, value) {
    var name = $('#tabname').val();
    if (name === '') {
        name = "default";
        $('#tabname').val(name);
    }

    var url = '/Home/Update/' + name + "/";

    var request = {
        name: name,
        cellId: id,
        cellValue: value
    };

    $.ajax({
        type: "POST",
        url: url,
        cache: false,
        dataType: "json",
        data: JSON.stringify(request),
        contentType: "application/json; charset=utf-8",
        success: function (result) {
            if (result !== undefined && result !== null && result.status === "success") {
                //alert('The cell has been updated.');
            }
            else {
                alert("Sorry, the cell could not be updated in cloud cache");   // : " + error.responseText);
                alert(error.status);
                alert(error.error);
            }
        },
        error: function (error) {
            alert("Sorry, communication with the server failed.");
        }
    });
}

WorldSheet on the Server

On the server, the HomeController of the MVC application uses the Windows Azure Cache Service to hold the spreadsheet data. It’s initialized with this code and config:

static DataCacheFactory dataCacheFactory = new DataCacheFactory();
static DataCache dataCache = dataCacheFactory.GetDefaultCache();

<dataCacheClients>
  <dataCacheClient name="default">
    <hosts>
      <host name="worldsheet.cache.windows.net" cachePort="22233" />
    </hosts>
    <securityProperties mode="Message">
      <messageSecurity
        authorizationInfo="YWNzOmh0dHBzOi8vd29ybGR...WhOQXJ1UXVBPSZodHRwOi8vd29ybGRzaGVldC5jYWNoZS53aW5kb3dzLm5ldA==">
      </messageSecurity>
    </securityProperties>
  </dataCacheClient>
</dataCacheClients>

Putting the spreadsheet to or from the cache is easy thanks to the simple nature of the Windows Azure Cache Service API. Here’s the function to load a spreadsheet out of cache:

// Load a worksheet from storage.

[HttpPost]
public JsonResult Load(LoadRequestModel request)
{
    LoadResponseModel response = new LoadResponseModel();

    try
    {
        Document doc = dataCache.Get(request.name) as Document;

        if (doc == null)
        {
            doc = new Document();
        }

        response.status = "success";
        response.data = doc.DataString;

        return Json(response);
    }
    catch (Exception ex)
    {
        response.status = "error";
        response.error = ex.ToString();
    }
    return Json(response);
}

And here’s the server method to update a cell of the spreadsheet whose value has changed:

// Update a cell.

[HttpPost]
public JsonResult Update(UpdateRequestModel request)
{
    UpdateResponseModel response = new UpdateResponseModel();

    try
    {
        Document doc = dataCache.Get(request.name) as Document;
        if (doc == null)
        {
            doc = new Document();
        }

        if (request.cellId == "*")
        {
            doc.Clear();
        }
        else
        {
            doc.Add(request.cellId, request.cellValue);
        }

        dataCache.Put(request.name, doc);
        response.status = "success";
    }
    catch (Exception ex)
    {
        response.status = "error";
        response.error = ex.ToString();
    }
    return Json(response);

}

The source code to WorldSheet can be downloaded here. When I have some more time, I plan to make the application more robust and do some refactoring and clean-up of the code and put it up on CodePlex -- but for now, I’m satisfied with these results for a one-day effort.