function _sheetnode_phpexcel_import_cell in Sheetnode 7.2

Same name and namespace in other branches

1. 5 modules/sheetnode_phpexcel/sheetnode_phpexcel.import.inc \_sheetnode_phpexcel_import_cell()
2. 6 modules/sheetnode_phpexcel/sheetnode_phpexcel.import.inc \_sheetnode_phpexcel_import_cell()
3. 7 modules/sheetnode_phpexcel/sheetnode_phpexcel.import.inc \_sheetnode_phpexcel_import_cell()

1 call to _sheetnode_phpexcel_import_cell()

_sheetnode_phpexcel_import_do in modules/sheetnode_phpexcel/sheetnode_phpexcel.import.inc

File

modules/sheetnode_phpexcel/sheetnode_phpexcel.import.inc, line 244

Import sheetnodes through PHPExcel for xls or xlsx spreadsheets.

Code

function _sheetnode_phpexcel_import_cell($workbook, $sheet, $cell, &$sc, &$styles) {
  $c = array();

  // output cell
  // Position
  $c['pos'] = array(
    PHPExcel_Cell::columnIndexFromString($cell
      ->getColumn()),
    $cell
      ->getRow(),
  );

  // Value and type
  switch ($cell
    ->getDataType()) {
    case PHPExcel_Cell_DataType::TYPE_BOOL:
      $c['datavalue'] = (bool) $cell
        ->getValue();
      $c['datatype'] = 'v';
      $c['valuetype'] = 'n';
      break;
    case PHPExcel_Cell_DataType::TYPE_NUMERIC:
      $c['datavalue'] = floatval($cell
        ->getValue());
      $c['datatype'] = 'v';
      $c['valuetype'] = 'n';
      break;
    case PHPExcel_Cell_DataType::TYPE_STRING:
      $text = $cell
        ->getValue();
      if ($text instanceof PHPExcel_RichText) {
        $style = $sheet
          ->getStyle($cell
          ->getCoordinate());
        $defaultfont = $style
          ->getFont();
        $cellData = '';
        foreach ($text
          ->getRichTextElements() as $element) {
          $font = $element instanceof PHPExcel_RichText_Run ? $element
            ->getFont() : $defaultfont;
          $cellData .= '<span style="' . _sheetnode_phpexcel_assembleCSS(_sheetnode_phpexcel_createCSSStyleFont($font)) . '">';
          if ($font
            ->getSuperScript()) {
            $cellData .= '<sup>';
          }
          else {
            if ($font
              ->getSubScript()) {
              $cellData .= '<sub>';
            }
          }

          // Convert UTF8 data to PCDATA
          $cellText = $element
            ->getText();
          $cellData .= htmlspecialchars($cellText);
          if ($font
            ->getSuperScript()) {
            $cellData .= '</sup>';
          }
          else {
            if ($font
              ->getSubScript()) {
              $cellData .= '</sub>';
            }
          }
          $cellData .= '</span>';
        }
        $c['datavalue'] = $cellData;
        $c['valuetype'] = 'th';
        $c['datatype'] = 't';
      }
      else {
        if (empty($text)) {

          // empty text?
          // That's a bug in PHPExcel where a blank cell is implictly converted to a text cell.
          // Convert it back here by leaving the datatype and valuetype blank.
        }
        else {

          // it is plain text
          $c['datavalue'] = $text;
          $c['valuetype'] = 't';
          $c['datatype'] = 't';
        }
      }
      break;
    case PHPExcel_Cell_DataType::TYPE_FORMULA:
      $c['datatype'] = 'f';
      $c['formula'] = ltrim($cell
        ->getValue(), '=');
      try {
        $c['datavalue'] = $cell
          ->getCalculatedValue();
      } catch (Exception $e) {
        $c['datavalue'] = 0;
        watchdog('sheetnode_phpexcel', '!title: Error importing formula "!formula" at cell !coord.', array(
          '!title' => $sheet
            ->getTitle(),
          '!formula' => $c['formula'],
          '!coord' => $cell
            ->getCoordinate(),
        ), WATCHDOG_WARNING);
      }
      $c['valuetype'] = is_numeric($c['datavalue']) ? 'n' : 't';
      break;
    case PHPExcel_Cell_DataType::TYPE_ERROR:
      $c['errors'] = $cell
        ->getValue();
      break;
    case PHPExcel_Cell_DataType::TYPE_NULL:
      break;
  }

  // Comment
  $comment = $sheet
    ->getComment($cell
    ->getCoordinate());
  if (!empty($comment)) {
    $text = $comment
      ->getText()
      ->getPlainText();
    if (!empty($text)) {
      $c['comment'] = $comment
        ->getText()
        ->getPlainText();
    }
  }

  // Style
  $style = $sheet
    ->getStyle($cell
    ->getCoordinate());
  $hash = $style
    ->getHashCode();
  if (empty($styles[$hash])) {

    // Font
    $font = _sheetnode_phpexcel_get_font($style
      ->getFont());
    if ($font) {
      $styles[$hash]['font'] = _sheetnode_phpexcel_insert_hash($sc, 'font', $font);
    }

    // Color
    $color = _sheetnode_phpexcel_get_color($style
      ->getFont()
      ->getColor());
    if ($color) {
      $styles[$hash]['color'] = _sheetnode_phpexcel_insert_hash($sc, 'color', $color);
    }

    // Background color
    if ($style
      ->getFill()
      ->getFillType() != PHPExcel_Style_Fill::FILL_NONE) {
      $bgcolor = _sheetnode_phpexcel_get_color($style
        ->getFill()
        ->getStartColor());
      if ($bgcolor) {
        $styles[$hash]['bgcolor'] = _sheetnode_phpexcel_insert_hash($sc, 'color', $bgcolor);
      }
    }

    // Borders
    $borders = array(
      'bt' => 'getTop',
      'bl' => 'getLeft',
      'bb' => 'getBottom',
      'br' => 'getRight',
    );
    foreach ($borders as $bi => $method) {
      $border = _sheetnode_phpexcel_get_border($style
        ->getBorders()
        ->{$method}());
      if ($border) {
        $styles[$hash][$bi] = _sheetnode_phpexcel_insert_hash($sc, 'borderstyle', $border);
      }
    }

    // Cell format (horizontal alignment)
    $cellformat = _sheetnode_phpexcel_get_cellformat($style);
    if ($cellformat) {
      $styles[$hash]['cellformat'] = _sheetnode_phpexcel_insert_hash($sc, 'cellformat', $cellformat);
    }

    // Layout (padding + vertical alignment)
    $layout = _sheetnode_phpexcel_get_layout($style);
    if ($layout) {
      $styles[$hash]['layout'] = _sheetnode_phpexcel_insert_hash($sc, 'layout', $layout);
    }

    // Number format
    $ntvf = _sheetnode_phpexcel_get_valueformat($style
      ->getNumberFormat());
    if ($ntvf) {
      $styles[$hash]['nontextvalueformat'] = _sheetnode_phpexcel_insert_hash($sc, 'valueformat', $ntvf);
    }
  }
  $c += $styles[$hash];

  // merge the cached styles into the cell directly.
  $sc['cells'][$cell
    ->getCoordinate()] = $c;
}