Создание exсel файлов на php с помощью PhpSpreadsheet

Сентябрь 22, 2019 21:35 Admin excel-php

В данной статье я бы хотел рассмотреть библиотеку, которая позволяет создавать файлы формата xls и xlsx. Библиотека PhpSpreadsheet является по сути продолжением популярной библиотеки PHPExcel и разрабатывается теми же разработчиками, которые сделали PHPExcel , и, поскольку PHPExcel больше не поддерживается, использовать я буду именно PhpSpreadsheet.

Установка PhpSpreadsheet и требования

Прежде чем начать установку убедитесь, что у вас установлен composer (более подробно про установку composer можно прочесть здесь), а также установлена версия PHP не ниже 7.1. Затем для установки библиотеки выполняем следующую команду:

composer require phpoffice/phpspreadsheet

После того, как composer установит библиотеку и все ее зависимости можно приступать к формированию excel файлов.

Работа с библиотекой, запись данных и создание xlsx файлов

В рамках данной статьи мы познакомимся с некоторыми основными возможностями библиотеки, для более детального ее изучения можно ознакомиться с примерами, которые расположены в папке samples самой библиотеки.

Самый простой пример создания файла может выглядеть следующим образом:

<?php

require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

//Создаем экземпляр класса электронной таблицы
$spreadsheet = new Spreadsheet();
//Получаем текущий активный лист
$sheet = $spreadsheet->getActiveSheet();
// Записываем в ячейку A1 данные
$sheet->setCellValue('A1', 'Hello my Friend!');

$writer = new Xlsx($spreadsheet);
//Сохраняем файл в текущей папке, в которой выполняется скрипт.
//Чтобы указать другую папку для сохранения. 
//Прописываем полный путь до папки и указываем имя файла
$writer->save('hello.xlsx');

Чтобы создать файл с расширением xls, а не xlsx, достаточно заменить класс Xlsx на Xls и изменить расширение в названии создаваемого файла.

Если при открытии файла с расширением xls возникают ошибки, необходимо проверить в настройках PHP параметр mbstring.func_overload. Для корректной работы библиотеки с расширением файлов xls необходимо, чтобы он был установлен в mbstring.func_overload = 0.

Установка стилей ячеек

Для установки стилей ячеек проще всего использовать метод applyFromArray, который принимает массив параметров. С его помощью можно задать шрифт, цвет текста, границы, выравнивание текста и другие параметры:

<?php

require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\{Font, Border, Alignment};

$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setCellValue('B2', 'Hello!');
// Получаем ячейку для которой будем устанавливать стили
$sheet->getStyle('B2')->applyFromArray([
    'font' => [
      'name' => 'Arial',
      'bold' => true,
      'italic' => false,
      'underline' => Font::UNDERLINE_DOUBLE,
      'strikethrough' => false,
      'color' => [
          'rgb' => '808080'
        ]
    ],
    'borders' => [
        'allBorders' => [
            'borderStyle' => Border::BORDER_THIN,
            'color' => [
                'rgb' => '808080'
            ]
        ],
    ],
    'alignment' => [
        'horizontal' => Alignment::HORIZONTAL_CENTER,
        'vertical' => Alignment::VERTICAL_CENTER,
        'wrapText' => true,
    ]
]);

// Выбросим исключение в случае, если не удастся сохранить файл
try {
    $writer = new Xlsx($spreadsheet);
    $writer->save('hello.xlsx');

} catch (PhpOffice\PhpSpreadsheet\Writer\Exception $e) {
    echo $e->getMessage();
}

Исключение обработано просто для демонстрации в случае, если не удастся создать файл.

Установка размеров и объединение ячеек

Высота и ширина устанавливается на строку и столбец соответственно. Для установки высоты используется метод setRowHeight, а для установки ширины метод setWidth. Чтобы объединить несколько ячеек необходимо использовать метод mergeCells:

<?php

require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setCellValue('B2', 'Hello!');
$sheet->setCellValue('C2', 'Hello there!');
//Установка ширины столбца B
$sheet->getColumnDimension('B')->setWidth(50);
//Установка высоты для 2 строки
$sheet->getRowDimension(2)->setRowHeight(50);
//Объединение ячеек
$sheet->mergeCells('C2:D3');

try {
    $writer = new Xlsx($spreadsheet);
    $writer->save('hello.xlsx');

} catch (PhpOffice\PhpSpreadsheet\Writer\Exception $e) {
    echo $e->getMessage();
}

Как видно из примера для установки высоты и ширины предварительно необходимо вызвать вспомогательные методы getColumnDimension и getRowDimension.

Добавление картинок

Для добавления картинок используется класс Drawing:

<?php

require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Worksheet\Drawing;

$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();

//С помощью класса Drawing можно осуществлять вставку картинок
$drawing = new Drawing();
//Указываем путь до картинки, которая должна быть расположена
//на том же сервере
$drawing->setPath('images/image.jpg');
//Указываем ячейку в которой разместим изображение
$drawing->setCoordinates('B2');
//Можно задать отступ по X или Y оси
$drawing->setOffsetY(50);
//Передаем объект текущего листа
$drawing->setWorksheet($sheet);

try {
    $writer = new Xlsx($spreadsheet);
    $writer->save('hello.xlsx');

} catch (PhpOffice\PhpSpreadsheet\Writer\Exception $e) {
    echo $e->getMessage();
}

Помимо тех возможностей, что используются в примере, также можно задавать тени у картинок, придавать им вращение и т.д. Более подробно можно обратиться к примерам библиотеки. Также стоит отметить, что путь до файла с картинкой необходимо задавать относительно сервера на котором запускается скрипт создания ваших таблиц. Указать путь в виде ссылки на сторонний ресурс не удастся (на сколько мне известно), для этого сначала необходимо скачать данную картинку на ваш сервер и только потом указывать путь до картинки относительно текущего сервера.

Арифметические операции

В библиотеке доступно большое количество операций для выполнения расчетов, таких как: нахождение суммы чисел, нахождение среднего значения, взятие максимального и минимального числа и многие другие вычисления. В примере ниже показано, как вычислить сумму чисел, среднее значение и нахождение максимального из чисел:

<?php

require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setCellValue('B1', '1');
$sheet->setCellValue('B2', '5');
$sheet->setCellValue('B3', '9');
$sheet->setCellValue('A4', 'Sum:');
$sheet->setCellValue('A5', 'Average:');
$sheet->setCellValue('A6', 'Max:');
$sheet->setCellValue('B4', '=SUM(B1:B3)');
$sheet->setCellValue('B5', '=AVERAGE(B1:B3)');
$sheet->setCellValue('B6', '=MAX(B1:B3)');

try {
    $writer = new Xlsx($spreadsheet);
    $writer->save('hello.xlsx');

} catch (PhpOffice\PhpSpreadsheet\Writer\Exception $e) {
    echo $e->getMessage();
}

Узнать более подробно о всех возможных операциях можно из примеров библиотеки.

Резюме

Создание файлов для excel на php не является такой сложной процедурой благодаря данной библиотеке. В ней доступно большое количество необходимых возможностей, которых должно быть достаточно для генерация различного рода отчетов и документов. Для более старых версий PHP можно использовать предыдущую версию данной библиотеки PHPExcel. Также хотел отметить, что при использовании PhpSpreadsheet были случаи, когда с помощью метода save класса Xlsx не удавалось сохранять файл в выходной буфер php://output, как правило эта ошибка связана с ограниченным доступом к временной папке. Для решения данной проблемы я сохранял файл в папку на сервере, а потом с помощью функции readfile отправлял его в буфер вывода.