function _sheetnode_google_import_infer_valueformat in Sheetnode 7

Same name and namespace in other branches

1. 6 modules/sheetnode_google/sheetnode_google.import.inc \_sheetnode_google_import_infer_valueformat()
2. 7.2 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 332

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';
  }
  else {
    if (preg_match('/^-?[\\d.,]+%$/', $display)) {

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

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

          // Currency left
          $valueformat = $matches[1] . '#,##0.00';
        }
        else {
          if (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';
            }
          }
          else {
            if (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';
              }
            }
            else {
              if (preg_match('/^\\d{1,2}-\\w{3}-\\d{2,4}$/', $display)) {

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

                  // Date mmmm d, yyyy
                  $valueformat = 'mmmm d, yyyy';
                }
                else {
                  if (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';
                    }
                  }
                  else {
                    if (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];
}