function _sheetnode_phpexcel_export_cell_value_and_format in Sheetnode 7.2

Same name and namespace in other branches

1. 5 modules/sheetnode_phpexcel/sheetnode_phpexcel.export.inc \_sheetnode_phpexcel_export_cell_value_and_format()
2. 6 modules/sheetnode_phpexcel/sheetnode_phpexcel.export.inc \_sheetnode_phpexcel_export_cell_value_and_format()
3. 7 modules/sheetnode_phpexcel/sheetnode_phpexcel.export.inc \_sheetnode_phpexcel_export_cell_value_and_format()

Helper function to export a cell.

1 call to _sheetnode_phpexcel_export_cell_value_and_format()

_sheetnode_phpexcel_export_sheet in modules/sheetnode_phpexcel/sheetnode_phpexcel.export.inc

Helper function to export a single spreadsheet.

File

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

Export sheetnodes through PHPExcel to xls or xlsx spreadsheets.

Code

function _sheetnode_phpexcel_export_cell_value_and_format($c, $coord, $sc, $worksheet, $style) {

  // Taken from socialcalc-3.js:SocialCalc.FormatValueForDisplay().
  $cell = $worksheet
    ->getCell($coord);
  $valuetype = empty($c['valuetype']) ? NULL : substr($c['valuetype'], 0, 1);
  $valuesubtype = empty($c['valuetype']) ? NULL : substr($c['valuetype'], 1);
  $displayvalue = @$c['datavalue'];
  $valueformat = NULL;

  // Error.
  if (!empty($c['errors']) || $valuetype == 'e') {
    $displayvalue = !empty($c['errors']) ? $c['errors'] : (!empty($valuesubtype) ? $valuesubtype : t('Unknown error'));
    $cell
      ->setValueExplicit($displayvalue, PHPExcel_Cell_DataType::TYPE_ERROR);
  }
  else {
    if ($valuetype == 't') {

      // Get formatting.
      $valueformat = !empty($c['textvalueformat']) ? $sc['valueformats'][$c['textvalueformat']] : (!empty($sc['defaulttextvalueformat']) ? $sc['valueformats'][$sc['defaulttextvalueformat']] : NULL);
      if (in_array(strtolower($valueformat), array(
        'auto',
        'none',
        'general',
      ))) {
        $valueformat = NULL;
      }

      // Default text formatting based on cell type.
      if (empty($valueformat)) {
        switch ($valuesubtype) {
          case 'h':
            $valueformat = 'text-html';
            break;
          case 'w':
          case 'r':
            $valueformat = 'text-wiki';
            break;
          case 'l':
            $valueformat = 'text-link';
            break;
          default:
            $valueformat = 'text-plain';
            break;
        }
      }

      // Set the value.
      if ($c['datatype'] == 'f') {
        if (strpos($c['formula'], '!') !== FALSE) {
          _sheetnode_phpexcel_export_reference($c, $cell, $worksheet);
        }
        else {
          $cell
            ->setValue('=' . $c['formula']);
        }
      }
      else {
        switch ($valueformat) {
          case 'text-plain':
            $cell
              ->setValueExplicit($displayvalue, PHPExcel_Cell_DataType::TYPE_STRING);
            break;
          case 'text-html':
            _sheetnode_phpexcel_load_library(SHEETNODE_PHPEXCEL_PDF_RENDERER);
            module_load_include('inc', 'sheetnode_phpexcel', 'html2richtext');
            $html2rtf = new Sheetnode_PHPExcel_HTML2RichText($displayvalue);
            $rtf = $html2rtf
              ->convert($cell);
            if ($rtf) {
              $cell
                ->setValue($rtf);
            }
            else {
              $stripped = preg_replace('/^\\s+|\\s+$/', '', strip_tags($displayvalue));
              $cell
                ->setValueExplicit($stripped, PHPExcel_Cell_DataType::TYPE_STRING);
            }
            break;
          case 'text-url':
          case 'text-link':
            $matches = array();
            if (preg_match('/^(.*)<(.*)>$/', $displayvalue, $matches)) {
              $text = trim($matches[1], "\r\n\t \"");
              $url = $matches[2];
            }
            else {
              $url = $displayvalue;
              $parse_url = parse_url($url);
              $text = $parse_url['host'] . $parse_url['path'];
            }
            $cell
              ->setValueExplicit($text, PHPExcel_Cell_DataType::TYPE_STRING);
            $cell
              ->getHyperlink()
              ->setUrl($url);
            break;
          case 'text-wiki':

            // TODO
            break;
          case 'text-image':
            if (function_exists('curl_init')) {

              // Download the file using cURL.
              $ch = curl_init($displayvalue);
              $filename = tempnam(file_directory_temp(), 'sheetnode_phpexcel_');
              $fp = fopen($filename, 'wb');
              $options = array(
                CURLOPT_FILE => $fp,
                CURLOPT_HEADER => 0,
                CURLOPT_FOLLOWLOCATION => 1,
                CURLOPT_TIMEOUT => 60,
              );
              curl_setopt_array($ch, $options);
              curl_exec($ch);
              curl_close($ch);
              fclose($fp);
              $image_info = image_get_info($filename);
              if (!empty($image_info)) {

                // Insert the image in the PHPExcel file.
                $objDrawing = new PHPExcel_Worksheet_Drawing();
                $objDrawing
                  ->setPath($filename);
                $objDrawing
                  ->setCoordinates($coord);
                $objDrawing
                  ->setWidth($image_info['width']);
                $objDrawing
                  ->setWorksheet($worksheet);
              }
            }
            break;
          case 'hidden':

            // TODO
            break;
          case 'formula':

            // TODO
            break;
          default:
            if (substr($valueformat, 0, 12) == 'text-custom:') {

              // TODO
            }
            else {
              if (substr($valueformat, 0, 6) == 'custom') {

                // TODO
              }
              else {
                $cell
                  ->setValue($displayvalue);
              }
            }
            break;
        }
      }
    }
    else {
      if ($valuetype == 'n') {

        // Get formatting.
        $valueformat = !empty($c['nontextvalueformat']) ? $sc['valueformats'][$c['nontextvalueformat']] : (!empty($sc['defaultnontextvalueformat']) ? $sc['valueformats'][$sc['defaultnontextvalueformat']] : NULL);
        if (in_array(strtolower($valueformat), array(
          'auto',
          'none',
        ))) {
          $valueformat = NULL;
        }

        // Default number formatting based on cell type.
        if (empty($valueformat)) {
          switch ($valuesubtype) {
            case '%':
              $valueformat = SocialCalc_Constants::$defaultFormatp;
              break;
            case '$':
              $valueformat = SocialCalc_Constants::$defaultFormatc;
              break;
            case 'dt':
              $valueformat = SocialCalc_Constants::$defaultFormatdt;
              break;
            case 'd':
              $valueformat = SocialCalc_Constants::$defaultFormatd;
              break;
            case 't':
              $valueformat = SocialCalc_Constants::$defaultFormatt;
              break;
            case 'l':
              $valueformat = 'logical';
              break;
            default:
              $valueformat = PHPExcel_Style_NumberFormat::FORMAT_GENERAL;
              break;
          }
        }

        // Set the value.
        if ($c['datatype'] == 'f') {
          if (strpos($c['formula'], '!') !== FALSE) {
            _sheetnode_phpexcel_export_reference($c, $cell, $worksheet);
          }
          else {
            $cell
              ->setValue('=' . $c['formula']);
          }
        }
        else {
          if ($valueformat == 'logical') {
            $displayvalue = empty($displayvalue) ? SocialCalc_Constants::$defaultDisplayFALSE : SocialCalc_Constants::$defaultDisplayTRUE;
            $cell
              ->setValue($displayvalue);
            $valueformat = NULL;
          }
          else {
            if ($valueformat == 'formula') {
              $cell
                ->setValueExplicit('=' . $c['formula'], PHPExcel_Cell_DataType::TYPE_STRING);
            }
            else {
              if ($valueformat == 'forcetext') {
                $cell
                  ->setValueExplicit($displayvalue, PHPExcel_Cell_DataType::TYPE_STRING);
              }
              else {
                $cell
                  ->setValueExplicit($displayvalue, PHPExcel_Cell_DataType::TYPE_NUMERIC);
              }
            }
          }
        }

        // Set the formatting.
        if ($valueformat == 'hidden') {

          // TODO
        }
        else {
          if (!empty($valueformat) && !empty($style)) {
            $style
              ->getNumberFormat()
              ->setFormatCode($valueformat);
          }
        }
      }
      else {
        $cell
          ->setValueExplicit('', PHPExcel_Cell_DataType::TYPE_NULL);
      }
    }
  }
}