Calculating certain columns

I've been hacking on Tablefield to (a) assign spreadsheet-like cell references, (b) mark specific cells as headers (readonly) and (c) support simple formulas (refer to the screenshot below). I've done this by hacking the tablefield_process() function to support the special default cell values and attach additional attributes, output javascript code, etc. I can make this available as a patch if there is interest.

I am interested. Can you make this patch available?

Sorry for the delay. Unfortunately making it available as a patch is problematic because I'm also apply a bunch of other patches to this module and it's not easy to separate out the code for this. In any case it would good to get more input regarding how best to augment TableField to have this capability; perhaps some kind of render hook.

In tablefield.module, I added the following. This only handles the simplest case: A1+B1+C2 for example.

function tablefield_parse_formula($formula) {
  $js = '';
  // convert B1 to Drupal.parseNumber($('.B1', t).val())
  // Need regex
  // strip spaces
  $parts = explode('+', strtr($formula, array(' ' => '')));
  if (count($parts)) {
    foreach ($parts as $key => $part) {
      $parts[$key] = 'Drupal.parseNumber($(\'.'. $part .'\', t).val())';
    }
    $js = implode('+', $parts);
  }

  return $js;
}

There's no out-of-range checking etc. at this point. What we really need is perhaps to eval in js for a limited set of operators.

The tablefield_process() function has been rewritten to output spreadsheet-style cell identifiers in the cell class (e.g. A1, B2 etc.) Also, I've introduced a notation of *header* to process the cell as th instead of td. If the render detects = it assumes a formula and outputs javascript.

/**
 * Process the tablefield
 */
function tablefield_process($element, $edit, $form_state, $form) {
  $delta = $element['#delta'];
  $field = $form['#field_info'][$element['#field_name']];

  if (isset($element['#value']['tablefield'])) {
    // A form was submitted
    $default_value = tablefield_rationalize_table($element['#value']['tablefield']);

    // Catch empty form sumissions for required tablefields
    if ($form_state['submitted'] && $element['#required'] && tablefield_content_is_empty($element['#value'], $field)) {
      form_set_error($element['#field_name'], t('@field is a required field.', array('@field' => $form[$element['#parents'][0]]['#title'])));
    }
  }
  elseif (isset($element['#default_value']['value'])) {
    // Default from database
    $default_value = tablefield_rationalize_table(unserialize($element['#default_value']['value']));
  }
  else {
    // Get the widget default value
    $default_count_cols = $field['widget']['default_value'][0]['tablefield']['count_cols'];
    $default_count_rows = $field['widget']['default_value'][0]['tablefield']['count_rows'];
  }

  $description = $element['#description'] ? $element['#description'] . ' ' : '';
  $description .= t('The first row will appear as the table header.');

  $element['tablefield'] = array(
    '#title' => $field['widget']['label'],
    '#description' => $description,
    '#attributes' => array('id' => 'node-tablefield-' . str_replace('_', '-', $element['#field_name']) .'-'. $delta, 'class' => 'node-tablefield'),
    '#type' => 'fieldset',
    '#tree' => TRUE,
    '#collapsible' => FALSE,
    '#field_name' => $element['#field_name'],
    '#type_name' => $element['#type_name'],
    '#delta' => $element['#delta'],
    '#columns' => $element['#columns'],
  );

  // Give the fieldset the appropriate class if it is required
  if ($element['#required']) {
    $element['tablefield']['#title'] .= ' <span class="form-required" title="' . t('This field is required') . '">*</span>';
  }

  if ($form['#id'] == 'content-field-edit-form') {
    $element['tablefield']['#description'] = t('The first row will appear as the table header. This form defines the table field defaults, but the number of rows/columns and content can be overridden on a per-node basis.');
  }

  // Determine how many rows/columns are saved in the data
  if (!empty($default_value)) {
    $count_rows = count($default_value);
    foreach ($default_value as $row) {
      $temp_count = count($row);
      if ($temp_count > $count_cols) {
        $count_cols = $temp_count; 
      }
    }
  }
  else {
    $count_cols = $default_count_cols;
    $count_rows = $default_count_rows;
  }

  // Override the number of rows/columns if the user rebuilds the form
  if (!empty($edit['tablefield']['count_cols'])) {
    $count_cols = $edit['tablefield']['count_cols'];
  }
  if (!empty($edit['tablefield']['count_rows'])) {
    $count_rows = $edit['tablefield']['count_rows'];
  }

  if ($field['enable_csv_import']) {
    // Allow the user to import a csv file
    $element['tablefield']['import'] = array(
      '#type' => 'fieldset',
      '#tree' => FALSE,
      '#title' => t('Import from CSV'),
      '#collapsible' => TRUE,
      '#collapsed' => TRUE,
    );
    $element['tablefield']['import']['tablefield_csv_' . $field['field_name'] . '_' . $delta] = array(
      '#title' => 'File upload',
      '#type' => 'file',
    );
    $element['tablefield']['import']['rebuild_' . $field['field_name'] . '_' . $delta] = array(
      '#type' => 'button',
      '#validate' => array(),
      '#limit_validation_errors' => array('title'),
      '#executes_submit_callback' => TRUE,
      '#validate' => array('tablefield_import_csv'),
      '#submit' => array('tablefield_rebuild_form', 'node_form_build_preview'),
      '#value' => 'Import File to: ' . $field['field_name'] . '-' . $delta,
      '#attributes' => array(
        'class' => array('tablefield-rebuild'),
        'id' => 'tablefield-import-button-' . $field['field_name'] . '-' . $delta,
      ),
    );
  }

  // Render the form table
  $js = '';
  $element['tablefield']['a_break'] = array(
    '#type' => 'markup',
    '#value' => '<table>',
  );
  for ($i = 0; $i < $count_rows; $i++) {
    $element['tablefield']['b_break' . $i] = array(
      '#type' => 'markup',
      '#value' => '<tr class="tablefield-row-'. $i .'">',
    );
    for ($ii = 0; $ii < $count_cols; $ii++) {
      $cell = 'td';
      $type = 'textfield';
      $attributes = array();
      $class = array('tablefield-row-'. $i .' tablefield-column-'. $ii);

      // Spreadsheet column.
      $col_ref = chr(65 + $ii); // 65 = A.
      $cell_class = array('column-'. $col_ref);
      // Spreadsheet row.
      $row_ref = ($i + 1);
      $cell_class = array('row-'. $row_ref);
      $cell_ref = $col_ref . $row_ref; // A1, B2 etc.
      $class[] = $cell_ref;

      // Handle special codes: *=bold, readonly, header.
      if ($form['#id'] != 'content-field-edit-form') {
        if (trim($default_value[$i][$ii], '*') != $default_value[$i][$ii]) {
          $attributes['readonly'] = 'readonly';
          $cell_class[] = 'th';
          $cell = 'th';
          //$type = 'markup';
          $default_value[$i][$ii] = trim($default_value[$i][$ii], '*');
        }
        // Formula.
        if (substr($default_value[$i][$ii], 0, 1) == '=') {
          $attributes['readonly'] = 'readonly';
          $cell_class[] = 'formula';
          $formula_js = tablefield_parse_formula(substr($default_value[$i][$ii], 1));
          $js .= '$(\'.'. $cell_ref .'\', t).val('. $formula_js .');' ."\n";
          $default_value[$i][$ii] = 0;
        }
      }

      $attributes['id'] = 'tablefield-' . str_replace('_', '-', $element['#field_name']) .'-'. $delta . '-cell-' . $i . '-' . $ii;
      $attributes['class'] = implode(' ', $class);
      $element['tablefield']['cell_' . $i . '_' . $ii] = array(
        '#type' => $type,
        '#size' => strlen($default_value[$i][$ii]) > 10 && substr($default_value[$i][$ii], 0, 1) != '=' ? 20 : 10, // If default value > 10, bump up field size.
        '#attributes' => $attributes,
        '#default_value' => (empty($field_value)) ? $default_value[$i][$ii] : $field_value,
        '#prefix' => count($cell_class) ? '<'. $cell .' class="'. implode(' ', $cell_class) .'">' : '<'. $cell .'>',
        '#suffix' => '</'. $cell .'>',
      );
    }
    $element['tablefield']['d_break' . $i] = array(
      '#type' => 'markup',
      '#value' => '</tr>',
    );
  }
  $element['tablefield']['t_break' . $i] = array(
    '#type' => 'markup',
    '#value' => '</table>',
  );

  // If we have javascript, inject it.
  if (!empty($js)) {
    //$table_id = $element['#field_name'] .'_'. $delta;
    $table_id = 'node-tablefield-' . str_replace('_', '-', $element['#field_name']) .'-'. $delta;
    $calc_id = $element['#field_name'] .'_'. $delta;
    $js = "Drupal.behaviors.$calc_id = function(context) {
  $('#$table_id input:not(.calc-processed)', context).addClass('calc-processed').each(function() {
      $(this).bind('change', function() {
        tablefield_calc_$calc_id();
      });
    }
  );
}\n
function tablefield_calc_$calc_id() {
      t = $('#$table_id'); // Table.
      ". $js ."
    }";
    drupal_add_js($js, 'inline');
  }

  if ($field['enable_column_change'] || $form['#id'] == 'content-field-edit-form') {
    $type = 'textfield';
  }
  else {
    $type = 'value';
  }

  // Allow the user to add more rows/columns
  $element['tablefield']['count_cols'] = array(
    '#title' => t('How many Columns'),
    '#type' => $type,
    '#size' => 5,
    '#prefix' => '<div class="clear-block">',
    '#suffix' => '</div>',
    //'#default_value' => $count_cols,
    '#value' => $count_cols,
  );

  if ($field['enable_row_change'] || $form['#id'] == 'content-field-edit-form') {
    $type = "textfield"; 
  }
  else {
    $type = 'value';
  }

  $element['tablefield']['count_rows'] = array(
    '#title' => t('How many Rows'),
    '#type' => $type,
    '#size' => 5,
    '#prefix' => '<div class="clear-block">',
    '#suffix' => '</div>',
    //'#default_value' => $count_rows,
    '#value' => $count_rows,
  );

  if ($field['enable_csv_import'] || $field['enable_row_change'] || $field['enable_column_change'] || $form['#id'] == 'content-field-edit-form') {
    $element['tablefield']['rebuild'] = array(
      '#type' => 'button',
      '#value' => t('Rebuild Table'),
      '#attributes' => array('class' => 'tablefield-rebuild'),
    );
  }
  if (!empty($field['cell_processing'])) {
    $filter_key = $element['#columns'][0];
    $format = isset($element['#value'][$filter_key]) ? $element['#value'][$filter_key] : FILTER_FORMAT_DEFAULT;
    $parents = array_merge($element['#parents'] , array($filter_key));
    $element[$filter_key] = filter_form($format, 1, $parents);
  }

  return $element;
}

This suits my current use case so I don't expect to do much more with it but it would be nice to have this capability included in the module, or for the module to provide some extension points to make this modification easy in a separate module.

I am using tablefield version 7.x-2.0-beta4.

I added the two code snippets you posted and I did not get any errors. :)

How do I use the code to add up a column of data? Ie a2+a3+a4
How do I call the function?

There looks like there might be an element or two missing in your code snippet in order to implement the function. Can you give me a rundown how you implemented to code?

Thanks,

spineless

Take a look at https://www.drupal.org/project/sheetnode. Out of scope for this module.