function _sheetnode_google_import_infer_valueformat in Sheetnode 7.2

Same name and namespace in other branches

1. 6 modules/sheetnode_google/sheetnode_google.import.inc \_sheetnode_google_import_infer_valueformat()
2. 7 modules/sheetnode_google/sheetnode_google.import.inc \_sheetnode_google_import_infer_valueformat()

Helper function to infer a numeric format given a formatted number.

Possible formats: number, date, time, percentage.

1 call to _sheetnode_google_import_infer_valueformat()

_sheetnode_google_import_do in modules/sheetnode_google/sheetnode_google.import.inc

File

modules/sheetnode_google/sheetnode_google.import.inc, line 367

Extension to sheetnode for importing from Google sheets.

Code

function _sheetnode_google_import_infer_valueformat(&$sc, &$c, $value, $display) {
  if (preg_match('/^-?[\\d.]+$/', $display)) {

    // Regular number.
    return;
  }
  if (preg_match('/^-?[\\d.,]+$/', $display)) {

    // Formatted number.
    $valueformat = '#,##0.00';
  }
  elseif (preg_match('/^-?[\\d.,]+%$/', $display)) {

    // Percent.
    $valueformat = '0.00%';
  }
  elseif (preg_match('/^-?[\\d.,]+([^\\d.,]+)$/', $display, $matches)) {

    // Currency right.
    $valueformat = '#,##0.00' . $matches[1];
  }
  elseif (preg_match('/^-?([^\\d.,]+)[\\d.,]+$/', $display, $matches)) {

    // Currency left.
    $valueformat = $matches[1] . '#,##0.00';
  }
  elseif (preg_match('/^\\d{1,2}([-\\/\\s])\\d{1,2}\\1\\d{2,4}((?:\\s+\\d{2}:\\d{2}:\\d{2}){0,1})$/', $display, $matches)) {

    // Datetime mm-dd-yyyy hh:mm:ss.
    $valueformat = 'mm' . $matches[1] . 'dd' . $matches[1] . 'yyyy';
    if (@$matches[2]) {
      $valueformat .= ' hh:mm:ss';
    }
  }
  elseif (preg_match('/^\\d{2,4}([-\\/\\s])\\d{1,2}\\1\\d{1,2}((?:\\s+\\d{2}:\\d{2}:\\d{2}){0,1})$/', $display, $matches)) {

    // Datetime yyyy-mm-dd hh:mm:ss.
    $valueformat = 'yyyy' . $matches[1] . 'mm' . $matches[1] . 'dd';
    if (@$matches[2]) {
      $valueformat .= ' hh:mm:ss';
    }
  }
  elseif (preg_match('/^\\d{1,2}-\\w{3}-\\d{2,4}$/', $display)) {

    // Date d-mmm-yyyy.
    $valueformat = 'd-mmm-yyyy';
  }
  elseif (preg_match('/^\\w+\\s+\\d{1,2},\\s*\\d{2,4}$/', $display)) {

    // Date mmmm d, yyyy.
    $valueformat = 'mmmm d, yyyy';
  }
  elseif (preg_match('/^\\d{1,2}:\\d{1,2}(:\\d{1,2}){0,1}((?:\\s*\\w{1,2}){0,1})$/', $display, $matches)) {

    // Time.
    $valueformat = 'hh:mm';
    if (@$matches[1]) {
      $valueformat .= ':ss';
    }
    if (@$matches[2]) {
      $valueformat .= ' AM/PM';
    }
  }
  elseif (preg_match('/^-?[\\d.]+E[+-]?\\d+$/', $display)) {

    // Scientific.
    $valueformat = '0.00e+000';
  }
  else {
    return;
  }

  // Create the value format and attach it to the cell.
  if (!isset($sc['valueformathash'][$valueformat])) {
    $index = count(@$sc['valueformats']) + 1;
    $sc['valueformats'][$index] = $valueformat;
    $sc['valueformathash'][$valueformat] = $index;
  }
  $c['nontextvalueformat'] = $sc['valueformathash'][$valueformat];
}