function _sheetnode_phpexcel_export_do in Sheetnode 5

2 calls to _sheetnode_phpexcel_export_do()

sheetnode_phpexcel_plugin_style::render in modules/sheetnode_phpexcel/sheetnode_phpexcel_plugin_style.inc

_sheetnode_phpexcel_export in modules/sheetnode_phpexcel/sheetnode_phpexcel.export.inc

File

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

Code

function _sheetnode_phpexcel_export_do($format, $title, $socialcalc) {
  require_once variable_get('sheetnode_phpexcel_library_path', '') . '/Classes/PHPExcel.php';

  // Create new PHPExcel object
  $sc = $socialcalc['sheet'];
  $workbook = new PHPExcel();

  // Title
  try {

    // TODO: Split titles with format "Spreadsheet > Workbook" and sanitize.
    $workbook
      ->getActiveSheet()
      ->SetTitle($title);
  } catch (Exception $e) {
    watchdog('sheetnode_phpexcel', 'Error setting worksheet title to "!title": @error', array(
      '!title' => $title,
      '@error' => $e
        ->getMessage(),
    ), WATCHDOG_WARNING);
  }

  /*
    // TODO: Properties
    $workbook->getProperties()->setCreator("Maarten Balliauw")
                   ->setLastModifiedBy("Maarten Balliauw")
                   ->setTitle("Office 2007 XLSX Test Document")
                   ->setSubject("Office 2007 XLSX Test Document")
                   ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
                   ->setKeywords("office 2007 openxml php")
                   ->setCategory("Test result file");
  */

  // Names
  // TODO: Not supported in Excel5.
  if (!empty($sc['names'])) {
    foreach ($sc['names'] as $n => $info) {

      // TODO: Support external references.
      $workbook
        ->addNamedRange(new PHPExcel_NamedRange($n, $workbook
        ->getActiveSheet(), $info['definition']));
    }
  }

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

      // Value and format
      _sheetnode_phpexcel_export_cell_value_and_format($c, $coord, $sc, $workbook
        ->getActiveSheet());

      // Comment
      // TODO: Not supported in Excel5.
      if (!empty($c['comment'])) {
        $workbook
          ->getActiveSheet()
          ->getComment($coord)
          ->getText()
          ->createTextRun($c['comment']);
      }

      // Font
      $style = $workbook
        ->getActiveSheet()
        ->getStyle($coord);
      if (!empty($c['font'])) {
        $scf = socialcalc_cellformat_parsefont($c, $sc);
        $font = $style
          ->getFont();
        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);
        }
      }

      // 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));
        $workbook
          ->getActiveSheet()
          ->mergeCells($coord . ':' . $coord2);
      }
    }
  }

  // Columns
  $font = $workbook
    ->getActiveSheet()
    ->getDefaultStyle()
    ->getFont();
  if (!empty($sc['colattribs']['width'])) {
    foreach ($sc['colattribs']['width'] as $c => $width) {
      $workbook
        ->getActiveSheet()
        ->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) {
      $workbook
        ->getActiveSheet()
        ->getRowDimension($r)
        ->setRowHeight($height);
    }
  }

  // Test formulas and replace with calculated value upon failure.
  foreach ($workbook
    ->getActiveSheet()
    ->getCellCollection() as $cellID) {
    $cell = $workbook
      ->getActiveSheet()
      ->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'],
        ));
        $workbook
          ->getActiveSheet()
          ->getComment($coord)
          ->getText()
          ->createTextRun($comment);
      }
    }
  }

  //return '';

  // Redirect output to a client’s web browser.
  $plugins = sheetnode_phpexcel_get_plugins();
  $plugin = $plugins[$format];
  header('Content-Type: ' . $plugin['content-type']);
  header('Content-Disposition: attachment;filename="' . _sheetnode_sanitize_filename($title, $format) . '"');
  header('Cache-Control: max-age=0');
  $writer = PHPExcel_IOFactory::createWriter($workbook, $plugin['php-excel-type']);
  $writer
    ->save('php://output');
  exit;
}