function _sheetnode_phpexcel_export_sheet in Sheetnode 7

Same name and namespace in other branches

1. 6 modules/sheetnode_phpexcel/sheetnode_phpexcel.export.inc \_sheetnode_phpexcel_export_sheet()
2. 7.2 modules/sheetnode_phpexcel/sheetnode_phpexcel.export.inc \_sheetnode_phpexcel_export_sheet()

Helper function to export a single spreadsheet.

2 calls to _sheetnode_phpexcel_export_sheet()

_sheetnode_phpexcel_batch_export_sheet in modules/sheetnode_phpexcel/sheetnode_phpexcel.export.inc

Batch API callback to export a single sheet to PHPExcel.

_sheetnode_phpexcel_export_reference in modules/sheetnode_phpexcel/sheetnode_phpexcel.export.inc

Helper to export referenced sheet.

File

modules/sheetnode_phpexcel/sheetnode_phpexcel.export.inc, line 122

Export sheetnodes through PHPExcel to xls or xlsx spreadsheets.

Code

function _sheetnode_phpexcel_export_sheet($worksheet, $title, $socialcalc) {
  module_load_include('inc', 'sheetnode', 'socialcalc');
  @set_time_limit(0);
  $sc = $socialcalc['sheet'];
  $workbook = $worksheet
    ->getParent();

  // Title
  try {
    $worksheet
      ->SetTitle($title);
  } catch (Exception $e) {
    watchdog('sheetnode_phpexcel', 'Error setting worksheet title to "!title": @error', array(
      '!title' => $title,
      '@error' => $e
        ->getMessage(),
    ), WATCHDOG_WARNING);
  }

  // Names
  if (!empty($sc['names'])) {
    foreach ($sc['names'] as $name => $info) {
      $definition = ltrim($info['definition'], '=');
      @(list($sheetname, $reference) = explode('!', $definition, 2));
      if (empty($reference)) {
        $reference = $definition;
        $external = $worksheet;
      }
      else {
        $sheetname = trim($sheetname, '"\'');
        list($value, $title2) = sheetnode_find_sheet($sheetname);
        if ($value) {
          $external = $workbook
            ->getSheetByName($title2);
          if (!$external) {
            $external = $workbook
              ->createSheet($workbook
              ->getSheetCount());
            $socialcalc2 = socialcalc_parse($value);
            _sheetnode_phpexcel_export_sheet($external, $title2, $socialcalc2);
          }
        }
      }

      // TODO: PHPExcel does not support non-range names.
      try {
        $range = PHPExcel_Cell::rangeBoundaries($reference);
      } catch (Exception $e) {
        watchdog('sheetnode_phpexcel', 'Could not export name %name with value %definition because non-range names are not yet supported.', array(
          '%name' => $name,
          '%definition' => $definition,
        ));
        continue;
      }
      if (!empty($external)) {
        $workbook
          ->addNamedRange(new PHPExcel_NamedRange($name, $external, $reference));
      }
      else {
        watchdog('sheetnode_phpexcel', 'Could not export name %name with value %definition because %sheetname was not found.', array(
          '%name' => $name,
          '%definition' => $definition,
          '%sheetname' => $sheetname,
        ));
      }
    }
  }

  // Cells
  if ($sc['cells']) {
    foreach ($sc['cells'] as $coord => $c) {

      // Get cached style if any.
      static $styles = array();
      $hash = _sheetnode_phpexcel_export_get_style_hash($c);
      if (empty($styles[$hash])) {
        $styles[$hash] = $style = new PHPExcel_Style();
      }
      else {
        $style = NULL;

        // mark that we don't need a new style.
      }

      // Value and format
      _sheetnode_phpexcel_export_cell_value_and_format($c, $coord, $sc, $worksheet, $style);

      // Comment
      if (!empty($c['comment'])) {
        $worksheet
          ->getComment($coord)
          ->getText()
          ->createTextRun($c['comment']);
      }
      if (!empty($style)) {

        // Font
        $font = $style
          ->getFont();
        if (!empty($c['font'])) {
          $scf = socialcalc_cellformat_parsefont($c, $sc);
          if (!empty($scf['family'])) {
            $font
              ->setName($scf['family']);
          }
          if (!empty($scf['size'])) {
            $font
              ->setSize(floatval($scf['size']));
          }
          if (!empty($scf['bold'])) {
            $font
              ->setBold(true);
          }
          if (!empty($scf['italic'])) {
            $font
              ->setItalic(true);
          }
        }
        if (!empty($c['color'])) {
          $scc = socialcalc_cellformat_parsecolor($c, $sc, 'color');
          $rgb = sprintf('%02X%02X%02X', $scc['r'], $scc['g'], $scc['b']);
          $font
            ->getColor()
            ->setRGB($rgb);
        }

        // Borders
        foreach (array(
          'br' => 'getRight',
          'bl' => 'getLeft',
          'bt' => 'getTop',
          'bb' => 'getBottom',
        ) as $pos => $method) {
          $border = socialcalc_cellformat_parseborder($c, $sc, $pos);
          if (empty($border)) {
            continue;
          }
          $borderobj = $style
            ->getBorders()
            ->{$method}();
          $thickness = str_replace('px', '', $border['thickness']);

          // TODO: what about other units?
          if ($thickness > 0 && $thickness < 7) {
            $thickness = 'thin';
          }
          else {
            if ($thickness > 7 && $thickness < 15) {
              $thickness = 'medium';
            }
            else {
              if ($thickness > 15) {
                $thickness = 'thick';
              }
            }
          }
          $borderstyles = array(
            'thin' => array(
              'solid' => PHPExcel_Style_Border::BORDER_THIN,
              'dashed' => PHPExcel_Style_Border::BORDER_DASHED,
              'dotted' => PHPExcel_Style_Border::BORDER_DOTTED,
              'double' => PHPExcel_Style_Border::BORDER_DOUBLE,
            ),
            'medium' => array(
              'solid' => PHPExcel_Style_Border::BORDER_MEDIUM,
              'dashed' => PHPExcel_Style_Border::BORDER_MEDIUMDASHED,
            ),
            'thick' => array(
              'solid' => PHPExcel_Style_Border::BORDER_THICK,
            ),
          );

          // TODO: what about other combinations?
          $borderstyle = isset($borderstyles[$thickness][$border['style']]) ? $borderstyles[$thickness][$border['style']] : PHPExcel_Style_Border::BORDER_THIN;
          $borderobj
            ->setBorderStyle($borderstyle);
          $scc = $border['color'];
          $rgb = sprintf('%02X%02X%02X', $scc['r'], $scc['g'], $scc['b']);
          $borderobj
            ->getColor()
            ->setRGB($rgb);
        }

        // Background color
        if (!empty($c['bgcolor'])) {
          $scc = socialcalc_cellformat_parsecolor($c, $sc, 'bgcolor');
          $rgb = sprintf('%02X%02X%02X', $scc['r'], $scc['g'], $scc['b']);
          $style
            ->getFill()
            ->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
          $style
            ->getFill()
            ->getStartColor()
            ->setRGB($rgb);
        }

        // Alignment
        if (!empty($c['cellformat'])) {
          $alignments = array(
            'left' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT,
            'right' => PHPExcel_Style_Alignment::HORIZONTAL_RIGHT,
            'center' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
          );
          $alignment = isset($alignments[$sc['cellformats'][$c['cellformat']]]) ? $alignments[$sc['cellformats'][$c['cellformat']]] : NULL;
          if ($alignment) {
            $style
              ->getAlignment()
              ->setHorizontal($alignment);
          }
        }

        // Vertical Alignment
        $layout = socialcalc_cellformat_parselayout($c, $sc);
        if (!empty($layout['alignvert'])) {
          $valignments = array(
            'top' => PHPExcel_Style_Alignment::VERTICAL_TOP,
            'bottom' => PHPExcel_Style_Alignment::VERTICAL_BOTTOM,
            'middle' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
          );
          $valignment = isset($valignments[$layout['alignvert']]) ? $valignments[$layout['alignvert']] : NULL;
          if ($valignment) {
            $style
              ->getAlignment()
              ->setVertical($valignment);
          }
        }
      }
      $worksheet
        ->duplicateStyle($styles[$hash], $coord);

      // Merged regions
      if (@$c['colspan'] > 1 || @$c['rowspan'] > 1) {
        $coord2 = socialcalc_cr_to_coord($c['pos'][0] + max(@$c['colspan'] - 1, 0), $c['pos'][1] + max(@$c['rowspan'] - 1, 0));
        $worksheet
          ->mergeCells($coord . ':' . $coord2);
      }
    }
  }

  // Columns
  $font = $worksheet
    ->getDefaultStyle()
    ->getFont();
  if (!empty($sc['colattribs']['width'])) {
    foreach ($sc['colattribs']['width'] as $c => $width) {
      $worksheet
        ->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($c - 1))
        ->setWidth(PHPExcel_Shared_Drawing::pixelsToCellDimension($width, $font));
    }
  }

  // Rows
  if (!empty($sc['rowattribs']['height'])) {
    foreach ($sc['rowattribs']['height'] as $r => $height) {
      $worksheet
        ->getRowDimension($r)
        ->setRowHeight($height);
    }
  }

  // Test formulas and replace with calculated value upon failure.
  foreach ($worksheet
    ->getCellCollection() as $cellID) {
    $cell = $worksheet
      ->getCell($cellID);
    if ($cell
      ->getDatatype() == PHPExcel_Cell_DataType::TYPE_FORMULA) {
      try {
        $cell
          ->getCalculatedValue();

        // don't care about return value
      } catch (Exception $e) {
        $coord = $cell
          ->getCoordinate();
        $cell
          ->setValue($sc['cells'][$coord]['datavalue']);
        $comment = t(variable_get('sheetnode_phpexcel_bad_formula', 'Could not export formula !formula.'), array(
          '!formula' => $sc['cells'][$coord]['formula'],
        ));
        $worksheet
          ->getComment($coord)
          ->getText()
          ->createTextRun($comment);
      }
    }
  }

  // Reset style cache.
  unset($styles);
}