function feeds_xls_set_headers in Feeds XLS 7

2 calls to feeds_xls_set_headers()

feeds_xls_download_template_helper in ./feeds_xls.template.inc

feeds_xls_redirect_to_populated_file in ./feeds_xls.template.inc

Finish function for populate data.

File

./feeds_xls.template.inc, line 566

Code

function feeds_xls_set_headers($feeds_importer, $objPHPExcel, $output = 'php://output', $writer = 'Excel5') {
  $not_required = array();

  // Allow other modules to specify fields that should not be required
  drupal_alter('feeds_xls_not_required', $not_required);

  // Set properties
  $title = t('Template for !feeds_name', array(
    '!feeds_name' => $feeds_importer->config['name'],
  ));
  $objPHPExcel
    ->getProperties()
    ->setCreator(FEEDS_EXCEL_CREATOR)
    ->setLastModifiedBy(FEEDS_EXCEL_CREATOR)
    ->setTitle($title);

  // Set active sheet index to the first sheet, so Excel opens this as the first
  // sheet
  $objPHPExcel
    ->setActiveSheetIndex(0);

  // Add some data
  $sheet = $objPHPExcel
    ->getActiveSheet();

  // Add the Column headers.
  $required_cells = array();
  foreach ($feeds_importer->processor->config['mappings'] as $key => $header) {
    $first_letter = floor($key / 26);
    $second_letter = $key % 26 + 1;
    $cell = chr(64 + $second_letter);
    if ($first_letter) {
      $cell = chr(64 + $first_letter) . $cell;
    }
    $column = $cell;
    $cell = "{$cell}1";
    $sheet
      ->setCellValue($cell, _feeds_xls_get_value($header['source']));

    // Set the columnn width
    $sheet
      ->getColumnDimension($column)
      ->setAutoSize(TRUE);

    // Validation
    if (isset($feeds_importer->processor->config['bundle']) || isset($feeds_importer->processor->config['vocabulary']) || $feeds_importer->processor->id == 'user_importer_user') {
      if (isset($feeds_importer->processor->config['vocabulary'])) {
        $bundle = $feeds_importer->processor->config['vocabulary'];
        $entity_type = 'taxonomy_term';
      }
      elseif ($feeds_importer->processor->id == 'user_importer_user') {
        $bundle = 'user';
        $entity_type = 'user';
      }
      else {
        if (isset($feeds_importer->processor->config['bundle'])) {
          $bundle = $feeds_importer->processor->config['bundle'];
          $entity_type = 'node';
        }
      }
      if (strpos($header['target'], ':')) {
        $temp_array = explode(':', $header['target']);
        switch ($temp_array[1]) {
          case 'guid':
          case 'tid':
            $field = FALSE;
            break;
          default:
            $field = field_info_field($temp_array[0]);
        }
      }
      else {
        $field = field_info_field($header['target']);
      }
      if ($field) {

        // Get the field instance, so that we can check if this field is
        // required.
        $class = get_class($feeds_importer->processor);
        $field_instance = field_info_instance($entity_type, $field['field_name'], $bundle);
        $objValidation = $sheet
          ->getDataValidation($column . '2:' . $column . '65536');
        $objValidation
          ->setAllowBlank($field_instance['required'] ? FALSE : TRUE);
        if ($help_text = feeds_xls_field_type_help_text($field)) {
          $objValidation
            ->setPromptTitle(t('Help'));
          $objValidation
            ->setPrompt($help_text);
        }
        if ($field_instance['required']) {

          // Allow override of required fields
          if (!in_array($field['field_name'], $not_required)) {
            $required_cells[] = $column;
            $objValidation
              ->setPromptTitle(t('Required'));
            $objValidation
              ->setPrompt(t('This cell may not be left blank') . $help_text ? $help_text : '');
          }
        }
        $objValidation
          ->setShowInputMessage(TRUE);
        $objValidation
          ->setShowErrorMessage(TRUE);
        $allowed_values = array();
        if (isset($field['settings']['allowed_values_function']) && function_exists($field['settings']['allowed_values_function'])) {
          $allowed_values = call_user_func($field['settings']['allowed_values_function'], $field);
        }
        else {
          if (isset($field['settings']['allowed_values']) && is_array($field['settings']['allowed_values'])) {
            $allowed_values = $field['settings']['allowed_values'];

            // Especially for the creative_commons module
            // FIXME - there must be a nicer way of doing this.
            if ($field['type'] == 'creative_commons') {
              $allowed_values = creative_commons_get_licence_types();
            }
          }
          else {
            if ($field['type'] == 'node_reference') {

              // If the view name is set, we can not do anything.
              if (!$field['settings']['view']['view_name'] && array_filter($field['settings']['referenceable_types'])) {
                $results = db_select('node', 'n')
                  ->condition('type', $field['settings']['referenceable_types'])
                  ->fields('n', array(
                  'nid',
                  'title',
                ))
                  ->execute();
                $target_parts = explode(':', $header['target']);
                foreach ($results as $row) {
                  $allowed_values[] = $row->{$target_parts}[1];
                }
              }
            }
            else {
              if ($field['type'] == 'user_reference') {
                $query = db_select('users', 'u');
                $query
                  ->condition('uid', 1, '>');
                $query
                  ->condition('name', 'Anonymous', '!=');
                $query
                  ->fields('u', array(
                  'uid',
                  'name',
                ));
                $results = $query
                  ->execute();
                $target_parts = explode(':', $header['target']);
                foreach ($results as $row) {
                  $allowed_values[] = $row->{$target_parts}[1];
                }
              }
            }
          }
        }
        if (count($allowed_values)) {
          if (!isset($field['cardinality']) || $field['cardinality'] < 2) {
            $objValidation
              ->setType(PHPExcel_Cell_DataValidation::TYPE_LIST);
            $objValidation
              ->setErrorStyle(PHPExcel_Cell_DataValidation::STYLE_STOP);
            $objValidation
              ->setShowDropDown(TRUE);
            $objValidation
              ->setErrorTitle('Input error');
            $objValidation
              ->setError(t('Your input did not match one of the allowed values.'));
            $objValidation
              ->setPromptTitle(t('Allowed input'));
          }
          $row = 0;
          foreach ($allowed_values as $allowed_value) {
            $num_sheets = count($objPHPExcel
              ->getAllSheets());
            if ($num_sheets == 1) {
              $objPHPExcel
                ->createSheet();
              $objPHPExcel
                ->getSheet(1)
                ->setTitle('PermittedValues');
            }
            if (is_array($allowed_value) && isset($allowed_value['vocabulary'])) {

              // Get the vocabulary so we can get the vid.
              $vocabulary = taxonomy_vocabulary_machine_name_load($allowed_value['vocabulary']);
              if ($vocabulary) {
                $results = db_select('taxonomy_term_data', 't')
                  ->fields('t', array(
                  'name',
                ))
                  ->condition('vid', $vocabulary->vid)
                  ->distinct()
                  ->execute();
                foreach ($results as $term) {
                  $row++;
                  $objPHPExcel
                    ->getSheet(1)
                    ->setCellValue("{$column}{$row}", _feeds_xls_get_value($term->name));
                }
              }
              $break_at_end = FALSE;
            }
            else {
              foreach ($allowed_values as $key => $term) {
                if (!$term) {
                  $term = $key;
                }
                $row++;
                $objPHPExcel
                  ->getSheet(1)
                  ->setCellValue("{$column}{$row}", _feeds_xls_get_value($term));
              }
              $break_at_end = TRUE;
            }
            $objValidation
              ->setPrompt(t("Only values from column '!column' in the 'PermittedValues' worksheet are allowed.", array(
              '!columnn' => $column,
            )) . "\n\n" . ($field_instance['required'] ? t('This cell may not be left blank.') : t('This cell may be left blank.')));
            $objValidation
              ->setFormula1('PermittedValues!$' . $column . '$1:$' . $column . '$' . $row);
            if ($break_at_end) {
              break;
            }
          }
        }
      }
    }
    else {

      // Here we should set the title to be required, and also other random
      // fields that aren't standard "fields".
      // Set title to required.
      if ($header['target'] == 'title' && strtolower(substr(get_class($feeds_importer->processor), 5, strlen(get_class($feeds_importer->processor)) - 14)) == 'node') {
        $objValidation = $sheet
          ->getDataValidation($column . '2:' . $column . '65536');
        $objValidation
          ->setAllowBlank(FALSE);
        $objValidation
          ->setPromptTitle(t('Required'));
        $objValidation
          ->setPrompt(t('This cell may not be left blank'));
        $objValidation
          ->setShowInputMessage(TRUE);
      }
    }
  }

  // setAutoSize for the row
  $sheet
    ->getRowDimension(1)
    ->setRowHeight(30);

  // Set the font for the header row
  $sheet
    ->getStyle("A1:{$cell}")
    ->getFont()
    ->applyFromArray(array(
    'name' => 'Arial',
    'bold' => TRUE,
    'italic' => FALSE,
    'color' => array(
      'rgb' => 'ffffff',
    ),
    'size' => 14,
  ));

  // Set font colour for required cells
  foreach ($required_cells as $required_cell) {
    $sheet
      ->getStyle($required_cell . '1')
      ->getFont()
      ->applyFromArray(array(
      'name' => 'Arial',
      'bold' => TRUE,
      'italic' => FALSE,
      'color' => array(
        'rgb' => 'ff0000',
      ),
      'size' => 14,
    ));
  }

  // Set the background colour for the header row.
  $sheet
    ->getStyle("A1:{$cell}")
    ->getFill()
    ->applyFromArray(array(
    'type' => PHPExcel_Style_fill::FILL_SOLID,
    'color' => array(
      'rgb' => '000000',
    ),
  ));

  // Size the cells accordingly.
  $sheet
    ->getStyle("A1:{$cell}")
    ->getAlignment()
    ->applyFromArray(array(
    'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
    'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
    'rotation' => 0,
    'wrap' => FALSE,
    'shrinkToFit' => FALSE,
  ));

  // Rename sheet
  $sheet
    ->setTitle('Template');

  // If we are outputting to a browser, then we need to add some headers.
  if ($output == 'php://output') {
    $filename = str_replace(' ', '_', preg_replace('/[^a-z\\ ]/', '', strtolower($feeds_importer->config['name'])));
    switch ($writer) {
      case 'Excel5':

        // Redirect output to a client’s web browser (Excel5)
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="TEMPLATE-' . $filename . '.xls"');
        break;
      case 'Excel2007':

        // Redirect output to a client’s web browser (Excel5)
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="TEMPLATE-' . $filename . '.xlsx"');
        break;
    }
    header('Cache-Control: max-age=0');
  }
  else {
    $output = drupal_realpath($output);
  }
  $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, $writer);
  $objWriter
    ->save($output);
}