Экспорт данных в Excel файл с помощью PHPExcel

Вчера столкнулся с задачей экспортировать данные в Excel файл. Поиски в интернете привели меня к библиотеке PHPExcel. Когда я прочитал документацию, посмотрел примеры и почитал отзывы, я остановился именно на этой библиотеке. С помощью PHPExcel можно форматировать таблицы(устанавливать выравнивание, менять текст и его цвет, менять границы таблиц и их цвет), вставлять изображения, рисовать диаграммы и многое другое.

Сейчас я покажу вам на примере экспорта контента как экспортировать данные в Excel.

Первым делом скачиваем саму библиотеку PHPExcel. Распаковываем архив, идем в папку "Classes" и копируем ее содержимое. Далее идем в sites/all/libraries нашего сайта, создаем там каталог PHPExcel и вставляем в нее то, что мы копировали.

Следующим шагом будет установка и включение модуля Libraries API.

Теперь переходим к созданию нашего модуля. Я назову модуль "phpexcel_example", следовательно создаем в sites/all/modules нашего сайта папку "phpexcel_example". В ней создаем "phpexcel_example.info". Содержимое этого файла:

name = PHPExcel Example
description = Allows export nodes to *.xls file
core = 7.x

package = phpexcel

dependencies[] = libraries

Здесь мы указали зависимость нашего модуля от Libraries API:

dependencies[] = libraries

Далее создаем файл "phpexcel_example.module" и создаем страницу на которой будет форма с выбором типа материала:

<?php

/**
 * Implements hook_menu().
 */
function phpexcel_example_menu() {
  $items = array();

  $items['admin/config/content/phpexcel-example'] = array(
    'title' => 'PHPExcel Example',
    'description' => 'Export nodes to *.xls file.',
    'page callback' => 'drupal_get_form',
    'page arguments' => array('phpexcel_example_admin_form'),
    'access arguments' => array('administer nodes'),
    'file' => 'phpexcel_example.admin.inc',
  );

  return $items;
}

Страницу создали, теперь необходимо создать функцию, которая будет формировать нашу таблицу. Так как в hook_menu() я написал следующую строчку:

'file' => 'phpexcel_example.admin.inc',

То функция будет находится в файле "phpexcel_example.admin.inc". Создаем этот файл и пишем нашу функцию:

<?php

/**
 * @file
 * Admin menu callbacks for phpexcel_example.module.
 */

/**
 * Administrative settings form.
 */
function phpexcel_example_admin_form($form, $form_state) {

  // Получаем путь к библиотеке.
  $path = libraries_get_path('PHPExcel');

  // Если отсутствуют файлы библиотеки, то выводим текст с ошибкой.
  if (!file_exists($path . '/PHPExcel.php') || !is_dir($path . '/PHPExcel')) {
    $form['empty'] = array(
      '#markup' => t('PHPExcel library not found.'),
    );

    return $form;
  }

  $form['phpexcel_example'] = array(
    '#type' => 'fieldset',
    '#title' => t('Node types'),
  );

  $form['phpexcel_example']['phpexcel_example_node_type'] = array(
    '#type' => 'select',
    '#title' => t('Select node type'),
    '#options' => node_type_get_names(),
  );

  $form['action'] = array(
    '#type' => 'actions',
  );

  $form['action']['submit'] = array(
    '#type' => 'submit',
    '#value' => t('Export'),
  );

  return $form;
}

Теперь необходимо написать сабмит функцию:

/**
 * Submit callback for phpexcel_example_admin_form().
 */
function phpexcel_example_admin_form_submit($form, $form_state) {
  $type = $form_state['values']['phpexcel_example_node_type'];

  // Загружаем из базы ноды выбранного типа.
  $nodes = _phpexcel_example_load_nodes($type);

  // Если удалось получить из базы ноды, то экспортируем их.
  if (!empty($nodes)) {
    phpexcel_example_export($nodes);
  }
}

Как видно из кода, на сабмите у нас выполняется 2 функции  _phpexcel_example_load_nodes() и phpexcel_example_export(). Эти функции мы добавим в файл "phpexcel_example.module". Функция _phpexcel_example_load_nodes() загружает ноды переданного типа:

/**
 * @param $type
 * @return array
 */
function _phpexcel_example_load_nodes($type) {
  $query = db_select('node', 'n')
    ->fields('n', array('nid', 'vid', 'type', 'language', 'title', 'uid', 'status', 'created', 'changed'))
    ->condition('n.type', $type);

  $result = $query->execute();

  $nodes = array();
  foreach ($result as $record) {
    $nodes[] = (array) $record;
  }

  return $nodes;
}

Функция phpexcel_example_export() формирует Excel файл, сохраняет его на сервере и открывает диалог для скачивания созданного файла:

/**
 * @param $nodes
 */
function phpexcel_example_export($nodes) {
  global $user;

  // Пытаемся создать директорию, в которую будем сохранять файл, если 
  // неудачно, то выводим соответствующее сообщение.
  $directory = 'public://nodes_export';
  if (!file_prepare_directory($directory, FILE_MODIFY_PERMISSIONS | FILE_CREATE_DIRECTORY)) {
    drupal_set_message(t('Failed to create %directory.', array('%directory' => $directory)), 'error');
    return FALSE;
  }

  $path = libraries_get_path('PHPExcel');
  require_once DRUPAL_ROOT . '/' . $path . '/PHPExcel.php';

  // Начинаем создавать excel файл.
  $PHPExcel = new PHPExcel();

  // Устанавливаем информацию об файле.
  $PHPExcel->getProperties()
    ->setCreator($user->name)
    ->setLastModifiedBy($user->name)
    ->setTitle('Office 2003 XLS Document')
    ->setSubject('Office 2003 XLS Document')
    ->setDescription('Document for Office 2003 XLS, generated using PHP classes.')
    ->setKeywords('office 2003 openxml php')
    ->setCategory('Nodes export result file');

  $rows = array();

  // Формируем первую строку таблицы, одно значение массива соответствует 
  // одному столбику.
  $i = 0;
  $columnCount = 'A';
  foreach ($nodes['0'] as $key => $value) {
    $rows['0'][] = $key;

    if (++$i != count($nodes['0'])) {
      $columnCount++;
    }
  }

  // Формируем содержимое таблицы, одна нода является одной строкой
  // таблицы.
  $rowCount = 1;
  foreach ($nodes as $node) {
    $rows[] = array(
      $node['nid'],
      $node['vid'],
      $node['type'],
      $node['language'],
      $node['title'],
      $node['uid'],
      $node['status'],
      $node['created'],
      $node['changed'],
    );

    $rowCount++;
  }

  // Записываем в файл данные из ранее подготовленного массива.
  $PHPExcel->getActiveSheet()->fromArray($rows);

  // Устанавливаем авто ширину каждой колонки.
  for($i = 'A'; $i !== $columnCount; $i++) {
    $PHPExcel->getActiveSheet()->getColumnDimension($i)->setAutoSize(TRUE);
  }

  // Формируем массив с форматированием для таблицы.
  $styleArray = array(
    'borders' => array(
      'allborders' => array(
        'style' => PHPExcel_Style_Border::BORDER_THIN,
      ),
    ),
  );

  // Устанавливаем форматирование для таблицы из ранее созданного массива, 
  // в данном случае границы таблицы.
  $PHPExcel->getActiveSheet()->getStyle('A1:' . $columnCount . $rowCount)->applyFromArray($styleArray);

  // Устанавливаем вертикальное выравнивание по верхнему краю, горизонтальное - по 
  // левому и перенос слов.
  $PHPExcel->getActiveSheet()->getStyle('A1:' . $columnCount . $rowCount)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT)->setWrapText(TRUE);

  // Изеняем название активного листа в excel файле.
  $PHPExcel->getActiveSheet()->setTitle('PHPExcel Example');

  // Делаем активным первый лист, для того, чтобы при открытии файла был 
  // открыт первый лист.
  $PHPExcel->setActiveSheetIndex(0);

  // Формируем имя файла.
  $filename = 'phpexcel_example_' . date('d_m_Y', REQUEST_TIME) . '.xls';

  // Формируем полный буть для будущего сохранения файла.
  $path = file_create_filename($filename, 'public://nodes_export');

  // Создаем excel файл.
  $objWriter = PHPExcel_IOFactory::createWriter($PHPExcel, 'Excel5');

  // Сохраняем excel файл.
  $objWriter->save($path);

  // Создаем объект файла для сохранение его в базу.
  $file = new stdClass();
  $file->fid = NULL;
  $file->uri = $path;
  $file->filename = drupal_basename($path);
  $file->filemime = file_get_mimetype($path);
  $file->uid = $user->uid;

  // По умолчанию, файл будет удален через 6 часов по крону, если 
  // необходимо, чтобы файл был сохранен на сервере, то необходимо 
  // раскоментировать строку.
  // $file->status = FILE_STATUS_PERMANENT;

  // Сохраняем файл базу.
  file_save($file);

  // Формируем заголовки, для того, чтобы сразу же скачать созданный файл.
  $http_headers = array(
    'Content-Type' => 'application/vnd.ms-excel',
    'Content-Disposition' => 'attachment; filename="' . drupal_basename($path) . '"',
    'Content-Length' => filesize($path),
  );

  if(strpos($_SERVER['HTTP_USER_AGENT'], 'MSIE')) {
    $http_headers['Cache-Control'] = 'must-revalidate, post-check=0, pre-check=0';
    $http_headers['Pragma'] = 'public';
  }
  else {
    $http_headers['Pragma'] = 'no-cache';
  }

  // Открываем диалог для скачивания файла.
  file_transfer($path, $http_headers);
}

Теперь включаем наш модуль, идем на страницу "admin/config/content/phpexcel-example", выбираем необходимый тип материала и жмем кнопку Export.

P.S. Данный модуль создан только в качестве наглядного примера экспорта данных в Excel файл.

Комментарии (16)

Аватар пользователя Андрей
Андрей

Хорошая статья. ++ к карме.

Аватар пользователя Benya
Benya

Спасибо. Рад, что Вам понравилось:)

Аватар пользователя Илья
Илья

Комментирую редко, но спасибо, реально помог=)))). статья отличная, код понятный и рабочий!!!=)))

Аватар пользователя zviryatk0
zviryatk0

Отличная статья по работе с либой, использовал себе для экспорта комерц продуктов из категории, из мелочей могу добавить что есть статический метод для перевода числового номера столбца в буквенный - PHPExcel_Cell::stringFromColumnIndex()

Аватар пользователя Benya
Benya

Ага, видел, но подходит не для всех задач

Аватар пользователя Дмитрий
Дмитрий

Добавьте пожалуйста в урок пример того как процесс записи сделать в батче

Аватар пользователя Дяд Бералус
Дяд Бералус

Замечательно! И огромное количество благодарности!
Использовал себе для экспорта
А что на счет импорта?

Аватар пользователя Benya
Benya

Для импорта можно посмотреть feeds. Думаю там из коробки все будет работать

Аватар пользователя Amrita
Amrita

Спасибо огромное!

Аватар пользователя arkona
arkona

Большое спасибо за очень полезный модуль.
А есть ли в планах, адаптировать этот модуль под Друпал-8?

Аватар пользователя Benya
Benya

пока такой необходимости у меня нет

Аватар пользователя nemezida.su
nemezida.su

А почему я не могу использовать эту библиотеку, если он расположена на другом xосте. Просто есть несколько сайтов, с которыx xотелось бы обращаться к файлам, наxодящимся только на одном из ниx. Но конструкция вида

require_once (‘адрес_сайта/PHPExcel/Classes/PHPExcel/IOFactory.php’); почему-то не работает((

Аватар пользователя Benya
Benya

потому что нельзя

Аватар пользователя Сергей
Сергей

на 6 версии это буде работать?

Аватар пользователя Кирилл
Кирилл

Модуль не видит библиотеку PHPExcel. Пишет PHPExcel library not found. В отчете сайта библиотека подключена. Пожалуйста помогите!