Contents


Use Django and jQuery to create a spreadsheet application

Comments

This article describes how to implement a simple web-based spreadsheet using jQuery, jQuery plug-ins, and Django. It's by no means complete or an attempt to compete with Google Docs, but rather a demonstration of how easy it is to create an "office"-style web application these days, given the large number of jQuery plug-ins and tools available. I use a SQLite/Python/Django stack for the back end, but you can port to another framework such as Ruby on Rails with minimum effort.

Project dependencies

This article uses the following Python technologies (see Related topics for links):

  • Python 2.5+
  • simplejson
  • Django 1.2.3

Note: Python 2.5 does not include simplejson, but it is included with later Python versions.

If you don't want to go through the hassle of getting all of the jQuery dependencies, feel free to download the complete demo using the link in Related topics. On the front end, you need the following technologies:

  • jQuery 1.4.3
  • jQuery UI 1.8.5
  • SlickGrid
  • jQuery JSON

All of these third-party libraries handle most of the workload for you, particularly SlickGrid. I chose to use SlickGrid because of its ability to highlight/select groups of cells—in case I want to improve on the cells' math operation and parsing abilities. It also lets you load data while scrolling. Several other excellent jQuery grids are also available, including Flexigrid, jQuery Grid, jqGridView, and Ingrid. Also, the jQuery project has announced plans for an official jQuery Grid plug-in.

Spreadsheet specification

Each spreadsheet contains a single workbook, with each workbook containing one or more data sheets. Each cell in the sheet should perform arithmetic operations when the first character entered is the equal sign (=). Otherwise, the text entered should remain as is. The data is loaded into a JSON object, sent to the back end asynchronously, and saved to a database. The spreadsheet will handle Open, New, and Save operations, and the name of the workbook is displayed in an editable text box at the top.

Clicking Open opens a jQuery UI window that displays the existing workbooks in the database. After choosing a workbook, the stored JSON data is retrieved using Asynchronous JavaScript and XML (Ajax) and rendered to the grid. Saving asynchronously sends the grid data in JSON format to the back end for storage. The New operation erases any references and reloads a clean workbook.

Finally, the sheets for the workbook are divided into distinct jQuery UI tabs. The tabs are displayed at the bottom, like any other spreadsheet, and are added dynamically by clicking a button at the bottom.

Project structure

You place all your CSS/JavaScript/images under a resources folder at the top level of the project. The Django application will contain a single template called index.html, which is nothing more than a bit of markup, as you want to keep the HTML semantic and the JavaScript code nonintrusive. Component creation, such as the grid, is dynamically done. The spreadsheet definition is included in a file called spreadsheet.js.

Creating the Django back end

First, create the Django project by issuing the command:

django-admin startproject spreadsheet

Then, Cd into the newly created project to create an application by calling:

django-admin startapp spreadsheet_app

This article uses SQLite3 to avoid getting into extra database work, but feel free to use whatever relational database system (RDBS) you prefer. Modify the settings.py file to use the code in Listing 1.

Listing 1. The Django settings.py file
import os
APPLICATION_DIR = os.path.dirname( globals()[ '__file__' ] )

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.sqlite3', 
        'NAME': 'db',                      
        'USER': '',                      
        'PASSWORD': '',                 
        'HOST': '',                    
        'PORT': '',                   
    }
}

MEDIA_ROOT = os.path.join( APPLICATION_DIR, 'resources' )
MEDIA_URL = 'http://localhost:8000/resources/'

ROOT_URLCONF = 'spreadsheet.urls'

TEMPLATE_DIRS = (
    os.path.join( APPLICATION_DIR, 'templates' ),
)

INSTALLED_APPS = (
    'django.contrib.auth',
    'django.contrib.contenttypes',
    'django.contrib.sessions',
    'django.contrib.sites',
    'django.contrib.messages',
    'spreadsheet_app',
)

You needn't modify any other variables in the settings.py file. Now, configure the URL mappings. In this case, only two mappings are necessary: one for the statically served files and another pointing to the index. Listing 2 shows the code.

Listing 2. The urls.py file
from django.conf.urls.defaults import *
from django.conf import settings
import spreadsheet.spreadsheet_app.views as views

urlpatterns = patterns('',
    ( r'^resources/(?P<path>.*)$',
      'django.views.static.serve',
      { 'document_root': settings.MEDIA_ROOT } ),
    url( r'^spreadsheet_app/', views.index, name="index" ) ,
)

Create a directory called resources at the top level of the project and subdirectories called css, js, and images. The downloaded dependencies, such as SlickGrid, are stored here as well as the custom JavaScript code for your application. If you're feeling lazy, just download the demo and copy the resources directory.

Next, create your domain model (see Listing 3). This model will only contain three fields: workbook_name, sheet_name, and data. The Django Object-Relational Mapper (ORM) automatically creates a key field called id.

Listing 3. The models.py file
# file: spreadsheet_app/models.py

from django.db import models

class Workbooks(models.Model):
    workbook_name = models.CharField(max_length=30)
    sheet_name = models.CharField(max_length=30)
    data = models.TextField()

As you can tell, you're not using the full power of Django. You just want it to handle the back-end work while leaving the heavy lifting to the jQuery front end.

Finally, create the index view. The index view handles your create/read/update operations for the spreadsheets. Without getting into all the details of the index view, Listing 4 shows the basics of how you'll handle the incoming requests.

Listing 4. The view
# file:spreadsheet_app/views.py
from django.template.context import RequestContext
from spreadsheet.spreadsheet_app.models import Workbooks
import simplejson as json
from django.http import HttpResponse

def index(request):

    app_action = request.POST.get('app_action')
    posted_data = request.POST.get('json_data')

    if posted_data is not None and app_action == 'save':
        ...

    elif app_action == 'get_sheets':
        ...
  
    elif app_action == 'list':
        ...

After the imports, you see that the index view accepts a request object that contains the post data sent from the client. You get two parameters: app_action and posted_data. The app_action parameter tells you what action the client is requesting, such as creating a new workbook. The posted_data parameter is the JSON data sent from the client for a single sheet. The different actions are handled by a simple if statement; you can save a sheet, get all sheets for a workbook, or get a list of the workbooks in the database.

You'll return to the index view later to go through the details. For now, add a directory called templates in the spreadsheets_app directory. In the templates subdirectory, add a file called index.html, the only template in the project. Listing 5 shows the code.

Listing 5. The index template
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" 
    "http://www.w3.org/TR/html4/strict.dtd">
<html>
  <head>
     <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"i>
     <title>Overly Simple Spreadsheet</title>
     <link rel="stylesheet" href="{{MEDIA_URL}}css/smoothness/jquery-ui-1.8.5.custom.css" 
           type="text/css" media="screen" charset="utf-8" />
     <link rel="stylesheet" href="{{MEDIA_URL}}css/slick.grid.css" type="text/css" 
           media="screen" charset="utf-8" />
     <link rel="stylesheet" href="{{MEDIA_URL}}css/examples.css" type="text/css" 
           media="screen" charset="utf-8" />
     <link rel="stylesheet" href="{{MEDIA_URL}}css/spreadsheet.css" type="text/css" 
           media="screen" charset="utf-8" />

     <script type="text/javascript" src="{{MEDIA_URL}}js/jquery-1.4.3.min.js"></script>
     <script type="text/javascript" src="{{MEDIA_URL}}js/jquery.json.js"></script>
     <script type="text/javascript" src="{{MEDIA_URL}}js/jquery-ui-1.8.5.custom.min.js">
         </script>
     <script type="text/javascript" src="{{MEDIA_URL}}js/jquery.event.drag-2.0.min.js">
         </script>
     <script type="text/javascript" src="{{MEDIA_URL}}js/ui/jquery.ui.tabs.js"></script>
     <script type="text/javascript" src="{{MEDIA_URL}}js/slick.editors.js"></script>
     <script type="text/javascript" src="{{MEDIA_URL}}js/slick.grid.js"></script>
     <script type="text/javascript" src="{{MEDIA_URL}}js/spreadsheet.js"></script>

  </headi>
  <body>
  </body>
</html>

As you can see, there is no control logic or styling in the HTML—just markup. As a matter a fact, there aren't even any elements in the body. It's all generated by the JavaScript code on the fly. The idea is to add and delete elements with method calls.

A bird's-eye view of spreadsheet.js

Upon loading, the spreadsheet renders the UI and loads a single tab. Listing 6 shows the code for render_ui.

Listing 6. The render_ui method found in spreadsheet.js
function render_ui(){
    insert_menu_markup();
    insert_grid_markup();
    make_grid_component();
    add_newtab_button();
    insert_open_dialog_markup();
    make_open_dialog();
}

Let's take each method's modus operandi, starting with insert_menu_markup. This method just adds the HTML code for the top menu, as seen in Listing 7. The menu consists of three buttons—new, open, and save—and a text field for displaying and entering the workbook name. You use the jQuery prepend to make sure that when the markup is added, it is inserted as the first element in the body.

Listing 7. The method for generating the menu markup
// OK, it's not really a menu...yet :-)
function insert_menu_markup(){
    $("body").prepend(
       '<input type="text" id="workbook_name" name="workbook_name" value="">');
    $("body").prepend('<input id="save" type="button" value="save"/>');
    $("body").prepend('<input id="open" type="button" value="open"/>');
    $("body").prepend('<input id="new" type="button" value="new"/>');
}

Figure 1 shows the very simple menu, which is really just some buttons and a text box.

Figure 1. The menu
Spreadsheet with 3 tabs: new, open,         and save, and a name of invoices_2010.
Spreadsheet with 3 tabs: new, open, and save, and a name of invoices_2010.

The insert_grid_markup method is similar to insert_menu_markup, except that this time you use the append method. jQuery UI requires a list inside a <div> to generate the tab widget:

function insert_grid_markup(){
    var workbook_widget = '<div id="tabs" class="tabs-bottom"><ul><li></li></ul></div>';  
    $('body').append(workbook_widget);
}

Now, make the tabs operational by calling the method make_grid_component. Listing 8 shows the code.

Listing 8. Changing the grid div into a tab
function make_grid_component(){
    $("#tabs").tabs();
    $(".tabs-bottom .ui-tabs-nav, .tabs-bottom .ui-tabs_nav > *")
    .removeClass("ui-corner-all ui-corner-top")
    .addClass("ui-corner-bottom");
}

You use the jQuery id selector to get a reference to the tabs <div>, and then call the tabs() method to convert the <div> to a tab widget. The default CSS class ui-corner-top is removed, then the ui-corner-bottom class is added so that the tabs appear at the bottom, as Figure 2 shows.

Figure 2. The workbook tabs
Three tabs shown with names Sheet 0,         Sheet 1, Sheet 2.

This component is the container for all the data grids. Now, add a button under the tab components that will dynamically add a tab each time it is clicked. You do this with the add_newtab_button method:

function add_newtab_button(){
    $('body').append('<input id="new_tab_button" type="button" value="+"/>');
}

The last visual component to create is the Open window, which you create by calling the method insert_open_dialog_markup shown in Listing 9. Like the other insert markup methods, this one creates a string containing the markup information and appends it to the body.

Listing 9. A method for generating the dialog markup
function insert_open_dialog_markup(){
    var dlg = '<div id="dialog_form" title="Open">' +
    '<div id="dialog_form" title="Open">' +
    '<p>Select an archive.</p><form>'+
    '<select id="workbook_list" name="workbook_list">' +
    '</select></form></div>';
    $("body").append(dlg);
}

Now that the markup for the window is there, you add its functionality with the make_open_dialog method shown in Listing 10—the last method in render_ui. By calling the .dialog() method and passing it the parameter autoOpen:false, the form won't be displayed on the web page until it's explicitly opened. The dialog form contains a select list that contains the list of workbook names when loaded.

Listing 10. Making the Open window functional
function make_open_dialog(){
    $('#dialog_form').dialog({
        autoOpen: false,
        modal: true,
        buttons: {
            "OK":function(){
                selected_wb = $('option:selected').attr('value');
                $(this).dialog('close');

                // remove grid, existing forms, and recreate
                $('body').html('');
                render_ui();

                // load grids and create forms with invisible inputs
                load_sheets(selected_wb);

                // place workbook name in text field
                $('#workbook_name').val(selected_wb);
            },
            "Cancel":function(){
                $(this).dialog('close');
            }
        }
    });
}

You again use the jQuery selectors to get a handle on the dialog_form and call the dialog() method, which converts the html element into a jQuery window. The Open window is modal and does not open upon page load. It also contains two buttons—OK and Cancel. The latter does nothing more than close the window. The OK function finds the selected item in the select list and gets its value. It then closes the window, removes any child elements in the body, re-renders the GUI components, and loads the sheets (or, if you prefer the terminology, SlickGrids). As mentioned earlier, because the generation of the markup is all in methods, it's now a trivial matter to add and remove these widget components.

After rendering the UI foundation, you now code a method for opening a new tab with a grid. Continuing with the top-bottom approach, Listing 11 shows the openTab method, which is a critical piece of functionality in this application.

Note: Each sheet in the application contains an ID that follows a simple convention: tabs_ followed by the tab number in the sheet.

Listing 11. A method for adding a new tab to the workbook
function openTab(sheet_id) {
  numberOfTabs = $("#tabs").tabs("length");
  tab_name = "tabs_" + numberOfTabs;

  $("#tabs").tabs("add","#" + tab_name,"Sheet " + numberOfTabs, numberOfTabs);
  $("#" + tab_name).css("display","block");
  $("#tabs").tabs("select",numberOfTabs);

  $('#'+tab_name ).css('height','80%');
  $('#'+tab_name ).css('width','95%');
  $('#'+tab_name ).css('float','left');
  add_grid(tab_name, numberOfTabs);

  // add form for saving this tabs data
  if(!sheet_id){
   $('body').append(
   '<form method="post" action="?" id="'+tab_name +'_form" name="'+tab_name+'_form">'+
   '<input type="hidden" id="data'+numberOfTabs+'" name="data'+numberOfTabs+'" value="">'+
   '<input type="hidden" id="sheet_id" name="sheet_id" value="">' +
   '</form>');
  } else {
   $('body').append(
   '<form method="post" action="?" id="'+tab_name +'_form" name="'+tab_name+'_form">' +
  '<input type="hidden" id="data'+numberOfTabs +'" name="data'+numberOfTabs+'" value="">'+
   '<input type="hidden" id="sheet_id" name="sheet_id" value="'+sheet_id+'">' +
   '</form>');
  }
}

If you're interested, an elegant way to code the markup strings is to use some kind of JavaScript templating instead of concatenating the strings together. The unique sheet ID is saved to the hidden input element. You also add a hidden element for saving the JSON data. This hidden input follows a simple naming convention—data followed by the tab number. Another important point to note is that the newly added tab has its CSS attributes modified before adding the actual SlickGrid. If you don't do that, then the grid will render incorrectly.

The openTab method calls the add_grid method, which does the actual instantiation of the SlickGrid objects. Listing 12 shows the heavy lifting of the application. You create two JavaScript objects—workbook and grid_references. The Workbook object contains a reference to the current workbook object, while grid_references contains references to each SlickGrid object. The add_grid method accepts two parameters: the grid name and the grid number.

In the column definitions, you want to have 16 columns as a default—a to p—using the TextCellEditor provided in one of the SlickGrid examples. After the column definition, you provide the parameter definition to SlickGrid. The cells are editable, re-sizable, and selectable. It's important to verify that the asyncEditorLoading option is set to True, which lets you implement Ajax operations on the grid.

Listing 12. Adding the SlickGrids to your application
var workbook = {};
var grid_references = {};

function add_grid(grid_name, gridNumber){
    var grid;
    var current_cell = null;

    // column definitions
    var columns = [
         {id:"row", name:"#", field:"num", cssClass:"cell-selection", width:40, 
         cannotTriggerInsert:true, resizable:false, unselectable:true },
         {id:"a", name:"a", field:"a", width:70, cssClass:"cell-title", 
              editor:TextCellEditor},
         {id:"b", name:"b", field:"b", width:70, cssClass:"cell-title", 
              editor:TextCellEditor},
         {id:"c", name:"c", field:"c", width:70, cssClass:"cell-title", 
              editor:TextCellEditor},
         {id:"d", name:"d", field:"d", width:70, cssClass:"cell-title", 
              editor:TextCellEditor},
         {id:"e", name:"e", field:"e", width:70, cssClass:"cell-title", 
              editor:TextCellEditor},
         {id:"f", name:"f", field:"f", width:70, cssClass:"cell-title", 
              editor:TextCellEditor},
         {id:"g", name:"g", field:"g", width:70, cssClass:"cell-title", 
              editor:TextCellEditor},
         {id:"h", name:"h", field:"h", width:70, cssClass:"cell-title", 
              editor:TextCellEditor},
         {id:"i", name:"i", field:"i", width:70, cssClass:"cell-title", 
              editor:TextCellEditor},
         {id:"j", name:"j", field:"j", width:70, cssClass:"cell-title", 
              editor:TextCellEditor},
         {id:"k", name:"k", field:"k", width:70, cssClass:"cell-title", 
              editor:TextCellEditor},
         {id:"l", name:"l", field:"l", width:70, cssClass:"cell-title", 
              editor:TextCellEditor},
         {id:"m", name:"m", field:"m", width:70, cssClass:"cell-title", 
              editor:TextCellEditor},
         {id:"n", name:"n", field:"n", width:70, cssClass:"cell-title", 
              editor:TextCellEditor},
         {id:"o", name:"o", field:"o", width:70, cssClass:"cell-title", 
              editor:TextCellEditor},
         {id:"p", name:"p", field:"p", width:70, cssClass:"cell-title", 
              editor:TextCellEditor},
    ];

    var options = {
          editable: true,
          autoEdit: true,
          enableAddRow: true,
          enableCellNavigation: true,
          enableCellRangeSelection : true,
          asyncEditorLoading: true,
          multiSelect: true,
          leaveSpaceForNewRows : true,
          rerenderOnResize : true
    };

    eval("var data" + gridNumber + " = [];");
    workbook["data" + gridNumber] = [];
    for( var i=0; i < 100 ; i++ ){
        var d = (workbook["data"+gridNumber][i] = {});
        d["num"] = i;
        d["value"] = "";
    }

    grid = new Slick.Grid($("#"+grid_name),workbook["data"+gridNumber], columns, options);
...

You use the eval statement, in a somewhat "hackish" way, to dynamically create a variable name. The data variable is then loaded with empty string data, and a SlickGrid instance is created.

Now, add attach to the grid to events, as shown in Listing 13. When the onCurrentCellChanged event occurs, it grabs the grid data and updates the cell content. The onBeforeCellEditorDestroy event is called before cell editing is finished. When triggered, you get the cell data as before, but this time, you determine whether the first character is an equals sign. If so, use the JavaScript eval method to evaluate the expression entered.

Warning: Do NOT use this code in a production environment. It will open your system to all kinds of nasty injection attacks. Always sanitize your data.

Finally, the reference to the grid is saved for use in other methods.

Listing 13. Adding event handling to the grids
// file: resources/js/spreadsheet.js continued
    // Events
    grid.onCurrentCellChanged = function(){
        d = grid.getData();
        row  = grid.getCurrentCell().row;
        cell = grid.getCurrentCell().cell;
        this_cell_data = d[row][grid.getColumns()[cell].field];
    };

    grid.onBeforeCellEditorDestroy = function(){
        d = grid.getData();
        row  = grid.getCurrentCell().row;
        cell = grid.getCurrentCell().cell;
        this_cell_data = d[row][grid.getColumns()[cell].field];

        if(this_cell_data && this_cell_data[0] === "="){
            // evaluate JavaScript expression, don't use
            // in production!!!!
            eval("var result = " + this_cell_data.substring(1));
            d[row][grid.getColumns()[cell].field] = result;
        }
    };
    grid_references[grid_name] = grid;
};

Going back to the specification, your spreadsheet must create a new workbook as well as save and open existing workbooks. Let's implement the new workbook function, the simplest of the three tasks. Here, all you need to do is destroy the UI and any references, and then redraw, as shown in Listing 14.

Listing 14. Creating a new workbook
$('#new').live('click', function(){
    // delete any existing references
    workbook = {};
    grid_references = {};

    // remove grid, existing forms, and recreate
    $('body').html('');

    // recreate
    render_ui();
    openTab();
});

The "save" function is a bit more complicated. You use a jQuery selector to get each element that has a name attribute beginning with data, and then use the each method to loop through the result set. The most critical thing to notice is that the grid data is encoded into a JSON format using the jquery.json plug-in for sending "over the wire." You use the $.post method to send the data asynchronously. The parameters passed to the index view are the action to perform (in this case, save, as Listing 15 shows), the unique sheet ID, the workbook name, and the grid data in a JSON format.

Listing 15. Saving the grid data
$('#save').live('click',function(){
    // Do a foreach on all the grids. The ^= operator gets all
    // the inputs with a name attribute that begins with data
    $("[name^='data']").each(function(index, value){
        var data_index = "data"+index;
        var sheet_id = $('#tabs_'+index+'_form').find('#sheet_id').val();
        if(sheet_id !== ''){
          sheet_id = eval(sheet_id);
        }

        // convenience variable for readability
        var data2post  = $.JSON.encode(workbook[data_index]);
        $("#"+data_index).val(data2post);

        $.post( '{% url index %}', {'app_action':'save', 'sheet_id': sheet_id,
                'workbook_name':$('#workbook_name').val(),
                'sheet':data_index, 'json_data':data2post});
    });
});

If you recall, opening requires the method load_sheets. Add that method now (see Listing 16). This method must call the back end, requesting the sheets with all their data for the workbook passed into the method. It then loads the data into the corresponding SlickGrid object. Notice that before inserting the data into the objects, you must de-serialize the JSON data using the decode method. The grid is then rendered.

Listing 16. Loading data into the grids
function load_sheets(workbook_name){
    $('#workbook_list').load('{% url index %}', 
        {'app_action':'get_sheets','workbook_name':workbook_name}, 
        function(sheets, resp, t){
        sheets = $.JSON.decode(sheets);

        workbook = {}; // reset
        grid_references = {};
        $.each(sheets, function(index, value){

            // add to workbook object
            var sheet_id = value["sheet_id"];
            openTab(sheet_id);

            // By calling eval, we translate value from
            // a string to a JavaScript object
            workbook[index] = eval(value["data"]);

            // insert data into hidden
            $("#data"+index).attr('value', workbook[index]);
            grid_references["tabs_"+index].setData(workbook[index]);
            grid_references["tabs_"+index].render();

        });
    });
}

Last, but not least, you implement the open function. Again, make an asynchronous call, this time sending the list action, and then de-serialize the data sent in JSON once more. Then, update the select list with the name of all the workbooks in the database. The dialog form is then opened. Listing 17 shows the code.

Listing 17. Opening an existing workbook
$('#open').live('click',function(){
    // load is used for doing asynchronous loading of data
    $('#workbook_list').load('{% url index %}', {'app_action':'list'}, 
        function(workbooks,success){
        workbooks = $.JSON.decode(workbooks);
        $.each(workbooks, function(index, value){
            $('#workbook_list').append(
              '<option value="'+ value +'">'+value +'*lt;/option>');
        });
    });

    $('#dialog_form').dialog('open');
});

The index view revisited

Now that you've coded the client-side posting, you can finish the index view. Listing 18 shows the complete index view, except the imports.

To (de)serialize the JSON code, use the simplejson module and the command dumps to serialize it as well as loads for the reverse. For the Save action, if an ID is given for the individual sheet, the sheet is updated. Otherwise, a new sheet is created.

In contrast, the get sheets action creates a JSON object with all the sheets for a particular workbook. It retrieves all the sheets (QuerySet objects) for a workbook using the filter() command. This is equivalent to select * from spreadsheet_app_workbooks, where workbook_name = wb_name". After the sets are retrieved, you convert them to JSON format again and send it back to the client.

List also uses the Django ORM, but this time with the values() method. Here, you're telling Django, "get the workbook_name column." By calling the distinct method on the QuerySet object, you're explicitly saying that you don't want any duplicates. You again use list comprehension to create a list from the results. For both get_sheets and list, you must return an HttpResponse object for jQuery to process your Ajax response.

Listing 18. The view completed
def index(request):
    template = 'index.html'

    app_action = request.POST.get('app_action')
    posted_data = request.POST.get('json_data')

    if posted_data is not None and app_action == 'save':
        this_sheet = request.POST.get("sheet")
        this_workbook = request.POST.get("workbook_name")
        sheet_id = request.POST.get("sheet_id")

        posted_data = json.dumps(posted_data)

        if(sheet_id):
            wb = Workbooks(id=sheet_id, workbook_name=this_workbook, 
                   sheet_name=this_sheet, data=posted_data)
        else:
            wb = Workbooks(workbook_name=this_workbook, 
                   sheet_name=this_sheet, data=posted_data)
        wb.save()

    elif app_action == 'get_sheets':
        wb_name = request.POST.get('workbook_name')
        sheets = Workbooks.objects.filter(workbook_name=wb_name)

        # use list comprehension to create python list which is like a JSON object
        sheets = [{ "sheet_id":i.id, "workbook_name": i.workbook_name.encode("utf-8"),
                    "sheet_name": i.sheet_name.encode("utf-8"), 
                    "data": json.loads(i.data.encode("utf-8"))} for i in sheets ]

        # dumps -> serialize to JSON
        sheets = json.dumps(sheets)

        return HttpResponse( sheets, mimetype='application/javascript' )

    elif app_action == 'list':
        workbooks = Workbooks.objects.values('workbook_name').distinct()

        # use list comprehension to make a list of just the work books names
        workbooks = [ i['workbook_name'] for i in workbooks ]

        # encode into json format before sending to page
        workbooks = json.dumps(workbooks)

        # We need to return an HttpResponse object in order to complete
        # the ajax call
        return HttpResponse( workbooks, mimetype='application/javascript' )

    return render_to_response(template, {},
           context_instance = RequestContext( request ))

Finalizing the grid

Now that you've defined all the JavaScript methods, you can generate the application with two method calls, as shown in Listing 19.

Listing 19. Code to execute when page is loaded
$(document).ready(function(){
    render_ui();
    openTab();
});

When the page finishes loading, the UI is rendered, and a new tab is inserted into the workbook.

You can now view your spreadsheet application in all its (lack of) glory at the command line by running python manage syncdb to create the database. When it's finished, execute the command python manage.py runserver and navigate to http://localhost:8000/spreadsheet_app. You should see the final version, as Figure 3 demonstrates.

Figure 3. The full spreadsheet application
Spreadsheet with 7 columns, 14         lines, and client information filled in.
Spreadsheet with 7 columns, 14 lines, and client information filled in.

There are so many things your could add to this project, but I had to apply some self-restraint. For example, you could include:

  • Plots
  • A safer, more functional parser for formulas entered into each cell
  • Exporting to other formats, such as Microsoft® Office Excel
  • A real menu using an additional jQuery plug-in

Conclusion

Although this web application is not production ready, it demonstrates how you can combine several techniques. Using unobtrusive JavaScript, semantic HTML, JSON objects to pass data back and forth from the server asynchronously, and—most notably—not reinventing the wheel when there are so many available jQuery plug-ins makes your task much easier.


Downloadable resources


Related topics


Comments

Sign in or register to add and subscribe to comments.

static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Web development
ArticleID=631789
ArticleTitle=Use Django and jQuery to create a spreadsheet application
publish-date=03152011