Porting Sheetnode to Drupal 7

About a month ago, I started porting Sheetnode to D7. The natives were getting restless on the issue queue, so I thought I would pacify them with some serious porting effort. I am glad to announce that the port was completed a few days ago: Sheetnode 7.x-1.0-beta1 is now available, a fully-functional port of the latest D6 version.

The porting process was surprisingly smooth. I'd been avoiding porting my modules to D7 because it felt like rewriting the same code all over again - and I hate rework. But during this month, I got to learn many aspects of the D7 API, and it's not all bad :-) Here are some snippets of my experience:

  • The Field API is actually cool. I liked that it carried forward the ideas of CCK pretty much verbatim, so porting all the Sheetnode CCK field code was relatively painless.
  • The Token API is a huge improvement over D6. Specifically, it is now possible to define dynamic tokens, i.e. tokens that are dependent upon the object instance at run-time. Sheetnode needs this to expose tokens for single spreadsheet cells, given a cell coordinate (e.g. B2). In D6, the Token module required that all tokens be defined ahead of time , so I had to use a hack to implement this feature. But in D7, all I needed to do was declare the cell token to be of type dynamic:
/**
 * Implements hook_token_info().
 */
function sheetnode_token_info() {
  $info['tokens']['sheet']['cell'] = array(
    'name' => t('Cell reference'),
    'description' => t('A cell reference such as B52, C79, etc.'),
    'dynamic' => TRUE,
  );
  // more stuff here...
  return $info;
}

/**
 * Implements hook_tokens().
 */
function sheetnode_tokens($type, $tokens, array $data = array(), array $options = array()) {
  $replacements = array();
  if ($type == 'sheet' && !empty($data['sheet'])) {
    foreach ($tokens as $name => $original) {
      list($token, $coord) = explode(':', $name, 2);
      if ($token == 'cell' && $cell = _sheetnode_get_cell($data['sheet'], $coord)) {
        $replacements[$original] = $cell->value;
      }
    }
    return $replacements;
  }
  • The Form API is also an improvement. AJAX support is finally sane :-) Attaching JS and CSS files to elements is a great idea.

  • The Database API is a disappointment. It added complexity but gave nothing in return: simple queries are more difficult to write, and in one instance I had to split my query into two because the new API does not support the UPDATE ... SELECT idiom. I am sure that the new API caused a lot of teeth grinding from module maintainers.

  • Because Sheetnode is JavaScript-heavy, I had to ensure that the SocialCalc engine and its Drupal integration script work correctly with the new Drupal themes. I spent a significant amount of time reworking the core SocialCalc engine to enhance its element positioning code. The outcome is a very flexible spreadsheet engine that can be embedded within absolute, relative or fixed containers and still behave correctly. Hint: element.getBoundingClientRect is magical. See for example the screenshot below of the template used in the Spreadsheet Views style plugin.

Spreadsheet template in Views admin UI

  • One of the more interesting challenges was to make SocialCalc compatible with the jQuery UI dialog element, which is used in the Views admin interface. The problem was that the dialog element was capturing key events and not passing them to the spreadsheet engine. Furthermore, it was handling the ESC key to close the dialog, whereas in SocialCalc ESC is used to cancel editing a single cell. The solution was to read the code of the jQuery UI library and undo some of its settings during initialization of the spreadsheet engine:
 // If we're in a jQuery UI dialog, disable closeOnEscape and unbind the keypress event that interferes with our keyboard handling.
  if ($('.ui-dialog-content').length) {
    $('.ui-dialog-content').dialog('option', 'closeOnEscape', false);
    $(document).unbind('keydown.dialog-overlay');
  }

All in all, I had fun porting Sheetnode to D7. But then again, maybe it's because my expectation was so low to start with :-)

AttachmentSize
Sheetview (Content) | d7.png136.21 KB

Comments

Hello,

I am trying to add sheetnode to my Drupal Commons but the custom content-type for sheetnode does not seem to work with the commons page. I duplicated the "posts" but changed it to "sheetnode" and also created the sheetnode view; however, still unable to create a new sheetnode via commons groups interface:

/** * Implements hook_commons_bw_group_widget(). */ function commons_sheetnode_commons_bw_group_widget() { return array( 'sheetnode' => array( 'title' => 'Sheetnode', 'type' => 'view', 'vid' => 'commons_bw_sheetnode', 'display' => 'page_1', 'weight' => 7, ), ); }

/** * Implements hook_views_pre_render(). */ function commons_sheetnode_views_pre_render(&$view) { if ($view->name == 'commons_bw_sheetnode') { $group_id = $view->args[0]; $view->attachment_before = l('Create a spreadsheet', 'node/add/sheetnode', array('query' => array('og_group_ref' => $group_id))); } }

/** * Implements hook_commons_entity_integration. */ function commons_sheetnode_commons_entity_integration() { return array( 'node' => array( 'sheetnode' => array( ), ), ); }

The goal is to have the "create new sheetnode" appear in the groups similiar to wiki/documents/post/question

Any tips?

Fantastic module. I'm having trouble with the importing modules, but it seems to be related to a different module (book_helper of all things). Nice work on the port, and a very nice contribution to Drupal. Thanks all to Dan Bricklan, Visicalc ruled the day!

Sheetnode definitely has potential when it comes to provide spreadsheet view to the users. However, porting sheetnode in Drupal seems like a huge task and non-developers might not be able to master the trick. In such cases, I would recommend using GroupDocs Viewer Module for your Drupal sites. It is easy to integrate and provides compatibility to a wide range of file formats. No need to write code or install software for adding this module! Just get the embed code, copy and paste it into the desired webpage and your docs, spreadsheets and even PDFs are visible within seconds. For more information, read this blog:

http://groupdocs.com/blog/groupdocs-viewer/archive/2012/11/23/announcing...

Inserting your own little advertising at my expense here? You could say thanks :-)

Hi, I like the potential of sheetnode, but instructions on how to use it?? Before adding anymore 'features' would it be possible to create a usuable set of instructions/tutorials, seems to be a product only for Advanced Drupal users.

I would love to have instructions and tutorials but unfortunately I suck at technical writing. I would welcome community help to create documentation.

"The natives" je ....

Thanks a lot for the amazing module! I wanted to use it sometime back but it wouldn't do exactly what I wanted. Will definitely check it out and give it a go!

Thanks once again, keep up the good work.

Thanks! If you have feature requests, it would be great to add them to the module's issue queue.